Merge Pandas Time Series Datasets on numerically nearest index, full outer join, aggregate column to max

user_736987 0
Viewed 340

I have two time series, they have overlapping events, but both also have distinct events that the other does not contain. Timestamps for overlapping events are close, but not guaranteed to be equal.

I want to merge these two datasets in a way that takes the max of the two max_val columns when the events overlap, and keeps the distinct events in the merged set with the max_val they were set with originally.

I've played with various merge_asof, groupby combinations, but I'm new to python and struggling to get anything that works as intended, more or less is readable and intuitive.

Note: The example data has integers as the timestamp for ease of setup, but the actual data is real timestamps that should get the equivalent of a merge_asof(direction="nearest", tolerance="10ms"). I don't see how to merge_asof as a full outer join, though. Seems only to provide a left join as far as I can tell. 

import pandas
df1 = pandas.DataFrame([[1.002,18],[2,22],[3,77],[5,23]], columns=["timestamp", "max_val"])
df2 = pandas.DataFrame([[1,33],[2,12],[3.001,87],[4,54]], columns=["timestamp", "max_val"])

merged_df = pandas.merge_asof(df1,df2, on="timestamp")

print(df1)
print(df2)
print(merged_df)

Output:

   timestamp  max_val
0      1.002       18
1      2.000       22
2      3.000       77
3      5.000       23
   timestamp  max_val
0      1.000       33
1      2.000       12
2      3.001       87
3      4.000       54
   timestamp  max_val_x  max_val_y
0      1.002         18         33
1      2.000         22         12
2      3.000         77         12
3      5.000         23         54

Desired Result: (don't care which timestamp persists when merged)

   timestamp  max_val
0      1.002         33
1      2.000         22
2      3.000         87
3      4.000         54
4      5.000         23
pandas time-series dataframe merge python
1 Answers

I would do one merge_asof followed by an outer merge:

# dummy variable for later join
df2['Rank'] = df2['timestamp'].rank()

new_df = (pd.merge_asof(df1, df2, 
              on='timestamp',
              direction='nearest', 
              tolerance=0.01)
   .merge(df2, on='Rank', how='outer')
   .assign(timestamp = lambda x: x.filter(like='timestamp').bfill(1).iloc[:,0])
   .assign(max_val=lambda x: x.filter(like='max_val').max(1))
   .sort_values('timestamp')
   [['timestamp','max_val']]
)

Output:

   timestamp  max_val
0      1.002     33.0
1      2.000     22.0
2      3.000     87.0
4      4.000     54.0
3      5.000     23.0
user_42384080
Related