Pandas - Set value in column by looking up in another dataframe

Question

I have two dataframes that have the following structure.

Dataframe A:

id	date	price
1	2021-09-01	null
1	2021-09-02	null
2	2021-09-01	null
2	2021-09-02	null
3	2021-09-01	null
3	2021-09-02	null

Dataframe B:

id	price
1	100
2	200
3	300

I need to set the price in dataframe A, for each id, to the same value as the id has in dataframe B, regardless of the date in dataframe A.

So expected result is the following:

id	date	price
1	2021-09-01	100
1	2021-09-02	100
2	2021-09-01	200
2	2021-09-02	200
3	2021-09-01	300
3	2021-09-02	300

The data set is very large so need something efficient.

Happy to hear your suggestions :)

score 0 · Answer 1 · answered Sep 21 '21 at 10:59

Use set_index and fillna:

>>> df1.set_index('id').fillna({'price': df2.set_index('id')['price']}).reset_index()
   id        date  price
0   1  2021-09-01  100.0
1   1  2021-09-02  100.0
2   2  2021-09-01  200.0
3   2  2021-09-02  200.0
4   3  2021-09-01  300.0
5   3  2021-09-02  300.0
>>>

score 0 · Accepted Answer · answered Sep 21 '21 at 11:03

0

Use pd.merge:

>>> dfA.drop(columns='price').merge(dfB, on='id', how='left')

   id        date  price
0   1  2021-09-01    100
1   1  2021-09-02    100
2   2  2021-09-01    200
3   2  2021-09-02    200
4   3  2021-09-01    300
5   3  2021-09-02    300

Please read carefully: Pandas Merging 101

answered Sep 21 '21 at 11:03

Corralien

109,409
8
28
52

Did not know merge existed. Thanks a lot! :) – golusp Sep 21 '21 at 11:26

score 0 · Answer 3 · answered Sep 21 '21 at 11:17

mapping may be useful as well:

dfA = pd.DataFrame(columns=["id","date", "price"],
                   data=[[1, "2021-09-01", "N/A"],
                         [1, "2021-09-02", "N/A"],
                         [2, "2021-09-01", "N/A"],
                         [2, "2021-09-02", "N/A"],
                         [3, "2021-09-01", "N/A"],
                         [3, "2021-09-02", "N/A"]])

dfB = pd.DataFrame(columns=["id", "price"],
                   data=[[1, 100],
                         [2, 200],
                         [3, 300]])

map_dict = dfB.set_index("id").to_dict()["price"]
dfA["price"] = dfA["id"]
dfA["price"] = dfA["price"].map(map_dict)

Pandas - Set value in column by looking up in another dataframe

3 Answers3