## groupby

import pandas as pd
import numpy as np
df = pd.DataFrame({'key1' : ['a', 'a', 'b', 'b', 'a'],
 'key2' : ['one', 'two', 'one', 'two', 'one'],
'data1' : np.random.randn(5),
'data2' : np.random.randn(5)})

df

grouped = df.data1.groupby(df.key1)

grouped

### mean

grouped.mean()

key1 a 0.942543 b 1.557667 Name: data1, dtype: float64 ### 多个key

means = df.data1.groupby([df.key1,df.key2]).mean()

means

key1 key2 a one 0.873787 two 1.080056 b one 0.464790 two 2.650545 Name: data1, dtype: float64 ### 行to列

means.unstack()

### 其他key

states = np.array(['Ohio','California','California','Ohio','Ohio'])
years = np.array([2005,2005,2006,2005,2006])

df.data1.groupby([states,years]).mean()

California 2005 1.080056 2006 0.464790 Ohio 2005 1.413680 2006 1.570758 Name: data1, dtype: float64 ### key类型不限制

df.groupby('key1').mean()

df.groupby(['key1','key2']).mean()

### size

df.groupby(['key1','key2']).size()

key1 key2 a one 2 two 1 b one 1 two 1 dtype: int64 ### 迭代

for name,group in df.groupby('key1'):
    print(name)
    print(group)

a data1 data2 key1 key2 0 0.176816 0.713241 a one 1 1.080056 0.110213 a two 4 1.570758 0.371698 a one b data1 data2 key1 key2 2 0.464790 -1.689117 b one 3 2.650545 -0.022297 b two ### 多个key

for (k1,k2),group in df.groupby(['key1','key2']):
    print((k1,k2))
    print(group)

(‘a’, ‘one’) data1 data2 key1 key2 0 0.176816 0.713241 a one 4 1.570758 0.371698 a one (‘a’, ‘two’) data1 data2 key1 key2 1 1.080056 0.110213 a two (‘b’, ‘one’) data1 data2 key1 key2 2 0.46479 -1.689117 b one (‘b’, ‘two’) data1 data2 key1 key2 3 2.650545 -0.022297 b two ### dict

pieces = dict(list(df.groupby('key1')))

pieces['b']

df.dtypes

data1 float64 data2 float64 key1 object key2 object dtype: object ### 轴

grouped = df.groupby(df.dtypes,axis=1)

for dtype,group in grouped:
    print(dtype)
    print(group)

float64 data1 data2 0 0.176816 0.713241 1 1.080056 0.110213 2 0.464790 -1.689117 3 2.650545 -0.022297 4 1.570758 0.371698 object key1 key2 0 a one 1 a two 2 b one 3 b two 4 a one ### 列

df.groupby('key1')['data1']

df.groupby('key1')[['data2']]

df['data1'].groupby(df['key1'])

df[['data2']].groupby(df['key1'])

df.groupby(['key1','key2'])[['data2']].mean()

s_grouped = df.groupby(['key1','key2'])['data2']

s_grouped

s_grouped.mean()

key1 key2 a one 0.542469 two 0.110213 b one -1.689117 two -0.022297 Name: data2, dtype: float64 ### dict和series

 people = pd.DataFrame(np.random.randn(5, 5), columns=['a', 'b', 'c', 'd', 'e'],index=['Joe', 'Steve', 'Wes', 'Jim', 'Travis'])

people.iloc[2:3,[1,2]] = np.nan

people

#### map

 mapping = {'a': 'red', 'b': 'red', 'c': 'blue', 'd': 'blue', 'e': 'red', 'f' : 'orange'}

by_column = people.groupby(mapping,axis=1)

by_column.sum()

#### series

mao_series = pd.Series(mapping)

mao_series

a red b red c blue d blue e red f orange dtype: object

people.groupby(mao_series,axis=1).count()

### 函数

people.groupby(len).sum()

key_list = ['one','one','one','two','two']

people.groupby([len,key_list]).min()

### 行索引

 columns = pd.MultiIndex.from_arrays([['US', 'US', 'US', 'JP', 'JP'], [1, 3, 5, 1, 3]],names=['cty', 'tenor'])

 hier_df = pd.DataFrame(np.random.randn(4, 5), columns=columns)

hier_df

hier_df.groupby(level = 'cty',axis=1).count()

## Aggregation groupby methods: Function name Description count Number of non-NA values in the group sum Sum of non-NA values mean Mean of non-NA values median Arithmetic median of non-NA values std, var Unbiased (n – 1 denominator) standard deviation and variance min, max Minimum and maximum of non-NA values prod Product of non-NA values first, last First and last non-NA values

df

grouped = df.groupby('key1')

grouped.data1.quantile(0.9)

key1 a 1.472618 b 2.431969 Name: data1, dtype: float64

