I need to extract the following info as a table from JSON string:
flightId lat lon
657853226 39.588 -123.6683
...
This is how I started to solve the task:
request=Request('...')
response_flights = urlopen(request)
fn = response_flights.read()
flights = json.loads(fn)
flights = pd.DataFrame(json_normalize(flights['flightPositions']))
Buthow can I save positions in a DataFrame along with flightId?
Let's say I have an input file input.json that looks like
{
"flightPositions": [
{
"flightId": 65782839,
"positions": [
{
"lon": -123.6683,
"lat": 39.588
},
{
"lon": -123.734,
"lat": 39.6446
}
]
}
]
}
I can then build up a pandas DataFrame from a list of Series objects, like so:
import json
import pandas
data = json.load(open('input.json', 'r'))
flightdata = []
for flight_position in data['flightPositions']:
flight_id = flight_position['flightId']
for position in flight_position['positions']:
position['flightId'] = flight_id
series = pandas.Series(position)
flightdata.append(series)
df = pandas.DataFrame(flightdata)
print(df)
This will give me:
flightId lat lon
0 65782839 39.5880 -123.6683
1 65782839 39.6446 -123.7340
