Skip to content
Advertisement

Pandas creating a column comparing with different sheets

My excel includes id of users in current sheet/user sheet and id and name of the users in another sheet/name. I need to compare id and add the name of users in user sheet.Just as shown in figure.

current sheet another sheet output

Advertisement

Answer

assuming:

sheet1 is ‘s1’

sheet2 is ‘s2’

and names of the columns are user_id,names

you can use dictionary to do this

s1 = pd.read_excel(r'path_to_your_excel.xlsx',sheet_name='Sheet1')
s2 = pd.read_excel(r'path_to_your_excel.xlsx',sheet_name='Sheet2')

#creating a new column 
s1['names'] = s1['user_id'].map(dict(zip(s2.user_id,s2.name)))

you can then write the s1 dataframe to a new excel sheet.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement