Build dictionary from JSON and export it to Pandas-CodePudding

I am trying to build a script that pulls offline endpoints from the dictionary below:

[
    {
        "name": "My AP",
        "serial": "Q234-ABCD-5678",
        "mac": "00:11:22:33:44:55",
        "status": "online",
        "lanIp": "1.2.3.4",
        "publicIp": "123.123.123.1",
        "networkId": "N_24329156"
    }
]

and then populate a dictionary and export output to xlsx with pandas

# Build dictionary to organize endpoints
endpoint = {'name' : [], 'serial' : [], 'mac' : [], 'publicIp' : [], 'networkId' : [], 'status' : [],'lastReportedAt' : [], 'usingCellularFailover' : [], 'wan1Ip' : [], 'wan2Ip' : [], 'lanIp' : []}                                   
                                        


# Iterate over the endpoints to fill dictionary
for i in range(len(response_data)):
    if response_data[i]['status'] == 'offline':
        endpoint['Name'].append(['name'])
        endpoint['Serial'].append(['serial'])
        endpoint['MAC'].append(['mac'])
        endpoint['Public IP'].append(['publicIp'])
        endpoint['Network ID'].append(['networkId'])
        endpoint['Status'].append(['status'])
        endpoint['Last Reied'].append(['lastReiedAt'])
        endpoint['Cellular'].append(['usingCellularFailover'])
        endpoint['WAN 1'].append(['wan1Ip'])
        endpoint['WAN 2'].append(['wan2Ip'])
        endpoint['LAN'].append(['lanIp'])
        df = pd.DataFrame.from_dict(endpoint)
        df.to_excel("output.xlsx", index=False)

I am pretty sure there's a more efficient way to fulfill the task like may be importing the output to pandas and sorting the data but I am still a noob

CodePudding user response：

You could convert a list of dictionaries into a Pandas dataframe directly.

CodePudding user response：

If your list of dictionaries is called "response_data" then you can convert that list to a DataFrame directly like so:

df = pd.DataFrame(response_data, index=range(len(response_data)))
df.to_excel("output.xlsx", index=False)