时间戳

首页 > 解决方案 > Pandas - 按列值将数据框拆分为多个 Excel 工作簿

python - Pandas - 按列值将数据框拆分为多个 Excel 工作簿

问题描述

我是熊猫新手。我有一个大的 excel 文件，我想要做的是将操作后的数据框拆分为多个 excel 工作簿。大约有 400 个供应商，我希望每个供应商都有自己的命名工作簿。

例子。SallyCreative.xlsx、JohnWorks.xlsx、AlexGraphics.xlsx

标签： pythonexcelpandas

解决方案

这是我按列值将数据框拆分为多个 Excel 工作簿的方法。

import pandas as pd
    
data = pd.read_excel('anyexcelfile.xlsx', engine='openpyxl') # creates a dataframe called 'data'; pick any spreadsheet you can add paths to 'x:/folder/subfolder/anyexcelfile.xlsx' to be explict. 

grouped = data.groupby("Column Header Name") # change "Column Header Name" to the name of the column needed to categorise or group the rows in the dataframe, 

keys = grouped.groups.keys() #create a dictionary list of the each group unique varibles in the specifed column of the dataframe.   

print(keys) #a cheeky debug to check it's working

for key in keys: #looping through each key 
        splitdf = grouped.get_group(key) # creating a temporary dataframe with only the values of the current key. 
        splitdf.to_excel(str(key)+".xlsx", engine='xlsxwriter') #write the temporary dataframe called 'splitdf' to an excel file named after the key. At the end of the loop the temporary dataframe 'splitdf' is overwritten for use with the next key.

推荐阅读