title: How to create a new metric column based on 1 year lag of a date column?
body: <p>I would like create a new column which references a date column - 1 year and displays the corresponding values:</p>
  <pre><code>import pandas as pd
  
  Input DF
  df = pd.DataFrame({'consumption': [0,1,3,5], 'date':[pd.to_datetime('2017-04-01'),
                                               pd.to_datetime('2017-04-02'),
                                               pd.to_datetime('2018-04-01'),
                                               pd.to_datetime('2018-04-02')]})    
  &gt;&gt;&gt; df
     consumption       date
      0            2017-04-01
      1            2017-04-02
      3            2018-04-01
      5            2018-04-02
  
  
  Expected DF
  
  df = pd.DataFrame({'consumption': [0,1,3,5], 
                'prev_year_consumption': [np.NAN,np.NAN,0,1],
                'date':[pd.to_datetime('2017-04-01'),
                          pd.to_datetime('2017-04-02'),
                          pd.to_datetime('2018-04-01'),
                          pd.to_datetime('2018-04-02')]})  
  
    &gt;&gt;&gt; df
        consumption  prev_year_consumption       date
                0                      NAN     2017-04-01
                1                      NAN     2017-04-02
                3                      0       2018-04-01
                5                      1       2018-04-02
  </code></pre>
  <p>So prev_year_consumption is are simply values from the consumption column where 1 year is subtracted from date dynamically.</p>
  <p>in SQL I would probably do something like</p>
  <pre><code>SELECT df_past.consumption as prev_year_consumption, df_current.consumption
  FROM df as df_current 
  LEFT JOIN ON df df_past ON year(df_current.date) = year(df_past.date) - 1
  </code></pre>
  <p>Appreciate any hints</p>