CODEWITHSUNDEEP
pythonpython-3.xpandas
Wisdom258
Wisdom258

Reputation: 173

Reading a JSON file in pandas

I have the following structure of the json file, I am trying to load in pandas, but all the columns are not coming as I want.

[
    {
        "prime": {
            "n": "0"
        },
        "min": {
            "n": "1"
        },
        "sk": {
            "s": "1#2017-02-14#19:46:00#THIRDPARTYNEW"
        },
        "price": {
            "n": "3.49"
        },
        "asin": {
            "s": "B00LEACCKG"
        },
        "shCost": {
            "n": "0"
        },
        "date": {
            "s": "2017-02-14"
        },
        "merchId": {
            "s": "THIRDPARTYNEW"
        }
    },
    {
...
...
]


df = pd.read_json('combinedfiles/data.json', orient='records')
df.head()

Here is my output.

df

load the data properly, here dicts and list are showin inside the pandas dataframe.

I have tried other solutions here, but I believe they dont work.

Upvotes: 3

Views: 3348

Answers (1)

Dani G
Dani G

Reputation: 1252

Here you go:

import json
import pandas as pd

with open('test.json') as f:
    org = json.load(f)

transformed_dict = [{k:list(v.values())[0] for k,v in original_dict.items()} for 
original_dict  in org]
df = pd.DataFrame.from_records(transformed_dict)

Upvotes: 4

Related Questions