Writing pandas dataframes to multiple excel files

Question

import pandas as pd
import numpy as np

file = '/Dummy.xlsx'

Customer = pd.read_excel(file, sheet_name=0)
Items = pd.read_excel(file, sheet_name=1)
Commission = pd.read_excel(file, sheet_name=2)
Price = pd.read_excel(file, sheet_name=3)
Sheet1 = pd.read_excel(file, sheet_name=4) 

Inner_join = pd.merge(Price,Sheet1,on = 'Item_ID', how='inner')

Inner_join = pd.merge(Price, Sheet1,on = 'Item_ID', how='inner').merge(Commission, on = 'Commission_ID')

joined_table = pd.merge(Inner_join, Customer, right_on = 'Customer_ID', left_on = 'Customer_ID_x', how = 'inner')

final_table = joined_table[['Date','Customer_Name', 'Customer_ID','Item_Name', 'Commission', 'Qty','Base_Price','Rate']]

calculated_commission = final_table[final_table.loc[:,'Base_Price'] < final_table.loc[:, 'Rate']]

calculated_commission['final_com'] = cal_com.loc[:, 'Qty'] * cal_com.loc[:, 'Commission']][2]

customers = calculated_commission['Customer_Name'].unique()

for i in customers:
    a = calculated_commission[calculated_commission['Customer_Name'].str.match(i)]
    a.to_excel(i+'.xlsx')

I'm trying to iterate over unique customer names and write them in different excel files and name that with the same name.

It created both files but writes data only on the second file which is 'Chef Themiya'

access below link for the dataset:

https://drive.google.com/file/d/1VWc_WoN1nTWiDKK1YDtIXtzaAGdgbfpl/view?usp=sharing

Please help

Add your original dataset as file or text, not just image. Add the code you used to generate the dataframe from the dataset. Add your expected output as file or text, not just description. — Joshua, Jun 22 '21 at 15:48
I have added the whole code I used and the link for dataset @Joshua — Gayan Priyadarshana, Jun 22 '21 at 16:20

fthomson · Accepted Answer · 2021-06-22T16:13:33.333

0

str.match isnt picking up the pattern for customer name since your string has parenthesis in it. Use this instead.

df = pd.DataFrame({'Customer': ['88 Chinese Restaurant (Pvt) Ltd', 'Chef Themiya'], 'data': [1,2]})
customers = df['Customer'].unique()
for i in customers:
    test = df[df['Customer']== i]
    print(test)

you could also use

df = pd.DataFrame({'Customer': ['88 Chinese Restaurant (Pvt) Ltd', 'Chef Themiya'], 'data': [1,2]})
customers = df['Customer'].unique()
for i in customers:
    test = df[df['Customer'].str.contains(i, regex=False)]
    print(test)

       Customer  data
0  88 Chinese Restaurant (Pvt) Ltd     1

       Customer  data
1  Chef Themiya     2

Posting your dataset as images makes your example difficult to reproduce. Look into creating minimal reproducable examples.

https://stackoverflow.com/help/minimal-reproducible-example

edited Jun 22 '21 at 16:13

answered Jun 22 '21 at 16:03

fthomson

773
3
9

I have added the whole code I used and the link for dataset. If I have a long list of customers, how to tackle that with your solution. – Gayan Priyadarshana Jun 22 '21 at 16:22
both of these will work with any number of customers. – fthomson Jun 22 '21 at 16:24
If I am to write them in different sheets of the same excel and rename the sheet with customer name, what should I do? I'm even trying to sum the fina_com column and mention at the end of that column. if you know how to do it, please help. – Gayan Priyadarshana Jun 22 '21 at 17:29
I think this thread should help you out https://stackoverflow.com/questions/42370977/how-to-save-a-new-sheet-in-an-existing-excel-file-using-pandas instead of naming the sheets x1, x2... replace them with the customer name – fthomson Jun 22 '21 at 17:49

Writing pandas dataframes to multiple excel files

1 Answers1