交叉表和透视表

利用Python实现数据透视表和交叉表

1、透视表 pivot table

透视表（pivot table）是常见的数据汇总工具，它根据一个或多个键对数据进行聚合，根据行和列上的分组键将数据分配到矩形区域中。pandas中使用pivot_table方法创建透视表，

pd.pivot_table(data,values=None,index=None,columns=None,aggfunc='mean'

,fill_value=None,margins=False,dropna=True,margins_name='ALL')

• data:DataFrame对象
• values：要聚合的列或列的列表
• index：数据透视的index，从原始数据的列中筛选
• columns：数据透视表的columns，从原始数据的列中筛选
• aggfunc：用于聚合的函数，默认为numpy，mean，支持numpy计算方法

import numpy as np
import pandas as pd              
date = ['2019-1-1','2019-1-2','2019-1-3']*3
date_time = pd.to_datetime(date)
df = pd.DataFrame({'date':date_time,
                   'key':list('abcdabcab'),
                   'values':np.random.rand(9)*10})
print('------------原数据------------')
print(df)
 
print('------------透视表1------------')
#也可以aggfunc='sum'
print(pd.pivot_table(df,values = 'values',index = ['date'],columns='key',aggfunc=np.sum))
 
print('------------透视表2------------')
#分别以date，key共同做数据透视，值为values：统计不同（date，key）情况下values的计数
#aggfunc=len（或者count）：计数
print(pd.pivot_table(df,values = 'values',index = ['date','key'],aggfunc=len))

输出结果为：

------------原数据------------
        date key    values
0 2019-01-01   a  6.372699
1 2019-01-02   b  0.649605
2 2019-01-03   c  4.897285
3 2019-01-01   d  7.758373
4 2019-01-02   a  1.576888
5 2019-01-03   b  8.217029
6 2019-01-01   c  5.454403
7 2019-01-02   a  5.072132
8 2019-01-03   b  2.875602
------------透视表1------------
key                a          b         c         d
date                                               
2019-01-01  6.372699        NaN  5.454403  7.758373
2019-01-02  6.649020   0.649605       NaN       NaN
2019-01-03       NaN  11.092630  4.897285       NaN
------------透视表2------------
                values
date       key        
2019-01-01 a       1.0
           c       1.0
           d       1.0
2019-01-02 a       2.0
           b       1.0
2019-01-03 b       2.0
           c       1.0

2、交叉表：crosstab

默认情况下，crosstab计算因子的频率，比如用于str的数据透视分析

pd.crosstab(index,columns,values=None,rownames=None
,colnames=None,aggfunc=None,margins=False,dropna=True,normalize=False)

import numpy as np
import pandas as pd
 
df = pd.DataFrame({'A':[1,2,2,2,2],
                   'B':[3,3,4,4,4],
                   'C':[1,1,np.nan,1,1]})
 
print('------------原数据------------')
print(df)
 
#如果crosstab只接收两个series，他将提供一个频率表
#用A的唯一值，统计B唯一值的出现次数  （A,B）= （1，3）C出现了1次   （A,B）= （2，4）出现了3次
print('------------交叉表1------------')
print(pd.crosstab(df['A'],df['B']))
 
print('------------交叉表2------------')
print(pd.crosstab(df['A'],df['B'],normalize=True))#以频率的方式显示
 
print('------------交叉表3------------')
print(pd.crosstab(df['A'],df['B'],values=df['C'],aggfunc=np.sum))#values:根据因子聚合的值数组
#aggfunc：如果未传递values数组，则计算频率表，如果传递数组，则按照指定计算
#这里相当于以A和B界定分组，计算出每组中第三个系列C的值
 
print('------------交叉表4------------')
print(pd.crosstab(df['A'],df['B'],values=df['C'],aggfunc=np.sum,margins=True))
#margins:布尔值，默认值False，添加行/列边距（小计）

------------原数据------------
   A  B    C
0  1  3  1.0
1  2  3  1.0
2  2  4  NaN
3  2  4  1.0
4  2  4  1.0
------------交叉表1------------
B  3  4
A      
1  1  0
2  1  3
------------交叉表2------------
B    3    4
A          
1  0.2  0.0
2  0.2  0.6
------------交叉表3------------
B    3    4
A          
1  1.0  NaN
2  1.0  2.0
------------交叉表4------------
B      3    4  All
A                 
1    1.0  NaN  1.0
2    1.0  2.0  3.0
All  2.0  2.0  4.0

pandas的文件读取和写入

• 读取

 
    #excle文件读取
    pd.read_excel(io, sheetname=0, header=0, skiprows=None, skip_footer=0, index_col=None, names=None, parse_cols=None, parse_dates=False, date_parser=None, na_values=None, thousands=None, convert_float=True, has_index_names=None, converters=None, dtype=None, true_values=None, false_values=None, engine=None, squeeze=False, **kwds)
    #参数解析
io:字符串，路径对象(pathlib)。Path或py._path.local.LocalPath)、类似文件的obiect、panda ExcelFile或xlrd工作簿。字符串可以是URL。有效的URL方案包括http、ftp、53和file。对于文件url，需要一个主机。例如，一个本地文件可以是file://localhost/path/to/workbook.xlsx 
sheetname: string, int, string /int的混合列表，或者None，默认值为a字符串用于表名，整数用于零索引的表位置。字符串/整数列表用于请求多个工作表。
#csv文件读取 
 pd.read_csv(filepath_or_buffer, sep=',', delimiter=None, header='infer', names=None, index_col=None, usecols=None, squeeze=False, prefix=None, mangle_dupe_cols=True, dtype=None, engine=None, converters=None, true_values=None, false_values=None, skipinitialspace=False, skiprows=None, nrows=None, na_values=None, keep_default_na=True, na_filter=True, verbose=False, skip_blank_lines=True, parse_dates=False, infer_datetime_format=False, keep_date_col=False, date_parser=None, dayfirst=False, iterator=False, chunksize=None, compression='infer', thousands=None, decimal=b'.', lineterminator=None, quotechar='"', quoting=0, escapechar=None, comment=None, encoding=None, dialect=None, tupleize_cols=False, error_bad_lines=True, warn_bad_lines=True, skipfooter=0, skip_footer=0, doublequote=True, delim_whitespace=False, as_recarray=False, compact_ints=False, use_unsigned=False, low_memory=True, buffer_lines=None, memory_map=False, float_precision=None)
    #参数解析
    filepath_or_buffer : 字符串文件路径，
        

• 写入

sq.to_excel(excel_writer, sheet_name='Sheet1', na_rep='', float_format=None, columns=None, header=True, index=True, index_label=None, startrow=0, startcol=0, engine=None, merge_cells=True, encoding=None, inf_rep='inf', verbose=True, freeze_panes=None)

 sheet_name ：excel表名命名
 na_rep ： 缺失值填充 ，可以设置为字符串，如果na_rep设置为bool值，则写入excel时改为0和1；也可以写入字符串或数字
columns ：选择输出的的列存入
header :指定作为列名的行，默认0，即取第一行，数据为列名行以下的数据；若数据不含列名，则设定 header = None；
index：默认为True，显示index，当index=False 则不显示行索引（名字）
index_label：设置索引列的列名
float_format:设置输入格式


# excel文件写入
import pandas as pd
import numpy as np

data = np.arange(1,101).reshape((10,10))
data_df = pd.DataFrame(data)
data_df.columns = ['A','B','C','D','E','F','G','H','I','J']
data_df.index = ['a','b','c','d','e','f','g','h','i','j']

writer = pd.ExcelWriter('my.xlsx')
data_df.to_excel(writer,float_format='%.5f')
writer.save()