Joining using column names in pandas

user_3079439 0
Viewed 40

I'm having two dataframes in pandas, the one is initial one:

enter image description here

and the other is the result of TF-IDF operation. So basically, Name was grouped by Group and then sklearn TF-IDF was applied like that:

import pandas as pd
from sklearn.feature_extraction.text import TfidfVectorizer

frame = df.groupby(['Group'])['Name'].apply(' '.join).reset_index()
vect = TfidfVectorizer()
tfidf = vect.fit_transform(frame)

yy=pd.DataFrame(tfidf.toarray(), columns=vect.get_feature_names())
yy['Group']=frame['Group']

enter image description here

Right now I would like to join two dataframes (which, for sure, I can do using merge() using Group as ID) but I would like also to use column names from the second dataframe to join with respective values in Name column in first dataframe to construct something like that:

enter image description here

How can this be achieved in pandas and do I need to use for loops for completing this?

pandas for-loop join python
1 Answers

Melt the 2nd data frame and then merge with the 1st one:

df2.melt('Group', var_name='Name', value_name='Res').merge(df1, how='right')

   Group Name  Res
0      1    A  0.0
1      1    A  0.0
2      2    A  0.0
3      2    B  0.1
4      3    B  0.0
5      3    C  0.1
user_49834500
Related