Skip to main content
 首页 » 编程设计

python之Pandas 将上一时期的数据设置为新的 DataFrame 列

2025年05月04日16飞鱼

我有一个 Pandas 数据框:

import pandas as pd 
 
df = pd.DataFrame([['A', '2014-01-01', '2014-01-07', 1.2], 
                   ['B', '2014-01-01', '2014-01-07', 2.5], 
                   ['C', '2014-01-01', '2014-01-07', 3.], 
                   ['A', '2014-01-08', '2014-01-14', 13.], 
                   ['B', '2014-01-08', '2014-01-14', 2.], 
                   ['C', '2014-01-08', '2014-01-14', 1.], 
                   ['A', '2014-01-15', '2014-01-21', 10.], 
                   ['A', '2014-01-21', '2014-01-27', 98.], 
                   ['B', '2014-01-21', '2014-01-27', -5.], 
                   ['C', '2014-01-21', '2014-01-27', -72.], 
                   ['A', '2014-01-22', '2014-01-28', 8.], 
                   ['B', '2014-01-22', '2014-01-28', 25.], 
                   ['C', '2014-01-22', '2014-01-28', -23.], 
                   ['A', '2014-01-22', '2014-02-22', 8.], 
                   ['B', '2014-01-22', '2014-02-22', 25.], 
                   ['C', '2014-01-22', '2014-02-22', -23.], 
                  ], columns=['Group', 'Start Date', 'End Date', 'Value']) 

输出看起来像这样:

   Group  Start Date    End Date  Value 
0      A  2014-01-01  2014-01-07    1.2 
1      B  2014-01-01  2014-01-07    2.5 
2      C  2014-01-01  2014-01-07    3.0 
3      A  2014-01-08  2014-01-14   13.0 
4      B  2014-01-08  2014-01-14    2.0 
5      C  2014-01-08  2014-01-14    1.0 
6      A  2014-01-15  2014-01-21   10.0 
7      A  2014-01-21  2014-01-27   98.0 
8      B  2014-01-21  2014-01-27   -5.0 
9      C  2014-01-21  2014-01-27  -72.0 
10     A  2014-01-22  2014-01-28    8.0 
11     B  2014-01-22  2014-01-28   25.0 
12     C  2014-01-22  2014-01-28  -23.0 
13     A  2014-01-22  2014-02-22    8.0 
14     B  2014-01-22  2014-02-22   25.0 
15     C  2014-01-22  2014-02-22  -23.0 

我正在尝试添加一个新列,其中包含上一时期同一组的数据(如果存在)。所以,输出应该是这样的:

   Group  Start Date    End Date  Value   Last Period Value 
0      A  2014-01-01  2014-01-07    1.2                 NaN 
1      B  2014-01-01  2014-01-07    2.5                 NaN 
2      C  2014-01-01  2014-01-07    3.0                 NaN 
3      A  2014-01-08  2014-01-14   13.0                 1.2 
4      B  2014-01-08  2014-01-14    2.0                 2.5    
5      C  2014-01-08  2014-01-14    1.0                 3.0 
6      A  2014-01-15  2014-01-21   10.0                13.0  
7      A  2014-01-21  2014-01-27   98.0                 NaN 
8      B  2014-01-21  2014-01-27   -5.0                 NaN 
9      C  2014-01-21  2014-01-27  -72.0                 NaN 
10     A  2014-01-22  2014-01-28    8.0                10.0      
11     B  2014-01-22  2014-01-28   25.0                 NaN 
12     C  2014-01-22  2014-01-28  -23.0                 NaN 
13     A  2014-01-22  2014-02-22    8.0                 NaN    
14     B  2014-01-22  2014-02-22   25.0                 NaN    
15     C  2014-01-22  2014-02-22  -23.0                 NaN    

请注意,具有 NaN 的行在同一组中没有对应的值,并且在最后一个周期中。因此,跨越 7 天(一周)的行需要与来自同一组但来自前一周的同一行相匹配。

请您参考如下方法:

假设我们计算每一行的 Start 和 End 之间的持续时间:

df['duration'] = df['End']-df['Start'] 

并假设我们还根据该持续时间计算之前的开始值:

df['Prev'] = df['Start'] - df['duration'] - pd.Timedelta(days=1) 

然后我们可以将所需的 DataFrame 表示为 df 和它自身之间的merge 的结果,我们合并 Group 的行,duration 和 Prev(在一个 DataFrame 中)匹配 Group、duration 和 Start(在另一个 DataFrame):

import pandas as pd 
 
