# Remove columns with a certain number of consecutive zeros

I'm using pandas and I am dealing with time series of sales.

What I would like to do is to remove the columns where a certain number of consecutive zeros appear, since forecasting for sparse series or repeated zero values tend to be unreliable. My dataframe has a bit more than 4400 time series for a time span of 5 years.

I have tried applying this strategy from a previous SO question but I can't drop the columns fullfilling the condition.

Below is an example where the threshold would be 12. Any column having 12 or more consecutive zeros would be discarded.

input :

      A   B   C

0     1   1   8
1     2   0  14
2     3   0  20
3     0   0  15
4     0   0  23
5     0   0  25
6     0   0  22
7     0   0  18
8     0   0  16
9     0   0  14
10    0   0   8
11    1   0  10
12   18   0   7
13   34   0   4
14  110   1  14
15   11  30   2
16    0  24   8
17    0  11   7
18    1  22  11
19    0  33   3
20    0  90  12
21   12  32  19
22   11  90  17
23   77  13   2


desired output:

      A   C

0     1   8
1     2  14
2     3  20
3     0  15
4     0  23
5     0  25
6     0  22
7     0  18
8     0  16
9     0  14
10    0   8
11    1  10
12   18   7
13   34   4
14  110  14
15   11   2
16    0   8
17    0   7
18    1  11
19    0   3
20    0  12
21   12  19
22   11  17
23   77   2


Thanks.

# Max number of zeros in a row
threshold = 12

# 1. transform the column to boolean is_zero
# 2. calculate the cumulative sum to get the number of cumulative 0
# 3. Check how much of each count you get and remove 0 counts
# 4. Get the maximum number of cumulative zeros
# 6. Drop the columns with more than your threshold

to_remove = []
for col in df.columns:
count_zeros = (df[col] == 0).cumsum().value_counts().drop(0)
if len(count_zeros) and count_zeros.idxmax() > threshold:
to_remove.append(col)

df = df.drop(to_remove, axis=1)


You're welcome

• Hey thanks for your help but it doesn't work, it throws an error because of the "drop(0)" – Nathan Furnal Apr 26 '19 at 8:22
• Have you tried investigating why before saying it does not work? This is probably an index error meaning there is no zero-counts... Easily avoided with an if statement checking whether the index exists before dropping it... – qmeeus Apr 27 '19 at 10:32
def max0(sr):
return (sr != 0).cumsum().value_counts().max() - (0 if (sr != 0).cumsum().value_counts().idxmax()==0 else 1)

max0(pd.Series([0,0,0,0]))

Output:
> 4

max0(pd.Series([1,0,0,0,0,2,3]))
Output:
> 4

max0(pd.Series([0]))
Output:

> 1

max0(pd.Series([1]))
Output:

> 0



You can try rolling().sum:

thresh = 12

to_drop = df.eq(0).rolling(thresh).sum().thresh).any()
df.loc[:, ~to_drop]


Output:

      A   C
0     1   8
1     2  14
2     3  20
3     0  15
4     0  23
5     0  25
6     0  22
7     0  18
8     0  16
9     0  14
10    0   8
11    1  10
12   18   7
13   34   4
14  110  14
15   11   2
16    0   8
17    0   7
18    1  11
19    0   3
20    0  12
21   12  19
22   11  17
23   77   2