I am new to python programming, and I am trying to think how to create multiple excels from a data frame. I have a Pandas data frame as shown below,
Invoice No. | Voucher ID |
---|---|
MHI000000038710 | 100039 |
MHI000000038711 | 100043 |
MHI000000038712 | 100043 |
I am trying to create excels for every Invoice No. from data frame. For the above example, the output would be 3 excels named after each Invoice No. (i.e., MHI000000038710, MHI000000038711, MHI000000038712).
And each excel should contains rows grouped by Voucher ID
Excel 1(MHI000000038710.xlsx): -
Invoice No. | Voucher ID |
---|---|
MHI000000038710 | 100039 |
Excel 2(MHI000000038711.xlsx): -
Invoice No. | Voucher ID |
---|---|
MHI000000038711 | 100043 |
MHI000000038712 | 100043 |
Excel 3(MHI000000038712.xlsx): -
Invoice No. | Voucher ID |
---|---|
MHI000000038711 | 100043 |
MHI000000038712 | 100043 |
CodePudding user response:
- First iterate over a list of unique voucher IDs and select only rows matching this ID.
- For each temporary dataframe (
df_singleID
) created this way, iterate over the unique Invoice No. and use this name to create your output file name:
Assuming that your pandas dataframe is named df
, this can be done as shown below:
for myid in df['VoucherID'].unique()
df_singleID = df[df['VoucherID']==myid]
for myinvoice in df_singleID['Invoice No.'].unique():
df_singleID.to_excel('./' str(myinvoice) '.xlsx')
CodePudding user response:
i believe this should work:
for ind, row in df.iterrows():
df.loc[df["Voucher ID"] == row["Voucher ID"]].to_excel(f'{row["Invoice No."]}.xlsx')