First, i'm new to coding. I've only been doing this for a couple of months. However, I have been stuck on this for a few days. I am trying to add information from a nested json to a pandas DataFrame, but I get this as a result.
Bid Strike_Price Symbol Exp_Date
0 17.38 469.0 SPY_033122C469 1648756800000
Bid Strike_Price Symbol Exp_Date
0 16.72 470.0 SPY_033122C470 1648756800000
Bid Strike_Price Symbol Exp_Date
0 16.07 471.0 SPY_033122C471 1648756800000
Bid Strike_Price Symbol Exp_Date
0 15.43 472.0 SPY_033122C472 1648756800000
Bid Strike_Price Symbol Exp_Date
0 14.8 473.0 SPY_033122C473 1648756800000
Bid Strike_Price Symbol Exp_Date
0 19.02 469.0 SPY_041422C469 1649966400000
Bid Strike_Price Symbol Exp_Date
0 18.37 470.0 SPY_041422C470 1649966400000
Bid Strike_Price Symbol Exp_Date
0 17.72 471.0 SPY_041422C471 1649966400000
Bid Strike_Price Symbol Exp_Date
0 17.08 472.0 SPY_041422C472 1649966400000
Bid Strike_Price Symbol Exp_Date
0 16.46 473.0 SPY_041422C473 1649966400000
Here is an example of the nested JSON.
{
"symbol": "SPY",
"status": "SUCCESS",
"underlying": {
"symbol": "SPY",
"description": "SPDR S&P 500",
"change": 2.91,
"percentChange": 0.62,
"close": 467.69,
"quoteTime": 1640307600976,
"tradeTime": 1640307600001,
"bid": 470.52,
"ask": 470.6,
"last": 470.6,
"mark": 470.6,
"markChange": 2.91,
"markPercentChange": 0.62,
"bidSize": 300,
"askSize": 700,
"highPrice": 472.19,
"lowPrice": 468.64,
"openPrice": 468.75,
"totalVolume": 56439745,
"exchangeName": "PAC",
"fiftyTwoWeekHigh": 473.54,
"fiftyTwoWeekLow": 364.82,
"delayed": true
},
"strategy": "SINGLE",
"interval": 0.0,
"isDelayed": true,
"isIndex": false,
"interestRate": 0.1,
"underlyingPrice": 470.56,
"volatility": 29.0,
"daysToExpiration": 0.0,
"numberOfContracts": 10,
"putExpDateMap": {},
"callExpDateMap": {
"2022-03-31:97": {
"469.0": [
{
"putCall": "CALL",
"symbol": "SPY_033122C469",
"description": "SPY Mar 31 2022 469 Call (Quarterly)",
"exchangeName": "OPR",
"bid": 17.38,
"ask": 17.48,
"last": 17.6,
"mark": 17.43,
"bidSize": 17,
"askSize": 20,
"bidAskSize": "17X20",
"lastSize": 0,
"highPrice": 17.71,
"lowPrice": 16.93,
"openPrice": 0.0,
"closePrice": 15.99,
"totalVolume": 52,
"tradeDate": null,
"tradeTimeInLong": 1640282675820,
"quoteTimeInLong": 1640294094808,
"netChange": 1.61,
"volatility": 17.334,
"delta": 0.528,
"gamma": 0.009,
"theta": -0.083,
"vega": 0.972,
"rho": 0.596,
"openInterest": 474,
"timeValue": 16.0,
"theoreticalOptionValue": 17.436,
"theoreticalVolatility": 29.0,
"optionDeliverablesList": null,
"strikePrice": 469.0,
"expirationDate": 1648756800000,
"daysToExpiration": 97,
"expirationType": "Q",
"lastTradingDay": 1648771200000,
"multiplier": 100.0,
"settlementType": " ",
"deliverableNote": "",
"isIndexOption": null,
"percentChange": 10.07,
"markChange": 1.44,
"markPercentChange": 9.04,
"intrinsicValue": 1.6,
"nonStandard": false,
"mini": false,
"inTheMoney": true,
"pennyPilot": true
}
],
"470.0": [
{
"putCall": "CALL",
"symbol": "SPY_033122C470",
"description": "SPY Mar 31 2022 470 Call (Quarterly)",
"exchangeName": "OPR",
"bid": 16.72,
"ask": 16.82,
"last": 17.0,
"mark": 16.77,
"bidSize": 17,
"askSize": 20,
"bidAskSize": "17X20",
"lastSize": 0,
"highPrice": 17.47,
"lowPrice": 16.43,
"openPrice": 0.0,
"closePrice": 15.35,
"totalVolume": 124,
"tradeDate": null,
"tradeTimeInLong": 1640293092011,
"quoteTimeInLong": 1640294098603,
"netChange": 1.65,
"volatility": 17.162,
"delta": 0.519,
"gamma": 0.01,
"theta": -0.082,
"vega": 0.974,
"rho": 0.588,
"openInterest": 1260,
"timeValue": 16.4,
"theoreticalOptionValue": 16.78,
"theoreticalVolatility": 29.0,
"optionDeliverablesList": null,
"strikePrice": 470.0,
"expirationDate": 1648756800000,
"daysToExpiration": 97,
"expirationType": "Q",
"lastTradingDay": 1648771200000,
"multiplier": 100.0,
"settlementType": " ",
"deliverableNote": "",
"isIndexOption": null,
"percentChange": 10.75,
"markChange": 1.42,
"markPercentChange": 9.25,
"intrinsicValue": 0.6,
"nonStandard": false,
"mini": false,
"inTheMoney": true,
"pennyPilot": true
}
],
"471.0": [
{
"putCall": "CALL",
"symbol": "SPY_033122C471",
"description": "SPY Mar 31 2022 471 Call (Quarterly)",
"exchangeName": "OPR",
"bid": 16.07,
"ask": 16.17,
"last": 16.75,
"mark": 16.12,
"bidSize": 17,
"askSize": 20,
"bidAskSize": "17X20",
"lastSize": 0,
"highPrice": 16.75,
"lowPrice": 15.8,
"openPrice": 0.0,
"closePrice": 14.73,
"totalVolume": 22,
"tradeDate": null,
"tradeTimeInLong": 1640292441039,
"quoteTimeInLong": 1640294094808,
"netChange": 2.03,
"volatility": 16.976,
"delta": 0.509,
"gamma": 0.01,
"theta": -0.081,
"vega": 0.974,
"rho": 0.579,
"openInterest": 314,
"timeValue": 16.75,
"theoreticalOptionValue": 16.12,
"theoreticalVolatility": 29.0,
"optionDeliverablesList": null,
"strikePrice": 471.0,
"expirationDate": 1648756800000,
"daysToExpiration": 97,
"expirationType": "Q",
"lastTradingDay": 1648771200000,
"multiplier": 100.0,
"settlementType": " ",
"deliverableNote": "",
"isIndexOption": null,
"percentChange": 13.75,
"markChange": 1.39,
"markPercentChange": 9.47,
"intrinsicValue": -0.4,
"nonStandard": false,
"mini": false,
"inTheMoney": false,
"pennyPilot": true
}
],
"472.0": [
{
"putCall": "CALL",
"symbol": "SPY_033122C472",
"description": "SPY Mar 31 2022 472 Call (Quarterly)",
"exchangeName": "OPR",
"bid": 15.43,
"ask": 15.53,
"last": 15.49,
"mark": 15.48,
"bidSize": 17,
"askSize": 20,
"bidAskSize": "17X20",
"lastSize": 0,
"highPrice": 15.97,
"lowPrice": 15.12,
"openPrice": 0.0,
"closePrice": 14.12,
"totalVolume": 18,
"tradeDate": null,
"tradeTimeInLong": 1640293822217,
"quoteTimeInLong": 1640294098608,
"netChange": 1.37,
"volatility": 16.801,
"delta": 0.499,
"gamma": 0.01,
"theta": -0.081,
"vega": 0.975,
"rho": 0.569,
"openInterest": 295,
"timeValue": 15.49,
"theoreticalOptionValue": 15.48,
"theoreticalVolatility": 29.0,
"optionDeliverablesList": null,
"strikePrice": 472.0,
"expirationDate": 1648756800000,
"daysToExpiration": 97,
"expirationType": "Q",
"lastTradingDay": 1648771200000,
"multiplier": 100.0,
"settlementType": " ",
"deliverableNote": "",
"isIndexOption": null,
"percentChange": 9.7,
"markChange": 1.36,
"markPercentChange": 9.63,
"intrinsicValue": -1.4,
"nonStandard": false,
"mini": false,
"inTheMoney": false,
"pennyPilot": true
}
],
"473.0": [
{
"putCall": "CALL",
"symbol": "SPY_033122C473",
"description": "SPY Mar 31 2022 473 Call (Quarterly)",
"exchangeName": "OPR",
"bid": 14.8,
"ask": 14.9,
"last": 15.12,
"mark": 14.85,
"bidSize": 17,
"askSize": 20,
"bidAskSize": "17X20",
"lastSize": 0,
"highPrice": 15.12,
"lowPrice": 14.87,
"openPrice": 0.0,
"closePrice": 13.52,
"totalVolume": 23,
"tradeDate": null,
"tradeTimeInLong": 1640285417120,
"quoteTimeInLong": 1640294098608,
"netChange": 1.6,
"volatility": 16.626,
"delta": 0.489,
"gamma": 0.01,
"theta": -0.08,
"vega": 0.974,
"rho": 0.56,
"openInterest": 645,
"timeValue": 15.12,
"theoreticalOptionValue": 14.85,
"theoreticalVolatility": 29.0,
"optionDeliverablesList": null,
"strikePrice": 473.0,
"expirationDate": 1648756800000,
"daysToExpiration": 97,
"expirationType": "Q",
"lastTradingDay": 1648771200000,
"multiplier": 100.0,
"settlementType": " ",
"deliverableNote": "",
"isIndexOption": null,
"percentChange": 11.83,
"markChange": 1.33,
"markPercentChange": 9.84,
"intrinsicValue": -2.4,
"nonStandard": false,
"mini": false,
"inTheMoney": false,
"pennyPilot": true
}
]
},
"2022-04-14:111": {
"469.0": [
{
"putCall": "CALL",
"symbol": "SPY_041422C469",
"description": "SPY Apr 14 2022 469 Call",
"exchangeName": "OPR",
"bid": 19.02,
"ask": 19.12,
"last": 19.16,
"mark": 19.11,
"bidSize": 20,
"askSize": 20,
"bidAskSize": "20X20",
"lastSize": 0,
"highPrice": 19.89,
"lowPrice": 18.77,
"openPrice": 0.0,
"closePrice": 17.61,
"totalVolume": 522,
"tradeDate": null,
"tradeTimeInLong": 1640293703687,
"quoteTimeInLong": 1640294095144,
"netChange": 1.54,
"volatility": 17.785,
"delta": 0.53,
"gamma": 0.009,
"theta": -0.08,
"vega": 1.039,
"rho": 0.679,
"openInterest": 1144,
"timeValue": 17.56,
"theoreticalOptionValue": 19.108,
"theoreticalVolatility": 29.0,
"optionDeliverablesList": null,
"strikePrice": 469.0,
"expirationDate": 1649966400000,
"daysToExpiration": 111,
"expirationType": "R",
"lastTradingDay": 1649980800000,
"multiplier": 100.0,
"settlementType": " ",
"deliverableNote": "",
"isIndexOption": null,
"percentChange": 8.77,
"markChange": 1.49,
"markPercentChange": 8.47,
"intrinsicValue": 1.6,
"nonStandard": false,
"mini": false,
"inTheMoney": true,
"pennyPilot": true
}
],
"470.0": [
{
"putCall": "CALL",
"symbol": "SPY_041422C470",
"description": "SPY Apr 14 2022 470 Call",
"exchangeName": "OPR",
"bid": 18.37,
"ask": 18.47,
"last": 18.72,
"mark": 18.4,
"bidSize": 17,
"askSize": 20,
"bidAskSize": "17X20",
"lastSize": 0,
"highPrice": 18.95,
"lowPrice": 18.1,
"openPrice": 0.0,
"closePrice": 17.0,
"totalVolume": 112,
"tradeDate": null,
"tradeTimeInLong": 1640292936466,
"quoteTimeInLong": 1640294098604,
"netChange": 1.73,
"volatility": 17.578,
"delta": 0.521,
"gamma": 0.009,
"theta": -0.079,
"vega": 1.04,
"rho": 0.67,
"openInterest": 949,
"timeValue": 18.12,
"theoreticalOptionValue": 18.406,
"theoreticalVolatility": 29.0,
"optionDeliverablesList": null,
"strikePrice": 470.0,
"expirationDate": 1649966400000,
"daysToExpiration": 111,
"expirationType": "R",
"lastTradingDay": 1649980800000,
"multiplier": 100.0,
"settlementType": " ",
"deliverableNote": "",
"isIndexOption": null,
"percentChange": 10.15,
"markChange": 1.4,
"markPercentChange": 8.25,
"intrinsicValue": 0.6,
"nonStandard": false,
"mini": false,
"inTheMoney": true,
"pennyPilot": true
}
],
"471.0": [
{
"putCall": "CALL",
"symbol": "SPY_041422C471",
"description": "SPY Apr 14 2022 471 Call",
"exchangeName": "OPR",
"bid": 17.72,
"ask": 17.82,
"last": 17.8,
"mark": 17.77,
"bidSize": 25,
"askSize": 20,
"bidAskSize": "25X20",
"lastSize": 0,
"highPrice": 18.42,
"lowPrice": 17.69,
"openPrice": 0.0,
"closePrice": 16.38,
"totalVolume": 101,
"tradeDate": null,
"tradeTimeInLong": 1640293185215,
"quoteTimeInLong": 1640294098603,
"netChange": 1.42,
"volatility": 17.423,
"delta": 0.512,
"gamma": 0.009,
"theta": -0.079,
"vega": 1.041,
"rho": 0.661,
"openInterest": 435,
"timeValue": 17.8,
"theoreticalOptionValue": 17.767,
"theoreticalVolatility": 29.0,
"optionDeliverablesList": null,
"strikePrice": 471.0,
"expirationDate": 1649966400000,
"daysToExpiration": 111,
"expirationType": "R",
"lastTradingDay": 1649980800000,
"multiplier": 100.0,
"settlementType": " ",
"deliverableNote": "",
"isIndexOption": null,
"percentChange": 8.7,
"markChange": 1.39,
"markPercentChange": 8.5,
"intrinsicValue": -0.4,
"nonStandard": false,
"mini": false,
"inTheMoney": false,
"pennyPilot": true
}
],
"472.0": [
{
"putCall": "CALL",
"symbol": "SPY_041422C472",
"description": "SPY Apr 14 2022 472 Call",
"exchangeName": "OPR",
"bid": 17.08,
"ask": 17.18,
"last": 17.81,
"mark": 17.14,
"bidSize": 17,
"askSize": 20,
"bidAskSize": "17X20",
"lastSize": 0,
"highPrice": 17.81,
"lowPrice": 16.22,
"openPrice": 0.0,
"closePrice": 15.76,
"totalVolume": 74,
"tradeDate": null,
"tradeTimeInLong": 1640292857129,
"quoteTimeInLong": 1640294094807,
"netChange": 2.06,
"volatility": 17.269,
"delta": 0.503,
"gamma": 0.009,
"theta": -0.078,
"vega": 1.042,
"rho": 0.651,
"openInterest": 170,
"timeValue": 17.81,
"theoreticalOptionValue": 17.136,
"theoreticalVolatility": 29.0,
"optionDeliverablesList": null,
"strikePrice": 472.0,
"expirationDate": 1649966400000,
"daysToExpiration": 111,
"expirationType": "R",
"lastTradingDay": 1649980800000,
"multiplier": 100.0,
"settlementType": " ",
"deliverableNote": "",
"isIndexOption": null,
"percentChange": 13.04,
"markChange": 1.38,
"markPercentChange": 8.77,
"intrinsicValue": -1.4,
"nonStandard": false,
"mini": false,
"inTheMoney": false,
"pennyPilot": true
}
],
"473.0": [
{
"putCall": "CALL",
"symbol": "SPY_041422C473",
"description": "SPY Apr 14 2022 473 Call",
"exchangeName": "OPR",
"bid": 16.46,
"ask": 16.55,
"last": 17.15,
"mark": 16.51,
"bidSize": 17,
"askSize": 20,
"bidAskSize": "17X20",
"lastSize": 0,
"highPrice": 17.15,
"lowPrice": 16.3,
"openPrice": 0.0,
"closePrice": 15.14,
"totalVolume": 663,
"tradeDate": null,
"tradeTimeInLong": 1640292600017,
"quoteTimeInLong": 1640294095144,
"netChange": 2.01,
"volatility": 17.106,
"delta": 0.494,
"gamma": 0.009,
"theta": -0.077,
"vega": 1.042,
"rho": 0.641,
"openInterest": 238,
"timeValue": 17.15,
"theoreticalOptionValue": 16.506,
"theoreticalVolatility": 29.0,
"optionDeliverablesList": null,
"strikePrice": 473.0,
"expirationDate": 1649966400000,
"daysToExpiration": 111,
"expirationType": "R",
"lastTradingDay": 1649980800000,
"multiplier": 100.0,
"settlementType": " ",
"deliverableNote": "",
"isIndexOption": null,
"percentChange": 13.28,
"markChange": 1.37,
"markPercentChange": 9.02,
"intrinsicValue": -2.4,
"nonStandard": false,
"mini": false,
"inTheMoney": false,
"pennyPilot": true
}
]
}
}
}
This is what I have tried.
for value in data['callExpDateMap']:#Gets expiration dates [callExpDateMap][Bid]
for k in data['callExpDateMap'][value]:#Allows me to iterate through nested objects without knowing them. [callExpDateMap][Bid]
for v in data['callExpDateMap'][value][k]:#Gets me to the symbol, bid and strike price, etc
df = pd.DataFrame(columns=["Bid","Strike_Price","Symbol","Exp_Date"]) #Setting up columns
df1 = (v['bid'])
df2 = (v['strikePrice'])
df3 = (v['symbol'])
df4 = (v['expirationDate'])
df = df.append({'Bid' : df1,#Appending variables to the columns
'Strike_Price' : df2,
'Symbol' : df3,
'Exp_Date' : df4,
}, ignore_index=True)
pd.set_option('colheader_justify', 'center') # Indents and makes the columns pretty
print(df)
The information that I want is the Bid, Strike Price, Symbol and Expiration Date from the 4th layer in the json. The Second and Third layer is dynamic as it will change. Then I need to be able to sort through them via columns until I get the variable I need. Can anyone help me fix this so that I get one dataframe. I'm a new to this. I can't really list what I have tried already, as i've tried many random things over the last few days. I'm just stuck! I apologize for the poor question. Thanks in advance for any help.
CodePudding user response：
A simpler solution would be to extract all the actual data objects (the ones with the bid property) and put them in list. The pd.DataFrame() function can take a list of objects (dicts) and make a nice dataframe out of it:
objs = []
for v1 in data['callExpDateMap'].values():
for v2 in v1.values():
objs.extend(v2)
df = pd.DataFrame(objs)
Output:
>>> df
putCall symbol description exchangeName bid ask last mark ... percentChange markChange markPercentChange intrinsicValue nonStandard mini inTheMoney pennyPilot
0 CALL SPY_033122C469 SPY Mar 31 2022 469 Call (Quarterly) OPR 17.38 17.48 17.6 17.43 ... 10.07 1.44 9.04 1.6 False False True True
1 CALL SPY_033122C470 SPY Mar 31 2022 470 Call (Quarterly) OPR 16.72 16.82 17.0 16.77 ... 10.75 1.42 9.25 0.6 False False True True
[2 rows x 49 columns]