Extract values from Dictionary inside a pandas dataframe (Python)

Question

trying to extract the dictionary in a dataframe. but unable to. none of the solution mentioned matches my requirement hence seeking help for the same.

    instrument_token  last_price      change                                                                                          depth
0           17600770      180.75   20.500000  {'buy': [{'quantity': 1, 'price': 1, 'orders': 1},{'quantity': 0, 'price': 0.0, 'orders': 0}], 'sell': [{'quantity': 1, 'price': 1, 'orders': 1},{'quantity': 0, 'price': 0.0, 'orders': 0}]}
1           12615426        0.05  -50.000000  {'buy': [{'quantity': 2, 'price': 2, 'orders': 2},{'quantity': 0, 'price': 0.0, 'orders': 0}], 'sell': [{'quantity': 2, 'price': 2, 'orders': 2},{'quantity': 0, 'price': 0.0, 'orders': 0}]}
2           17543682        0.35  -89.062500  {'buy': [{'quantity': 3, 'price': 3, 'orders': 3},{'quantity': 0, 'price': 0.0, 'orders': 0}], 'sell': [{'quantity': 3, 'price': 3, 'orders': 3},{'quantity': 0, 'price': 0.0, 'orders': 0}]}
3           17565954        6.75  -10.000000  {'buy': [{'quantity': 4, 'price': 4, 'orders': 4},{'quantity': 0, 'price': 0.0, 'orders': 0}], 'sell': [{'quantity': 4, 'price': 4, 'orders': 4},{'quantity': 0, 'price': 0.0, 'orders': 0}]}
4           26077954        3.95  -14.130435  {'buy': [{'quantity': 5, 'price': 5, 'orders': 5},{'quantity': 0, 'price': 0.0, 'orders': 0}], 'sell': [{'quantity': 5, 'price': 5, 'orders': 5},{'quantity': 0, 'price': 0.0, 'orders': 0}]}
5           17599490      141.75   -2.241379  {'buy': [{'quantity': 6, 'price': 6, 'orders': 6},{'quantity': 0, 'price': 0.0, 'orders': 0}], 'sell': [{'quantity': 6, 'price': 6, 'orders': 6},{'quantity': 0, 'price': 0.0, 'orders': 0}]}
6           17566978       17.65   -1.671309  {'buy': [{'quantity': 7, 'price': 7, 'orders': 7},{'quantity': 0, 'price': 0.0, 'orders': 0}], 'sell': [{'quantity': 7, 'price': 7, 'orders': 7},{'quantity': 0, 'price': 0.0, 'orders': 0}]}
7          26075906       24.70  -16.554054  {'buy': [{'quantity': 8, 'price': 8, 'orders': 8},{'quantity': 0, 'price': 0.0, 'orders': 0}], 'sell': [{'quantity': 8, 'price': 8, 'orders': 8},{'quantity': 0, 'price': 0.0, 'orders': 0}]}

looking to convert to the following:

    instrument_token  last_price      change    buy_price    sell_price
0           17600770      180.75   20.500000       1              1
1           12615426        0.05  -50.000000       2              2
2           17543682        0.35  -89.062500       3              3
3           17565954        6.75  -10.000000       4              4
4           26077954        3.95  -14.130435       5              5  
5           17599490      141.75   -2.241379       6              6
6           17566978       17.65   -1.671309       7              7
...

able to access the individual elements using a for loop by unable to convert the dictionary to the desired df.col as shown in the above desired df.

Answer 1

You want to get price only from the first element of the list, and not a sum, then do:

df["buy_price"]=df["depth"].str["buy"].str[0].str["price"]
df["sell_price"]=df["depth"].str["sell"].str[0].str["price"]

In case you wish to get a sum of all nested elements:

df["buy_price"]=df["depth"].str["buy"].apply(lambda x: sum(el["price"] for el in x))
df["sell_price"]=df["depth"].str["sell"].apply(lambda x: sum(el["price"] for el in x))