How to add a calculated ratio in a pivot table?

Question

I wish to add a calculated field (a ratio) to a pandas pivot table. Similar to this question (How to add calculated % to a pandas pivottable) I could not have my code to work.

I am trying to get the recency open rate % for emails. A simple ratio with a formula shown below: dst['perc'] = (dst['recency_opened'] / dst['recency_sent'])

 # My Pivot Table code: 
 emails = 
 pd.pivot_table(dst,'emails_opened','emails_sent','recency_opened', 
 'recency_sent', 'perc'],['segment', 'hcp_type'], aggfunc='sum', 
 fill_value=None, margins=True, dropna=True, margins_name='Total')

 emails

The result is this:

                perc    recency_opened  recency_sent
hcp_type            
Doctor          113.0   113             150
Nurse           33.0     33              37
Total           146.0   146             187

But my expected result should be:

                perc    recency_opened  recency_sent
hcp_type            
Doctor          0.753   113             150
Nurse           0.891    33              37
Total                   146             187

Alternativelly, I could also get by with a DataFrame (not necessarily a pivot table) because I just really wish to analyse the email recency open rate%.

you should add in the raw data for others to work with, your output doesn't match your code (unless I'm wrong) — Umar.H, Apr 22 '19 at 19:31
Possible duplicate of [Pandas\_Pivot table - making additional columns from division of merged columns](https://stackoverflow.com/questions/45632484/pandas-pivot-table-making-additional-columns-from-division-of-merged-columns) — Edeki Okoh, Apr 22 '19 at 20:10

score 0 · Accepted Answer · answered Apr 30 '19 at 19:40

0

Are you dividing opened by sent?

>>> import pandas as pd
>>> df = pd.DataFrame([[113, 150], [33, 37]],
                  columns=['opened', 'sent'], index=['Doctor', 'Nurse'])
>>> df['ratio (%)'] = df['opened']/df['sent']
>>> df

        opened  sent  ratio (%)
Doctor     113   150   0.753333
Nurse       33    37   0.891892

answered Apr 30 '19 at 19:40

Chris

1,287
12
31

Yes. Dividing Opened by sent. – Rodrigo Teixeira May 16 '19 at 08:40

How to add a calculated ratio in a pivot table?

1 Answers1