Python JSON issues

Question

I am a total beginner and am currently trying to learn python. Right now objects is my biggest issue. I have a simple project where I request an REST api und the response is json and looks a bit like this:

{
"x": 1,
"y": 2,
"z": [
      {"a": 1, "b": 1, "c": 1}
      {"a": 3, "b": 4, "c": 5}]
}

Now I need something like this for my csv columns: for every item in z[]: a,b,c,x,y

I have several 1000 pages of response. How would you solve this? Right now I get objects in a single column. Using pandas to write to csv:

data = response.json()
df = pd.DataFrame(data)
df.to_csv("output.csv")

It's not clear what result you expect. A nested JSON structure does not trivially translate into a simple two-dimensional matrix, which is what you need for a dataframe, and eventually for the CSV format. — tripleee, Apr 26 '23 at 05:55
I'd guess you'd need to use `z` - data = response.json().get("z", []) - and use that? — Julian Camilleri, Apr 26 '23 at 05:55
@tripleee but this is exactly what I am looking for - bringing a nested json to a two dimensional matrix. is there a way? — Daniel Li, Apr 26 '23 at 07:22
In the general case no, not any more than you can faithfully represent all sides of a 3D object in 2D. https://stackoverflow.com/questions/1871524/how-can-i-convert-json-to-csv has some answers; did you search before asking? Perhaps see also https://stackoverflow.com/questions/65338470/json-lines-jsonl-generator-to-csv-format/65338582#65338582 — tripleee, Apr 26 '23 at 07:24
used search but could not find anything relevant. guess I have to find another way to deal with this. thanks for your help — Daniel Li, Apr 26 '23 at 07:53
Hi Daniel. We’ve added some answers you might find helpful. Please check them out. — Zach Young, May 01 '23 at 04:16

score 1 · Answer 1 · answered Apr 26 '23 at 12:02

If you just want to write the records in column z into the CSV-file:

data = {"x": 1, "y": 2, "z": [{"a": 1, "b": 1, "c": 1}, {"a": 3, "b": 4, "c": 5}]}
df = pd.DataFrame(data["z"])
df.to_csv("output.csv", index=None)

If you want to write the full set of data to the file, you could use pd.json_normalize to create the dataframe:

data = {"x": 1, "y": 2, "z": [{"a": 1, "b": 1, "c": 1}, {"a": 3, "b": 4, "c": 5}]}
df = pd.json_normalize(data, record_path="z", meta=["x", "y"])
df.to_csv("output.csv", index=None)

For the sample df will look like:

   a  b  c  x  y
0  1  1  1  1  2
1  3  4  5  1  2

Zach Young · Answer 2 · 2023-04-26T20:05:25.663

1

I interpret your question (like Timus) to mean you want to go from:

{
    "x": 1,
    "y": 2,
    "z": [
        {"a": 1, "b": 1, "c": 1},
        {"a": 3, "b": 4, "c": 5}
    ]
}

to

a,b,c,x,y
1,1,1,1,2
3,4,5,1,2

Even if this is correct, please edit your question and include the expected output CSV.

You can also do this without Pandas, using the csv module.

For every object, loop over the objects in z and write those a, b, and c columns, along with x and y:


data = {
    "x": 1,
    "y": 2,
    "z": [
        {"a": 1, "b": 1, "c": 1},
        {"a": 3, "b": 4, "c": 5},
    ],
}


with open("output.csv", "w", newline="") as f_out:
    writer = csv.writer(f_out)
    writer.writerow(["a", "b", "c", "x", "y"])  # write the header row, if you want it

    x = data["x"]
    y = data["y"]

    for z_obj in data["z"]:
        a = z_obj["a"]
        b = z_obj["b"]
        c = z_obj["c"]

        writer.writerow([a, b, c, x, y])

edited Apr 26 '23 at 20:05

answered Apr 26 '23 at 16:56

Zach Young

10,137
4
32
53

1

Yes, since @danielli is starting to learn Python this might actually be the better way - it teaches more basics and shows what pure Python is capable of (I almost included a version with `csv.DictWriter` which can be used nicely here). – Timus Apr 26 '23 at 18:02
1

@Timus, I almost included an extra example with DictWriter, too (and I just revisited it since your comment), but it just seems muddier to me. – Zach Young Apr 26 '23 at 20:03
It is certainly a bit harder to understand. – Timus Apr 27 '23 at 07:28

Python JSON issues

2 Answers2