I have a static dataframe lookupdf with lookup values:
NAME ISOCODE CENTX CENTY QUADRANT CONTINENT REGION
Armenia AM 44.9473935725 40.2862000299 NE Asia Southwest Asia
Australia AU 134.489562766 -25.7349687864 SE Oceania Pacific
Austria AT 14.1401911625 47.5929028612 NE Europe Central Europe
Azerbaijan AZ 48.8198278555 40.2969919154 NE Asia Southwest Asia
Bahamas BS -78.040600609 24.701450012 NW Americas West Indies
Bahrain BH 50.5596446282 26.0224059472 NE Asia Southwest Asia
Bangladesh BD 90.268498724 23.8432316368 NE Asia South Asia
Barbados BB -59.5619547005 13.1787144829 NW Americas West Indies
Belarus BY 28.0467876469 53.5399977269 NE Europe Eastern Europe
Belgium BE 4.66398728382 50.6428519008 NE Europe Western Europe
Cape Verde CV -23.634821585 15.071600796 NW Africa Western Africa
Cambodia KH 104.923981952 12.7164317848 NE Asia Southeast Asia
Cameroon CM 12.7435941169 5.68595228568 NE Africa Western Africa
Canada CA -98.2653306694 61.392016704 NW Americas North America
and another dataframe df with actual variable data:
DATE VALUE ISOCODE
0 2021-01-01 15:16 12 CA
1 2021-01-12 11:22 2 AZ
2 2021-02-11 03:27 22 BB
3 2021-02-23 05:09 14 AM
4 2021-02-23 16:43 7 CA
5 2021-03-17 01:06 21 BE
6 2021-03-19 09:56 11 KH
I want to merge the df dataframe with the values from lookupdf like this:
DATE COUNT ISOCODE QUADRANT CONTINENT REGION
0 2021-01-01 15:16 12 CA NW Americas North America
1 2021-01-12 11:22 2 AZ NE Asia Southwest Asia
2 2021-02-11 03:27 22 BB NW Americas West Indies
3 2021-02-23 05:09 14 AM NE Asia Southwest Asia
4 2021-02-23 16:43 7 CA NW Americas North America
5 2021-03-17 01:06 21 BE NE Europe Western Europe
6 2021-03-19 09:56 11 KH NE Asia Southeast Asia
(I don't really need them, but it is perfectly fine if CENTX and CENTY are pulled in, as well.)
I tried new_df = pd.merge(df, lookupdf, how='inner', on='countrycode'), but I got many duplicate rows. I also tried how='left', but also got duplicate rows.
Any suggestions?
