JSON with locations to a dataframe

Issue

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

Solution

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

Answered By – Tranbi

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published