将多个数据框多次保存到多个Excel工作表中? [英] Saving multiple dataframes to multiple excel sheets multiple times?

问题描述

我具有将多个数据框作为多个表保存到单个excel工作簿工作表的功能:

I have a function to save multiple dataframes as multiple tables to single excel workbook sheet:

def multiple_dfs(df_list, sheets, file_name, spaces):
    writer = pd.ExcelWriter(file_name,engine='xlsxwriter')   
    row = 0
    for dataframe in df_list:
        dataframe.to_excel(writer,sheet_name=sheets,startrow=row , startcol=0)   
        row = row + len(dataframe.index) + spaces + 1
    writer.save()

如果我多次调用此函数以将多个表写入多张工作表，则最终只能得到一张工作簿和一张工作表，即最后一个被调用的工作表:

If I call this function multiple times to write multiple tables to multiple sheets, I end up with just one workbook and one sheet, the one that was called last:

multiple_dfs(dfs_gfk, 'GFK', 'file_of_tables.xlsx', 1)
multiple_dfs(dfs_top, 'TOP', 'file_of_tables.xlsx', 1)
multiple_dfs(dfs_all, 'Total', 'file_of_tables.xlsx', 1)

所以最后我只有 file_of_tables 个工作簿，并且只有 Total 个工作表.我知道这是一个简单的问题，但是以某种方式，我只是想不出任何优雅的解决方案.有人可以帮忙吗?

So in the end I only have file_of_tables workbook with only Total sheet. I know it's a simple problem, but somehow I just can not think of any elegant solution to this. Can anyone help?

推荐答案

来自 pandas.ExcelWriter 在创建 ExcelWriter 类的实例时， mode 关键字很重要.

The mode keyword matters when you're creating an instance of the ExcelWriter class.

mode ='w'为文件分配空间(在您调用 .save()或 .close())如果没有一个文件，或者如果已经存在一个文件，则覆盖一个文件.

The mode='w' allocates space for the file (which it creates once you call .save() or .close()) when there isn't one or overwrites one if there is already an existing file.

mode ='a'假定存在一个现有文件，并且追加到该文件上.如果要保留代码的结构，则必须添加如下这样的小行:

The mode='a' assumes there's an existing file and appends on to that file. If you want to keep the structure of your code, you have to add a small line like so:

import pandas as pd
import os

def multiple_dfs(df_list, sheets, file_name, spaces):
    arg_mode = 'a' if file_name in os.getcwd() else 'w' # line added
    writer = pd.ExcelWriter(file_name, engine='xlsxwriter', mode=arg_mode) # added mode argument
    row = 0

    for dataframe in df_list:
        dataframe.to_excel(writer,sheet_name=sheets,startrow=row , startcol=0)   
        row = row + len(dataframe.index) + spaces + 1
    writer.save()

如果您随后运行以下一系列代码:

if you then run the following series of code(s):

multiple_dfs(dfs_gfk, 'GFK', 'file_of_tables.xlsx', 1)
multiple_dfs(dfs_top, 'TOP', 'file_of_tables.xlsx', 1)
multiple_dfs(dfs_all, 'Total', 'file_of_tables.xlsx', 1)

最后一个(和第二个函数调用)不会覆盖当前写入那里的数据.取而代之的是，第一个函数调用 创建 ，然后第二个和第三个函数调用 append 该数据.现在，您的功能应该可以使用了.

the last (and second function call) will not overwrite the data currently written in there. Instead what happens is that the first function call creates the file and then the second and third function call append to that data. Now, your function should work.

这篇关于将多个数据框多次保存到多个Excel工作表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！