Nested Json in Pandas Column

Question

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])

U13-Forward · Accepted Answer · 2021-08-24 07:50:27Z

1

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]

edited Aug 24, 2021 at 7:50

answered Aug 24, 2021 at 6:38

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Suraj Shejal Over a year ago

it comes one after another i want in one row

Suraj Shejal Over a year ago

why it creates 75 columns it should create for buy 5x3 = 15columns

Suraj Shejal Over a year ago

i have added demo dataframe can u check

U13-Forward Over a year ago

@SurajS Edited my answer pls check it out

Suraj Shejal Over a year ago

its not working it gives 3 columns but needs 15 buy columns and 15 sell columns with only 2 rows as per demo data given

Collectives™ on Stack Overflow

Nested Json in Pandas Column

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related