《利用Python 进行数据分析》第九章：数据聚合与分组运算

深碍√TFBOYSˉ_ 2022-12-10 10:07 310阅读 0赞

对《利用Python 进行数据分析》（Wes Mckinney著）一书中的第九章中数据聚合与分组运算进行代码实验。原书中采用的是Python2.7，而我采用的Python3.7在Pycharm调试的，因此对书中源代码进行了一定的修改，每步结果与原文校验对照一致（除了随机函数外；输出结果在注释中，简单的输出就没写结果），全手工敲写，供参考。

Pdf文档和数据集参见：[《利用Python 进行数据分析》第二章：引言中的分析代码（含pdf和数据集下载链接）][Python _pdf]

###    数据聚合与分组运算： ###

*  1、GroupBy技术
 *   *  1.1 分组入门示例
     *  1.2 对分组进行迭代
     *  1.3 选取一个或一组列
     *  1.4 通过字典或Series进行分组
     *  1.5 通过函数进行分组
     *  1.6 根据索引级别分组
 *  2、数据聚合
 *   *  2.1 基本数据聚合
     *  2.1 面向列的多函数应用
     *  2.2 以“无索引”的形式返回聚合数据
 *  3、分组级运算和转换
 *   *  3.1 apply：一般性的“拆分-应用-合并”
     *  3.2 分位数和桶分析
     *  3.3 示例：用特定于分组的值填充缺失值
     *  3.4 示例：随机采样和排列
     *  3.5 示例：分组加权平均数和相关系数
     *  3.6 示例：面向分组的现型回归
 *  4、透视表和交叉表
 *   *  4.1 透视表
     *  4.2 交叉表：crosstab

因为代码过长，放在一个代码段中显得冗长，因此进行了拆分，如下的库引入每个代码段中均可能有必要。

# -*- coding:utf-8 -*-
    
    from pandas import Series, DataFrame
    import pandas as pd
    import numpy as np

# 1、GroupBy技术 #

## 1.1 分组入门示例 ##

df = DataFrame({ 'key1':['a','a','b','b','a'],
                    'key2':['one','two', 'one','two','one'],
                    'data1':np.random.randn(5),
                    'data2':np.random.randn(5)})
    print(df)
    ''' key1 key2 data1 data2 0 a one 0.480143 1.463562 1 a two -1.329694 -0.866557 2 b one 0.457699 -1.283965 3 b two -0.067646 -0.959851 4 a one -0.259382 -0.460503 '''
    # 假设想按key1分组，并计算data1的平均值
    grouped = df['data1'].groupby(df['key1'])
    print(grouped) # <pandas.core.groupby.generic.SeriesGroupBy object at 0x0000020247557648>
    
    # grouped是一个GroupBy对象，没有进行任何计算，只是含有一些有关分组键df['key1']的中间数而已
    # 调用mean()方法即可按分组求取平均值
    print(grouped.mean())
    ''' key1 a -0.369644 b 0.195027 Name: data1, dtype: float64 '''
    
    # 如果一次传入多个数组，就会得到不同的结果
    means = df['data1'].groupby([df['key1'],df['key2']]).mean()
    print(means)
    ''' key1 key2 a one 0.110380 two -1.329694 b one 0.457699 two -0.067646 Name: data1, dtype: float64 '''
    
    # 通过对两个键对数据进行分组，得到的Series具有一个层次化索引（由唯一键组成）
    print(means.unstack())
    ''' key2 one two key1 a 0.110380 -1.329694 b 0.457699 -0.067646 '''
    
    states = np.array(['Ohio','California', 'California', 'Ohio', 'Ohio'])
    years = np.array([2005,2005,2006,2005,2006])
    # 上述分组键均为Series,实际上，分组键可以是任何长度适当的数组
    print(df['data1'].groupby([states,years]).mean())
    ''' California 2005 -1.329694 2006 0.457699 Ohio 2005 0.206248 2006 -0.259382 Name: data1, dtype: float64 '''
    
    # 还可以将列名（可以是字符串、数字或其他Python对象）用作分组键
    print(df.groupby('key1').mean())
    ''' data1 data2 key1 a -0.369644 0.045501 b 0.195027 -1.121908 '''
    
    print(df.groupby(['key1','key2']).mean())
    ''' data1 data2 key1 key2 a one 0.110380 0.501530 two -1.329694 -0.866557 b one 0.457699 -1.283965 two -0.067646 -0.959851 '''
    # 无论拿groupby做什么，都有可能用到Groupby的size方法，它可以返回一个含有分组大小的Series
    print(df.groupby(['key1','key2']).size())
    ''' a one 2 two 1 b one 1 two 1 dtype: int64 '''

## 1.2 对分组进行迭代 ##

