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

import numpy as np

import pandas as pd

from pandas import DataFrame

np.random.seed(123)

df = DataFrame({'col_1': np.random.randint(20, size=20), 

                      'col_2': np.random.randn(20)})

df

  (1) pd.cut : 동일 길이로 나누어서 범주 만들기(equal-length buckets categorization)

factor_col_1 = pd.cut(df.col_1, 4)

factor_col_1

0 (9.5, 14.25]

1     (-0.019, 4.75]
2     (-0.019, 4.75]
3        (4.75, 9.5]
4      (14.25, 19.0]
5      (14.25, 19.0]
6       (9.5, 14.25]
7     (-0.019, 4.75]
8     (-0.019, 4.75]
9      (14.25, 19.0]
10     (14.25, 19.0]
11       (4.75, 9.5]
12    (-0.019, 4.75]
13      (9.5, 14.25]
14    (-0.019, 4.75]
15     (14.25, 19.0]
16     (14.25, 19.0]
17      (9.5, 14.25]
18    (-0.019, 4.75]
19    (-0.019, 4.75]
Name: col_1, dtype: category
Categories (4, interval[float64]): [(-0.019, 4.75] < (4.75, 9.5] < (9.5, 14.25] < (14.25, 19.0]]

grouped_col_1 = df.col_1.groupby(factor_col_1)

grouped_col_1.agg(['count', 'mean', 'std', 'min', 'max'])

def summary_func(group):

    return {'count': group.count(), 

             'mean': group.mean(), 

             'std': group.std(), 

             'min': group.min(), 

             'max': group.max()}

grouped_col_1.apply(summary_func)

col_1                
(-0.019, 4.75]  count     8.000000
                max       4.000000
                mean      1.125000
                min       0.000000
                std       1.457738
(4.75, 9.5]     count     2.000000
                max       9.000000
                mean      7.500000
                min       6.000000
                std       2.121320
(9.5, 14.25]    count     4.000000
                max      14.000000
                mean     12.750000
                min      10.000000
                std       1.892969
(14.25, 19.0]   count     6.000000
                max      19.000000
                mean     17.000000
                min      15.000000
                std       1.788854
Name: col_1, dtype: float64

grouped_col_1.apply(summary_func).unstack()

  (2) pd.qcut() : 동일 개수로 나누어서 범주 만들기 (equal-size buckets categorization)

bucket_qcut_col_2 = pd.qcut(df.col_2, 4, labels=False)

bucket_qcut_col_2

0     3
1     3
2     0
3     2
4     1
5     3
6     2
7     0
8     1
9     3
10    3
11    0
12    0
13    1
14    1
15    1
16    2
17    2
18    0
19    2
Name: col_2, dtype: int64

bucket_qcut_label_col_2 = pd.qcut(df.col_2, 4, labels=np.arange(4, 0, -1))

bucket_qcut_label_col_2

0     1
1     1
2     4
3     2
4     3
5     1
6     2
7     4
8     3
9     1
10    1
11    4
12    4
13    3
14    3
15    3
16    2
17    2
18    4
19    2

Name: col_2, dtype: category
Categories (4, int64): [4 < 3 < 2 < 1]

grouped = df.col_2.groupby(bucket_qcut_label_col_2)

grouped.apply(summary_func).unstack()

 import numpy as np

 import pandas as pd

 from pandas import DataFrame

 df = DataFrame({'group_1': ['a', 'a', 'a', 'a', 'a',  

                                      'b', 'b', 'b', 'b', 'b',], 

                       'group_2': ['c', 'c', 'c', 'd', 'd', 

                                     'e', 'e', 'e', 'f', 'f'], 

                       'col': [1, 2, np.NaN, 4, np.NaN, 

                              6, 7, np.NaN, 9, 10]})

 df

  (a) 그룹 별 NaN이 아닌 원소의 개수 구하여 데이터프레임에 새로운 칼럼 추가하기

