How do I change multiple values in pandas df column to np.nan, based on condition in other column?

user_10879908 0
Viewed 549

I don't have much experience in coding and this is my first question, so please be patient with me. I need to find a way to change multiple values of a pandas df column to np.nan, based on a condition in another column. Therefore I have created copies of the required columns "Vorgabe" and "Temp".

Whenever the value in "Grad" isn't 0 i want to change the values in a definded area in "Vorgabe" and "Temp" to np.nan. 

print(df)  

    OptOpTemp  OpTemp  BSP  Grad  Vorgabe  Temp
0        22.0    20.0    5   0.0     22.0  20.0
1        22.0    20.5    7   0.0     22.0  20.5
2        22.0    21.0    8   1.0     22.0  21.0
3        22.0    21.0    6   0.0     22.0  21.0
4        22.0    23.5    7   0.0     22.0  20.0
5        23.0    21.5    1   0.0     23.0  21.5
6        24.0    22.5    3   1.0     24.0  22.5
7        24.0    23.0    4   0.0     24.0  23.0
8        24.0    25.5    9   0.0     24.0  25.5

So I want to achieve something like this: 

    OptOpTemp  OpTemp  BSP  Grad  Vorgabe  Temp
0        22.0    20.0    5   0.0     22.0  20.0
1        22.0    20.5    7   0.0     nan   nan      <-one row above
2        22.0    21.0    8   1.0     nan   nan
3        22.0    21.0    6   0.0     nan   nan      <-one row among
4        22.0    23.5    7   0.0     22.0  20.0
5        23.0    21.5    1   0.0     nan   nan
6        24.0    22.5    3   1.0     nan   nan
7        24.0    23.0    4   0.0     nan   nan
8        24.0    25.5    9   0.0     24.0  25.5

Does somebody have a solution to my problem?

EDIT: I may have been unclear. The goal is to change every value in "Vorgabe" and "Temp" in an defined area to nan. In my example the area would be one row above, the row with 1.0 in it, and one row among. So not only the row, where 1.0 is located, but also rows above and under.

pandas dataframe python
4 Answers

Use loc:

df.loc[df.Grad != 0.0, ['Vorgabe', 'Temp']] = np.nan
print(df)

Output

   OptOpTemp  OpTemp  BSP  Grad  Vorgabe  Temp
0       22.0    20.0    5   0.0     22.0  20.0
1       22.0    20.5    7   0.0     22.0  20.5
2       22.0    21.0    8   1.0      NaN   NaN
3       22.0    21.0    6   0.0     22.0  21.0
4       22.0    23.5    7   0.0     22.0  20.0
5       23.0    21.5    1   0.0     23.0  21.5
6       24.0    22.5    3   1.0      NaN   NaN
7       24.0    23.0    4   0.0     24.0  23.0
8       24.0    25.5    9   0.0     24.0  25.5
user_40015920

You could use numpy.where.

import numpy as np

df['Vorbage']=np.where(df['Grad']!=0, df['OptOpTemp'], np.nan)
df['Temp']=np.where(df['Grad']!=0, df['OpTemp'], np.nan)
user_100670430

Chain 3 conditions with | for bitwise OR, for rows above and under 1 use mask with shift:

mask1 = df['Grad'] == 1
mask2 = df['Grad'].shift() == 1
mask3 = df['Grad'].shift(-1) == 1

mask1 = df['Grad'] != 0
mask2 = df['Grad'].shift() != 0
mask3 = df['Grad'].shift(-1) != 0

mask = mask1 | mask2 | mask3

df.loc[mask, ['Vorgabe', 'Temp']] = np.nan
print (df)
   OptOpTemp  OpTemp  BSP  Grad  Vorgabe  Temp
0       22.0    20.0    5   0.0     22.0  20.0
1       22.0    20.5    7   0.0      NaN   NaN
2       22.0    21.0    8   1.0      NaN   NaN
3       22.0    21.0    6   0.0      NaN   NaN
4       22.0    23.5    7   0.0     22.0  20.0
5       23.0    21.5    1   0.0      NaN   NaN
6       24.0    22.5    3   1.0      NaN   NaN
7       24.0    23.0    4   0.0      NaN   NaN
8       24.0    25.5    9   0.0     24.0  25.5

General solution for multiple rows:

N = 1
#create range for test value betwen -N to N
r = np.concatenate([np.arange(0, N+1), np.arange(-1, -N-1, -1)])
#create boolean mask by comparing with shift and join together by reduce 
mask = np.logical_or.reduce([df['Grad'].shift(x) == 1 for x in r])

df.loc[mask, ['Vorgabe', 'Temp']] = np.nan

EDIT:

You can join both masks together:

N = 1
r1 = np.concatenate([np.arange(0, N+1), np.arange(-1, -N-1, -1)])
mask1 = np.logical_or.reduce([df['Grad'].shift(x) == 1 for x in r1])

N = 2
r2 = np.concatenate([np.arange(0, N+1), np.arange(-1, -N-1, -1)])
mask2 = np.logical_or.reduce([df['Grad'].shift(x) == 1.5 for x in r2])
#if not working ==1.5 because precision of floats
#mask2 = np.logical_or.reduce([np.isclose(df['Grad'].shift(x), 1.5) for x in r2])

mask = mask1 | mask2
df.loc[mask, ['Vorgabe', 'Temp']] = np.nan
print (df)
   OptOpTemp  OpTemp  BSP  Grad  Vorgabe  Temp
0       22.0    20.0    5   0.0     22.0  20.0
1       22.0    20.5    7   0.0      NaN   NaN
2       22.0    21.0    8   1.0      NaN   NaN
3       22.0    21.0    6   0.0      NaN   NaN
4       22.0    23.5    7   0.0      NaN   NaN
5       23.0    21.5    1   0.0      NaN   NaN
6       24.0    22.5    3   1.5      NaN   NaN <- changed value to 1.5
7       24.0    23.0    4   0.0      NaN   NaN
8       24.0    25.5    9   0.0      NaN   NaN
user_29010020

You can use df.apply(f,axis=1), and define f to be what you want to do on each row. You description seems to be saying you want

 def f(row):
     if row['Grad']!=0:
         row.loc[['Vorgabe','Temp']]=np.nan
     return row

However, your example seems to be saying you want something else.

user_85441230
Related