Get value from another df based on condition

user_6226993 0
Viewed 40

I have 2 df

df1:

ID       X Y Cond
Johnson  2 3 fine
Sand     NAN NAN sick
Cooper   1 2 fine
Nelson   1 2 fine
Peterson 4 5 fine

and df2 :

id2      X Y 
Magic    2 3 
Sand     2 3 
Cooper   1 2 
Dean     1 2

I want to update x value in df1, if Cond ="sick" and df["id"] = df["id2] to get the new df1 :

ID       X Y Cond
Johnson  2 3 fine
Sand     2 3  sick
Cooper   1 2 fine
Nelson   1 2 fine
Peterson 4 5 fine

I tried :

df1["x"] = np.where((df["cond"]=="sick")& (df1["id"]==df2["id2"]),df2["x"],"")

But its not working. I get this ValueError :

ValueError: Can only compare identically-labeled Series objects

Thank you

pandas dataframe numpy
2 Answers

First convert both ID columns to index values for possible match selected rows by DataFrame.loc:

df11 = df1.set_index('ID')
df22 = df2.set_index('id2')

df11.loc[df11["Cond"]=="sick", ['X','Y']] = df22[['X','Y']]
df = df11.reset_index()
print (df)
         ID  X  Y  Cond
0   Johnson  2  3  fine
1      Sand  2  3  sick
2    Cooper  1  2  fine
3    Nelson  1  2  fine
4  Peterson  4  5  fine
user_29010020

You can use the where() method of pandas dataframes instead of the wherefunction from numpy. The code looks like this :

df1.loc[:,["X", "Y"]] = df1.loc[:,["X", "Y"]].where(df1["Cond"]!="sick",df2.loc[:,["X", "Y"]])
user_139700890
Related