Create Grand Mean Centered Variables by Group Means in Pandas

user_12096670 0
Viewed 296

I am trying to create grand mean centered variables by groups.

The sample data is:

import pandas as pd
import numpy as np

dat = {
    'group': ['1', '1', '1', '2', '2', '1', '2'],
    'age': [40, 29, 34, 35, 37, 32, 36],
    'weight': [150, 175, 135, 125, 189, 178, 137],
    'score': [98.0, 77.0, 88.0, 78.0, 78.0, 85.0, 84.0]
    }
df = pd.DataFrame(data=dat)

I am trying to write a function that will estimate the grand mean centered variables by groups for all the variables in the dataset. My code to attempt that is below:

def group_mean_centered(x):
    
    d = []
    
    d.append(x.groupby(x.iloc[:, 0]).transform('mean') - x.iloc[:,0:].mean())
    
    d = np.asarray(d)
    
    d_ = d.reshape(-1,len(x.columns))
        
    dd = pd.DataFrame(d_, columns=[list(x.columns.values)])
    
    return dd

However, when I do that, it returns a dataframe where the grouping variable, group, is also transformed, instead of getting the groups as in the brackets []

     group           age         weight     score
0   -0.428571 [1]   -0.964286    3.928571    3.0
1   -0.428571 [1]   -0.964286    3.928571    3.0
2   -0.428571 [1]   -0.964286    3.928571    3.0
3    0.571429 [2]    1.285714   -5.238095   -4.0
4    0.571429 [2]    1.285714   -5.238095   -4.0
5   -0.428571 [1]   -0.964286    3.928571    3.0
6    0.571429 [2]    1.285714   -5.238095   -4.0

Just looking for some ideas on how to fix the code to keep the grouping variable, group, as is instead of transforming it.

pandas-groupby pandas dataframe aggregate python-3.x
3 Answers

If you are okay for another solution , what you do can also be done by groupby.transform directly.

out = ((df.groupby("group").transform("mean")-df.mean())
       .fillna({"group":df['group']}).reindex(columns=df.columns))

print(out)

  group       age    weight  score
0     1 -0.964286  3.928571    3.0
1     1 -0.964286  3.928571    3.0
2     1 -0.964286  3.928571    3.0
3     2  1.285714 -5.238095   -4.0
4     2  1.285714 -5.238095   -4.0
5     1 -0.964286  3.928571    3.0
6     2  1.285714 -5.238095   -4.0
user_98406370

We can do groupby + transform to calculate group mean then subtract the grand mean of numeric only columns

df[['group']].join(df.groupby('group').transform('mean') - df.mean(numeric_only=True))

Alternatively we can set the index of the dataframe to group, then groupby and transform on level=0 to calculate the group mean then subtract this transformed group mean from the grad mean to get the result.

s = df.set_index('group')
s.groupby(level=0).transform('mean').sub(s.mean()).reset_index()

  group       age    weight  score
0     1 -0.964286  3.928571    3.0
1     1 -0.964286  3.928571    3.0
2     1 -0.964286  3.928571    3.0
3     2  1.285714 -5.238095   -4.0
4     2  1.285714 -5.238095   -4.0
5     1 -0.964286  3.928571    3.0
6     2  1.285714 -5.238095   -4.0
user_128331660

I added the line: dd.iloc[:,0]=x.iloc[:,0] to your function. So the full function is:

def group_mean_centered(x):
    
    d = []
    
    d.append(x.groupby(x.iloc[:, 0]).transform('mean') - x.iloc[:,0:].mean())
    
    d = np.asarray(d)
    
    d_ = d.reshape(-1,len(x.columns))
        
    dd = pd.DataFrame(d_, columns=[list(x.columns.values)])
    
    dd.iloc[:,0]=x.iloc[:,0]
    
    return dd

The idea is to just replace the group column with the original column.

Returns:

group   age weight  score
0   1   -0.964286   3.928571    3.0
1   1   -0.964286   3.928571    3.0
2   1   -0.964286   3.928571    3.0
3   2   1.285714    -5.238095   -4.0
4   2   1.285714    -5.238095   -4.0
5   1   -0.964286   3.928571    3.0
6   2   1.285714    -5.238095   -4.0

As desired.

Other thoughts:

I used the .iloc method as you did, although I might consider adding a variable for the groupby column name to your function, so that you get increased flexibility, and can utilize the advantages of pandas over numpy in terms of readability/ease of use. You can always make the default value 'group' so that you don't have to think about it in this application, but can use the same function on another dataframe (where the first column isn't the grouping variable).

user_91500120
Related