如何用Pandas获取指定时间戳前1毫秒的最新ask_size值

需要为DataFrame的每行新增一列，取值为该行event_timestamp前1毫秒时刻的最新ask_size值。示例规则：

• 第1行前1毫秒的有效ask_size为165（尽管对应时间戳更早，但为该时刻的有效值）
• 第3至8行前1毫秒的最新值均为203

示例表格 #

idx event_timestamp                 ask_size
0   2024-02-12 09:00:00.178941829   165
1   2024-02-12 09:00:00.334673928   166
2   2024-02-12 09:00:00.334723166   203
3   2024-02-12 09:00:00.339505589   203
4   2024-02-12 09:00:00.339517572   241
5   2024-02-12 09:00:00.339585194   276
6   2024-02-12 09:00:00.339597200   276
7   2024-02-12 09:00:00.339679756   277
8   2024-02-12 09:00:00.339705796   312
9   2024-02-12 09:00:00.343967540   275
10  2024-02-12 09:00:00.393306026   275

原始数据 #

import pandas as pd

data = {
    'event_timestamp': ['2024-02-12 09:00:00.178941829', '2024-02-12 09:00:00.334673928',
                        '2024-02-12 09:00:00.334723166', '2024-02-12 09:00:00.339505589',
                        '2024-02-12 09:00:00.339517572', '2024-02-12 09:00:00.339585194',
                        '2024-02-12 09:00:00.339597200', '2024-02-12 09:00:00.339679756',
                        '2024-02-12 09:00:00.339705796', '2024-02-12 09:00:00.343967540'],
    'ask_size_1_x': [165.0, 166.0, 203.0, 203.0, 241.0, 276.0, 276.0, 277.0, 312.0, 275.0]
}

df = pd.DataFrame(data)

尝试过的代码及问题 #

第一次尝试 #

data['1ms'] = data['event_timestamp'] - pd.Timedelta(milliseconds=1)

temp = data[['event_timestamp','ask_size_1']]
temp_time_shift = data[['1ms','ask_size_1']]

temp2 = pd.merge_asof(
            temp,
            temp_time_shift,
            left_on = 'event_timestamp',
            right_on = '1ms',
            direction='backward'
        )

未得到预期结果。

补充尝试 #

import pandas as pd

data = {
    'event_timestamp': [
        '2024-02-12 09:00:00.393306026',
        '2024-02-12 09:00:00.393347792',
        '2024-02-12 09:00:00.393351971',
        '2024-02-12 09:00:00.393355738',
        '2024-02-12 09:00:00.393389724',
        '2024-02-12 09:00:00.542780521',
        '2024-02-12 09:00:00.542841917',
        '2024-02-12 09:00:00.714845055',
        '2024-02-12 09:00:00.714908862',
        '2024-02-12 09:00:00.747016524'
    ],
    'ask_size_1': [275.0, 275.0, 237.0, 237.0, 202.0, 202.0, 202.0, 262.0, 261.0, 263.0]
}

df = pd.DataFrame(data)
df['event_timestamp'] = pd.to_datetime(df['event_timestamp'])  # 转换为datetime格式

tolerance = pd.Timedelta('1ms')
df['out'] = pd.merge_asof(df['event_timestamp'].sub(tolerance),
                          df[['event_timestamp', 'ask_size_1']],
                          direction='forward', tolerance=tolerance
                         )['ask_size_1']

得到的输出中第7行的out值与ask_size_1相同（为262），但预期应为第6行的202（即该行时间戳前1毫秒的最新值）。输出结果如下：

event_timestamp             ask_size_1  out
0   2024-02-12 09:00:00.393306026   275.0   275.0
1   2024-02-12 09:00:00.393347792   275.0   275.0
2   2024-02-12 09:00:00.393351971   237.0   275.0
3   2024-02-12 09:00:00.393355738   237.0   275.0
4   2024-02-12 09:00:00.393389724   202.0   275.0
5   2024-02-12 09:00:00.542780521   202.0   202.0
6   2024-02-12 09:00:00.542841917   202.0   202.0
7   2024-02-12 09:00:00.714845055   262.0   262.0
8   2024-02-12 09:00:00.714908862   261.0   262.0
9   2024-02-12 09:00:00.747016524   263.0   263.0

正确解决方案 #

核心逻辑是：为每行计算event_timestamp - 1ms作为目标时间，然后在原数据中找到小于等于该目标时间的最新ask_size值，使用merge_asof的backward方向即可实现，同时要确保时间列已转为datetime类型，且右表按时间排序（merge_asof要求右表必须排序）。

代码如下：

import pandas as pd

data = {
    'event_timestamp': [
        '2024-02-12 09:00:00.393306026',
        '2024-02-12 09:00:00.393347792',
        '2024-02-12 09:00:00.393351971',
        '2024-02-12 09:00:00.393355738',
        '2024-02-12 09:00:00.393389724',
        '2024-02-12 09:00:00.542780521',
        '2024-02-12 09:00:00.542841917',
        '2024-02-12 09:00:00.714845055',
        '2024-02-12 09:00:00.714908862',
        '2024-02-12 09:00:00.747016524'
    ],
    'ask_size_1': [275.0, 275.0, 237.0, 237.0, 202.0, 202.0, 202.0, 262.0, 261.0, 263.0]
}

df = pd.DataFrame(data)
# 转换时间列为datetime类型
df['event_timestamp'] = pd.to_datetime(df['event_timestamp'])
# 计算每行的目标时间：event_timestamp 减1毫秒
df['target_time'] = df['event_timestamp'] - pd.Timedelta(milliseconds=1)
# 右表必须按时间排序，merge_asof要求
right_df = df[['event_timestamp', 'ask_size_1']].sort_values('event_timestamp')
# 使用merge_asof匹配小于等于target_time的最新ask_size
result = pd.merge_asof(
    df[['target_time', 'event_timestamp', 'ask_size_1']],
    right_df,
    left_on='target_time',
    right_on='event_timestamp',
    direction='backward',
    suffixes=('', '_prev_1ms')
)
# 重命名结果列
result.rename(columns={'ask_size_1_prev_1ms': 'ask_size_prev_1ms'}, inplace=True)
# 查看结果
print(result[['event_timestamp', 'ask_size_1', 'ask_size_prev_1ms']])

结果说明 #

运行后第7行的ask_size_prev_1ms会是202，符合预期。因为该行的target_time是2024-02-12 09:00:00.713845055，原数据中小于等于该时间的最新记录是第6行的2024-02-12 09:00:00.542841917，对应ask_size_1为202。

内容的提问来源于stack exchange，提问作者IGottaLearnMath