将德美利证券的JSON API输出转换为Pandas数据帧

Question

Python初学者。最近连接到我的TD Ameritrade API，并试图提取我账户的头寸信息以进行进一步分析。然而，我在将API的JSON输出转换为Pandas数据帧时遇到了问题，任何指导都将不胜感激。

这是代码：

import pprint
import pandas as pd
from config import consumer_key, redirect_uri, credentials_path
from td.client import TDClient
# Create a new instance of the client
td_client = TDClient(client_id = consumer_key, redirect_uri = redirect_uri, credentials_path = credentials_path)
# Login to a new session
td_client.login()
# Positions and Orders for an account or account(s)
positions = td_client.get_accounts(account = 'all', fields = ['positions'])
pprint.pprint(positions)

以下是输出的片段：

{'securitiesAccount': {'accountId': 'xxxx',
'currentBalances': {'accruedInterest': 0.0,
'bondValue': 0.0,
'cashAvailableForTrading': xxx,
'cashAvailableForWithdrawal': xxx,
'cashBalance': 0.0,
'cashCall': 0.0,
'cashDebitCallValue': 0.0,
'cashReceipts': 0.0,
'liquidationValue': xxx,
'longMarketValue': xxx,
'longNonMarginableMarketValue': xxx,
'longOptionMarketValue': 0.0,
'moneyMarketFund': xxx,
'mutualFundValue': 0.0,
'pendingDeposits': 0.0,
'savings': 0.0,
'shortMarketValue': 0.0,
'shortOptionMarketValue': 0.0,
'totalCash': 0.0,
'unsettledCash': 0.0},
'initialBalances': {'accountValue': xxx,
'accruedInterest': 0.0,
'bondValue': 0.0,
'cashAvailableForTrading': xxx,
'cashAvailableForWithdrawal': xxx,
'cashBalance': 0.0,
'cashDebitCallValue': 0.0,
'cashReceipts': 0.0,
'isInCall': False,
'liquidationValue': xxx,
'longOptionMarketValue': 0.0,
'longStockValue': xxx,
'moneyMarketFund': xxx,
'mutualFundValue': 0.0,
'pendingDeposits': 0.0,
'shortOptionMarketValue': 0.0,
'shortStockValue': 0.0,
'unsettledCash': 0.0},
'isClosingOnlyRestricted': False,
'isDayTrader': False,
'positions': [
{'averagePrice': 46.14,
'currentDayProfitLoss': -8.4,
'currentDayProfitLossPercentage': -1.92,
'instrument': {'assetType': 'EQUITY',
'cusip': '02209S103',
'symbol': 'MO'},
'longQuantity': 10.0,
'maintenanceRequirement': 0.0,
'marketValue': 429.4,
'settledLongQuantity': 10.0,
'settledShortQuantity': 0.0,
'shortQuantity': 0.0},
{'averagePrice': 152.14,
'currentDayProfitLoss': -1.52,
'currentDayProfitLossPercentage': -0.43,
'instrument': {'assetType': 'EQUITY',
'cusip': '88579Y101',
'symbol': 'MMM'},
'longQuantity': 2.0,
'maintenanceRequirement': 0.0,
'marketValue': 351.32,
'settledLongQuantity': 2.0,
'settledShortQuantity': 0.0,
'shortQuantity': 0.0},
],
'projectedBalances': {'cashAvailableForTrading': xxx,
'cashAvailableForWithdrawal': xxx},
'roundTrips': 0,
'type': 'CASH'}}

如何将其转换为只包含位置信息的数据帧？(例如，一个数据帧，我拥有的每一只股票都是一行(。

我尝试了以下代码，但它没有返回所需的结果：

df = pd.json_normalize(positions)
print(positions)

感谢您抽出时间！

Answer 1

将响应称为"positions"，然后查找该键可能会让人感到困惑，但仍然需要使用positions键的完整路径。

pd.json_normalize(positions['securitiesAccount']['positions'])

输出

averagePrice  currentDayProfitLoss  currentDayProfitLossPercentage  longQuantity  maintenanceRequirement  marketValue  settledLongQuantity  settledShortQuantity  shortQuantity instrument.assetType instrument.cusip instrument.symbol
0        46.140                -8.400                          -1.920        10.000                   0.000      429.400               10.000                 0.000          0.000               EQUITY        02209S103                MO
1       152.140                -1.520                          -0.430         2.000                   0.000      351.320                2.000                 0.000          0.000               EQUITY        88579Y101               MMM

Answer 2

您总是可以通过创建一个new_dict来隔离数据。

new_dict=(dictionary[‘securities Account’][‘positions’](