R, Python 분석과 프로그래밍의 친구 (by R Friend)

<CD>

</CATALOG> 

- source: https://www.w3schools.com/xml/cd_catalog.xml -

import pandas as pd

import xml.etree.ElementTree as ET

import sys

if sys.version_info[0] == 3:

    from urllib.request import urlopen

else:

    from urllib import urlopen

# If you are using Python 3.x version, then ImportError will be raised as below

from urllib import urlopen

---------------------------------------------------------------------------
ImportError                               Traceback (most recent call last)
<ipython-input-2-dbf1dbb53f94> in <module>()
----> 1 from urllib import urlopen

ImportError: cannot import name 'urlopen'

url = "https://www.w3schools.com/xml/cd_catalog.xml"

response = urlopen(url).read()

xtree = ET.fromstring(response)

xtree

<Element 'CATALOG' at 0x00000219E77DCCC8>

rows = []

# iterate through each node of the tree

for node in xtree:

    n_title = node.find("TITLE").text

    n_artist = node.find("ARTIST").text

    n_country = node.find("COUNTRY").text

    n_company = node.find("COMPANY").text

    n_price = node.find("PRICE").text

    n_year = node.find("YEAR").text

    rows.append({"title": n_title, 

                 "artist": n_artist, 

                 "country": n_country, 

                 "company": n_company, 

                 "price": n_price, 

                 "year": n_year})

# convert XML data to pandas DataFrame

columns = ["title", "artist", "country", "company", "price", "year"]

catalog_cd_df = pd.DataFrame(rows, columns = columns)

catalog_cd_df.head(10)

catalog_cd_df.dtypes

title      object
artist     object
country    object
company    object
price      object
year       object
dtype: object

import numpy as np

catalog_cd_df = catalog_cd_df.astype({'price': np.float, 

                                      'year': int})

catalog_cd_df.dtypes

title       object
artist      object
country     object
company     object
price      float64
year         int32
dtype: object

country_mean = catalog_cd_df.groupby('country').price.mean()

country_mean

country
EU        9.320000
Norway    7.900000
UK        8.984615
USA       9.385714
Name: price, dtype: float64

country_mean_df = pd.DataFrame(country_mean).reset_index()

import seaborn as sns

sns.barplot(x='country', y='price', data=country_mean_df)

plt.show()

 (1) API 웹 사이트에서 JSON 포맷 자료를 Python으로 읽어오기

# parse a JSON string using json.loads() method : returns a dictionary

import json

import urllib

import pandas as pd

# API request to the URL

import sys

if sys.version_info[0] == 3:

    from urllib.request import urlopen # for Python 3.x

else:

    from urllib import urlopen           # for Python 2.x

with urlopen("http://api.nobelprize.org/v1/prize.json") as url:

    novel_prize_json_file = url.read()

# ImportError: cannot import name 'urlopen' at python 3.x

from urllib import urlopen # It's only for Python 2.x. It's not working at Python 3.x

with urlopen("http://api.nobelprize.org/v1/prize.json") as url:

    novel_prize_json_file = url.read()

---------------------------------------------------------------------------
ImportError                               Traceback (most recent call last)
<ipython-input-1-81c8fae1a1fd> in <module>()
      1 # API request to the URL
----> 2 from urllib import urlopen
      3 
      4 with urlopen("http://api.nobelprize.org/v1/prize.json") as url:
      5     novel_prize_json_file = url.read()

ImportError: cannot import name 'urlopen'

# decoding to python object

novel_prize_json = json.loads(novel_prize_json_file.decode('utf-8'))

# decoding TypeError. decode using decode('utf-8')

novel_prize_json = json.loads(novel_prize_json_file.decode)

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-9-1fda68be6386> in <module>()
      1 # decoding TypeError
----> 2 novel_prize_json = json.loads(novel_prize_json_file.decode)

