Using reference rows within different groups in a DataFrame to perform data manipulations

user_6218849 0
Viewed 55

Given this DataFrame

df = pd.DataFrame({'Group': {0: 'a', 1: 'a', 2: 'b', 3: 'b', 4: 'c', 5: 'c', 6: 'd', 7: 'd'},
'Time': {0: 0, 1: 1, 2: 0, 3: 1, 4: 0, 5: 1, 6: 0, 7: 1},
'Mean': {0: 1, 1: 4, 2: 1, 3: 5, 4: 2, 5: 6, 6: 2, 7: 9}})

For each group in the DataFrame (a and b), I would like to take each number in the Mean column and divide it by the corresponding value at Time == 0, and take the np.log2 of this. In other words, I want to end up with this

df2 = pd.DataFrame({'Group': {0: 'a', 1: 'a', 2: 'b', 3: 'b', 4: 'c', 5: 'c', 6: 'd', 7: 'd'},
 'Time': {0: 0, 1: 1, 2: 0, 3: 1, 4: 0, 5: 1, 6: 0, 7: 1},
 'Mean': {0: 1, 1: 4, 2: 1, 3: 5, 4: 2, 5: 6, 6: 2, 7: 9},
 'New': {0: 0.0, 1: 2.0, 2: 0.0, 3: 2.321928094887362, 4: 0.0, 5: 1.584962500721156, 6: 0.0, 7: 2.169925001442312}})

Currently I achieved this in the following way

df2 = pd.DataFrame()
for group, sub in df.groupby('Group'):
    sub['New'] = np.log2(sub.Mean / sub.Mean.iloc[0])
    df2 = pd.concat([df2, sub], axis=0)

But I feel like there should be a simpler way.

pandas python
1 Answers

You can do groupby with transform first(to substitute iloc[0]) and then divide with the Mean column.

df['New'] = np.log2(df['Mean']/df.groupby("Group")['Mean'].transform('first'))

  Group  Time  Mean       New
0     a     0     1  0.000000
1     a     1     4  2.000000
2     b     0     1  0.000000
3     b     1     5  2.321928
4     c     0     2  0.000000
5     c     1     6  1.584963
6     d     0     2  0.000000
7     d     1     9  2.169925

EDIT:

You can also do the below if you are not sure of the index where the Time equals 0:

df['New'] = (np.log2(df['Mean']/df.loc[df['Time'].eq(0).groupby(df['Group'])
               .transform('idxmax'),'Mean'].to_numpy()))

This will for each group return the max index when the cond Time=0 is True. , then using df.loc we return the Mean Column and use it for division. For more information see how idxmax works

print(df)

  Group  Time  Mean       New
0     a     0     1  0.000000
1     a     1     4  2.000000
2     b     0     1  0.000000
3     b     1     5  2.321928
4     c     0     2  0.000000
5     c     1     6  1.584963
6     d     0     2  0.000000
7     d     1     9  2.169925
user_98406370
Related