Python, Pandas Merge x and y and fill with z from y

Question

I'm not even sure this is possible but it most certainly is worth asking. This would be a rather simple task in Excel however I'm finding it extremely difficult in Pandas.

I have DF1:

| Date | Location ID |

| -------- | -------------- |

| DD-MM-YYY | 1 |

| DD-MM-YYY | 2 | (120k Rows Total)

I have DF2:

|Date | Location ID | Location |

|:---- |:------:| -----:|

| DD-MM-YYY | 1 | India | (4 Rows Total) - 4 different locations

I want to merge the DFs together on ['Location ID'] and then auto-fill DF1 Location row with all the correct worded locations. So add the column Location to all the 120k rows based upon the Location ID.

Basically New DF1: |Date | Location ID | Location |

|:---- |:------:| -----:|

| DD-MM-YYY | 1 | India | (120K times)

Thanks in advance. If this is possible that would be great.

It's better to provide a [minimal, reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) so that others can help you. — Beatdown, Jun 19 '22 at 22:06

score 0 · Answer 1 · answered Jun 19 '22 at 23:34

import pandas as pd

df1 = pd.DataFrame({'Date': ['01-01-1999', '02-01-1999'],
                    'Location ID': [1, 2]})

df2 = pd.DataFrame({'Date': ['01-01-1999', '02-01-1999'], 
                    'Location ID': [1, 2],
                    'Location': ['India', 'Pakistan']})

df3 = pd.merge(left=df1, right=df2[['Location ID', 'Location']], how='left', on='Location ID')

print(df3)

gives

         Date  Location ID  Location
0  01-01-1999            1     India
1  02-01-1999            2  Pakistan

P.S. I'd suggest reading up on this excellent thread on how to make reproducible pandas examples. It will get you better responses here. ;)

Python, Pandas Merge x and y and fill with z from y

1 Answers1