How to import data from nested JSON file to CSV?

Question

I have a nested json file and I want to write some of its contents into a csv file using Python. I have managed to select the data I want, but I don't understand how to ask Python to write data from all the indexes. In other words, I can only write data from one index at a time, I don't understand how to select all indexes. I tried with a slice but it gives me TypeError (list indices must be integers or slices, etc.). Could you help me? Thanks!

This is the code so far (it works, but it just writes 1 line and I have 87):

import json
import csv


json_file = open("unique_json.json", "r", encoding="utf-8")
csv_file = open("csv_format2.csv", "w", encoding="utf-8")

data = json.load(json_file)
write = csv.writer(csv_file)


contents = [[data[0]["node"]["display_url"]],
            [data[0]["node"]["edge_liked_by"]["count"]],
            [data[0]["node"]["edge_media_to_comment"]["count"]],
            [data[0]["node"]["taken_at_timestamp"]]]

headers = ["url", "likes", "comments", "timestamp"]

write.writerow(headers)

for i in contents:
    for j in i:
        print(j, end=",", file=csv_file)

json_file.close()
csv_file.close()

If I'm understanding this correctly, each row of contents you have is a list, and that is then contained in a list you've named 'contents'. So wouldn't you be able to do: for row in contents: write.writerow(row) ? Let me know if this doesn't make sense. :) — Alexandros Gouvatsos
– Alexandros Gouvatsos, Commented Sep 7, 2020 at 11:02
@AlexandrosGouvatsos: thanks! I have just tried (replacing my for loop), but it just prints the contents from one single index to multiple lines. There is still the same problem: how to I get the values from all the other indexes? (87 in total). My aim is to have a csv with a line per index and each index has a value under each of the specified headers. — user10489057
– user10489057, Commented Sep 7, 2020 at 11:34

Adirio · Accepted Answer · 2020-09-07 11:35:39Z

2

Several things:

Opening files and then closing them has the issue that if an exception occurs, you are not closing them. To prevent that you can use a with statement that will handle closing the file when you go out of it, even with exceptions.
You created a writer but then parsed the CSV yourself without using the writer. I also named it writer instead of write.
You want to use a for loop for each item in data.
I also avoided creating a variable that I will only use once.
You had an extra set of square brackets ([]) per item in each row, that's why you had a second for loop inside.

import json
import csv


with open("unique_json.json", "r", encoding="utf-8") as json_file:
    data = json.load(json_file)

with open("csv_format2.csv", "w", encoding="utf-8") as csv_file
    writer = csv.writer(csv_file)

    writer.writerow(["url", "likes", "comments", "timestamp"])

    for row in data:
        writer.writerow([
            row["node"]["display_url"],
            row["node"]["edge_liked_by"]["count"],
            row["node"]["edge_media_to_comment"]["count"],
            row["node"]["taken_at_timestamp"],
        ])

edited Sep 7, 2020 at 11:35

answered Sep 7, 2020 at 11:29

Adirio

5,3041 gold badge18 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user10489057 Over a year ago

Thank you! Unfortunately, I am still a beginner with Python. This is so helpful. It works now.

Adirio Over a year ago

I noticed. That's why I commented which changes I did as I was doing them so that I didn't miss any of them. If you find difficult to understand any part just ask here in the comments.

Collectives™ on Stack Overflow

How to import data from nested JSON file to CSV?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related