# 'count' grouped by (['group_1', 'group_2'])

 df['count_col'] = df.groupby(['group_1', 'group_2']).col.transform('count')

 df

  (b) 그룹 별 NaN이 아닌 원소의 합을 구하여 데이터프레임에 새로운 칼럼 추가하기

 # 'sum' grouped by (['group_1', 'group_2'])

 df['sum_col'] = df.groupby(['group_1', 'group_2']).col.transform('sum')

 df

  (c) 그룹 별 NaN이 아닌 원소 중 최대값을 구하여 데이터프레임에 새로운 칼럼 추가하기

 # 'max' grouped by (['group_1', 'group_2'])

 df['max_col'] = df.groupby(['group_1', 'group_2']).col.transform('max')

 df

In [1]: import numpy as np

   ...: import pandas as pd

In [2]: np.random.seed(123) # for reproducibility

In [3]: df = pd.DataFrame({'grp': ['a', 'a', 'a', 'a', 'b', 'b', 'b', 'b'],

   ...: 'col_1': np.random.randn(8),

   ...: 'col_2': np.random.randn(8)})

   ...:

   ...: df

Out[3]:

col_1 col_2 grp

0 -1.085631 1.265936 a

1 0.997345 -0.866740 a

2 0.282978 -0.678886 a

3 -1.506295 -0.094709 a

4 -0.578600 1.491390 b

5 1.651437 -0.638902 b

6 -2.426679 -0.443982 b

7 -0.428913 -0.434351 b

In [4]: df.loc[[1, 6], ['col_1', 'col_2']] = np.nan

   ...: df

Out[4]:

col_1 col_2 grp

0 -1.085631 1.265936 a

1 NaN NaN a

2 0.282978 -0.678886 a

3 -1.506295 -0.094709 a

4 -0.578600 1.491390 b

5 1.651437 -0.638902 b

6 NaN NaN b

7 -0.428913 -0.434351 b

In [5]: df.groupby('grp').mean()

   ...:

Out[5]:

      col_1       col_2

grp

a -0.769649    0.164114

b  0.214641    0.139379

In [6]: fill_mean_func = lambda g: g.fillna(g.mean())

In [7]: df.groupby('grp').apply(fill_mean_func)

Out[7]:

            col_1         col_2       grp

grp

a     0  -1.085631    1.265936    a

      1  -0.769649    0.164114   a

      2   0.282978   -0.678886    a

      3  -1.506295   -0.094709    a

b    4  -0.578600    1.491390    b

      5   1.651437   -0.638902    b

      6   0.214641    0.139379    b

      7  -0.428913   -0.434351    b

In [8]: fill_values = {'a': 1.0, 'b': 0.5}

In [9]: fill_func = lambda d: d.fillna(fill_values[d.name])

In [10]: df.groupby('grp').apply(fill_func)

Out[10]:

      col_1            col_2 grp

0   -1.085631       1.265936 a

1    1.000000       1.000000 a

2    0.282978      -0.678886 a

3   -1.506295      -0.094709 a

4   -0.578600       1.491390 b

5    1.651437      -0.638902 b

6    0.500000       0.500000 b

7   -0.428913      -0.434351 b

import pandas as pd

import numpy as np

df = pd.DataFrame({'grp_col' : ['a', 'a', 'a', 'a', 'a', 'b', 'b', 'b', 'b', 'b'], 

                   'val' : np.arange(10)+1,                   

                   'weight' : [0.0, 0.1, 0.2, 0.3, 0.4, 0.0, 0.1, 0.2, 0.3, 0.4]})

df

  (1) GroupBy 를 활용하여 그룹 별 가중 평균 구하기

# group weighted average by category

grouped = df.groupby('grp_col')

weighted_avg_func = lambda g:np.average(g['val'], weights=g['weight'])

grouped.apply(weighted_avg_func)

grp_col
a    4.0
b    9.0
dtype: float64

 (2) 수작업으로 그룹 별 가중 평균 구하기 (Split -> Apply -> Combine) 

# split

df_a = df[df['grp_col']=='a']

df_b = df[df['grp_col']=='b']

 df_a

 df_b

# apply

weighted_avg_a = sum((df_a['val']*df_a['weight']))/sum(df_a['weight'])

weighted_avg_b = sum((df_b['val']*df_b['weight']))/sum(df_b['weight'])

 weighted_avg_a

