Subtracting mean of columns with groupby across multiple columns

user_15524510 0
Viewed 165

I am having trouble applying this function with a groupby in multiple columns, but skipping certain columns.

My dataframe looks like this:

arr = pd.DataFrame(
[[201207310930, 0.0022, -0.0160,    0.0055, 0.0324, -0.0328],
[201207310930,  0.0040, -0.0073,    0.0293, 0.0465, -0.0275],   
[201207310930,  -0.0095,-0.0080,    0.0215, 0.0525, -0.0317],   
[201207311030,  -0.0005,-0.0040,    -0.0149,    0.0135, 0.0488],
[201207311030,  -0.0087,-0.0240,    0.0134, 0.0480, -0.0331]]
)

I'd like to group on datetime and then de-trend (subtract the mean), so this is what I have tried (ignoring x1 column):

detrendfunc = lambda x: (x - x.mean())
cols = arr.columns[2:]
output=df.groupby(['Datetime'])[cols].transform(detrendfunc)

My problem is that this is just outputting a bunch of zeros, I think its just subtracting x from the mean of x, but the mean of x only includes the one element, not the group of elements.

The expected result is a new dataframe that has the mean of each group in each column subtracted from each element:

201207310930    0.0033  -0.00556    -0.01326    -0.0114 -0.00213
201207310930    0.0051  0.00313      0.01053    0.0027  0.00316
201207310930    -0.0084 0.00243      0.00273    0.0087  -0.001033
201207311030    0.0041  0.01        -0.01415    -0.01725  0.04095
201207311030    -0.0041 -0.01        0.01415    0.01725 -0.04095
pandas dataframe lambda python
2 Answers

Suppose you have this df:

       Datetime      x1      x2      x3      x4      x5
0  201207310930  0.0022 -0.0160  0.0055  0.0324 -0.0328
1  201207310930  0.0040 -0.0073  0.0293  0.0465 -0.0275
2  201207310930 -0.0095 -0.0080  0.0215  0.0525 -0.0317
3  201207311030 -0.0005 -0.0040 -0.0149  0.0135  0.0488
4  201207311030 -0.0087 -0.0240  0.0134  0.0480 -0.0331

Then:

detrendfunc = lambda x: (x - x.mean())
cols = df.columns[1:]  # <-- change `arr` to `df` and `[2:]` to `[1:]`
output = df.groupby(["Datetime"])[cols].transform(detrendfunc)

df_out = pd.concat([df["Datetime"], output], axis=1)
print(df_out)

Prints:

       Datetime      x1        x2        x3       x4        x5
0  201207310930  0.0033 -0.005567 -0.013267 -0.01140 -0.002133
1  201207310930  0.0051  0.003133  0.010533  0.00270  0.003167
2  201207310930 -0.0084  0.002433  0.002733  0.00870 -0.001033
3  201207311030  0.0041  0.010000 -0.014150 -0.01725  0.040950
4  201207311030 -0.0041 -0.010000  0.014150  0.01725 -0.040950
user_100359850

You could set the index on the first column, use transform to get the mean for each group, while maintaining the shape, and again, use transform to do the subtraction, before resetting the index:

(arr
 .set_index(0)
 .transform(lambda df: df - df.groupby(level=0)
                              .transform("mean")
            )
 .reset_index()
 )
 
              0       1         2         3        4         5
0  201207310930  0.0033 -0.005567 -0.013267 -0.01140 -0.002133
1  201207310930  0.0051  0.003133  0.010533  0.00270  0.003167
2  201207310930 -0.0084  0.002433  0.002733  0.00870 -0.001033
3  201207311030  0.0041  0.010000 -0.014150 -0.01725  0.040950
4  201207311030 -0.0041 -0.010000  0.014150  0.01725 -0.040950

Another option would be to do the computation on the relevant columns separately, then update the original dataframe (Note that this overrides the original arr dataframe - you can make a copy of the dataframe before running update):

arr.update(arr.iloc[:, 1:] - arr.groupby(0).transform("mean"))

arr
           0       1         2         3        4         5
0  201207310930  0.0033 -0.005567 -0.013267 -0.01140 -0.002133
1  201207310930  0.0051  0.003133  0.010533  0.00270  0.003167
2  201207310930 -0.0084  0.002433  0.002733  0.00870 -0.001033
3  201207311030  0.0041  0.010000 -0.014150 -0.01725  0.040950
4  201207311030 -0.0041 -0.010000  0.014150  0.01725 -0.040950
user_71757130
Related