Pandas creating a column comparing with different sheets-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

If you wanna do further analytics in python,

import pandas as pd
sheet_user = pd.read_excel('join_example.xlsx', sheet_name='user')
sheet_name = pd.read_excel('join_example.xlsx', sheet_name='name')
sheet_user = sheet_user.merge(sheet_name, left_on='User id', right_on='userid', how='left').drop(columns='userid')
sheet_user
###
   User id    Name
0        1    ramu
1        1    ramu
2        1    ramu
3        2  sachin
4        3   jacob
5        3   jacob
6        3   jacob
7        2  sachin
8        4   manoj

If you only want to join them and do analytics in Excel, then vlookup would be sufficient.

e.g. B2 = VLOOKUP($A2,name!$A$2:$B$6,2,FALSE)