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

  (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

# Python Exceptions: try, except, else, finally

def divide(x, y):

    try:

        result = x / y

    except ZeroDivisionError as zero_div_err:

        print("Except clause:", zero_div_err)

    else:

        print("Else clause: The result of division is", result)

    finally:

        print("Finally clause is executed.")

In [1]: def divide(x, y):

   ...: try:

   ...: result = x / y

   ...: except ZeroDivisionError as zero_div_err:

   ...: print("Except clause:", zero_div_err)

   ...: else:

   ...: print("Else clause: The result of division is", result)

   ...: finally:

   ...: print("Finally clause is executed.")

   ...:

   ...:

In [2]: divide(1, 2)

Else clause: The result of division is 0.5

Finally clause is executed. 

In [3]: divide(1, 0)

Except clause: division by zero

Finally clause is executed.

In [4]: divide("1", "2")

Finally clause is executed.

Traceback (most recent call last):

File "<ipython-input-4-bbf78a5b43b9>", line 1, in <module>

divide("1", "2")

File "<ipython-input-1-78cbb56f9746>", line 3, in divide

result = x / y

TypeError: unsupported operand type(s) for /: 'str' and 'str'

Traceback (most recent call last):

File "<ipython-input-4-bbf78a5b43b9>", line 1, in <module>

divide("1", "2")

File "<ipython-input-1-78cbb56f9746>", line 3, in divide

result = x / y

TypeError: unsupported operand type(s) for /: 'str' and 'str' 

import numpy as np

arr = np.array([[1., 2., 3.], [4.0, 5., 6.]])

print(arr)

[Out]

[[1. 2. 3.]
 [4. 5. 6.]]

# It will not work and eraise an error

try:

    arr.reshape(5, -1)

except Exception as e:

    print(f"{type(e).__name__}: {e}")

[Out] 

ValueError: cannot reshape array of size 6 into shape (5,newaxis)

 BaseException

 +-- SystemExit

 +-- KeyboardInterrupt

 +-- GeneratorExit

 +-- Exception

      +-- StopIteration

      +-- StopAsyncIteration

      +-- ArithmeticError

      |    +-- FloatingPointError

      |    +-- OverflowError

      |    +-- ZeroDivisionError

      +-- AssertionError

      +-- AttributeError

      +-- BufferError

      +-- EOFError

      +-- ImportError

      |    +-- ModuleNotFoundError

      +-- LookupError

      |    +-- IndexError

      |    +-- KeyError

      +-- MemoryError

      +-- NameError

      |    +-- UnboundLocalError

      +-- OSError

      |    +-- BlockingIOError

      |    +-- ChildProcessError

      |    +-- ConnectionError

      |    |    +-- BrokenPipeError

      |    |    +-- ConnectionAbortedError

      |    |    +-- ConnectionRefusedError

      |    |    +-- ConnectionResetError

      |    +-- FileExistsError

      |    +-- FileNotFoundError

      |    +-- InterruptedError

      |    +-- IsADirectoryError

      |    +-- NotADirectoryError

      |    +-- PermissionError

      |    +-- ProcessLookupError

      |    +-- TimeoutError

      +-- ReferenceError

      +-- RuntimeError

      |    +-- NotImplementedError

      |    +-- RecursionError

      +-- SyntaxError

      |    +-- IndentationError

      |         +-- TabError

      +-- SystemError

      +-- TypeError

      +-- ValueError

      |    +-- UnicodeError

      |         +-- UnicodeDecodeError

      |         +-- UnicodeEncodeError

      |         +-- UnicodeTranslateError

      +-- Warning

           +-- DeprecationWarning

           +-- PendingDeprecationWarning

           +-- RuntimeWarning

           +-- SyntaxWarning

           +-- UserWarning

           +-- FutureWarning

           +-- ImportWarning

           +-- UnicodeWarning

           +-- BytesWarning

           +-- ResourceWarning

 (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' 

  (1) 이름을 지정한 일반 매개변수를 앞에, 가변 매개변수를 뒤에 정의한 함수

      (Keyword argument First, Variable-length Arguments Second)

In [1]: def repeat_str_KeyArg_first(repeat_num, *strings):

   ...: result = [repeat_num * strings]

   ...: return result

   ...:

   ...:

In [2]: repeat_str_KeyArg_first(3, 'a', 'b', 'c')

   ...:

Out[2]: [('a', 'b', 'c', 'a', 'b', 'c', 'a', 'b', 'c')] 

In [1]: def repeat_str_KeyArg_first(repeat_num, *strings):

   ...: result = [repeat_num * strings]

   ...: return result

   ...:

In [3]: repeat_str_KeyArg_first(repeat_num = 3, 'a', 'b', 'c') # SyntaxError

   ...:

File "<ipython-input-3-2047f5ba4020>", line 1

repeat_str_KeyArg_first(repeat_num = 3, 'a', 'b', 'c')

^

SyntaxError: positional argument follows keyword argument

File "<ipython-input-3-2047f5ba4020>", line 1

repeat_str_KeyArg_first(repeat_num = 3, 'a', 'b', 'c')

^

SyntaxError: positional argument follows keyword argument

  (2) 가변 매개변수를 앞에, 이름을 지정한 일반 매개변수를 뒤에 정의한 함수

      (Variable-length Arguments First, Keyword argument Second)

In [4]: def repeat_str_VarArg_first(*strings, repeat_num):

   ...: result = [repeat_num * strings]

   ...: return result

   ...:

   ...:

In [5]: repeat_str_VarArg_first('a', 'b', 'c', repeat_num = 3) # works well

   ...:

Out[5]: [('a', 'b', 'c', 'a', 'b', 'c', 'a', 'b', 'c')] 

In [4]: def repeat_str_VarArg_first(*strings, repeat_num):

   ...: result = [repeat_num * strings]

   ...: return result

   ...:

   ...:

In [6]: repeat_str_VarArg_first('a', 'b', 'c', 3) # TypeError

   ...:

Traceback (most recent call last):

File "<ipython-input-6-13d658b54364>", line 1, in <module>

repeat_str_VarArg_first('a', 'b', 'c', 3)

TypeError: repeat_str_VarArg_first() missing 1 required keyword-only argument: 'repeat_num'

Traceback (most recent call last):

File "<ipython-input-6-13d658b54364>", line 1, in <module>

repeat_str_VarArg_first('a', 'b', 'c', 3)

TypeError: repeat_str_VarArg_first() missing 1 required keyword-only argument: 'repeat_num'