Replace values in a pandas df based off values in another column

Question

I am trying to replace values in multiple columns if the value in another column is equal to a specific value. For the df below I want to replace all integers will an empty value if Col A is == ABC

import pandas as pd

df = pd.DataFrame({          
    'B' : [10,20,30,40,50],
    'A' : ['ABC','DEF','XYZ','ABC','DEF'],          
    'C' : [1,1,1,1,1],              
    })

Output:

    B    A  C
0  10  ABC  1
1  20  DEF  1
2  30  XYZ  1
3  40  ABC  1
4  50  DEF  1

So I want to replace the integers in Col B,C when A is equal to ABC. I have tried this

mask = df.A != 'ABC'
col = ['B','C']
df = df.loc[mask, col].replace('')

But it only selects the values I want to replace. I'm hoping to produce:

    B    A  C
0  10  ABC  1
1      DEF   
2      XYZ   
3  40  ABC  1
4      DEF

jezrael · Accepted Answer · 2018-08-08 04:31:11Z

3

Use select_dtypes with np.integer or np.number if want select all numeric columns and then set empty string by condition with loc:

mask = df.A != 'ABC'
#if want select all integer columns
col = df.select_dtypes(np.integer).columns
#if want select columns by names
#col = ['B','C']
df.loc[mask, col] = ''
print (df)
    B    A  C
0  10  ABC  1
1      DEF   
2      XYZ   
3  40  ABC  1
4      DEF

edited Aug 8, 2018 at 4:31

answered Aug 8, 2018 at 4:26

jezrael

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

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

Comments

Zero · Accepted Answer · 2018-08-08 04:26:23Z

2

You can use

In [189]: df[['B', 'C']] = df[['B', 'C']].where(df.A.eq('ABC'), '')

In [190]: df
Out[190]:
    B    A  C
0  10  ABC  1
1      DEF
2      XYZ
3  40  ABC  1
4      DEF

answered Aug 8, 2018 at 4:26

Zero

77.4k22 gold badges153 silver badges153 bronze badges

Comments

U13-Forward · Accepted Answer · 2018-08-08 04:31:03Z

0

Pandas apply:

import pandas as pd

df = pd.DataFrame({          
    'B' : [10,20,30,40,50],
    'A' : ['ABC','DEF','XYZ','ABC','DEF'],          
    'C' : [1,1,1,1,1],              
    })
print(df.apply(lambda row: [i if isinstance(i,str) else '' for i in row.tolist()] if row['A']!='ABC' else row,axis=1))

Output:

     A   B  C
0  ABC  10  1
1  DEF       
2  XYZ       
3  ABC  40  1
4  DEF

answered Aug 8, 2018 at 4:31

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Collectives™ on Stack Overflow

Replace values in a pandas df based off values in another column

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