Optimizing a groupby agg function to return multiple result columns

Question

I have this dataframe;

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'Client':np.random.choice(['Customer_A', 'Customer_B'], 1000),
    'Product':np.random.choice( ['Guns', 'Ammo', 'Armour'], 1000),
    'Value':(np.random.randn(1000))
})
Categoricals = ['Client', 'Product']
df[Categoricals] = df[Categoricals].astype('category')
df = df.drop_duplicates()
df

And I want this result;

# Non-anonymous function for Anomaly limit
def Anomaly (x):
    Q3 = np.nanpercentile(x, q = 75)
    Q1 = np.nanpercentile(x, q = 25)
    IQR = (Q3 - Q1)
    return (Q3 + (IQR * 2.0))

# Non-anonymous function for CriticalAnomaly limit
def CriticalAnomaly (x):
    Q3 = np.nanpercentile(x, q = 75)
    Q1 = np.nanpercentile(x, q = 25)
    IQR = (Q3 - Q1)
    return (Q3 + (IQR * 3.0))


# Define metrics
Metrics = {'Value':['count', Anomaly, CriticalAnomaly]}

# Groupby has more than 1 grouping column, so agg can only accept non-anonymous functions
Limits = df.groupby(['Client', 'Product']).agg(Metrics)
Limits

But it's slow on large datasets because the functions "Anomaly" and "CriticalAnomaly" have to recalculate Q1, Q3 and IQR twice, instead of once. By combining both functions together makes it much faster. But the results are output into 1 column instead of 2.

# Combined anomaly functions
def CombinedAnom (x):
    Q3 = np.nanpercentile(x, q = 75)
    Q1 = np.nanpercentile(x, q = 25)
    IQR = (Q3 - Q1)
    Anomaly = (Q3 + (IQR * 2.0))
    CriticalAnomaly = (Q3 + (IQR * 3.0))
    return (Anomaly, CriticalAnomaly)

# Define metrics
Metrics = {'Value':['count', CombinedAnom]}

# Groupby has more than 1 grouping column, so agg can only accept non-anonymous functions
Limits = df.groupby(['Client', 'Product']).agg(Metrics)
Limits

How can I make a combined function so the results go into 2 columns?

Answer 1

If you use apply instead of agg, you can return a Series that gets unpacked into columns:

def f(g):
    return pd.Series({
        'c1': np.sum(g.b),
        'c2': np.prod(g.b)
    })

df = pd.DataFrame({'a': list('aabbcc'), 'b': [1,2,3,4,5,6]})
df.groupby('a').apply(f)

This goes from:

   a  b
0  a  1
1  a  2
2  b  3
3  b  4
4  c  5
5  c  6

to

   c1  c2
a        
a   3   2
b   7  12
c  11  30