python - Pandas 数据框加入重叠的时间范围

Question

我有两个数据框。每个都有一个表示开始时间的时间戳索引和一个可用于计算结束时间的持续时间值(以秒为单位)。每个数据帧的时间间隔和持续时间不同，并且在每个数据帧内也可能不同。

                     duration   param1
Start Time (UTC) 
2017-10-14 02:00:31   60         95
2017-10-14 02:01:31   60         34
2017-10-14 02:02:31   60         10
2017-10-14 02:03:31   60         44
2017-10-14 02:04:31   60         63
2017-10-14 02:05:31   60         52
...

                     duration   param2
Start Time (UTC)
2017-10-14 02:00:00   300        93
2017-10-14 02:05:00   300        95
2017-10-14 02:10:00   300        91
...

我想加入这两个数据帧，这样第一个数据帧的索引和列得以保留，但第二个数据帧的参数值使用以下方案复制到它:

对于第一个数据帧中的每一行，从包含 50% 或更多时间范围的(排序的)第二个数据帧中的第一行分配 param2 值。

示例输出如下:

                     duration   param1   param2
Start Time (UTC) 
2017-10-14 02:00:31   60         95        93
2017-10-14 02:01:31   60         34        93
2017-10-14 02:02:31   60         10        93
2017-10-14 02:03:31   60         44        93
2017-10-14 02:04:31   60         63        95
2017-10-14 02:05:31   60         52        95
...

Answer 1

这是一种主要解决此问题的方法，但对问题进行了一些简化。如前所述，代码也可以扩展以解决这些问题。该解决方案对时间序列间隙(跳过的索引值)以及时间序列空白(故意 NaN)也很稳健。

import numpy as np
import pandas as pd

def merge_nearest(df_left, df_right):
    """
    Assumptions:
        1. constant duration in df_left # could be solved
           with a `df_left.groupby('duration')` which calls
           this function on each group
        2. which is always less than or equal to the variable
           duration of df_right # could probably just
           programatically get the min
    """
    df_left = df_left.sort_index()
    df_right = df_right.sort_index()
    min_duration = df_left['duration'].min() # seconds

    # merge nearest start times together, still blank df_right
    # values for the rest of each interval's duration
    matched = pd.merge_asof(df_left, df_right, left_index=True,
                            right_index=True, suffixes=('_left', '_right'),
                            tolerance=pd.Timedelta(min_duration / 2, unit='s'),
                            direction='nearest')

    # fancy forward fill that uses a variable timedelta-based limit
    righteous_cols = [col + '_right' if col in df_left.columns else col \
                      for col in df_right.columns]
    store_index = matched.index
    duration_string = f'{int(np.round(min_duration))}s'
    index_gaps_to_blanks = pd.date_range(start=matched.index.min().round(duration_string),
                                         end=matched.index.max().round(duration_string),
                                         freq=duration_string)
    rounded = matched.index.round(duration_string)
    tolerances = matched.index - rounded
    matched.index = rounded
    matched = matched.reindex(index=index_gaps_to_blanks)
    # this ffill is just to group properly
    grouped = matched.fillna(method='ffill').groupby('duration_right', sort=False)
    for duration, index_group in grouped.groups.items():
        fill_limit = int(np.round(duration / min_duration)) - 1
        if fill_limit > 0:
            matched.loc[index_group, righteous_cols] = \
            matched.loc[index_group, righteous_cols].fillna(method='ffill',
                                                            limit=fill_limit)
    matched = matched.reindex(index=store_index, method='nearest', tolerance=np.abs(tolerances))
    return matched

测试:

# sample data

# 1 minute timeseries with 1 day gap
arr = np.linspace(25, 55, 100)
sotime = pd.date_range(start='2017-10-14 02:00:31', freq='1min', 
                       periods=100, name='Start Time (UTC)')
sotime = sotime[:27].append(sotime[27:] + pd.Timedelta(1, unit='day'))
sodf = pd.DataFrame(dict(level=arr.round(2), duration=[60.0] * 100), index=sotime)

# an offset 5, 10, 1 minute timeseries also with an offset 1 day gap
arr = np.linspace(0, 2.5, 29)
turtime1 = pd.date_range(start='2017-10-14 02:10:00', freq='5min', 
                         periods=6, name='Start Time (UTC)')
turtime2 = pd.date_range(start='2017-10-14 02:40:00', freq='10min', 
                         periods=3, name='Start Time (UTC)')
turtime3 = pd.date_range(start='2017-10-14 03:10:00', freq='1min', 
                         periods=20, name='Start Time (UTC)')
turtime = turtime1.append(turtime2).append(turtime3)
turtime = turtime[:4].append(turtime[4:] + pd.Timedelta(1, unit='day'))
turdf = pd.DataFrame(dict(power=arr.round(2), 
                          duration=[300] * 6 + [600] * 3 + [60] * 20), index=turtime)

merge_nearest(sodf, turdf)