Pandas: Sum of the Max 3 Column Values in Each Row

3338 views python
-1

Sample data:

        0        1         2        3       4  Sum_max_3
0  591949  2575703  22479693  2202865  499835   27258261
1    2705    11426    339913     5438    1016     356777
2      18      119      4162       18       0       4299
3     264     1213     14999      246     116      16476
4       0       35      1292       10       0       1337
5       0        0      1442        0       0       1442
6       0       28      5596       20       0       5644
7       0       10       102       56       0        168
8      33        0      1224       17       0       1274
9      39      198      9505       62      35       9765

I want to get the sum of the 3 columns with the largest values in the row. These are different columns for every row (Sum_max_3).

I have many many columns so I need to do this automatically for all of them.

answered question

1 Answer

12

Use the underlying np array (using df.values), sort each row using np.sort, and sum the last 3 columns (which will be the 3 maximum values for each row):

>>> df
        0        1         2        3       4
0  591949  2575703  22479693  2202865  499835
1    2705    11426    339913     5438    1016
2      18      119      4162       18       0
3     264     1213     14999      246     116
4       0       35      1292       10       0
5       0        0      1442        0       0
6       0       28      5596       20       0
7       0       10       102       56       0
8      33        0      1224       17       0
9      39      198      9505       62      35


>>> df['Sum_max_3'] = np.sum(np.sort(df.values)[:,-3:],axis=1)

>>> df
        0        1         2        3       4  Sum_max_3
0  591949  2575703  22479693  2202865  499835   27258261
1    2705    11426    339913     5438    1016     356777
2      18      119      4162       18       0       4299
3     264     1213     14999      246     116      16476
4       0       35      1292       10       0       1337
5       0        0      1442        0       0       1442
6       0       28      5596       20       0       5644
7       0       10       102       56       0        168
8      33        0      1224       17       0       1274
9      39      198      9505       62      35       9765

posted this

Have an answer?

JD

Please login first before posting an answer.