Python Pandas Dataframe: Nested JSON to columns, query 1 column and return respective cell value

user_9651740 0
Viewed 85

I am fetching JSON from API, here is an example of returned JSON:

dct = {
    "error": [],
    "result": {
        "open": {
            "OECXRU-DHI22-OOCVY7": {
                "refid": None,
                "userref": 0,
                "status": "open",
                "opentm": 1627129746.1643,
                "starttm": 0,
                "expiretm": 0,
                "descr": {
                    "pair": "STORJUSD",
                    "type": "sell",
                    "ordertype": "stop-loss",
                    "price": "0.79000",
                    "price2": "0",
                    "leverage": "none",
                    "order": "sell 63.72307378 STORJUSD @ stop loss 0.79000",
                    "close": "",
                },
                "vol": "63.72307378",
                "vol_exec": "0.00000000",
                "cost": "0.000000",
                "fee": "0.000000",
                "price": "0.000000",
                "stopprice": "0.000000",
                "limitprice": "0.000000",
                "misc": "",
                "oflags": "fciq",
            }
        }
    },
}

I wish to query dataframe based on key pair and return the transaction ID i.e. return OECXRU-DHI22-OOCVY7 when queried STORJUSD.

I have tried various methods: json_normalize(), DataFrame.from_dict(), DataFrame.from_records()

But I am getting all different kinds of errors in each of these methods.

pandas dataframe python
2 Answers

Just using Python, you could try this:

def query(dct, pair):
    for ref, transaction in dct["result"]["open"].items():
        if transaction["descr"]["pair"] == pair:
            return ref

print(query(dct, "STORJUSD"))
# Outputs OECXRU-DHI22-OOCVY7
user_112460560

Try:

dct = {
    "error": [],
    "result": {
        "open": {
            "OECXRU-DHI22-OOCVY7": {
                "refid": None,
                "userref": 0,
                "status": "open",
                "opentm": 1627129746.1643,
                "starttm": 0,
                "expiretm": 0,
                "descr": {
                    "pair": "STORJUSD",
                    "type": "sell",
                    "ordertype": "stop-loss",
                    "price": "0.79000",
                    "price2": "0",
                    "leverage": "none",
                    "order": "sell 63.72307378 STORJUSD @ stop loss 0.79000",
                    "close": "",
                },
                "vol": "63.72307378",
                "vol_exec": "0.00000000",
                "cost": "0.000000",
                "fee": "0.000000",
                "price": "0.000000",
                "stopprice": "0.000000",
                "limitprice": "0.000000",
                "misc": "",
                "oflags": "fciq",
            }
        }
    },
}

all_data = []
for k, v in dct["result"]["open"].items():
    data = {}
    data["transaction id"] = k
    data.update(v)
    all_data.append(data)

df = pd.json_normalize(all_data)

# print the dataframe
print(df)

# query the dataframe
print(df.loc[df["transaction id"].eq("OECXRU-DHI22-OOCVY7"), "descr.pair"])

Prints the df:

        transaction id refid  userref status        opentm  starttm  expiretm          vol    vol_exec      cost       fee     price stopprice limitprice misc oflags descr.pair descr.type descr.ordertype descr.price descr.price2 descr.leverage                                    descr.order descr.close
0  OECXRU-DHI22-OOCVY7  None        0   open  1.627130e+09        0         0  63.72307378  0.00000000  0.000000  0.000000  0.000000  0.000000   0.000000        fciq   STORJUSD       sell       stop-loss     0.79000            0           none  sell 63.72307378 STORJUSD @ stop loss 0.79000

And the query:

0    STORJUSD
Name: descr.pair, dtype: object
user_100359850
Related