CODEWITHSUNDEEP
pythonexcelpandasdictionary
joe-cormier
joe-cormier

Reputation: 53

Save Dictionary of Multiple Series to Excel

I have a dataframe with 101 columns and I want to look at the distribution of each variable in my DataFrame. Using Pandas value_counts I have created a dictionary of several series with multiple lengths. Each series has its own key.

First I do:

    out={}
    for c in df.columns:
    out[c]=df[c].value_counts(dropna=False).fillna(0)

So, out is a dictionary with size 101. Within out is a list of series with various sizes. 

Key     |  Type  | Size |  Value
Key1      Series   (12,)   class 'pandas.core.series.Series'
Key2      Series   (7,)    class 'pandas.core.series.Series'
Key3      Series   (24,)   class 'pandas.core.series.Series'
.
.
.
Key101

Each Key is unique. I want to save each of these series to an Excel file. This answer is close and will work for the first key in the loop, but it won't continue on to the next key in the dictionary. This is what I have now:

for key in out.keys():
    s=out[key]
    name=key[:30]
    s.to_excel('xlfile.xlsx', sheet_name=name)

I only keep the first 30 characters because that is the limit in excel for a sheet name. I don't necessary need them to have their own sheet, I would rather them all be saved to a single sheet by column, but this is the closest I can get to saving them. Obviously a newbie, so if there is a better approach to my fundamental question, that would be awesome too.

I'm open to any suggestions, thanks for your time.

Upvotes: 2

Views: 1241

Answers (2)

jezrael
jezrael

Reputation: 863226

Need ExcelWriter:

writer = pd.ExcelWriter('xlfile.xlsx')
for key in out.keys():
    out[key].to_excel(writer, sheet_name=key[:30])
writer.save()

Or if is not necessary create dict, write it in first loop (also advantage is same order of sheet names as columns of DataFrame):

writer = pd.ExcelWriter('xlfile.xlsx')
for c in df.columns:
    df[c].value_counts(dropna=False).fillna(0).to_excel(writer, sheet_name=c[:30])
writer.save()

Upvotes: 1

pypypy
pypypy

Reputation: 1105

You need to create an excel writer and pass that into the loop i.e.

>>> writer = pd.ExcelWriter('output.xlsx')
>>> df1.to_excel(writer,'Sheet1')
>>> df2.to_excel(writer,'Sheet2')
>>> writer.save()

Refer here for more

Upvotes: 0

Related Questions