Replacing values in a pandas dataframe based on multiple conditions

Question

I have a fairly simple question based on this sample code:

x1 = 10*np.random.randn(10,3)
df1 = pd.DataFrame(x1)

I am looking for a single DataFrame derived from df1 where positive values are replaced with "up", negative values are replaced with "down", and 0 values, if any, are replaced with "zero". I have tried using the .where() and .mask() methods but could not obtain the desired result.

I have seen other posts which filter according to multiple conditions at once, but they do not show how to replace values according to different conditions.

Possible duplicate of Pandas: Filtering multiple conditions

FChm
– FChm

2019-03-09 19:40:15 +00:00
Commented Mar 9, 2019 at 19:40 — FChm
– FChm, Commented Mar 9, 2019 at 19:40

Harshit Jain · Accepted Answer · 2020-06-12 12:00:37Z

For multiple conditions ie. (df['employrate'] <=55) & (df['employrate'] > 50)

use this:

df['employrate'] = np.where(
   (df['employrate'] <=55) & (df['employrate'] > 50) , 11, df['employrate']
   )

or you can do it this way as well,

gm.loc[(gm['employrate'] <55) & (gm['employrate'] > 50),'employrate']=11

here informal syntax can be:

<dataset>.loc[<filter1> & (<filter2>),'<variable>']='<value>'

out[108]:
       country  employrate alcconsumption
0  Afghanistan   55.700001            .03
1      Albania   11.000000           7.29
2      Algeria   11.000000            .69
3      Andorra         nan          10.17
4       Angola   75.699997           5.57

therefore syntax we used here is:

 df['<column_name>'] = np.where((<filter 1> ) & (<filter 2>) , <new value>, df['column_name'])

for single condition, ie. ( 'employrate'] > 70 )

       country        employrate alcconsumption
0  Afghanistan  55.7000007629394            .03
1      Albania  51.4000015258789           7.29
2      Algeria              50.5            .69
3      Andorra                            10.17
4       Angola  75.6999969482422           5.57

use this:

df.loc[df['employrate'] > 70, 'employrate'] = 7

       country  employrate alcconsumption
0  Afghanistan   55.700001            .03
1      Albania   51.400002           7.29
2      Algeria   50.500000            .69
3      Andorra         nan          10.17
4       Angola    7.000000           5.57

therefore syntax here is:

df.loc[<mask>(here mask is generating the labels to index) , <optional column(s)> ]

perl · Accepted Answer · 2019-03-09 19:47:51Z

9

df1.apply(np.sign).replace({-1: 'down', 1: 'up', 0: 'zero'})

Output:

      0     1     2
0  down    up    up
1    up  down  down
2    up  down  down
3  down  down    up
4  down  down    up
5  down    up    up
6  down    up  down
7    up  down  down
8    up    up  down
9  down    up    up

P.S. Getting exactly zero with randn is pretty unlikely, of course

answered Mar 9, 2019 at 19:47

perl

9,9811 gold badge14 silver badges23 bronze badges

2 Comments

Manuel F. Over a year ago

sleek solution @perl... but what if i need to compute operations with the df1 dataframe (let's say, x + 20 if np.sign(x)==-1) and not just replacement with character strings?

perl Over a year ago

@ManuelF That kind of replacement would be easier done with where: df.where(np.sign(df)!=-1, df + 20) (note that it replaces values where the condition is false, hence the inversion)

ALollz · Accepted Answer · 2019-03-09 20:04:11Z

8

In general, you could use np.select on the values and re-build the DataFrame

import pandas as pd
import numpy as np

df1 = pd.DataFrame(10*np.random.randn(10, 3))
df1.iloc[0, 0] = 0 # So we can check the == 0 condition 

conds = [df1.values < 0 , df1.values > 0]
choices = ['down', 'up']

pd.DataFrame(np.select(conds, choices, default='zero'),
             index=df1.index,
             columns=df1.columns)

Output:

      0     1     2
0  zero  down    up
1    up  down    up
2    up    up    up
3  down  down  down
4    up    up    up
5    up    up    up
6    up    up  down
7    up    up  down
8  down    up  down
9    up    up  down

answered Mar 9, 2019 at 20:04

ALollz

59.7k7 gold badges73 silver badges97 bronze badges

Comments

Mohammed Shameem · Accepted Answer · 2020-06-15 16:23:31Z

4

IF condition with OR

from pandas import DataFrame

names = {'First_name': ['Jon','Bill','Maria','Emma']}

df = DataFrame(names,columns=['First_name'])

df.loc[(df['First_name'] == 'Bill') | (df['First_name'] == 'Emma'), 'name_match'] = 'Match'  
df.loc[(df['First_name'] != 'Bill') & (df['First_name'] != 'Emma'), 'name_match'] = 'Mismatch'
print (df)

Output

  First_name name_match
0        Jon   Mismatch
1       Bill      Match
2      Maria   Mismatch
3       Emma      Match

answered Jun 15, 2020 at 16:23

Mohammed Shameem

492 bronze badges

Collectives™ on Stack Overflow

Replacing values in a pandas dataframe based on multiple conditions

4 Answers 4

Comments

2 Comments

Output:

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Output:

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related