how can I convert a json object and pass to a pandas dataframe?-CodePudding

I am using the Kucoin api to get all the tickers that they currently offer. I then want to pass parts of the JSON object to a pandas dataframe. One ticker JSON object (of hundreds) looks like this:

data = {
    "time":1602832092060,
    "ticker":[
        {
            "symbol": "BTC-USDT",
            "symbolName":"BTC-USDT",
            "buy": "11328.9",
            "sell": "11329",
            "changeRate": "-0.0055",
            "changePrice": "-63.6",
            "high": "11610",
            "low": "11200",
            "vol": "2282.70993217",
            "volValue": "25984946.157790431",
            "last": "11328.9",
            "averagePrice": "11360.66065903",
            "takerFeeRate": "0.001",
            "makerFeeRate": "0.001",
            "takerCoefficient": "1",
            "makerCoefficient": "1"
        }
    ]

 ..... }

I want to make a datframe that includes some but not all of the "columns". Namely: symbol, buy, sell, high, low, averagePrice

I have tried:

pd.to_json
pd.json_normalize

I have tried

df = pd.DataFrame(data['ticker'][0])

Nothing works. I get various different error messages quoting "scalar values", lack of index.

I shall be grateful for any help!

CodePudding user response：

Use json_normalize with record_path and meta parameters:

out = pd.json_normalize(data, record_path='ticker', meta='time')[['time', 'symbol', 'buy', 'sell', 'high', 'low', 'averagePrice']]

Output:

            time    symbol      buy   sell   high    low    averagePrice
0  1602832092060  BTC-USDT  11328.9  11329  11610  11200  11360.66065903