Converting JSON to CSV with Python

JSON formatted data is a common way of sending data over MQTT.

Storing the data directly as JSON formatted data is also common

However it is also very common to store this data as CSV or a least convert it to CSV for use in legacy systems.

Converting Simple JSON Data

This is actually quite straight forward provided the data is a single python dictionary object without any nesting.

This is easier to explain and see with example data . So if we take the data shown below:

{"ms":115318,"Urms":0,"Umin":0,"Umax":87,"count":mcount}

A CSV file consists of a header which contains the column names followed by rows of data.

The elements are normally separated by a comma

column1, column2,  column3,
row1      1,21,345
row2      2 ,23,347

What we need to do is to extract the column names which are essentially the keys in the JSON data and then extract the data which are the values.

We could do this manually using code like that below:

To extract the keys and create the header use:

columns =""
for key in data:
 print("key =",key)
 if columns =="":
  columns=key
 else:
  columns=columns+","+key
print(columns)

this gives us an array of keys as shown in the screen shot:

You can then extract the data elements by looping though them as shown below:

line_out=""
for key in data:
    #print("here ",data[key])
    if line_out =="":
        line_out=str(data[key])
    else:
        line_out=line_out+","+str(data[key])
print(line_out)

Now we see the data as shown in the screen shot:

csv-data

Now we just need to write it to a file so our psuedo code is

get header
write header
loop data
get data
write data
end loop

and example code is shown below:

filename="test.csv"
fo=open(filename, 'w')
for key in data:
    print("key =",key)
    if columns =="":
        columns=key
    else:
        columns=columns+","+key
print(columns)
fo.write(columns+"\n")
#start data loop here
line_out=""
for key in data:
    #print("here ",data[key])
    if line_out =="":
        line_out=str(data[key])
    else:
        line_out=line_out+","+str(data[key])
print(line_out)
fo.write(line_out+"\n")
#end data loop here
fo.close()

Converting Nested JSON

If the JSON data is more complex then the following code will work to flatten the JSON object:

def flatten_dict(d, parent_key='', sep='_'):
    items = []
    for k, v in d.items():
        new_key = parent_key + sep + k if parent_key else k
        if isinstance(v, dict):
            items.extend(flatten_dict(v, new_key, sep=sep).items())
        else:
            items.append((new_key, v))
    return dict(items)

When used with the following input data:

msg={"Time":"2024-02-15T19:33:08","Uptime":"0T00:05:20",\
"UptimeSec":320,"Heap":27,"SleepMode":"Dynamic","Sleep":50,\
"LoadAvg":19,"MqttCount":1,"POWER":"OFF","Wifi":{"AP":2,"SSId":"Akehurst3",\
"BSSId":"AC:84:C6:3F:31:76","Channel":1,"RSSI":38,"Signal":-81,\
"LinkCount":1,"Downtime":"0T00:00:03"}}

This is the result:

Time,Uptime,UptimeSec,Heap,SleepMode,Sleep,LoadAvg,MqttCount,POWER,Wifi_AP,Wifi_SSId,Wifi_BSSId,Wifi_Channel,Wifi_RSSI,Wifi_Signal,Wifi_LinkCount,Wifi_Downtime
2024-02-15T19:33:08,0T00:05:20,320,27,Dynamic,50,19,1,OFF,2,Akehurst3,AC:84:C6:3F:31:76,1,38,-81,1,0T00:00:03

Notice the nested wifi object.

A demo script is below:

download

Usage Examples

Both the MQTT logger and topic logger include this conversion code.

Related tutorials and resources:

Please rate? And use Comments to let me know more

Leave a Reply

Your email address will not be published. Required fields are marked *