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
JavaScript
x
2
1
df = pd.read_json('2016-06-20-2359Z.json', lines=True),
2
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?
JavaScript
1
97
97
1
"src":1,
2
"feeds":[
3
{
4
"id":1,
5
"name":"ADSBexchange.com",
6
"polarPlot":false
7
}
8
],
9
"srcFeed":1,
10
"showSil":true,
11
"showFlg":true,
12
"showPic":true,
13
"flgH":20,
14
"flgW":85,
15
"acList":[
16
{
17
"Id":11281748,
18
"Rcvr":1,
19
"HasSig":false,
20
"Icao":"AC2554",
21
"Bad":false,
22
"Reg":"N882AS",
23
"FSeen":"/Date(1466467166951)/",
24
"TSecs":3,
25
"CMsgs":1,
26
"AltT":0,
27
"Tisb":false,
28
"TrkH":false,
29
"Type":"CRJ2",
30
"Mdl":"2001
31
BOMBARDIER INC
32
CL-600-2B19",
33
"Man":"Bombardier",
34
"CNum":"7503",
35
"Op":"EXPRESSJET AIRLINES INC - ATLANTA, GA",
36
"OpIcao":"ASQ",
37
"Sqk":"",
38
"VsiT":0,
39
"WTC":2,
40
"Species":1,
41
"Engines":"2",
42
"EngType":3,
43
"EngMount":1,
44
"Mil":false,
45
"Cou":"United States",
46
"HasPic":false,
47
"Interested":false,
48
"FlightsCount":0,
49
"Gnd":false,
50
"SpdTyp":0,
51
"CallSus":false,
52
"TT":"a",
53
"Trt":1,
54
"Year":"2001"
55
},
56
{
57
"Id":11402205,
58
"Rcvr":1,
59
"HasSig":true,
60
"Sig":110,
61
"Icao":"ADFBDD",
62
"Bad":false,
63
"FSeen":"/Date(1466391940977)/",
64
"TSecs":75229,
65
"CMsgs":35445,
66
"Alt":8025,
67
"GAlt":8025,
68
"AltT":0,
69
"Call":"TEST1234",
70
"Tisb":false,
71
"TrkH":false,
72
"Sqk":"0262",
73
"Help":false,
74
"VsiT":0,
75
"WTC":0,
76
"Species":0,
77
"EngType":0,
78
"EngMount":0,
79
"Mil":true,
80
"Cou":"United States",
81
"HasPic":false,
82
"Interested":false,
83
"FlightsCount":0,
84
"Gnd":true,
85
"SpdTyp":0,
86
"CallSus":false,
87
"TT":"a",
88
"Trt":1
89
}
90
],
91
"totalAc":4231,
92
"lastDv":"636019887431643594",
93
"shtTrlSec":61,
94
"stm":1466467170029
95
}
96
</pre>
97
Advertisement
Answer
If you already have your data in acList
column in a pandas DataFrame, simply do:
JavaScript
1
7
1
import pandas as pd
2
pd.io.json.json_normalize(df.acList[0])
3
4
Alt AltT Bad CMsgs CNum Call CallSus Cou EngMount EngType Sqk TSecs TT Tisb TrkH Trt Type VsiT WTC Year
5
0 NaN 0 False 1 7503 NaN False United States 1 3 3 a False False 1 CRJ2 0 2 2001
6
1 8025.0 0 False 35445 NaN TEST1234 False United States 0 0 0262 75229 a False False 1 NaN 0 0 NaN
7
Since pandas 1.0 the imports should be:
JavaScript
1
3
1
import pandas as pd
2
pd.json_normalize(df.acList[0])
3