df = DataFrame({ 'key1':['a','a','b','b','a'],
                    'key2':['one','two', 'one','two','one'],
                    'data1':np.random.randn(5),
                    'data2':np.random.randn(5)})
    print(df)
    ''' key1 key2 data1 data2 0 a one -0.947119 -0.535459 1 a two -0.512065 -0.950734 2 b one -0.025885 -0.357145 3 b two -1.301764 1.014542 4 a one -0.066668 -1.278276 '''
    # GroupBy对象支持迭代，可以产生一组二元元组（由分组名和数据块组成）
    for name, grouped in df.groupby('key1'):
        print(name)
        print(grouped)
    ''' a key1 key2 data1 data2 0 a one -0.947119 -0.535459 1 a two -0.512065 -0.950734 4 a one -0.066668 -1.278276 b key1 key2 data1 data2 2 b one -0.025885 -0.357145 3 b two -1.301764 1.014542 '''
    
    # 对于多重复键的情况，元组的第一个元素将会是由键值组成的元组
    for (k1, k2),group in df.groupby(['key1', 'key2']):
        print(k1, k2)
        print(group)
    ''' a one key1 key2 data1 data2 0 a one -0.947119 -0.535459 4 a one -0.066668 -1.278276 a two key1 key2 data1 data2 1 a two -0.512065 -0.950734 b one key1 key2 data1 data2 2 b one -0.025885 -0.357145 b two key1 key2 data1 data2 3 b two -1.301764 1.014542 '''
    
    # 也可以对这些数据片段做操作，例如将这些数据片段做成一个字典
    pieces = dict(list(df.groupby('key1')))
    print(pieces['b'])
    ''' key1 key2 data1 data2 2 b one -0.025885 -0.357145 3 b two -1.301764 1.014542 '''
    
    # GroupBy默认是axis=0进行分组，通过设置可以在其他任何轴上分组
    # 例如可以根据dtype对列进行分组(我的理解，即将数据按数据类型对字段分组)
    print(df.dtypes)
    ''' key1 object key2 object data1 float64 data2 float64 dtype: object '''
    grouped = df.groupby(df.dtypes, axis=1)
    print(dict(list(grouped)))
    ''' {dtype('float64'): data1 data2 0 -0.947119 -0.535459 1 -0.512065 -0.950734 2 -0.025885 -0.357145 3 -1.301764 1.014542 4 -0.066668 -1.278276, dtype('O'): key1 key2 0 a one 1 a two 2 b one 3 b two 4 a one} '''

## 1.3 选取一个或一组列 ##

# 对于DataFrame产生的GroupBy对象，如果用一个（单个字符串）或一组（字符串数组）列名对其进行索引，就可以实现选取部分列进行聚合目的
    ''' df.groupby('key1')['data1'] df.groupby('key1')['data2'] 是以下下代码的语法糖 df['data1'].groupby(df['key1']) df['data2'].groupby(df['key1']) '''
    df = DataFrame({ 'key1':['a','a','b','b','a'],
                    'key2':['one','two', 'one','two','one'],
                    'data1':np.random.randn(5),
                    'data2':np.random.randn(5)})
    print(df)
    ''' key1 key2 data1 data2 0 a one -0.558967 0.489646 1 a two -0.031497 -0.034653 2 b one -0.217540 -0.190346 3 b two -1.150574 0.062234 4 a one -1.718567 -0.265710 '''
    
    # 对于大数据，很可能只需对部分列进行聚合如，返回的是以分组的DataFrame（如果传入的是列表或数组）
    print(df.groupby(['key1','key2'])[['data2']].mean())
    ''' data2 key1 key2 a one 0.111968 two -0.034653 b one -0.190346 two 0.062234 '''
    
    # 如果传入的是标量形式的单个列名，则返回的已分组的Series
    s_grouped =df.groupby(['key1','key2'])['data2'] # 与 print(df['data2'].groupby([df['key1'],df['key2']]).mean())结果一样
    print(s_grouped) # <pandas.core.groupby.generic.SeriesGroupBy object at 0x0000024D38E0B448>
    print(s_grouped.mean())
    ''' key1 key2 a one 0.111968 two -0.034653 b one -0.190346 two 0.062234 Name: data2, dtype: float64 '''

## 1.4 通过字典或Series进行分组 ##

# 除了分组以外，分组信息还可以存在其他形式存在
    people = DataFrame(np.random.randn(5,5),
                       columns=['a','b','c','d','e'],
                       index=['Joe', 'Steve', 'Wes', 'Jim', 'Travis'])
    people.loc[2:3,['b','c']] = np.nan
    print(people)
    ''' a b c d e Joe -1.938347 1.045929 -0.381666 -1.864672 -0.025615 Steve 1.795533 1.517467 0.082680 -0.564577 -0.692434 Wes -0.693408 NaN NaN -0.762670 -1.188578 Jim 0.617972 -2.403982 0.644797 1.571813 1.807247 Travis -1.066016 0.590022 -0.735421 -0.851050 0.001561 '''
    
    # 假设已知列的分组关系，并希望根据分组计算列的总计
    mapping={ 'a':'red','b':'red','c':'blue',
             'd':'blue','e':'red','f':'orange'}
    by_column = people.groupby(mapping, axis=1)
    print(by_column.sum())
    ''' blue red Joe -2.246337 -0.918033 Steve -0.481897 2.620566 Wes -0.762670 -1.881986 Jim 2.216610 0.021238 Travis -1.586472 -0.474434 '''
    
    # Series也有同样的功能，如果用Series作为分组键，则pandas会检查Series以确保其索引跟分组轴是对齐的
    map_series = Series(mapping)
    print(map_series)
    ''' a red b red c blue d blue e red f orange dtype: object '''
    print(people.groupby(map_series, axis=1).count())
    ''' blue red Joe 2 3 Steve 2 3 Wes 1 2 Jim 2 3 Travis 2 3 '''

