How to convert JSON data inside a pandas column into new columns

Question

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 } 

Answer 1

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])