我有一个 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 的速度大致是线性的。