## 1.5 通过函数进行分组 ##

# 如果希望根据人名的长度进行分组，虽然可以求取一个字符串长度数组，但其实传入len函数就可以
    print(people.groupby(len).sum())  # people数据来自1.4小节
    ''' a b c d e 3 -0.034264 -0.432738 1.155553 1.190769 -0.013697 5 -0.066825 0.428833 -1.194401 0.188680 -0.644329 6 0.566323 1.229906 0.089051 0.503252 -1.098167 '''
    # 将函数和数组、列表、字典、Series混合使用也可以，因为任何东西都被转化为数组
    key_list = ['one', 'one', 'one', 'two', 'two']
    print(people.groupby([len, key_list]).min())
    ''' a b c d e 3 one -1.303887 -0.906267 1.027824 0.157696 0.049667 two 0.969103 0.473529 0.127729 -0.142159 -0.530606 5 one -0.066825 0.428833 -1.194401 0.188680 -0.644329 6 two 0.566323 1.229906 0.089051 0.503252 -1.098167 '''

## 1.6 根据索引级别分组 ##

# 层次化索引数据最方便的地方在于它可以根据索引级别进行聚合，通过level关键字传入级别编号或名称即可
    columns = pd.MultiIndex.from_arrays([['US','US','US', 'JP','JP'],
                                         [1,3,5,1,3]],names = ['cty', 'tenor'])
    hier_df = DataFrame(np.random.randn(4,5), columns=columns)
    print(hier_df)
    ''' cty US JP tenor 1 3 5 1 3 0 -1.070243 1.068501 0.989753 0.270197 -0.800152 1 0.849513 -1.150283 1.394608 -1.271483 1.923552 2 0.647856 -0.021692 -0.334504 0.708273 -1.436954 3 -0.985744 1.616744 0.637920 0.315748 0.165119 '''
    print(hier_df.groupby(level='cty',axis=1).count())
    ''' cty JP US 0 2 3 1 2 3 2 2 3 3 2 3 '''
    print(hier_df.groupby(level='cty',axis=1).max()) # 对每列中，求每行的最大值，跟内层索引无关
    ''' cty JP US 0 0.270197 1.068501 1 1.923552 1.394608 2 0.708273 0.647856 3 0.315748 1.616744 '''

# 2、数据聚合 #

## 2.1 基本数据聚合 ##

df = DataFrame({ 'key1':['a','a','b','b','a'],
                    'key2':['one','two', 'one','two','one'],
                    'data1':np.random.randn(5),
                    'data2':np.random.randn(5)})
    print(df)
    ''' key1 key2 data1 data2 0 a one 1.619465 0.240445 1 a two -0.064894 0.442789 2 b one 0.478711 0.580644 3 b two -0.721379 -0.132166 4 a one -2.797641 -0.572633 '''
    grouped = df.groupby('key1')
    print(grouped['data1'].quantile(0.9))
    ''' key1 a 1.282593 b 0.358702 Name: data1, dtype: float64 '''
    
    # GroupBy会高效地对Series进行切片，然后对各片调用piece
    def peak_to_peak(arr):
        return arr.max() - arr.min()
    # 如果使用自己的聚合函数，只需将其传入aggregate或agg方法即可
    print(grouped.agg(peak_to_peak))
    ''' data1 data2 key1 a 4.417106 1.015422 b 1.200090 0.712809 '''

## 2.1 面向列的多函数应用 ##

