[Python] VLOOKUP in Python Pandas

A question that has been asked similarly a million times but I cannot find the correct answer that applies to my case. I have two excel files that I want to join like an Excel VLOOKUP.

File A already has the columns already created and they are partially filled (or empty). There is one column with adresses that will be the Join column for the other Excel file, File B. However, in File A are more addresses than in File B, so File B will only fill the targeted columns based on the values for those addresses, the others should not be affected (so either remain empty or with their old value).

File B has this column with addresses and then the columns with the values that are to be assigned to the respective addresses in File A. There are other columns in File B but I don't want those in File A.

According to my research I need an Inner Left Join to accomplish this. The column in common is AdressID, the columns to merge let's call them AA, BB, CC and DD.

My dataframes are called dfFileA and dfFileB.

I tried the following code:

dfFileA = pd.merge(dfFileA[['AA', 'BB', 'CC', 'AdressID']], dfFileB[['AA', 'BB', 'CC', 'AdressID']], on=['AdressID'], how='left')


Error message is KeyError: "['AA', 'BB', 'CC'] not in index"

Where is the mistake?

Thanks a lot!

[EDIT:] Here is an example of my problem, however, here it creates new columns with the same name but ending on _y and it renamed the original ones ending on _x (I don't know why it does that in this example and not in the original file):

import pandas as pd
FileA = {
'AA' : ['', 'DEF', '', 'JKL'],
'BB' : ['MNO', '', 'STU', ''],
'CC' : ['WX', '', 'GDG', 'GJ'],
'AdressID' : ['Great Street 1', 'Amazing Street 5', 'Perfect Street 21', 'Fantastic Street 88']
}

FileB = {
'AA' : ['ABC', '', 'GHI', ''],
'BB' : ['', 'PQR', '', 'FAS'],
'CC' : ['', 'YZ', '', 'GJ'],
'AdressID' : ['Great Street 1', 'Amazing Street 5', 'Perfect Street 21', 'Fantastic Street 88']
}

dfFileA = pd.DataFrame(FileA)
dfFileB = pd.DataFrame(FileB)

print(dfFileA)
print(dfFileB)

merged = pd.merge(dfFileA, dfFileB, on='AdressID', how='inner')
print(merged)


Obviously, I want the output to be like

merged = {
'AA' : ['ABC', 'DEF', 'GHI', 'JKL'],
'BB' : ['MNO', 'PQR', 'STU', 'FAS'],
'CC' : ['WX', 'YZ', 'GDG', 'GJ'],
'AdressID' : ['Great Street 1', 'Amazing Street 5', 'Perfect Street 21', 'Fantastic Street 88']
}


Hope this helps better

Continue reading...