How to set values to np.nan with multiple conditions for series?

user_4443378 0
Viewed 128

Let's say I have a dataframe:

    A   B   C   D   E   F
0   x   R   i   R   nan h
1   z   g   j   x a   nan
2   z   h   nan y nan nan
3   x   g   nan nan nan nan
4   x   x   h   x   s   f

I want to replace all the cells where:

  1. the value in row 0 is R (df.loc[0] == 'R')
  2. the cell is not 'x' (!= 'x')
  3. only rows 2 and below (2:)

with np.nan.

Essentially I want to do:

df.loc[2:,df.loc[0]=='R']!='x' = np.nan

I get the error:

SyntaxError: can't assign to comparison

I just don't know how the syntax is supposed to be.

I've tried

df[df.loc[2:,df.loc[0]=='R']!='x']

but this doesn't list the values I want.

pandas python
3 Answers

Solution

mask = df.ne('x') & df.iloc[0].eq('R')
mask.iloc[:2] = False

df.mask(mask)

   A    B    C    D    E    F
0  x    R    i    R  NaN    h
1  z    g    j    x    a  NaN
2  z  NaN  NaN  NaN  NaN  NaN
3  x  NaN  NaN  NaN  NaN  NaN
4  x    x    h    x    s    f

Explanation

Build the mask up

  1. df.ne('x') gives

            A      B     C      D     E     F
 0  False   True  True   True  True  True
 1   True   True  True  False  True  True
 2   True   True  True   True  True  True
 3  False   True  True   True  True  True
 4  False  False  True  False  True  True

  2. But we want that in conjunction with df.iloc[0].eq('R') which is a Series. Turns out that if we just & those two together, it will align the Series index with the columns of the mask in step 1.

     A    False
 B     True
 C    False
 D     True
 E    False
 F    False
 Name: 0, dtype: bool

 # &

        A      B     C      D     E     F
 0  False   True  True   True  True  True
 1   True   True  True  False  True  True
 2   True   True  True   True  True  True
 3  False   True  True   True  True  True
 4  False  False  True  False  True  True

 # GIVES YOU

        A      B      C      D      E      F
 0  False   True  False   True  False  False
 1  False   True  False  False  False  False
 2  False   True  False   True  False  False
 3  False   True  False   True  False  False
 4  False  False  False  False  False  False

  3. Finally, we want to exclude the first two rows from these shenanigans so...

     mask.iloc[:2] = False
user_23366540

Try with:

mask = df.iloc[0] !='R'

df.loc[2:, mask] = df.loc[2:,mask].where(df.loc[2:,mask]=='x')

Output:

     A  B    C    D    E    F
0    x  R    i    R  NaN    h
1    z  g    j    x    a  NaN
2  NaN  h  NaN    y  NaN  NaN
3    x  g  NaN  NaN  NaN  NaN
4    x  x  NaN    x  NaN  NaN
user_42384080

By your approach:

df[df.loc[2:,df.loc[0]=='R']!='x']=np.nan

Output:

>>> df
   A    B    C    D    E    F
0  x    R    i    R  NaN    h
1  z    g    j    x    a  NaN
2  z  NaN  NaN  NaN  NaN  NaN
3  x  NaN  NaN  NaN  NaN  NaN
4  x    x    h    x    s    f
user_147849120
Related