How to save max row values for each column in python dataframe?

Question

I have a dataframe like:

   Name  A   B   C
0  Sen   1   0   NaN
1  Kes   0   1   0
2  Pas   0   0   1
3  Sen   0   0   NaN
4  Pas   0   0   2

I would like to drop duplicated for each column individually with a rule:

Name column is the key.

For example Sen is duplicated, but its value is changing only in A, for B & C its value is the same. So for A i would like to do an OR operation and retain Sen A's value as 1 and in the other row it should populate 'Nan'.

Basically i dont want to drop the entire row for duplication, but rather modify values inside each column for all columns.

Expected output:

   Name  A     B   C
0  Sen   1     0   NaN
1  Kes   0     1   0
2  Pas   0     0   Nan
3  Sen   Nan   0   NaN
4  Pas   0     0   2

BENY · Accepted Answer · 2020-05-25 13:09:48Z

1

We can do groupby+max with where

s=df.groupby('Name').max().reindex(df.Name).values
df.drop('Name',1).where(df.drop('Name',1)==s)
     A  B    C
0  1.0  0  NaN
1  0.0  1  0.0
2  0.0  0  NaN
3  NaN  0  NaN
4  0.0  0  2.0
#df.loc[:,'A':]=df.drop('Name',1).where(df.drop('Name',1)==s)

answered May 25, 2020 at 13:09

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

hakuna_code Over a year ago

but what if i want to retain the 'Name' column?

BENY Over a year ago

@hakuna_code noticed my mask line ? df.loc[:,'A':]=df.drop('Name',1).where(df.drop('Name',1)==s)

hakuna_code Over a year ago

df.drop('Name',1).where(df.drop('Name',1)==s) fails with an error 'cannot broadcast shape [(8007, 10)] with block values [(8007, 11)] ' ... i have columns from A to K

BENY Over a year ago

@hakuna_code what error , and did you try with your sample ?

hakuna_code Over a year ago

yea.. but higher dimesion its failing... but ideally it sud work the same way right?

|

Collectives™ on Stack Overflow

How to save max row values for each column in python dataframe?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related