Anila A Anila A - 7 months ago 17
Python Question

Copy a json nested dictionary to csv file

I need to copy the following nested dictionary in json from an api to a csv file

{
"result": [{
"short_description": "I am getting bluescreen error",
"sys_id": "39b5f8c2376ede007520021a54990e5c",
"opened_at": "2016-04-04 05:19:53",
"number":"INC0258523"
},
{
"short_description": "laptop crashed with a blue screen",
"sys_id": "da0095380f43d200a4f941fce1050eeb",
"opened_at":"2016-04-25 06:33:52",
"number":"INC0259067"
},
{
"short_description": "Laptop not booting",
"sys_id": "ecf9c9b00f43d200a4f941fce1050e17",
"opened_at": "2016-04-25 06:07:16",
"number": "INC0259061"
}]
}


This is what I have tried so far

with open('D:/file.csv', 'wb') as f:
w = csv.DictWriter(f, data.keys())
w.writerow(data)


I get the following result in single cell of the csv file

[{"short_description":"I am getting bluescreen error","sys_id":"39b5f8c2376ede007520021a54990e5c","opened_at":"2016-04-04 05:19:53","number":"INC0258523"},{"short_description":"laptop crashed with a blue screen","sys_id":"da0095380f43d200a4f941fce1050eeb","opened_at":"2016-04-25 06:33:52","number":"INC0259067"},{"short_description":"Laptop not booting","sys_id":"ecf9c9b00f43d200a4f941fce1050e17","opened_at":"2016-04-25 06:07:16","number":"INC0259061"}]}


Whereas I need it to be in the format where keys short_description, sys_id, opened_at, number are column names and their values as corresponding column data.

Answer

You need to extract the row dictionaries. Right now you are using the top level 'result' dict to feed the csv writer.

  with open('D:/file.csv', 'wb') as f:  
        w = csv.DictWriter(f, data['result'][0].keys())   
        for row in data['result']:
            w.writerow(row)