I have two dfs:
df_1
date id value
2021-01-01 A1 100
2021-01-01 A1 200
2021-01-01 A1 300
2021-01-02 A1 100
2021-01-02 A1 200
2021-01-03 A1 500
2021-01-03 A1 800
df_2
date id value_to_add
2021-01-01 A1 150
2021-01-03 A1 350
I am trying to maintain the structure of df_1 and to add the value_to_add in the first occurrence during merge so that the end result would look like this after filling NaN and all but the first values with a 0:
date id value value_to_add
2021-01-01 A1 100 150
2021-01-01 A1 200 0 # 0 because the 150 have been already added
2021-01-01 A1 300 0
2021-01-02 A1 100 0 # 0 because value_to_add does not exist
2021-01-02 A1 200 0
2021-01-03 A1 500 350
2021-01-03 A1 800 0 # 0 because the 350 have been already added
My first thought was to drop duplicates of a ['date', 'id'] subset, then merge df_2 to it but then I am not sure how I would go back to the original structure of df_1.
So the problem is the following – being able to merge on the first occurrence of keys during pd.merge operation. I was not able to find anything on this topic and frankly not sure how I could achieve this.
>Solution :
Use:
df_1.loc[~df_1.duplicated(['date', 'id']),
df_1.columns.union(df_2.columns)] = df_1.merge(df_2, how='left')
df_1 = df_1.fillna(0)
print (df_1)
date id value value_to_add
0 2021-01-01 A1 100 150.0
1 2021-01-01 A1 200 0.0
2 2021-01-01 A1 300 0.0
3 2021-01-02 A1 100 0.0
4 2021-01-02 A1 200 0.0
5 2021-01-03 A1 500 350.0
6 2021-01-03 A1 800 0.0