十二 EXCEL文件合并和拆分
12.1 一个文件夹下多个工作簿合并
import pandas as pd
import os
root = 课件025/'# data = pd.read_excel(root)
#准备一张空表set = pd.DataFrame()for i in os.listdir(root):sheet= pd.read_excel(root + i)set = pd.concat([set,sheet])print(set)# 写回去set.to_excel('')先创建一个工作表 遍历跟目录下面的工作表 把工作表路径先拼接好，之后合并到一张工作表中12.2 同一个工作簿中多个sheet合并
import pandas as pd
import os
root = '合并2.xlsx'data = pd.read_excel(root)set = pd.DataFrame()
#把字段名全部拿出来放在name中
name = list(data.keys())for i in name:#data[i] 是遍历 list中有的data.keys() 赋值给新的数据data1 =data[i]set = pd.concat([set,data1])print(set)创建工作表 data.keys() 找到字段 通过list(data.keys()) 拿到返回的列表循环拿到的列表 创建新数据data1 = data[i] 最后再合并 空表和每次的新数据连接12.3 将一个工作表拆分多个工作表
ExcelWriter() 可以向同一个excel的不同sheet中写入对应的表格，首先需要创建一个write对象，传入的主要参数为已存在容器表格的路径以及文件名称
excelWrite对已经设置好的格式是无法更改的，因此，有pandas转入excel的时候，必须先清除格式，尤其是表头
import pandas.io.formats.excel
pandas.io.formats.excel.header_style=None
标准的保存到pandas表到excel的形式为
writer = pd.ExcelWrite('C:/123.xlsx')
data.to_excel(writer,'工作表名') #这里假设df是一个pandas的dataframe
write.save()
write.close()
import pandas as pdroot = '拆分.xlsx'data = pd.read_excel(root)
#去重
slit = list(data['部门'].drop_duplicates())root1 = pd.ExcelWriter('C:拆.xlsx')
# i 不同部门
for i in slit:data1 =data[data['部门']== i ]data1.to_excel(root1,sheet_name=i)
root1._save()root1.close()首先拿到一个去重的数据列表，ExcelWriter创建一个新的路径放生成的表
遍历这个列表 将拿到的不同的部门的数据data['部门'] 赋值给一个新的数据
将拿到的新的数据重新写入到新创建的路径 ，表名 i 保存路径 关闭12.4 将一个工作表拆分成多个工作簿
import pandas as pdroot = '拆分.xlsx'data = pd.read_excel(root)#去重 部门做一个列表slit = list(data['部门'].drop_duplicates())for i in slit:data1 = data[data['部门'] == i]data1.to_excel('c:/' + i + '.xlsx')十三 分组聚合
13.1 分组集合groupby
groupby分为三个步骤 拆分 - 应用-合并
DataFrame可以在其行（axis=0）或者(axis=1) 上进行分组
然后，将一个函数应用到各个分组产生新值
最后，所有这些函数的执行结果会被合并到最终的结果对象中
Groupby的size方法可以返回一个含有分组大小的Series
| 函数名 | |
| count | 分组中非Nan值的数量 |
| sum | 非NA值的和 |
| mean | 非NA值的算术中位数 |
| std var | 无偏 分母为n-1 标准差 和方差 |
| min max | 非NA 值的最小值和最大值 |
| prod | 非NA值的积 |
| first last | 第一个最后一个非Na的值 |
agg函数一般与groupbu函数配合使用，agg是基于列的聚合操作，而groupby是基于行的
DataFrame.agg(func,axis=0,*args,**kwargs)
func():函数，函数名称，函数列表，字典{行名/列名,函数名}
使用特定轴上的一个或者多个操作进行聚合
import pandas as pdroot = '6/分组聚合4.xlsx'
data = pd.read_excel(root,header=[0,1])L1 = ['1季度','1季度','1季度','2季度','2季度']L2 = ['1月','2月','3月','4月','5月']set = pd.MultiIndex.from_arrays([L1,L2],names=['季度','月份'])data2= pd.DataFrame(data,columns=set)#axis=1列分组data3 = data2.groupby(level='季度',axis=1).sum()print(data3)13.2分组对象和创建
isin()接收一个列表，判断列中元素是否存在列表中
isin()的逆函数 没有isnotin 它的反函数就是前面加上~ 例如 ~数据.班级.insin()
import pandas as pdroot = 分组.xlsx'data = pd.read_excel(root)# data2 = data.groupby(data.index%2==0 )[['语文','数学','英语']].sum()# data2 = data.groupby(data.姓名.str[0:3])[['语文','数学','英语']].sum()data2 = data.groupby(data.班级.isin(['1班','2班']))[['语文','数学','英语']].sum()print(data2)班级分组
data2 = data.pipe(pd.DataFrame.groupby,'班级').sum()十四 数据透视表
14.1 pivot_table
pd.pivot_table(index=['',''],values=['',''],columns=[''],aggfunc=[sum,np.mean])
Aggfunc聚合函数或者函数
index需要聚合的列名
values在结果透视的行上进行分组的列名
columns在结果透视表的列上进行分组的列名
fill_value =0 缺失的值填充为0
dropna=True 不显示空列
margins 添加行/列和小计和总计
import pandas as pdimport numpy as nproot = '透视.xlsx' data = pd.read_excel(root)data2 = pd.pivot_table(data,index=['部门','销售人员'],values=['数量','金额'],columns='所属区域',aggfunc=[len,sum,np.mean],fill_value=0,dropna=True)print(data2)
14.2 crosstab
import pandas as pdimport numpy as nproot = 透视.xlsx'
data = pd.read_excel(root)data2 = pd.crosstab([data.日期.dt.month,data.所属区域],data.部门,margins=True)结果日期 部门 销售人员 所属区域 数量 金额 成本
0 2019-01-01 销售 李平平 湖北 16 7 8
1 2019-01-02 销售 李平平 湖北 40 8 3
2 2019-01-03 销售 李平平 湖北 20 6 7
3 2019-01-04 销售 李平平 湖北 20 7 5
4 2019-01-05 销售 李平平 湖北 16 6 6... .. ... ... ... .. ..
1215 2022-04-30 财务 熊牧 山东 250 4 8十五 vlookup
15.1
import pandas as pdimport numpy as nproot = 'Vlookup.xlsx'data = pd.read_excel(root,sheet_name='花名册')data1 = pd.read_excel(root,sheet_name='成绩单')result = pd.merge(data,data1.loc[:,['学号','总分']],how='left',on='学号')s = result.总分result = result.drop('总分',axis=1)result.insert(0,'总分',s)print(result)writer =pd.ExcelWriter(root)result.to_excel(writer,idnex=False)writer._save()writer.close()十六
16.1 map
import pandas as pd
#
import numpy as nproot = '数据.xlsx'data = pd.read_excel(root)set = {'男':'先生','女':'女士'}data['性别']=data['性别'].map(set)print(data)# def c(x,w):
# return x+w
# data['语文'] = data['语文'].apply(c,args=(3,))
#
# print(data)# set = {'男':'先生','女':'女士'}
#
# data['性别']=data['性别'].map(set)# def d(x):
# name = '先生' if x == '男' else '女士'
# return name
# data['称呼'] = data['性别'].map(d)
# print(data)def BMI(data):h= data['身高']w = data['体重']BMI= h/w **2return BMIdata['BMI'] = data['BMI'].apply(BMI,axis=1)
16.2同比 环比
import pandas as pd
#
import numpy as nproot = '同比.xlsx'data = pd.read_excel(root)y = data['日期'].dt.yeardata2 = pd.pivot_table(data,index='店号',values='金额',columns=y,aggfunc='sum')data2['同比'] = (data2[2019]-data2[2018])/data2[2018]print(data2)#同比# 环比
# def d(x):
# x['环比'] = x.金额 = x.金额.shift()
# return x
# data2 = data.sort_values(['城市','月份']).groupby('城市').apply(d)
#
# print(data2)