我有一个 Pandas DataFrame,格式如下:
I have a pandas DataFrame of the form:
id start_time sequence_no value
0 71 2018-10-17 20:12:43+00:00 114428 3
1 71 2018-10-17 20:12:43+00:00 114429 3
2 71 2018-10-17 20:12:43+00:00 114431 79
3 71 2019-11-06 00:51:14+00:00 216009 100
4 71 2019-11-06 00:51:14+00:00 216011 150
5 71 2019-11-06 00:51:14+00:00 216013 180
6 92 2019-12-01 00:51:14+00:00 114430 19
7 92 2019-12-01 00:51:14+00:00 114433 79
8 92 2019-12-01 00:51:14+00:00 114434 100
我想要做的是填补每个id
/start_time
组合中缺失的sequence_no
。例如,71
和2018-10-17 20:12:43+00:00
这一对id
/start_time
缺少序列号为114430。对于每个添加的缺失序列号,我还需要计算并插值缺失的value
列的值。因此,上述数据的最终处理结果如下: id start_time sequence_no value
0 71 2018-10-17 20:12:43+00:00 114428 3
1 71 2018-10-17 20:12:43+00:00 114429 3
2 71 2018-10-17 20:12:43+00:00 114430 41 **
3 71 2018-10-17 20:12:43+00:00 114431 79
4 71 2019-11-06 00:51:14+00:00 216009 100
5 71 2019-11-06 00:51:14+00:00 216010 125 **
6 71 2019-11-06 00:51:14+00:00 216011 150
7 71 2019-11-06 00:51:14+00:00 216012 165 **
8 71 2019-11-06 00:51:14+00:00 216013 180
9 92 2019-12-01 00:51:14+00:00 114430 19
10 92 2019-12-01 00:51:14+00:00 114431 39 **
11 92 2019-12-01 00:51:14+00:00 114432 59 **
12 92 2019-12-01 00:51:14+00:00 114433 79
13 92 2019-12-01 00:51:14+00:00 114434 100
(在新插入的行右侧添加**
以便更易读)
我最初解决这个问题的方案倚赖于Python循环处理大量数据表格,因此numpy和pandas似乎是发光发亮的理想之地。参考诸如 Pandas:创建用于填充数字间隙的行的SO答案,我得出了以下结论:
import pandas as pd
import numpy as np
# Generate dummy data
df = pd.DataFrame([
(71, '2018-10-17 20:12:43+00:00', 114428, 3),
(71, '2018-10-17 20:12:43+00:00', 114429, 3),
(71, '2018-10-17 20:12:43+00:00', 114431, 79),
(71, '2019-11-06 00:51:14+00:00', 216009, 100),
(71, '2019-11-06 00:51:14+00:00', 216011, 150),
(71, '2019-11-06 00:51:14+00:00', 216013, 180),
(92, '2019-12-01 00:51:14+00:00', 114430, 19),
(92, '2019-12-01 00:51:14+00:00', 114433, 79),
(92, '2019-12-01 00:51:14+00:00', 114434, 100),
], columns=['id', 'start_time', 'sequence_no', 'value'])
# create a new DataFrame with the min/max `sequence_no` values for each `id`/`start_time` pairing
by_start = df.groupby(['start_time', 'id'])
ranges = by_start.agg(
sequence_min=('sequence_no', np.min), sequence_max=('sequence_no', np.max)
)
reset = ranges.reset_index()
mins = reset['sequence_min']
maxes = reset['sequence_max']
# Use those min/max values to generate a sequence with ALL values in that range
expanded = pd.DataFrame(dict(
start_time=reset['start_time'].repeat(maxes - mins + 1),
id=reset['id'].repeat(maxes - mins + 1),
sequence_no=np.concatenate([np.arange(mins, maxes + 1) for mins, maxes in zip(mins, maxes)])
))
# Use the above generated DataFrame as an index to generate the missing rows, then interpolate
expanded_index = pd.MultiIndex.from_frame(expanded)
df.set_index(
['start_time', 'id', 'sequence_no']
).reindex(expanded_index).interpolate()
输出结果是正确的,但它运行速度与我的很多Python循环解决方案几乎相同。我相信有一些步骤可以简化,但在我的测试中最慢的部分似乎是reindex
。考虑到真实世界的数据几乎包含了一百万行(经常操作),有没有明显的方法可以比我已经编写的程序获得一些性能优势?有没有任何方法可以加速这个转换?
2019年9月12日更新
将此答案中的合并解决方案与扩展数据帧的原始构建相结合,在足够大的数据集上测试的结果最快:import pandas as pd
import numpy as np
# Generate dummy data
df = pd.DataFrame([
(71, '2018-10-17 20:12:43+00:00', 114428, 3),
(71, '2018-10-17 20:12:43+00:00', 114429, 3),
(71, '2018-10-17 20:12:43+00:00', 114431, 79),
(71, '2019-11-06 00:51:14+00:00', 216009, 100),
(71, '2019-11-06 00:51:14+00:00', 216011, 150),
(71, '2019-11-06 00:51:14+00:00', 216013, 180),
(92, '2019-12-01 00:51:14+00:00', 114430, 19),
(92, '2019-12-01 00:51:14+00:00', 114433, 79),
(92, '2019-12-01 00:51:14+00:00', 114434, 100),
], columns=['id', 'start_time', 'sequence_no', 'value'])
# create a ranges df with groupby and agg
ranges = df.groupby(['start_time', 'id'])['sequence_no'].agg([
('sequence_min', np.min), ('sequence_max', np.max)
])
reset = ranges.reset_index()
mins = reset['sequence_min']
maxes = reset['sequence_max']
# Use those min/max values to generate a sequence with ALL values in that range
expanded = pd.DataFrame(dict(
start_time=reset['start_time'].repeat(maxes - mins + 1),
id=reset['id'].repeat(maxes - mins + 1),
sequence_no=np.concatenate([np.arange(mins, maxes + 1) for mins, maxes in zip(mins, maxes)])
))
# merge expanded and df
merge = expanded.merge(df, on=['start_time', 'id', 'sequence_no'], how='left')
# interpolate and assign values
merge['value'] = merge['value'].interpolate()
merge
比reindex
快得多是正确的,但事实证明,在较大的数据集上,explode
非常慢。当将你的合并与扩展数据集的原始构建相结合时,我们得到了迄今为止最快的实现(请参见2019年9月12日问题的更新)。 - MBrizzlecopy=False
也可以加快速度,因为您将避免不必要的数据复制。merge = expanded.merge(df, on=['start_time', 'id', 'sequence_no'], how='left', copy=False)
- It_is_Chris