0

Below is my sample Dataframes and my expected result. How can I get the expected result. I tried using loop for i in range(len(df2)): for x in range(len(df1)):

and using if condition to compare column values and assign with .at method but getting KeyError: 0 error

Df1

Index acn name dob Insert Update New
0 6946 THAPA 25-04-1982 NaN NaN NaN
1 7012 SEKE 14-07-1966 NaN NaN NaN
2 5824 SEKE 14-07-1966 NaN NaN NaN
3 6946 JOHN 25-04-1982 NaN NaN NaN

Df2

Index acn name dob id
0 6946 JOHN 25-04-1982 IKO7
1 5824 SEKE 14-07-1966 GGF1

Expected Result

Index acn name dob Insert Update New
0 6946 THAPA 25-04-1982 NaN NaN Create
1 7012 SEKE 14-07-1966 NaN GGF1 NaN
2 5824 SEKE 14-07-1966 GGF1 NaN NaN
3 6946 JOHN 25-04-1982 IKO7 NaN NaN

How do I achieve this. Appreciate your help

PDitta
  • 19
  • 6
  • Where does the `Create` value in the `New` column come from? Also, how `Update` column has been changed? – TheFaultInOurStars Mar 22 '22 at 08:57
  • Based on conditions, like below if ( df1.at[i,"acn"] == df2.at[x, "acn"] and df1.at[i, "name"] == df2.at[x, "name"] and df1.at[i, "dob"] == df2.at[x, "dob"] ): df1.at[i, "Cont Insert"] = df2.at[i, "id"] if ( df1.at[i, "name"] == df2.at[x, "name"] and df1.at[i, "dob"] == df2.at[x, "dob"] ): df1.at[i, "ACR Insert"] = df2.at[i, "id"] else: df1.at[i, "New"] ="Create" – PDitta Mar 22 '22 at 12:25
  • @AmirhosseinKiani Thanks for ur response, trying to populate Insert Update, New columns in df1 Based on conditions above. – PDitta Mar 22 '22 at 12:40

1 Answers1

0

Try merge, using on and how arguments:

(df1.merge(df2, on=["acn", "name", "dob"], how="left")
      .drop(columns=["Insert","Index_y"])
      .rename(columns={"id":"Insert","Index_y":"Index"}))

Output

Index_x acn name dob Update New Insert
0 6946 THAPA 1982-04-25 00:00:00 nan nan nan
1 7012 SEKE 1966-07-14 00:00:00 nan nan nan
2 5824 SEKE 1966-07-14 00:00:00 nan nan GGF1
3 6946 JOHN 1982-04-25 00:00:00 nan nan IKO7
TheFaultInOurStars
  • 3,464
  • 1
  • 8
  • 29
  • Was to be achieve this as below for i in range(len(df1)): for x in range(len(df2)): if ( df1.at[i,"acn"] == df2.at[x, "acn"] and df1.at[i, "name"] == df2.at[x, "name"] and df1.at[i, "dob"] == df2.at[x, "dob"] ): df1.at[i, " Insert"] = df2.at[i, "id"] if ( df1.at[i, "name"] == df2.at[x, "name"] and df1.at[i, "dob"] == df2.at[x, "dob"] ): df1.at[i, " Insert"] = df2.at[i, "id"] else: df1.at[i, "New"] ="Create" – PDitta Mar 23 '22 at 01:36
  • And second loop to remove the redundant values for i in range(len(df1)): if df1.at[i,'Insert']==df1.at[i,'Insert']: df1.at[i,'Insert']=np.nan df1.at[i,'Con']=np.nan if pd.isna(df1.at[i,'Insert']) and pd.notna(df1.at[i,'Insert']) and pd.notna(df1.at[i,'Con']): df1.at[i,'Con']=np.nan Serves the purpose, but keen to know if there's better way to achieve the same result. – PDitta Mar 23 '22 at 01:41
  • @PDitta Thank you for coming back to this. It would be best if you could add your code to your question because comments usually are not meant to be for code snippets and I am having a hard time reading the code you wrote here. Thank you. – TheFaultInOurStars Mar 23 '22 at 07:54