C:\Users\admin\Anaconda3\lib\json\__init__.py in loads(s, encoding, cls, object_hook, parse_float, parse_int, parse_constant, object_pairs_hook, **kw)
    310     if not isinstance(s, str):
    311         raise TypeError('the JSON object must be str, not {!r}'.format(
--> 312                             s.__class__.__name__))
    313     if s.startswith(u'\ufeff'):
    314         raise JSONDecodeError("Unexpected UTF-8 BOM (decode using utf-8-sig)",

TypeError: the JSON object must be str, not 'builtin_function_or_method'

novel_prize_json.keys()

dict_keys(['prizes'])

novel_prize_json['prizes'][0].keys()

dict_keys(['laureates', 'year', 'overallMotivation', 'category'])

novel_prize_json['prizes'][0]

{'category': 'physics',
 'laureates': [{'firstname': 'Arthur',
   'id': '960',
   'motivation': '"for the optical tweezers and their application to biological systems"',
   'share': '2',
   'surname': 'Ashkin'},
  {'firstname': 'Gérard',
   'id': '961',
   'motivation': '"for their method of generating high-intensity, ultra-short optical pulses"',
   'share': '4',
   'surname': 'Mourou'},
  {'firstname': 'Donna',
   'id': '962',
   'motivation': '"for their method of generating high-intensity, ultra-short optical pulses"',
   'share': '4',
   'surname': 'Strickland'}],
 'overallMotivation': '"for groundbreaking inventions in the field of laser physics"', 

'year': '2018'} 

print(json.dumps(novel_prize_json['prizes'][0], indent=4))

{
    "laureates": [
        {
            "share": "2",
            "id": "960",
            "surname": "Ashkin",
            "motivation": "\"for the optical tweezers and their application to biological systems\"",
            "firstname": "Arthur"
        },
        {
            "share": "4",
            "id": "961",
            "surname": "Mourou",
            "motivation": "\"for their method of generating high-intensity, ultra-short optical pulses\"",
            "firstname": "G\u00e9rard"
        },
        {
            "share": "4",
            "id": "962",
            "surname": "Strickland",
            "motivation": "\"for their method of generating high-intensity, ultra-short optical pulses\"",
            "firstname": "Donna"
        }
    ],
    "year": "2018",
    "overallMotivation": "\"for groundbreaking inventions in the field of laser physics\"",
    "category": "physics"
}

print(json.dumps(novel_prize_json['prizes'][0], indent=4, sort_keys=True))

{
    "category": "physics",
    "laureates": [
        {
            "firstname": "Arthur",
            "id": "960",
            "motivation": "\"for the optical tweezers and their application to biological systems\"",
            "share": "2",
            "surname": "Ashkin"
        },
        {
            "firstname": "G\u00e9rard",
            "id": "961",
            "motivation": "\"for their method of generating high-intensity, ultra-short optical pulses\"",
            "share": "4",
            "surname": "Mourou"
        },
        {
            "firstname": "Donna",
            "id": "962",
            "motivation": "\"for their method of generating high-intensity, ultra-short optical pulses\"",
            "share": "4",
            "surname": "Strickland"
        }
    ],
    "overallMotivation": "\"for groundbreaking inventions in the field of laser physics\"",
    "year": "2018"
}

 (2) JSON 포맷 데이터를 pandas DataFrame으로 만들기

novel_prize_physics = pd.DataFrame(novel_prize_json['prizes'][0]["laureates"])

novel_prize_physics

novel_prize_physics = pd.DataFrame(novel_prize_json['prizes'][0]["laureates"], 

                                   columns = ['id', 'firstname', 'surname', 'share', 'motivation'])

novel_prize_physics

# make a dictionary

pgm_lang = {

    "java": 20, 

    "javascript": 8, 

    "c": 7,  

    "r": 4, 

    "python": 28 } 

 (1) 키를 기준으로 오름차순 정렬 (sort by key in ascending order): sorted()

sorted(pgm_lang.keys())

['c', 'java', 'javascript', 'python', 'r']

sorted(pgm_lang.items())

[('c', 7), ('java', 20), ('javascript', 8), ('python', 28), ('r', 4)]

for key, value in sorted(pgm_lang.items()):

    print(key, ":", value)

c : 7
java : 20
javascript : 8
python : 28
r : 4

# list comprehension

[print(key, ":", value) for (key, value) in sorted(pgm_lang.items())]

c : 7
java : 20
javascript : 8
python : 28
r : 4

[None, None, None, None, None]

# sort a dictionary by custom key function (eg. by the length of key strings)

pgm_lang_len = sorted(pgm_lang.items(), key = lambda item: len(item[0])) # key: [0]

for key, value in pgm_lang_len:

    print(key, ":", value)

c : 7
r : 4
java : 20
python : 28
javascript : 8

 (2) 키를 기준으로 내림차순 정렬 (sort by key in descending order): reverse=True

# sorting in reverse order

sorted(pgm_lang.keys(), reverse=True)

['r', 'python', 'javascript', 'java', 'c']

for (key, value) in sorted(pgm_lang.items(), reverse=True):

    print(key, ":", value)

r : 4
python : 28
javascript : 8
java : 20
c : 7

 (3) 값을 기준으로 오름차순 정렬 (sort by value in ascending order)

sorted(pgm_lang.items(), key = lambda item: item[1]) # value: [1]

[('r', 4), ('c', 7), ('javascript', 8), ('java', 20), ('python', 28)]

pgm_lang_val = sorted(pgm_lang.items(), key = lambda item: item[1])

for key, value in pgm_lang_val:

    print(key, ":", value)

r : 4
c : 7
javascript : 8
java : 20
python : 28

# list comprehension

[print(key, ":", value) for (key, value) in sorted(pgm_lang.items(), key = lambda item: item[1])]

r : 4
c : 7
javascript : 8
java : 20
python : 28

 (4) 값을 기준으로 내림차순 정렬 (sort by value in descending order) 

# in reverse order

pgm_lang_val_reverse = sorted(pgm_lang.items(), 

                              reverse=True, 

                              key=lambda item: item[1])

for key, value in pgm_lang_val_reverse:

    print(key, ":", value)

python : 28
java : 20
javascript : 8
c : 7
r : 4

# the same result with above

for items in pgm_lang_val_reverse:

    print(items[0], ":", items[1])

python : 28
java : 20
javascript : 8
c : 7
r : 4

 (1) pandas DataFrame 내 문자열 칼럼을 구분자로 분리하여 여러개의 칼럼 만들기

import pandas as pd

df = pd.DataFrame({'col': ['a:1:20.3:S', 'b:2:10.5:C', 'c:3:51.9:A']})

df

df[['group', 'id', 'value', 'grade']] = pd.DataFrame(df.col.str.split(':', 3).tolist())

df

df2 = pd.DataFrame(df.col.str.split(':', 3).tolist(), 

                   columns = ['group', 'id', 'value', 'grade'])

df2

df2.dtypes

group    object
id       object
value    object
grade    object
dtype: object

 (2) PostgreSQL, GPDB에서 문자열 칼럼을 구분자로 분리하여 여러개 칼럼 만들기

-- make a table

DROP TABLE IF EXISTS grp_val_grade;

CREATE TABLE grp_val_grade (

col varchar(100) NOT NULL

);

INSERT INTO grp_val_grade VALUES ('a:1:20.3:S');

INSERT INTO grp_val_grade VALUES('b:2:10.5:C');

INSERT INTO grp_val_grade VALUES('c:3:51.9:A');

SELECT * FROM grp_val_grade; 

-- split a column by delimeter and make 4 columns

DROP TABLE IF EXISTS grp_val_grade2;

CREATE TABLE grp_val_grade2 AS (

SELECT 

col

, split_part(col, ':', 1) AS group

, split_part(col, ':', 2) AS id 

, split_part(col, ':', 3) AS value

, split_part(col, ':', 4) AS grade

FROM grp_val_grade

);

SELECT * FROM grp_val_grade2;

  (1) pd.to_numeric() 함수를 이용한 문자열 칼럼의 숫자형 변환

import numpy as np

import pandas as pd

# make a DataFrame

df = pd.DataFrame({'col_str': ['1', '2', '3', '4', '5']})

df

# check data types 

df.dtypes

col_str    object
dtype: object

df['col_int'] = pd.to_numeric(df['col_str'])

df

df.dtypes

col_str    object
col_int     int64
dtype: object

# make a DataFrame with 3 string columns

df2 = pd.DataFrame({'col_str_1': ['1', '2', '3'], 

                   'col_str_2': ['4', '5', '6'], 

                   'col_str_3': ['7.0', '8.1', '9.2']})

df2

df2.dtypes

col_str_1    object
col_str_2    object
col_str_3    object
dtype: object

# convert 'col_str_1' and 'col_str_2' to numeric

df2[['col_int_1', 'col_int_2']] = df2[['col_str_1', 'col_str_2']].apply(pd.to_numeric)

df2

df2.dtypes

col_str_1    object
col_str_2    object
col_str_3    object
col_int_1     int64
col_int_2     int64
dtype: object

# convert all columns of a DataFrame to numeric using apply() and to_numeric together

df3 = df2.apply(pd.to_numeric)

df3.dtypes

col_str_1      int64
col_str_2      int64
col_str_3    float64
col_int_1      int64
col_int_2      int64
dtype: object

  (2) astype() 메소드를 이용한 문자열 칼럼의 숫자형 변환

df4 = pd.DataFrame({'col_str_1': ['1', '2', '3'], 

                   'col_str_2': ['4.1', '5.5', '6.0']}) 

df4.dtypes

col_str_1    object
col_str_2    object
dtype: object

df5 = df4.astype(float)

df5

df5.dtypes

col_str_1    float64
col_str_2    float64
dtype: object

df6 = df4.astype({'col_str_1': int, 

                  'col_str_2': np.float})

df6

 df6.dtypes

col_str_1      int64
col_str_2    float64
dtype: object

  DataFrame에 문자가 포함된 칼럼이 같이 있을 경우 ValueError

df7 = pd.DataFrame({'col_1': ['1', '2', '3'], 

                   'col_2': ['aaa', 'bbb', 'ccc']})

df7

df7.dtypes

col_1    object
col_2    object
dtype: object

# ValueError

df7 = df7.apply(pd.to_numeric)

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
pandas/_libs/src/inference.pyx in pandas._libs.lib.maybe_convert_numeric()

ValueError: Unable to parse string "aaa"

During handling of the above exception, another exception occurred:
-- 중간 생략 --

~/anaconda3/lib/python3.6/site-packages/pandas/core/tools/numeric.py in to_numeric(arg, errors, downcast)
    124             coerce_numeric = False if errors in ('ignore', 'raise') else True
    125             values = lib.maybe_convert_numeric(values, set(),
--> 126                                                coerce_numeric=coerce_numeric)
    127 
    128     except Exception:

pandas/_libs/src/inference.pyx in pandas._libs.lib.maybe_convert_numeric()

ValueError: ('Unable to parse string "aaa" at position 0', 'occurred at index col_2')

# ValueError

df7 = df7.astype(int)

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-124-f50dad302c83> in <module>()
----> 1 df7 = df7.astype(int)

-- 중간 생략 --

~/anaconda3/lib/python3.6/site-packages/pandas/core/dtypes/cast.py in astype_nansafe(arr, dtype, copy)
    623     elif arr.dtype == np.object_ and np.issubdtype(dtype.type, np.integer):
    624         # work around NumPy brokenness, #1987
--> 625         return lib.astype_intsafe(arr.ravel(), dtype).reshape(arr.shape)
    626 
    627     if dtype.name in ("datetime64", "timedelta64"):

pandas/_libs/lib.pyx in pandas._libs.lib.astype_intsafe()

pandas/_libs/src/util.pxd in util.set_value_at_unsafe()
 

ValueError: invalid literal for int() with base 10: 'aaa' 

 문자열을 숫자형으로 변환 시 ValueError 를 무시하기: df.apply(pd.to_numeric, errors = 'coerce') 

df8 = pd.DataFrame({'col_1': ['1', '2', '3'], 

                   'col_2': ['4', 'bbb', '6']})

df8

df8 = df8.apply(pd.to_numeric)

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
pandas/_libs/src/inference.pyx in pandas._libs.lib.maybe_convert_numeric()

ValueError: Unable to parse string "bbb"

During handling of the above exception, another exception occurred:

ValueError                                Traceback (most recent call last)
<ipython-input-130-9e8d711c10d5> in <module>()
----> 1 df8 = df8.apply(pd.to_numeric)

~/anaconda3/lib/python3.6/site-packages/pandas/core/frame.py in apply(self, func, axis, broadcast, raw, reduce, args, **kwds)
   4260                         f, axis,
   4261                         reduce=reduce,
-> 4262                         ignore_failures=ignore_failures)
   4263             else:
   4264                 return self._apply_broadcast(f, axis)

