CODEWITHSUNDEEP
pythonpandasdataframe
Sudipta Paul
Sudipta Paul

Reputation: 53

Update row values based on condition on column in python

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

Upvotes: 2

Views: 152

Answers (2)

Mayank Porwal
Mayank Porwal

Reputation: 34056

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

Upvotes: 3

Quang Hoang
Quang Hoang

Reputation: 150735

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

Upvotes: 4

Related Questions