Python pandas apply function on columns value (base on columns names patern)

Question

I have a dataframe:

a b  val1_b1 val1_b2 val2_b1 val2_v2
1 2    5       9        4      6

I want to take the max by column group, so the dataframe will be:

a b  val1  val2 
1 2    9     6

or the RMS:

a b       val1    val2
1 2    sqrt(106)   sqrt(52)

What is the best way to do so?

So if use `max` why is output `5` and `4` ? Why is `_v2` in last column name? Why is `106` and `52` in last `df` ? — jezrael, Aug 11 '20 at 06:02
@jezrael sorry the post was messed uo for some reason, I edit now a fix — Cranjis, Aug 11 '20 at 06:36
For second `RMS` is necessary actual and predicted columns, not sure what columns are in input data. — jezrael, Aug 11 '20 at 06:47
@jezrael Great, max is working but not sure what do you mean and how to apply the RMS? — Cranjis, Aug 11 '20 at 07:25
I think `rms` from [this](https://stackoverflow.com/questions/47914428/python-dataframe-calculating-r2-and-rmse-using-groupby-on-one-column) - what columns are necessary use in your sample data. — jezrael, Aug 11 '20 at 07:26
@jezrael I meant RMS not RMSE - meaning sqrt(val1_b1^2 + val1_b2^2) — Cranjis, Aug 11 '20 at 07:45

jezrael · Accepted Answer · 2020-08-11T07:55:45.213

You can use max with DataFrame.groupby by columns, axis=1 and lambda function fors values before first _ (if exist):

df1 = df.groupby(lambda x: x.split('_')[0], axis=1).max()
print (df1)
   a  b  val1  val2
0  1  2     9     6

EDIT for RMS use:

df2 = df.iloc[:, :2]
df3 = df.iloc[:, 2:].pow(2).groupby(lambda x: x.split('_')[0], axis=1).sum().pow(1./2)

#alternative
df3 = (df.iloc[:, 2:].groupby(lambda x: x.split('_')[0], axis=1)
         .apply(lambda a: pd.Series(np.linalg.norm(a))))

df4 = pd.concat([df2, df3], axis=1)
print (df4)

   a  b      val1      val2
0  1  2  10.29563  7.211103

Python pandas apply function on columns value (base on columns names patern)

1 Answers1