def peak_to_peak(arr):
    return arr.max() - arr.min()

grouped.agg(peak_to_peak)

grouped.describe()

### 复杂函数应用

tips = pd.read_csv('/Users/meininghang/Downloads/pydata-book-2nd-edition/examples/tips.csv')

tips['tip_pct'] = tips.tip / tips.total_bill

tips[:6]

grouped = tips.groupby(['day','smoker'])

grouped_pct = grouped['tip_pct']

grouped_pct.agg('mean')

day smoker Fri No 0.151650 Yes 0.174783 Sat No 0.158048 Yes 0.147906 Sun No 0.160113 Yes 0.187250 Thur No 0.160298 Yes 0.163863 Name: tip_pct, dtype: float64

grouped_pct.agg(['mean','std',peak_to_peak])

grouped_pct.agg([('foo','mean'),('bar',np.std)])

functions = ['count','mean','max']

result = grouped['tip_pct','total_bill'].agg(functions)

result

result['tip_pct']

ftuples = [('Durchschnitt','mean',),('Abweichung',np.var)]

grouped.agg({'tip':np.max,'size':'sum'})

grouped.agg({'tip_pct':['min','max','mean','std'],'size':'sum'})

### 原始索引

tips.groupby(['day','smoker'],as_index=False).mean()

## split-apply-combine

def top(df, n=5, column='tip_pct'):
   ....:     return df.sort_values(by=column)[-n:]

top(tips,n=6)

tips.groupby('smoker').apply(top)

tips

tips.groupby(['smoker','day']).apply(top,n=1,column = 'total_bill')

result = tips.groupby('smoker')['tip_pct'].describe()

result

result.unstack('smoker')

smoker count No 151.000000 Yes 93.000000 mean No 0.159328 Yes 0.163196 std No 0.039910 Yes 0.085119 min No 0.056797 Yes 0.035638 25% No 0.136906 Yes 0.106771 50% No 0.155625 Yes 0.153846 75% No 0.185014 Yes 0.195059 max No 0.291990 Yes 0.710345 dtype: float64 #### lambda

f = lambda x:x.describe()
grouped.apply(f)

### 抑制关键字

tips.groupby('smoker',group_keys = False).apply(top)

### 填充缺失值

s = pd.Series(np.random.randn(6))

s[::2] = np.nan

s

0 NaN 1 -0.975734 2 NaN 3 -0.359448 4 NaN 5 1.577221 dtype: float64

s.fillna(s.mean())

0 0.080680 1 -0.975734 2 0.080680 3 -0.359448 4 0.080680 5 1.577221 dtype: float64

states = ['Ohio', 'New York', 'Vermont', 'Florida',

          'Oregon', 'Nevada', 'California', 'Idaho']

group_key = ['East'] * 4 + ['West'] * 4

data = pd.Series(np.random.randn(8),index = states)

data

Ohio -0.017642 New York -0.655295 Vermont 0.469594 Florida -0.161041 Oregon 0.795937 Nevada 1.144693 California -1.096555 Idaho 0.166461 dtype: float64

data[['Vermont','Nevada','Idaho']] = np.nan

data

Ohio -0.017642 New York -0.655295 Vermont NaN Florida -0.161041 Oregon 0.795937 Nevada NaN California -1.096555 Idaho NaN dtype: float64

data.groupby(group_key).mean()

East -0.277993 West -0.150309 dtype: float64

fill_mean = lambda g: g.fillna(g.mean())

data.groupby(group_key).apply(fill_mean)

Ohio -0.017642 New York -0.655295 Vermont -0.277993 Florida -0.161041 Oregon 0.795937 Nevada -0.150309 California -1.096555 Idaho -0.150309 dtype: float64 ## 透视表

tips.pivot_table(index = ['day','smoker'])

tips.pivot_table(['tip_pct','size'],index=['time','day'],columns = 'smoker')

tips.pivot_table(['tip_pct','size'],index=['time','day'],columns='smoker',margins=True)

tips.pivot_table('tip_pct',index=['time','smoker'],columns='day',aggfunc=len,margins=True)

tips.pivot_table('tip_pct',index=['time','smoker'],columns='day',aggfunc='mean',fill_value=0,margins=True)

method:
Function name Description
values Column name or names to aggregate; by default aggregates all numeric columns
index Column names or other group keys to group on the rows of the resulting pivot table
columns Column names or other group keys to group on the columns of the resulting pivot table
aggfunc Aggregation function or list of functions (‘mean’ by default); can be any function valid in a groupby context
fill_value Replace missing values in result table
dropna If True, do not include columns whose entries are all NA
margins Add row/column subtotals and grand total (False by default)

#### crosstab

pd.crosstab([tips.time,tips.day],tips.smoker,margins=True)