如何以优雅的功能将Panda DataFrames导出到Excel文件

Question

我正在尝试编写一个函数，该函数接受数据和文件名作为参数，前者是要保存在后者名称下的数据。我想把它放在一个函数中，而不是像通过test3使OutWriter3工作那样采用列表理解。

import numpy as np
import pandas as pd
data_a = np.random.randint(100, size=21)
data_b = np.random.randint(200, size=21)
def OutWriter1(data, filename):
for d, f in zip(data, filename):
out = pd.DataFrame(d)
return out.to_excel(f, header=False, index=False)
def OutWriter2(data, filename):
out = []
for d, f in zip(data, filename):
df = pd.DataFrame(d)
out.append(df)
out = pd.DataFrame(out)
return out.to_excel(f, header=False, index=False)
def OutWriter3(data, filename):
out = pd.DataFrame(data)
return out.to_excel(filename, header=False, index=False)
test1 = OutWriter1([data_a, data_b], ['data_a_1.xlsx', 'data_b_1.xlsx'])
test2 = OutWriter2([data_a, data_b], ['data_a_2.xlsx', 'data_b_2.xlsx'])
test3 = [OutWriter3(i, j) for i, j in zip([data_a, data_b], ['data_a_3.xlsx', 'data_b_3.xlsx'])]

来自OutWriter1的data_a_1.xlsx是正确的，但data_b_1.xlsx不存在，data_a_2.xlsx完全错误，data_b_2.xlsx也不存在。但是，data_a_3.xlsx和data_b_3.xlsx是正确的。

受另一个问题的启发，我还尝试将data_a和data_b保存为一个Excel文件中的表格，但没有任何运气(AttributeError: 'list' object has no attribute 'write'(。

def OutWriter4(data, filename):
data = pd.DataFrame(data)
with pd.ExcelWriter(filename) as writer:
for n, df in enumerate(data):
df.to_excel(writer, 'sheet%s' % n)
writer.save()
test4 = OutWriter4([data_a, data_b], ['data_a_4.xlsx', 'data_b_3.xlsx'])

• 有没有一种优雅的方法来创建一个函数，该函数可以创建提供数据和文件名的Excel文件
• 是否还有一种优雅的方法可以创建一个函数，将不同的数据写入单个Excel文件中指定的工作表

Answer 1

解决方案

您可以使用以下代码将多个数据帧写入单个excel文件，或者将每个数据帧写入一个excel文件。

target = 'single_file.xlsx'
targets = ['mult_1.xlsx', 'mult_2.xlsx', 'mult_3.xlsx']
# df1 = pd.DataFrame(data_a)
# df2 = pd.DataFrame(data_b)
# df3 = pd.DataFrame(data_c)
# dfs = [pd.DataFrame(x) for x in [data_a, data_b]] # >> in your case
dfs = [df1, df2, df3]
# to write to a single file
write_to_excel(targets = target, dfs = dfs, verbose=1)
# to write to multiple files
write_to_excel(targets = targets, dfs = dfs, verbose=1)

自定义函数

def write_to_excel(targets, dfs: list, verbose=1):
"""Writes single of multiple dataframes to either a single or multiple excel files.
targets: str or list of str --> path(s) excel files
dfs: list of dataframes
Example
-------
target = 'single_file.xlsx'
targets = ['mult_1.xlsx', 'mult_2.xlsx', 'mult_3.xlsx']
dfs = [df1, df2, df3]
# to write to a single file
write_to_excel(targets = target, dfs = dfs, verbose=1)
# to write to a multiple files
write_to_excel(targets = targets, dfs = dfs, verbose=1)
"""
if not isinstance(targets, list):
targets = [targets]
writer_scheme = 'single' if len(target)==1 else 'multi'
if verbose>=1:
print(f'excel-writer-scheme: {writer_scheme}-file(s)')
if writer_scheme == 'single':
with pd.ExcelWriter(targets[0]) as writer:
for i, df in enumerate(dfs):  
df.to_excel(writer, sheet_name=f'Sheet_{i+1}')
else:
for df, target in zip(dfs, targets):
df.to_excel(target, sheet_name='data')

参考文献

1. https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html