How to calculate the product wise sum in a dataframe and write it in xlsxwriter worksheet below the Sub Total?

Question

I want to calculate the product wise sum and display it in excel sheet using xlsxwriter.Input dataframe is shown below:

final_fact = pd.DataFrame({'factory': ['kerala', 'kerala', 'kerala', 'delhi', 'delhi', 'goa', 'goa'],
           'plant': ['', '', '', '', '', '', ''],
           'market': ['', '', '', '', '', '', ''],
           'product': ['A', 'B', 'C', 'A', 'B', 'A', 'B'],
           'uom': ['l', 'l', 'l', 'l', 'l', 'l', 'l'],
           'BP4-2023': [4, 4, 5, 6, 4, 5, 5],
           'RE4-2023': [7, 7, 8, 8, 7, 8, 8],
           'BP5-2023': [4, 4, 5, 6, 4, 5, 5],
           'RE5-2023': [7, 7, 8, 8, 7, 8, 8]})

I want output like below in the excel sheet below Sub Total Row:

Product wise Total                                 
 A                            l    15        23       15        23
 B                            l    13        22       13        22
 C                            l    5         8        5         8

Values should comes under corresponding BP and RE.

I used below code to implement this.Till Sub Total part (including Sub Total row),data shown in excel sheet is fine.But remaining part is not correct.

code:

                factory_value = final_fact['factory'].unique()
                total_dataframe = pd.DataFrame(columns=final_fact.columns)
                total = []
                for fact_val in factory_value:
                    factory_data = final_fact[final_fact['factory'] == fact_val]
                    factory_data = factory_data.sort_values(by=['product'], ascending=True)
                    factory_data.loc['plant_total'] = factory_data.select_dtypes(include=['float64']).sum()
                    factory_data["product"] = factory_data["product"].replace(np.nan, "Plant Total")
                    factory_data = factory_data.fillna('')
                    factory_data = factory_data.round(2)

                    plant_total_row = factory_data.loc[factory_data['product'] == "Plant Total"]
                    total.append(plant_total_row)
                    output = factory_data.values.tolist()

                    row_num += 1
                    for data_item in output:
                        plant_total = "Plant Total"
                        if plant_total in data_item:
                            for col_num in range(len(data_item)):
                                worksheet.write(row_num, col_num, data_item[col_num], header_right_format)
                            row_num += 1
                        else:
                            for col_num in range(len(data_item)):
                                worksheet.write(row_num, col_num, data_item[col_num])
                            row_num += 1

                for i in total:
                    total_dataframe = pd.concat([total_dataframe, i], ignore_index=True)
                ignore = ['factory', 'planttype', 'market', 'product', 'uom']
                total_dataframe = (total_dataframe.set_index(ignore, append=True).astype(float).reset_index(ignore))
                total_dataframe.loc['sub_total'] = total_dataframe.select_dtypes(include=['float64']).sum()
                total_dataframe["product"] = total_dataframe["product"].replace(np.nan, "Sub Total")
                total_dataframe = total_dataframe.fillna('')
                total_dataframe = total_dataframe.round(2)
                print(total_dataframe)
                output = total_dataframe.values.tolist()

                for data_item in output:
                    total_production = "Sub Total"
                    if total_production in data_item:
                        for col_num in range(len(data_item)):
                            worksheet.write(row_num, col_num, data_item[col_num], header_right_format)
                        row_num += 1

                product_wise_total = final_fact.groupby(['product', 'uom']).sum()
                product_wise_output = product_wise_total.values.tolist()
                print(product_wise_total)

                for product_data_item in product_wise_output:
                    for product_wise_col_num in range(len(product_data_item)):
                        worksheet.write(row_num, product_wise_col_num, product_data_item[product_wise_col_num])
                        row_num += 1

Can anyone suggest a solution to solve this issue?

How should looks final data in excel? – jezrael May 05 '23 at 06:21 — jezrael, May 05 '23 at 06:21

score 0 · Answer 1 · answered May 05 '23 at 06:03

0

if i am understanding correctly, you want to calculate the sum of the values for each letter in column "product"

if so, you can simply use the groupby function of pandas (docs):

final_fact.groupby(by='product').sum()

the result is then:

product BP4-2023    RE4-2023    BP5-2023    RE5-2023
A       15          23          15          23
B       13          22          13          22
C       5           8           5           8

answered May 05 '23 at 06:03

coco18

836
8
18

Yop, in question it is `product_wise_total = final_fact.groupby(['product', 'uom']).sum()` I think problem OP is write to excel if some values written before. – jezrael May 05 '23 at 06:05
2

could be. It is not realy clrear, what OP wants – coco18 May 05 '23 at 06:20

score 0 · Answer 2 · answered May 05 '23 at 06:25

I think you can create all necessary DataFrames and then write to excel by multiple_dfs:

total_dataframe = (final_fact.groupby('factory')
                             .sum()
                             .assign(plant='Plant Total')
                             .set_index('plant', append=True)
                             .reset_index())
print(total_dataframe)
  factory        plant  BP4-2023  RE4-2023  BP5-2023  RE5-2023
0   delhi  Plant Total        10        15        10        15
1     goa  Plant Total        10        16        10        16
2  kerala  Plant Total        13        22        13        22 

product_wise_total = (final_fact.groupby(['product', 'uom'])
                                .sum()
                                .assign(plant='Sub Total')
                                .set_index('plant', append=True)
                                .reset_index())
print (product_wise_total)
  product uom      plant  BP4-2023  RE4-2023  BP5-2023  RE5-2023
0       A   l  Sub Total        15        23        15        23
1       B   l  Sub Total        13        22        13        22
2       C   l  Sub Total         5         8         5         8

# funtion
#https://stackoverflow.com/a/33004253/2901002
def multiple_dfs(df_list, sheets, file_name, spaces):
    writer = pd.ExcelWriter(file_name,engine='xlsxwriter')   
    row = 0
    for dataframe in df_list:
        dataframe.to_excel(writer,sheet_name=sheets,startrow=row , startcol=0)   
        row = row + len(dataframe.index) + spaces + 1
    writer.save()

# list of dataframes
dfs = [final_fact,total_dataframe,product_wise_total]

# run function
multiple_dfs(dfs, 'New', 'test1.xlsx', 1)

I need 'factory', 'planttype', 'market' column blank and show products such as A,B,C in product column and show the value sum in corresponding BP and RE columns@jezrael — AbinBenny, May 05 '23 at 07:12
@AbinBenny - How looks expected ouput? Can you add to question? — jezrael, May 05 '23 at 08:29

How to calculate the product wise sum in a dataframe and write it in xlsxwriter worksheet below the Sub Total?

2 Answers2