Update row values based on condition on column in python

Question

I have a python data frame like this

ID     ID_1    ID_2    ID_3   ID_4   ID_5

ID_1   1.0     20.1    31.0   23.1   31.5

ID_2   3.0     1.0     23.0   90.0   21.5

ID_3.  7.0     70.1    1.0    23.0   31.5

ID_4.  9.0     90.1    43.0   1.0    61.5

ID_5   11.0    10.1    11.0   23.0    1.0

I need to update values where COLUMN NAMES are equal to the ID values and then set the values to zero.

for example in the first row the ID value (ID_1) matches with first column ID_1 and I need to reset the value of 1.0 to zero , and similarly for second row , the ID value (ID_2) matches with second column ID_2 and reset the value of 1.0 to zero.

How do I do this in Python ? I am very new in python. Can anyone please help.

the expected output would be like this -

ID     ID_1    ID_2    ID_3   ID_4   ID_5

ID_1   0.0     20.1    31.0   23.1   31.5

ID_2   3.0     0.0     23.0   90.0   21.5

ID_3.  7.0     70.1    0.0    23.0   31.5

ID_4.  9.0     90.1    43.0   0.0    61.5

ID_5   11.0    10.1    11.0   23.0    0.0

kindly post your expected output

sammywemmy
– sammywemmy

2020-12-19 07:08:24 +00:00
Commented Dec 19, 2020 at 7:08 — sammywemmy
– sammywemmy, Commented Dec 19, 2020 at 7:08
Please post the expected output based on your sample data.

Mayank Porwal
– Mayank Porwal

2020-12-19 07:08:26 +00:00
Commented Dec 19, 2020 at 7:08 — Mayank Porwal
– Mayank Porwal, Commented Dec 19, 2020 at 7:08

Quang Hoang · Accepted Answer · 2020-12-19 07:25:55Z

4

Let's try broadcasting:

df[:] = np.where(df['ID'].values[:,None] == df.columns.values,0, df)

Output:

     ID  ID_1  ID_2  ID_3  ID_4  ID_5
0  ID_1   0.0  20.1  31.0  23.1  31.5
1  ID_2   3.0   0.0  23.0  90.0  21.5
2  ID_3   7.0  70.1   0.0  23.0  31.5
3  ID_4   9.0  90.1  43.0   0.0  61.5
4  ID_5  11.0  10.1  11.0  23.0   0.0

answered Dec 19, 2020 at 7:25

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

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

Comments

Mayank Porwal · Accepted Answer · 2020-12-19 07:28:26Z

3

Consider df:

In [1479]: df
Out[1479]: 
     ID  ID_1  ID_2  ID_3  ID_4  ID_5  ID_6
0  ID_1   1.0  20.1  31.0  23.0  31.5  24.6
1  ID_2   3.0   1.0  23.0  90.0  21.5  24.6
2  ID_3   7.0  70.1   1.0  23.0  31.5  24.6
3  ID_4   9.0  90.1  43.0   1.0  61.5  24.6
4  ID_5  11.0  10.1  11.0  23.0   1.0  24.6
5  ID_6   7.0  20.1  31.0  33.0  87.5   1.0

Use pd.get_dummies with df.combine_first:

In [1477]: import numpy as np

In [1497]: df.iloc[:, 1:] = pd.get_dummies(df['ID']).replace({0: np.nan, 1: 0}).combine_first(df.iloc[:, 1:])

In [1498]: df
Out[1498]: 
     ID  ID_1  ID_2  ID_3  ID_4  ID_5  ID_6
0  ID_1   0.0  20.1  31.0  23.0  31.5  24.6
1  ID_2   3.0   0.0  23.0  90.0  21.5  24.6
2  ID_3   7.0  70.1   0.0  23.0  31.5  24.6
3  ID_4   9.0  90.1  43.0   0.0  61.5  24.6
4  ID_5  11.0  10.1  11.0  23.0   0.0  24.6
5  ID_6   7.0  20.1  31.0  33.0  87.5   0.0

edited Dec 19, 2020 at 7:28

answered Dec 19, 2020 at 7:12

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

1 Comment

anky Over a year ago

I had another one with dummies which aligns columns if they arent in same order :-) : np.where(pd.get_dummies(df.index,dtype=bool).reindex(columns=df.columns),0,df)

Collectives™ on Stack Overflow

Update row values based on condition on column in python

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related