Saving a dictionary into an .XLSX

Question

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?

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")

but I get this error:

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

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

Answer 1

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)

---

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'>

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

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).