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

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

  (3) Functions를 이용한 GroupBy 집계

 df

 def row_grp_func(x):

    if x == 'r1' or x == 'r2':

        row_group = 'row_g1'

    else:

        row_group = 'row_g2'

    return row_group

 df.groupby(row_grp_func).sum()

  (4) Index Levels를 이용한 GroupBy 집계

 hier_columns = pd.MultiIndex.from_arrays([['col_g1', 'col_g1', 'col_g2', 'col_g2', 'col_g2'], 

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

                                        names = ['col_level_1', 'col_level_2'])

 hier_columns

MultiIndex(levels=[[u'col_g1', u'col_g2'], [u'c1', u'c2', u'c3', u'c4', u'c5']],
           labels=[[0, 0, 1, 1, 1], [0, 1, 2, 3, 4]],
           names=[u'col_level_1', u'col_level_2'])

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

                   columns = hier_columns, 

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

 hier_df

 hier_df.groupby(level = 'col_level_1', axis=1).mean()

In [1]: import pandas as pd

In [2]: df = pd.DataFrame({'id': [1, 2, 10, 20, 100, 200], 

   ...:                    'name': ['aaa', 'bbb', 'ccc', 'ddd', 'eee', 'fff']})

In [3]: df

Out[3]: 

    id name

0    1  aaa

1    2  bbb

2   10  ccc

3   20  ddd

4  100  eee

5  200  fff

  (1) 'id' 변수가 전체 5개 자리가 되도록 왼쪽에 비어있는 부분에 '0'을 채워서 새로운 변수 'id_2' 만들기

     (Left padding with zeros so that make 5 positions)

In [4]: df['id_2'] = df['id'].apply(lambda x: "{:0>5d}".format(x))

In [5]: df

Out[5]: 

    id      name   id_2

0      1   aaa      00001

1      2   bbb     00002

2    10   ccc     00010

3    20  ddd    00020

4  100  eee     00100

5  200  fff      00200

 (2) 새로 만든 'id_2' 변수와 'name' 변수를 각 원소별로 합쳐서 데이터프레임 안에

새로운 변수 'id_name' 만들기

    (element-wise string concatenation with multiple inputs array in pandas DataFrame)

In [6]: df['id_name'] = df[['id_2', 'name']].apply(lambda x: '_'.join(x), axis=1)

In [7]: df

Out[7]: 

       id    name   id_2         id_name

0      1    aaa      00001    00001_aaa

1      2    bbb     00002    00002_bbb

2    10    ccc     00010     00010_ccc

3    20   ddd    00020    00020_ddd

4  100   eee    00100     00100_eee

5  200   fff     00200     00200_fff 

(3) 'id' 변수의 값을 소숫점 두번째 자리까지 나타내는 새로운 변수 'id_3' 만들기

In [8]: df['id_3'] = df['id'].apply(lambda x: "{:.2f}".format(x))

In [9]: df

  (4) 'name' 변수의 문자열을 전부 대문자로 바꾼 새로운 변수 'name_3' 만들기

In [10]: df['name_3'] = df['name'].apply(lambda x: x.upper())

In [11]: df

  (5) 데이터프레임 안의 'id_3'와 'name_3' 변수를 합쳐서 새로운 변수 'id_name_3' 만들기

In [14]: df['id_name_3'] = df[['id_3', 'name_3']].apply(lambda x: ':'.join(x), axis=1)

In [15]: df

import pandas as pd

from pandas import DataFrame

df = DataFrame({'name': ['kim', 'KIM', 'Kim', 'lee', 'LEE', 'Lee', 'wang', 'hong'], 

                'value': [1, 2, 3, 4, 5, 6, 7, 8], 

                'value_2': [100, 300, 200, 100, 100, 300, 50, 80]

               })

df

  (1) 범주형 변수의 항목 매핑/변환에 사용할 기준 정보를 dict 자료형으로 만들기

name_mapping = {

    'KIM': 'kim',

    'Kim': 'kim', 

    'LEE': 'lee', 

    'Lee': 'lee', 

    'wang': 'others', 

    'hong': 'others'

}

name_mapping

 {'KIM': 'kim',

 'Kim': 'kim',
 'LEE': 'lee',
 'Lee': 'lee',
 'hong': 'others',
 'wang': 'others'}

  (2) dict.get() 함수를 이용하여 매핑/변환에 사용할 사용자 정의 함수 만들기

func = lambda x: name_mapping.get(x, x)

  (3) map() 함수로 매핑용 사용자 정의 함수를 DataFrame의 범주형 변수에 적용하여 매핑/변환하기

df['name_2'] = df.name.map(func)

df

  (4) groupby() 로 범주형 변수의 그룹별로 집계하기

# aggregation by name

df.groupby('name_2').sum()

df.groupby(['name_2', 'name'])['value_2'].sum()

name_2  name
kim     KIM     300
        Kim     200
        kim     100
lee     LEE     100
        Lee     300
        lee     100
others  hong     80
        wang     50
Name: value_2, dtype: int64

import numpy as np

import pandas as pd

abalone = pd.read_csv("/Users/ihongdon/Documents/Python/abalone.txt", 

                      sep=",", 

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

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

                               'shell_weight', 'rings'], 

                      header = None)

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

                                 'length_long', 

                                 'length_short')

