0

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

Chris Peng
  • 1
  • 1

1 Answers1

0

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

Sonny Monti
  • 58
  • 8
  • Hello, I added more information in the above post, but basically the total output is just summing all individual values within the rows. I have the metrics all in columns already, the issue would be trying to get the total iCPI to show on the 'total' row. Here is an image of my current output: https://i.stack.imgur.com/rbHfR.png – Chris Peng Mar 01 '23 at 15:53
  • When I try to use your code: # Create a new column "iCPI" results['iCPI'] = grouped_sums['Budget Delivered'].sum() / grouped_sums['Incr Conversions'].sum() It sets all the iCPI values to the total iCPI rather than just the 'total' row. So for example, all rows show 42.77 when in reality only the 'total' row should show that. – Chris Peng Mar 01 '23 at 16:26
  • ok, I still have difficulties to understand what you really need so if you want to sum by row formulas use ```grouped_sums['Total'] = grouped_sums.apply(lambda x: x['iCPI']+x['iCVR%']+x['CPC']+x['CPM'], axis=1)```. In any case you can use which column you want to create the total, just use ``` df.apply(lambda x: , axis=1)``` axis=1 means that you are working on individual rows – Sonny Monti Mar 01 '23 at 16:38
  • Can you provide an example of what you mean by ()? – Chris Peng Mar 01 '23 at 17:07
  • ```grouped_sums['Total'] = grouped_sums.apply(lambda x: x['iCPI']+x['iCVR%']+x['CPC']+x['CPM'], axis=1)``` basically *x* is the row, you can access attributes as usual, for example to access row's iCPI *x['iCPI']* – Sonny Monti Mar 01 '23 at 17:08