Match multiple columns on Python to a single value

user_15268135 0
Viewed 95

I hope you are doing well. I am trying to perform a match based on multiple columns where my values of Column B of df1 is scattered in three to four columns in df2. The goal here is the the return the values of Column A of df2 if values of Column B matches any values in the columns C,D,E. What I did until now was actually to do multiple left merges (and changing the name of Column B to match the name of columns C,D,E of df2). I am trying to simplify the process but I am unsure how I am supposed to do this? My dataset looks like that:

Df1:

    ID
0   77  
1   4859    
2   LSP

DF2:

    X           id1             id2             id3
0   AAAAA_XX    889             77              BSP
1   BBBBB_XX    4859            CC              998P
2   CCCC_YY     YUI             TYU             LSP

My goal is to have in df1:

    ID     X
0   77     AAAAA_XX
1   4859   BBBBB_XX 
2   LSP    CCCC_YY

Thank you very much !

pandas dataframe merge python
2 Answers

Use DataFrame.merge with DataFrame.melt:

df = df1.merge(df2.melt(id_vars='X', value_name='ID').drop('variable', axis=1), 
               how='left', 
               on='ID')
print (df)
     ID         X
0    77  AAAAA_XX
1  4859  BBBBB_XX
2   LSP   CCCC_YY

If possible duplicated ID is possible use:

df = (df1.merge(df2.melt(id_vars='X', value_name='ID')
                  .drop('variable', axis=1)
                  .drop_duplicates('ID'),
                how='left', 
                on='ID'))
user_29010020

you can get all the values in the columns to one first with pd.concat then we merge the tables like this:

df3 = pd.concat([df2.id1, df2.id2]).reset_index()
df1 = df2.merge(df3, how="left", left_on = df1.ID, right_on = df3[0])
df1 = df1.iloc[:, :2]
df1 = df1.rename(columns={"key_0": "ID"})

not the most beautiful code in the world, but it works.

output:

    ID      X
0   77      AAAAA_XX
1   4859    BBBBB_XX
2   LSP     CCCC_YY
user_97339990
Related