CODEWITHSUNDEEP
pythonexcelpandasdictionary
Anthony
Anthony

Reputation: 111

Write dictionary of dataframes to separate excel sheets

I have a dictionary containing a dataframe for each state in 'data.csv.'

df=pd.read_csv('data.csv')

dict_of_st = {k: v for k, v in df.groupby('Preferred State/Province')}

I would like to write each dataframe to a separate excel sheet in an workbook that already exists ('test.xlsx').

I tried using a for loop and load workbook

from openpyxl import load_workbook

book = load_workbook('test.xlsx')
writer = pd.ExcelWriter('test.xlsx', engine='openpyxl') 
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

for i in dict_of_st:
    i.to_excel(writer, sheet_name=i)

writer.save()

but jupyter notebook raises this error:

AttributeError                            Traceback (most recent call last)
<ipython-input-8-c1ba1b4d53d8> in <module>
      7 
      8 for i in dict_of_st:
----> 9     i.to_excel(writer, sheet_name=i)
     10 
     11 writer.save()

AttributeError: 'str' object has no attribute 'to_excel'

Upvotes: 4

Views: 11961

Answers (1)

DeepSpace
DeepSpace

Reputation: 81604

for x in some_dict iterates over the keys. You should explicitly iterate over items() which returns key-value pairs:

for df_name, df in dict_of_st.items():
    df.to_excel(writer, sheet_name=df_name)

Upvotes: 13

Related Questions