pandas学习日记(六)

excel文档拆分与合并

import pandas as pd

# 读取文件
df = pd.read_excel("文件路径/文件名")

# 文件分组所属者
userNames = ["小帅", "小王", "小明", "小雷", "小博", "小红","小韩","小艺"]

# 查看文件有几行数据
rows = df.shape[0]
# 计算每个人多少行
count = rows//len(userName)
if row%len(userName)!=0:  # 如果不能除尽 需要给每个人加1，最后几个人可能会超出索引范围
	count += 1

df_subs = []

# 将数据分组并放入列表中
for index,userName in enumerate(userNames):
	# 起始索引
	begin = index * count
	# 结束索引
	end = begin + count
	# 实现分割
	df_sub = df[begin:end]
	# 加入列表
	df_subs.append(df_sub)

# 写入新文件
for index,userName,df_sub in df_subs:
	fileName = f"文件路径/No_{index}_{userName}.xlsx" # 组合新文件地址
	df_sub,to_excel(fileName,index=False) # 写入新文件


# ==================================================================
# 合并文件
import os
import pandas as pd
excel_names = []
for excel_name in os.listdir("拆分后文件位置"):
	excel_names.append(excel_name) # 将文件名放入列表


df_list = []
for excel_name in excel_names:
	# 读取
	excel_path = f"文件位置/{excel_name}"
	df_split = pd.read_excel(excel_path) # 读取单个拆分后的文件
	# 获取用户名字
	userName = excel_name.replace("No_","").replace(".xlsx","")[2:]
	df_split["userName"] = userName # 添加一列 署名 区分来自哪个文件
	df_list.append(df_split)

# 将各个dataFrame 合并
df_merge = pd.concat(df_list, axis=0, ignore_index=True)
df_merge.to_excel("文件地址/新文件名", index=False)

分组统计

import pandas as pd
import numpy as np
%matplotlib inline # 添加图形化展示

# 自定义数据 
df = pd.DataFrame({"A":["foo","bar","foo","bar","foo","bar","foo","foo"],
                   "B":["one","one","two","three","two","two","one","tree"],
                   "C":np.random.randn(8), # 使用np生成随机数
                   "D":np.random.randn(8)
})

# 以A字段分组，求和
df.groupby("A").sum()
# 以A,B字段分组求平均值
df.groupby(["A","B"]).mean()
# 以A,B字段分组 并查看 总和 平均值 标准差
df.groupby(["A","B"]).agg([np.sum,np.mean,np.std])
# 以A分组 统计不同列的不同值
result = df.groupby("A").agg({'C': ['sum', 'mean', 'std'], 'D': ['sum', 'mean', 'std']})
# 单列函数 (某一列的某些值)
df.groupby("A")["C"].agg([np.sum,np,mean,np.std])