title: how to efficienlty do dataframe shift with a jump
body: <p>I have 60 log files that I am analysis. From those files I created the dataframe <code>state_n_ev_mat</code>. Its size is <strong><code>[2083656 rows x 8 columns]</code></strong>. Below you can see the first 50 rows of <code>state_n_ev_mat</code>.</p>
  
  <p>Where, <code>block</code> identifies each file, <code>ts</code> is the current timeslot, <code>n_ts</code> are the next 24 open timeslots for the current <code>ts</code>. <code>n_cp</code> is the clearing price that ocurred in <code>ts</code> for <code>n_ts</code>, <code>c_cp</code> the clearing price of previous <code>ts</code> for <code>n_ts</code>. For the moment don't consider the rest of the columns.</p>
  
  <p>What I'm trying to do is to populate <code>c_cp</code> with the clearing prices of the previous <code>ts</code>. Except for the very first 24 since there was no activity prior to that.</p>
  
  <p>For example, the <code>c_cp</code> value of <code>row 24</code> (i.e. 27.9) is the value of <code>n_cp</code> in <code>row 1</code>. It is basically a shift up to the 23rd value since the 24th <code>n_ts</code> wasn't open in the previous <code>ts</code>, but discarding the initial <code>n_cp</code> value since that <code>ts</code> is no longer open in the next <code>ts</code>.</p>
  
  <p>What I mean with the latter is that in <code>ts=361</code> the <code>n_ts=361</code> is no longer available. </p>
  
  <p>In few words, <code>c_cp</code> is populated from the previous 23 data points in <code>n_cp</code> leaving the 24th as <code>NaN</code>. </p>
  
  <p>Does this makes sense?</p>
  
  <p>My attempt is:</p>
  
  <pre><code> for m in range(60):
      last_current = last_ts[m]-24
      for n in range(360,last_current):
          state_n_ev_mat.c_cp.loc[(state_n_ev_mat.block==m) &amp; (state_n_ev_mat.ts==n+1)] = \
              state_n_ev_mat.n_cp.loc[(state_n_ev_mat.block==m) &amp; (state_n_ev_mat.ts==n)].shift(-1).values  
  </code></pre>
  
  <p>This approach is taking a lot of time to finish. <strong>So far it is computing the 56th block and the running time is 18 hrs.</strong></p>
  
  <p><strong>Is there a better way of doing this? Thanks</strong></p>
  
  <p>Below the first 50 rows of <code>state_n_ev_mat</code>, after one loop of the inner <code>for</code>.</p>
  
  <pre><code>   block   ts  n_ts  n_cp  c_cp  c_w  f_w  p_w
  0      0  360 361.0  27.5   NaN  NaN  NaN  NaN
  1      0  360 362.0  27.9   NaN  NaN  NaN  NaN
  2      0  360 363.0  27.9   NaN  NaN  NaN  NaN
  3      0  360 364.0  27.7   NaN  NaN  NaN  NaN
  4      0  360 365.0  34.0   NaN  NaN  NaN  NaN
  5      0  360 366.0  33.9   NaN  NaN  NaN  NaN
  6      0  360 367.0  34.1   NaN  NaN  NaN  NaN
  7      0  360 368.0  33.7   NaN  NaN  NaN  NaN
  8      0  360 369.0  40.3   NaN  NaN  NaN  NaN
  9      0  360 370.0  40.1   NaN  NaN  NaN  NaN
  10     0  360 371.0  50.1   NaN  NaN  NaN  NaN
  11     0  360 372.0  40.1   NaN  NaN  NaN  NaN
  12     0  360 373.0  44.4   NaN  NaN  NaN  NaN
  13     0  360 374.0 106.0   NaN  NaN  NaN  NaN
  14     0  360 375.0  74.9   NaN  NaN  NaN  NaN
  15     0  360 376.0  95.4   NaN  NaN  NaN  NaN
  16     0  360 377.0 125.0   NaN  NaN  NaN  NaN
  17     0  360 378.0 139.1   NaN  NaN  NaN  NaN
  18     0  360 379.0 133.3   NaN  NaN  NaN  NaN
  19     0  360 380.0 124.0   NaN  NaN  NaN  NaN
  20     0  360 381.0 139.1   NaN  NaN  NaN  NaN
  21     0  360 382.0  53.1   NaN  NaN  NaN  NaN
  22     0  360 383.0  45.1   NaN  NaN  NaN  NaN
  23     0  360 384.0  46.0   NaN  NaN  NaN  NaN
  24     0  361 362.0  36.4  27.9  NaN  NaN  NaN
  25     0  361 363.0  31.8  27.9  NaN  NaN  NaN
  26     0  361 364.0  31.2  27.7  NaN  NaN  NaN
  27     0  361 365.0  33.4  34.0  NaN  NaN  NaN
  28     0  361 366.0  34.2  33.9  NaN  NaN  NaN
  29     0  361 367.0   0.0  34.1  NaN  NaN  NaN
  30     0  361 368.0   0.0  33.7  NaN  NaN  NaN
  31     0  361 369.0   0.0  40.3  NaN  NaN  NaN
  32     0  361 370.0   0.0  40.1  NaN  NaN  NaN
  33     0  361 371.0   0.0  50.1  NaN  NaN  NaN
  34     0  361 372.0   0.0  40.1  NaN  NaN  NaN
  35     0  361 373.0   0.0  44.4  NaN  NaN  NaN
  36     0  361 374.0   0.0 106.0  NaN  NaN  NaN
  37     0  361 375.0   0.0  74.9  NaN  NaN  NaN
  38     0  361 376.0   0.0  95.4  NaN  NaN  NaN
  39     0  361 377.0   0.0 125.0  NaN  NaN  NaN
  40     0  361 378.0   0.0 139.1  NaN  NaN  NaN
  41     0  361 379.0   0.0 133.3  NaN  NaN  NaN
  42     0  361 380.0   0.0 124.0  NaN  NaN  NaN
  43     0  361 381.0   0.0 139.1  NaN  NaN  NaN
  44     0  361 382.0   0.0  53.1  NaN  NaN  NaN
  45     0  361 383.0   0.0  45.1  NaN  NaN  NaN
  46     0  361 384.0   0.0  46.0  NaN  NaN  NaN
  47     0  361 385.0  46.2   NaN  NaN  NaN  NaN
  48     0  362 363.0  39.0   NaN  NaN  NaN  NaN
  49     0  362 364.0  33.1   NaN  NaN  NaN  NaN
  </code></pre>