4.0

 weighted_avg_b

9.0

# combine

weighted_avg_ab = pd.DataFrame({'grp_col': ['a', 'b'], 

                               'weighted_average': [weighted_avg_a, weighted_avg_b]})

weighted_avg_ab

  (1) 데이터프레임에서 여러개의 칼럼에 대해 다른 함수 적용하여 Group By 집계하기

      : grouped.apply(function)

import numpy as np

import pandas as pd

df = pd.DataFrame({'grp_col_1' : ['a', 'a', 'a', 'a', 'a', 'b', 'b', 'b', 'b', 'b'], 

                   'grp_col_2' : ['c', 'c', 'd', 'd', 'd', 'e', 'e', 'f', 'f', 'f'], 

                   'val_1' : np.arange(10),                   

                   'val_2' : np.random.randn(10)})

df

def func(x):

    d = {}

    d['val_1_mean'] = x['val_1'].mean()

    d['val_1_std'] = x['val_1'].std()

    d['val_2_max'] = x['val_2'].max()

    d['val_2_min'] = x['val_2'].min()

    d['val_2_range'] = x['val_2'].max() - x['val_2'].min()

    return pd.Series(d, index=['val_1_mean', 'val_1_std', 'val_2_max', 'val_2_min', 'val_2_range'])

df_return = df.groupby(['grp_col_1', 'grp_col_2']).apply(func)

df_return

df_return.reset_index()

df_return.reset_index(level='grp_col_2')

df_return.reset_index(drop=True)

df_return[['val_1_mean', 'val_1_std']]

  (2) 계층적 인덱스를 가진 데이터프레임의 여러개의 칼럼에 대해 다른 함수 적용하여 Group By 집계하기

      : grouped(level=['index1', 'index2']).apply(function)

# How to make a DataFrame with Hierarchical Index

arrays = [['a', 'a', 'a', 'a', 'a', 'b', 'b', 'b', 'b', 'b'], 

         ['c', 'c', 'd', 'd', 'd', 'e', 'e', 'f', 'f', 'f']]

myindex = pd.MultiIndex.from_arrays(arrays, names=('grp_idx_1', 'grp_idx_2'))

df2 = pd.DataFrame({'val_1': np.arange(10), 

                    'val_2': np.random.randn(10)}, 

                   index = myindex)

df2

df2.groupby(level=['grp_idx_1', 'grp_idx_2']).apply(func)

[ UCI Machine Learning Repository ]

• Abalone Data Set 설명: http://archive.ics.uci.edu/ml/datasets/Abalone
• Abalone Data Set: http://archive.ics.uci.edu/ml/machine-learning-databases/abalone/abalone.data
• Variables

Name		Data Type   Meas.   Description
----		---------   -----   -----------
Sex		nominal		    M, F, and I (infant)
Length		continuous   mm	    Longest shell measurement
Diameter	continuous   mm	    perpendicular to length
Height		continuous   mm	    with meat in shell
Whole weight	continuous   grams  whole abalone
Shucked weight	continuous   grams  weight of meat
Viscera weight	continuous   grams  gut weight (after bleeding)
Shell weight	continuous   grams  after being dried
Rings		integer		    +1.5 gives the age in years

# Importing common libraries

import numpy as np

import pandas as pd

# Import Abalone data set from UCI machine learning repository directly

import csv

import urllib2

url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/abalone/abalone.data'

downloaded_data  = urllib2.urlopen(url)

abalone = pd.read_csv(downloaded_data, 

                      names = ['sex', 'length', 'diameter', 'height', 

                               'whole_weight', 'shucked_weight', 'viscera_weight', 

                               'shell_weight', 'rings'], 

                      header = None)

abalone.head()

abalone['length_cat'] = np.where(abalone.length > np.median(abalone.length), 

                                 'length_long', # True

                                 'length_short') # False

abalone[['length', 'length_cat']][:10]

 (1) 함수 리스트(List)를 사용하여 다수의 GroupBy 집계 함수를 동일한 칼럼에 적용하기

grouped_ww = abalone.groupby(['sex', 'length_cat'])['whole_weight']

grouped_ww

