Is there an easy way to convert API data with python commands and output the results in CSV format?

Here's what I'm doing now:

import requests

params = {
  "api_key": "fec742c1-c846-4343-a9f1-91c729acd097",
  "format": "jsonp"
r = requests.get('https://data.sa.gov.au/data/api/3/action/datastore_search')

# This bit of code will write the result of the query to output.csv

with open('output.csv', 'w+') as f:

This code doesn't work. The API data is available here

  • $\begingroup$ Convert the JSON response to CSV directly using pandas? $\endgroup$
    – Aditya
    Commented Oct 29, 2019 at 12:33

2 Answers 2


On the page you linked there is actually a Python example on how to get the data. It is in Python 2, but I will show you how to make it work in Python 3.

import urllib
import json                 # Used to load data into JSON format
from pprint import pprint   # pretty-print

url = "https://data.sa.gov.au/data/api/3/action/datastore_search?resource_id=fec742c1-c846-4343-a9f1-91c729acd097&limit=5&q=title:jones"
response = urllib.request.urlopen(url)
# Just an object:   <http.client.HTTPResponse at 0x7f2618123e10>

We get the text data out by using the read() method:

text = response.read()

The response in this case is a raw string. We can use the json module's function loads to load a string):

json_data = json.loads(text)


returns the following JSON data:

{'help': 'https://data.sa.gov.au/data/api/3/action/help_show?name=datastore_search',
 'result': {'_links': {'next': '/api/3/action/datastore_search?q=title%3Ajones&offset=5&limit=5&resource_id=fec742c1-c846-4343-a9f1-91c729acd097',
                       'start': '/api/3/action/datastore_search?q=title%3Ajones&limit=5&resource_id=fec742c1-c846-4343-a9f1-91c729acd097'},
            'fields': [{'id': '_id', 'type': 'int4'},
                       {'id': 'LGA Name', 'type': 'text'},
                       {'id': 'Tenure type', 'type': 'text'},
                       {'id': 'Very low income <$603 per wk', 'type': 'numeric'},
                       {'id': 'Low income $603-$964 per wk', 'type': 'numeric'},
                       {'id': 'Moderate income $965-$1446 per wk',                            'type': 'numeric'},
                       {'id': 'Total', 'type': 'numeric'},
                       {'id': '_full_count', 'type': 'int8'},
                       {'id': 'rank', 'type': 'float4'}],
            'limit': 5,
            'q': 'title:jones',
            'records': [],
            'resource_id': 'fec742c1-c846-4343-a9f1-91c729acd097'},
 'success': True}

I would suggest using Pandas, which can do a lot of the tedious work for you very easily. It can read straight from a JSON string (our text above). The issue is that is will parse it a little strangely.

There is no simple way to write this directly to a CSV file, because there are nested structures: e.g. under "result" there "fields" and then more values, and CSV files can't display that directly. You need to essentially flatten the structure yourself, perhaps decide what is important or what you want to can leave out.

You could take the JSON formatted json_data above and unpack it manually, removing nested parts, which means looking through the response and making your own Python dictionary with only single level i.e. no nesting. Let's say you do that and have a new flattened dictionary named r. Once you have done that, you can do the following using Pandas to write the CSV file:

import pandas as pd

df = pd.read_json(r)
  • $\begingroup$ well that is perfect answer. I tried the code but it gives this error " NameError: name 'r' is not defined" $\endgroup$ Commented Oct 29, 2019 at 23:29
  • $\begingroup$ @CaseMsee - r would be the final dictionary that you need to create yourself. I have updated the final paragraph. $\endgroup$
    – n1k31t4
    Commented Oct 30, 2019 at 0:06
  • $\begingroup$ @ n1k31t4. Many thanks for this. I saved the CSV successfully but it gives me different results as i have attached the snapshot here.ibb.co/vBCdw7J. Both CSV data and API data is available here data.gov.au/dataset/ds-sa-fe77c7df-52f9-415e-b0be-df0f8813c924/… for comparison. $\endgroup$ Commented Oct 30, 2019 at 0:18

You can also do this in bash.

For example, if your CSV file is a list of queries for the API


while IFS='' read -r line || [[ -n "$line" ]]; do
    echo $line, `curl "$line" \
         -H 'Authorization: bearer xxx'`;
done < "processed.csv"

The output would be


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.