# 以餐馆消费的数据集为例，说明一些更高级的聚合功能
    tips =pd.read_csv('python_data/ch08/tips.csv')
    # 添加“消费占总额百分比”一列
    tips['tip_pct'] = tips['tip'] / tips['total_bill']
    print(tips[:6])
    ''' total_bill tip sex smoker day time size tip_pct 0 16.99 1.01 Female No Sun Dinner 2 0.059447 1 10.34 1.66 Male No Sun Dinner 3 0.160542 2 21.01 3.50 Male No Sun Dinner 3 0.166587 3 23.68 3.31 Male No Sun Dinner 2 0.139780 4 24.59 3.61 Female No Sun Dinner 4 0.146808 5 25.29 4.71 Male No Sun Dinner 4 0.186240 '''
    grouped = tips.groupby(['sex','smoker'])
    grouped_pct = grouped['tip_pct']
    # 对于描述统计，可以将函数名以字符串的形式传入，与后面两个写法的效果一样
    print(grouped_pct.agg('mean'))
    ''' sex smoker Female No 0.156921 Yes 0.182150 Male No 0.160669 Yes 0.152771 Name: tip_pct, dtype: float64 '''
    print(tips.groupby(['sex','smoker'])['tip_pct'].mean())
    ''' sex smoker Female No 0.156921 Yes 0.182150 Male No 0.160669 Yes 0.152771 Name: tip_pct, dtype: float64 '''
    print(tips['tip_pct'].groupby([tips['sex'], tips['smoker']]).mean())
    ''' sex smoker Female No 0.156921 Yes 0.182150 Male No 0.160669 Yes 0.152771 Name: tip_pct, dtype: float64 '''
    # 如果传入一组函数或函数名，则得到的DataFrame列会议相应的函数冲击
    print(grouped_pct.agg(['mean','std', peak_to_peak]))
    ''' mean std peak_to_peak sex smoker Female No 0.156921 0.036421 0.195876 Yes 0.182150 0.071595 0.360233 Male No 0.160669 0.041849 0.220186 Yes 0.152771 0.090588 0.674707 '''
    
    # 如果传入的是一个由元组组成的列表，则各元素的第一个元素就会被作用于DataFrame的列名
    # 即二元元组列表可以看作为一个有序映射
    print(grouped_pct.agg([('foo','mean'),('bar', np.std)]))
    ''' foo bar sex smoker Female No 0.156921 0.036421 Yes 0.182150 0.071595 Male No 0.160669 0.041849 Yes 0.152771 0.090588 '''
    
    # 对于DataFrame，还可以定义应用于全部列的函数，或不同的列应用不同的函数
    functions = ['count', 'mean', 'max']
    # 相当于分别对各列进行聚合，然后用concat将结果组装到一起
    result = grouped['tip_pct','total_bill'].agg(functions)
    print(result)
    ''' tip_pct total_bill count mean max count mean max sex smoker Female No 54 0.156921 0.252672 54 18.105185 35.83 Yes 33 0.182150 0.416667 33 17.977879 44.30 Male No 97 0.160669 0.291990 97 19.791237 48.33 Yes 60 0.152771 0.710345 60 22.284500 50.81 '''
    print(result['tip_pct'])
    ''' count mean max sex smoker Female No 54 0.156921 0.252672 Yes 33 0.182150 0.416667 Male No 97 0.160669 0.291990 Yes 60 0.152771 0.710345 '''
    
    # 也可以传入带有自己义名称的元组列表
    ftuples = [('Durchschnitt','mean'),('Abweichung',np.var)]
    print(grouped['tip_pct','total_bill'].agg(ftuples))
    ''' tip_pct total_bill Durchschnitt Abweichung Durchschnitt Abweichung sex smoker Female No 0.156921 0.001327 18.105185 53.092422 Yes 0.182150 0.005126 17.977879 84.451517 Male No 0.160669 0.001751 19.791237 76.152961 Yes 0.152771 0.008206 22.284500 98.244673 '''
    
    # 假设对不同列应用不同函数，可以向agg传入一个从列名映射到函数的字典
    print(grouped.agg({ 'tip':np.max, 'size':'sum'}))
    ''' tip size sex smoker Female No 5.2 140 Yes 6.5 74 Male No 9.0 263 Yes 10.0 150 '''
    
    print(grouped.agg({ 'tip_pct':['min','max','mean','std'],
                       'size':'sum'}))
    ''' tip_pct size min max mean std sum sex smoker Female No 0.056797 0.252672 0.156921 0.036421 140 Yes 0.056433 0.416667 0.182150 0.071595 74 Male No 0.071804 0.291990 0.160669 0.041849 263 Yes 0.035638 0.710345 0.152771 0.090588 150 '''

## 2.2 以“无索引”的形式返回聚合数据 ##

# 向groupby传入as_index=False禁用“聚合数据都有唯一的分组键组成的索引”
    print(tips.groupby(['sex','smoker'], as_index = False).mean())
    ''' sex smoker total_bill tip size tip_pct 0 Female No 18.105185 2.773519 2.592593 0.156921 1 Female Yes 17.977879 2.931515 2.242424 0.182150 2 Male No 19.791237 3.113402 2.711340 0.160669 3 Male Yes 22.284500 3.051167 2.500000 0.152771 '''
    # 对比不加的效果
    ''' total_bill tip size tip_pct sex smoker Female No 18.105185 2.773519 2.592593 0.156921 Yes 17.977879 2.931515 2.242424 0.182150 Male No 19.791237 3.113402 2.711340 0.160669 Yes 22.284500 3.051167 2.500000 0.152771 '''

# 3、分组级运算和转换 #

df = DataFrame({ 'key1':['a','a','b','b','a'],
                    'key2':['one','two', 'one','two','one'],
                    'data1':np.random.randn(5),
                    'data2':np.random.randn(5)})
    print(df)
    # 为DataFrame添加一个存放各索引分组平均值的列，可以先聚合再合并
    k1_means = df.groupby('key1').mean().add_prefix('mean_')
    print(k1_means)
    df_merge = pd.merge(df, k1_means, left_on='key1',right_index=True)
    print(df_merge)
    
    people = DataFrame(np.random.randn(5,5),
                       columns=['a','b','c','d','e'],
                       index=['Joe', 'Steve', 'Wes', 'Jim', 'Travis'])
    people.loc[2:3,['b','c']] = np.nan
    print(people)
    ''' a b c d e Joe -0.191788 -1.151933 0.831241 0.298819 -0.542925 Steve -0.211227 -0.408024 0.915287 1.417264 0.436030 Wes -0.878840 NaN NaN 1.049762 -0.539757 Jim -2.110415 -0.415388 1.217989 -0.755579 1.960404 Travis 0.088417 -0.639453 0.919854 1.900576 -1.834850 '''
    key = ['one','two','one','two','one']
    print(people.groupby(key).transform(np.mean)) # 1,3,5行均值相同，2,4行均值相同
    ''' a b c d e Joe -0.327404 -0.895693 0.875548 1.083052 -0.972510 Steve -1.160821 -0.411706 1.066638 0.330843 1.198217 Wes -0.327404 -0.895693 0.875548 1.083052 -0.972510 Jim -1.160821 -0.411706 1.066638 0.330843 1.198217 Travis -0.327404 -0.895693 0.875548 1.083052 -0.972510 '''
    # transform将函数应用到各个分组，然后将结果放到适当位置
    def demean(arr):
        return arr - arr.mean()
    demeaned = people.groupby(key).transform(demean)
    
    # # 检查demeaned的分组平均值是否为0
    print(demeaned.groupby(key).mean())
    ''' a b c d e one 1.850372e-17 0.000000e+00 5.551115e-17 0.0 -1.480297e-16 two 0.000000e+00 -2.775558e-17 5.551115e-17 0.0 0.000000e+00 '''

