我有两个时间序列数据框(~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
您的帮助将不胜感激。我对编程仍然太陌生，无法轻松解决这个问题。
非常感谢
reindex
为了只使用这些值一次，我必须从较小的数据帧跟踪时间戳。 所以我在reindex时包括这些值'nearest'. 然后我在面具中使用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'列 - 确保将
direction设置为'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