在熊猫数据帧中发生滞后突变

Question

我有一些使用突变和滞后的R。我想在熊猫中复制这个。这是数据

已编辑：包括对分组依据和索引的需求

        Name       Date_x  
  0   American   2009-10-31
  1   American   2009-09-22
  2   Zydaco     2009-09-26
  3   American   2009-04-17
  4   American   2009-02-18
  5   American   2009-02-03
  6   American   2009-01-16
  7   Catalina   2009-09-02
  8   Zydaco     2009-08-29
  9   Zydaco     2009-08-15
 10   Zydaco     2009-06-26
 11   Zydaco     2009-10-27
 12   Zydaco     2009-10-13
 13   Zydaco     2009-04-04

这是 R 代码

test<-  test %.%                      #need dplyr %.% 
        group_by(name) %.%
        mutate(Date_y = lag(Date_x, 1))

编辑以反映此方法也失败这是我制作的 Python 代码，无法复制我试图实现的输出

import pandas as pd
pd.options.mode.chained_assignment = None  # default='warn'
test1 = test
test1['Rank'] = test1['Rank'] + 1
test1 = test1.drop('Name', 1)
test2 = pd.merge(test, test1, on='Rank')

这是使用 .shift 创建输出的尝试。 这似乎更有效率。但是输出不正确。

编辑;演示问题

test['Date_y'] = test.groupby(['Name'])['Date_x'].shift(-1)
test.sort(['Name', 'Date_x'], ascending=[1, 0])
                 Name     Date_x      Date_y
         American    2009-10-31  2009-09-22
         American    2009-09-22  2009-04-17
         American    2009-04-17  2009-02-18
         American    2009-02-18  2009-02-03
         American    2009-02-03  2009-01-16
         American    2009-01-16  NaN
         Catalina    2009-09-02  NaN
         Zydaco      2009-10-27  2009-10-13
         Zydaco      2009-10-13  2009-04-04
         Zydaco      2009-09-26  2009-08-29
         Zydaco      2009-08-29  2009-08-15
         Zydaco      2009-08-15  2009-06-26
         Zydaco      2009-06-26  2009-10-27
         Zydaco      2009-04-04  NaN

实现这一目标的最佳方法是什么？如果它有效，我想使用 .shift。还是有更好的方法？

这是不正确的行

          Zydaco         2009-06-26  2009-10-27

这将重现错误。

df = pd.Series        (['American','American','Zydaco','American','American','American','American','Catalina',
'Zydaco','Zydaco','Zydaco','Zydaco','Zydaco','Zydaco'])
df = pd.DataFrame(df)
df.columns = ['names']
df['date_x'] = pd.Series(['2009-10-31','2009-09-22','2009-09-26','2009-04-17','2009-02-18',' 2009-02-        03','2009-01-16','2009-09-02','2009-08-29','2009-08-15',' 2009-06-26',' 2009-10-27','2009-10-13','2009-       04-04'])
df['date_y'] = df.groupby(['names'])['date_x'].shift(-1)
mask = df['names'] == "Zydaco"
df = df[mask]
df['date_x'] = pd.to_datetime(df['date_x'])
df.groupby('date_x').apply(lambda d: d.sort()).reset_index('date_x',drop=True)

date_x从最远的日期到最近的日期按顺序排列。似乎 shift 不使用日期的顺序，而是使用索引顺序进行移位。

       names date_x     date_y
13   Zydaco 2009-04-04   NaN
10   Zydaco 2009-06-26   2009-10-27
9    Zydaco 2009-08-15   2009-06-26
8    Zydaco 2009-08-29   2009-08-15
2    Zydaco 2009-09-26   2009-08-29
12   Zydaco 2009-10-13   2009- 04-04
11   Zydaco 2009-10-27   2009-10-13

Answer 1

您的数据一开始

就没有排序，因此它将按这种无序顺序移动。如果要按排序方式移动它，请先在分组依据之前对其进行排序。例如：

In [49]: test['Date_y'] = test.sort('Date_x', ascending=False).groupby(['Name'])'Date_x'].shift(-1)
In [50]: test.sort(['Name', 'Date_x'], ascending=[1, 0])
Out[50]:
        Name      Date_x      Date_y
i
0   American  2009-10-31  2009-09-22
1   American  2009-09-22  2009-04-17
3   American  2009-04-17  2009-02-18
4   American  2009-02-18  2009-02-03
5   American  2009-02-03  2009-01-16
6   American  2009-01-16         NaN
7   Catalina  2009-09-02         NaN
11    Zydaco  2009-10-27  2009-10-13
12    Zydaco  2009-10-13  2009-09-26
2     Zydaco  2009-09-26  2009-08-29
8     Zydaco  2009-08-29  2009-08-15
9     Zydaco  2009-08-15  2009-06-26
10    Zydaco  2009-06-26  2009-04-04
13    Zydaco  2009-04-04         NaN

---

我不知道你是如何得到结果的（一个完全可运行的示例会有所帮助），但是如果我运行类似的东西，我会得到：

In [26]: s="""Name       Date_x   Rank
   ....: American    2009-10-31  6
   ....: American    2009-09-22  5
   ....: American    2009-04-17  4
   ....: American    2009-02-18  3
   ....: American    2009-02-03  2
   ....: American    2009-01-16  1
   ....: Catalina    2009-09-02  1
   ....: Zydaco      2009-10-27  7
   ....: Zydaco      2009-10-13  6
   ....: Zydaco      2009-09-26  5
   ....: Zydaco      2009-08-29  4
   ....: Zydaco      2009-08-15  3
   ....: Zydaco      2009-06-26  2
   ....: Zydaco      2009-04-04  1"""
In [27]: test = pd.read_csv(StringIO(s), delim_whitespace=True)
In [29]: test['Date_y'] = test.groupby(['Name'])['Date_x'].shift(-1)
In [30]: test
Out[30]:
        Name      Date_x  Rank      Date_y
0   American  2009-10-31     6  2009-09-22
1   American  2009-09-22     5  2009-04-17
2   American  2009-04-17     4  2009-02-18
3   American  2009-02-18     3  2009-02-03
4   American  2009-02-03     2  2009-01-16
5   American  2009-01-16     1         NaN
6   Catalina  2009-09-02     1         NaN
7     Zydaco  2009-10-27     7  2009-10-13
8     Zydaco  2009-10-13     6  2009-09-26
9     Zydaco  2009-09-26     5  2009-08-29
10    Zydaco  2009-08-29     4  2009-08-15
11    Zydaco  2009-08-15     3  2009-06-26
12    Zydaco  2009-06-26     2  2009-04-04
13    Zydaco  2009-04-04     1         NaN

这是你想要的吗？或者它有什么问题？

请注意，在这种情况下您不需要 groupby，因为Name列中只有一个名称，但我想这是因为您简化了示例。