[Python] Apply different aggregate functions to different columns of a pandas dataframe, and...

The issue


In SQL it is very easy to apply different aggregate functions to different columns, e.g. :

select item, sum(a) as [sum of a], avg(b) as [avg of b], min(c) as [min of c]


In Python, not so much. For a simple groupby, this answer provides an elegant and pythonic way to do it.

Desired output


The answer linked above shows calculations ( sum of a, weighted avg of b, etc) for each city. Now I need to add another dimension - let's call it colour - and show the intersection / pivot / crosstab of city and colour. I want to create the two tables below:



What I have tried (with a toy example)


Using groupby() and unstack() gives me Version 2. I am not sure how to obtain version 1 of the screenshot above. See toy example below.

import pandas as pd
import numpy as np

np.random.seed(100) # so results are always the same
df = pd.DataFrame(columns =['a','b','c','d'], data = np.random.rand(300,4))
df['city'] = np.repeat(['London','New York','Buenos Aires'], 100)
df['colour'] = np.random.choice(['green','red'],300)


def func(x, df):
# func() gets called within a lambda function; x is the row, df is the entire table
s_dict = {}

s_dict['sum of a'] = x['a'].sum()
s_dict['% of a'] = x['a'].sum() / df['a'].sum() if df['a'].sum() !=0 else np.nan

s_dict['avg of b'] = x['b'].mean()

s_dict['weighted avg of a, weighted by b'] = ( x['a'] * x['b']).sum() / x['b'].sum() if x['b'].sum() >0 else np.nan

s_dict['sum of c'] = x['c'].sum()
s_dict['sum of d'] = x['d'].sum()

return pd.Series( s_dict )

out = df.groupby(['city','colour']).apply(lambda x: func(x,df)).unstack()

Why this is not a duplicate question


The answer linked above does not address the case of an additional dimension.

Most of the examples I have seen with pivot() and crosstab() do not contain custom functions, but more basic functions like count, sum, average. There are examples with custom functions, but not similar to my case, e.g. operating on one field only, not 2 (see here ) or without the bi-dimensional aspect (repeat the calculation for each colour) - e.g. here If you have found a question which is more similar to my case, I'd appreciate it if you could first flag it in a comment or answer, as it is not uncommon for questions to be erroneously marked as duplicate. Thank you.

Continue reading...