Converting a JSON list to CSV file in python

Question

I have a list of JSON that I print it like this:

for item in points:
    print(format(item))

The result looks like this:

{u'TEMP': 30, u'LIGHT': 315, u'HUMIDITY': 30.9, u'SOURCE': u'arduino_1', u'PLACE': u'kitchen', u'time': u'2016-12-31T11:18:38.822822913Z'}
{u'TEMP': 31, u'LIGHT': 325.5, u'HUMIDITY': 31.93, u'SOURCE': u'arduino_1', u'PLACE': u'garage', u'time': u'2016-12-31T11:18:39.919019993Z'}
{u'TEMP': 32, u'LIGHT': 336, u'HUMIDITY': 32.96, u'SOURCE': u'arduino_1', u'PLACE': u'living_room', u'time': u'2016-12-31T11:18:41.014792508Z'}
{u'TEMP': 33, u'LIGHT': 346.5, u'HUMIDITY': 33.99, u'SOURCE': u'arduino_1', u'PLACE': u'basement', u'time': u'2016-12-31T11:18:42.11100167Z'}

First of all, there is a problem with my data source that prints that 'u' characters before every item.

I want to write each line in a CSV file with a format like this ( the first line is the CSV header)

TIME,SOURCE,PLACE,TEMP,LIGHT,HUMIDITY
2016-12-31T11:18:38.822822913Z,arduino_1,kitchen,30,315,30.9

I am trying to do this using the csv package. But I am not sure how can I get the data of each row out of the items in the list and change the order of where they appear in the resulting CSV file:

with open('output.csv', 'wb') as csvfile:
    writer = csv.writer(csvfile, delimiter=',', quotechar='|', quoting=csv.QUOTE_MINIMAL)
    writer.writerow(points)

I would appreciate your help toward a Python noob.

Sable

"First of all, there is a problem with my data source that prints that 'u' characters before every item." Why do you think that's a problem? – Rozina

DictWriter is made for you, writing a list of dicts into a csv file. – Commencement

@Rozina hmmm what is the meaning of that u then? – Sable

@SaeidYazdani #2465459 – Rozina

Answer 1

csv.writer is made for lists of lists or lists of tuples. So using it for list of dicts triggers a "sequence expected" error. Instead, use csv.DictWriter as follows:

import csv

data=[{u'TEMP': 30, u'LIGHT': 315, u'HUMIDITY': 30.9, u'SOURCE': u'arduino_1', u'PLACE': u'kitchen', u'time': u'2016-12-31T11:18:38.822822913Z'},
{u'TEMP': 31, u'LIGHT': 325.5, u'HUMIDITY': 31.93, u'SOURCE': u'arduino_1', u'PLACE': u'garage', u'time': u'2016-12-31T11:18:39.919019993Z'},
{u'TEMP': 32, u'LIGHT': 336, u'HUMIDITY': 32.96, u'SOURCE': u'arduino_1', u'PLACE': u'living_room', u'time': u'2016-12-31T11:18:41.014792508Z'},
{u'TEMP': 33, u'LIGHT': 346.5, u'HUMIDITY': 33.99, u'SOURCE': u'arduino_1', u'PLACE': u'basement', u'time': u'2016-12-31T11:18:42.11100167Z'}]

with open("output.csv","w",newline="") as f:  # python 2: open("output.csv","wb")
    title = "time,SOURCE,PLACE,TEMP,LIGHT,HUMIDITY".split(",") # quick hack
    cw = csv.DictWriter(f,title,delimiter=',', quotechar='|', quoting=csv.QUOTE_MINIMAL)
    cw.writeheader()
    cw.writerows(data)

fixing title order is done by reusing the order you provided (else order is the dictionary order, not the one you want).

Write the header to get the title, then use writerows on the list of dictionaries to write the data.

Output:

time,SOURCE,PLACE,TEMP,LIGHT,HUMIDITY
2016-12-31T11:18:38.822822913Z,arduino_1,kitchen,30,315,30.9
2016-12-31T11:18:39.919019993Z,arduino_1,garage,31,325.5,31.93
2016-12-31T11:18:41.014792508Z,arduino_1,living_room,32,336,32.96
2016-12-31T11:18:42.11100167Z,arduino_1,basement,33,346.5,33.99

note that the u prefix that was worrying you doesn't appear in the result. It's just a representation character.

Duchamp

Answer 2

Pandas has a lot of I/O tools to read/write many files. I guess, you're trying to transform a JSON file to CSV.

So you can just do :

import pandas as pd
data = pd.read_json(path_to_input_file)
data.to_csv(path_to_csv_output_file)

Capacitate

This only works for a simple JSON file.This does not work for all cases, mainly when your JSON file has a nested structure with a List of JSON with different keys. It would be best if you put in your logic to get the correct value mapped to the correct title of your csv – Kirkman