Duplicated join on dataframes to assign values

user_13236293 0
Viewed 26

I want to make a duplicated join on two dataframes by year and id regardless the rolling month. This would result in the quantity value getting assigned to each month according to the year. I am wondering how to overcome this.

data = {
        'wt': [0.5, 0.4, 0.3, 0.8, 0.9, 0.3, 0.2, 0.6, 0.7, 0.2, 0.4, 0.9],
        'date': ["2020-03-31", "2020-04-30", "2020-05-31", "2021-03-31", "2021-04-30", "2021-05-31",
        "2020-03-31", "2020-04-30", "2020-05-31", "2021-03-31", "2021-04-30", "2021-05-31"],
        'year': ["2020", "2020", "2020", "2021", "2021", "2021", 
        "2020", "2020", "2020", "2021", "2021", "2021",]
        }
dg = pd.DataFrame(data, index=['a123', 'a123', 'a123', 'a123', 'a123', 'a123', 'a518', 'a518', 'a518','a518', 'a518', 'a518'])
dg

data = {'quantity': [1200, 1300, 150, 160],
        'year': [2020, 2021, 2020, 2021]
        }
df = pd.DataFrame(data, index=['a123', 'a123', 'a518', 'a518'])
df = df.reset_index()

>>> dg
       wt        date  year
a123  0.5  2020-03-31  2020
a123  0.4  2020-04-30  2020
a123  0.3  2020-05-31  2020
a123  0.8  2021-03-31  2021
a123  0.9  2021-04-30  2021
a123  0.3  2021-05-31  2021
a518  0.2  2020-03-31  2020
a518  0.6  2020-04-30  2020
a518  0.7  2020-05-31  2020
a518  0.2  2021-03-31  2021
a518  0.4  2021-04-30  2021
a518  0.9  2021-05-31  2021

>>> df
  index  quantity  year
0  a123      1200  2020
1  a123      1300  2021
2  a518       150  2020
3  a518       160  2021

Expected outcome:
       wt        date  year  quantity
a123  0.5  2020-03-31  2020  1200
a123  0.4  2020-04-30  2020  1200
a123  0.3  2020-05-31  2020  1200
a123  0.8  2021-03-31  2021  1300
a123  0.9  2021-04-30  2021  1300
a123  0.3  2021-05-31  2021  1300
a518  0.2  2020-03-31  2020  150
a518  0.6  2020-04-30  2020  150
a518  0.7  2020-05-31  2020  150
a518  0.2  2021-03-31  2021  160
a518  0.4  2021-04-30  2021  160
a518  0.9  2021-05-31  2021  160
pandas python
1 Answers

Use DataFrame.merge with convert index to columns:

dg['year'] = dg['year'].astype(int)

df = (dg.reset_index()
        .merge(df.reset_index(),
               on=['index','year'], how='left')
        .set_index('index')
        .rename_axis(None))
print (df)
       wt        date  year  quantity
a123  0.5  2020-03-31  2020      1200
a123  0.4  2020-04-30  2020      1200
a123  0.3  2020-05-31  2020      1200
a123  0.8  2021-03-31  2021      1300
a123  0.9  2021-04-30  2021      1300
a123  0.3  2021-05-31  2021      1300
a518  0.2  2020-03-31  2020       150
a518  0.6  2020-04-30  2020       150
a518  0.7  2020-05-31  2020       150
a518  0.2  2021-03-31  2021       160
a518  0.4  2021-04-30  2021       160
a518  0.9  2021-05-31  2021       160
user_29010020
Related