CODEWITHSUNDEEP
pandas
kang
kang

Reputation: 707

Convert JSON file

I have a set of data in JSON format. From what I understand, JSON is not usable and I need to convert it into some kind of table format for me to use as a data set.

I have tried to convert the file to CSV. It works but it doesn't come out correctly. It ends up as without proper labelling and columns. Is there a way around this?

This is the data set example. 

{
  "data": {
    "1": {
      "id": 1,
      "items": [
        "bar",
        "cream"
      ],
      "extras": {},
      "supply": "crate"
    }
}

This is what I did with Pandas.

import pandas as pd
pd.read_json("myfile.json").to_csv("new.csv")

Upvotes: 4

Views: 167

Answers (1)

dportman
dportman

Reputation: 1109

You need to call pd.read_json with the proper orient parameter.

This is from the read_json doc:

orient : string,

Indication of expected JSON string format. Compatible JSON strings can be produced by to_json() with a corresponding orient value. The set of possible orients is:

'split' : dict like {index -> [index], columns -> [columns], data -> [values]}

'records' : list like [{column -> value}, ... , {column -> value}]

'index' : dict like {index -> {column -> value}}

'columns' : dict like {column -> {index -> value}}

'values' : just the values array

In your case, I think pd.read_json("myfile.json", orient="values") should work.

If you have nested json fields, you will have to also use pd.json_normalize() to flatten the nested fields (consult here).

Upvotes: 2

Related Questions