~/anaconda3/lib/python3.6/site-packages/pandas/core/frame.py in _apply_standard(self, func, axis, ignore_failures, reduce)
   4356             try:
   4357                 for i, v in enumerate(series_gen):
-> 4358                     results[i] = func(v)
   4359                     keys.append(v.name)
   4360             except Exception as e:

~/anaconda3/lib/python3.6/site-packages/pandas/core/tools/numeric.py in to_numeric(arg, errors, downcast)
    124             coerce_numeric = False if errors in ('ignore', 'raise') else True
    125             values = lib.maybe_convert_numeric(values, set(),
--> 126                                                coerce_numeric=coerce_numeric)
    127 
    128     except Exception:

pandas/_libs/src/inference.pyx in pandas._libs.lib.maybe_convert_numeric()

ValueError: ('Unable to parse string "bbb" at position 1', 'occurred at index col_2')

df8

 (1) 하나의 DataFrame을 하나의 Excel Sheet에 쓰기

In [1]: import numpy as np

   ...: import pandas as pd

   ...: import os

In [2]: base_dir = "C:/Users/admin/Documents/data"

   ...: file_nm = "df.xlsx"

   ...: xlxs_dir = os.path.join(base_dir, file_nm) 

In [3]: df = pd.DataFrame({'group': ['a', 'b', 'c', 'd', 'e'],

   ...: 'value_1': [10.056, 20.534, 30.90, 41.9423, 35.21],

   ...: 'value_2': [200, 500, 600, np.nan, 1200]},

   ...: index = [1, 2, 3, 4, 5])

