CODEWITHSUNDEEP
pythonpandasdataframepivot-tablemulti-index
Caiotru
Caiotru

Reputation: 325

Pandas how to calculate 2 columns in a pivot table with each other

I have the following dataframe as an example:

cars = {'Brand': ['Honda Civic','Toyota Corolla','Audi A4', 'Audi A4', 'Toyota Corolla', 'Audi A4', 'Honda Civic', 'Toyota Corolla'],
        'Price': [22000,25000,27000, 35069, 565, 384, 893, 45],
        'Speed': [20, 80, 90, 100, 52, 45, 793, 55],
        'Date' : ['13/02/2019', '13/02/2019', '18/01/2019', '18/01/2019', '13/02/2019', '13/02/2019', '18/01/2019', '18/01/2019']
       }

df = pd.DataFrame(cars)

and I have created my pivot table:

df_piv = pd.pivot_table(df, values=['Price', 'Speed'], index=['Brand'], columns=['Date'], aggfunc={'Price':max, 'Speed':[np.sum]})

enter image description here

What I need is to multiply the the max Price columns with the Speed sum columns for each date and arrive to have a single columns called let's say Multiplication.

I am able to do it using the df.groupby with the .agg().eval, however I would like to do it using a pivot table.

the expected output is the below

enter image description here

Upvotes: 1

Views: 887

Answers (1)

Mayank Porwal
Mayank Porwal

Reputation: 34056

You can first stack() the pivoted df. Then use hierarchical indexes for multiplication and can then pivot again:

In [1788]: x = df_piv.stack().reset_index()
In [1805]: x['val'] = x[('Price', 'max')] * x[('Speed', 'sum')]

In [1821]: ans = x.drop(['Price','Speed'], 1).pivot(index='Brand', columns='Date')
In [1822]: ans
Out[1822]: 
               Multiplication           
                                        
Date               13/02/2019 18/01/2019
Brand                                   
Audi A4                 17280    6663110
Honda Civic            440000     708149
Toyota Corolla        3300000       2475

Upvotes: 1

Related Questions