Using python loop to create multiple excel sheets in one excel workbook from one dataframe

Question

I have a dataframe as below:

user = pd.DataFrame({'User':['101','101','101','102','102','101','101','102','102','102'],'Country':['India','Japan','India','Brazil','Japan','UK','Austria','Japan','Singapore','UK'],'Count':[50,1,2,5,6,89,10.9,10,5,6]})

My desired outcome is to have multiple excel sheets for every 'User' in one excel workbook from the dataframe above using loop. For example:

User 101 will be in sheet named "101" while User 102 in a sheet named "102" inside the same workboook.

I tried to loop using the code below but it only loop the first user, 101.

for user in user['User'].unique():
    path = r"C:\Users\Desktop\df_user.xlsx"
    writer = pd.ExcelWriter(path, engine = 'xlsxwriter')
    user[user['User'] == user].to_excel(writer, sheet_name = f"{user}")
    writer.save()
    writer.close()

edited what i have tried using for loop.

rain123
– rain123

2019-09-30 07:57:23 +00:00
Commented Sep 30, 2019 at 7:57 — rain123
– rain123, Commented Sep 30, 2019 at 7:57

jezrael · Accepted Answer · 2019-09-30 08:02:27Z

1

Loop by groupby object and save to same excel file:

with pd.ExcelWriter('output.xlsx') as writer:
    for i, g in user.groupby('User'):
        g.to_excel(writer, sheet_name=i, index=False)

If is necessary remove column User:

with pd.ExcelWriter('output.xlsx') as writer:
    for i, g in user.groupby('User'):
        g.drop('User', axis=1).to_excel(writer, sheet_name=i, index=False)

In your solution is necessary change for user in user['User'].unique() to for u in user['User'].unique() for avoid overwrite DataFrame variable user, also define writer is better out of loop for avoid overwriting excel file:

path = "df_user.xlsx"
writer = pd.ExcelWriter(path, engine = 'xlsxwriter')
for u in user['User'].unique():
    user[user['User'] == u].to_excel(writer, sheet_name = f"{u}", index=False)
writer.save()
writer.close()

edited Sep 30, 2019 at 8:02

answered Sep 30, 2019 at 7:51

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using python loop to create multiple excel sheets in one excel workbook from one dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related