Can't append nested JSON value in python pandas dataframe

Question

I try to append one value from JSON data to a dataframe and print it. What works is to print the close, or to append and print the entire JSON block json_message. Unfortunately it doesn't print anything if I try it with json_message['k']['c'] How could I access and print only the nested 'c' value?

import websocket, json, pprint
import numpy as np
import pandas as pd

SOCKET = "wss://stream.binance.com:9443/ws/ethusdt@kline_1m"

df = pd.DataFrame(columns=['1'])

def on_open(ws):
    print('opened connection')

def on_close(ws):
    print('closed connection')

def on_message(ws, message):
    global df
    json_message = json.loads(message)
    pprint.pprint(json_message)

    close = float(json_message['k']['c'])

    df = df.append(close, ignore_index=True)

    is_bar_closed = json_message['k']['x']
    
    if is_bar_closed == False:
    
        print(close)


ws = websocket.WebSocketApp(SOCKET, on_open=on_open, on_close=on_close, on_message=on_message)
ws.run_forever()

OUTPUT:

{'E': 1616880759079,
 'e': 'kline',
 'k': {'B': '0',
       'L': 339352101,
       'Q': '89163.86464830',
       'T': 1616880779999,
       'V': '51.79165000',
       'c': '1721.18000000',
       'f': 339351937,
       'h': '1721.69000000',
       'i': '1m',
       'l': '1721.00000000',
       'n': 165,
       'o': '1721.66000000',
       'q': '154581.23992910',
       's': 'ETHUSDT',
       't': 1616880720000,
       'v': '89.79198000',
       'x': False},
 's': 'ETHUSDT'}

Desired OUTPUT:

{'E': 1616880831330,
 'e': 'kline',
 'k': {'B': '0',
       'L': 339352406,
       'Q': '134124.45737740',
       'T': 1616880839999,
       'V': '77.94314000',
       'c': '1720.65000000',
       'f': 339352213,
       'h': '1720.82000000',
       'i': '1m',
       'l': '1720.59000000',
       'n': 194,
       'o': '1720.81000000',
       'q': '177486.68404770',
       's': 'ETHUSDT',
       't': 1616880780000,
       'v': '103.14269000',
       'x': False},
 's': 'ETHUSDT'}
1720.65

Answer 1

Issue with your code is that you are trying to append a float to dataframe which is not a supported operation: df = df.append(close, ignore_index=True)

Here close must be a DataFrame or Series/dict-like object, or list of these. So converting close to a dict should solve the issue:

row = {'1': close}

Now we can append this row to df (printing df to see it works):

def on_message(ws, message):
    global df
    json_message = json.loads(message)
    pprint.pprint(json_message)

    close = float(json_message['k']['c'])
    row = {'1': close}
    df = df.append(row, ignore_index=True)
    print(df)

    is_bar_closed = json_message['k']['x']

    if is_bar_closed == False:
        print(close)