0

I have a dataframe df1 which has :

F_ID I_Code     F_Date
1   FT-54185    01/09/2019
2   FT-35447    01/09/2019
3   FT-39312    01/09/2019
4   FT-21146    01/09/2019
5   FT-40664    01/09/2019
1   FT-56984    02/09/2019
2   FT-19680    02/09/2019
3   FT-89801    02/09/2019
4   FT-29238    02/09/2019
5   FT-45919    02/09/2019
1   FT-97960    03/09/2019
2   FT-76736    03/09/2019
3   FT-73886    03/09/2019
4   FT-70293    03/09/2019
5   FT-72250    03/09/2019

I have another dataframe df2 which has the following entries : 

F_Date    ID_code
01/09/2019  5
01/09/2019  5
01/09/2019  5
01/09/2019  5
01/09/2019  2
01/09/2019  2
01/09/2019  2
01/09/2019  2

I want to generate a new column 'ID' that is to be picked up from df1 and inserted into the df2

Expected Output : 

F_Date    ID_code  ID
01/09/2019  5      FT-40664
01/09/2019  5      FT-40664
01/09/2019  5      FT-40664
01/09/2019  5      FT-40664
01/09/2019  2      FT-35447
01/09/2019  2      FT-35447
01/09/2019  2      FT-35447
01/09/2019  2      FT-35447

if there is no value in ID_code('NA' or 0), then 'NA' needs to be filled in the column.

I was trying to achieve the same using : 

d = df1.groupby('F_Date')['I_Code'].apply(list).to_dict()

df2['new'] = df2.apply(lambda x: d.get(x['F_Date'], [])[x['ID_Code']], axis=1)

I am getting the following error :

TypeError: ('list indices must be integers or slices, not float', 'occurred at index 0')

Can anyone tell what I am doing wrong or is there any other way to achieve the same.

dper
  • 884
  • 1
  • 8
  • 31
  • This is just a basic `join/merge`: `df2.merge(df1, left_on=['F_Date', 'ID_code'], right_on=['F_Date', 'F_ID'])` – Erfan Oct 27 '19 at 20:48

1 Answers1

1

Try:

df2.merge(df1, left_on = ['ID_code','F_Date'], right_on = ['F_ID','F_Date'], how = 'left').rename(columns={"I_Code": "ID"}).fillna('NA').drop('F_ID', axis = 1)
Adam Zeldin
  • 898
  • 4
  • 6