replace Value in a dataframe depending on another dataframe

Question

I have 2 dataframes :

   >>> tab1
        Value       Sp_name    
    0   None        ROOT  
    1   0.066       Genus_1_sp1
    2   0.1044      Genus_2_sp1
    3   0.0708      EDGE  
    4   0.0586      Genus_3_sp1
    5   0.0083      Genus_4_sp1

and the idea is to parse another data frame such as :

>>> tab2
Old_name             New_name
Genus_1_sp1_A     Genus_1_sp1
Genus_2_sp1_A     Genus_2_sp1
Genus_3_sp1_A     Genus_3_sp1
Genus_4_sp1_A     Genus_4_sp1

and in replace Sp_name in the tab1 that match the New_name in tab2, and then replace the Sp_name by the corresponding Old_name In the exemple I should get :

>>> tab1
    Value       Sp_name    
0   None        ROOT  
1   0.066       Genus_1_sp1_A
2   0.1044      Genus_2_sp1_A
3   0.0708      EDGE  
4   0.0586      Genus_3_sp1_A
5   0.0083      Genus_4_sp1_A

I tried so far:

for i in tab1['Sp_name']:
    found= tab2[tab2['New_name'].str.contains(i)]
    if len(found) > 0:
        tab1.at[i,'Sp_name'] = str(row['Old_name'])

ResidentSleeper · Accepted Answer · 2019-05-05 09:12:04Z

2

Create a name dictionary from tab2 then use .replace to replace it back to tab1

name_dict = dict(zip(tab2.New_name, tab2.Old_name))
tab1['Sp_name'] = tab1['Sp_name'].replace(name_dict)

tab1

    Value        Sp_name
0    None           ROOT
1   0.066  Genus_1_sp1_A
2  0.1044  Genus_2_sp1_A
3  0.0708           EDGE
4  0.0586  Genus_3_sp1_A
5  0.0083  Genus_4_sp1_A

answered May 5, 2019 at 9:12

ResidentSleeper

2,4952 gold badges13 silver badges21 bronze badges

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

Comments

iamklaus · Accepted Answer · 2019-05-05 10:33:30Z

1

using pd.merge

df['Sp_name'] = pd.merge(df1,df2.rename(columns={'New_name':'Sp_name'}),how='left' ,on='Sp_name').apply(lambda x: x['Old_name'] if x['Old_name'] == np.nan else x['Sp_name'], axis=1)

Output

    Value      Sp_name
0    None         ROOT
1   0.066  Genus_1_sp1
2  0.1044  Genus_2_sp1
3  0.0708         EDGE
4  0.0586  Genus_3_sp1
5  0.0083  Genus_4_sp1

answered May 5, 2019 at 10:33

iamklaus

3,7682 gold badges14 silver badges21 bronze badges

Comments

Yong Wang · Accepted Answer · 2019-05-05 10:33:47Z

1

Try DataFrame.update. It is designed for such requirement. Modify in place using non-NA values from another DataFrame.

Here is my sample code for your reference:

from io import StringIO
import pandas as pd
from pprint import pprint

tab1="""
Value,Sp_name
None,ROOT
0.066,Genus_1_sp1
0.1044,Genus_2_sp1
0.0708,EDGE
0.0586,Genus_3_sp1
0.0083,Genus_4_sp1
"""
tab2="""
Old_name,New_name
Genus_1_sp1_A,Genus_1_sp1
Genus_2_sp1_A,Genus_2_sp1
Genus_3_sp1_A,Genus_3_sp1
Genus_4_sp1_A,Genus_4_sp1A
"""
df1 = pd.read_csv(StringIO(tab1)).set_index("Sp_name",drop=False)

df2=  pd.read_csv(StringIO(tab2)).rename(columns={"Old_name":"Sp_name"}).set_index("New_name")
df1.index.name ='New_name'

new_df = df1.copy()
new_df.update(df2)

print("\nthis is table 1 ")
pprint(df1,)

print("\nthis is table 2 ")

pprint(df2,)

print("\nthis is updated table")

pprint(new_df.reset_index(drop=True),)

And here is the output.

this is table 1 
              Value      Sp_name
New_name                        
ROOT           None         ROOT
Genus_1_sp1   0.066  Genus_1_sp1
Genus_2_sp1  0.1044  Genus_2_sp1
EDGE         0.0708         EDGE
Genus_3_sp1  0.0586  Genus_3_sp1
Genus_4_sp1  0.0083  Genus_4_sp1

this is table 2 
                    Sp_name
New_name                   
Genus_1_sp1   Genus_1_sp1_A
Genus_2_sp1   Genus_2_sp1_A
Genus_3_sp1   Genus_3_sp1_A
Genus_4_sp1A  Genus_4_sp1_A

this is updated table
    Value        Sp_name
0    None           ROOT
1   0.066  Genus_1_sp1_A
2  0.1044  Genus_2_sp1_A
3  0.0708           EDGE
4  0.0586  Genus_3_sp1_A
5  0.0083    Genus_4_sp1

answered May 5, 2019 at 10:33

Yong Wang

1,31311 silver badges15 bronze badges

2 Comments

chippycentra Over a year ago

Thank you for all the explanations !

Yong Wang Over a year ago

Welcom. Have a nice day.

anky · Accepted Answer · 2019-05-05 10:42:11Z

1

You can use series.map() for mapping. It is the most vectorised too (IMO):

tab1.Sp_name=tab1.Sp_name.map(tab2.set_index('New_name')['Old_name']).fillna(tab1.Sp_name)
print(tab1)

    Value        Sp_name
0    None           ROOT
1   0.066  Genus_1_sp1_A
2  0.1044  Genus_2_sp1_A
3  0.0708           EDGE
4  0.0586  Genus_3_sp1_A
5  0.0083  Genus_4_sp1_A

answered May 5, 2019 at 10:42

anky

75.3k11 gold badges46 silver badges76 bronze badges

Collectives™ on Stack Overflow

replace Value in a dataframe depending on another dataframe

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related