Lookup and join dataframes with multiple column names in Python

Question

df1:

col1        col2        col3        col4        col5        col6        col7        col8 
7865                                                                                abc
                                    7269                                            def
            8726                                                                    ghi
                                                                        986         jkl
                                                7689                                mno
                        8762                                                        pqr
                                                                                    stu
                        9698                                                        vwx
            3568                                                                    yz

df2:

Scientific value         mapping_value
1                        8726
2                        9698
3                        3568
4                        986
5                        7269

I want to match "col1, col2, col3, col4, col5, col6, col7" column values in df1 with "mapping_value" column in df2 and create a new column called "Scientific value" in df1 which would have entries from "Scientific value" column in df2.

Output:

col1        col2        col3        col4        col5        col6        col7        col8   Scientific value
7865                                                                                abc    
                                    7269                                            def    5
            8726                                                                    ghi    1
                                                                        986         jkl    4
                                                7689                                mno
                        8762                                                        pqr
                                                                                    stu
                        9698                                                        vwx    2
            3568                                                                    yz     3

Would merge work in this case..or would be happy to learn any other efficient method as well!!

Thanks!

jezrael · Accepted Answer · 2020-04-04 05:40:51Z

1

If there are only one number per rows for any of column without last solution should be simplify by sum, max, min function for one column, so is possible use Series.map:

s = df2.set_index('mapping_value')['Scientific value']
df1['Scientific value'] = df1.iloc[:, :-1].max(axis=1).map(s)
#if empty string are not missing values
#df1['Scientific value'] = df1.iloc[:, :-1].replace('',np.nan).max(axis=1).map(s)

If possible multiple values first select by position by DataFrame.iloc, then reshape by DataFrame.stack, mapping by Series.map, remove possible missing values for non match values and last aggregate lists:

s = df2.set_index('mapping_value')['Scientific value']
df1['Scientific value'] = (df1.iloc[:, :-1]
                              .stack()
                              .map(s)
                              .dropna()
                              .groupby(level=0)
                              .agg(list))

edited Apr 4, 2020 at 5:40

answered Apr 4, 2020 at 5:26

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

omdurg Over a year ago

sorry for the additional doubt @jezrael I have now. In cases where I want to mention the column names from col1 to col7 instead of .iloc positions, how would I do that with .loc[]

omdurg Over a year ago

When using 2nd approach(where multiple values possible) @jezrael, I get output values like [5] instead of 5. When I checked print(type(df1['Scientific value'])), it gives '<class 'pandas.core.series.Series'>, how can I get normal output values as in 1st approach you had given

jezrael Over a year ago

@omdurg - I think it is data dependent, is possivble create new question with this multiple output values?

omdurg Over a year ago

Thanks @jezrael, I have posted it

jezrael Over a year ago

@omdurg - do you test posted data with this problem Do you get [5] instead 5?

|

Collectives™ on Stack Overflow

Lookup and join dataframes with multiple column names in Python

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related