separating a CSV with a value with multiple values inside

Question

Currently trying to write a script that pulls data from a ZabbixAPI key. I have the script working with data pulling out then being converted to a CSV file.

The issue I am facing is that after it is being converted one of the headers I have defined has multiple values inside which I need as single headers with the value attached as its own column.

e.g.

{'hostid': '10084', 'host': 'Zabbix server', 'name': 'Zabbix server', 'status': '0', 'inventory': {'os_full': '', 'tag': '', 'location': '', 'location_lat': '', 'location_lon': ''}}

Is the code, however as the issue is saying, the value of 'Inventory' is encompassing all the data I need to be in its own columns.

How can I separate the value to properly show this?

This is the full script

import requests
import json
import pandas as pd
import csv


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

payload = '{"jsonrpc": "2.0", "method": "host.get", "params": {"output": ["hostid","host","name","status","location","location_lat","location_lon"],"selectInventory": ["os_full","tag","location","location_lat","location_lon"]}, "auth": "XXX", "id": 1 }'
headers = {'content-type': 'application/json-rpc'}
r = requests.post(url, data=payload, headers=headers, )
hostslist = r.json()['result']

print(type(hostslist))
print(hostslist)



file = open('hostInventory.csv', 'w+', newline='')

with file:

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

    writer.writeheader()
    writer.writerows(hostslist)

Answer 1

Modify the dicts first, then write as before. Example:

for host in hostlist:
    host['os_full'] = host['inventory']['os_full']
    del host['inventory']

with open('hostInventory.csv', 'w+', newline=''):

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

    writer.writeheader()
    writer.writerows(hostslist)