portrait picture

TIMO ZIMMERMANN

balancing software engineering & infosec

Python – convert JSON to CSV

posted on Thursday 21st of May 2020 in ,

Assume you have an API which returns some data. You want to provide the data to someone who wants to load it in Excel and do some analysis. Yes, this actually still happens. Yes, this is fine, it works and gets people what they need. But writing a response transformer for your API to spit out a proper CSV is a bit tedious if your framework does not provide this functionality. Let us fix this in the most easy way which is also a total overkill considering the tool we will use.

Your API needs to return consistent data. Something like [{"x": "1"}, {"y": "2"}, 3, 4,] will not work. The good news is most (pseudo) REST like JSON APIs will work. First we dump the response data to a file.

Now to convert the response data to a CSV. We will use Pandas. I told you the solution will be a total overkill.

import pandas
dfm = pandas.read_json("path_to_json_file.json")
dfm.to_csv("win.csv")

That is it.

Just in case you want to add CSV output to your app you can also do this in memory.

import pandas
import requests

response = requests.get("https://foo.tld/api/…"))

csv = io.StringIO()

dfm = pandas.DataFrame.from_records(response.json())
dfm.to_csv(csv)

Using io.StringIO is neat if you do not want to write an actual file but send the CSV as API response or via email for example.