I have this short version of ADSB json data and would like to convert it into dataFrame columns as Icao, Alt, Lat, Long, Spd, Cou…..
After Alperen told me to do this
df = pd.read_json('2016-06-20-2359Z.json', lines=True),
I can load it into a DataFrame. However, df.acList
is
[{'Id': 10537990, 'Rcvr': 1, 'HasSig': False, ... Name: acList, dtype: object
How can I get the Icao, Alt, Lat, Long, Spd, Cou data?
"src":1, "feeds":[ { "id":1, "name":"ADSBexchange.com", "polarPlot":false } ], "srcFeed":1, "showSil":true, "showFlg":true, "showPic":true, "flgH":20, "flgW":85, "acList":[ { "Id":11281748, "Rcvr":1, "HasSig":false, "Icao":"AC2554", "Bad":false, "Reg":"N882AS", "FSeen":"/Date(1466467166951)/", "TSecs":3, "CMsgs":1, "AltT":0, "Tisb":false, "TrkH":false, "Type":"CRJ2", "Mdl":"2001 BOMBARDIER INC CL-600-2B19", "Man":"Bombardier", "CNum":"7503", "Op":"EXPRESSJET AIRLINES INC - ATLANTA, GA", "OpIcao":"ASQ", "Sqk":"", "VsiT":0, "WTC":2, "Species":1, "Engines":"2", "EngType":3, "EngMount":1, "Mil":false, "Cou":"United States", "HasPic":false, "Interested":false, "FlightsCount":0, "Gnd":false, "SpdTyp":0, "CallSus":false, "TT":"a", "Trt":1, "Year":"2001" }, { "Id":11402205, "Rcvr":1, "HasSig":true, "Sig":110, "Icao":"ADFBDD", "Bad":false, "FSeen":"/Date(1466391940977)/", "TSecs":75229, "CMsgs":35445, "Alt":8025, "GAlt":8025, "AltT":0, "Call":"TEST1234", "Tisb":false, "TrkH":false, "Sqk":"0262", "Help":false, "VsiT":0, "WTC":0, "Species":0, "EngType":0, "EngMount":0, "Mil":true, "Cou":"United States", "HasPic":false, "Interested":false, "FlightsCount":0, "Gnd":true, "SpdTyp":0, "CallSus":false, "TT":"a", "Trt":1 } ], "totalAc":4231, "lastDv":"636019887431643594", "shtTrlSec":61, "stm":1466467170029 } </pre>
Advertisement
Answer
If you already have your data in acList
column in a pandas DataFrame, simply do:
import pandas as pd pd.io.json.json_normalize(df.acList[0]) Alt AltT Bad CMsgs CNum Call CallSus Cou EngMount EngType ... Sqk TSecs TT Tisb TrkH Trt Type VsiT WTC Year 0 NaN 0 False 1 7503 NaN False United States 1 3 ... 3 a False False 1 CRJ2 0 2 2001 1 8025.0 0 False 35445 NaN TEST1234 False United States 0 0 ... 0262 75229 a False False 1 NaN 0 0 NaN
Since pandas 1.0 the imports should be:
import pandas as pd pd.json_normalize(df.acList[0])