Creating a new dataframe column by comparing strings of two unequal dataframes

Question

What is the most efficient approach in Python to create a new data frame column df1['Description'] obtained when df1['a'] string is equal df2['b'] string? When condition is met, then a separate string df2['Description'] would be assigned to df1['Description']. df1 and df2 are large data frames (~1/2 Million rows) of unequal sizes.

df1:

df2:

b    Description
W2   asadsde  
Z0   evrverve
A7   eveveerv

I would like:

df1
a    b   Description
Z0   1   evrverve  
Z1   2   jsbdbcje
A7   3   eveveerv

Trenton McKinney · Accepted Answer · 2020-08-06 05:37:13Z

2

Use pandas.merge

import pandas as pd

df1 = pd.DataFrame([['Z0', 1],['Z1', 2], ['A7', 3]], columns=['a', 'b'])
    
    a  b
0  Z0  1
1  Z1  2
2  A7  3
    
df2 = pd.DataFrame([['W2', 'asadsde'], ['Z0', 'evrverve'], ['A7', 'eveveerv'], ['Z1', 'jsbdbcje']], columns=['a', 'Description'])
    
    a Description
0  W2     asadsde
1  Z0    evrverve
2  A7    eveveerv
3  Z1    jsbdbcje
    
df3 = pd.merge(left=df1, right=df2, on='a', )
    
    a  b Description
0  Z0  1    evrverve
1  Z1  2    jsbdbcje
2  A7  3    eveveerv

edited Aug 6, 2020 at 5:37

answered Sep 4, 2018 at 5:03

Trenton McKinney

63.2k41 gold badges169 silver badges212 bronze badges

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

Comments

Space Impact · Accepted Answer · 2018-09-04 05:09:44Z

1

Considering these are your dataframes:

df1
a    b
Z0   1  
Z1   2
A7   3

df2
b   Description
W2  asadsde
Z0  evrverve
A7  eveveerv
Z1  jsbdbcje

Code to achieve your desired output using map and assign:

df1.assign(description = df1['a'].map(dict(df2.values)))


    a   b   description
0   Z0  1   evrverve
1   Z1  2   jsbdbcje
2   A7  3   eveveerv

In df1 if you want only matched rows then use dropna:

df1.dropna(inplace=True)

edited Sep 4, 2018 at 5:09

answered Sep 4, 2018 at 5:03

Space Impact

13.3k26 silver badges51 bronze badges

Comments

pd farhad · Accepted Answer · 2018-09-04 05:26:14Z

0

import pandas as pd
df1 = pd.DataFrame([['Z0', 1],['Z1', 2], ['A7', 3]], columns=['a', 'b'])
df2 = pd.DataFrame([['W2', 'asadsde'], ['Z0', 'evrverve'], ['A7', 'eveveerv'], ['Z1', 'jsbdbcje']], columns=['b', 'Description'])

After the initialization you can join your dataframes based on your acolumn and setting your other datafrmae's b column as index . So the code will be-

df1.join(df2.set_index('b'),on='a')

And your desire output will be -

    a   b   description
    0   Z0  1   evrverve
    1   Z1  2   jsbdbcje
    2   A7  3   eveveerv

answered Sep 4, 2018 at 5:26

pd farhad

6,4523 gold badges33 silver badges48 bronze badges

Collectives™ on Stack Overflow

Creating a new dataframe column by comparing strings of two unequal dataframes

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related