## 3.1 apply：一般性的“拆分-应用-合并” ##

tips = pd.read_csv('python_data/ch08/tips.csv')
    # 添加“消费占总额百分比”一列
    tips['tip_pct'] = tips['tip'] / tips['total_bill']
    
    # 编写一个选取指定列具有最大值的行的函数
    def top(df,n=5,column='tip_pct'):
        return df.sort_values(by=column)[-n:]
    
    print(top(tips,n=6))
    ''' total_bill tip sex smoker day time size tip_pct 109 14.31 4.00 Female Yes Sat Dinner 2 0.279525 183 23.17 6.50 Male Yes Sun Dinner 4 0.280535 232 11.61 3.39 Male No Sat Dinner 2 0.291990 67 3.07 1.00 Female Yes Sat Dinner 1 0.325733 178 9.60 4.00 Female Yes Sun Dinner 2 0.416667 172 7.25 5.15 Male Yes Sun Dinner 2 0.710345 '''
    print(tips.groupby('smoker').apply(top))
    ''' total_bill tip sex smoker day time size tip_pct smoker No 88 24.71 5.85 Male No Thur Lunch 2 0.236746 185 20.69 5.00 Male No Sun Dinner 5 0.241663 51 10.29 2.60 Female No Sun Dinner 2 0.252672 149 7.51 2.00 Male No Thur Lunch 2 0.266312 232 11.61 3.39 Male No Sat Dinner 2 0.291990 Yes 109 14.31 4.00 Female Yes Sat Dinner 2 0.279525 183 23.17 6.50 Male Yes Sun Dinner 4 0.280535 67 3.07 1.00 Female Yes Sat Dinner 1 0.325733 178 9.60 4.00 Female Yes Sun Dinner 2 0.416667 172 7.25 5.15 Male Yes Sun Dinner 2 0.710345 '''
    print(tips.groupby(['smoker','day']).apply(top, n = 1, column='total_bill'))
    ''' total_bill tip sex smoker day time size tip_pct smoker day No Fri 94 22.75 3.25 Female No Fri Dinner 2 0.142857 Sat 212 48.33 9.00 Male No Sat Dinner 4 0.186220 Sun 156 48.17 5.00 Male No Sun Dinner 6 0.103799 Thur 142 41.19 5.00 Male No Thur Lunch 5 0.121389 Yes Fri 95 40.17 4.73 Male Yes Fri Dinner 4 0.117750 Sat 170 50.81 10.00 Male Yes Sat Dinner 3 0.196812 Sun 182 45.35 3.50 Male Yes Sun Dinner 3 0.077178 Thur 197 43.11 5.00 Female Yes Thur Lunch 4 0.115982 '''
    
    # 再看describe()方法
    result = tips.groupby('smoker')['tip_pct'].describe()
    print(result)
    ''' count mean std ... 50% 75% max smoker ... No 151.0 0.159328 0.039910 ... 0.155625 0.185014 0.291990 Yes 93.0 0.163196 0.085119 ... 0.153846 0.195059 0.710345 '''
    # 在GroupBy中，当你调用诸如describe之类的方法时，实际上调用了如下两条代码的快捷方法:
    f = lambda x:x.describe()
    grouped.apply(f)
    
    # 禁止分组键：上面的例子分组键会跟原始对象的索引共同构成结果对象中的层次化索引
    # 将group_keys = False传入即可禁止该效果
    print(tips.groupby('smoker',group_keys=False).apply(top))
    ''' total_bill tip sex smoker day time size tip_pct 88 24.71 5.85 Male No Thur Lunch 2 0.236746 185 20.69 5.00 Male No Sun Dinner 5 0.241663 51 10.29 2.60 Female No Sun Dinner 2 0.252672 149 7.51 2.00 Male No Thur Lunch 2 0.266312 232 11.61 3.39 Male No Sat Dinner 2 0.291990 109 14.31 4.00 Female Yes Sat Dinner 2 0.279525 183 23.17 6.50 Male Yes Sun Dinner 4 0.280535 67 3.07 1.00 Female Yes Sat Dinner 1 0.325733 178 9.60 4.00 Female Yes Sun Dinner 2 0.416667 172 7.25 5.15 Male Yes Sun Dinner 2 0.710345 '''

## 3.2 分位数和桶分析 ##

