[Python] How to synchronize or merge dataframe with JSON data based on timestamp

There are many examples out there on how to merge two pandas dataframe but my problem is that I have two kind of data. data1 is a csv data that I read it with pandas and turn it to dataframe and the other data2 is in JSON format.

Here is an example of the json data:

[{'timestamp': 1572430625231, 'url': 'brakePressure', 'value': 10},
{'timestamp': 1572430625275, 'url': 'lateralAcceleration', 'value': 120},
{'timestamp': 1572430625290, 'url': 'longitudinalAcceleration', 'value': 110},
{'timestamp': 1572430625299, 'url': 'acceleratorPosition', 'value': 1230},
{'timestamp': 1572430625310, 'url': 'currentTorque', 'value': 10}]


As you can see every feature value is inside a dictionary with a timestamp. the problem is if I convert this to a dataframe the rows would be a timestamp, url and value but I don't want that, I want that my columns (features) would be brakePressure, lateralAcceleration etc.. and in every column there are all values that correspond to that feature and that are inside this JSON.

My goal is to merge the two datasets based on timestamp. this is so hard because here in the JSON I have a timestamp associated with every single feature value, on the contrary in the CSV data I have a timestamp that correspond to a row (that mean n feature value and not a single one). I tried so hard to do this but no chance, so I thought maybe I can search which is the closest timestamp and then replace a single value at a time, here is my try:

def sync_vehicle_gps_data(dataset=vehicle_data, gps_data=gps_data):
vehicle = dataset.copy()
gps = gps_data.copy()
d = {}

for json in vehicle:

timestamp, feature, val = json.values()
index = abs(gps['timestamp'] - timestamp).idxmin()
print("closest value index = ", index)
gps.at[index, feature] = val

return gps


The vehicle_data are the JSON data and the gps_data is a pandas DataFrame, as you can see I search through the whole dataset which timestamp is the closest to the single feature timestamp and then I update that specific value but this didn't work well for me. I ended up having messed up data. Is there any way to do this in Python? I can also use any other libraries if there is one so I'm not restricted to pandas.

The expected output is that I can append those values in the json above to the existing dataframe so that means in this example that a new columns 'brakePressure', 'lateralAcceleration' etc.. would be added and the value of each feature (as it is in the JSON above, yes they are zeros but it's only an example) would be put in the row where the timestamp of that row is the nearest to the timestamp of the timestamp key of each feature in the JSON above.

Here is an example: let's say this is the gps data

timestamp X Y Z
1572430510880 595.00 179.00 -60.00
1572430510890 -0.23 -0.09 0.01
1572430510900 -0.11 -0.02 0.04
1572430510910 -1.96 -5.19 -6.10


I want this output (I'll show this only for one feature):

timestamp X Y Z brakePressure
1572430510880 595.00 179.00 -60.00 10
1572430510890 -0.23 -0.09 0.01 nan
1572430510900 -0.11 -0.02 0.04 nan
1572430510910 -1.96 -5.19 -6.10 nan


The value of the brakePressure feature in the dictionary was put in the first row because the closest gps timestamp to the timestamp of the brakePressure in the dictionary above is also in the first row. Basically I want to do that same thing for all the features in the JSON, I want to synchronize all those feature values with the gps data.

Continue reading...