abalone.head()

for sex, group_data in abalone[['sex', 'length_cat', 'whole_weight', 'rings']].groupby('sex'):

    print sex, 

    print group_data[:5]

F    sex    length_cat  whole_weight  rings

2    F  length_short        0.6770      9
6    F  length_short        0.7775     20
7    F  length_short        0.7680     16
9    F   length_long        0.8945     19
10   F  length_short        0.6065     14

I    sex    length_cat  whole_weight  rings
4    I  length_short        0.2050      7
5    I  length_short        0.3515      8
16   I  length_short        0.2905      7
21   I  length_short        0.2255     10
42   I  length_short        0.0700      5

M    sex    length_cat  whole_weight  rings
0    M  length_short        0.5140     15
1    M  length_short        0.2255      7
3    M  length_short        0.5160     10
8    M  length_short        0.5095      9
11   M  length_short        0.4060     10

for (sex, length_cat), group_data in abalone[['sex', 'length_cat', 'whole_weight', 'rings']]\

.groupby(['sex', 'length_cat']):

    print sex, length_cat

    print group_data[:5]

F length_long
   sex   length_cat  whole_weight  rings
9    F  length_long        0.8945     19
22   F  length_long        0.9395     12
23   F  length_long        0.7635      9
24   F  length_long        1.1615     10
25   F  length_long        0.9285     11

F length_short
   sex    length_cat  whole_weight  rings
2    F  length_short        0.6770      9
6    F  length_short        0.7775     20
7    F  length_short        0.7680     16
10   F  length_short        0.6065     14
13   F  length_short        0.6845     10

I length_long
    sex   length_cat  whole_weight  rings
509   I  length_long        0.8735     16
510   I  length_long        1.1095     10
549   I  length_long        0.8750     11
550   I  length_long        1.1625     17
551   I  length_long        0.9885     13

I length_short
   sex    length_cat  whole_weight  rings
4    I  length_short        0.2050      7
5    I  length_short        0.3515      8
16   I  length_short        0.2905      7
21   I  length_short        0.2255     10
42   I  length_short        0.0700      5

M length_long
   sex   length_cat  whole_weight  rings
27   M  length_long        0.9310     12
28   M  length_long        0.9365     15
29   M  length_long        0.8635     11
30   M  length_long        0.9975     10
32   M  length_long        1.3380     18

M length_short
   sex    length_cat  whole_weight  rings
0    M  length_short        0.5140     15
1    M  length_short        0.2255      7
3    M  length_short        0.5160     10
8    M  length_short        0.5095      9
11   M  length_short        0.4060     10

abalone_sex_group = dict(list(abalone[:10][['sex', 'length_cat', 'whole_weight', 'rings']]

                              .groupby('sex')))

abalone_sex_group

{'F':   sex    length_cat  whole_weight  rings
 2   F  length_short        0.6770      9
 6   F  length_short        0.7775     20
 7   F  length_short        0.7680     16
 9   F   length_long        0.8945     19,

 'I':   sex    length_cat  whole_weight  rings
 4   I  length_short        0.2050      7
 5   I  length_short        0.3515      8,

 'M':   sex    length_cat  whole_weight  rings
 0   M  length_short        0.5140     15
 1   M  length_short        0.2255      7
 3   M  length_short        0.5160     10
 8   M  length_short        0.5095      9}

abalone_sex_group['M'] 

[ 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

• Abalone CSV dataset download:  abalone.txt
• 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 pandas as pd

from pandas import DataFrame

from pandas import Series

import numpy as np

# Reading abalone data set

abalone = pd.read_csv("/Users/ihongdon/Documents/Python/abalone.txt", 

                      sep=",", 

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

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

                               'shell_weight', 'rings'], 

                      header = None)

# View of top 5 observations

abalone.head()

sex length diameter height whole_weight shucked_weight viscera_weight shell_weight rings

0 M 0.455 0.365 0.095 0.5140 0.2245 0.1010 0.150 15

1 M 0.350 0.265 0.090 0.2255 0.0995 0.0485 0.070 7

2 F 0.530 0.420 0.135 0.6770 0.2565 0.1415 0.210 9

3 M 0.440 0.365 0.125 0.5160 0.2155 0.1140 0.155 10

4 I 0.330 0.255 0.080 0.2050 0.0895 0.0395 0.055 7

# Check the missing value

np.sum(pd.isnull(abalone))

sex               0
length            0
diameter          0
height            0
whole_weight      0
shucked_weight    0
viscera_weight    0
shell_weight      0
rings             0
dtype: int64

# Descriptive statics

abalone.describe()

grouped = abalone['whole_weight'].groupby(abalone['sex'])

grouped 

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

grouped.size()

sex
F    1307
I    1342
M    1528
Name: whole_weight, dtype: int64

grouped.sum()

sex
F    1367.8175
I     578.8885
M    1514.9500
Name: whole_weight, dtype: float64

grouped.mean()

sex
F    1.046532
I    0.431363
M    0.991459
Name: whole_weight, dtype: float64

abalone.groupby(abalone['sex']).mean()