将字典保存到.XLSX中 [英] Saving a dictionary into an .XLSX

问题描述

使用Python + Pandas，是否可以快速方便地将一个Dict（包括文件名的密钥和数据列的数据组合）保存到.XLSX文件中？

Using Python + Pandas, is there a quick and easy way to save a Dict (consisting of file names for the keys and several columns of data for the values) into an .XLSX file?

经过一番研究，我尝试将其转换为Pandas DataFrame（因为我知道您可以使用Pandas DataFrame写入.XLSX文件），使用以下代码： / p>

After some research, I tried converting it into a Pandas DataFrame (because I know you can write to an .XLSX file from a Pandas DataFrame) using the following code:

import pandas as pd
import glob
f_list = glob.glob("C:\\Users\\me\\dt\\xx\\*.xlsx")

sheets = {f: pd.read_excel(f) for f in f_list}
new_df = pd.DataFrame.from_dict(sheets, orient = "index")
new_df.to_excel("ALL_RUCDR_DATA.xlsx")

但是我收到这个错误：

TypeError：不支持的类型（pandas.core .frame.DataFrame'）在write（）中。

TypeError: Unsupported type (class 'pandas.core.frame.DataFrame') in write().

我知道它会成功创建字典，似乎创建了DataFrame但是为什么不会创建文件？

I know it will create the dictionary successfully, and it seems to create the DataFrame, but why won't it create the file?

推荐答案

docs 建议您执行以下操作：

The docs suggest you do this with the following:

with pd.ExcelWriter('path_to_file.xlsx') as writer:
    for (sheet_name, df) in sheets.items():
        df.to_excel(writer, sheet_name=sheet_name)

---

错误发生在一个 from_dict DataFrames的dict将创建一个奇怪的DataFrame，其中每个元素都是一个DataFrame：

---

The error occurs as from_dict on a dict of DataFrames creates a strange DataFrame where each element is a DataFrame:

In [11]: sheets = {"a": pd.DataFrame([[1]], columns=["A"]), "b": pd.DataFrame([[2], [3]], columns=["B"])}

In [12]: pd.DataFrame.from_dict(sheets, orient='index')
Out[12]:
                0
b     B
0  2
1  3
a          A
0  1

In [13]: pd.DataFrame.from_dict(sheets, orient='index').applymap(type)
Out[13]:
                                       0
b  <class 'pandas.core.frame.DataFrame'>
a  <class 'pandas.core.frame.DataFrame'>

这不会映射到Excel表，因为期望单个值（例如int / float / string）作为元素。

This isn't going to map to a excel sheet as that expects single values (e.g. int/float/string) as the elements.

如果要将字典中的数据合并/连接/连接到单个文件中，请检查文档的合并部分（根据大熊猫DataFrames而不是电子表格考虑这一点）。

If you want to merge/concat/join the data from your dictionary into a single file, checkout the merging section of the docs (think of this in terms of pandas DataFrames rather than spreadsheets).

这篇关于将字典保存到.XLSX中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！