将数据帧列中的数据转换为自己的数据帧

2024-10-03 13:29:13 发布

您现在位置：Python中文网/ 问答频道 /正文
2659 3

我搔了好几个小时的头。我用API提取了一些数据，并用pandas将其转换为数据帧。我可以将我想要构造的列隔离到它自己的DF中，但我一直在研究如何实际执行它。我到处找，但没有这样的例子。如果有帮助的话，来自API的数据就是json

其中一列被称为xpdatemap，其中的数据是：

{'10.0': [{'putCall': 'CALL', 'symbol': 'MIK_021921C10', 'description': 'MIK Feb 19 2021 10 Call', 'exchangeName': 'OPR', 'bid': 5.5, 'ask': 6.6, 'last': 5.41, 'mark': 6.05, 'bidSize': 36, 'askSize': 36, 'bidAskSize': '36X36', 'lastSize': 0, 'highPrice': 0.0, 'lowPrice': 0.0, 'openPrice': 0.0, 'closePrice': 6.64, 'totalVolume': 0, 'tradeDate': None, 'tradeTimeInLong': 1612471445495, 'quoteTimeInLong': 1613077155694, 'netChange': -1.09, 'volatility': 271.984, 'delta': 0.901, 'gamma': 0.027, 'theta': -0.068, 'vega': 0.004, 'rho': 0.002, 'openInterest': 119, 'timeValue': -0.25, 'theoreticalOptionValue': 6.05, 'theoreticalVolatility': 29.0, 'optionDeliverablesList': None, 'strikePrice': 10.0, 'expirationDate': 1613768400000, 'daysToExpiration': 8, 'expirationType': 'R', 'lastTradingDay': 1613782800000, 'multiplier': 100.0, 'settlementType': ' ', 'deliverableNote': '', 'isIndexOption': None, 'percentChange': -16.42, 'markChange': -0.59, 'markPercentChange': -8.87, 'nonStandard': False, 'inTheMoney': True, 'mini': False}], '12.5': [{'putCall': 'CALL', 'symbol': 'MIK_021921C12.5', 'description': 'MIK Feb 19 2021 12.5 Call', 'exchangeName': 'OPR', 'bid': 1.7, 'ask': 3.4, 'last': 3.6, 'mark': 2.55, 'bidSize': 408, 'askSize': 36, 'bidAskSize': '408X36', 'lastSize': 0, 'highPrice': 4.2, 'lowPrice': 3.6, 'openPrice': 0.0, 'closePrice': 4.19, 'totalVolume': 25, 'tradeDate': None, 'tradeTimeInLong': 1613065348968, 'quoteTimeInLong': 1613077199219, 'netChange': -0.59, 'volatility': 81.53, 'delta': 0.97, 'gamma': 0.036, 'theta': -0.01, 'vega': 0.002, 'rho': 0.003, 'openInterest': 1138, 'timeValue': 0.44, 'theoreticalOptionValue': 3.2, 'theoreticalVolatility': 29.0, 'optionDeliverablesList': None, 'strikePrice': 12.5, 'expirationDate': 1613768400000, 'daysToExpiration': 8, 'expirationType': 'R', 'lastTradingDay': 1613782800000, 'multiplier': 100.0, 'settlementType': ' ', 'deliverableNote': '', 'isIndexOption': None, 'percentChange': -14.04, 'markChange': -1.64, 'markPercentChange': -39.11, 'nonStandard': False, 'inTheMoney': True, 'mini': False}], '15.0': [{'putCall': 'CALL', 'symbol': 'MIK_021921C15', 'description': 'MIK Feb 19 2021 15 Call', 'exchangeName': 'OPR', 'bid': 0.95, 'ask': 1.05, 'last': 1.0, 'mark': 1.0, 'bidSize': 13, 'askSize': 12, 'bidAskSize': '13X12', 'lastSize': 0, 'highPrice': 1.65, 'lowPrice': 0.97, 'openPrice': 0.0, 'closePrice': 1.79, 'totalVolume': 30, 'tradeDate': None, 'tradeTimeInLong': 1613076748160, 'quoteTimeInLong': 1613077199427, 'netChange': -0.79, 'volatility': 64.009, 'delta': 0.691, 'gamma': 0.229, 'theta': -0.033, 'vega': 0.009, 'rho': 0.002, 'openInterest': 3370, 'timeValue': 0.34, 'theoreticalOptionValue': 1.0, 'theoreticalVolatility': 29.0, 'optionDeliverablesList': None, 'strikePrice': 15.0, 'expirationDate': 1613768400000, 'daysToExpiration': 8, 'expirationType': 'R', 'lastTradingDay': 1613782800000, 'multiplier': 100.0, 'settlementType': ' ', 'deliverableNote': '', 'isIndexOption': None, 'percentChange': -44.07, 'markChange': -0.79, 'markPercentChange': -44.07, 'nonStandard': False, 'inTheMoney': True, 'mini': False}], '17.5': [{'putCall': 'CALL', 'symbol': 'MIK_021921C17.5', 'description': 'MIK Feb 19 2021 17.5 Call', 'exchangeName': 'OPR', 'bid': 0.15, 'ask': 0.25, 'last': 0.15, 'mark': 0.2, 'bidSize': 12, 'askSize': 42, 'bidAskSize': '12X42', 'lastSize': 0, 'highPrice': 0.45, 'lowPrice': 0.15, 'openPrice': 0.0, 'closePrice': 0.47, 'totalVolume': 37, 'tradeDate': None, 'tradeTimeInLong': 1613077039645, 'quoteTimeInLong': 1613077173597, 'netChange': -0.32, 'volatility': 78.904, 'delta': 0.199, 'gamma': 0.147, 'theta': -0.031, 'vega': 0.007, 'rho': 0.001, 'openInterest': 2088, 'timeValue': 0.15, 'theoreticalOptionValue': 0.2, 'theoreticalVolatility': 29.0, 'optionDeliverablesList': None, 'strikePrice': 17.5, 'expirationDate': 1613768400000, 'daysToExpiration': 8, 'expirationType': 'R', 'lastTradingDay': 1613782800000, 'multiplier': 100.0, 'settlementType': ' ', 'deliverableNote': '', 'isIndexOption': None, 'percentChange': -68.42, 'markChange': -0.27, 'markPercentChange': -57.89, 'nonStandard': False, 'inTheMoney': False, 'mini': False}], '20.0': [{'putCall': 'CALL', 'symbol': 'MIK_021921C20', 'description': 'MIK Feb 19 2021 20 Call', 'exchangeName': 'OPR', 'bid': 0.0, 'ask': 0.05, 'last': 0.05, 'mark': 0.03, 'bidSize': 0, 'askSize': 28, 'bidAskSize': '0X28', 'lastSize': 0, 'highPrice': 0.15, 'lowPrice': 0.05, 'openPrice': 0.0, 'closePrice': 0.13, 'totalVolume': 306, 'tradeDate': None, 'tradeTimeInLong': 1613076428675, 'quoteTimeInLong': 1613076591127, 'netChange': -0.08, 'volatility': 83.171, 'delta': 0.033, 'gamma': 0.037, 'theta': -0.009, 'vega': 0.002, 'rho': 0.0, 'openInterest': 3015, 'timeValue': 0.05, 'theoreticalOptionValue': 0.025, 'theoreticalVolatility': 29.0, 'optionDeliverablesList': None, 'strikePrice': 20.0, 'expirationDate': 1613768400000, 'daysToExpiration': 8, 'expirationType': 'R', 'lastTradingDay': 1613782800000, 'multiplier': 100.0, 'settlementType': ' ', 'deliverableNote': '', 'isIndexOption': None, 'percentChange': -60.0, 'markChange': -0.1, 'markPercentChange': -80.0, 'nonStandard': False, 'inTheMoney': False, 'mini': False}], '22.5': [{'putCall': 'CALL', 'symbol': 'MIK_021921C22.5', 'description': 'MIK Feb 19 2021 22.5 Call', 'exchangeName': 'OPR', 'bid': 0.0, 'ask': 0.05, 'last': 0.04, 'mark': 0.03, 'bidSize': 0, 'askSize': 1, 'bidAskSize': '0X1', 'lastSize': 0, 'highPrice': 0.05, 'lowPrice': 0.04, 'openPrice': 0.0, 'closePrice': 0.08, 'totalVolume': 31, 'tradeDate': None, 'tradeTimeInLong': 1613072074061, 'quoteTimeInLong': 1613076574089, 'netChange': -0.04, 'volatility': 114.755, 'delta': 0.025, 'gamma': 0.021, 'theta': -0.009, 'vega': 0.001, 'rho': 0.0, 'openInterest': 5484, 'timeValue': 0.04, 'theoreticalOptionValue': 0.025, 'theoreticalVolatility': 29.0, 'optionDeliverablesList': None, 'strikePrice': 22.5, 'expirationDate': 1613768400000, 'daysToExpiration': 8, 'expirationType': 'R', 'lastTradingDay': 1613782800000, 'multiplier': 100.0, 'settlementType': ' ', 'deliverableNote': '', 'isIndexOption': None, 'percentChange': -46.67, 'markChange': -0.05, 'markPercentChange': -66.67, 'nonStandard': False, 'inTheMoney': False, 'mini': False}], '25.0': [{'putCall': 'CALL', 'symbol': 'MIK_021921C25', 'description': 'MIK Feb 19 2021 25 Call', 'exchangeName': 'OPR', 'bid': 0.0, 'ask': 0.1, 'last': 0.05, 'mark': 0.05, 'bidSize': 0, 'askSize': 252, 'bidAskSize': '0X252', 'lastSize': 0, 'highPrice': 0.05, 'lowPrice': 0.05, 'openPrice': 0.0, 'closePrice': 0.05, 'totalVolume': 17, 'tradeDate': None, 'tradeTimeInLong': 1613056533967, 'quoteTimeInLong': 1613077199219, 'netChange': 0.0, 'volatility': 157.63, 'delta': 0.036, 'gamma': 0.021, 'theta': -0.017, 'vega': 0.002, 'rho': 0.0, 'openInterest': 985, 'timeValue': 0.05, 'theoreticalOptionValue': 0.05, 'theoreticalVolatility': 29.0, 'optionDeliverablesList': None, 'strikePrice': 25.0, 'expirationDate': 1613768400000, 'daysToExpiration': 8, 'expirationType': 'R', 'lastTradingDay': 1613782800000, 'multiplier': 100.0, 'settlementType': ' ', 'deliverableNote': '', 'isIndexOption': None, 'percentChange': 0.0, 'markChange': 0.0, 'markPercentChange': 0.0, 'nonStandard': False, 'inTheMoney': False, 'mini': False}]}`

我想要的是一个新的DF

    Price    putCall           symbol                descprition...
     10.0       CALL    MIK_021921C10    MIK Feb 19 2021 10 Call...

等等。我只是不知道如何正确地迭代这种格式


Tags： nonefalsedescriptioncallsymbolfebasklast
1条回答
网友
1楼 · 发布于 2024-10-03 13:29:13

尝试使用具有正确记录路径的pd.json_normalise()

  1. 您希望将每个“价格”循环作为路径，并为每个“价格”创建单独的数据帧
  2. 将它们追加到名为dfs的列表中
  3. 然后pd.concat创建一个数据帧
  4. 最后，在开始处插入prices列-
data = {'10.0': [{'putCall': 'CALL', 'symbol': 'MIK_021921C10', 'description': 'MIK Feb 19 2021 10 Call', 'exchangeName': 'OPR', 'bid': 5.5, 'ask': 6.6, 'last': 5.41, 'mark': 6.05, 'bidSize': 36, 'askSize': 36, 'bidAskSize': '36X36', 'lastSize': 0, 'highPrice': 0.0, 'lowPrice': 0.0, 'openPrice': 0.0, 'closePrice': 6.64, 'totalVolume': 0, 'tradeDate': None, 'tradeTimeInLong': 1612471445495, 'quoteTimeInLong': 1613077155694, 'netChange': -1.09, 'volatility': 271.984, 'delta': 0.901, 'gamma': 0.027, 'theta': -0.068, 'vega': 0.004, 'rho': 0.002, 'openInterest': 119, 'timeValue': -0.25, 'theoreticalOptionValue': 6.05, 'theoreticalVolatility': 29.0, 'optionDeliverablesList': None, 'strikePrice': 10.0, 'expirationDate': 1613768400000, 'daysToExpiration': 8, 'expirationType': 'R', 'lastTradingDay': 1613782800000, 'multiplier': 100.0, 'settlementType': ' ', 'deliverableNote': '', 'isIndexOption': None, 'percentChange': -16.42, 'markChange': -0.59, 'markPercentChange': -8.87, 'nonStandard': False, 'inTheMoney': True, 'mini': False}], '12.5': [{'putCall': 'CALL', 'symbol': 'MIK_021921C12.5', 'description': 'MIK Feb 19 2021 12.5 Call', 'exchangeName': 'OPR', 'bid': 1.7, 'ask': 3.4, 'last': 3.6, 'mark': 2.55, 'bidSize': 408, 'askSize': 36, 'bidAskSize': '408X36', 'lastSize': 0, 'highPrice': 4.2, 'lowPrice': 3.6, 'openPrice': 0.0, 'closePrice': 4.19, 'totalVolume': 25, 'tradeDate': None, 'tradeTimeInLong': 1613065348968, 'quoteTimeInLong': 1613077199219, 'netChange': -0.59, 'volatility': 81.53, 'delta': 0.97, 'gamma': 0.036, 'theta': -0.01, 'vega': 0.002, 'rho': 0.003, 'openInterest': 1138, 'timeValue': 0.44, 'theoreticalOptionValue': 3.2, 'theoreticalVolatility': 29.0, 'optionDeliverablesList': None, 'strikePrice': 12.5, 'expirationDate': 1613768400000, 'daysToExpiration': 8, 'expirationType': 'R', 'lastTradingDay': 1613782800000, 'multiplier': 100.0, 'settlementType': ' ', 'deliverableNote': '', 'isIndexOption': None, 'percentChange': -14.04, 'markChange': -1.64, 'markPercentChange': -39.11, 'nonStandard': False, 'inTheMoney': True, 'mini': False}], '15.0': [{'putCall': 'CALL', 'symbol': 'MIK_021921C15', 'description': 'MIK Feb 19 2021 15 Call', 'exchangeName': 'OPR', 'bid': 0.95, 'ask': 1.05, 'last': 1.0, 'mark': 1.0, 'bidSize': 13, 'askSize': 12, 'bidAskSize': '13X12', 'lastSize': 0, 'highPrice': 1.65, 'lowPrice': 0.97, 'openPrice': 0.0, 'closePrice': 1.79, 'totalVolume': 30, 'tradeDate': None, 'tradeTimeInLong': 1613076748160, 'quoteTimeInLong': 1613077199427, 'netChange': -0.79, 'volatility': 64.009, 'delta': 0.691, 'gamma': 0.229, 'theta': -0.033, 'vega': 0.009, 'rho': 0.002, 'openInterest': 3370, 'timeValue': 0.34, 'theoreticalOptionValue': 1.0, 'theoreticalVolatility': 29.0, 'optionDeliverablesList': None, 'strikePrice': 15.0, 'expirationDate': 1613768400000, 'daysToExpiration': 8, 'expirationType': 'R', 'lastTradingDay': 1613782800000, 'multiplier': 100.0, 'settlementType': ' ', 'deliverableNote': '', 'isIndexOption': None, 'percentChange': -44.07, 'markChange': -0.79, 'markPercentChange': -44.07, 'nonStandard': False, 'inTheMoney': True, 'mini': False}], '17.5': [{'putCall': 'CALL', 'symbol': 'MIK_021921C17.5', 'description': 'MIK Feb 19 2021 17.5 Call', 'exchangeName': 'OPR', 'bid': 0.15, 'ask': 0.25, 'last': 0.15, 'mark': 0.2, 'bidSize': 12, 'askSize': 42, 'bidAskSize': '12X42', 'lastSize': 0, 'highPrice': 0.45, 'lowPrice': 0.15, 'openPrice': 0.0, 'closePrice': 0.47, 'totalVolume': 37, 'tradeDate': None, 'tradeTimeInLong': 1613077039645, 'quoteTimeInLong': 1613077173597, 'netChange': -0.32, 'volatility': 78.904, 'delta': 0.199, 'gamma': 0.147, 'theta': -0.031, 'vega': 0.007, 'rho': 0.001, 'openInterest': 2088, 'timeValue': 0.15, 'theoreticalOptionValue': 0.2, 'theoreticalVolatility': 29.0, 'optionDeliverablesList': None, 'strikePrice': 17.5, 'expirationDate': 1613768400000, 'daysToExpiration': 8, 'expirationType': 'R', 'lastTradingDay': 1613782800000, 'multiplier': 100.0, 'settlementType': ' ', 'deliverableNote': '', 'isIndexOption': None, 'percentChange': -68.42, 'markChange': -0.27, 'markPercentChange': -57.89, 'nonStandard': False, 'inTheMoney': False, 'mini': False}], '20.0': [{'putCall': 'CALL', 'symbol': 'MIK_021921C20', 'description': 'MIK Feb 19 2021 20 Call', 'exchangeName': 'OPR', 'bid': 0.0, 'ask': 0.05, 'last': 0.05, 'mark': 0.03, 'bidSize': 0, 'askSize': 28, 'bidAskSize': '0X28', 'lastSize': 0, 'highPrice': 0.15, 'lowPrice': 0.05, 'openPrice': 0.0, 'closePrice': 0.13, 'totalVolume': 306, 'tradeDate': None, 'tradeTimeInLong': 1613076428675, 'quoteTimeInLong': 1613076591127, 'netChange': -0.08, 'volatility': 83.171, 'delta': 0.033, 'gamma': 0.037, 'theta': -0.009, 'vega': 0.002, 'rho': 0.0, 'openInterest': 3015, 'timeValue': 0.05, 'theoreticalOptionValue': 0.025, 'theoreticalVolatility': 29.0, 'optionDeliverablesList': None, 'strikePrice': 20.0, 'expirationDate': 1613768400000, 'daysToExpiration': 8, 'expirationType': 'R', 'lastTradingDay': 1613782800000, 'multiplier': 100.0, 'settlementType': ' ', 'deliverableNote': '', 'isIndexOption': None, 'percentChange': -60.0, 'markChange': -0.1, 'markPercentChange': -80.0, 'nonStandard': False, 'inTheMoney': False, 'mini': False}], '22.5': [{'putCall': 'CALL', 'symbol': 'MIK_021921C22.5', 'description': 'MIK Feb 19 2021 22.5 Call', 'exchangeName': 'OPR', 'bid': 0.0, 'ask': 0.05, 'last': 0.04, 'mark': 0.03, 'bidSize': 0, 'askSize': 1, 'bidAskSize': '0X1', 'lastSize': 0, 'highPrice': 0.05, 'lowPrice': 0.04, 'openPrice': 0.0, 'closePrice': 0.08, 'totalVolume': 31, 'tradeDate': None, 'tradeTimeInLong': 1613072074061, 'quoteTimeInLong': 1613076574089, 'netChange': -0.04, 'volatility': 114.755, 'delta': 0.025, 'gamma': 0.021, 'theta': -0.009, 'vega': 0.001, 'rho': 0.0, 'openInterest': 5484, 'timeValue': 0.04, 'theoreticalOptionValue': 0.025, 'theoreticalVolatility': 29.0, 'optionDeliverablesList': None, 'strikePrice': 22.5, 'expirationDate': 1613768400000, 'daysToExpiration': 8, 'expirationType': 'R', 'lastTradingDay': 1613782800000, 'multiplier': 100.0, 'settlementType': ' ', 'deliverableNote': '', 'isIndexOption': None, 'percentChange': -46.67, 'markChange': -0.05, 'markPercentChange': -66.67, 'nonStandard': False, 'inTheMoney': False, 'mini': False}], '25.0': [{'putCall': 'CALL', 'symbol': 'MIK_021921C25', 'description': 'MIK Feb 19 2021 25 Call', 'exchangeName': 'OPR', 'bid': 0.0, 'ask': 0.1, 'last': 0.05, 'mark': 0.05, 'bidSize': 0, 'askSize': 252, 'bidAskSize': '0X252', 'lastSize': 0, 'highPrice': 0.05, 'lowPrice': 0.05, 'openPrice': 0.0, 'closePrice': 0.05, 'totalVolume': 17, 'tradeDate': None, 'tradeTimeInLong': 1613056533967, 'quoteTimeInLong': 1613077199219, 'netChange': 0.0, 'volatility': 157.63, 'delta': 0.036, 'gamma': 0.021, 'theta': -0.017, 'vega': 0.002, 'rho': 0.0, 'openInterest': 985, 'timeValue': 0.05, 'theoreticalOptionValue': 0.05, 'theoreticalVolatility': 29.0, 'optionDeliverablesList': None, 'strikePrice': 25.0, 'expirationDate': 1613768400000, 'daysToExpiration': 8, 'expirationType': 'R', 'lastTradingDay': 1613782800000, 'multiplier': 100.0, 'settlementType': ' ', 'deliverableNote': '', 'isIndexOption': None, 'percentChange': 0.0, 'markChange': 0.0, 'markPercentChange': 0.0, 'nonStandard': False, 'inTheMoney': False, 'mini': False}]}

dfs = []

for i in data.keys():
    d = pd.json_normalize(data, record_path=i)
    dfs.append(d)

out = pd.concat(dfs)
out.insert(0, 'Price', data.keys())
out

enter image description here

相关问题 更多 >

    编程相关推荐