how do you extract some data from json file using python

I have this json file:

print(data)

{'entityId': 'clusterId123', 
'displayName': 'dev_cluster', 
'firstSeenTms': 1584113406351, 
'lastSeenTms': 1627524312116,  
'properties': {'detectedName': 'dev_cluster'}, 
'tags': [], 
'icon': {'primaryIconType': 'hypervisor'}, 
'toRelationships': {
    'isMemberOf': [
        {'id': 'HYPERVISOR_123', 'type': 'HYPERVISOR'}, 
        {'id': 'HYPERVISOR_234', 'type': 'HYPERVISOR'}, 
        {'id': 'HYPERVISOR_345', 'type': 'HYPERVISOR'}
        ]
    }
}

I need to create a data frame that looks like this:

clusterId,  clusterName, hypervisorId
clusterId123 dev_cluster HYPERVISOR_123
clusterId123 dev_cluster HYPERVISOR_234
clusterId123 dev_cluster HYPERVISOR_345

as you can see clusterId and clusterName repeats but the hypervisorId changes just like in the data file.

I am doing this:

#create an empty list
 `cluList=[]`
#apend elements to the list
`cluList.append([data['entityId'], data['displayName']])`

I dont know how to pull the HYPERVISOR_123, HYPERVISOR_234, HYPERVISOR_345 from this data sets. Any guidance appreciated.

Solution 1:

Use dict comprehesion:

import pandas as pd

data = {'entityId': 'clusterId123', 
'displayName': 'dev_cluster', 
'firstSeenTms': 1584113406351, 
'lastSeenTms': 1627524312116,  
'properties': {'detectedName': 'dev_cluster'}, 
'tags': [], 
'icon': {'primaryIconType': 'hypervisor'}, 
'toRelationships': {
    'isMemberOf': [
        {'id': 'HYPERVISOR_123', 'type': 'HYPERVISOR'}, 
        {'id': 'HYPERVISOR_234', 'type': 'HYPERVISOR'}, 
        {'id': 'HYPERVISOR_345', 'type': 'HYPERVISOR'}
        ]
    }
}

df = pd.DataFrame({
    "clusterId": data["entityId"],
    "clusterName": data["displayName"],
    "hypervisorId": _id["id"]
} for _id in data["toRelationships"]["isMemberOf"])

df

And result:

    clusterId       clusterName   hypervisorId
0   clusterId123    dev_cluster   HYPERVISOR_123
1   clusterId123    dev_cluster   HYPERVISOR_234
2   clusterId123    dev_cluster   HYPERVISOR_345

Solution 2:

You can use a list comprehension that loops over the isMemberOf list.

clulist = [(data['entityId'], data['displayName'], hypid) 
            for hypid in data['toRelationships']['isMemberOf']
        ]