# 将pandas中的根据指定面元或样本分位数将数据拆分成多块的工具（cut和qcut）结合groupy就可对数据集进行桶或分位数分析
    frame = DataFrame({ 'data1':np.random.randn(1000),
                       'data2':np.random.rand(1000)})
    factor = pd.cut(frame.data1, 4)
    print(factor[:10])
    ''' 0 (-3.214, -1.656] 1 (-1.656, -0.103] 2 (-1.656, -0.103] 3 (-0.103, 1.449] 4 (-1.656, -0.103] 5 (-0.103, 1.449] 6 (-1.656, -0.103] 7 (-0.103, 1.449] 8 (-0.103, 1.449] 9 (-0.103, 1.449] Name: data1, dtype: category Categories (4, interval[float64]): [(-3.214, -1.656] < (-1.656, -0.103] < (-0.103, 1.449] < (1.449, 3.001]] '''
    
    # 由cut返回的Factor对象可以直接用于groupby
    def get_status(group):
        return{ 'min':group.min(), 'max':group.max(),
               'count':group.count(), 'mean':group.mean()}
    
    grouped = frame.data2.groupby(factor)
    print(grouped.apply(get_status).unstack())
    ''' min max count mean data1 (-3.214, -1.656] 0.009857 0.995564 59.0 0.487598 (-1.656, -0.103] 0.006060 0.999923 405.0 0.514107 (-0.103, 1.449] 0.002710 0.999007 460.0 0.495123 (1.449, 3.001] 0.008487 0.994226 76.0 0.540540 '''
    
    # 以上是长度相同的桶，要根据样本分位数得到大小相等的桶，使用qcut即可
    # 传入labels=False即可获取分位数的编号
    grouping = pd.qcut(frame.data1, 10, labels = False)
    grouped = frame.data2.groupby(grouping)
    print(grouped.apply(get_status).unstack())
    ''' min max count mean data1 0 0.009857 0.995564 100.0 0.490709 1 0.010508 0.991483 100.0 0.541817 2 0.019523 0.999923 100.0 0.503681 3 0.006060 0.999240 100.0 0.509751 4 0.009489 0.994014 100.0 0.486793 5 0.005966 0.993424 100.0 0.483321 6 0.014450 0.995279 100.0 0.459698 7 0.005576 0.999007 100.0 0.528312 8 0.025848 0.975744 100.0 0.497035 9 0.002710 0.994226 100.0 0.557073 '''

## 3.3 示例：用特定于分组的值填充缺失值 ##

# 对于缺失数据，用一个固定值或由数据本身所产生的值去填充NA值
    s =Series(np.random.randn(6))
    s[::2]=np.nan
    print(s)
    ''' 0 NaN 1 1.326207 2 NaN 3 0.228645 4 NaN 5 0.829395 dtype: float64 '''
    # 用均值填充缺失值
    s_filled = s.fillna(s.mean())
    print(s_filled)
    ''' 0 0.794749 1 1.326207 2 0.794749 3 0.228645 4 0.794749 5 0.829395 dtype: float64 '''
    
    # 如果对不同的分组填充不同的值，将数据分组，并使用apply和一个能对各数据块调用fillna的函数即可
    states = ['Ohio', 'NewYork', 'Vermont','Florida',
              'Oregon', 'Nevada', 'California', 'Idaho']
    group_key = ['East'] * 4 + ['West'] * 4
    data = Series(np.random.randn(8), index=states)
    data[['Vermont','Nevada','Idaho']] = np.nan
    print(data)
    ''' Ohio 0.577633 NewYork -0.245283 Vermont NaN Florida 0.544941 Oregon -0.869017 Nevada NaN California 0.544036 Idaho NaN dtype: float64 '''
    print(data.groupby(group_key).mean())
    ''' East 0.29243 West -0.16249 dtype: float64 '''
    # 用分组平均值去填充NA
    fill_mean = lambda g: g.fillna(g.mean())
    print(data.groupby(group_key).apply(fill_mean))
    ''' Ohio 0.577633 NewYork -0.245283 Vermont 0.292430 Florida 0.544941 Oregon -0.869017 Nevada -0.162490 California 0.544036 Idaho -0.162490 dtype: float64 '''
    # 还可以在代码中预定义各组的填充值
    fill_values = { 'East':0.5, 'West':-1}
    fill_func=lambda g:g.fillna(fill_values[g.name])
    data_filled = data.groupby(group_key).apply(fill_func)
    print(data_filled)
    ''' Ohio 0.577633 NewYork -0.245283 Vermont 0.500000 Florida 0.544941 Oregon -0.869017 Nevada -1.000000 California 0.544036 Idaho -1.000000 dtype: float64 '''

## 3.4 示例：随机采样和排列 ##

