CODEWITHSUNDEEP
pythonjsonpandas
Suraj Shejal
Suraj Shejal

Reputation: 650

Nested Json in Pandas Column

I have a dataframe with nested json as column.

df.depth
0     {'buy': [{'quantity': 51, 'price': 2275.85, 'o...
1     {'buy': [{'quantity': 1, 'price': 2275.85, 'or...
2     {'buy': [{'quantity': 1, 'price': 2275.85, 'or..

inside each row have 5 depths of buy sell

df.depth[0]
{'buy': [{...}, {...}, {...}, {...}, {...}], 'sell': [{...}, {...}, {...}, {...}, {...}]}

real json structure is as below

{'buy': [{'quantity': 51, 'price': 2275.85, 'orders': 2}, {'quantity': 38, 'price': 2275.8, 'orders': 2}, {'quantity': 108, 'price': 2275.75, 'orders': 3}, {'quantity': 120, 'price': 2275.7, 'orders': 2}, {'quantity': 6, 'price': 2275.6, 'orders': 1}], 'sell': [{'quantity': 353, 'price': 2276.95, 'orders': 1}, {'quantity': 29, 'price': 2277.0, 'orders': 2}, {'quantity': 54, 'price': 2277.1, 'orders': 2}, {'quantity': 200, 'price': 2277.2, 'orders': 1}, {'quantity': 4, 'price': 2277.25, 'orders': 1}]}

i want to explode this in to something like this

Required Output:

   depth.buy.quantity1  df.depth.buy.price1 ... depth.sell.quantity1 depth.sell.price1....
0      51                2275.85....                    353          2276
1      1                 2275.85....                    352          2276

how to do it ?

Edit:

for help i have added demo dataframe:

a={'buy': [{'quantity': 51, 'price': 2275.85, 'orders': 2}, {'quantity': 38, 'price': 2275.8, 'orders': 2}, {'quantity': 108, 'price': 2275.75, 'orders': 3}, {'quantity': 120, 'price': 2275.7, 'orders': 2}, {'quantity': 6, 'price': 2275.6, 'orders': 1}], 'sell': [{'quantity': 353, 'price': 2276.95, 'orders': 1}, {'quantity': 29, 'price': 2277.0, 'orders': 2}, {'quantity': 54, 'price': 2277.1, 'orders': 2}, {'quantity': 200, 'price': 2277.2, 'orders': 1}, {'quantity': 4, 'price': 2277.25, 'orders': 1}]}
c=dict()
c['depth'] = a
df = pd.DataFrame([c,c])

Upvotes: 1

Views: 62

Answers (1)

U13-Forward
U13-Forward

Reputation: 71580

You could try concat:

df = pd.concat([pd.concat([pd.DataFrame(x, index=[0]) for x in i], axis=1) for i in pd.json_normalize(df['depth'])['buy'].tolist()], ignore_index=True)
print(df)

Output:

   quantity    price  orders  quantity   price  orders  ...  quantity   price  orders  quantity   price  orders
0        51  2275.85       2        38  2275.8       2  ...       120  2275.7       2         6  2275.6       1
1        51  2275.85       2        38  2275.8       2  ...       120  2275.7       2         6  2275.6       1

[2 rows x 15 columns]

Upvotes: 1

Related Questions