pandas 教你如何合并文件夹下的多张表

解法1

import os
import pandas as pd
result = []
path = r"C:\Users\Administrator\Desktop\权威流量数据\all时段表"
for root, dirs, files in os.walk(path, topdown=False):
    for name in files:
        if name.endswith(".xls") or name.endswith(".xlsx"):
            df = pd.read_excel(os.path.join(root, name), sheet_name=None)
            result.append(df)

data_list = []
for data in result:
    # print(data.values())
    data_list.extend(data.values())

df = pd.concat(data_list)
df.to_excel(r"C:\Users\Administrator\Desktop\权威流量数据\all时段合并.xlsx", index=False)
print("合并完成!")

解法2

import pandas as pd
import glob

path = r"C:\Users\Administrator\Desktop\权威流量数据\视频时段表"
# glob.glob(f'{path}/**/[!~]*.xls*') # x改成*，两个**代表递归，


data = []
for excel_file in glob.glob(f'{path}/*.xls*'): # x改成*，两个**代表递归，如果是xls,[f'{path}/**/[!~]*.xls*]
    excel = pd.ExcelFile(excel_file)
    for sheet_name in excel.sheet_names:
#         print(sheet_name)
        df = excel.parse(sheet_name)
#         print(df)
        data.append(df)# pandas的append相对pandas的concat太慢哈

data = pd.concat(data,ignore_index=True)
data.to_excel(r"C:\Users\Administrator\Desktop\权威流量数据\视频合并.xlsx",index = False)

print("完成")

解法3

import pandas as pd
import glob

path = r"C:\Users\Administrator\Desktop\权威流量数据\音频时段表"
data = []
for excel_file in glob.glob(f'{path}/*.xls*'): 
    dfs = pd.read_excel(excel_file,sheet_name = None).values()
    data.extend(dfs)
data = pd.concat(data,ignore_index=True)
data.to_excel(r"C:\Users\Administrator\Desktop\权威流量数据\音频时段合并.xlsx",index = False)

print("完成")

解法4

import pandas as pd
import glob

path = r"C:\Users\Administrator\Desktop\权威流量数据\音频时段表\*.xlsx"
data = []
for excel_file in glob.glob(path): 
    dfs = pd.read_excel(excel_file)
    data.append(dfs)
data = pd.concat(data,ignore_index=True)
data.to_excel(r"C:\Users\Administrator\Desktop\权威流量数据\音频时段合并.xlsx",index = False)

print("完成")

多sheet 合并

解法1 ：

import pandas as pd
from glob import glob

path = r"C:\Users\Administrator\Desktop\Excel多sheet合并"
data = {}
for file in glob(f"{path}/**/[!~]*.xls*", recursive=True):
    for name, df in pd.read_excel(file, sheet_name=None).items():
        data.setdefault(name, []).append(df)# setdefault类似于get函数，如果字典中包含有给定键，则返回该键对应的值，否则返回为该键设置的值

with pd.ExcelWriter(r"C:\Users\Administrator\Desktop\多sheet表格合并.xlsx") as write:
    for name, dfs in data.items():
        pd.concat(dfs).to_excel(write, name, index=False)

解法2

import pandas as pd
import glob
path = r"C:\Users\Administrator\Desktop\Excel多sheet合并"
dfs = [pd.read_excel(file) for file in glob.glob(f"{path}/**/[!~]*.xls*", recursive=True)]
print(dfs)
df = pd.concat(dfs, ignore_index=True)
df
# df.to_excel("合并结果.xlsx", index=False)