Convert JSON to Table with Pandas

Question

I started to study python and I have one problem when the result is a JSON format.

My JSON is this:

[{'eventid': '30', 'clock': '1602601344', 'name': 'Zabbix server has been restarted (uptime < 10m)', 'severity': '2', 'hosts': [{'hostid': '10084', 'name': 'Zabbix server'}], 'acknowledges': []}, {'eventid': '42', 'clock': '1603128324', 'name': 'Zabbix server has been restarted (uptime < 10m)', 'severity': '2', 'hosts': [{'hostid': '10084', 'name': 'Zabbix server'}], 'acknowledges': []}, {'eventid': '44', 'clock': '1603138524', 'name': 'Zabbix server has been restarted (uptime < 10m)', 'severity': '2', 'hosts': [{'hostid': '10084', 'name': 'Zabbix server'}], 'acknowledges': []}]

I try to convert the result to a table with json_normalize but my result is: Table after json_normalize

I see json_normalize documentation with an example, but I don't understand how to put "name" on column "hosts".

My function is:

def get_event_get(zbx_srv, token):
    try: 
        json_event_get = {
            "jsonrpc": "2.0",
            "method": "event.get",
            "params": {
                "selectHosts": ["name"],
                "output": ["eventid", "clock", "name", "severity"],
                "select_acknowledges": "extend",
                "sortfield": ["clock"],
                "filter": {
                    "value": ["1"],
                    "object": ["0"]
                },
            },
            "auth": token,
            "id": 1
        }
        event_get_request = requests.post(zbx_srv, json=json_event_get)
        event_get = event_get_request.json()['result']
        print(event_get)
        table_event = json_normalize(event_get)
        print(table_event)
        return event_get
    except Exception as e:
        print("Erro: %s" %(e))

Could someone help me understand?

Answer 1

Try this, data is your json dict that you posted above.

df = pd.json_normalize(data,
                       record_path=['hosts'],
                       meta=['eventid', 'clock', 'name', 'severity'],
                       meta_prefix='_')
print(df)



hostid           name _eventid      _clock                                            _name _severity
0  10084  Zabbix server       30  1602601344  Zabbix server has been restarted (uptime < 10m)         2
1  10084  Zabbix server       42  1603128324  Zabbix server has been restarted (uptime < 10m)         2
2  10084  Zabbix server       44  1603138524  Zabbix server has been restarted (uptime < 10m)         2

Or you can use record_prefix instead:

df = pd.json_normalize(data,
                       record_path=['hosts'],
                       meta=['eventid', 'clock', 'name', 'severity'],
                       record_prefix='hosts_')
print(df)




hosts_hostid     hosts_name eventid       clock                                             name severity
0        10084  Zabbix server      30  1602601344  Zabbix server has been restarted (uptime < 10m)        2
1        10084  Zabbix server      42  1603128324  Zabbix server has been restarted (uptime < 10m)        2
2        10084  Zabbix server      44  1603138524  Zabbix server has been restarted (uptime < 10m)        2