Normalize a grouped DataFrame by the mean of a subset of the group

Question

I would like to normalize my data in a Pandas DataFrame grouped by Type with the mean of the values that are in the Condition CT.

The DataFrame is something like this:

df = pd.DataFrame({'Type' : ['A', 'A', 'A', 'A',
                          'B', 'B', 'B', 'B'],
                   'Condition' : ['Tx', 'CT', 'Tx', 'CT',
                          'Tx', 'CT', 'Tx', 'CT'],
                   'Var1' : np.random.randn(8),
                   'Var2' : np.random.randn(8)})

print(df)
  Condition Type      Var1      Var2  Var1_Norm  Var2_Norm
0        Tx    A -1.555886 -0.454512   3.290695  -1.059712
1        CT    A  0.820324  0.357123  -1.734983   0.832645
2        Tx    A -0.355758  0.807324   0.752426   1.882305
3        CT    A -0.799936  1.005673   1.691862   2.344762
4        Tx    B -0.253152 -0.585186   0.234666   6.790024
5        CT    B -0.672658  0.851191   0.623540  -9.876536
6        Tx    B -1.768877 -0.083506   1.639711   0.968933
7        CT    B -1.620407 -0.527232   1.502083   6.117579

I know how to normalize by the mean of the entire group:

df[['Var1_Norm', 'Var2_Norm']] = df.groupby(['Type']).transform(lambda x: x/x.mean())

But how do I normalize the grouped data by the mean of a subset of the group (rows with Condition == 'CT'?

I tried the following which results in an AttributeError:

df[['Var1_Norm', 'Var2_Norm']] = df.groupby(['Type']).transform(lambda x: x/x[x.Condition == 'CT'].mean())
AttributeError: ("'Series' object has no attribute 'Condition'", 'occurred at index Condition')

With the help of @piRSquared's answer I found a solution using a for loop:

df[['Var1_Norm', 'Var2_Norm']] = df[['Var1', 'Var2']]
for t in df.Type.unique():
    ct_mean = df.loc[(df.Type == t) & (df.Condition == 'CT'),['Var1_Norm', 'Var2_Norm']].mean()
    df.loc[df.Type == t,['Var1_Norm', 'Var2_Norm']] = df.loc[df.Type == t,['Var1_Norm', 'Var2_Norm']].div(ct_mean)

Sorry I wasn't able to help. You may want to show the exact numbers you expect to get. — piRSquared, Mar 28 '17 at 07:48
Your inputs are greatly appreciated! I updated the question with a solution using a for loop. I would really like to replace the loop with a `groupby`. — adabsurdum, Mar 28 '17 at 07:51

score 2 · Answer 1 · edited May 23 '17 at 12:09

You can use the apply method instead of transform.

The groupby method transform passes a series and expects a series in return, while apply passes a dataframe and expects either a dataframe or a series in return (explained in more detail here). This will allow you to check for the condition since you'll have access to the relevant column inside the function:

df[['Var1_Norm', 'Var2_Norm']] = df.groupby(['Type']).apply(
    lambda x: x[['Var1', 'Var2']] / x.loc[x['Condition'] == 'CT', ['Var1', 'Var2']].mean())

print(df)

Result:

  Condition Type      Var1      Var2  Var1_Norm  Var2_Norm
0        Tx    A  0.285153  0.093274   0.653616  -0.281818
1        CT    A  0.947555 -0.998790   2.171946   3.017739
2        Tx    A -1.123067 -0.572842  -2.574246   1.730783
3        CT    A -0.075015  0.336844  -0.171946  -1.017739
4        Tx    B  0.126968 -1.095042   0.146513  -2.741475
5        CT    B  0.441539  0.431948   0.509506   1.081396
6        Tx    B -1.945165 -0.233643  -2.244588  -0.584932
7        CT    B  1.291665  0.366923   1.490494   0.918604

Of course you can generalize this to work on any number of columns, or even create a function generator that generates a function based on a given condition.

Normalize a grouped DataFrame by the mean of a subset of the group

1 Answers1