# 红桃(H)、黑桃(S)、梅花(C)、方片(D)
    
    suits = ['H','S','C','D']
    card_val = (list(range(1,11)) + [10] * 3) * 4
    # (list(range(1,11)) + [10] * 3) * 4
    base_names = ['A'] + list(range(2,11)) + ['J','K','Q']
    cards =[]
    for suit in suits:
        cards.extend(str(num) + suit for num in base_names)
    deck = Series(card_val, index = cards)
    print(deck[:13])
    ''' AH 1 2H 2 3H 3 4H 4 5H 5 6H 6 7H 7 8H 8 9H 9 10H 10 JH 10 KH 10 QH 10 dtype: int64 '''
    # 从整副牌抽出5张
    def draw(deck, n=5):
        return deck.take(np.random.permutation(len(deck))[:n])
    
    print(draw(deck))
    ''' 9S 9 2S 2 3H 3 AD 1 8H 8 dtype: int64 '''
    
    # 假设想要从每种花色中随机抽取两张
    get_suit = lambda card:card[-1]
    ret = deck.groupby(get_suit).apply(draw,n = 2)
    print(ret)
    ''' C 4C 4 8C 8 D 10D 10 QD 10 H 7H 7 9H 9 S 5S 5 3S 3 dtype: int64 '''
    
    # 另一种办法
    ret = deck.groupby(get_suit, group_keys=False).apply(draw, n = 2)
    print(ret)
    ''' QC 10 JC 10 10D 10 5D 5 9H 9 KH 10 JS 10 9S 9 dtype: int64 '''

## 3.5 示例：分组加权平均数和相关系数 ##

df = DataFrame({ 'category':['a','a','a','a','b','b','b','b'],
                    'data':np.random.randn(8),
                    'weights':np.random.rand(8)})
    print(df)
    ''' category data weights 0 a 0.878828 0.723115 1 a 0.582120 0.563386 2 a -0.459732 0.867047 3 a -0.457866 0.889768 4 b 0.492052 0.134058 5 b 0.450398 0.436383 6 b -1.166284 0.228464 7 b 0.840301 0.232131 '''
    # 利用catgegory计算分组加权平均数
    grouped = df.groupby('category')
    # 分组加权平均
    get_wavg=lambda g:np.average(g['data'],weights=g['weights'])
    print(grouped.apply(get_wavg))
    ''' category a 0.051736 b 0.185362 dtype: float64 '''
    
    # 对Yahoo!Finance的数据集进行分析
    close_px = pd.read_csv('python_data/ch09/stock_px.csv', parse_dates=True, index_col=0)
    print(close_px[-4:])
    ''' AAPL MSFT XOM SPX 2011-10-11 400.29 27.00 76.27 1195.54 2011-10-12 402.19 26.96 77.16 1207.25 2011-10-13 408.43 27.18 76.37 1203.66 2011-10-14 422.00 27.27 78.11 1224.58 '''
    
    # 计算一个由日收益率（通过百分比变化计算）与SPX之间的年度相关系数组成的DataFrame
    rets = close_px.pct_change().dropna()
    spx_corr=lambda x:x.corrwith(x['SPX'])
    by_year=rets.groupby(lambda x:x.year)
    print(by_year.apply(spx_corr))
    ''' AAPL MSFT XOM SPX 2003 0.541124 0.745174 0.661265 1.0 2004 0.374283 0.588531 0.557742 1.0 2005 0.467540 0.562374 0.631010 1.0 2006 0.428267 0.406126 0.518514 1.0 2007 0.508118 0.658770 0.786264 1.0 2008 0.681434 0.804626 0.828303 1.0 2009 0.707103 0.654902 0.797921 1.0 2010 0.710105 0.730118 0.839057 1.0 2011 0.691931 0.800996 0.859975 1.0 '''
    
    # 计算列与列之间的相关系数
    print(by_year.apply(lambda g:g['AAPL'].corr(g['MSFT'])))
    ''' 2003 0.480868 2004 0.259024 2005 0.300093 2006 0.161735 2007 0.417738 2008 0.611901 2009 0.432738 2010 0.571946 2011 0.581987 dtype: float64 '''

## 3.6 示例：面向分组的现型回归 ##

# 定义regress函数（利用statsmodels库）对各数据块执行普通最小二乘法回归(OLS)
    import statsmodels.api as sm
    def regress(data, yvar, xvars):
        Y = data[yvar]
        X = data[xvars]
        X['intercept'] = 1.
        result = sm.OLS(Y,X).fit()
        return result.params
    
    # 为了按年计算AAPL对SPX收益率的线性回归
    print(by_year.apply(regress, 'AAPL', ['SPX']))
    ''' SPX intercept 2003 1.195406 0.000710 2004 1.363463 0.004201 2005 1.766415 0.003246 2006 1.645496 0.000080 2007 1.198761 0.003438 2008 0.968016 -0.001110 2009 0.879103 0.002954 2010 1.052608 0.001261 2011 0.806605 0.001514 '''

# 4、透视表和交叉表 #

## 4.1 透视表 ##

