How To Create A Join In Dataframe Based On The Other Dataframe?

I have 2 dataframes. One containing student batch details and another one with points. I want to join 2 dataframes. Dataframe1 contains +-------+-------+-------+--+ | s1 | s2

Solution 1:

Solution working same if all values from df3 exist in column Name:

s = dfnamepoints.set_index('Name')['Point']
df = df3.join(df3.replace(s).add_prefix('new_'))

Or:

df = df3.join(df3.apply(lambda x: x.map(s)).add_prefix('new_'))

Or:

df = df3.join(df3.applymap(s.get).add_prefix('new_'))

print (df)
      s1     s2     s3  new_s1  new_s2  new_s3
0  Stud1  Stud2  Stud3      90      80      95
1  Stud2  Stud4  Stud1      80      55      90
2  Stud1  Stud3  Stud4      90      95      55

If not, output is different - for not exist values (Stud1) get NaNs:

print (dfnamepoints)
    Name  Point Category
0  Stud2     80  Average
1  Stud3     95     Good
2  Stud4     55     Poor

df = df3.join(df3.applymap(s.get).add_prefix('new_'))
#or df = df3.join(df3.applymap(s.get).add_prefix('new_'))

print (df)
      s1     s2     s3  new_s1  new_s2  new_s3
0  Stud1  Stud2  Stud3     NaN      80    95.0
1  Stud2  Stud4  Stud1    80.0      55     NaN
2  Stud1  Stud3  Stud4     NaN      95    55.0

And for replace get original value:

df = df3.join(df3.replace(s).add_prefix('new_'))
print (df)
      s1     s2     s3 new_s1  new_s2 new_s3
0  Stud1  Stud2  Stud3  Stud1      80     95
1  Stud2  Stud4  Stud1     80      55  Stud1
2  Stud1  Stud3  Stud4  Stud1      95     55

Solution 2:

Alternativey, you could use df.replace() after creating a dictionary of the 2 concerned columns of df2 :

pd.concat([df1,df1.replace(dict(zip(df2.Name,df2.Point))).add_prefix('new_')],axis=1)

Output:

      s1     s2     s3  new_s1  new_s2  new_s3
0  Stud1  Stud2  Stud3      90      80      95
1  Stud2  Stud4  Stud1      80      55      90
2  Stud1  Stud3  Stud4      90      95      55