title: Pandas Count zeros in time series
body: <p>I have a daily time series [1980 - present] where I need to check each daily timestep for zeros and systematically drop records.  I would ultimately like to vectorize this solution, so I can pre-process these operations before proceeding with my analysis. If I have the dataframe <code>df</code>:</p>
  
  <pre><code>         date               name  elev_exact      swe
  0  1990-10-30   COTTONWOOD_CREEK    2337.816  0.01524
  1  1990-10-30    EMIGRANT_SUMMIT    2252.472  0.00000
  2  1990-10-30     PHILLIPS_BENCH    2499.360  0.05334
  3  1990-10-30    PINE_CREEK_PASS    2048.256  0.00000
  4  1990-10-30  SALT_RIVER_SUMMIT    2328.672  0.00000
  5  1990-10-30      SEDGWICK_PEAK    2392.680  0.00000
  6  1990-10-30          SHEEP_MTN    2026.920  0.00000
  7  1990-10-30  SLUG_CREEK_DIVIDE    2202.180  0.00000
  8  1990-10-30       SOMSEN_RANCH    2072.640  0.00000
  9  1990-10-30   WILDHORSE_DIVIDE    1978.152  0.00000
  10 1990-10-30       WILLOW_CREEK    2462.784  0.01778
  11 1991-03-15   COTTONWOOD_CREEK    2337.816  0.41910
  12 1991-03-15    EMIGRANT_SUMMIT    2252.472  0.42418
  13 1991-03-15     PHILLIPS_BENCH    2499.360  0.52832
  14 1991-03-15    PINE_CREEK_PASS    2048.256  0.32258
  15 1991-03-15  SALT_RIVER_SUMMIT    2328.672  0.23876
  16 1991-03-15      SEDGWICK_PEAK    2392.680  0.39878
  17 1991-03-15          SHEEP_MTN    2026.920  0.31242
  18 1991-03-15  SLUG_CREEK_DIVIDE    2202.180  0.29464
  19 1991-03-15       SOMSEN_RANCH    2072.640  0.29972
  20 1991-03-15   WILDHORSE_DIVIDE    1978.152  0.35052
  21 1991-03-15       WILLOW_CREEK    2462.784  0.60706
  22 1991-10-25   COTTONWOOD_CREEK    2337.816  0.01270
  23 1991-10-25    EMIGRANT_SUMMIT    2252.472  0.01016
  24 1991-10-25     PHILLIPS_BENCH    2499.360  0.02286
  25 1991-10-25    PINE_CREEK_PASS    2048.256  0.00508
  26 1991-10-25  SALT_RIVER_SUMMIT    2328.672  0.01016
  27 1991-10-25      SEDGWICK_PEAK    2392.680  0.00254
  28 1991-10-25          SHEEP_MTN    2026.920  0.00000
  29 1991-10-25  SLUG_CREEK_DIVIDE    2202.180  0.00762
  30 1991-10-25       SOMSEN_RANCH    2072.640  0.00000
  31 1991-10-25   WILDHORSE_DIVIDE    1978.152  0.00508
  32 1991-10-25       WILLOW_CREEK    2462.784  0.02032
  </code></pre>
  
  <p>The problem is I want to find days where more than one zero <code>swe</code> measurement, and only keep the observation with the largest <code>elev_exact</code>. I then need to merge the desired zero record back into <code>df</code>.</p>
  
  <p>Here is a groupby loop that would achieve what I want:</p>
  
  <pre><code>result = pd.DataFrame()
  for name, group in df.groupby('date'):
  
      non_zero = group.where(group.swe &gt;0).dropna()
  
      if not group.equals(non_zero):
          zeros = group.where(group.swe == 0).dropna() 
          zero_kept = zeros.loc[zeros.elev_exact.idxmax()]
          out = non_zero.append(zero_kept)
          out = out[out.elev_exact &gt;= zero_kept.elev_exact]
          result = pd.concat([result, out])
      else:
          result = pd.concat([result, non_zero])
  </code></pre>
  
  <p>I dont mind using <code>groupby</code> but I would like to use it a little more methodically so I don't have the inner <code>if-else</code> loop. </p>
  
  <p>Here is how I am thinking about the problem</p>
  
  <ol>
  <li>For each daily timestep, I want to find where there are more than one zero measurement</li>
  </ol>
  
  <pre><code>zero_count = df.groupby('date').apply(lambda x: np.count_nonzero(x==0))
  zero_count = zero_count.where(zero_count &gt;1).dropna()
  </code></pre>
  
  <ol start="2">
  <li>Separate dates with where <code>zero_count &gt; 1</code></li>
  </ol>
  
  <pre><code>zero_fix = zero_count.where(zero_count &gt;1).dropna()
  </code></pre>
  
  <ol start="3">
  <li>Find the maximum elevation for each day with multiple zeros</li>
  </ol>
  
  <pre><code>fixes = df[df.date.isin(zero_fix.index)].dropna()
  fixes = fixes.loc[fixes[fixes.swe==0].groupby('date')['elev_exact'].idxmax().to_list()]
  </code></pre>
  
  <ol start="4">
  <li>Apply the found elevation thresholds back to <code>df</code>. </li>
  </ol>
  
  <pre><code>df.loc[:,'threshold'] = df.date.map(lu_dict)
  df = df.replace(np.nan, 0)
  df = df[df.elev_exact &gt;= df.threshold].drop('threshold', axis=1)
  </code></pre>
  
  <p>This also works, but the lambda function is step 1 is pretty slow.  Is there another way to count zeros?</p>
  
  <p>Expected output:</p>
  
  <pre><code>          date               name  elev_exact      swe
  2   1990-10-30     PHILLIPS_BENCH    2499.360  0.05334
  5   1990-10-30      SEDGWICK_PEAK    2392.680  0.00000
  10  1990-10-30       WILLOW_CREEK    2462.784  0.01778
  11  1991-03-15   COTTONWOOD_CREEK    2337.816  0.41910
  12  1991-03-15    EMIGRANT_SUMMIT    2252.472  0.42418
  13  1991-03-15     PHILLIPS_BENCH    2499.360  0.52832
  14  1991-03-15    PINE_CREEK_PASS    2048.256  0.32258
  15  1991-03-15  SALT_RIVER_SUMMIT    2328.672  0.23876
  16  1991-03-15      SEDGWICK_PEAK    2392.680  0.39878
  17  1991-03-15          SHEEP_MTN    2026.920  0.31242
  18  1991-03-15  SLUG_CREEK_DIVIDE    2202.180  0.29464
  19  1991-03-15       SOMSEN_RANCH    2072.640  0.29972
  20  1991-03-15   WILDHORSE_DIVIDE    1978.152  0.35052
  21  1991-03-15       WILLOW_CREEK    2462.784  0.60706
  22  1991-10-25   COTTONWOOD_CREEK    2337.816  0.01270
  23  1991-10-25    EMIGRANT_SUMMIT    2252.472  0.01016
  24  1991-10-25     PHILLIPS_BENCH    2499.360  0.02286
  26  1991-10-25  SALT_RIVER_SUMMIT    2328.672  0.01016
  27  1991-10-25      SEDGWICK_PEAK    2392.680  0.00254
  29  1991-10-25  SLUG_CREEK_DIVIDE    2202.180  0.00762
  30  1991-10-25       SOMSEN_RANCH    2072.640  0.00000
  32  1991-10-25       WILLOW_CREEK    2462.784  0.02032
  </code></pre>