Python: Formating totals to showcase different formulas per row rather than just the total sums

Question

I have a large dataset with different rows that I created using formulas and the grouped by function

# Create new column "iCPI" 
grouped_sums['iCPI'] = grouped_sums['Budget Delivered'] / grouped_sums['Incr Conversions']

# Create new column "iCVR" 
grouped_sums['iCVR%'] = (grouped_sums['Incr Conversions'] / grouped_sums['Clicks']) * 100

# Create new column "CPC" 
grouped_sums['CPC'] = grouped_sums['Budget Delivered'] / grouped_sums['Clicks']

# Create new column "CPM" 
grouped_sums['CPM'] = (grouped_sums['Budget Delivered'] / grouped_sums['Impressions']) * 1000

I created a column named Total using this code grouped_sums.loc['Total'] = grouped_sums.sum() however this just takes the total sum of all the columns rather than taking into account the formulas I created up above. I need the total for each of the new columns to reflect the formula of the totals (ie. for iCPI, it needs to be the sum of budget delivered / sum of Incr Conversions

My problem is the output as I do have the columns showing iCPI and other metrics. However, for the total output, the iCPI is only taking the sum of all iCPI values. Here is what it looks like for me:

Total Output that sums all individual values

Answer 1

I do not fully understand your question but I try to help anyway.

ie. for iCPI, it needs to be the sum of budget delivered / sum of Incr Conversions

# Create new column "iCPI" 
grouped_sums['iCPI'] = grouped_sums['Budget Delivered'].sum() / grouped_sums['Incr Conversions'].sum()

In my opinion you need another DataFrame that only display the convertion rate and the other forumas that you need. Packing all in a single DataFrame can be confusing. If you need to export an excel to be shown to sales or business, try to put grouped_sums in a sheet, so that every group's details can be seen. Then create another sheet Formulas with the totals you need:

so something similar

# Create a new column "iCPI" 
results['iCPI'] = grouped_sums['Budget Delivered'].sum() / grouped_sums['Incr Conversions'].sum()

Finally, you export all:

with pd.ExcelWriter('fancy_report_for_business.xlsx') as writer:
    grouped_sums.to_excel(writer, index=False, sheet_name='grouped_sums')
    results.to_excel(writer, sheet_name='Formulas')

I hope this helps. In case try to explain your need in the comments.

p.s do not forget to upvote or accept if my answer is correct