In [4]: df

Out[4]:

  group  value_1  value_2

1     a  10.0560    200.0

2     b  20.5340    500.0

3     c  30.9000    600.0

4     d  41.9423      NaN

5     e  35.2100   1200.0

#-- write an object to an Excel sheet using pd.DataFrame.to_excel()

df.to_excel(xlxs_dir, # directory and file name to write

            sheet_name = 'Sheet1', 

            na_rep = 'NaN', 

            float_format = "%.2f", 

            header = True, 

            #columns = ["group", "value_1", "value_2"], # if header is False

            index = True, 

            index_label = "id", 

            startrow = 1, 

            startcol = 1, 

            #engine = 'xlsxwriter', 

            freeze_panes = (2, 0)

            ) 

 (2) 두 개 이상의 DataFrame을 여러개의 Excel Sheets에 나누어서 쓰기

In [8]: df_1 = df.copy()

In [9]: df_2 = pd.DataFrame(np.arange(15).reshape(5, 3),

   ...: columns = ['col_1', 'col_2', 'col_3'],

   ...: index = [1, 2, 3, 4, 5])

In [10]: df_1

Out[10]:

  group  value_1  value_2

1     a  10.0560    200.0

2     b  20.5340    500.0

3     c  30.9000    600.0

4     d  41.9423      NaN

