CODEWITHSUNDEEP
pythonpandas
excelguy
excelguy

Reputation: 1624

Pandas Merge Vlookup, KeyError: "['Value'] not in index"

I am trying to perform a vlookup/merge betwen two dataframes. I get the error KeyError: "['Player'] not in index"

i've tried to reindex the columns but doesnt seem to work. df1= df1.reindex(columns = ['Player','Category'])

My current code is like so missingnames = pd.merge(df1,df2[['Player','Player Name']],on='Player',how = 'left')

My dataframes are like below:

df1:

enter image description here

df2:

enter image description here

expected output

enter image description here

can anyone help with this? Thanks.

Upvotes: 1

Views: 224

Answers (3)

ansev
ansev

Reputation: 30920

Use numpy.where:

df1['Exists']=np.where(df1['Player'].str.upper().isin(df2['Player Name'].str.upper()),'Exists','')

Upvotes: 1

Hillygoose
Hillygoose

Reputation: 195

Look closer at your merge argument and the columns you have in each dataframe

df1 includes "Player" and "Category" df2 includes "Player Name", "Height" and "Weight"

Your merge argument says that the column "Player" is in df2 however it is not

missingnames = pd.merge(df1,df2[['Player','Player Name']],on='Player',how = 'left') ===============================^

missingnames needs to be changed to: missingnames = df1.merge(df2,left_on='Player',right_on='Player Name',how = 'left')

And then from there ascertain if there are any missing values

Upvotes: 1

zipa
zipa

Reputation: 27879

You can do it like this:

df1['Exists'] = df1['Player'].str.lower().isin(df2['Player Name'].str.lower())

Upvotes: 1

Related Questions