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

Do you mind to share with us the expected output?

rpanai
– rpanai

2018-06-14 14:06:33 +00:00
Commented Jun 14, 2018 at 14:06 — rpanai
– rpanai, Commented Jun 14, 2018 at 14:06
@user32185 Please check the expected output!

jason
– jason

2018-06-14 14:25:31 +00:00
Commented Jun 14, 2018 at 14:25 — jason
– jason, Commented Jun 14, 2018 at 14:25

MaxU - stand with Ukraine · Accepted Answer · 2018-06-14 14:42:38Z

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

edited Jun 14, 2018 at 14:42

answered Jun 14, 2018 at 14:12

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

1 Comment

jason Over a year ago

Hi MaxU, I'm looking for solution something around this : ,data,data2,results.id,results.more_data.0.no,results.more_data.0.code,results.name 0,abc,123,1, '111', "3",w

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related