5     e  35.2100   1200.0

In [11]: df_2

Out[11]:

   col_1  col_2  col_3

1      0      1      2

2      3      4      5

3      6      7      8

4      9     10     11

5     12     13     14

# Write two DataFrames to Excel using to_excel(). Need to specify an ExcelWriter object first.

with pd.ExcelWriter(xlxs_dir) as writer:

    df_1.to_excel(writer, sheet_name = 'DF_1')

    df_2.to_excel(writer, sheet_name = 'DF_2') 

In [13]: with pd.ExcelWriter(xlxs_dir) as writer:

    ...: df_1.to_excel(writer, sheet_name = 'DF_1')

    ...: df_2.to_excel(writer, sheet_name = 'DF_2')

    ...:

    ...:

Traceback (most recent call last):

File "<ipython-input-13-9ba7e09cf9e3>", line 3, in <module>

df_2.to_excel(writer, sheet_name = 'DF_2')

File "C:\Users\admin\Anaconda3\lib\site-packages\pandas\io\excel.py", line 1191, in __exit__

self.close()

File "C:\Users\admin\Anaconda3\lib\site-packages\pandas\io\excel.py", line 1195, in close

return self.save()

 .... 중간 생략 ....

File "C:\Users\admin\Anaconda3\lib\site-packages\xlsxwriter\workbook.py", line 611, in _store_workbook

allowZip64=self.allow_zip64)

File "C:\Users\admin\Anaconda3\lib\zipfile.py", line 1009, in __init__

self.fp = io.open(file, filemode)

PermissionError: [Errno 13] Permission denied: 'C:/Users/admin/Documents/data\\df.xlsx'