CODEWITHSUNDEEP
pandasdataframenumpydata-science
Javad.Rad
Javad.Rad

Reputation: 93

How to update the value in a column based on another value in the same column where both rows have the same value in another column?

The dataframe df is given:

    ID    I   J   K
0   10    1   a   1
1   10    2   b   nan
2   10    3   c   nan
3   11    1   f   0
4   11    2   b   nan
5   11    3   d   nan
6   12    1   b   1
7   12    2   d   nan
8   12    3   c   nan

For each unique value in the ID, when I==3, if J=='c' then the K=1 where I==1, else K=0. The other values in K do not matter. In other words, the value of K in the row 0, 3, and 6 are determined based on the value of I in the row 2, 5, and 8 respectively.

Upvotes: 0

Views: 49

Answers (1)

Andrej Kesely
Andrej Kesely

Reputation: 195438

Try:

IDs = df.loc[df.I.eq(3) & df.J.eq("c"), "ID"]
df["K"] = np.where(df["ID"].isin(IDs) & df.I.eq(1), 1, 0)
df["K"] = np.where(df.I.eq(1), df.K, np.nan) # <-- if you want other values NaNs
print(df)

Prints:

   ID  I  J    K
0  10  1  a  1.0
1  10  2  b  NaN
2  10  3  c  NaN
3  11  1  f  0.0
4  11  2  b  NaN
5  11  3  d  NaN
6  12  1  b  1.0
7  12  2  d  NaN
8  12  3  c  NaN

Upvotes: 1

Related Questions