Pandas select data between both index and value range

user_9546874 0
Viewed 331

I wish to set the values of a dataframe that lie between an index range and a value range to be NaN values. For example, say I have n columns, I want for every numeric data point in these columns to be set to NaN if they meet the following conditions:

  • The value is between -1 and 1
  • The index of this value is between 1 and 3

Below I have some code that is trying to do what I'm describing above, and it almost does it, it's just that it is setting these values on a copy of the original dataframe, and trying to use .loc throws the following error:

KeyError: "None of [Index([('a',), ('b',), ('c',)], dtype='object')] are in the [columns]"

import numpy as np
import pandas as pd


np.random.seed(398)

df = pd.DataFrame(np.random.randn(5, 3), columns=['a', 'b', 'c'])


row_indexer = (df.index > 0) & (df.index < 4)
col_indexer = (df > -1) & (df < 1)


df[row_indexer][col_indexer] = np.nan

I'm sure there's a really simple solution, I just can't figure out the correct syntax.

(Additionally, I want to "extract" these filtered values (the ones I'm setting to NaN) into a second dataframe, but I'm fairly sure any solution that solves the primary question will solve this additional issue)

Any help would be appreciated

pandas numpy python
2 Answers

Try broadcasting with numpy:

df[row_indexer[:,None] & col_indexer] = np.nan

Output:

          a         b         c
0 -1.810802 -0.776590 -0.495147
1  1.381038       NaN  2.334671
2       NaN -1.571401  1.011139
3 -1.200217 -1.013983       NaN
4  1.261759  0.863896  0.228914
user_42384080

I will do mul since True * True = True

out = df.mask(col_indexer.mul(row_indexer ,axis=0))
Out[81]: 
          a         b         c
0 -1.810802 -0.776590 -0.495147
1  1.381038       NaN  2.334671
2       NaN -1.571401  1.011139
3 -1.200217 -1.013983       NaN
4  1.261759  0.863896  0.228914
user_79645270
Related