The first time I came across JSON, I was really happy. It is a very light and fluffy object representation in plain text. The beauty was that there were no new or extra specs; existing concepts of lists, objects, strings, numbers etc. were taken and put together in it's own clean way. I use JSON widely when I am working with APIs. In my playbook it is second only to single word/number replies.
Apart from becoming a standard for data interchange between clients and server calls, JSON has also become popular for intermediate representation and storage of data. This is pretty evident when working with reporting tools and frameworks. Why I say intermediate is because, the origin might be some XML or data from some DB and the actual consumption format might be some chart, graph or CSV data.
JSON allows expression of hierarchical, well formed and structured data by nesting objects and arrays within one another up to multiple nesting levels. Often, this sort of representation is not very easy or almost impossible to express in row/cols based data structures like CSV. However, when you talk about reporting and making dashboards it is almost always required to convert such structured data to more flatter key/value data for interpretation and exporting. This interpretation allows data analysts to import the data into tools like excel and work with it.
Here I am going to discuss about converting multiple nested JSON which might or might not contain similar elements to CSV for usage with tools like excel or open office calc. The script is written in Python2.7.
Let's take a valid multi-level JSON and start off...
{
"fruit":[
{
"name":"Apple",
"binomial name":"Malus domestica",
"major_producers":[
"China",
"United States",
"Turkey"
],
"nutrition":{
"carbohydrates":"13.81g",
"fat":"0.17g",
"protein":"0.26g"
}
},
{
"name":"Orange",
"binomial name":"Citrus x sinensis",
"major_producers":[
"Brazil",
"United States",
"India"
],
"nutrition":{
"carbohydrates":"11.75g",
"fat":"0.12g",
"protein":"0.94g"
}
},
{
"name":"Mango",
"binomial name":"Mangifera indica",
"major_producers":[
“India",
"China",
"Thailand"
],
"nutrition":{
"carbohydrates":"15g",
"fat":"0.38g",
"protein":"0.82g"
}
}
]
}
This is a valid JSON of 3 Fruit objects. Each fruit object has multiple valid data structures nested within.
Each object has to be parsed individually and reduced to a more flatter structure before putting it back together and writing it as a CSV. This is also essential for determining the headers. A few key things for doing this are...
Deducing key/value pairs for each object
I've visualized this structure as a tree and then the key and value is decided as follows...
Value: This is the leaf node.
Key: This is the concatenation of all parent node names with an '_' as separator. In case of Arrays, the immediate parent of the value is the index.
Taking the Apple object as example the tree can be visualized as...
Therefore, the key/value pairs become...
Fruit_Name -> Apple
Fruit_Major Producers_0 -> China
and so on. The same thing is done for all Fruit objects. Here the array indices are ignored. Otherwise it would be Fruit_0_Name -> Apple as against Fruit_Name -> Apple
After this exercise we will be left with a much flatter set of the required key/value pairs derived from the original JSON object. Same thing has to be done for all the objects in the original data to obtain reduced data.
Determining Headers
The headers are decided by adding all keys of all objects together and removing duplicates among them. This will ensure that all the keys across objects are covered. Further, headers are sorted alphabetically so that all the similar keys stay next to each other making it easier to read the CSV file. However, this also means that the original ordering of the JSON is not maintained. This is a current limitation, I am working on this and will update when I fix this.
Some key/value pair(s) existing in one object may not be present in another object. In this situation, the value will be empty wherever the key does not exist.
I am also working on writing multi-row, pretty headers without the '_'. If done, this can make reading and using this CSV even more easier. This will also enable usage of Merge and Center functionality of excel and allow for easier filter/formula application.
Writing the CSV
Finally, after reducing all original objects to flat objects as discussed above, the headers are written followed by every object as a row. the csv.DictWriter is used to write the CSV which will ensure that the rows are written as per the header and in the same order.
All code is available at Github. I am yet to setup an online demo where you can upload a JSON file and get a CSV for download. Will update once this is live.
Please drop me a mail if you have any suggestions or come across any bugs in the code.
This post was first published on my blog
Top comments (9)
You can fully automate the JSON to CSV conversion process with Flexter our free JSON converter. Flexter is an ETL tool for JSON and XML. It automates the conversion of JSON to a database, text, or Hadoop. We have written up a blog post (including a video) that shows how easy it is to automatically convert your JSON files to CSV sonra.io/2018/03/16/converting-fhi.... No manual coding needed. In this blog post we convert FHIR JSON, which is based on an industry data standard in healtcare.
When you convert your JSON files it also provides a diagram of the target model and the data lineage. It can handle JSON of any complexity.
Try for yourself: jsonconverter.sonra.io/
Great article, Vinay... I like the way you laid this all out. Reminds me of a script I wrote last year. Your explanation is much better than mine, admittedly haha.
Have any of you guys (or gals) on here heard of 4DIQ's Flow?
The framework has this whole new concept of 'generic data' where the whole idea of conversion is approached totally differently. Seems to be the quickest / most interesting way to convert files that I've come across as of recent.
Recommend checking it out! Below is a link to the blog post I found about about it from. It has to do with JSON to CSV, as well, but the more I play with the program the more it seems conversions are just a small piece of what it does.
Check it: flow-analytics.com/blog/how-to-nor...
Hi Vinay,
Can i get the code for
{
MANGO:{ key1 : fruit
key2 : yellow
key3 : Juice
key4 : Summer
}
KIWI :{
key1 : fruit
key2 : Green
key3 : [Juice, eat]
key4 : Summer
}
}
I like the script! Built a similar tool wrapped around a nice web interface, you can find it here: json to csv.
The idea to sort the headers is great. I'll put it as an option in my JSON to CSV Converter
can you please email me back @ nvnsngh1008@hotmail.com. I am having some trouble converting my json file to csv
or are you available at whatsapp so that i can contact you
Nice article I use these tools to do the same.
jsonformatter.org
codebeautify.org/json-to-csv
Hi, another online tool to do this: extendsclass.com/json-to-csv.html
Some comments may only be visible to logged-in visitors. Sign in to view all comments.