Python and API Output to CSV

Question

I'm trying to get the api result from the URL and then store it into a CSV file. I'm not very familiar with API and storing data, since I'm practicing.

I have the URL and the response to get the data from.

url = "..." 
response = requests...
print(response.text)

The results of this is

[
{
    "name": "Josh",
    "city": "New York",
    "Height": "5'9"
},
{
    "name": "Katty",
    "city": "Los Angeles",
    "Height": "5'6"
}
]

this is how I'm storing this data into the CSV file

with open('data.csv', 'w') as f:
f.write(response.text)

The problem with this is that when I open the CSV file the data is store in the same row. Basically like this

{"name":"Josh","city":"New York","Height":"5,9"}{"name":"Katty","city":"Los Angeles","Height":"5,6"}

I want it to be store the same way as the response from the API. The bracket in one line, then the name, then the city, then the height, then finish with the last bracket. Then continue with the next list.

Please provide the expected output, your description is insufficient. — Michael Ruth
– Michael Ruth, Commented Jun 11, 2021 at 21:31
You’re writing a JSON string to a file and hoping/imagining this turns it into “CSV”? It’s JSON. It’s still JSON when you write it to a file even if that has the extension “.csv”. CSV files don’t have one line a [ then the next line something beginning { (at least not if they’re actually CSV format). The C in CSV stands for Comma — DisappointedByUnaccountableMod
– DisappointedByUnaccountableMod, Commented Jun 11, 2021 at 21:56
That would be a very weird csv file. I don’t think you really really want to do that - you should be making the header row e.g. name,city,height and each line after that the values for those fields, in that order, separated by, err, comma, and with no [ ] { } anywhere. Why don’t you try manually creating a file in the format you think you want and giving the file a .csv extension and then try opening that file in a spreadsheet program like excel? — DisappointedByUnaccountableMod
– DisappointedByUnaccountableMod, Commented Jun 11, 2021 at 22:03

DisappointedByUnaccountableMod · Accepted Answer · 2021-06-11 22:47:18Z

Your response.text is a JSON string, and writing it literally to a “csv” file won’t turn it into CSV format.

CSV is a column/row-oriented format, so it's completely different from JSON - a CSV often (but optionally) starts with a line called the header row which lists the name for each column separated by comma, then subsequent lines one per row with the value for each column separated by comma - there are as many columns in each line/row as specified in the header. It's possible to use a different separator than comma, such as tab, or semi-colon, but the principle is the same - when importing you need to specify the separator if it's not comma. The CSV format allows values to contain the separator or a newline by quoting them usually using " and also to contain " by double-quoting it. CSV is an ad-hoc "standard" which works pretty well. In Python the library includes a module called csv which understands the complexities and does the detailed work for you.

So you should use the csv module because it handles the details.

You need code something like this, although you will use response.json which for your response is a Python list of dictionaries, i.e. it's been converted from JSON string into Python things. The code below hardcodes the response string and turns this into the equivalent of response.json using json.loads into variable responsejson. The header row is also hardcoded, but you could work this out dynamically by scanning all the rows and working out the header from thje dictionary keys.

import json
import csv

# this is a hardcoding of your response.text
responsetext = """[
{
    "name": "Josh",
    "city": "New York",
    "Height": "5'9"
},
{
    "name": "Katty",
    "city": "Los Angeles",
    "Height": "5'6"
}
]
"""
# this converts that text into a Python list - you don't need to do this, you should use response.json
responsejson = json.loads(responsetext)

with open('response.csv', 'w', newline='') as csvfile:
    fieldnames = ['name', 'city', 'Height']
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)

    writer.writeheader()
    for row in responsejson:
        writer.writerow(row)

Result:

name,city,Height
Josh,New York,5'9
Katty,Los Angeles,5'6

This csv file will open in a spreadsheet program the way you might expect:

Documentation for csv is here https://docs.python.org/3/library/csv.html

You are right about the CSV file. I am now realizing that I do need the JSON in order to do what I want for my next step. I managed to save the data into JSON. I thought that by saving it into a row/column CSV file it would be easier to compare two different files to see if any of them was missing from one another.

Collectives™ on Stack Overflow

Python and API Output to CSV

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related