Преобразование JSON в DataFrame
Как преобразовать json в DataFrame - pandas?
При запросе получаю json данные:
[{'ship_id': 'AMERICANCHAMPION',
'ship_name': 'American Champion',
'ship_model': None,
'ship_type': 'Tug',
'roles': ['Support Ship', 'Barge Tug'],
'active': False,
'imo': 7434016,
'mmsi': 367020820,
'abs': 571252,
'class': 7604342,
'weight_lbs': 588000,
'weight_kg': 266712,
'year_built': 1976,
'home_port': 'Port of Los Angeles',
'status': '',
'speed_kn': None,
'course_deg': None,
'position': {'latitude': None, 'longitude': None},
'successful_landings': None,
'attempted_landings': None,
'missions': [{'name': 'COTS 1', 'flight': 7},
{'name': 'COTS 2', 'flight': 8}],
'url': 'https://www.marinetraffic.com/en/ais/details/ships/shipid:434663/vessel:AMERICAN%20CHAMPION',
'image': 'https://i.imgur.com/woCxpkj.jpg'},
{'ship_id': 'AMERICANISLANDER',
'ship_name': 'American Islander',
'ship_model': None,
'ship_type': 'Cargo',
'roles': ['Dragon Recovery'],
'active': False,
'imo': None,
'mmsi': 367035570,
'abs': None,
'class': None,
'weight_lbs': None,
'weight_kg': None,
'year_built': None,
'home_port': 'Port of Los Angeles',
'status': '',
'speed_kn': None,
'course_deg': None,
'position': {'latitude': None, 'longitude': None},
'successful_landings': None,
'attempted_landings': None,
'missions': [{'name': 'CRS-1', 'flight': 9},
{'name': 'CRS-2', 'flight': 10},
{'name': 'CRS-3', 'flight': 14},
{'name': 'CRS-4', 'flight': 18}],
'url': 'https://www.marinetraffic.com/en/ais/details/ships/shipid:435112/vessel:AMERICAN%20ISLANDER',
'image': 'https://i.imgur.com/jmj8Sh2.jpg'}
Необходимо из этих данных вытащить 'missions'. На основании его данных создать DataFrame, который будет содержать столбцы Name и Flight.
Мой код
import requests
import pandas as pd
import numpy as np
from pandas import Series, DataFrame
import json
data = requests.get('https://api.spacexdata.com/v3/ships').json()
df_g = [ch.get('missions') for ch in data]
После генератора списков получаю:
[[{'name': 'COTS 1', 'flight': 7}, {'name': 'COTS 2', 'flight': 8}],
[{'name': 'CRS-1', 'flight': 9},
{'name': 'CRS-2', 'flight': 10},
{'name': 'CRS-3', 'flight': 14},
{'name': 'CRS-4', 'flight': 18}],
[{'name': 'CASSIOPE', 'flight': 11}],
[],
[{'name': 'FormoSat-5', 'flight': 46},
{'name': 'Iridium NEXT Mission 3', 'flight': 48}],
[{'name': 'CRS-5', 'flight': 19},
{'name': 'DSCOVR', 'flight': 20},
{'name': 'CRS-6', 'flight': 22},
{'name': 'CRS-7', 'flight': 24},
{'name': 'SES-9', 'flight': 27},
{'name': 'CRS-8', 'flight': 28},
{'name': 'JCSAT-2B', 'flight': 29},
{'name': 'Thaicom 8', 'flight': 30},
{'name': 'ABS-2A / Eutelsat 117W B', 'flight': 31},
{'name': 'CRS-9', 'flight': 32},
{'name': 'JCSAT-16', 'flight': 33},
{'name': 'SES-10', 'flight': 38},
{'name': 'BulgariaSat-1', 'flight': 42},
{'name': 'Boeing X-37B OTV-5', 'flight': 47}],
[{'name': 'JCSat 18 / Kacific 1', 'flight': 86},
{'name': 'Starlink 3', 'flight': 89},
{'name': 'Starlink 4', 'flight': 90}],
[{'name': 'KoreaSat 5A', 'flight': 50},
{'name': 'Paz / Starlink Demo', 'flight': 56},
{'name': 'Iridium NEXT Mission 5', 'flight': 58},
{'name': 'Iridium NEXT Mission 6', 'flight': 62},
{'name': 'Iridium NEXT Mission 7', 'flight': 66},
{'name': 'SSO-A', 'flight': 71},
{'name': 'STP-2', 'flight': 81},
{'name': 'Amos-17', 'flight': 83},
{'name': 'Starlink 1', 'flight': 84},
{'name': 'JCSat 18 / Kacific 1', 'flight': 86},
{'name': 'Starlink 2', 'flight': 87},
{'name': 'Starlink 3', 'flight': 89},
{'name': 'Starlink 4', 'flight': 90}],
[{'name': 'ArabSat 6A', 'flight': 77},
{'name': 'Starlink v0.9', 'flight': 79},
{'name': 'STP-2', 'flight': 81},
{'name': 'Amos-17', 'flight': 83},
{'name': 'Starlink 1', 'flight': 84},
{'name': 'Starlink 2', 'flight': 87}],
[{'name': 'TESS', 'flight': 60},
{'name': 'Bangabandhu-1', 'flight': 61},
{'name': 'SES-12', 'flight': 63},
{'name': 'Telstar 19V', 'flight': 65}],
[{'name': 'CRS-5', 'flight': 19},
{'name': 'DSCOVR', 'flight': 20},
{'name': 'CRS-6', 'flight': 22},
{'name': 'CRS-7', 'flight': 24},
{'name': 'SES-9', 'flight': 27},
{'name': 'CRS-8', 'flight': 28},
{'name': 'JCSAT-2B', 'flight': 29},
{'name': 'Thaicom 8', 'flight': 30},
{'name': 'ABS-2A / Eutelsat 117W B', 'flight': 31},
{'name': 'CRS-9', 'flight': 32},
{'name': 'JCSAT-16', 'flight': 33},
{'name': 'SES-10', 'flight': 38},
{'name': 'BulgariaSat-1', 'flight': 42},
{'name': 'Boeing X-37B OTV-5', 'flight': 47},
{'name': 'SES-11 / Echostar 105', 'flight': 49},
{'name': 'SES-16 / GovSat-1', 'flight': 54},
{'name': 'TESS', 'flight': 60},
{'name': 'Bangabandhu-1', 'flight': 61},
{'name': 'Telstar 19V', 'flight': 65},
{'name': 'Telstar 18V', 'flight': 68},
{'name': 'CRS-16', 'flight': 72},
{'name': 'ArabSat 6A', 'flight': 77},
{'name': 'CRS-17', 'flight': 78},
{'name': 'Starlink v0.9', 'flight': 79},
{'name': 'STP-2', 'flight': 81},
{'name': 'Starlink 2', 'flight': 87},
{'name': 'Starlink 3', 'flight': 89},
{'name': 'Starlink 4', 'flight': 90}],
[{'name': 'CRS-5', 'flight': 19},
{'name': 'DSCOVR', 'flight': 20},
{'name': 'CRS-6', 'flight': 22},
{'name': 'CRS-7', 'flight': 24},
{'name': 'SES-9', 'flight': 27},
{'name': 'CRS-8', 'flight': 28},
{'name': 'JCSAT-2B', 'flight': 29},
{'name': 'Thaicom 8', 'flight': 30},
{'name': 'ABS-2A / Eutelsat 117W B', 'flight': 31},
{'name': 'CRS-9', 'flight': 32},
{'name': 'JCSAT-16', 'flight': 33},
{'name': 'SES-10', 'flight': 38},
{'name': 'NROL-76', 'flight': 39},
{'name': 'BulgariaSat-1', 'flight': 42},
{'name': 'Falcon Heavy Test Flight', 'flight': 55},
{'name': 'ArabSat 6A', 'flight': 77},
{'name': 'Starlink v0.9', 'flight': 79},
{'name': 'Crew Dragon In Flight Abort Test', 'flight': 88}],
[{'name': 'SES-11 / Echostar 105', 'flight': 49},
{'name': 'KoreaSat 5A', 'flight': 50},
{'name': 'Falcon Heavy Test Flight', 'flight': 55},
{'name': 'TESS', 'flight': 60},
{'name': 'Telstar 19V', 'flight': 65},
{'name': 'Merah Putih', 'flight': 67},
{'name': 'Telstar 18V', 'flight': 68},
{'name': 'Es’hail 2', 'flight': 70},
{'name': 'Starlink 2', 'flight': 87},
{'name': 'Starlink 3', 'flight': 89},
{'name': 'Starlink 4', 'flight': 90}],
[{'name': 'ArabSat 6A', 'flight': 77},
{'name': 'CRS-17', 'flight': 78},
{'name': 'STP-2', 'flight': 81}],
[{'name': 'CRS-5', 'flight': 19}, {'name': 'CRS-6', 'flight': 22}],
[{'name': 'Jason 3', 'flight': 26},
{'name': 'Iridium NEXT Mission 1', 'flight': 35},
{'name': 'Iridium NEXT Mission 2', 'flight': 43},
{'name': 'FormoSat-5', 'flight': 46},
{'name': 'Iridium NEXT Mission 3', 'flight': 48},
{'name': 'Iridium NEXT Mission 7', 'flight': 66},
{'name': 'SSO-A', 'flight': 71},
{'name': 'Iridium NEXT Mission 8', 'flight': 74}],
[{'name': 'Iridium NEXT Mission 2', 'flight': 43}],
[{'name': 'CRS-5', 'flight': 19},
{'name': 'CRS-6', 'flight': 22},
{'name': 'Jason 3', 'flight': 26},
{'name': 'CRS-8', 'flight': 28},
{'name': 'CRS-9', 'flight': 32},
{'name': 'Iridium NEXT Mission 1', 'flight': 35},
{'name': 'CRS-10', 'flight': 36},
{'name': 'CRS-11', 'flight': 41},
{'name': 'Iridium NEXT Mission 2', 'flight': 43},
{'name': 'CRS-12', 'flight': 45},
{'name': 'CRS-13', 'flight': 51},
{'name': 'CRS-14', 'flight': 59},
{'name': 'CRS-15', 'flight': 64},
{'name': 'Iridium NEXT Mission 7', 'flight': 66},
{'name': 'SSO-A', 'flight': 71},
{'name': 'Iridium NEXT Mission 8', 'flight': 74}],
[{'name': 'SES-9', 'flight': 27},
{'name': 'CRS-8', 'flight': 28},
{'name': 'Thaicom 8', 'flight': 30},
{'name': 'ABS-2A / Eutelsat 117W B', 'flight': 31},
{'name': 'JCSAT-16', 'flight': 33},
{'name': 'SES-10', 'flight': 38},
{'name': 'BulgariaSat-1', 'flight': 42},
{'name': 'SES-11 / Echostar 105', 'flight': 49},
{'name': 'KoreaSat 5A', 'flight': 50},
{'name': 'Falcon Heavy Test Flight', 'flight': 55},
{'name': 'TESS', 'flight': 60},
{'name': 'Bangabandhu-1', 'flight': 61},
{'name': 'Telstar 19V', 'flight': 65},
{'name': 'Merah Putih', 'flight': 67},
{'name': 'Telstar 18V', 'flight': 68},
{'name': 'Es’hail 2', 'flight': 70},
{'name': 'Nusantara Satu (PSN-6) / S5 / Beresheet', 'flight': 75},
{'name': 'CCtCap Demo Mission 1', 'flight': 76},
{'name': 'ArabSat 6A', 'flight': 77},
{'name': 'CRS-17', 'flight': 78},
{'name': 'Starlink v0.9', 'flight': 79},
{'name': 'STP-2', 'flight': 81},
{'name': 'Starlink 1', 'flight': 84},
{'name': 'JCSat 18 / Kacific 1', 'flight': 86},
{'name': 'Starlink 2', 'flight': 87},
{'name': 'Starlink 3', 'flight': 89},
{'name': 'Starlink 4', 'flight': 90},
{'name': 'Starlink 5', 'flight': 92}],
[{'name': 'Jason 3', 'flight': 26},
{'name': 'Iridium NEXT Mission 7', 'flight': 66},
{'name': 'SSO-A', 'flight': 71},
{'name': 'Iridium NEXT Mission 8', 'flight': 74}],
[{'name': 'Iridium NEXT Mission 1', 'flight': 35}],
[{'name': 'Bangabandhu-1', 'flight': 61}]]
Далее ловлю ступор и не понимаю, как из этих данных создать DataFrame, который будет содержать столбцы: NAME и FLIGHT. Желательно при помощи pandas, если это возможно.
Дополнение: Даже при формировании запроса через pandas:
df1 = pd.read_json('https://api.spacexdata.com/v3/ships', orient='columns')
Получаю столбец с данными, которые содержит списки словарей:
df1['missions']
0 [{'name': 'COTS 1', 'flight': 7}, {'name': 'CO...
1 [{'name': 'CRS-1', 'flight': 9}, {'name': 'CRS...
2 [{'name': 'CASSIOPE', 'flight': 11}]
3 []
4 [{'name': 'FormoSat-5', 'flight': 46}, {'name'...
5 [{'name': 'CRS-5', 'flight': 19}, {'name': 'DS...
6 [{'name': 'JCSat 18 / Kacific 1', 'flight': 86...
7 [{'name': 'KoreaSat 5A', 'flight': 50}, {'name...
8 [{'name': 'ArabSat 6A', 'flight': 77}, {'name'...
9 [{'name': 'TESS', 'flight': 60}, {'name': 'Ban...
10 [{'name': 'CRS-5', 'flight': 19}, {'name': 'DS...
11 [{'name': 'CRS-5', 'flight': 19}, {'name': 'DS...
12 [{'name': 'SES-11 / Echostar 105', 'flight': 4...
13 [{'name': 'ArabSat 6A', 'flight': 77}, {'name'...
14 [{'name': 'CRS-5', 'flight': 19}, {'name': 'CR...
15 [{'name': 'Jason 3', 'flight': 26}, {'name': '...
16 [{'name': 'Iridium NEXT Mission 2', 'flight': ...
17 [{'name': 'CRS-5', 'flight': 19}, {'name': 'CR...
18 [{'name': 'SES-9', 'flight': 27}, {'name': 'CR...
19 [{'name': 'Jason 3', 'flight': 26}, {'name': '...
20 [{'name': 'Iridium NEXT Mission 1', 'flight': ...
21 [{'name': 'Bangabandhu-1', 'flight': 61}]
Name: missions, dtype: object
Ответы (1 шт):
Автор решения: strawdog
→ Ссылка
берете свой df_g и пробуйте сделать следующее:
import numpy as np
df = pd.DataFrame.from_records(np.concatenate(df_g))
получите что-то вроде такого:
name flight
0 COTS 1 7
1 COTS 2 8
2 CRS-1 9
3 CRS-2 10
4 CRS-3 14
.. ... ...
165 Iridium NEXT Mission 7 66
166 SSO-A 71
167 Iridium NEXT Mission 8 74
168 Iridium NEXT Mission 1 35
169 Bangabandhu-1 61
[170 rows x 2 columns]