这里有一种方法可以解决大部分问题,但对问题进行了一些简化。根据描述,代码可能也可以扩展以解决这些问题。此解决方案还可以适应时间序列间隙(跳过索引值)以及时间序列空白(有意的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()
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')
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)
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
测试一下:
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)
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)