JSON with locations to a dataframe-CodePudding

I have this json file that contains the distance and the travel duration between one point to another one. For instance, below I have the distances from the depot to the depot, and then the depot to A. And again, from A to the depot, and then from A to A.

json_data = '''
{
    "depot": [
                        {
                            "destinationIndex": 0,
                            "originIndex": 0,
                            "totalWalkDuration": 0,
                            "travelDistance": 0,
                            "travelDuration": 0
                        },
                        {
                            "destinationIndex": 0,
                            "originIndex": 0,
                            "totalWalkDuration": 0,
                            "travelDistance": 39.025,
                            "travelDuration": 30.4167
                        }
                    ],
    "A": [
                        {
                            "destinationIndex": 0,
                            "originIndex": 0,
                            "totalWalkDuration": 0,
                            "travelDistance": 39.128,
                            "travelDuration": 31.9
                        },
                        {
                            "destinationIndex": 1,
                            "originIndex": 0,
                            "totalWalkDuration": 0,
                            "travelDistance": 0,
                            "travelDuration": 0
                        }
                    ]
}
'''

What I would like to have is a distance matrix. As I have more than 350 locations, I want to loop over the file to create a distance matrix like in this example:


0	39.025
39.128	0

If any one can help or give some advice on working with this data that would be great! If you can't tell I haven't worked much with json data before...

Thanks

CodePudding user response：

IIUC your distance matrix should be symetric. You can use json_normalize on your dictionary values:

import pandas as pd
import json

data = json.loads(json_data)
locs = data.keys()
df = pd.concat([pd.json_normalize(val)['travelDistance'] for val in data.values()], axis=1)
df.index = locs
df.columns = locs
print(df)

Output:

        depot       A
depot   0.000  39.128
A      39.025   0.000