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
...

- davegravy

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)

- user2561747

Answer 2

这似乎有效：

def join_on_fifty_pct_overlap(s, df):
  df = df.copy()
  s_duration_delta = pd.Timedelta(seconds = s["duration"])
  df_duration_delta = pd.to_timedelta(df["duration"], unit='s')
  s_end_time = s.name + s_duration_delta
  df_end_time = df.index + df_duration_delta

  df.loc[df.index > s.name, "larger start time"] = df.loc[df.index > s.name].index
  df.loc[df.index <= s.name, "larger start time"] = s.name
  df.loc[df_end_time < s_end_time, "smaller end time"] = df_end_time
  df.loc[df_end_time >= s_end_time, "smaller end time"] = s_end_time
  delta = df["smaller end time"] - df["larger start time"]
  df = df.drop(["smaller end time", "larger start time", "duration"], axis=1)

  acceptable_overlap = delta / s_duration_delta >= 0.5
  matched_df = df[acceptable_overlap].iloc[0]
  df_final = pd.concat([s, matched_df])
  return df_final

df1.apply(join_on_fifty_pct_overlap, axis=1, args=[df2])

- davegravy