透视图与交叉表

import pandas as pd
import numpy as np
import os
os.chdir(r'C:\Users\wangyaqian\Desktop\Excel学习包')
df = pd.read_excel('test.xlsx',header = 0,sheet_name = 0,\
                      encoding = 'utf-8')
a = pd.pivot_table(data = df, index = '语文',values = '数学',\
					aggfunc = np.mean,margins=True,margins_name = '总计')
#columns = ''//添加列标签，类似二维表格
#index= [],columns=[]//多个叠加行、列标签用列表表示
#fill_value = 0//q缺失值补0

# print(a)
#            数学
# 语文
# 44  32.000000
# 45  79.000000
# 60  64.000000
# 77  88.500000
# 78  79.000000
# 86  67.250000
# 95  83.750000
# 总计  73.928571


#计算行列约束类型下的频数统计
b = pd.crosstab(index = df['语文'],columns = df['数学'],\
	margins = True)
# print(b)
# 数学   32  47  55  64  71  79  88  89  96  98  All
# 语文
# 44    1   0   0   0   0   0   0   0   0   0    1
# 45    0   0   0   0   0   1   0   0   0   0    1
# 60    0   0   0   1   0   0   0   0   0   0    1
# 77    0   0   0   0   0   1   0   0   0   1    2
# 78    0   0   0   0   0   1   0   0   0   0    1
# 86    0   1   1   0   0   1   1   0   0   0    4
# 95    0   0   0   0   1   1   0   1   1   0    4
# All   1   1   1   1   1   5   1   1   1   1   14

#每一频数在该行/列的占比
c = pd.crosstab(index = df['语文'],columns = df['数学'],\
	normalize='index',margins = True)
# print(c)
# 数学         32        47        55    ...           89        96        98
# 语文                                   ...                                 
# 44   1.000000  0.000000  0.000000    ...     0.000000  0.000000  0.000000
# 45   0.000000  0.000000  0.000000    ...     0.000000  0.000000  0.000000
# 60   0.000000  0.000000  0.000000    ...     0.000000  0.000000  0.000000
# 77   0.000000  0.000000  0.000000    ...     0.000000  0.000000  0.500000
# 78   0.000000  0.000000  0.000000    ...     0.000000  0.000000  0.000000
# 86   0.000000  0.250000  0.250000    ...     0.000000  0.000000  0.000000
# 95   0.000000  0.000000  0.000000    ...     0.250000  0.250000  0.000000
# All  0.071429  0.071429  0.071429    ...     0.071429  0.071429  0.071429
# 
# [8 rows x 10 columns]