CODEWITHSUNDEEP
pythonarraysjsonpandas
Sunny J
Sunny J

Reputation: 47

Get nested JSON to be pandas dataframe

I have this JSON,

{
  "status": "ok",
  "stocks": [
    {
      "symbol": "TSLA",
      "price": [
        {
          "date": "2021-10-19",
          "close": 141.98
        }
      ]
    },
    {
      "symbol": "AMZN",
      "price": [
        {
          "date": "2021-10-19",
          "close": 3444.15
        }
      ]
    }
  ]
}

I need to create two pandas dataframe from the first array and second array based on the price without using the stock name to identify it.

Expected:

First array (which is based on TSLA), df1

    date      close
2021-10-19   141.98

Second array (which is based on AMZN), df2

    date      close
2021-10-19   3444.15

I had explored json_normalize function from pandas but it seem like it can only flatten first level of a JSON. How can I flatten the second layer and only get my expected result?

EDIT:

Managed to somewhat get it to work, less not my expected result totally. Using this,

df = pd.json_normalize(data['stocks'], record_path='price', meta=['symbol'])

It returns

    date      close    symbol
2021-10-19   141.98    TSLA
2021-10-19   3444.15   AMZN

Is there a way to make the two stocks split when performing the json_normalize like what my expected result suppose to be?

Upvotes: 1

Views: 45

Answers (1)

BENY
BENY

Reputation: 323236

In your example

df = df.set_index('symbol')

Then just call

df.loc['TSLA']

Or you can do

d = {x : y for x ,y in df.groupby(df.index)}
d[0]
d[1]

Upvotes: 1

Related Questions