<pandas.core.groupby.SeriesGroupBy object at 0x10a7e0290> 

(방법1) GroupBy methods

(방법2) grouped.agg(function)

grouped_ww.mean() # Series

sex  length_cat  
F    length_long     1.261330
     length_short    0.589702
I    length_long     0.923215
     length_short    0.351234
M    length_long     1.255182
     length_short    0.538157
Name: whole_weight, dtype: float64

grouped_ww.agg('mean') # Series

sex  length_cat  
F    length_long     1.261330
     length_short    0.589702
I    length_long     0.923215
     length_short    0.351234
M    length_long     1.255182
     length_short    0.538157
Name: whole_weight, dtype: float64

grouped_ww.agg(['size', 'mean', 'std', 'min', 'max']) # DataFrame

function_list = ['size', 'mean', 'std', 'min', 'max']

grouped_ww.agg(function_list)

grouped = abalone.groupby(['sex', 'length_cat'])

function_list = ['size', 'mean', 'std']

groupby_result = grouped['whole_weight', 'shell_weight'].agg(function_list)

groupby_result

groupby_result['shell_weight']

groupby_result['shell_weight'][['size', 'mean']]

groupby_result.loc['M']

groupby_result.loc['M', 'shell_weight']

 (2) 칼럼과 함수를 매핑한 Dict를 사용하여 칼럼별로 특정 GroupBy 집계 함수를 적용하기 

def range_func(x):

    max_val = np.max(x)

    min_val = np.min(x)

    range_val = max_val - min_val

    return range_val

def iqr_func(x):

    q3, q1 = np.percentile(x, [75, 25])

    iqr = q3 - q1

    return iqr

grouped.agg({'whole_weight': ['size', 'mean', 'std'], # put method's name as a string

            'shell_weight': [range_func, iqr_func]}) # UDF name

 (3) (이름, 함수)의 튜플 (Tuples of (name, function))을 사용하여 GroupBy 집계 함수에 이름 부여하기

# (name, function) tuples

grouped.agg({'whole_weight': ['size', 'mean', 'std'], 

            'shell_weight': [('Range', range_func),  # (name, function) tuple

                                    ('Inter-Quartile_Range', iqr_func)]}) # (name, function) tuple

# Importing common libraries

import numpy as np

import pandas as pd

# sample DataFrame

df = pd.DataFrame({'group': ['a', 'a', 'a', 'b', 'b', 'b'], 

                  'value_1': np.arange(6), 

                 'value_2': np.random.randn(6)})

df

# Making GroupBy object

grouped = df.groupby('group')

grouped

<pandas.core.groupby.DataFrameGroupBy object at 0x11136f550>

  (1) GroupBy 메소드를 이용한 집계 (GroupBy aggregation using methods)

count(): 그룹 내 non-NA 개수 

sum(): 그룹 내 non-NA 합 

grouped.count()

grouped.sum() # DataFrame

grouped.sum()['value_2'] # Series

group

a   -0.012473
b    0.072545 

Name: value_2, dtype: float64

pd.DataFrame(grouped.sum()['value_2']) # DataFrame 

min(): 그룹 내 non-NA 값 중 최소값 

max(): 그룹 내 non-NA 값 중 최대값 

grouped.min()

grouped.max()

mean(): 그룹 내 non-NA 값들의 평균값 

median(): 그룹 내 non-NA 값들의 중앙값 

grouped.mean()

grouped.median()

std(): 그룹 내 표준편차

var(): 그룹 내 분산

quantile(): 그룹 내 분위수 

grouped.std() 

grouped.var()

 # interpolation

grouped.quantile(0.1) 

first(): 그룹 내 non-NA 값 중 첫번째 값 

last(): 그룹 내 non-NA 값 중 마지막 값 

grouped.first()

grouped.last()

describe(): 그룹 별 기술통계량 

- 옆으로 길게

 describe().T: 그룹 별 기술통계량 

- 세로로 길게

grouped.describe()['value_1']

grouped.describe()['value_1'].T

  (2) 함수를 이용한 GroupBy 집계: grouped.agg(function)

