Nested Data inside output needing to be expanded

Question

I have the following code

import requests
import json
import pandas as pd
import csv
import numpy
from pandas.io.json import json_normalize

url = 'http://URL/api_jsonrpc.php' 

payload = '{"jsonrpc": "2.0", "method": "event.get", "params": {"output": "extend", "selectAcknowledges": "extend", "selectTags": "extend", "selectSuppressionData": "extend", "selectHosts": ["hostid", "host", "name"], "recent": "true", "sortorder": "DESC"}, "auth": "XXX", "id": 1 }'
headers = {'content-type': 'application/json-rpc'}
r = requests.post(url, data=payload, headers=headers, )
geteventlist = r.json()['result']

file = open('event.csv', 'w+', newline='', encoding="utf_8")

with file:

    header = ['hosts', 'host', 'hostid', 'location', 'eventid', 'userid', 'acknowledged', 'opdata', 'object', 'name', 'suppressed', 'c_eventid', 'clock', 'source', 'objectid', 'severity', 'urls', 'r_eventid', 'value', 'ns', 'suppression_data', 'correlationid', 'tags']
    writer = csv.DictWriter(file, fieldnames = header)

    writer.writeheader()
    writer.writerows(geteventlist)

This outputs the following type of data into a csv file. But I currently am running into an issue where the data outputs correctly bar one column where it has a nested java list which I cant seem to separate. Please see below the output in an excel format

Basically looking for a way to expand this so I get 3 new columns of 'host' 'hostid' and 'name'.

I have tried the following solutions all with separate errors:

for hosts in geteventlist:
    hosts['host'] = hosts['hosts']['host']
    hosts['hostid'] = hosts['hosts']['hostid']
    hosts['name'] = hosts['hosts']['name']
    del hosts['hosts']

This outputs the following error: TypeError: list indices must be integers or slices, not str

I have also tried using the json_normalize function and that outputs the same error when confronted with 'hosts'

I have also tried to add a new dictionary by specifying the new dictionary with mydict = {}

But then this only captures the rows and not the data I need unless I am doing it incorrectly.

Does anyone have any information that would be beneficial for this?

Also to add the full output of the above code is the following

[{'eventid': '49', 'source': '0', 'object': '0', 'objectid': '22608', 'clock': '1648445474', 'value': '1', 'acknowledged': '0', 'ns': '948833226', 'name': 'Unavailable by ICMP ping', 'severity': '4', 'r_eventid': '89', 'c_eventid': '0', 'correlationid': '0', 'userid': '0', 'opdata': '', 'hosts': [{'hostid': '10519', 'host': 'Zabbix', 'name': 'Zabbix'}], 'suppression_data': [], 'suppressed': '0', 'urls': [], 'tags': [{'tag': 'scope', 'value': 'availability'}, {'tag': 'component', 'value': 'health'}, {'tag': 'component', 'value': 'network'}]}

What needs to be expanded is the nested list inside this which can be found after host:

'hosts': [{'hostid': '10519', 'host': 'Zabbix', 'name': 'Zabbix'}]

Answer 1

Building up on my comment : geteventlist looks like a list of list, with each nested list containing 1 dict.

In order to build a correct list :

csvList = []
for hosts in geteventlist:
    try:
        csvList.append({
            'host': hosts['hosts'][0]['host'],
            'hostid': hosts['hosts'][0]['hostid'],
            'name': hosts['hosts'][0]['name']
            }
        )
    except IndexError:
        print(f"List hosts at index {geteventlist.index(hosts)} is empty")
        continue

# Then writing the csv
with file:

    header = ['host', 'hostid', 'name']
    writer = csv.DictWriter(file, fieldnames = header)

    writer.writeheader()
    writer.writerows(csvList)

Output in the csv file with a list of 4 events :

host,hostid,name
Zabbix,10519,Zabbix
Zabbix,10519,Zabbix
Zabbix,10519,Zabbix
Zabbix,10519,Zabbix