How To Extract Json From Nested Column To Dataframe

I'm pulling stock data from TD Ameritrade API and I want to store it in a DataFrame. From the API I get a nested JSON object and when I put it in a data frame I get 4 columns: Ind

Solution 1:

You're using json_normalize 1 level too high. You're wanting to normalize/flatten out the data under data['candles']:

I'd also be careful about posting api keys.

import pandas as pd
import requests
from pandas.io.json import json_normalize

endpoint = r'https://api.tdameritrade.com/v1/marketdata/{}/pricehistory'.format('GOOG')

client_id = 'XXXXXXXXXXX'

payload = {'apikey':client_id,
       'periodType': 'day',
       'frequencyType': 'minute',
       'frequency' :'1',
       'period':'2',
       'endDate': '1556158524000',
       'startDate': '1554535854000',
       'needExtendedHoursData':'true'}

content = requests.get(url = endpoint, params = payload)

data = content.json()
df = json_normalize(data['candles'])

Output:

print(df)closedatetimehighlowopenvolume01267.0000  15560358600001267.8600  1267.0000  1267.8600    145011266.8500  15560359200001266.8500  1266.8500  1266.8500     10021266.5300  15560359800001266.7300  1266.2400  1266.6750    129031267.1613  15560360400001267.1613  1266.5400  1266.5500    119041267.4150  15560361000001267.4150  1266.8800  1266.8800    110051267.4299  15560361600001267.4299  1267.4299  1267.4299     25061267.4540  15560362200001268.1800  1267.4540  1267.8100    165071267.0800  15560362800001267.5100  1267.0800  1267.4900     90081265.6850  15560363400001267.1210  1265.5300  1267.1210    414891265.4600  15560364000001265.9600  1265.1703  1265.8300    2290101266.2774  15560364600001266.4800  1265.4050  1265.4050    3341111266.4684  15560365200001266.4684  1266.3247  1266.3247    1134121266.8550  15560365800001267.0500  1266.4600  1266.4600    1500131267.2550  15560366400001267.3500  1266.6401  1267.0393    1619141267.2400  15560367000001267.2450  1267.2400  1267.2450     230151266.8000  15560367600001267.4400  1266.8000  1267.4400     940161266.0992  15560368200001266.5270  1266.0992  1266.5270    1523171266.2599  15560368800001266.2700  1266.2599  1266.2700     600181265.8400  15560369400001266.2350  1265.6800  1265.8400    2165191265.5400  15560370000001265.8600  1265.5000  1265.5300    1400201265.9650  15560370600001265.9900  1265.1200  1265.4532    1550211265.6300  15560371200001265.7750  1265.4300  1265.5929    1580221265.4469  15560371800001265.5300  1265.1000  1265.5300    1071231265.6600  15560372400001265.7100  1265.6313  1265.7100     650241266.1850  15560373000001266.1950  1265.6257  1265.6257     930251266.1400  15560373600001266.2500  1265.9400  1266.1300    1050261266.4250  15560374200001266.5750  1266.3000  1266.3294    1130271266.4800  15560374800001266.6500  1266.3500  1266.6500     900281266.7400  15560375400001266.8300  1266.5700  1266.7100    1103291266.8450  15560376000001266.8600  1266.8100  1266.8600     600....................5851256.0000  15561360000001256.0000  1256.0000  1256.0000  2116255861258.0000  15561363600001258.0000  1256.0000  1256.0000    11545871260.7100  15561364200001260.7100  1260.0000  1260.0000     5505881262.9500  15561365400001262.9500  1262.9500  1262.9500     1005891265.2600  15561366000001265.2600  1262.9500  1262.9500    21035901264.5000  15561366600001264.5000  1263.9700  1263.9700     4865911264.0000  15561368400001264.0000  1264.0000  1264.0000     1005921265.6100  15561369000001265.6100  1265.5000  1265.5000     3005931264.0600  15561369600001264.0600  1264.0600  1264.0600     1005941265.1800  15561370200001265.1800  1265.1800  1265.1800     1005951264.0000  15561371400001264.0000  1264.0000  1264.0000     1925961264.9000  15561373200001265.1400  1264.9000  1264.9000     5375971264.6500  15561376200001264.6500  1264.6500  1264.6500     5005981264.7500  15561376800001264.7500  1264.7500  1264.7500     2435991266.4900  15561377400001266.4900  1266.4900  1266.4900     1246001268.0000  15561385800001268.0000  1268.0000  1268.0000     1006011267.2900  15561387000001267.2900  1267.2900  1267.2900     1006021268.9800  15561388200001268.9800  1268.9800  1268.9800     1006031269.0700  15561392400001269.1200  1269.0700  1269.1200     2006041256.0000  15561394200001256.0000  1256.0000  1256.0000     1186051269.0900  15561394800001269.0900  1269.0900  1269.0900     1006061270.0000  15561395400001270.0000  1270.0000  1270.0000     2006071267.3800  15561410400001267.3800  1267.3800  1267.3800     1006081268.0000  15561411000001268.0000  1268.0000  1268.0000     1506091268.6600  15561419400001268.6600  1268.6600  1268.6600     1006101265.0000  15561436200001265.0000  1265.0000  1265.0000     2006111265.0000  15561437400001265.0000  1265.0000  1265.0000     1006121256.0000  15561466200001256.0000  1256.0000  1256.0000     1366131264.6100  15561469200001264.6100  1264.6100  1264.6100     1006141266.0000  15561472200001266.0000  1265.8700  1265.8700     232

[615rowsx6columns]