Python JSON to CSV not working

Question

I am having trouble to convert json to CSV. I am kinda new to JSON parsing. All I want is to get from my JSON some "pretty" csv. So I can have data that I can read.

I am trying

f = csv.writer(open('data.csv', 'w+', encoding='utf-8', newline=''))
for item in json_head:
    f.writerow(item)

but all I get in data.csv is

d,a,t,a
i,n,c,l,u,d,e,d
l,i,n,k,s
m,e,t,a

Json template that's in documentation looks like this

{
  "data": {
    "type": "string",
    "id": "string",
    "attributes": {
      "createdAt": "string",
      "duration": 0,
      "gameMode": "string",
      "patchVersion": "string",
      "shardId": "string",
      "stats": {},
      "tags": {},
      "titleId": "string"
    },
    "relationships": {
      "assets": {
        "data": [
          {
            "type": "string",
            "id": "string"
          }
        ]
      },
      "rosters": {
        "data": [
          {
            "type": "string",
            "id": "string"
          }
        ]
      },
      "rounds": {},
      "spectators": {}
    },
    "links": {
      "schema": "string",
      "self": "string"
    }
  },
  "included": [
    null
  ],
  "links": {
    "first": "string",
    "previous": "string",
    "self": "string",
    "next": "string"
  },
  "meta": {}
}

And part of real JSON I received via API looks like this

{"data":
    {"type":"match","id":"bdd8b91c-8866-47f1-b5ee-45de4163d3ad","attributes":{"createdAt":"2018-04-02T12:16:33Z","duration":1806,"gameMode":"squad-fpp","patchVersion":"","shardId":"pc-eu","stats":null,"tags":null,"titleId":"bluehole-pubg"},"relationships":{"assets":{"data":[{"type":"asset","id":"2609ba26-3674-11e8-b808-0a586466e20b"}]},
    "rosters":{"data":[
    {"type":"roster","id":"80a9215c-bed1-4c11-8ff7-e4c9d643befa"},
    {"type":"roster","id":"1457bd01-80f9-45e3-b98f-aa9ea946f6b2"}]},
    "rounds":{"data":[]},"spectators":{"data":[]}},"links":{"schema":"","self":"https://api.playbattlegrounds.com/shards/pc-eu/matches/bdd8b91c-8866-47f1-b5ee-45de4163d3ad"}},
    "included":[
    {"type":"participant","id":"f0d889c7-49e7-4c83-ac0b-d7f23d13e09f","attributes":{"actor":"","shardId":"pc-eu","stats":{"DBNOs":3,"assists":0,"boosts":2,"damageDealt":253.554047,"deathType":"byplayer","headshotKills":1,"heals":0,"killPlace":4,"killPoints":1182,"killPointsDelta":48.6543655,"killStreaks":0,"kills":4,"lastKillPoints":0,"lastWinPoints":0,"longestKill":5,"mostDamage":0,"name":"TheFieryArrow","playerId":"account.e951c5cf324d4153850fb411a13f4e8d","revives":0,"rideDistance":4288.21143,"roadKills":0,"teamKills":0,"timeSurvived":1215,"vehicleDestroys":0,"walkDistance":1508.55408,"weaponsAcquired":0,"winPlace":15,"winPoints":1113,"winPointsDelta":7.15782833}}},
    {"type":"participant","id":"5c26da97-6733-49d4-8a56-5dc7513e3c77","attributes":{"actor":"","shardId":"pc-eu","stats":{"DBNOs":0,"assists":0,"boosts":0,"damageDealt":64.68,"deathType":"byplayer","headshotKills":0,"heals":0,"killPlace":93,"killPoints":1111,"killPointsDelta":-14.2245054,"killStreaks":0,"kills":0,"lastKillPoints":0,"lastWinPoints":0,"longestKill":0,"mostDamage":0,"name":"Ritter-Sport","playerId":"account.f704b21b861b4c649ec56a0d688171c8","revives":0,"rideDistance":0,"roadKills":0,"teamKills":0,"timeSurvived":142,"vehicleDestroys":0,"walkDistance":43.5367622,"weaponsAcquired":0,"winPlace":28,"winPoints":1220,"winPointsDelta":-19.1033173}}},

For beginning it's enough if there is a way to get data from specific elements, for me those are these elements "type":"participant" And I would expect tabular data like this

ID                                    |DBNOs | assists | boosts | othercolumns from "stats"
f0d889c7-49e7-4c83-ac0b-d7f23d13e09f  | 3    |   0     |   2    |
5c26da97-6733-49d4-8a56-5dc7513e3c77  | 0    |   0     |   0    |

I know I could hardcode some code to parse it, I am just looking for every possibility.

Thanks

JSON stores arbitrarily shaped data, CSV is specifically (usually) for tabular data (a table with distinct columns). There isn't any one particular, good way to convert the JSON schema you provided into a CSV table. Please give an example of the kind of CSV output you expect for the given real JSON data you provided. — scnerd
– scnerd, Commented Apr 3, 2018 at 21:33
@PatrikPlecho If all you want to get is the data printed in an easy-to-read way, ask that instead of "how to get to CSV". There are actually good answers to that questions, but CSV seems like a very strange and potentially impossible thing to ask for in this particular situation — scnerd
– scnerd, Commented Apr 3, 2018 at 21:39
I think the correct answer is "hardcode some code to parse it", there's no simpler way to extract out the data you're looking for. JSON isn't tabular, and if you want CSV out, you need to manually write the code to extract the data you're interested in into a tabular format. The JSON data you give is multi-tiered with embedded lists, and you want data from multiple levels... You can iterate through dictionaries using dict.items() to get tuples of key-value pairs. I really don't know what else to offer to help you out here. There's no magic bullet that does exactly what you want. — scnerd
– scnerd, Commented Apr 3, 2018 at 21:53

Anshul Goyal · Accepted Answer · 2018-04-03 21:08:19Z

2

When you do

for item in json_head:
    f.writerow(item)

it only reads the keys within your json, which are then passed as a string to the writerow method, which treats it as an iterable of characters and writes individual characters to csv.

Instead, try doing this:

for item in json_head:
    f.writerow([item, json_head[item])

answered Apr 3, 2018 at 21:08

Anshul Goyal

78k42 gold badges161 silver badges196 bronze badges

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

3 Comments

Barmar Over a year ago

Or for index, item in enumerate(json_head)

Patrik Plecho Over a year ago

The result in final file looked like "item" : "plain JSON as string" "item2" : "plain JSON as string"

Anshul Goyal Over a year ago

@PatrikPlecho Alright. what would your expected CSV look like for the data in the question? Json keys can be arbitary, and there isn't a way of knowing if they are all going to be common across the board.

Collectives™ on Stack Overflow

Python JSON to CSV not working

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related