将多个DataFrame附加到多个现有excel工作表

Question

我有多个送货和多个地址的关系。

我为每个地区(5个地区)制作了一个数据透视表列表

使用">

; for"在jupyter notebook中，每个列表中的每个项目都显示为一个单独的数据透视表，一个在另一个之上，这正是我需要的方式。

但是我如何将它们保存在5个工作表的excel中?

我已经尝试了所有方法，只是保存使用每个区域列表创建的最后一个枢轴，或者保存现有的枢轴并删除所有内容。

我目前为每个地区创建了一个空的电子表格，在D列第1行只有一个标题。

expedition.xlsx(内、北、东北、中西部、东南、南部共5张)

当我尝试保存时，它最终删除了其他的，只保留了'north'

我放了一个规则来确定列D是否有一个空单元格，如果它被填充，再试一次跳过一行，如果它是空的，理论上它应该填充列表的数据框。

在这个图像中，它就像jupyter笔记本显示，因为我想把它保存在excel中(一个枢轴在另一个下面，有2个空格)

使用openpyxl，我设法使规则工作，并在空格下面的第一列填充一个示例'aaaaaa'，而不需要删除其他表

如何在另一个数据透视表下面填充一个数据透视表?对于每个区域和列表项。

代码:https://pastebin.com/Cx3Zvf6D

import pandas as pd
import openpyxl

writer = pd.ExcelWriter("expedition.xlsx", engine='xlsxwriter')

# Creating the base sheet for each region, empty
pivot1 = pd.DataFrame({'Lista de Romaneio para Região Norte':  [' ']})
pivot2 = pd.DataFrame({'Lista de Romaneio para Região Nordeste':  [' ']})
pivot3 = pd.DataFrame({'Lista de Romaneio para Região Centro Oeste':  ['']})
pivot4 = pd.DataFrame({'Lista de Romaneio para Região Sudeste':  [' ']})
pivot5 = pd.DataFrame({'Lista de Romaneio para Região Sul':  [' ']})

# creating a sheet in the spreadsheet for each region, with the title in column D, row 1
pivot1.to_excel(writer, sheet_name='Norte', index=False, startcol=3, freeze_panes=(1,0))
pivot2.to_excel(writer, sheet_name='Nordeste', index=False, startcol=3, freeze_panes=(1,0))
pivot3.to_excel(writer, sheet_name='Centro Oeste', index=False, startcol=3, freeze_panes=(1,0))
pivot4.to_excel(writer, sheet_name='Sudeste', index=False, startcol=3, freeze_panes=(1,0))
pivot5.to_excel(writer, sheet_name='Sul', index=False, startcol=3, freeze_panes=(1,0))

writer.close()

# List with the "keys" of each pivot table for each region
norte = ['PA_BEL', 'TO_PMW', 'AC_RBR']
nordeste = ['AL_MCZ', 'PB_JPA', 'BA_SSA', 'RN_NAT', 'PE_REC', 'CE_FOR', 'MA_IMP', 'MA_THE', 'PI_THE', 'BA_FEC']
centro_oeste = ['GO_GYN', 'DF_BSB', 'GO_BSB', 'MT_CGB', 'MS_CGR']
sudeste = ['ES_SRR', 'MG_BHZ', 'SP_PNM', 'SP_JDI', 'RJ_RIO', 'MG_UDI']
sul = ['RS_POA', 'PR_CWB', 'SC_CCM', 'RS_RIA', 'SC_FLN']


# example for the north (norte) region
if len(norte) > 0:
frete_expresso_norte = 0
for filial in norte:
# creating a pivot table for each flilial(key)
pivot1 = df[df.Filial_Transportador == filial].pivot_table(
index=['BU', 'Sold to Region', 'Filial_Transportador', 'Sold_to_Name', 'Sold to City', 'Delivery'],
values=['Quantidade','Volume', 'Palete', 'Net Value'], aggfunc='sum',
margins=True)
# reorders columns and renames ALL of pivot table to Total
ordem_das_colunas = ['Quantidade', 'Volume', 'Palete', 'Net Value']
pivot1 = pivot1[ordem_das_colunas].rename(index=dict(All='Total Romaneio'))    
# creating subtotals and finding express shipping (if any)
total_palete_norte = pivot1.groupby('Filial_Transportador')['Palete'].sum()[1]
total_net_value_norte = pivot1.groupby('Filial_Transportador')['Net Value'].sum()[1]
# save the pivot table (pivot1) in excel in the north sheet where it is blank
# Here's where I want to put the code below saving the pivot table before starting the creation of the next one.
# code under construction
# after saving it continues normally
if total_palete_norte >= 29 or total_net_value_norte >= 2500000:
frete_expresso_norte = frete_expresso_norte + 1
else:
pass
display(pivot1)

else:
expedicao_norte = 'Não há volume para ser expedido à região Norte'


# Code under construction to insert inside the loop:
import openpyxl

# opening the spreadsheet with specific name
n = 0 # 0 = Norte / 1 = Nordeste / 2 = Centro Oeste / 3 = Sudeste / 4 = Sul
planilha_cx = openpyxl.load_workbook("Expedition.xlsx")
folhas = planilha_cx.sheetnames
folha = planilha_cx[folhas[n]]

# reading the cell
coluna = 4  # column D of the selected sheet
linha = 1  # start on the first line of the sheet
celula = folha.cell(linha, coluna).value

while celula != None:  # looping while cell in column D is not blank
celula = folha.cell(linha, coluna).value  # cell current value

if celula == None:  # filling the cell if it is blank
linha = linha + 2
folha.cell(row=linha, column=1).value = 'aaaaaaa'  # inserts the word 'aaaaa' but doesn't work with pivot1
planilha_cx.save("Expedition.xlsx")
break

else:  # while cell D1 is not blank, add +1 to row
linha = linha + 1
pass

Answer 1

问题是，当加载到工作簿时，您正在为每次加载创建一个具有新工作表名称的新对象(您正在解决这个问题)。关键是要让python/pandas知道，你正在上传不同页的同一本书。

试试下面的代码:

from openpyxl import load_workbook
book = load_workbook(your_destination_file)
writer = pd.ExcelWriter(your_destination_file, engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)  # tells 
pandas/python what the sheet names are
Your_dataframe.to_excel(writer, sheet_name=DesiredSheetname, startcol=3, 
freeze_panes=(1,0))
writer.save()

以上代码应该可以解决你的问题。