我有两个时间序列数据帧(约 45k 行对 5 行)。一个的时间戳低至毫秒,另一个的时间戳低至秒。我想在较大的数据框中创建一个新列,以便: a)一个值被附加到较大数据框中的行,其时间戳最接近(以秒为单位)较小数据框中的时间戳 b) 任何其他时间戳的 NaN。
larger df =
timestamp price
0 2018-04-24 06:01:02.600 1
1 2018-04-24 06:01:02.600 1
2 2018-04-24 06:01:02.600 2
3 2018-04-24 06:01:02.600 4
4 2018-04-24 06:01:02.775 2
5 2018-04-24 06:01:02.825 3
6 2018-04-24 06:01:03.050 5
7 2018-04-24 06:01:03.125 6
8 2018-04-24 06:01:03.275 7
9 2018-04-24 06:01:03.300 4
10 2018-04-24 06:01:03.300 3
11 2018-04-24 06:01:03.950 5
12 2018-04-24 06:01:04.050 5
smaller df =
timestamp price
0 24/04/2018 06:01:02 2
1 24/04/2018 12:33:37 4
2 24/04/2018 14:29:34 5
3 24/04/2018 15:02:50 6
4 24/04/2018 15:20:04 7
desired df =
timestamp price newCol
0 2018-04-24 06:01:02.600 1 aValue
1 2018-04-24 06:01:02.600 1 NaN
2 2018-04-24 06:01:02.600 2 NaN
3 2018-04-24 06:01:02.600 4 NaN
4 2018-04-24 06:01:02.775 2 NaN
5 2018-04-24 06:01:02.825 3 NaN
6 2018-04-24 06:01:03.050 5 NaN
7 2018-04-24 06:01:03.125 6 NaN
8 2018-04-24 06:01:03.275 7 NaN
9 2018-04-24 06:01:03.300 4 NaN
10 2018-04-24 06:01:03.300 3 NaN
11 2018-04-24 06:01:03.950 5 NaN
12 2018-04-24 06:01:04.050 5 NaN
非常感谢您的帮助。我对一般的编程还太陌生,无法轻松解决这个问题。
非常感谢
请您参考如下方法:
重建索引
为了只使用一次值,我必须从较小的数据帧跟踪时间戳。因此,当我使用 'nearest' reindex 时,我会包含这些值。然后我在掩码中使用 duplicated。
df_small_new = df_small.set_index('timestamp', drop=False)
df_small_new = df_small_new.reindex(df_large.timestamp, method='nearest')
df_large.assign(
newcol=df_small_new.price.mask(df_small_new.timestamp.duplicated()).values)
timestamp price newcol
0 2018-04-24 06:01:02.600 1 2.0
1 2018-04-24 06:01:02.600 1 NaN
2 2018-04-24 06:01:02.600 2 NaN
3 2018-04-24 06:01:02.600 4 NaN
4 2018-04-24 06:01:02.775 2 NaN
5 2018-04-24 06:01:02.825 3 NaN
6 2018-04-24 06:01:03.050 5 NaN
7 2018-04-24 06:01:03.125 6 NaN
8 2018-04-24 06:01:03.275 7 NaN
9 2018-04-24 06:01:03.300 4 NaN
10 2018-04-24 06:01:03.300 3 NaN
11 2018-04-24 06:01:03.950 5 NaN
12 2018-04-24 06:01:04.050 5 NaN
pandas.merge_asof
- 重命名小数据框中的
'price'列 - 确保将
方向设置为'nearest' - 这几乎回答了问题
pd.merge_asof(
df_large,
df_small.rename(columns={'price': 'newcol'}),
on='timestamp', direction='nearest'
)
timestamp price newcol
0 2018-04-24 06:01:02.600 1 2
1 2018-04-24 06:01:02.600 1 2
2 2018-04-24 06:01:02.600 2 2
3 2018-04-24 06:01:02.600 4 2
4 2018-04-24 06:01:02.775 2 2
5 2018-04-24 06:01:02.825 3 2
6 2018-04-24 06:01:03.050 5 2
7 2018-04-24 06:01:03.125 6 2
8 2018-04-24 06:01:03.275 7 2
9 2018-04-24 06:01:03.300 4 2
10 2018-04-24 06:01:03.300 3 2
11 2018-04-24 06:01:03.950 5 2
12 2018-04-24 06:01:04.050 5 2
