Removing rows with less than x consecutive non NaN values

user_13851653 0
Viewed 52

I have a data frame with a time_date index and 29 columns of data. The data frame has isolated Non-NaN values, which I want to remove (or convert into NaN) and only keep rows with, let's say at least 5 consecutive values. For example, initially, I have:

Date A B C D
18/01/2018 7:00 NaN NaN 3.493148804 -3.861461957
19/01/2018 6:00 0.000643658 NaN 4.493148804 -3.861461957
19/01/2018 7:00 0.003109299 NaN 7.247741699 -4.749528885
19/01/2018 8:00 0.003109299 -0.031979417 NaN -3.726334095
19/01/2018 9:00 0.003109299 -0.031979417 NaN 0.13656346
19/01/2018 10:00 NaN -0.031979417 NaN 2.823025544
19/01/2018 11:00 NaN -0.031979417 NaN 3.529650052
19/01/2018 12:00 NaN -0.038014129 0.006496742 4.243628979
19/01/2018 13:00 -0.003737779 NaN -0.003895367 5.595969041
19/01/2018 14:00 -0.003999399 NaN -0.013323511 6.294107278
19/01/2018 15:00 -0.003999399 2.823025544 -0.026859129 5.231494427
19/01/2018 16:00 -0.003999399 3.529650052 -0.031979417 5.075140158
19/01/2018 17:00 -0.003999399 NaN -0.038014129 4.057830334
19/01/2018 18:00 -0.003999399 NaN NaN 4.384686947

What I want is something like this:

Date A B C D
18/01/2018 7:00 NaN NaN NaN -3.861461957
19/01/2018 6:00 NaN NaN NaN -3.861461957
19/01/2018 7:00 NaN NaN NaN -4.749528885
19/01/2018 8:00 NaN -0.031979417 NaN -3.726334095
19/01/2018 9:00 NaN -0.031979417 NaN 0.13656346
19/01/2018 10:00 NaN -0.031979417 NaN 2.823025544
19/01/2018 11:00 NaN -0.031979417 NaN 3.529650052
19/01/2018 12:00 NaN -0.038014129 0.006496742 4.243628979
19/01/2018 13:00 -0.003737779 NaN -0.003895367 5.595969041
19/01/2018 14:00 -0.003999399 NaN -0.013323511 6.294107278
19/01/2018 15:00 -0.003999399 NaN -0.026859129 5.231494427
19/01/2018 16:00 -0.003999399 NaN -0.031979417 5.075140158
19/01/2018 17:00 -0.003999399 NaN -0.038014129 4.057830334
19/01/2018 18:00 -0.003999399 NaN NaN 4.384686947

The real number of required consective rows is 24. So any non NaN values less than 24 are converted into NaN. In other words, I only want episodes having length of at least 24 in my data. Any help would be much appreciated. Thank you

pandas dataframe nan python
1 Answers

Use a custom function to mask values is the consecutive non-NA are less than N, then apply to all columns:

def mask_below(s, N=5):
    m1 = s.isna()
    m2 = s.groupby(m1.cumsum()).transform('count').ge(N)
    return s.where(m1|m2)

df.set_index('Date').apply(mask_below).reset_index()

output:

                Date         A         B         C         D
0    18/01/2018 7:00       NaN       NaN       NaN -3.861462
1    19/01/2018 6:00       NaN       NaN       NaN -3.861462
2    19/01/2018 7:00       NaN       NaN       NaN -4.749529
3    19/01/2018 8:00       NaN -0.031979       NaN -3.726334
4    19/01/2018 9:00       NaN -0.031979       NaN  0.136563
5   19/01/2018 10:00       NaN -0.031979       NaN  2.823026
6   19/01/2018 11:00       NaN -0.031979       NaN  3.529650
7   19/01/2018 12:00       NaN -0.038014  0.006497  4.243629
8   19/01/2018 13:00 -0.003738       NaN -0.003895  5.595969
9   19/01/2018 14:00 -0.003999       NaN -0.013324  6.294107
10  19/01/2018 15:00 -0.003999       NaN -0.026859  5.231494
11  19/01/2018 16:00 -0.003999       NaN -0.031979  5.075140
12  19/01/2018 17:00 -0.003999       NaN -0.038014  4.057830
13  19/01/2018 18:00 -0.003999       NaN       NaN  4.384687
user_163434640
Related