Appending multiple dataframes to excel on different sheets

Question

I would like to add multiple dataframes from a dictionary to one excel file and each dataframe should be written in a sheet. Sheen name is actually the key of the dictionary. The following code

for key in df_dict:
    df = df_dict[key]
    df.to_excel('data-frames.xlsx', sheet_name=key, index=False)

only writes the last dataframe. So, I only see one sheet. On the other hand the append_df_to_excel() method which is described here doesn't works. I get the error that there is no such method, although the pandas version is 1.4.2.

...
AttributeError: 'DataFrame' object has no attribute 'append_df_to_excel'

In [2]: import pandas as pd
In [2]: print(pd.__version__)
1.4.2

How can I fix that?

score 1 · Accepted Answer · answered May 28 '22 at 17:48

1

Could you try this code?

for key in df_dict:
    df = df_dict[key].copy()
    with pd.ExcelWriter('data-frames.xlsx', mode='a') as writer:  
           df.to_excel(writer, sheet_name=key)

answered May 28 '22 at 17:48

WithThee

79
4

score 1 · Answer 2 · answered May 28 '22 at 17:48

Here's a way to do what you've asked:

import pandas as pd
df_dict = {f'key_{i}':pd.DataFrame([[i+1,i+2,i+3],['a','b','c']], columns=['first','second','third']) for i in range(4)}
[print(f'{k}:\n{df}') for k, df in df_dict.items()]
with pd.ExcelWriter('TestOutput.xlsx') as writer:
    for sheet, df in df_dict.items():
        df.to_excel(writer, index=None, sheet_name=sheet)

Input

key_0:
  first second third
0     1      2     3
1     a      b     c
key_1:
  first second third
0     2      3     4
1     a      b     c
key_2:
  first second third
0     3      4     5
1     a      b     c
key_3:
  first second third
0     4      5     6
1     a      b     c

Output

Excel file TestOutput.xlsx with four sheets named key_1, key_1, key_3, and key_4, each containing 3 columns named first, second, and third with two rows of data.

Appending multiple dataframes to excel on different sheets

2 Answers2