I have dataset containing data for some vehicles. In this dataset, the last column corresponds to the routing information of each vehicle (the header is ‘route’).

The data in this column has a format as follows:
[{“longitude”: 101, “latitude”:3, “timestamp”:2, “accuracy”:0.5, “hdop”:1}, {“longitude”: 101, “latitude”:3, “timestamp”:2, “accuracy”:0.5 “hdop”:1}, {“longitude”: 101, “latitude”:3, “timestamp”:2, “accuracy”:0.5, “hdop”:1},{“longitude”: 101, “latitude”:3, “timestamp”:2, “accuracy”:0.5, “hdop”:1}…etc

I want to split the data in this last column based on the “longitude”, “latitude”, “timestamp”, “accuracy”, “hdop”. Each of these set of data (separated by {…}) represent a coordinate. Also note that not all coordinates have the “hdop” and “accuracy” keys.

I have tried the following commands.

    import csv
    import json
    import ast
    import pandas as pd

    # Read data into a Pandas data frame
    df = pd.read_csv('Trips Jan Week 1.csv')
    df['route'] = pd.Series(df['route'],dtype="string")

    # Extract the last column as a string
    routes = df.iloc[:, -1].astype(str)

    # Remove any leading or trailing characters before parsing the JSO
    routes2 = routes.apply(lambda x: x.strip('[]').strip())
    print(routes2)
    print(type(routes2))

The results are as follows:

    0       {"longitude":101.70393638087968,"latitude":3.1...
    1       {"longitude":101.5249183,"latitude":3.0761391,...
    2       {"longitude":101.70862944829587,"latitude":3.1...
    3       {"longitude":101.705162,"latitude":3.1590512,"...
    4       {"longitude":101.71380749913092,"latitude":3.1...
                              ...                        
    7928    {"longitude":101.7115741,"latitude":3.1961516,...
    7929    {"longitude":101.71194960096184,"latitude":3.1... 
    7930    {"longitude":101.71191491852223,"latitude":3.1...
    7931    {"longitude":101.6748983,"latitude":3.1257983,...
    7932    {"longitude":101.69488815920366,"latitude":3.0...
    Name: route, Length: 7933, dtype: object
    <class 'pandas.core.series.Series'>

The results I would like to get for each of these would therefore be as follows:
#Latitude# #Longitude# #Timestamp# #Accuracy# #Hdop#