基于多准则和偏移量的数据帧合并

Question

我有一个数据帧如下

              Date  Open  High   Low  Close  Volume  Open Interest  ContractYear Commodity Month_Code  ContractMonth Filename    CY
292715  03/04/1968  8.99  8.99  8.99   8.99       0              1          1969        ZL          F              1  ZL1969F  1968
292716  03/05/1968  9.02  9.02  8.96   8.96       1              2          1969        ZL          F              1  ZL1969F  1968
292717  03/06/1968  8.99  8.99  8.99   8.99       0              2          1969        ZL          F              1  ZL1969F  1968
292718  03/07/1968  9.01  9.01  9.01   9.01       0              2          1969        ZL          F              1  ZL1969F  1968
292719  03/08/1968  9.03  9.04  9.03   9.04       1              3          1969        ZL          F              1  ZL1969F  1968
292720  03/11/1968  9.03  9.04  9.01   9.01       9             10          1969        ZL          F              1  ZL1969F  1968
292721  03/12/1968  9.01  9.01  9.01   9.01       0             10          1969        ZL          F              1  ZL1969F  1968
292722  03/13/1968  9.00  9.00  9.00   9.00       0             10          1969        ZL          F              1  ZL1969F  1968
292723  03/14/1968  9.03  9.03  9.03   9.03       1             11          1969        ZL          F              1  ZL1969F  1968
292724  03/15/1968  8.97  9.01  8.97   9.01       1             12          1969        ZL          F              1  ZL1969F  1968

我尝试从数据框中提取值并按如下方式合并它们：

df1 = df[(df.Commodity == c) & (df.ContractMonth == n)]

df2 = df[(df.Commodity == c) & (df.ContractMonth == f) & (df.ContractYear == (df.ContractYear) +1)]

tempdf = pd.merge(df1, df2, how = 'inner', on = 'Date')

对于df2，我试图将过滤器值偏移1或到下一年。上面的代码/逻辑似乎不起作用。任何帮助都将不胜感激

Answer 1

使用shift，它将创建一个按给定行数偏移的新序列：

df2 = df[(df.Commodity == c) & (df.ContractMonth == f) & (df.ContractYear == (df.ContractYear.shift(-1))]