I'm trying to combine two Pandas dataframes together where they both have a timestamp column with some of the same timestamp values, some different, and several other different columns. Because the only column these dataframes share is the timestamp column, how I want to combine them is such that if there's a matching timestamp value, then I just want to merge the columns together for that row. If the timestamp is different, then I still want to merge columns together, but add in NaNs in the columns of the dataframe that didn't have that timestamp. Please check out the sample code below for a clearer explanation:
import numpy as np
import pandas as pd
gt = pd.DataFrame({'time':np.array([1.5, 2, 3]),
'gt_x':np.array([50, 55, 57.5]),
'gt_y':np.array([60, 65, 67.5]),
'gt_z':np.array([70, 75, 77.5])})
svo = pd.DataFrame({'time':np.array([1.5, 2.5, 3.5]),
'svo_x':np.array([11.1, 7.4, 15.7]),
'svo_y':np.array([13.2, 34.5, 32.8]),
'svo_z':np.array([17.3, 23.6, 19.9])})
i_want_this_after_merge = pd.DataFrame({'time':np.array([1.5, 2.0, 2.5, 3.0, 3.5]),
'gt_x':np.array([50, 55, np.nan, 57.5, np.nan]),
'gt_y':np.array([60, 65, np.nan, 67.5, np.nan]),
'gt_z':np.array([70, 75, np.nan, 77.5, np.nan]),
'svo_x':np.array([11.1, np.nan, 7.4, np.nan, 15.7]),
'svo_y':np.array([13.2, np.nan, 34.5, np.nan, 32.8]),
'svo_z':np.array([17.3, np.nan, 23.6, np.nan, 19.9])})
In case you're unable to run the code, this is what the dataframes in the above code sample look like:
gt:
time gt_x gt_y gt_z
0 1.5 50.0 60.0 70.0
1 2.0 55.0 65.0 75.0
2 3.0 57.5 67.5 77.5
svo:
time svo_x svo_y svo_z
0 1.5 11.1 13.2 17.3
1 2.5 7.4 34.5 23.6
2 3.5 15.7 32.8 19.9
And after merging these two, this is the output I'm looking for:
time gt_x gt_y gt_z svo_x svo_y svo_z
0 1.5 50.0 60.0 70.0 11.1 13.2 17.3
1 2.0 55.0 65.0 75.0 NaN NaN NaN
2 2.5 NaN NaN NaN 7.4 34.5 23.6
3 3.0 57.5 67.5 77.5 NaN NaN NaN
4 3.5 NaN NaN NaN 15.7 32.8 19.9
I've gone through several posts seemingly related to this question, and tried things such as using the pandas merge function with various join conditions, such as "inner". I've also tried pandas.merge_asof, but that hasn't accomplished what I wanted either. Would anyone have any ideas what I could do here? Thanks in advance.
