Question 108752 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Python JSON issues

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")
Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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])
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search