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.

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.

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)