# 以餐馆消费的数据集为例,介绍pivot_table方法
    tips =pd.read_csv('python_data/ch08/tips.csv')
    # 添加“消费占总额百分比”一列
    tips['tip_pct'] = tips['tip'] / tips['total_bill']
    print(tips[:6])
    ''' total_bill tip sex smoker day time size tip_pct 0 16.99 1.01 Female No Sun Dinner 2 0.059447 1 10.34 1.66 Male No Sun Dinner 3 0.160542 2 21.01 3.50 Male No Sun Dinner 3 0.166587 3 23.68 3.31 Male No Sun Dinner 2 0.139780 4 24.59 3.61 Female No Sun Dinner 4 0.146808 5 25.29 4.71 Male No Sun Dinner 4 0.186240 '''
    # 根据sex和smoker计算分组平均数（pivot_table的默认聚合类型）
    print(tips.pivot_table(index=['sex', 'smoker']))  # python3.7用index代替rows
    ''' size tip tip_pct total_bill sex smoker Female No 2.592593 2.773519 0.156921 18.105185 Yes 2.242424 2.931515 0.182150 17.977879 Male No 2.711340 3.113402 0.160669 19.791237 Yes 2.500000 3.051167 0.152771 22.284500 '''
    
    # 利用groupby实现上述效果如下
    print(tips.groupby(['sex', 'smoker']).mean())
    ''' total_bill tip size tip_pct sex smoker Female No 18.105185 2.773519 2.592593 0.156921 Yes 17.977879 2.931515 2.242424 0.182150 Male No 19.791237 3.113402 2.711340 0.160669 Yes 22.284500 3.051167 2.500000 0.152771 '''
    
    # 只聚合tip_pct和size两列，并且按照day进行分组
    print(tips.pivot_table(['tip_pct','size'], index=['sex','day'],
                           columns='smoker'))
    ''' size tip_pct smoker No Yes No Yes sex day Female Fri 2.500000 2.000000 0.165296 0.209129 Sat 2.307692 2.200000 0.147993 0.163817 Sun 3.071429 2.500000 0.165710 0.237075 Thur 2.480000 2.428571 0.155971 0.163073 Male Fri 2.000000 2.125000 0.138005 0.144730 Sat 2.656250 2.629630 0.162132 0.139067 Sun 2.883721 2.600000 0.158291 0.173964 Thur 2.500000 2.300000 0.165706 0.164417 '''
    
    # 对表进一步处理，传入margins=True添加积分项小计，会添加标签为All的行和列
    # 其值对应于单个等级中所有数据的分组统计
    ret = tips.pivot_table(['tip_pct','size'], index = ['sex','day'],
                           columns='smoker',margins=True)
    print(ret)
    ''' size tip_pct smoker No Yes All No Yes All sex day Female Fri 2.500000 2.000000 2.111111 0.165296 0.209129 0.199388 Sat 2.307692 2.200000 2.250000 0.147993 0.163817 0.156470 Sun 3.071429 2.500000 2.944444 0.165710 0.237075 0.181569 Thur 2.480000 2.428571 2.468750 0.155971 0.163073 0.157525 Male Fri 2.000000 2.125000 2.100000 0.138005 0.144730 0.143385 Sat 2.656250 2.629630 2.644068 0.162132 0.139067 0.151577 Sun 2.883721 2.600000 2.810345 0.158291 0.173964 0.162344 Thur 2.500000 2.300000 2.433333 0.165706 0.164417 0.165276 All 2.668874 2.408602 2.569672 0.159328 0.163196 0.160803 '''
    
    # 如果存在空的键（即NA），可能希望设置一个fill_value
    ret = tips.pivot_table('size', index = ['time','sex','smoker'],
                           columns='day',aggfunc='sum', fill_value=0)
    print(ret)
    ''' day Fri Sat Sun Thur time sex smoker Dinner Female No 2 30 43 2 Yes 8 33 10 0 Male No 4 85 124 0 Yes 12 71 39 0 Lunch Female No 3 0 0 60 Yes 6 0 0 17 Male No 0 0 0 50 Yes 5 0 0 23 '''

## 4.2 交叉表：crosstab ##

# 交叉表是一种用于计算分组计数的特殊透视表
    data = DataFrame({ 'Sample':np.arange(1,11),
                      'Gender':['Female','Male','Female','Male','Male','Male','Female','Female','Male','Female'],
                      'Handedness':["right-hand",'left-hand','right-hand','right-hand','left-hand',
                                    'right-hand','right-hand','left-hand','right-hand','right-hand']})
    print(data)
    ''' Sample Gender Handedness 0 1 Female right-hand 1 2 Male left-hand 2 3 Female right-hand 3 4 Male right-hand 4 5 Male left-hand 5 6 Male right-hand 6 7 Female right-hand 7 8 Female left-hand 8 9 Male right-hand 9 10 Female right-hand '''
    
    # 虽然可以使用pivot_table实现该功能，但是pandas.crosstab函数更方便
    ret = pd.crosstab(data.Gender, data.Handedness, margins=True)
    print(ret)
    ''' Handedness left-hand right-hand All Gender Female 1 4 5 Male 2 3 5 All 3 7 10 '''
    # 对比使用pivot_table()函数实现如下
    ret = data.pivot_table(index = 'Gender', columns='Handedness',aggfunc='count',margins=True)
    print(ret)
    ''' Handedness left-hand right-hand All Gender Female 1 4 5 Male 2 3 5 All 3 7 10 '''
    
    # crosstab前两个参数可以是数组、Series或数组列表
    
    ret = pd.crosstab([tips.time,tips.day],tips.smoker, margins=True)
    print(ret)
    ''' smoker No Yes All time day Dinner Fri 3 9 12 Sat 45 42 87 Sun 57 19 76 Thur 1 0 1 Lunch Fri 1 6 7 Thur 44 17 61 All 151 93 244 smoker No Yes All '''
    # print(pd.crosstab([tips['time'],tips['day']],tips['smoker'], margins=True))

[Python _pdf]: https://blog.csdn.net/zhou4411781/article/details/108556600