Sort with spaces a JSON File from a dataframe in Pandas

Question

I'm exporting a dataframe to a JSON file, through these lines of code:

with open('example.json', 'w') as f:
for row in df3.iterrows():
    row[1].to_json(f, orient=None, lines=False)
    f.write("\n")

And it returns a file like this:

{"age":20,"city":"Burdinne","email":"[email protected]","name":"Zorita","phone":4565434645.0,"postal_code":42680.0,"regDate":"2015-06-14T12:12:00-07:00"}
{"age":22,"city":"Bharatpur","email":"[email protected]","name":"Mariam","phone":null,"postal_code":null,"regDate":"2016-10-14T18:52:48-07:00"}
{"age":28,"city":"Neerheylissem","email":"[email protected]","name":"Malik","phone":null,"postal_code":null,"regDate":"2016-09-20T18:06:55-07:00"}
{"age":24,"city":"San Fratello","email":"[email protected]","name":"Claire","phone":null,"postal_code":null,"regDate":"2016-12-29T09:49:13-08:00"}
{"age":30,"city":"La Cruz","email":"[email protected]","name":"Hilel","phone":null,"postal_code":null,"regDate":"2016-07-09T12:03:31-07:00"}

However, I would like that JSON file to be tabulated like this:

[
  {
    "name": "Zorita",
    "email": "[email protected]",
    "regDate": "2015-06-14T12:12:00-07:00",
    "city": "Burdinne",
    "age": 20,
    "postal_code":42680,
    "phone": 4565434645
  },
  {
    "name": "Mariam",
    "email": "[email protected]",
    "regDate": "2016-10-14T18:52:48-07:00",
    "city": "Bharatpur",
    "age": 22
  },
  {
    "name": "Malik",
    "email": "[email protected]",
    "regDate": "2016-09-20T18:06:55-07:00",
    "city": "Neerheylissem",
    "age": 28
  },
  {
    "name": "Claire",
    "email": "[email protected]",
    "regDate": "2016-12-29T09:49:13-08:00",
    "city": "San Fratello",
    "age": 24
  },
  {
    "name": "Hilel",
    "email": "[email protected]",
    "regDate": "2016-07-09T12:03:31-07:00",
    "city": "La Cruz",
    "age": 30
  }
]

How could I do this? In my code I'm trying to put the line break with "\ n" but apparently I'm not doing it correctly

Use the json library - it has a dump function with an indent parameter that will pretty-print the code for you. — bmat
– bmat, Commented Mar 8, 2019 at 8:29

skaul05 · Accepted Answer · 2019-03-08 08:55:20Z

1

Try below code:

final_list = list()
for row in df3.iterrows():
    final_list.append(row[1].to_dict(orient=None))

with open('example.json', 'w') as f:
    f.write(json.dumps(final_list, indent=4))

edited Mar 8, 2019 at 8:55

answered Mar 8, 2019 at 8:32

skaul05

2,3843 gold badges21 silver badges29 bronze badges

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

5 Comments

IriSandivel Over a year ago

With the code the result is similar to this: "[{\"age\":20,\"city\":\"Burdinne\",\"email\":\"[email protected]\",\"name\":\"Zorita\.." and in a single line

skaul05 Over a year ago

You need indentation for not a single line output. Updated solution @IriSandivel

IriSandivel Over a year ago

These diagonals keep appearing: "\" [ "{\"age\":20,\"city\":\"Burdinne\",\"email\":\"[email protected]\",\"name\":\"Zorita\",\"phone\":4565434645.0,\"postal_code\":42680.0,\"regDate\":\"2015-06-14T12:12:00-07:00\"}", ]

skaul05 Over a year ago

These quotes are arriving due to double encoding of JSON strings. Try to remove to _json while appending in final_list. Updated solution @IriSandivel

IriSandivel Over a year ago

I just had to remove "orient=None". But it's ready. Thanks!!

jezrael · Accepted Answer · 2019-03-08 09:30:58Z

You can convert column to list and write to file by json.dump with parameter indent and if necessary sort_keys=True for pretty json:

import json

with open("example.json", "w") as f:
    json.dump(df[1].tolist(), f, indent=4, sort_keys=True)

Sample:

d = [
  {
    "name": "Zorita",
    "email": "[email protected]",
    "regDate": "2015-06-14T12:12:00-07:00",
    "city": "Burdinne",
    "age": 20,
    "postal_code":42680,
    "phone": 4565434645
  },
  {
    "name": "Mariam",
    "email": "[email protected]",
    "regDate": "2016-10-14T18:52:48-07:00",
    "city": "Bharatpur",
    "age": 22
  }

]

df = pd.DataFrame({1: d})
#print (df)

import json

with open("example.json", "w") as f:
    json.dump(df[1].tolist(), f, indent=4, sort_keys=True)

[
    {
        "age": 20,
        "city": "Burdinne",
        "email": "[email protected]",
        "name": "Zorita",
        "phone": 4565434645,
        "postal_code": 42680,
        "regDate": "2015-06-14T12:12:00-07:00"
    },
    {
        "age": 22,
        "city": "Bharatpur",
        "email": "[email protected]",
        "name": "Mariam",
        "regDate": "2016-10-14T18:52:48-07:00"
    }
]

Kenan · Accepted Answer · 2019-07-29 18:03:21Z

0

Although this has been answered by @skaul05, using iterrows can be inefficient. This might be better

with open('file.json', 'w') as f:
    f.write(json.dumps(json.loads(df.to_json()), indent=4))

answered Jul 29, 2019 at 18:03

Kenan

14.2k9 gold badges47 silver badges56 bronze badges

Collectives™ on Stack Overflow

Sort with spaces a JSON File from a dataframe in Pandas

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related