def iqr_func(x):

    q3, q1 = np.percentile(x, [75, 25])

    iqr = q3 - q1

    return iqr

grouped.aggregate(function) 

grouped.agg(function) 

grouped.aggregate(iqr_func)

grouped.agg(iqr_func)

grouped.quantile([0.75, 0.25])

  (1) Dicts를 이용한 GroupBy 집계

# importing libraries

 import numpy as np

 import pandas as pd

 from pandas import DataFrame

 from pandas import Series

# making sample dataset

 df = DataFrame(data=np.arange(20).reshape(4, 5),

               columns = ['c1', 'c2', 'c3', 'c4', 'c5'], 

               index = ['r1', 'r2', 'r3', 'r4'])

 df

 mapping_dict_row = {'r1': 'row_g1', 

                   'r2': 'row_g1', 

                   'r3': 'row_g2', 

                   'r4': 'row_g2'}

 grouped_by_row = df.groupby(mapping_dict_row)

 grouped_by_row.sum()

 mapping_dict_col = {'c1': 'col_g1', 

                   'c2': 'col_g1', 

                   'c3': 'col_g2', 

                   'c4': 'col_g2', 

                   'c5': 'col_g2'}

 grouped_by_col = df.groupby(mapping_dict_col, axis=1)

 grouped_by_col.sum()

  (2) Series를 이용한 GroupBy 집계

 mapping_series_row = Series(mapping_dict_row)

 mapping_series_row

r1    row_g1
r2    row_g1
r3    row_g2
r4    row_g2
dtype: object

 df.groupby(mapping_series_row).sum()

 mapping_series_col = Series(mapping_dict_col)

 mapping_series_col

c1 col_g1

c2    col_g1
c3    col_g2
c4    col_g2
c5    col_g2 

dtype: object

 df.groupby(mapping_series_col, axis=1).sum()

 df.groupby(mapping_series_col, axis=1).mean()

 mapping_list_col = ['col_g1', 'col_g1', 'col_g2', 'col_g2', 'col_g2'] # lists

 df.groupby(mapping_list_col, axis=1).mean()

 mapping_list_col_2 = ['col_g1', 'col_g1', 'col_g2', 'col_g2', 'col_g2', 'col_g2']

 df.groupby(mapping_list_col_2, axis=1).mean()

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-31-76bb72a2996c> in <module>()
----> 1 df.groupby(mapping_list_col_2, axis=1).mean()

/Users/ihongdon/anaconda3/envs/py2.7_tf1.4/lib/python2.7/site-packages/pandas/core/generic.pyc in groupby(self, by, axis, level, as_index, sort, group_keys, squeeze, **kwargs)
   4414         return groupby(self, by=by, axis=axis, level=level, as_index=as_index,
   4415                        sort=sort, group_keys=group_keys, squeeze=squeeze,
-> 4416                        **kwargs)
   4417 
   4418     def asfreq(self, freq, method=None, how=None, normalize=False,

/Users/ihongdon/anaconda3/envs/py2.7_tf1.4/lib/python2.7/site-packages/pandas/core/groupby.pyc in groupby(obj, by, **kwds)
   1697         raise TypeError('invalid type: %s' % type(obj))
   1698 
-> 1699     return klass(obj, by, **kwds)
   1700 
   1701 

/Users/ihongdon/anaconda3/envs/py2.7_tf1.4/lib/python2.7/site-packages/pandas/core/groupby.pyc in __init__(self, obj, keys, axis, level, grouper, exclusions, selection, as_index, sort, group_keys, squeeze, **kwargs)
    390                                                     level=level,
    391                                                     sort=sort,
--> 392                                                     mutated=self.mutated)
    393 
    394         self.obj = obj

/Users/ihongdon/anaconda3/envs/py2.7_tf1.4/lib/python2.7/site-packages/pandas/core/groupby.pyc in _get_grouper(obj, key, axis, level, sort, mutated)
   2688                 in_axis, name, level, gpr = False, None, gpr, None
   2689             else:
-> 2690                 raise KeyError(gpr)
   2691         elif isinstance(gpr, Grouper) and gpr.key is not None:
   2692             # Add key to exclusions

KeyError: 'col_g1'