Converting string to dictionary to dataframe

Question

I am trying to fetch data via API which gives output as a string. I converted it to JSON and am having difficulties converting it to a dataframe.

String looks like:

{"MessageCode":1502,"MessageVersion":4,"ApplicationType":0,"TokenID":0,"ExchangeSegment":1,"ExchangeInstrumentID":22,"ExchangeTimeStamp":1309963220,"Bids":[{"Size":1,"Price":1980.15,"TotalOrders":1,"BuyBackMarketMaker":0},{"Size":30,"Price":1980.1,"TotalOrders":1,"BuyBackMarketMaker":0},{"Size":232,"Price":1980.05,"TotalOrders":5,"BuyBackMarketMaker":0},{"Size":593,"Price":1980,"TotalOrders":4,"BuyBackMarketMaker":0},{"Size":141,"Price":1979.95,"TotalOrders":4,"BuyBackMarketMaker":0}],"Asks":[{"Size":5,"Price":1980.65,"TotalOrders":4,"BuyBackMarketMaker":0},{"Size":564,"Price":1980.7,"TotalOrders":4,"BuyBackMarketMaker":0},{"Size":105,"Price":1980.9,"TotalOrders":2,"BuyBackMarketMaker":0},{"Size":2,"Price":1981,"TotalOrders":1,"BuyBackMarketMaker":0},{"Size":2,"Price":1981.3,"TotalOrders":1,"BuyBackMarketMaker":0}],"Touchline":{"BidInfo":{"Size":1,"Price":1980.15,"TotalOrders":1,"BuyBackMarketMaker":0},"AskInfo":{"Size":5,"Price":1980.65,"TotalOrders":4,"BuyBackMarketMaker":0},"LastTradedPrice":1980.15,"LastTradedQunatity":10,"TotalBuyQuantity":109663,"TotalSellQuantity":101522,"TotalTradedQuantity":287746,"AverageTradedPrice":1990.53,"LastTradedTime":1309963216,"LastUpdateTime":1309963220,"PercentChange":-0.3572776449867865,"Open":1997.65,"High":2006.6,"Low":1975,"Close":1987.25,"TotalValueTraded":null,"BuyBackTotalBuy":0,"BuyBackTotalSell":0},"BookType":1,"XMarketType":1,"SequenceNumber":476310350954633}

i did: res = json.loads(data)

which made the string like a dictionary.

"MessageCode":1502,
"MessageVersion":4,
"ApplicationType":0,
"TokenID":0,
"ExchangeSegment":1,
"ExchangeInstrumentID":22,
"ExchangeTimeStamp":1309954231,
"Bids":[{"Size":21,"Price":1999.15,"TotalOrders":2,"BuyBackMarketMaker":0},
{"Size":20,"Price":1999.1,"TotalOrders":1,"BuyBackMarketMaker":0},
{"Size":40,"Price":1999.05,"TotalOrders":1,"BuyBackMarketMaker":0},
{"Size":185,"Price":1999,"TotalOrders":6,"BuyBackMarketMaker":0},
{"Size":7,"Price":1998.8,"TotalOrders":2,"BuyBackMarketMaker":0}],
"Asks":[{"Size":1,"Price":1999.8,"TotalOrders":1,"BuyBackMarketMaker":0},
{"Size":3,"Price":1999.85,"TotalOrders":1,"BuyBackMarketMaker":0},
{"Size":34,"Price":1999.9,"TotalOrders":2,"BuyBackMarketMaker":0},
{"Size":199,"Price":2000,"TotalOrders":10,"BuyBackMarketMaker":0},
{"Size":1,"Price":2000.05,"TotalOrders":1,"BuyBackMarketMaker":0}],
"Touchline":{"BidInfo":{"Size":21,"Price":1999.15,"TotalOrders":2,"BuyBackMarketMaker":0},
"AskInfo":{"Size":1,"Price":1999.8,"TotalOrders":1,"BuyBackMarketMaker":0},
"LastTradedPrice":1999.9,
"LastTradedQunatity":12,
"TotalBuyQuantity":145954,
"TotalSellQuantity":81287,
"TotalTradedQuantity":114118,
"AverageTradedPrice":1999.65,
"LastTradedTime":1309954224,
"LastUpdateTime":1309954231,
"PercentChange":0.6365580576173091,
"Open":1997.65,
"High":2006.6,
"Low":1989.1,
"Close":1987.25,
"TotalValueTraded":null,
"BuyBackTotalBuy":0,
"BuyBackTotalSell":0},
"BookType":1,
"XMarketType":1,
"SequenceNumber":476310325663841}

then i did

df = pd.DataFrame(res)

which gave an error:

ValueError: Mixing Dict with non-Series may lead to ambiguous ordering

I further tried this:

df = pd.concat([pd.DataFrame(res['Bids']), 
             pd.DataFrame(res['Asks'])], axis=1, keys=('Bids','Asks'))
df.columns = df.columns.map('_'.join)

but this works for two keys only. I want to convert every key to df. Any optimize code will help.

any way to expand Bids, Asks into separate columns? The entire 1st key-value pair is printed "{"Size":21,"Price":1999.15,"TotalOrders":2,"BuyBackMarketMaker":0}" — Deep Vora
– Deep Vora, Commented Jul 7, 2021 at 5:09

jezrael · Accepted Answer · 2021-07-07 05:37:00Z

2

Idea is convert separately by keys and join together by MessageCode:

df1 = pd.json_normalize(d, 'Bids').add_prefix('bid')
df2 = pd.json_normalize(d, 'Asks','MessageCode').add_prefix('ask')
df3 = pd.json_normalize(d).drop(['Bids','Asks'], axis=1)

df = (pd.concat([df1, df2], axis=1)
        .rename(columns={'askMessageCode':'MessageCode'})
        .merge(df3, on='MessageCode', how='left'))
print (df)

answered Jul 7, 2021 at 5:37

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

1 Comment

Deep Vora Over a year ago

can these be converted into one row?? i.e. 30 columns only stackoverflow.com/questions/68302979/…

Collectives™ on Stack Overflow

Converting string to dictionary to dataframe

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related