0

Tried solution shared in link :: Nested json to csv - generic approach

This worked for Sample 1 , but giving only a single row for Sample 2. is there a way to have generic python code to handle both Sample 1 and Sample 2.

Sample 1 ::

{
    "Response": "Success",
    "Message": "",
    "HasWarning": false,
    "Type": 100,
    "RateLimit": {},
    "Data": {
        "Aggregated": false,
        "TimeFrom": 1234567800,
        "TimeTo": 1234567900,
        "Data": [
            {
                "id": 11,
                "symbol": "AAA",
                "time": 1234567800,
                "block_time": 123.282828282828,
                "block_size": 1212121,
                "current_supply": 10101010
            },
            {
                "id": 12,
                "symbol": "BBB",
                "time": 1234567900,
                "block_time": 234.696969696969,
                "block_size": 1313131,
                "current_supply": 20202020
            },
        ]
    }
}

Sample 2::

{
    "Response": "Success",
    "Message": "Summary succesfully returned!",
    "Data": {
        "11": {
            "Id": "3333",
            "Url": "test/11.png",
            "value": "11",
            "Name": "11 entries (11)"
        },
        "122": {
            "Id": "5555555",
            "Url": "test/122.png",
            "Symbol": "122",
            "Name": "122 cases (122)"
        }
     },
    "Limit": {},
    "HasWarning": False,
    "Type": 50
}
Krishna
  • 924
  • 1
  • 7
  • 28
Eja
  • 45
  • 1
  • 7
  • I haven't looked in too close detail to sample 2, but doesn't pandas normalize JSON work for sample 2? Then you can push it into a dataframe and export into any format you like. – Michaël van der Haven Feb 28 '22 at 07:52
  • json_normalize does not work out of the box if you have lists somewhere in JSON – Krishna Feb 28 '22 at 08:15
  • Agreed with Krishna, direct json_normalize doesn't support generically nested JSON files. – Eja Feb 28 '22 at 11:22
  • Tried using shared code, which works well for Sample1 data , but its generates single row as output with this generic code for Sample2 data – Eja Feb 28 '22 at 11:24
  • What is the error you are getting – Krishna Mar 02 '22 at 04:10

1 Answers1

0

Try this, you need to install flatten_json from here

import sys
import csv
import json
from flatten_json import flatten


data = json.load(open(sys.argv[1]))
data = flatten(data)

with open('foo.csv', 'w') as f:
    out = csv.DictWriter(f, data.keys())
    out.writeheader()
    out.writerow(data)

Output

> cat foo.csv
Response,Message,Data_11_Id,Data_11_Url,Data_11_value,Data_11_Name,Data_122_Id,Data_122_Url,Data_122_Symbol,Data_122_Name,Limit,HasWarning,Type
Success,Summary succesfully returned!,3333,test/11.png,11,11 entries (11),5555555,test/122.png,122,122 cases (122),{},False,50

Note: False is incorrect in Json, you need to change it to false

Krishna
  • 924
  • 1
  • 7
  • 28
  • getting this error while trying to use flatten_json "UnicodeDecodeError: 'charmap' codec can't decode byte 0x8d in position 1001498: character maps to ". Looking for some generic code that can handle Sample data 1 and Sample data 2 both. flatten_json is unable to handle sample code 1 – Eja Mar 02 '22 at 15:31
  • It's not because of the library. It's the encoding of the file you have issues with – Krishna Mar 02 '22 at 21:47
  • HI Krishna , tried using utf8 , utf16, cp437 along with errors='ignore' as well. But still looking for a generic code eg like :: https://stackoverflow.com/questions/37706351/nested-json-to-csv-generic-approach which can handle both Sample Code 1 and Sample code 2 to convert from nested JSON file to CSV. – Eja Mar 03 '22 at 13:04
  • and even if we try to take sample code 2 in a string of data and apply the solution provided, it creates only a single row as output, we should be getting 2 rows as output of Sample code 2 JSON data. – Eja Mar 03 '22 at 14:43
  • Please share your expected output in each case – Krishna Mar 04 '22 at 11:04
  • Expected Output of Sample code 1 :: Response,Message,HasWarning,Type,Data.Aggregated,Data.TimeFrom,Data.TimeTo,Data.Data.id,Data.Data.symbol,Data.Data.time,Data.Data.block_time,Data.Data.block_size,Data.Data.current_supply Success,,False,100,False,1234567800,1234567900,11,AAA,1234567800,123.282828282828,1212121,10101010 Success,,False,100,False,1234567800,1234567900,12,BBB,1234567900,234.696969696969,1313131,20202020 – Eja Mar 06 '22 at 10:55
  • Expected Output of Sample Code 2:: Response,Message,Data,Id,Url,Symbol,Name,HasWarning,Type Success,Summary succesfully returned!,11,3333,test/11.png,11,11 entries (11),false,50 Success,Summary succesfully returned!,122,5555555,test/122.png,122,122 cases (122),false,50 – Eja Mar 06 '22 at 11:00