python - 在 Pandas DataFrame 中因滞后而发生变异

Question

我有一些使用 mutate 和 lag 的 R。我想在 Pandas 中复制它。这是数据

编辑:包括分组依据和索引的需要

        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))

编辑以反射(reflect)这种方法也失败了这是我编写的 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

您的数据一开始没有排序，因此将按此无序顺序移动。如果你想以排序的方式移动它，首先在groupby之前对其进行排序。例如:

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 列中只有一个名称，但我想那是因为您简化了示例。