CODEWITHSUNDEEP
pythonexcel
rain123
rain123

Reputation: 273

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

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

Upvotes: 0

Views: 2971

Answers (1)

jezrael
jezrael

Reputation: 863611

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

Upvotes: 1

Related Questions