Build dictionary from JSON and export it to Pandas

Question

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

you should create `dataframe` after `for`-loop - this way you would do it only once, and you would write file only once. — furas, Nov 25 '21 at 23:36
you could learn to use `for item in response_data:` and later use `item['status']`, `item['name']`, etc. — furas, Nov 25 '21 at 23:41

score 0 · Answer 1 · answered Nov 25 '21 at 23:30

0

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

answered Nov 25 '21 at 23:30

Franco Piccolo

6,845
8
34
52

score 0 · Answer 2 · answered Nov 25 '21 at 23:36

0

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)

answered Nov 25 '21 at 23:36

inteoryx

785
2
9

furas · Answer 3 · 2021-11-25T23:50:36.937

You can use directly DataFrame and later rename columns and filter data.

response_data = [
    {
        "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"
    },

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

import pandas as pd

df = pd.DataFrame(response_data)

df = df.rename(columns={
    'name': 'Name',
    'serial': 'Serial',
    'mac': 'MAC',
    'status': 'Status',
    'publicIp': 'Public IP',
    'networkId': 'Network ID',
    'lastReiedAt': 'Last Reied',
    'usingCellularFailover': 'Cellular',
    'wan1Ip': 'WAN 1',
    'wan2Ip': 'WAN 2',
    'lanIp': 'LAN',
})

df = df[ df['Status'] != 'offline' ]

print(df)

df.to_excel("output.xlsx", index=False)

Build dictionary from JSON and export it to Pandas

3 Answers3