I have two data frames and I need to append data from df2 to df1 using 'ID' column. df1 has duplicates on same ID whereas df2 has ID as primary key. I need to add data from df2 to df1 such that :
df1 :
|Index| ID|Last Updated|
| 0| A1| 1.10.2022|
| 1| A2| 12.11.2022|
| 2| A3| 3.12.2022|
| 3| C1| 4.12.2022|
| 4| C1| 19.12.2022|
df2 :
|Index| ID| Title |
| 0| A1| system1|
| 1| A2| system2|
| 2| A3| system3|
| 3| C1| system4|
expected output (df3):
|Index| ID|Last Updated| Title |
| 0| A1| 1.10.2022| system1|
| 1| A2| 12.11.2022| system2|
| 2| A3| 3.12.2022| system3|
| 3| C1| 4.12.2022| system4|
| 4| C1| 19.12.2022| system4|
I tried a brute approach to solve this:
for idx, data in df1.iterrows():
temp = df2[df2['id'] == data['id']]
column_list = temp.columns.to_list()
for column in column_list:
df1.at[idx, column] = temp.iloc[0][column]
I want to understand if there is any direct/better approach to do this?