数据整合、层次化索引、分组聚合

1.数据整合

import pandas as pd
df1 = pd.DataFrame({'id':[1,2,3,4,5],'name':['张三','李四','王五','丁一','赵二'],'age':[27,25,16,34,16],'gender':['男','女','女','男','男']})

df2 = pd.DataFrame({'Id':[1,2,2,4,5,6,7],'kemu':['科目1','科目1','科目1','科目2','科目3','科目2','科目2'],'score':[83,81,67,92,86,79,99]})

df3 = pd.DataFrame({'id':[1,3,5],'name':['张三','李四','王五'],'income':[8000,40001,20000]})

#求交集
merge1 = pd.concat([df1,df2],axis = 1,join = 'inner')
#求并集
merge2 = pd.concat([df1,df2],axis = 1,join = 'outer')

#将三张表单合并
import os
os.chdir(r'C:\Users\wangyaqian\Desktop\Excel学习包')
order1 = pd.read_excel('1.xlsx',sheet_name = 0,header = 0,encoding='gbk')
order2 = pd.read_excel('1.xlsx',sheet_name = 2,header = 0,encoding='gbk')
order3 = pd.read_excel('1.xlsx',sheet_name = 3,header = 0,encoding='gbk')

data = pd.concat([order1,order2,order3],axis = 0,ignore_index = True)
#ignore_index = True,表示忽略原索引，按合并之后重新排序

#或者单独重置索引
data.reset_index(drop = True, inplace = True)


#整合
merge1 = pd.merge(left = df1, right = df2, how = 'right', left_on = 'id',right_on = 'Id')

#
merge2 = pd.merge(left = df1, right = df2, how = 'left', left_on = 'id',right_on = 'Id')

#
merge3 = pd.merge(left = df1, right = df2, how = 'outer', left_on = 'id',right_on = 'Id')
#
merge4 = pd.merge(left = df1, right = df2, how = 'inner', left_on = 'id',right_on = 'Id')

2.层次化索引

3.排序

import pandas as pd
import numpy as np
import os
os.chdir(r'C:\Users\wangyaqian\Desktop\Excel学习包')
order = pd.read_excel('test.xlsx',header = 0,sheet_name = 0,\
                      encoding = 'utf-8')
order.isnull()#判断是否有缺失值
np.sum(order.isnull(),axis = 0)#统计缺失值
#根据语文分数排序，ascending=True升序，na_position='last'缺失值排末尾
order.sort_values('语文',ascending=True,\
                  na_position='last',inplace=True)
#根据排序后顺序重置索引
order.reset_index(drop = True, inplace = True)
#按多列排序
order.sort_values('语文','数学')

4.分类聚合

import pandas as pd
import numpy as np
import os
os.chdir(r'C:\Users\wangyaqian\Desktop\Excel学习包')
order = pd.read_excel('test.xlsx',header = 0,sheet_name = 0,\
                      encoding = 'utf-8')
#选择想要查看的列
var_name=['语文','数学','英语']
#求列最小值，max()最大值，media()中位数，cumsum()累积和
np.min(order[var_name],axis=0)
#求分位数
order[var_name].quantile([0,0.2,0.5,1])
#求所有的基本统计量
order[var_name].describe()
#对于非数字量的统计
order.describe(include = ['object'])

groupe = order.groupby('语文')
print(type(groupe))#<class 'pandas.core.groupby.groupby.DataFrameGroupBy'>
#分组之后可以和上面一样计算一个组的均值等
groupe.count()#统计每一组的数目
groupe.cumcount()#对每一组数据进行索引标记

#多组分类汇总及索引
grouped = order[var_name].groupby(by = ['语文','数学'])
b = grouped.median().loc[([95,86]),:]
# 语文 数学 英语
# 86 47  54
#    55  90
#    79  98
#    88  67
# 95 71  77
#    79  77
#    89  98
#    96  94

import pandas as pd
import numpy as np
import os
os.chdir(r'C:\Users\wangyaqian\Desktop\Excel学习包')
order = pd.read_excel('test.xlsx',header = 0,sheet_name = 0,\
                      encoding = 'utf-8')
#选择想要查看的列
var_name=['语文','数学','英语']
grouped = order[var_name].groupby('语文')
a = grouped.agg([np.mean,np.sum])
b = grouped.mean()
# print(a,b)
c = grouped.agg([np.mean,np.sum]).loc[[60,77],['数学']]
# print(c)
#       数学
#     mean  sum
# 语文
# 60  64.0   64
# 77  88.5  177
d = grouped.agg({'数学':np.mean,'英语':np.sum})
# print(d)
#        数学   英语
# 语文
# 44  32.00   94
# 45  79.00   94
# 60  64.00   77
# 77  88.50  171
# 78  79.00   77
# 86  67.25  309
# 95  83.75  346
e = grouped.agg({'数学':[np.mean,np.median],'英语':np.sum})
# print(e)
#        数学          英语
#      mean median  sum
# 语文
# 44  32.00   32.0   94
# 45  79.00   79.0   94
# 60  64.00   64.0   77
# 77  88.50   88.5  171
# 78  79.00   79.0   77
# 86  67.25   67.0  309
# 95  83.75   84.0  346
#自定义函数
def DoubleSUM(data):
	s = data.sum()*2
	return s
f = grouped.agg({'数学':DoubleSUM})
# print(f)
#      数学
# 语文
# 44   64
# 45  158
# 60  128
# 77  354
# 78  158
# 86  538
# 95  670
g = order.agg({'数学':DoubleSUM})
# print(g)
# 数学    2070
# dtype: int64
h = order[var_name].apply(np.sum,axis = 0)
# print(h)
# 语文    1105
# 数学    1035
# 英语    1168
# dtype: int64
j = order[var_name].apply(lambda x:x[2]-x[1],axis = 1)
# print(j)
# 0     35
# 1     13
# 2     19
# 3     -2
# 4      7
# 5      6
# 6      9
# 7     15
# 8    -21
# 9     -2
# 10    -2
# 11    -2
# 12    62
# 13    -4
# dtype: int64