pandas: Keep only every row that has cumulated change by a threshold?
<p>I'm interested to extract the rows where a column's value has either gone up cumulatively by at least 5 or gone down cumulatively by at least 5, then get the signs of these cumulative changes, <code>up_or_down</code>.</p>

<p>For example, let's say I want to apply this to column <code>y</code> in the following:</p>

<pre><code>df = pd.DataFrame({'x': range(16), 'y': [1,10,14,12,13,9,4,2,6,7,10,11,16,17,14,11]})
</code></pre>

<p>It should yield:</p>

<pre><code>x   y        # up_or_down
1   10       # +1
6   4        # -1
10  10       # +1
12  16       # +1
15  11       # -1
</code></pre>

<p>My dataframe is quite large so I was hoping that there is a nice vectorized way to do this natively with pandas's API rather than looping through it with <code>iterrows()</code>.</p>

<p>see <a class='doc-link' href="http://stackoverflow.com/documentation/pandas/1751/indexing-and-selecting-data/19351/path-dependent-slicing#t=201609082151350926644">Path Dependent Slicing</a></p>

<p>This is the core of the solution</p>

<pre><code>def big_diff(y):
    val = y.values
    r = val[0]
    for i, x in enumerate(val):
        d = r - x
        if abs(d) &gt;= 5:
            yield i, 1 if d &lt; 0 else -1
            r = x
</code></pre>

<p>Then you can do something like this</p>

<pre><code>slc = np.array(list(big_diff(df.y)))
df_slcd = pd.DataFrame(df.values[slc[:, 0]], df.index[slc[:, 0]], df.columns)
signs = pd.Series(slc[:, 1], df.index[slc[:, 0]], name='up_or_down')

df_slcd
</code></pre>

<p><a href="http://i.stack.imgur.com/ns6V5.png" rel="nofollow"><img src="http://i.stack.imgur.com/ns6V5.png" alt="enter image description here"></a></p>

<pre><code>signs

1     1
6    -1
10    1
12    1
15   -1
Name: up_or_down, dtype: int64
</code></pre>

<hr>

<pre><code>pd.concat([df_slcd, signs], axis=1)
</code></pre>

<p><a href="http://i.stack.imgur.com/xGzIR.png" rel="nofollow"><img src="http://i.stack.imgur.com/xGzIR.png" alt="enter image description here"></a></p>

python pandas numpy