Skip to main content
 首页 » 编程设计

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

2025年05月04日9kuangbin

我有一些使用 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 

请您参考如下方法:

您的数据一开始没有排序,因此将按此无序顺序移动。如果你想以排序的方式移动它,首先在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 列中只有一个名称,但我想那是因为您简化了示例。