8

I have created a pandas data frame and then converted it into pivot table.

My pivot table looks like this:

Operators   TotalCB     Qd(cb)  Autopass(cb)
Aircel India    55        11       44
Airtel Ghana    20        17        3
Airtel India    41         9        9
Airtel Kenya    9          4        5
Airtel Nigeria  24        17        7
AT&T USA        18        10        8

I was wondering how to add calculated columns so that I get my pivot table with Autopass% (Autopass(cb)/TotalCB*100) just like we are able to create them in Excel using calculated field option.

I want my pivot table output to be something like below:

Operators   TotalCB     Qd(cb)  Autopass(cb)    Qd(cb)% Autopass(cb)%
Aircel India    55          11    44             20%     80%
Airtel Ghana    20          17     3             85%     15%
Airtel India    41          29     9             71%     22%
Airtel Kenya     9           4     5             44%     56%
AT&T USA        18          10     8             56%     44%

How do I define the function which calculates the percentage columns and how to apply that function to my two columns namely Qd(cb) and Autopass(cb) to give me additional calculated columns

Korem
  • 11,383
  • 7
  • 55
  • 72
Pynewbie
  • 150
  • 1
  • 1
  • 5
  • 14
    Clearly some code has been written @Tony: You don't get to the stage of making a pivot table in pandas without some code. Your comment was not helpful. – Frames Catherine White Oct 11 '14 at 12:37
  • Thanks Oxinabox. @Tony Yes I imported the data using read_csv into a dataframe and the used the pivot_table function to create the pivot table. – Pynewbie Oct 11 '14 at 13:16

1 Answers1

7

This should do it, assuming data is your pivoted dataframe:

data['Autopass(cb)%'] = data['Autopass(cb)'] / data['TotalCB'] * 100
data['Qd(cb)%'] = data['Qd(cb)'] / data['TotalCB'] * 100

Adding a new column to a dataframe is as simple as df['colname'] = new_series. Here we assign it with your requested function, when we do it as a vector operation it creates a new series.

Korem
  • 11,383
  • 7
  • 55
  • 72
  • In my case the pivot returns a multi index for the columns, if this is so, you need to either "flatten" the header: df.columns = df.columns.map('{0[0]} {0[1]}'.format) or adress the columns like that: df = df.join(df[['bar']].div(df['baz']).rename(columns={'bar':'foo'})) (from https://stackoverflow.com/a/45189391/2360229) – n.r. Mar 02 '22 at 06:08