df = pd.DataFrame([['A', '2014-01-01', '2014-01-07', 1.2], 
                   ['B', '2014-01-01', '2014-01-07', 2.5], 
                   ['C', '2014-01-01', '2014-01-07', 3.], 
                   ['A', '2014-01-08', '2014-01-14', 3.], 
                   ['B', '2014-01-08', '2014-01-14', 2.], 
                   ['C', '2014-01-08', '2014-01-14', 1.], 
                   ['A', '2014-01-15', '2014-01-21', 10.], 
                   ['A', '2014-01-21', '2014-01-27', 98.], 
                   ['B', '2014-01-21', '2014-01-27', -5.], 
                   ['C', '2014-01-21', '2014-01-27', -72.], 
                   ['A', '2014-01-22', '2014-01-28', 8.], 
                   ['B', '2014-01-22', '2014-01-28', 25.], 
                   ['C', '2014-01-22', '2014-01-28', -23.], 
                   ['A', '2014-01-22', '2014-02-22', 8.], 
                   ['B', '2014-01-22', '2014-02-22', 25.], 
                   ['C', '2014-01-22', '2014-02-22', -23.], 
                  ], columns=['Group', 'Start', 'End', 'Value']) 
for col in ['Start', 'End']: 
    df[col] = pd.to_datetime(df[col]) 
 
df['duration'] = df['End']-df['Start'] 
df['Prev'] = df['Start'] - df['duration'] - pd.Timedelta(days=1) 
 
result = pd.merge(df, df[['Group','duration','Start','Value']], how='left', 
                  left_on=['Group','duration','Prev'],  
                  right_on=['Group','duration','Start'], suffixes=['', '_y']) 
result = result[['Group', 'Start', 'End', 'Value', 'Value_y']] 
result = result.rename(columns={'Value_y':'Prev Value'}) 
print(result) 

产量

   Group      Start        End  Value  Prev Value 
0      A 2014-01-01 2014-01-07    1.2         NaN 
1      B 2014-01-01 2014-01-07    2.5         NaN 
2      C 2014-01-01 2014-01-07    3.0         NaN 
3      A 2014-01-08 2014-01-14    3.0         1.2 
4      B 2014-01-08 2014-01-14    2.0         2.5 
5      C 2014-01-08 2014-01-14    1.0         3.0 
6      A 2014-01-15 2014-01-21   10.0         3.0 
7      A 2014-01-21 2014-01-27   98.0         NaN 
8      B 2014-01-21 2014-01-27   -5.0         NaN 
9      C 2014-01-21 2014-01-27  -72.0         NaN 
10     A 2014-01-22 2014-01-28    8.0        10.0 
11     B 2014-01-22 2014-01-28   25.0         NaN 
12     C 2014-01-22 2014-01-28  -23.0         NaN 
13     A 2014-01-22 2014-02-22    8.0         NaN 
14     B 2014-01-22 2014-02-22   25.0         NaN 
15     C 2014-01-22 2014-02-22  -23.0         NaN 

在评论中,Artur Nowak 询问了pd.merge 的时间复杂度。我相信它正在做一个 O(N + M) 哈希连接,其中 N 是哈希表的大小,而 M 是查找表。下面是一些代码,用于根据经验测试 pd.merge 的性能作为 DataFrame 大小的函数。

import collections 
import string 
import timeit  
import numpy as np 
import pandas as pd 
from scipy import stats 
import matplotlib.pyplot as plt 
 
timing = collections.defaultdict(list) 
 
def make_df(ngroups, ndur, ndates): 
    groups = list(string.uppercase[:ngroups]) 
    durations = range(ndur) 
    start = pd.date_range('2000-1-1', periods=ndates, freq='D') 
 
    index = pd.MultiIndex.from_product([start, durations, groups],  
                                       names=['Start', 'duration', 'Group']) 
    values = np.arange(len(index)) 
    df = pd.DataFrame({'Value': values}, index=index).reset_index() 
    df['End'] = df['Start'] + pd.to_timedelta(df['duration'], unit='D') 
    df = df.drop('duration', axis=1) 
    df = df[['Group', 'Start', 'End', 'Value']] 
 
    df['duration'] = df['End']-df['Start'] 
    df['Prev'] = df['Start'] - df['duration'] - pd.Timedelta(days=1) 
    return df 
 
def using_merge(df): 
    result = pd.merge(df, df[['Group','duration','Start','Value']], how='left', 
                      left_on=['Group','duration','Prev'],  
                      right_on=['Group','duration','Start'], suffixes=['', '_y']) 
    return result 
 
Ns = np.array([10**i for i in range(5)]) 
for n in Ns: 
    timing['merge'].append(timeit.timeit( 
        'using_merge(df)', 
        'from __main__ import using_merge, make_df; df = make_df(10, 10, {})'.format(n), 
        number=5)) 
 
print(timing['merge']) 
slope, intercept, rval, pval, stderr = stats.linregress(Ns, timing['merge']) 
print(slope, intercept, rval, pval, stderr) 
 
plt.plot(Ns, timing['merge'], label='merge') 
plt.plot(Ns, slope*Ns + intercept) 
plt.legend(loc='best') 
plt.show() 

这表明对于数万行的 DataFrames,pd.merge 的速度大致是线性的。