How to create multiple data frames & write it to excel file-CodePudding

I have two data frames and joined the data with left join from the column "country"

i need to create a separate table in excel for each 4 countries from the joined dataframes as per the attached format.

Please advise how can i achieve this ?

please advise how can i achieve this?

Import Pandas as pd
import numpy as np
file = pd.ExcelFile(r"p:\test\sample.xlsx")
df1 = pd.read_excel(file, 'sample1')
df2 = pd.read_excel(file, 'sample2')
df3 = (pd.merge(df1, df2, left_on='country', right_on='Country', how='left').drop.('amount', axis=1))
n = len(pd.unique(df3['country']))`

CodePudding user response：

You can loop on df3['Country'].unique():

for country in df3['Country'].unique():
   df_ = df3[df3['Country'] == country]
   df_to_excel(f'path_to_output_{country}.xlsx',index=False)

CodePudding user response：

You can use df.groupby:

for country, g in df.groupby("country"):
    g.to_excel(f"file_{country}.xls", index=False)