CODEWITHSUNDEEP
python-3.xpandas
jonas778
jonas778

Reputation: 85

Lookup value in one dataframe and paste it into another dataframe

I have two dataframes in Python one big (car listings), one small (car base configuration prices). The small one looks like this:

      Make  Model               MSRP
0   Acura   ILX                27990
1   Acura   MDX                43015
2   Acura   MDX Sport Hybrid    51960
3   Acura   NSX                156000
4   Acura   RDX                 35670
5   Acura   RLX                54450
6   Acura   TLX                31695
7   Alfa Romeo  4C             55900
8   Alfa Romeo  Giulia         37995
…   …          … .               …
391 Toyota  Yaris              14895
392 Toyota  Yaris iA           15950
393 Volkswagen  Atlas          33500
394 Volkswagen  Beetle         19795
395 Volkswagen  CC             34475
396 Volkswagen  GTI            24995
397 Volkswagen  Golf           19575
398 Volkswagen  Golf Alltrack  25850
399 Volkswagen  Golf R         37895
400 Volkswagen  Golf SportWagen 21580
401 Volkswagen  Jetta           17680
402 Volkswagen  Passat          22440
403 Volkswagen  Tiguan          24890
404 Volkswagen  Touareg         42705
405 Volkswagen  e-Golf          28995
406 Volvo        S60            33950

Now I want to paste the values from the MSRP column (far right column) based on matching the Make and Model columns into the big dataframe (car listings) that looks like the following:

    makeName    modelName   trimName    carYear mileage
0   BMW            X5         sDrive35i   2017     0
1   BMW            X5         sDrive35i   2017     3
2   BMW            X5         sDrive35i   2017     0
3   Audi           A4         Premium Plus2017     0
4   Kia          Optima       LX          2016    10
5   Kia          Optima       SX Turbo    2017    15
6   Kia          Optima       EX          2016    425
7   Rolls-Royce   Ghost       Series II   2017    15
…    …              …          …            …      …

In the end I would like to have the following:

    makeName    modelName   trimName    carYear mileage  MSRP
0   BMW            X5         sDrive35i   2017     0     value from the other table
1   BMW            X5         sDrive35i   2017     3     value from the other table
2   BMW            X5         sDrive35i   2017     0     value from the other table
3   Audi           A4         Premium Plus2017     0     value from the other table
4   Kia          Optima       LX          2016    10     value from the other table
5   Kia          Optima       SX Turbo    2017    15     value from the other table
6   Kia          Optima       EX          2016    425    value from the other table
7   Rolls-Royce   Ghost       Series II   2017    15     value from the other table
…    …              …          …            …      …

I read the documentation regarding pd.concat, merge and join but I am not making any progress.

Can you guys help?

Thanks!

Upvotes: 1

Views: 3889

Answers (1)

Scott Boston
Scott Boston

Reputation: 153460

You can use merge to join the two dataframes together.

car_base.merge(car_listings, left_on=['makeName','modelName'], right_on=['Make','Model'])

Upvotes: 1

Related Questions