Remove values from specific columns with a condition in Python

Question

I would like to remove values in the value1, value2, value3 and value4 columns if the 'on status' column contains the string 'new'

Data

id  date        location    on status   value1  value2  value3  value 4
CC  1/1/2022    ny          new         12      1       0       1
CC  4/1/2022    ny          new         1       1       8       9
CC  7/1/2022    ny          new         1       1       1       0
CC  10/1/2022   ny          new         1       2       2       1
CC  1/1/2023    ny          ok          1       2       2       1

Desired

id  date        location    on status   value1  value2  value3  value4
CC  1/1/2022    ny          new         
CC  4/1/2022    ny          new         
CC  7/1/2022    ny          new         
CC  10/1/2022   ny          new         
CC  1/1/2023    ny          ok          1       2      2         1

This only works on the first 2 columns, but it actually adds two additional columns (value3 and value4 and deletes the data from all rows not just the conditional 'new' Any suggestion is appreciated

Doing

df.loc[(df['on status'] == 'new'),  ['value1', 'value2','value3', 'value4']]= ''

@added a solution, does it work for you?

Naveed
– Naveed

2022-07-28 17:26:20 +00:00
Commented Jul 28, 2022 at 17:26 — Naveed
– Naveed, Commented Jul 28, 2022 at 17:26

srinath · Accepted Answer · 2022-07-28 17:23:48Z

1

sample input

df = pd.DataFrame({
    
    "on_status" : ["new", "new", "new", "new", "ok"],
    "value1" : [x for x in range(5)],
    "value2" : [x for x in range(5)],
    "value3" : [x for x in range(5)],
    "value4" : [x for x in range(5)],
})

it does work for me using loc

df.loc[(df.on_status == "new"), ["value1", "value2", "value3", "value4"]] = ''

sample output

on_status   value1  value2  value3  value4
0   new             
1   new             
2   new             
3   new             
4   ok  4   4   4   4

answered Jul 28, 2022 at 17:23

srinath

3,0288 gold badges39 silver badges62 bronze badges

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

Comments

Manjari · Accepted Answer · 2022-07-28 17:33:55Z

1

df.loc[(df['status'].str.strip() == 'new'),  ['value1', 'value2','value3', 'value4']]= ''

try this as mentioned Naveed, if there are whitespaces around then it will check by stripping white spaces.

answered Jul 28, 2022 at 17:33

Manjari

3323 silver badges13 bronze badges

Comments

Sanju Halder · Accepted Answer · 2022-07-28 17:38:49Z

1

for i in range(len(df.index)):
    if df.loc[i,'on status'] == 'new':
        df.loc[i,'value1': 'value 4'] = ' '
df

edited Jul 28, 2022 at 17:38

answered Jul 28, 2022 at 17:37

Sanju Halder

967 bronze badges

Comments

Naveed · Accepted Answer · 2022-07-28 17:37:01Z

0

here is one way to do it

df.loc[(df['on status'] == 'new'), ['value1', 'value2','value3', 'value4']] = ''

OR

df.loc[(df['on status'].str.strip() == 'new'), ['value1', 'value2','value3', 'value4']] = ''

df

    id  date    location    on status   value1  value2  value3  value4
0   CC  1/1/2022    ny      new                 
1   CC  4/1/2022    ny      new                 
2   CC  7/1/2022    ny      new                 
3   CC  10/1/2022   ny      new                 
4   CC  1/1/2023    ny       ok             1      2       2       1

edited Jul 28, 2022 at 17:37

answered Jul 28, 2022 at 17:24

Naveed

11.7k2 gold badges16 silver badges21 bronze badges

4 Comments

Lynn Over a year ago

Hi Naveed thanks this is exactly what I have in my post and this does not work

Naveed Over a year ago

i just noticed it :( why it works for me? can you post your result? I wonder if the value 'new' has some whitespaces around it

Naveed Over a year ago

try to strip the "on status" before comparing

Lynn Over a year ago

right I see the issue now, the column names had spaces thanks

Collectives™ on Stack Overflow

Remove values from specific columns with a condition in Python

4 Answers 4

Comments

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related