convert all data within the json to csv using pandas or python

Question

I tried using json_normalize to flatten data but a part of the data is still not flattened. How can I get flattened data for this json. Note: This is a sample json file but the data might increase in the nested part.

Code:

json_file = {
  "data": "abc",
  "data2": 123,
  "results": {
    "name": "w",
    "more_data": [
      {
        "no": "111",
        "code": 3
      }
    ],
    "id": 1
  }
}

data = json_normalize(json_file)
data.to_csv('flatten.csv')

Result:

,data,data2,results.id,results.more_data,results.name
0,abc,123,1,"[{'no': '111', 'code': 3}]",w


results.more_data still gives me a json instead of flattening it.

How can I get it to work?

Expected output:

Since results.more_data is an array even this has to be flattened. Eg:

,data,data2,results.id,results.more_data.0.no,results.more_data.0.code,results.name
    0,abc,123,1, '111', "3",w

Answer 1

Consider the following demo:

In [105]: json_file = {
     ...:   "data": "abc",
     ...:   "data2": 123,
     ...:   "results": {
     ...:     "name": "w",
     ...:     "more_data": [
     ...:       {
     ...:         "no": "111",
     ...:         "code": 3
     ...:       },
     ...:       {
     ...:         "no": "222",
     ...:         "code": 4
     ...:       }
     ...:
     ...:     ],
     ...:     "id": 1
     ...:   }
     ...: }
     ...:

In [106]:

In [106]: json_normalize(json_file, 
                         [['results','more_data']], 
                         ['data','data2', ['results','id'], ['results','name']],
                         record_prefix='results.more_data.')
Out[106]:
   results.more_data.code results.more_data.no data  data2  results.id results.name
0                       3                  111  abc    123           1            w
1                       4                  222  abc    123           1            w