title: How to generate lagged variable for unbalanced panel in Pandas dataframe?
body: <p>For unbalanced panel data, it's hard for me to generate lagged variable, especially the lagged length is more than 2. For example, I have a dataset that is a unbalanced panel data. The objective of the task is to generate a lagged 2-month variable.</p>
  <pre><code>import pandas as pd
  import numpy as np 
  
  a=[[1,'1990/1/1',1],
  [1,'1990/2/1',2],
  [1,'1990/3/1',3],
  [2,'1989/12/1',3],
  [2,'1990/1/1',3],
  [2,'1990/2/1',4],
  [2,'1990/3/1',5.5],
  [2,'1990/4/1',5],
  [2,'1990/6/1',6]]
  
  data=pd.DataFrame(a,columns=['id','date','value'])
  data['date']=pd.to_datetime(data['date'])
  </code></pre>
  <p>Currently, My solution is</p>
  <pre><code>data['lag2value']=np.where((data.groupby('id')['date'].diff(2)/np.timedelta64(1, 'M')).fillna(0).round()==2,
          data.sort_values(['id','date']).groupby('id')['value'].shift(2),np.nan)
  </code></pre>
  <p>However, for the last obs, it does have a lagged two-month observation, that's to say the date <code>1990-6-1</code> corresponds to the <code>1990-4-1</code>. My codes cannot figure it out.</p>
  <pre><code>   id       date  value  lag2value
  0   1 1990-01-01    1.0        NaN
  1   1 1990-02-01    2.0        NaN
  2   1 1990-03-01    3.0        1.0
  3   2 1989-12-01    3.0        NaN
  4   2 1990-01-01    3.0        NaN
  5   2 1990-02-01    4.0        3.0
  6   2 1990-03-01    5.5        3.0
  7   2 1990-04-01    5.0        4.0
  8   2 1990-06-01    6.0        NaN
  </code></pre>
  <p>One possible solution is to build a complete date table that is a balanced panel dataset, and merge the current table to it. However, if the working data is large, it's time-comsuming to work on the complete table.</p>
  <p>I want to know any elegent solution to the problem? Thanks in advance.</p>