How to map values in single column of a dataframe to two columns of another dataframe and extract the mapped values?

Question

I have two data frames

df1 = pd.read_excel("data1.xlsx")
df2 = pd.read_excel("data2.xlsx"

df1

Data1   Data2   Score
ABC      AB1    1
AB1      ABC    4
AB2      AB2    6
ABC      ABD    0.7
GDH      ABD    0.9
KMN      KSF    0.5
KSF      KSF    6

df2

Data1
AB1
AB2
ABC

I want to map df2["Data1"] to df1 and extract the mapping values:

merged_data = pd.merge(df2, df1, how='inner', left_on='Data1', right_on='Data1')
merged_data2 = pd.merge(df2, df1, how='inner', left_on='Data1', right_on='Data2')

combined_data = pd.concat([merged_data,merged_data2])
#remove duplicates
combined_data_rm = combined_data.drop_duplicates()
combined_data_rm

Data1   Data2   Score
ABC      AB1    1
AB1      ABC    4
AB2      AB2    6
ABC      ABD    0.7

Is there any alternate way of doing with analysis using pandas?

```df = df1[df1['Data1'].isin(df2['Data1']) | df1['Data2'].isin(df2['Data1'])]``` — svp, Sep 08 '20 at 09:14

score 2 · Accepted Answer · answered Sep 08 '20 at 07:10

2

try this,

print(pd.merge(df1, df2, on=["Data1"]))

O/P:

  Data1 Data2  Score
0   ABC   AB1    1.0
1   ABC   ABD    0.7
2   AB1   ABC    4.0
3   AB2   AB2    6.0

answered Sep 08 '20 at 07:10

Mohamed Thasin ah

8,314
8
35
66

1

say suppose I have different header name in the data frame. Will this work using `left_on` and `right_on` – svp Sep 08 '20 at 07:16
yes, it will work. – Mohamed Thasin ah Sep 08 '20 at 07:19
it will not work as you expected. Try and see @svp – user288925 Sep 08 '20 at 10:58
@user288925- why it won't work? If you have any solution for OP, please post your answer as a separate thread. – Mohamed Thasin ah Sep 08 '20 at 12:00

How to map values in single column of a dataframe to two columns of another dataframe and extract the mapped values?

1 Answers1