How to loop through a pandas dataframe to query with another dataframe? or merge using python pandas-CodePudding

I got 2 dataframes:

this is a table that shows the user info, without the role name:

Name	Role ID	Number of Roles
Daniel	45678	1
James	45678	1
Jacob	45678,12345,98765	3
Erin	98765,45678	2
Ash	12345	1
Bob	12345	1

this table shows all of the existing Roles:

Role ID	Role Name
45678	Admin
12345	Supervisor
98765	Manager

using python pandas, I'm trying to find a way to merge these table into one so it will show the info like that:

Name	Role ID	Number of Roles	Role Name
Daniel	45678	1	Admin
James	45678	1	Admin
Jacob	45678,12345,98765	3	Admin, Supervisor, Manager
Erin	98765,45678	2	Manager, Admin
Ash	12345	1	Admin
Bob	12345	1	Admin

I'm new to python, sorry if this is simple, could find the solution anywhere else. :)

CodePudding user response：

I think your expected output is not right.

You can use Series.str.replace and df2 and regex from df2's Role Name to get what you need:

d = df2.set_index('Role ID')['Role Name']
reg = '|'.join(df2['Role ID'].unique().astype(str))
df1['Role Name'] = df1['Role ID'].str.replace(f'({reg})', 
                              lambda m: d[int(m.group(1))], regex=True)

print(df1)

     Name             Role ID  Number of Roles                  Role Name
0  Daniel               45678                 1                     Admin 
1   James               45678                 1                     Admin 
2   Jacob   45678,12345,98765                 3  Admin,Supervisor,Manager 
3    Erin         98765,45678                 2             Manager,Admin 
4     Ash               12345                 1                Supervisor 
5     Bob               12345                 1                Supervisor