Pandas DataFrame shift columns by date to create lag values
<p>I have a dataframe:</p>

<pre><code>df = pd.DataFrame({'year':[2000,2000,2000,2001,2001,2002,2002,2002],'ID':['a','b','c','a','b','a','b','c'],'values':[1,2,3,4,5,7,8,9]})
</code></pre>

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

<p>I would like to create a column that has the lag value of each ID-year, for example, ID'a' in 2000 has a value of 1, so ID'a' in 2001 should have a pre-value of 1. The key point is that if an ID doesn't have an value in the previous year (so the year is not continuous for some ID), then the pre-value should be NaN, instead of having the value from two years ago. For example, ID'c' doesn't show up in 2001, then for 2002, ID'c' should have pre-value = NaN.
Ideally, the final output should look like the following: 
<a href="https://i.stack.imgur.com/MBY73.png" rel="nofollow"><img src="https://i.stack.imgur.com/MBY73.png" alt="enter image description here"></a></p>

<p>I tried the df.groupby(['ID'])['values'].shift(1), but it gives the following: 
<a href="https://i.stack.imgur.com/I2ybO.png" rel="nofollow"><img src="https://i.stack.imgur.com/I2ybO.png" alt="enter image description here"></a></p>

<p>The problem is that when ID'c' doesn't have a value one year ago, the value two years ago is used. I also tried multiindex shift, which gives me the same result. </p>

<pre><code>df.set_index(['year','ID'], inplace = True)
df.groupby(level=1)['values'].shift(1)
</code></pre>

<p>The thing that works is the answer mentioned <a href="http://stackoverflow.com/questions/34699779/pandas-dataframe-shift-column-by-date">here</a>. But since my dataframe is fairly large, the merge kills the kernel. So far, I haven't figured out a better way to do it. I hope I explained my problem clearly. </p>

<p>Suppose the <code>year</code> column is unique for each id, i.e, there are no duplicated years for each specific id, then you can shift the value firstly and then replace shifted values where the difference between the year at the current row and previous row is not equal to <code>1</code> with <code>NaN</code>:</p>

<pre><code>import pandas as pd
import numpy as np
df['pre_value'] = df.groupby('ID')['values'].shift(1)
df['pre_value'] = df.pre_value.where(df.groupby('ID').year.diff() == 1, np.nan)
df
</code></pre>

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

python pandas dataframe panel-data