0
$\begingroup$

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.

$\endgroup$
0
$\begingroup$

Short answer:

# 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

$\endgroup$
  • $\begingroup$ Hey thanks for your help but it doesn't work, it throws an error because of the "drop(0)" $\endgroup$ – NaturalFrequency Apr 26 at 8:22
  • $\begingroup$ 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... $\endgroup$ – qmeeus Apr 27 at 10:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.