合并excel
import pandas as pd, os
cwd = os.getcwd() # 当前目录
og_dir = cwd + '/origin/' # 存放待合并的excel
og_ls = os.listdir(og_dir) # 所有excel名称
cb_name = 'combine.xlsx' # 合并后的文件名
# 合并
excel_ls = []
for f in og_ls:
excel_ls.append(pd.read_excel(og_dir + f))
df0 = pd.concat(excel_ls)
df0.to_excel(cb_name, index=False)
拆分excel
import os, pandas as pd
b_dir = 'before/' # 放置待拆分的excel(限1个)
fname = b_dir + os.listdir(b_dir)[0] # 待拆分的excel的文件名
a_dir = 'after/' # 放置拆分后的excel
def split_xlsx(col=0): # 按列序拆
df = pd.read_excel(fname)
field = df.columns[col]
unique = df[field]
for u in unique:
df[df[field] == u].to_excel(a_dir + '{}.xlsx'.format(u), index=False)
split_xlsx()
基础补充
1、合并DataFrame
- 相同列合并
import pandas as pd
import numpy as np
df1 = pd.DataFrame(np.array(range(8)).reshape(2, 4), columns=list('ABCD'))
df2 = pd.DataFrame(np.array(range(100, 108)).reshape(2, 4), columns=list('ABCD'))
df3 = pd.concat([df1, df2])
print(df1, df2, df3, sep='\n\n')
- 相同行合并:axis=1