{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Covering:\n",
"-----------------------\n",
"\n",
"* SELECT\n",
"* FROM\n",
"* WHERE field = value, field in (value1, value2)\n",
"* LIMIT\n",
"* GROUP BY\n",
"* COUNT, MAX, MIN, AVG\n",
"* ORDER BY field [DESC]\n",
"* HAVING\n",
"* JOIN, INNER JOIN, LEFT OUTER JOIN, RIGHT JOIN, FULL JOIN\n",
"* UNION\n",
"* UPDATE table SET field = value\n",
"* INSERT INTO table (field1, field2) VALUES (value1, value2)\n",
"* DELETE FROM table WHERE [condition]\n"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd\n",
"import numpy as np"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [],
"source": [
"airports = pd.read_csv('data/airports.csv')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Selecting data"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" ident | \n",
" type | \n",
" name | \n",
" latitude_deg | \n",
" longitude_deg | \n",
" elevation_ft | \n",
" continent | \n",
" iso_country | \n",
" iso_region | \n",
" municipality | \n",
" scheduled_service | \n",
" gps_code | \n",
" iata_code | \n",
" local_code | \n",
" home_link | \n",
" wikipedia_link | \n",
" keywords | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 6523 | \n",
" 00A | \n",
" heliport | \n",
" Total Rf Heliport | \n",
" 40.070801 | \n",
" -74.933601 | \n",
" 11.0 | \n",
" NaN | \n",
" US | \n",
" US-PA | \n",
" Bensalem | \n",
" no | \n",
" 00A | \n",
" NaN | \n",
" 00A | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 1 | \n",
" 323361 | \n",
" 00AA | \n",
" small_airport | \n",
" Aero B Ranch Airport | \n",
" 38.704022 | \n",
" -101.473911 | \n",
" 3435.0 | \n",
" NaN | \n",
" US | \n",
" US-KS | \n",
" Leoti | \n",
" no | \n",
" 00AA | \n",
" NaN | \n",
" 00AA | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 2 | \n",
" 6524 | \n",
" 00AK | \n",
" small_airport | \n",
" Lowell Field | \n",
" 59.949200 | \n",
" -151.695999 | \n",
" 450.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Anchor Point | \n",
" no | \n",
" 00AK | \n",
" NaN | \n",
" 00AK | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" id ident type name latitude_deg \\\n",
"0 6523 00A heliport Total Rf Heliport 40.070801 \n",
"1 323361 00AA small_airport Aero B Ranch Airport 38.704022 \n",
"2 6524 00AK small_airport Lowell Field 59.949200 \n",
"\n",
" longitude_deg elevation_ft continent iso_country iso_region municipality \\\n",
"0 -74.933601 11.0 NaN US US-PA Bensalem \n",
"1 -101.473911 3435.0 NaN US US-KS Leoti \n",
"2 -151.695999 450.0 NaN US US-AK Anchor Point \n",
"\n",
" scheduled_service gps_code iata_code local_code home_link wikipedia_link \\\n",
"0 no 00A NaN 00A NaN NaN \n",
"1 no 00AA NaN 00AA NaN NaN \n",
"2 no 00AK NaN 00AK NaN NaN \n",
"\n",
" keywords \n",
"0 NaN \n",
"1 NaN \n",
"2 NaN "
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# select * from airports limit 3\n",
"\n",
"airports.head(3)"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [],
"source": [
"airport_freq = pd.read_csv('data/airport-frequencies.csv')"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" airport_ref | \n",
" airport_ident | \n",
" type | \n",
" description | \n",
" frequency_mhz | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 70518 | \n",
" 6528 | \n",
" 00CA | \n",
" CTAF | \n",
" CTAF | \n",
" 122.9 | \n",
"
\n",
" \n",
" 1 | \n",
" 307581 | \n",
" 6589 | \n",
" 01FL | \n",
" ARCAL | \n",
" NaN | \n",
" 122.9 | \n",
"
\n",
" \n",
" 2 | \n",
" 75239 | \n",
" 6589 | \n",
" 01FL | \n",
" CTAF | \n",
" CEDAR KNOLL TRAFFIC | \n",
" 122.8 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" id airport_ref airport_ident type description \\\n",
"0 70518 6528 00CA CTAF CTAF \n",
"1 307581 6589 01FL ARCAL NaN \n",
"2 75239 6589 01FL CTAF CEDAR KNOLL TRAFFIC \n",
"\n",
" frequency_mhz \n",
"0 122.9 \n",
"1 122.9 \n",
"2 122.8 "
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# select * from airport_freq limit 3\n",
"\n",
"airport_freq.head(3)"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"27322 3632\n",
"Name: id, dtype: int64"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# select id from airports where ident = 'KLAX'\n",
"\n",
"airports[airports.ident == 'KLAX'].id"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"array(['heliport', 'small_airport', 'closed', 'seaplane_base',\n",
" 'balloonport', 'medium_airport', 'large_airport'], dtype=object)"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# select distinct type from airport\n",
"\n",
"airports.type.unique()"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" ident | \n",
" type | \n",
" name | \n",
" latitude_deg | \n",
" longitude_deg | \n",
" elevation_ft | \n",
" continent | \n",
" iso_country | \n",
" iso_region | \n",
" municipality | \n",
" scheduled_service | \n",
" gps_code | \n",
" iata_code | \n",
" local_code | \n",
" home_link | \n",
" wikipedia_link | \n",
" keywords | \n",
"
\n",
" \n",
" \n",
" \n",
" 967 | \n",
" 7436 | \n",
" 0O0 | \n",
" seaplane_base | \n",
" San Luis Reservoir Seaplane Base | \n",
" 37.058300 | \n",
" -121.125999 | \n",
" 544.0 | \n",
" NaN | \n",
" US | \n",
" US-CA | \n",
" Los Banos | \n",
" no | \n",
" 0O0 | \n",
" NaN | \n",
" 0O0 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 2471 | \n",
" 8877 | \n",
" 22CA | \n",
" seaplane_base | \n",
" Commodore Center Seaplane Base | \n",
" 37.879902 | \n",
" -122.514000 | \n",
" NaN | \n",
" NaN | \n",
" US | \n",
" US-CA | \n",
" Sausalito | \n",
" no | \n",
" 22CA | \n",
" NaN | \n",
" 22CA | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 6192 | \n",
" 12298 | \n",
" 5CA9 | \n",
" seaplane_base | \n",
" Konocti - Clear Lake Seaplane Base | \n",
" 38.977699 | \n",
" -122.718002 | \n",
" 1326.0 | \n",
" NaN | \n",
" US | \n",
" US-CA | \n",
" Kelseyville | \n",
" no | \n",
" 5CA9 | \n",
" NaN | \n",
" 5CA9 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 12657 | \n",
" 16514 | \n",
" C39 | \n",
" seaplane_base | \n",
" Folsom Lake Seaplane Base | \n",
" 38.707199 | \n",
" -121.133003 | \n",
" 466.0 | \n",
" NaN | \n",
" US | \n",
" US-CA | \n",
" Folsom | \n",
" no | \n",
" C39 | \n",
" NaN | \n",
" C39 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 14960 | \n",
" 16830 | \n",
" CN20 | \n",
" seaplane_base | \n",
" Ferndale Resort Seaplane Base | \n",
" 39.002998 | \n",
" -122.796997 | \n",
" 1326.0 | \n",
" NaN | \n",
" US | \n",
" US-CA | \n",
" Kelseyville | \n",
" no | \n",
" CN20 | \n",
" NaN | \n",
" CN20 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 17299 | \n",
" 17157 | \n",
" E20 | \n",
" seaplane_base | \n",
" Lake Berryessa Seaplane Base | \n",
" 38.591599 | \n",
" -122.259003 | \n",
" 440.0 | \n",
" NaN | \n",
" US | \n",
" US-CA | \n",
" Napa | \n",
" no | \n",
" E20 | \n",
" NaN | \n",
" E20 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 22340 | \n",
" 17613 | \n",
" H77 | \n",
" seaplane_base | \n",
" Bridge Bay Resort Seaplane Base | \n",
" 40.757599 | \n",
" -122.322998 | \n",
" 1065.0 | \n",
" NaN | \n",
" US | \n",
" US-CA | \n",
" Redding | \n",
" no | \n",
" H77 | \n",
" NaN | \n",
" H77 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 29218 | \n",
" 21444 | \n",
" L11 | \n",
" seaplane_base | \n",
" Pebbly Beach Seaplane Base | \n",
" 33.338402 | \n",
" -118.311996 | \n",
" NaN | \n",
" NaN | \n",
" US | \n",
" US-CA | \n",
" Avalon | \n",
" no | \n",
" KL11 | \n",
" NaN | \n",
" L11 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 35236 | \n",
" 23479 | \n",
" O06 | \n",
" seaplane_base | \n",
" Lake Oroville Landing Area Seaplane Base | \n",
" 39.566601 | \n",
" -121.468002 | \n",
" 900.0 | \n",
" NaN | \n",
" US | \n",
" US-CA | \n",
" Oroville | \n",
" no | \n",
" O06 | \n",
" NaN | \n",
" O06 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 38576 | \n",
" 24384 | \n",
" S74 | \n",
" seaplane_base | \n",
" Lost Isle Seaplane Base | \n",
" 38.004101 | \n",
" -121.457001 | \n",
" NaN | \n",
" NaN | \n",
" US | \n",
" US-CA | \n",
" Stockton | \n",
" no | \n",
" S74 | \n",
" NaN | \n",
" S74 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 45874 | \n",
" 300312 | \n",
" TWH | \n",
" seaplane_base | \n",
" Two Harbors Amphibious Terminal | \n",
" 33.432222 | \n",
" -118.508611 | \n",
" NaN | \n",
" NaN | \n",
" US | \n",
" US-CA | \n",
" Two Harbors | \n",
" no | \n",
" NaN | \n",
" TWH | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" id ident type name \\\n",
"967 7436 0O0 seaplane_base San Luis Reservoir Seaplane Base \n",
"2471 8877 22CA seaplane_base Commodore Center Seaplane Base \n",
"6192 12298 5CA9 seaplane_base Konocti - Clear Lake Seaplane Base \n",
"12657 16514 C39 seaplane_base Folsom Lake Seaplane Base \n",
"14960 16830 CN20 seaplane_base Ferndale Resort Seaplane Base \n",
"17299 17157 E20 seaplane_base Lake Berryessa Seaplane Base \n",
"22340 17613 H77 seaplane_base Bridge Bay Resort Seaplane Base \n",
"29218 21444 L11 seaplane_base Pebbly Beach Seaplane Base \n",
"35236 23479 O06 seaplane_base Lake Oroville Landing Area Seaplane Base \n",
"38576 24384 S74 seaplane_base Lost Isle Seaplane Base \n",
"45874 300312 TWH seaplane_base Two Harbors Amphibious Terminal \n",
"\n",
" latitude_deg longitude_deg elevation_ft continent iso_country \\\n",
"967 37.058300 -121.125999 544.0 NaN US \n",
"2471 37.879902 -122.514000 NaN NaN US \n",
"6192 38.977699 -122.718002 1326.0 NaN US \n",
"12657 38.707199 -121.133003 466.0 NaN US \n",
"14960 39.002998 -122.796997 1326.0 NaN US \n",
"17299 38.591599 -122.259003 440.0 NaN US \n",
"22340 40.757599 -122.322998 1065.0 NaN US \n",
"29218 33.338402 -118.311996 NaN NaN US \n",
"35236 39.566601 -121.468002 900.0 NaN US \n",
"38576 38.004101 -121.457001 NaN NaN US \n",
"45874 33.432222 -118.508611 NaN NaN US \n",
"\n",
" iso_region municipality scheduled_service gps_code iata_code local_code \\\n",
"967 US-CA Los Banos no 0O0 NaN 0O0 \n",
"2471 US-CA Sausalito no 22CA NaN 22CA \n",
"6192 US-CA Kelseyville no 5CA9 NaN 5CA9 \n",
"12657 US-CA Folsom no C39 NaN C39 \n",
"14960 US-CA Kelseyville no CN20 NaN CN20 \n",
"17299 US-CA Napa no E20 NaN E20 \n",
"22340 US-CA Redding no H77 NaN H77 \n",
"29218 US-CA Avalon no KL11 NaN L11 \n",
"35236 US-CA Oroville no O06 NaN O06 \n",
"38576 US-CA Stockton no S74 NaN S74 \n",
"45874 US-CA Two Harbors no NaN TWH NaN \n",
"\n",
" home_link wikipedia_link keywords \n",
"967 NaN NaN NaN \n",
"2471 NaN NaN NaN \n",
"6192 NaN NaN NaN \n",
"12657 NaN NaN NaN \n",
"14960 NaN NaN NaN \n",
"17299 NaN NaN NaN \n",
"22340 NaN NaN NaN \n",
"29218 NaN NaN NaN \n",
"35236 NaN NaN NaN \n",
"38576 NaN NaN NaN \n",
"45874 NaN NaN NaN "
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# select * from airports where iso_region = 'US-CA' and type = 'seaplane_base'\n",
"\n",
"airports[(airports.iso_region == 'US-CA') & (airports.type == 'seaplane_base')]"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" airport_ref | \n",
" airport_ident | \n",
" type | \n",
" description | \n",
" frequency_mhz | \n",
"
\n",
" \n",
" \n",
" \n",
" 11852 | \n",
" 60767 | \n",
" 3632 | \n",
" KLAX | \n",
" APP | \n",
" SOCAL APP | \n",
" 36.07 | \n",
"
\n",
" \n",
" 11853 | \n",
" 60766 | \n",
" 3632 | \n",
" KLAX | \n",
" APP | \n",
" SOCAL APP | \n",
" 124.30 | \n",
"
\n",
" \n",
" 11854 | \n",
" 60768 | \n",
" 3632 | \n",
" KLAX | \n",
" ATIS | \n",
" ATIS | \n",
" 133.80 | \n",
"
\n",
" \n",
" 11855 | \n",
" 60769 | \n",
" 3632 | \n",
" KLAX | \n",
" CLD | \n",
" CLNC DEL | \n",
" 121.40 | \n",
"
\n",
" \n",
" 11856 | \n",
" 60770 | \n",
" 3632 | \n",
" KLAX | \n",
" DEP | \n",
" SOCAL DEP | \n",
" 124.30 | \n",
"
\n",
" \n",
" 11857 | \n",
" 60771 | \n",
" 3632 | \n",
" KLAX | \n",
" GND | \n",
" GND | \n",
" 121.65 | \n",
"
\n",
" \n",
" 11858 | \n",
" 60772 | \n",
" 3632 | \n",
" KLAX | \n",
" MISC | \n",
" CG | \n",
" 34.50 | \n",
"
\n",
" \n",
" 11859 | \n",
" 60773 | \n",
" 3632 | \n",
" KLAX | \n",
" MISC | \n",
" CG | \n",
" 898.40 | \n",
"
\n",
" \n",
" 11860 | \n",
" 60774 | \n",
" 3632 | \n",
" KLAX | \n",
" OPS | \n",
" AF | \n",
" 37.22 | \n",
"
\n",
" \n",
" 11861 | \n",
" 60775 | \n",
" 3632 | \n",
" KLAX | \n",
" TWR | \n",
" TWR | \n",
" 119.80 | \n",
"
\n",
" \n",
" 11862 | \n",
" 60776 | \n",
" 3632 | \n",
" KLAX | \n",
" UNIC | \n",
" UNICOM | \n",
" 122.95 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" id airport_ref airport_ident type description frequency_mhz\n",
"11852 60767 3632 KLAX APP SOCAL APP 36.07\n",
"11853 60766 3632 KLAX APP SOCAL APP 124.30\n",
"11854 60768 3632 KLAX ATIS ATIS 133.80\n",
"11855 60769 3632 KLAX CLD CLNC DEL 121.40\n",
"11856 60770 3632 KLAX DEP SOCAL DEP 124.30\n",
"11857 60771 3632 KLAX GND GND 121.65\n",
"11858 60772 3632 KLAX MISC CG 34.50\n",
"11859 60773 3632 KLAX MISC CG 898.40\n",
"11860 60774 3632 KLAX OPS AF 37.22\n",
"11861 60775 3632 KLAX TWR TWR 119.80\n",
"11862 60776 3632 KLAX UNIC UNICOM 122.95"
]
},
"execution_count": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# 3632 is LAX's id, KLAX is ident\n",
"\n",
"airport_freq[airport_freq.airport_ident == 'KLAX']"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" airport_ref | \n",
" airport_ident | \n",
" type | \n",
" description | \n",
" frequency_mhz | \n",
"
\n",
" \n",
" \n",
" \n",
" 11852 | \n",
" 60767 | \n",
" 3632 | \n",
" KLAX | \n",
" APP | \n",
" SOCAL APP | \n",
" 36.07 | \n",
"
\n",
" \n",
" 11853 | \n",
" 60766 | \n",
" 3632 | \n",
" KLAX | \n",
" APP | \n",
" SOCAL APP | \n",
" 124.30 | \n",
"
\n",
" \n",
" 11854 | \n",
" 60768 | \n",
" 3632 | \n",
" KLAX | \n",
" ATIS | \n",
" ATIS | \n",
" 133.80 | \n",
"
\n",
" \n",
" 11855 | \n",
" 60769 | \n",
" 3632 | \n",
" KLAX | \n",
" CLD | \n",
" CLNC DEL | \n",
" 121.40 | \n",
"
\n",
" \n",
" 11856 | \n",
" 60770 | \n",
" 3632 | \n",
" KLAX | \n",
" DEP | \n",
" SOCAL DEP | \n",
" 124.30 | \n",
"
\n",
" \n",
" 11857 | \n",
" 60771 | \n",
" 3632 | \n",
" KLAX | \n",
" GND | \n",
" GND | \n",
" 121.65 | \n",
"
\n",
" \n",
" 11858 | \n",
" 60772 | \n",
" 3632 | \n",
" KLAX | \n",
" MISC | \n",
" CG | \n",
" 34.50 | \n",
"
\n",
" \n",
" 11859 | \n",
" 60773 | \n",
" 3632 | \n",
" KLAX | \n",
" MISC | \n",
" CG | \n",
" 898.40 | \n",
"
\n",
" \n",
" 11860 | \n",
" 60774 | \n",
" 3632 | \n",
" KLAX | \n",
" OPS | \n",
" AF | \n",
" 37.22 | \n",
"
\n",
" \n",
" 11861 | \n",
" 60775 | \n",
" 3632 | \n",
" KLAX | \n",
" TWR | \n",
" TWR | \n",
" 119.80 | \n",
"
\n",
" \n",
" 11862 | \n",
" 60776 | \n",
" 3632 | \n",
" KLAX | \n",
" UNIC | \n",
" UNICOM | \n",
" 122.95 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" id airport_ref airport_ident type description frequency_mhz\n",
"11852 60767 3632 KLAX APP SOCAL APP 36.07\n",
"11853 60766 3632 KLAX APP SOCAL APP 124.30\n",
"11854 60768 3632 KLAX ATIS ATIS 133.80\n",
"11855 60769 3632 KLAX CLD CLNC DEL 121.40\n",
"11856 60770 3632 KLAX DEP SOCAL DEP 124.30\n",
"11857 60771 3632 KLAX GND GND 121.65\n",
"11858 60772 3632 KLAX MISC CG 34.50\n",
"11859 60773 3632 KLAX MISC CG 898.40\n",
"11860 60774 3632 KLAX OPS AF 37.22\n",
"11861 60775 3632 KLAX TWR TWR 119.80\n",
"11862 60776 3632 KLAX UNIC UNICOM 122.95"
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"\"\"\"\n",
"select * \n",
"from airport_freq \n",
"where airport_ident = 'KLAX' \n",
"order by type\n",
"\"\"\"\n",
"\n",
"airport_freq[airport_freq.airport_ident == 'KLAX']\\\n",
" .sort_values('type')"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" airport_ref | \n",
" airport_ident | \n",
" type | \n",
" description | \n",
" frequency_mhz | \n",
"
\n",
" \n",
" \n",
" \n",
" 11862 | \n",
" 60776 | \n",
" 3632 | \n",
" KLAX | \n",
" UNIC | \n",
" UNICOM | \n",
" 122.95 | \n",
"
\n",
" \n",
" 11861 | \n",
" 60775 | \n",
" 3632 | \n",
" KLAX | \n",
" TWR | \n",
" TWR | \n",
" 119.80 | \n",
"
\n",
" \n",
" 11860 | \n",
" 60774 | \n",
" 3632 | \n",
" KLAX | \n",
" OPS | \n",
" AF | \n",
" 37.22 | \n",
"
\n",
" \n",
" 11858 | \n",
" 60772 | \n",
" 3632 | \n",
" KLAX | \n",
" MISC | \n",
" CG | \n",
" 34.50 | \n",
"
\n",
" \n",
" 11859 | \n",
" 60773 | \n",
" 3632 | \n",
" KLAX | \n",
" MISC | \n",
" CG | \n",
" 898.40 | \n",
"
\n",
" \n",
" 11857 | \n",
" 60771 | \n",
" 3632 | \n",
" KLAX | \n",
" GND | \n",
" GND | \n",
" 121.65 | \n",
"
\n",
" \n",
" 11856 | \n",
" 60770 | \n",
" 3632 | \n",
" KLAX | \n",
" DEP | \n",
" SOCAL DEP | \n",
" 124.30 | \n",
"
\n",
" \n",
" 11855 | \n",
" 60769 | \n",
" 3632 | \n",
" KLAX | \n",
" CLD | \n",
" CLNC DEL | \n",
" 121.40 | \n",
"
\n",
" \n",
" 11854 | \n",
" 60768 | \n",
" 3632 | \n",
" KLAX | \n",
" ATIS | \n",
" ATIS | \n",
" 133.80 | \n",
"
\n",
" \n",
" 11852 | \n",
" 60767 | \n",
" 3632 | \n",
" KLAX | \n",
" APP | \n",
" SOCAL APP | \n",
" 36.07 | \n",
"
\n",
" \n",
" 11853 | \n",
" 60766 | \n",
" 3632 | \n",
" KLAX | \n",
" APP | \n",
" SOCAL APP | \n",
" 124.30 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" id airport_ref airport_ident type description frequency_mhz\n",
"11862 60776 3632 KLAX UNIC UNICOM 122.95\n",
"11861 60775 3632 KLAX TWR TWR 119.80\n",
"11860 60774 3632 KLAX OPS AF 37.22\n",
"11858 60772 3632 KLAX MISC CG 34.50\n",
"11859 60773 3632 KLAX MISC CG 898.40\n",
"11857 60771 3632 KLAX GND GND 121.65\n",
"11856 60770 3632 KLAX DEP SOCAL DEP 124.30\n",
"11855 60769 3632 KLAX CLD CLNC DEL 121.40\n",
"11854 60768 3632 KLAX ATIS ATIS 133.80\n",
"11852 60767 3632 KLAX APP SOCAL APP 36.07\n",
"11853 60766 3632 KLAX APP SOCAL APP 124.30"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"\"\"\"\n",
"select * \n",
"from airport_freq \n",
"where airport_ident = 'KLAX' \n",
"order by type desc\n",
"\"\"\"\n",
"\n",
"airport_freq[airport_freq.airport_ident == 'KLAX']\\\n",
" .sort_values('type', ascending=False)"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" ident | \n",
" name | \n",
" municipality | \n",
"
\n",
" \n",
" \n",
" \n",
" 25697 | \n",
" KBAB | \n",
" Beale Air Force Base | \n",
" Marysville | \n",
"
\n",
" \n",
" 26532 | \n",
" KEDW | \n",
" Edwards Air Force Base | \n",
" Edwards | \n",
"
\n",
" \n",
" 27322 | \n",
" KLAX | \n",
" Los Angeles International Airport | \n",
" Los Angeles | \n",
"
\n",
" \n",
" 27849 | \n",
" KOAK | \n",
" Metropolitan Oakland International Airport | \n",
" Oakland | \n",
"
\n",
" \n",
" 27905 | \n",
" KONT | \n",
" Ontario International Airport | \n",
" Ontario | \n",
"
\n",
" \n",
" 28587 | \n",
" KSAN | \n",
" San Diego International Airport | \n",
" San Diego | \n",
"
\n",
" \n",
" 28627 | \n",
" KSFO | \n",
" San Francisco International Airport | \n",
" San Francisco | \n",
"
\n",
" \n",
" 28648 | \n",
" KSJC | \n",
" Norman Y. Mineta San Jose International Airport | \n",
" San Jose | \n",
"
\n",
" \n",
" 28670 | \n",
" KSMF | \n",
" Sacramento International Airport | \n",
" Sacramento | \n",
"
\n",
" \n",
" 28676 | \n",
" KSNA | \n",
" John Wayne Airport-Orange County Airport | \n",
" Santa Ana | \n",
"
\n",
" \n",
" 28725 | \n",
" KSUU | \n",
" Travis Air Force Base | \n",
" Fairfield | \n",
"
\n",
" \n",
" 28957 | \n",
" KVBG | \n",
" Vandenberg Air Force Base | \n",
" Lompoc | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" ident name municipality\n",
"25697 KBAB Beale Air Force Base Marysville\n",
"26532 KEDW Edwards Air Force Base Edwards\n",
"27322 KLAX Los Angeles International Airport Los Angeles\n",
"27849 KOAK Metropolitan Oakland International Airport Oakland\n",
"27905 KONT Ontario International Airport Ontario\n",
"28587 KSAN San Diego International Airport San Diego\n",
"28627 KSFO San Francisco International Airport San Francisco\n",
"28648 KSJC Norman Y. Mineta San Jose International Airport San Jose\n",
"28670 KSMF Sacramento International Airport Sacramento\n",
"28676 KSNA John Wayne Airport-Orange County Airport Santa Ana\n",
"28725 KSUU Travis Air Force Base Fairfield\n",
"28957 KVBG Vandenberg Air Force Base Lompoc"
]
},
"execution_count": 15,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"\"\"\"\n",
"select ident, name, municipality from airports\n",
"where iso_region = 'US-CA' \n",
"and type = 'large_airport'\n",
"\"\"\"\n",
"\n",
"airports[(airports.iso_region == 'US-CA') &\n",
" (airports.type == 'large_airport')]\\\n",
" [['ident', 'name', 'municipality']]"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" ident | \n",
" type | \n",
" name | \n",
" latitude_deg | \n",
" longitude_deg | \n",
" elevation_ft | \n",
" continent | \n",
" iso_country | \n",
" iso_region | \n",
" municipality | \n",
" scheduled_service | \n",
" gps_code | \n",
" iata_code | \n",
" local_code | \n",
" home_link | \n",
" wikipedia_link | \n",
" keywords | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 6523 | \n",
" 00A | \n",
" heliport | \n",
" Total Rf Heliport | \n",
" 40.070801 | \n",
" -74.933601 | \n",
" 11.0 | \n",
" NaN | \n",
" US | \n",
" US-PA | \n",
" Bensalem | \n",
" no | \n",
" 00A | \n",
" NaN | \n",
" 00A | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 9 | \n",
" 322658 | \n",
" 00CN | \n",
" heliport | \n",
" Kitchen Creek Helibase Heliport | \n",
" 32.727374 | \n",
" -116.459742 | \n",
" 3350.0 | \n",
" NaN | \n",
" US | \n",
" US-CA | \n",
" Pine Valley | \n",
" no | \n",
" 00CN | \n",
" NaN | \n",
" 00CN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 12 | \n",
" 6532 | \n",
" 00FD | \n",
" heliport | \n",
" Ringhaver Heliport | \n",
" 28.846600 | \n",
" -82.345398 | \n",
" 25.0 | \n",
" NaN | \n",
" US | \n",
" US-FL | \n",
" Riverview | \n",
" no | \n",
" 00FD | \n",
" NaN | \n",
" 00FD | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" id ident type name latitude_deg \\\n",
"0 6523 00A heliport Total Rf Heliport 40.070801 \n",
"9 322658 00CN heliport Kitchen Creek Helibase Heliport 32.727374 \n",
"12 6532 00FD heliport Ringhaver Heliport 28.846600 \n",
"\n",
" longitude_deg elevation_ft continent iso_country iso_region municipality \\\n",
"0 -74.933601 11.0 NaN US US-PA Bensalem \n",
"9 -116.459742 3350.0 NaN US US-CA Pine Valley \n",
"12 -82.345398 25.0 NaN US US-FL Riverview \n",
"\n",
" scheduled_service gps_code iata_code local_code home_link wikipedia_link \\\n",
"0 no 00A NaN 00A NaN NaN \n",
"9 no 00CN NaN 00CN NaN NaN \n",
"12 no 00FD NaN 00FD NaN NaN \n",
"\n",
" keywords \n",
"0 NaN \n",
"9 NaN \n",
"12 NaN "
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"\"\"\"\n",
"select * from airports\n",
"where type in ('heliport', 'balloonport')\n",
"\"\"\"\n",
"\n",
"airports[\n",
" airports.type.isin(['heliport', 'balloonport'])].head(3)"
]
},
{
"cell_type": "code",
"execution_count": 17,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" ident | \n",
" type | \n",
" name | \n",
" latitude_deg | \n",
" longitude_deg | \n",
" elevation_ft | \n",
" continent | \n",
" iso_country | \n",
" iso_region | \n",
" municipality | \n",
" scheduled_service | \n",
" gps_code | \n",
" iata_code | \n",
" local_code | \n",
" home_link | \n",
" wikipedia_link | \n",
" keywords | \n",
"
\n",
" \n",
" \n",
" \n",
" 1 | \n",
" 323361 | \n",
" 00AA | \n",
" small_airport | \n",
" Aero B Ranch Airport | \n",
" 38.704022 | \n",
" -101.473911 | \n",
" 3435.0 | \n",
" NaN | \n",
" US | \n",
" US-KS | \n",
" Leoti | \n",
" no | \n",
" 00AA | \n",
" NaN | \n",
" 00AA | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 2 | \n",
" 6524 | \n",
" 00AK | \n",
" small_airport | \n",
" Lowell Field | \n",
" 59.949200 | \n",
" -151.695999 | \n",
" 450.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Anchor Point | \n",
" no | \n",
" 00AK | \n",
" NaN | \n",
" 00AK | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 3 | \n",
" 6525 | \n",
" 00AL | \n",
" small_airport | \n",
" Epps Airpark | \n",
" 34.864799 | \n",
" -86.770302 | \n",
" 820.0 | \n",
" NaN | \n",
" US | \n",
" US-AL | \n",
" Harvest | \n",
" no | \n",
" 00AL | \n",
" NaN | \n",
" 00AL | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" id ident type name latitude_deg \\\n",
"1 323361 00AA small_airport Aero B Ranch Airport 38.704022 \n",
"2 6524 00AK small_airport Lowell Field 59.949200 \n",
"3 6525 00AL small_airport Epps Airpark 34.864799 \n",
"\n",
" longitude_deg elevation_ft continent iso_country iso_region municipality \\\n",
"1 -101.473911 3435.0 NaN US US-KS Leoti \n",
"2 -151.695999 450.0 NaN US US-AK Anchor Point \n",
"3 -86.770302 820.0 NaN US US-AL Harvest \n",
"\n",
" scheduled_service gps_code iata_code local_code home_link wikipedia_link \\\n",
"1 no 00AA NaN 00AA NaN NaN \n",
"2 no 00AK NaN 00AK NaN NaN \n",
"3 no 00AL NaN 00AL NaN NaN \n",
"\n",
" keywords \n",
"1 NaN \n",
"2 NaN \n",
"3 NaN "
]
},
"execution_count": 17,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"\"\"\"\n",
"select * from airports\n",
"where type not in ('heliport', 'balloonport')\n",
"\"\"\"\n",
"\n",
"airports[\n",
" ~airports.type.isin(['heliport', 'balloonport'])].head(3)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Aggregating data"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"type\n",
"balloonport 23\n",
"closed 2682\n",
"heliport 9946\n",
"large_airport 589\n",
"medium_airport 4531\n",
"seaplane_base 1016\n",
"small_airport 33549\n",
"dtype: int64"
]
},
"execution_count": 18,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"\"\"\"\n",
"select type, count(*) from airports\n",
"group by type\n",
"\"\"\"\n",
"\n",
"airports.groupby('type').size()"
]
},
{
"cell_type": "code",
"execution_count": 19,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"type\n",
"balloonport 23\n",
"large_airport 589\n",
"seaplane_base 1016\n",
"closed 2682\n",
"medium_airport 4531\n",
"heliport 9946\n",
"small_airport 33549\n",
"dtype: int64"
]
},
"execution_count": 19,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"\"\"\"\n",
"select type, count(*) from airports\n",
"group by type\n",
"order by count(*)\n",
"\"\"\"\n",
"\n",
"airports.groupby('type')\\\n",
" .size()\\\n",
" .sort_values()"
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"type\n",
"small_airport 33549\n",
"heliport 9946\n",
"medium_airport 4531\n",
"closed 2682\n",
"seaplane_base 1016\n",
"large_airport 589\n",
"balloonport 23\n",
"dtype: int64"
]
},
"execution_count": 20,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"\"\"\"\n",
"select type, count(*) from airports\n",
"group by type\n",
"order by count(*) desc\n",
"\"\"\"\n",
"\n",
"airports.groupby('type')\\\n",
" .size()\\\n",
" .sort_values(ascending=False)"
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"iso_country type \n",
"AD heliport 2\n",
"AE closed 1\n",
" heliport 21\n",
" large_airport 4\n",
" medium_airport 7\n",
" seaplane_base 3\n",
" small_airport 16\n",
"AF heliport 3\n",
" medium_airport 8\n",
" small_airport 51\n",
"AG medium_airport 1\n",
" small_airport 2\n",
"AI medium_airport 1\n",
"AL closed 1\n",
" heliport 2\n",
" large_airport 1\n",
" medium_airport 4\n",
" small_airport 2\n",
"AM closed 4\n",
" large_airport 1\n",
" medium_airport 3\n",
" small_airport 5\n",
"AO closed 10\n",
" medium_airport 21\n",
" small_airport 70\n",
"AQ closed 2\n",
" medium_airport 4\n",
" small_airport 20\n",
"AR balloonport 1\n",
" closed 18\n",
" ... \n",
"VI seaplane_base 3\n",
"VN closed 6\n",
" heliport 5\n",
" large_airport 3\n",
" medium_airport 17\n",
" small_airport 16\n",
"VU medium_airport 4\n",
" small_airport 28\n",
"WF medium_airport 2\n",
"WS medium_airport 1\n",
" small_airport 3\n",
"XK closed 2\n",
" heliport 2\n",
" large_airport 1\n",
" medium_airport 1\n",
"YE medium_airport 11\n",
" small_airport 14\n",
"YT medium_airport 1\n",
"ZA closed 10\n",
" heliport 4\n",
" large_airport 4\n",
" medium_airport 56\n",
" small_airport 406\n",
"ZM closed 1\n",
" large_airport 1\n",
" medium_airport 7\n",
" small_airport 92\n",
"ZW closed 1\n",
" medium_airport 9\n",
" small_airport 126\n",
"Length: 808, dtype: int64"
]
},
"execution_count": 21,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"\"\"\"\n",
"select iso_country, type, count(*)\n",
"from airports\n",
"group by iso_country, type\n",
"order by iso_country, type -- implicit\n",
"\"\"\"\n",
"\n",
"airports.groupby(['iso_country', 'type'])\\\n",
" .size()"
]
},
{
"cell_type": "code",
"execution_count": 22,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" iso_country | \n",
" type | \n",
" size | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" RU | \n",
" closed | \n",
" 143 | \n",
"
\n",
" \n",
" 1 | \n",
" RU | \n",
" heliport | \n",
" 107 | \n",
"
\n",
" \n",
" 2 | \n",
" RU | \n",
" large_airport | \n",
" 20 | \n",
"
\n",
" \n",
" 3 | \n",
" RU | \n",
" medium_airport | \n",
" 252 | \n",
"
\n",
" \n",
" 4 | \n",
" RU | \n",
" seaplane_base | \n",
" 4 | \n",
"
\n",
" \n",
" 5 | \n",
" RU | \n",
" small_airport | \n",
" 493 | \n",
"
\n",
" \n",
" 6 | \n",
" US | \n",
" balloonport | \n",
" 17 | \n",
"
\n",
" \n",
" 7 | \n",
" US | \n",
" closed | \n",
" 696 | \n",
"
\n",
" \n",
" 8 | \n",
" US | \n",
" heliport | \n",
" 6184 | \n",
"
\n",
" \n",
" 9 | \n",
" US | \n",
" large_airport | \n",
" 174 | \n",
"
\n",
" \n",
" 10 | \n",
" US | \n",
" medium_airport | \n",
" 686 | \n",
"
\n",
" \n",
" 11 | \n",
" US | \n",
" seaplane_base | \n",
" 564 | \n",
"
\n",
" \n",
" 12 | \n",
" US | \n",
" small_airport | \n",
" 13888 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" iso_country type size\n",
"0 RU closed 143\n",
"1 RU heliport 107\n",
"2 RU large_airport 20\n",
"3 RU medium_airport 252\n",
"4 RU seaplane_base 4\n",
"5 RU small_airport 493\n",
"6 US balloonport 17\n",
"7 US closed 696\n",
"8 US heliport 6184\n",
"9 US large_airport 174\n",
"10 US medium_airport 686\n",
"11 US seaplane_base 564\n",
"12 US small_airport 13888"
]
},
"execution_count": 22,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"airports[airports.iso_country.isin(['US', 'RU'])]\\\n",
" .groupby(['iso_country', 'type'])\\\n",
" .size()\\\n",
" .to_frame('size')\\\n",
" .reset_index()"
]
},
{
"cell_type": "code",
"execution_count": 23,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" iso_country | \n",
" type | \n",
" size | \n",
"
\n",
" \n",
" \n",
" \n",
" 5 | \n",
" RU | \n",
" small_airport | \n",
" 493 | \n",
"
\n",
" \n",
" 3 | \n",
" RU | \n",
" medium_airport | \n",
" 252 | \n",
"
\n",
" \n",
" 0 | \n",
" RU | \n",
" closed | \n",
" 143 | \n",
"
\n",
" \n",
" 1 | \n",
" RU | \n",
" heliport | \n",
" 107 | \n",
"
\n",
" \n",
" 2 | \n",
" RU | \n",
" large_airport | \n",
" 20 | \n",
"
\n",
" \n",
" 4 | \n",
" RU | \n",
" seaplane_base | \n",
" 4 | \n",
"
\n",
" \n",
" 12 | \n",
" US | \n",
" small_airport | \n",
" 13888 | \n",
"
\n",
" \n",
" 8 | \n",
" US | \n",
" heliport | \n",
" 6184 | \n",
"
\n",
" \n",
" 7 | \n",
" US | \n",
" closed | \n",
" 696 | \n",
"
\n",
" \n",
" 10 | \n",
" US | \n",
" medium_airport | \n",
" 686 | \n",
"
\n",
" \n",
" 11 | \n",
" US | \n",
" seaplane_base | \n",
" 564 | \n",
"
\n",
" \n",
" 9 | \n",
" US | \n",
" large_airport | \n",
" 174 | \n",
"
\n",
" \n",
" 6 | \n",
" US | \n",
" balloonport | \n",
" 17 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" iso_country type size\n",
"5 RU small_airport 493\n",
"3 RU medium_airport 252\n",
"0 RU closed 143\n",
"1 RU heliport 107\n",
"2 RU large_airport 20\n",
"4 RU seaplane_base 4\n",
"12 US small_airport 13888\n",
"8 US heliport 6184\n",
"7 US closed 696\n",
"10 US medium_airport 686\n",
"11 US seaplane_base 564\n",
"9 US large_airport 174\n",
"6 US balloonport 17"
]
},
"execution_count": 23,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"\"\"\"\n",
"select iso_country, type, count(*)\n",
"from airports\n",
"group by iso_country, type\n",
"order by iso_country, count(*) desc\n",
"\"\"\"\n",
"\n",
"airports[airports.iso_country.isin(['US', 'RU'])]\\\n",
" .groupby(['iso_country', 'type'])\\\n",
" .size()\\\n",
" .to_frame('size')\\\n",
" .reset_index()\\\n",
" .sort_values(['iso_country', 'size'], ascending=[True, False])"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## HAVING condition"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"type\n",
"small_airport 13888\n",
"heliport 6184\n",
"dtype: int64"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"\"\"\"\n",
"select type, count(*)\n",
"from airports\n",
"where iso_country = 'US'\n",
"group by iso_country\n",
"having count(*) > 1000\n",
"order by count(*) desc\n",
"\"\"\"\n",
"\n",
"airports[airports.iso_country == 'US']\\\n",
" .groupby('type')\\\n",
" .filter(lambda g: len(g) > 1000)\\\n",
" .groupby('type')\\\n",
" .size()\\\n",
" .sort_values(ascending=False)"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"pandas.core.groupby.DataFrameGroupBy"
]
},
"execution_count": 18,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"step1 = airports[airports.iso_country == 'US'].groupby('type')\n",
"step1.__class__"
]
},
{
"cell_type": "code",
"execution_count": 19,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"pandas.core.frame.DataFrame"
]
},
"execution_count": 19,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"step2 = step1.filter(lambda g: len(g) > 1000)\n",
"step2.__class__"
]
},
{
"cell_type": "code",
"execution_count": 24,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" ident | \n",
" type | \n",
" name | \n",
" latitude_deg | \n",
" longitude_deg | \n",
" elevation_ft | \n",
" continent | \n",
" iso_country | \n",
" iso_region | \n",
" municipality | \n",
" scheduled_service | \n",
" gps_code | \n",
" iata_code | \n",
" local_code | \n",
" home_link | \n",
" wikipedia_link | \n",
" keywords | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 6523 | \n",
" 00A | \n",
" heliport | \n",
" Total Rf Heliport | \n",
" 40.070801 | \n",
" -74.933601 | \n",
" 11.0 | \n",
" NaN | \n",
" US | \n",
" US-PA | \n",
" Bensalem | \n",
" no | \n",
" 00A | \n",
" NaN | \n",
" 00A | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 1 | \n",
" 323361 | \n",
" 00AA | \n",
" small_airport | \n",
" Aero B Ranch Airport | \n",
" 38.704022 | \n",
" -101.473911 | \n",
" 3435.0 | \n",
" NaN | \n",
" US | \n",
" US-KS | \n",
" Leoti | \n",
" no | \n",
" 00AA | \n",
" NaN | \n",
" 00AA | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 2 | \n",
" 6524 | \n",
" 00AK | \n",
" small_airport | \n",
" Lowell Field | \n",
" 59.949200 | \n",
" -151.695999 | \n",
" 450.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Anchor Point | \n",
" no | \n",
" 00AK | \n",
" NaN | \n",
" 00AK | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 3 | \n",
" 6525 | \n",
" 00AL | \n",
" small_airport | \n",
" Epps Airpark | \n",
" 34.864799 | \n",
" -86.770302 | \n",
" 820.0 | \n",
" NaN | \n",
" US | \n",
" US-AL | \n",
" Harvest | \n",
" no | \n",
" 00AL | \n",
" NaN | \n",
" 00AL | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 5 | \n",
" 322127 | \n",
" 00AS | \n",
" small_airport | \n",
" Fulton Airport | \n",
" 34.942803 | \n",
" -97.818019 | \n",
" 1100.0 | \n",
" NaN | \n",
" US | \n",
" US-OK | \n",
" Alex | \n",
" no | \n",
" 00AS | \n",
" NaN | \n",
" 00AS | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 6 | \n",
" 6527 | \n",
" 00AZ | \n",
" small_airport | \n",
" Cordes Airport | \n",
" 34.305599 | \n",
" -112.165001 | \n",
" 3810.0 | \n",
" NaN | \n",
" US | \n",
" US-AZ | \n",
" Cordes | \n",
" no | \n",
" 00AZ | \n",
" NaN | \n",
" 00AZ | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 7 | \n",
" 6528 | \n",
" 00CA | \n",
" small_airport | \n",
" Goldstone /Gts/ Airport | \n",
" 35.350498 | \n",
" -116.888000 | \n",
" 3038.0 | \n",
" NaN | \n",
" US | \n",
" US-CA | \n",
" Barstow | \n",
" no | \n",
" 00CA | \n",
" NaN | \n",
" 00CA | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 8 | \n",
" 324424 | \n",
" 00CL | \n",
" small_airport | \n",
" Williams Ag Airport | \n",
" 39.427188 | \n",
" -121.763427 | \n",
" 87.0 | \n",
" NaN | \n",
" US | \n",
" US-CA | \n",
" Biggs | \n",
" no | \n",
" 00CL | \n",
" NaN | \n",
" 00CL | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 9 | \n",
" 322658 | \n",
" 00CN | \n",
" heliport | \n",
" Kitchen Creek Helibase Heliport | \n",
" 32.727374 | \n",
" -116.459742 | \n",
" 3350.0 | \n",
" NaN | \n",
" US | \n",
" US-CA | \n",
" Pine Valley | \n",
" no | \n",
" 00CN | \n",
" NaN | \n",
" 00CN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 11 | \n",
" 6531 | \n",
" 00FA | \n",
" small_airport | \n",
" Grass Patch Airport | \n",
" 28.645500 | \n",
" -82.219002 | \n",
" 53.0 | \n",
" NaN | \n",
" US | \n",
" US-FL | \n",
" Bushnell | \n",
" no | \n",
" 00FA | \n",
" NaN | \n",
" 00FA | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 12 | \n",
" 6532 | \n",
" 00FD | \n",
" heliport | \n",
" Ringhaver Heliport | \n",
" 28.846600 | \n",
" -82.345398 | \n",
" 25.0 | \n",
" NaN | \n",
" US | \n",
" US-FL | \n",
" Riverview | \n",
" no | \n",
" 00FD | \n",
" NaN | \n",
" 00FD | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 13 | \n",
" 6533 | \n",
" 00FL | \n",
" small_airport | \n",
" River Oak Airport | \n",
" 27.230900 | \n",
" -80.969200 | \n",
" 35.0 | \n",
" NaN | \n",
" US | \n",
" US-FL | \n",
" Okeechobee | \n",
" no | \n",
" 00FL | \n",
" NaN | \n",
" 00FL | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 14 | \n",
" 6534 | \n",
" 00GA | \n",
" small_airport | \n",
" Lt World Airport | \n",
" 33.767502 | \n",
" -84.068298 | \n",
" 700.0 | \n",
" NaN | \n",
" US | \n",
" US-GA | \n",
" Lithonia | \n",
" no | \n",
" 00GA | \n",
" NaN | \n",
" 00GA | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 15 | \n",
" 6535 | \n",
" 00GE | \n",
" heliport | \n",
" Caffrey Heliport | \n",
" 33.884201 | \n",
" -84.733902 | \n",
" 957.0 | \n",
" NaN | \n",
" US | \n",
" US-GA | \n",
" Hiram | \n",
" no | \n",
" 00GE | \n",
" NaN | \n",
" 00GE | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 16 | \n",
" 6536 | \n",
" 00HI | \n",
" heliport | \n",
" Kaupulehu Heliport | \n",
" 19.832500 | \n",
" -155.981995 | \n",
" 43.0 | \n",
" NaN | \n",
" US | \n",
" US-HI | \n",
" Kailua/Kona | \n",
" no | \n",
" 00HI | \n",
" NaN | \n",
" 00HI | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 17 | \n",
" 6537 | \n",
" 00ID | \n",
" small_airport | \n",
" Delta Shores Airport | \n",
" 48.145302 | \n",
" -116.213997 | \n",
" 2064.0 | \n",
" NaN | \n",
" US | \n",
" US-ID | \n",
" Clark Fork | \n",
" no | \n",
" 00ID | \n",
" NaN | \n",
" 00ID | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 18 | \n",
" 322581 | \n",
" 00IG | \n",
" small_airport | \n",
" Goltl Airport | \n",
" 39.724028 | \n",
" -101.395994 | \n",
" 3359.0 | \n",
" NaN | \n",
" US | \n",
" US-KS | \n",
" McDonald | \n",
" no | \n",
" 00IG | \n",
" NaN | \n",
" 00IG | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 19 | \n",
" 6538 | \n",
" 00II | \n",
" heliport | \n",
" Bailey Generation Station Heliport | \n",
" 41.644501 | \n",
" -87.122803 | \n",
" 600.0 | \n",
" NaN | \n",
" US | \n",
" US-IN | \n",
" Chesterton | \n",
" no | \n",
" 00II | \n",
" NaN | \n",
" 00II | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 20 | \n",
" 6539 | \n",
" 00IL | \n",
" small_airport | \n",
" Hammer Airport | \n",
" 41.978401 | \n",
" -89.560402 | \n",
" 840.0 | \n",
" NaN | \n",
" US | \n",
" US-IL | \n",
" Polo | \n",
" no | \n",
" 00IL | \n",
" NaN | \n",
" 00IL | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 21 | \n",
" 6540 | \n",
" 00IN | \n",
" heliport | \n",
" St Mary Medical Center Heliport | \n",
" 41.511398 | \n",
" -87.260597 | \n",
" 634.0 | \n",
" NaN | \n",
" US | \n",
" US-IN | \n",
" Hobart | \n",
" no | \n",
" 00IN | \n",
" NaN | \n",
" 00IN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 22 | \n",
" 6541 | \n",
" 00IS | \n",
" small_airport | \n",
" Hayenga's Cant Find Farms Airport | \n",
" 40.025600 | \n",
" -89.122902 | \n",
" 820.0 | \n",
" NaN | \n",
" US | \n",
" US-IL | \n",
" Kings | \n",
" no | \n",
" 00IS | \n",
" NaN | \n",
" 00IS | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 23 | \n",
" 6542 | \n",
" 00KS | \n",
" small_airport | \n",
" Hayden Farm Airport | \n",
" 38.727798 | \n",
" -94.930496 | \n",
" 1100.0 | \n",
" NaN | \n",
" US | \n",
" US-KS | \n",
" Gardner | \n",
" no | \n",
" 00KS | \n",
" NaN | \n",
" 00KS | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 24 | \n",
" 6543 | \n",
" 00KY | \n",
" small_airport | \n",
" Robbins Roost Airport | \n",
" 37.409401 | \n",
" -84.619698 | \n",
" 1265.0 | \n",
" NaN | \n",
" US | \n",
" US-KY | \n",
" Stanford | \n",
" no | \n",
" 00KY | \n",
" NaN | \n",
" 00KY | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 25 | \n",
" 45437 | \n",
" 00LA | \n",
" heliport | \n",
" Shell Chemical East Site Heliport | \n",
" 30.191944 | \n",
" -90.980833 | \n",
" 15.0 | \n",
" NaN | \n",
" US | \n",
" US-LA | \n",
" Gonzales | \n",
" no | \n",
" 00LA | \n",
" NaN | \n",
" 00LA | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 26 | \n",
" 6544 | \n",
" 00LL | \n",
" heliport | \n",
" Ac & R Components Heliport | \n",
" 39.665298 | \n",
" -89.705597 | \n",
" 600.0 | \n",
" NaN | \n",
" US | \n",
" US-IL | \n",
" Chatham | \n",
" no | \n",
" 00LL | \n",
" NaN | \n",
" 00LL | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 27 | \n",
" 6545 | \n",
" 00LS | \n",
" small_airport | \n",
" Lejeune Airport | \n",
" 30.136299 | \n",
" -92.429398 | \n",
" 12.0 | \n",
" NaN | \n",
" US | \n",
" US-LA | \n",
" Esterwood | \n",
" no | \n",
" 00LS | \n",
" NaN | \n",
" 00LS | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 28 | \n",
" 6546 | \n",
" 00MD | \n",
" small_airport | \n",
" Slater Field | \n",
" 38.757099 | \n",
" -75.753799 | \n",
" 45.0 | \n",
" NaN | \n",
" US | \n",
" US-MD | \n",
" Federalsburg | \n",
" no | \n",
" 00MD | \n",
" NaN | \n",
" 00MD | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 29 | \n",
" 6547 | \n",
" 00MI | \n",
" heliport | \n",
" Dow Chemical Heliport | \n",
" 43.949402 | \n",
" -86.416702 | \n",
" 588.0 | \n",
" NaN | \n",
" US | \n",
" US-MI | \n",
" Ludington | \n",
" no | \n",
" 00MI | \n",
" NaN | \n",
" 00MI | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 30 | \n",
" 6548 | \n",
" 00MN | \n",
" small_airport | \n",
" Battle Lake Municipal Airport | \n",
" 46.299999 | \n",
" -95.700302 | \n",
" 1365.0 | \n",
" NaN | \n",
" US | \n",
" US-MN | \n",
" Battle Lake | \n",
" no | \n",
" 00MN | \n",
" NaN | \n",
" 00MN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 31 | \n",
" 6549 | \n",
" 00MO | \n",
" small_airport | \n",
" Cooper Flying Service Airport | \n",
" 37.202801 | \n",
" -94.412399 | \n",
" 970.0 | \n",
" NaN | \n",
" US | \n",
" US-MO | \n",
" Alba | \n",
" no | \n",
" 00MO | \n",
" NaN | \n",
" 00MO | \n",
" NaN | \n",
" NaN | \n",
" 5K8 | \n",
"
\n",
" \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
"
\n",
" \n",
" 50033 | \n",
" 26331 | \n",
" Y58 | \n",
" small_airport | \n",
" Sleepy Eye Municipal Airport | \n",
" 44.250000 | \n",
" -94.716904 | \n",
" 1004.0 | \n",
" NaN | \n",
" US | \n",
" US-MN | \n",
" Sleepy Eye | \n",
" no | \n",
" Y58 | \n",
" NaN | \n",
" Y58 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 50034 | \n",
" 26332 | \n",
" Y63 | \n",
" small_airport | \n",
" Elbow Lake Municipal - Pride of the Prairie Ai... | \n",
" 45.986099 | \n",
" -95.991997 | \n",
" 1205.0 | \n",
" NaN | \n",
" US | \n",
" US-MN | \n",
" Elbow Lake | \n",
" no | \n",
" Y63 | \n",
" NaN | \n",
" Y63 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 50035 | \n",
" 26333 | \n",
" Y71 | \n",
" small_airport | \n",
" Elgin Municipal Airport | \n",
" 46.382801 | \n",
" -101.845001 | \n",
" 2355.0 | \n",
" NaN | \n",
" US | \n",
" US-ND | \n",
" Elgin | \n",
" no | \n",
" Y71 | \n",
" NaN | \n",
" Y71 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 50036 | \n",
" 26334 | \n",
" Y73 | \n",
" small_airport | \n",
" Stambaugh Airport | \n",
" 46.078800 | \n",
" -88.635498 | \n",
" 1622.0 | \n",
" NaN | \n",
" US | \n",
" US-MI | \n",
" Iron River | \n",
" no | \n",
" Y73 | \n",
" NaN | \n",
" Y73 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 50037 | \n",
" 26335 | \n",
" Y76 | \n",
" small_airport | \n",
" Morningstar Field | \n",
" 41.655499 | \n",
" -93.644096 | \n",
" 805.0 | \n",
" NaN | \n",
" US | \n",
" US-IA | \n",
" Des Moines | \n",
" no | \n",
" Y76 | \n",
" NaN | \n",
" Y76 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 50038 | \n",
" 26336 | \n",
" Y77 | \n",
" small_airport | \n",
" Bayfield County Airport | \n",
" 46.576302 | \n",
" -91.458504 | \n",
" 1143.0 | \n",
" NaN | \n",
" US | \n",
" US-WI | \n",
" Iron River | \n",
" no | \n",
" Y77 | \n",
" NaN | \n",
" Y77 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 50039 | \n",
" 26337 | \n",
" Y87 | \n",
" small_airport | \n",
" Empire Airport | \n",
" 44.787498 | \n",
" -86.004303 | \n",
" 944.0 | \n",
" NaN | \n",
" US | \n",
" US-MI | \n",
" Empire | \n",
" no | \n",
" Y87 | \n",
" NaN | \n",
" Y87 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 50040 | \n",
" 26338 | \n",
" Y88 | \n",
" small_airport | \n",
" Green Lake Airport | \n",
" 44.606098 | \n",
" -85.758400 | \n",
" 866.0 | \n",
" NaN | \n",
" US | \n",
" US-MI | \n",
" Interlochen | \n",
" no | \n",
" Y88 | \n",
" NaN | \n",
" Y88 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 50041 | \n",
" 26339 | \n",
" Y91 | \n",
" small_airport | \n",
" Home Acres Sky Ranch Airport | \n",
" 44.320301 | \n",
" -85.171204 | \n",
" 1247.0 | \n",
" NaN | \n",
" US | \n",
" US-MI | \n",
" Lake City | \n",
" no | \n",
" Y91 | \n",
" NaN | \n",
" Y91 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 50042 | \n",
" 26340 | \n",
" Y96 | \n",
" small_airport | \n",
" Leo E. Goetz County Airport | \n",
" 45.370800 | \n",
" -84.224998 | \n",
" 830.0 | \n",
" NaN | \n",
" US | \n",
" US-MI | \n",
" Onaway | \n",
" no | \n",
" Y96 | \n",
" NaN | \n",
" Y96 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 50043 | \n",
" 26341 | \n",
" Y98 | \n",
" small_airport | \n",
" Grand Marais Airport | \n",
" 46.620800 | \n",
" -85.916801 | \n",
" 838.0 | \n",
" NaN | \n",
" US | \n",
" US-MI | \n",
" Grand Marais | \n",
" no | \n",
" Y98 | \n",
" NaN | \n",
" Y98 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 50044 | \n",
" 26342 | \n",
" Y99 | \n",
" small_airport | \n",
" Trulson Field | \n",
" 48.030602 | \n",
" -101.953003 | \n",
" 2105.0 | \n",
" NaN | \n",
" US | \n",
" US-ND | \n",
" Plaza | \n",
" no | \n",
" Y99 | \n",
" NaN | \n",
" Y99 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 51839 | \n",
" 26345 | \n",
" Z13 | \n",
" small_airport | \n",
" Akiachak Airport | \n",
" 60.904800 | \n",
" -161.421997 | \n",
" 23.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Akiachak | \n",
" yes | \n",
" Z13 | \n",
" KKI | \n",
" Z13 | \n",
" NaN | \n",
" https://en.wikipedia.org/wiki/Akiachak_Airport | \n",
" NaN | \n",
"
\n",
" \n",
" 51840 | \n",
" 26346 | \n",
" Z14 | \n",
" small_airport | \n",
" Tazlina Airport | \n",
" 62.064800 | \n",
" -146.460999 | \n",
" 2450.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Tazlina | \n",
" no | \n",
" Z14 | \n",
" NaN | \n",
" Z14 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 51841 | \n",
" 26347 | \n",
" Z17 | \n",
" small_airport | \n",
" Ophir Airport | \n",
" 63.146000 | \n",
" -156.529999 | \n",
" 575.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Ophir | \n",
" no | \n",
" Z17 | \n",
" NaN | \n",
" Z17 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 51846 | \n",
" 45975 | \n",
" Z25 | \n",
" small_airport | \n",
" Tripod Airport | \n",
" 59.263146 | \n",
" -158.557755 | \n",
" 225.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Aleknagik | \n",
" no | \n",
" Z25 | \n",
" NaN | \n",
" Z25 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 51848 | \n",
" 26350 | \n",
" Z40 | \n",
" small_airport | \n",
" Goose Bay Airport | \n",
" 61.394501 | \n",
" -149.845993 | \n",
" 78.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Goose Bay | \n",
" no | \n",
" Z40 | \n",
" NaN | \n",
" Z40 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 51849 | \n",
" 26351 | \n",
" Z41 | \n",
" small_airport | \n",
" Lake Hood Strip | \n",
" 61.186901 | \n",
" -149.964996 | \n",
" 73.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Anchorage | \n",
" no | \n",
" Z41 | \n",
" NaN | \n",
" Z41 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 51851 | \n",
" 26353 | \n",
" Z47 | \n",
" small_airport | \n",
" Basin Creek Airport | \n",
" 64.679802 | \n",
" -165.300003 | \n",
" 140.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Basin Creek | \n",
" no | \n",
" Z47 | \n",
" NaN | \n",
" Z47 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 51852 | \n",
" 26354 | \n",
" Z48 | \n",
" small_airport | \n",
" Bear Creek 3 Airport | \n",
" 63.573316 | \n",
" -156.149454 | \n",
" 740.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Bear Creek | \n",
" no | \n",
" Z48 | \n",
" BCC | \n",
" Z48 | \n",
" NaN | \n",
" http://en.wikipedia.org/wiki/Bear_Creek_3_Airport | \n",
" NaN | \n",
"
\n",
" \n",
" 51853 | \n",
" 26355 | \n",
" Z52 | \n",
" small_airport | \n",
" Johnsons Landing Airport | \n",
" 56.036701 | \n",
" -160.266006 | \n",
" 130.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Bear Lake | \n",
" no | \n",
" Z52 | \n",
" NaN | \n",
" Z52 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 51854 | \n",
" 26356 | \n",
" Z55 | \n",
" small_airport | \n",
" Lake Louise Airport | \n",
" 62.293701 | \n",
" -146.578995 | \n",
" 2450.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Lake Louise | \n",
" no | \n",
" Z55 | \n",
" NaN | \n",
" Z55 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 51859 | \n",
" 26361 | \n",
" Z81 | \n",
" small_airport | \n",
" Salmon Lake Airport | \n",
" 64.909203 | \n",
" -165.013000 | \n",
" 490.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Salmon Lake | \n",
" no | \n",
" Z81 | \n",
" NaN | \n",
" Z81 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 51860 | \n",
" 26362 | \n",
" Z86 | \n",
" small_airport | \n",
" Clearwater Airport | \n",
" 63.026798 | \n",
" -147.179001 | \n",
" 2900.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Clearwater | \n",
" no | \n",
" Z86 | \n",
" NaN | \n",
" Z86 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 51862 | \n",
" 26364 | \n",
" Z90 | \n",
" small_airport | \n",
" Stampede Airport | \n",
" 63.748699 | \n",
" -150.330002 | \n",
" 1850.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Kantishna | \n",
" no | \n",
" Z90 | \n",
" NaN | \n",
" Z90 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 51863 | \n",
" 26365 | \n",
" Z91 | \n",
" small_airport | \n",
" Birch Creek Airport | \n",
" 66.274002 | \n",
" -145.824005 | \n",
" 450.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Birch Creek | \n",
" yes | \n",
" Z91 | \n",
" KBC | \n",
" Z91 | \n",
" NaN | \n",
" http://en.wikipedia.org/wiki/Birch_Creek_Airport | \n",
" NaN | \n",
"
\n",
" \n",
" 51864 | \n",
" 26366 | \n",
" Z92 | \n",
" small_airport | \n",
" Harsens Island Airport | \n",
" 42.589699 | \n",
" -82.576401 | \n",
" 578.0 | \n",
" NaN | \n",
" US | \n",
" US-MI | \n",
" Harsens Island | \n",
" no | \n",
" Z92 | \n",
" NaN | \n",
" Z92 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 51865 | \n",
" 26367 | \n",
" Z93 | \n",
" small_airport | \n",
" Copper Center 2 Airport | \n",
" 61.941200 | \n",
" -145.294006 | \n",
" 1150.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Copper Center | \n",
" no | \n",
" Z93 | \n",
" CZC | \n",
" Z93 | \n",
" NaN | \n",
" http://en.wikipedia.org/wiki/Copper_Center_Air... | \n",
" NaN | \n",
"
\n",
" \n",
" 51866 | \n",
" 26368 | \n",
" Z95 | \n",
" small_airport | \n",
" Cibecue Airport | \n",
" 34.003300 | \n",
" -110.444000 | \n",
" 5037.0 | \n",
" NaN | \n",
" US | \n",
" US-AZ | \n",
" Cibecue | \n",
" no | \n",
" Z95 | \n",
" NaN | \n",
" Z95 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 52170 | \n",
" 26369 | \n",
" ZNC | \n",
" small_airport | \n",
" Nyac Airport | \n",
" 60.980701 | \n",
" -159.994003 | \n",
" 460.0 | \n",
" NaN | \n",
" US | \n",
" US-AK | \n",
" Nyac | \n",
" no | \n",
" ZNC | \n",
" ZNC | \n",
" ZNC | \n",
" NaN | \n",
" http://en.wikipedia.org/wiki/Nyac_Airport | \n",
" NaN | \n",
"
\n",
" \n",
"
\n",
"
20072 rows × 18 columns
\n",
"
"
],
"text/plain": [
" id ident type \\\n",
"0 6523 00A heliport \n",
"1 323361 00AA small_airport \n",
"2 6524 00AK small_airport \n",
"3 6525 00AL small_airport \n",
"5 322127 00AS small_airport \n",
"6 6527 00AZ small_airport \n",
"7 6528 00CA small_airport \n",
"8 324424 00CL small_airport \n",
"9 322658 00CN heliport \n",
"11 6531 00FA small_airport \n",
"12 6532 00FD heliport \n",
"13 6533 00FL small_airport \n",
"14 6534 00GA small_airport \n",
"15 6535 00GE heliport \n",
"16 6536 00HI heliport \n",
"17 6537 00ID small_airport \n",
"18 322581 00IG small_airport \n",
"19 6538 00II heliport \n",
"20 6539 00IL small_airport \n",
"21 6540 00IN heliport \n",
"22 6541 00IS small_airport \n",
"23 6542 00KS small_airport \n",
"24 6543 00KY small_airport \n",
"25 45437 00LA heliport \n",
"26 6544 00LL heliport \n",
"27 6545 00LS small_airport \n",
"28 6546 00MD small_airport \n",
"29 6547 00MI heliport \n",
"30 6548 00MN small_airport \n",
"31 6549 00MO small_airport \n",
"... ... ... ... \n",
"50033 26331 Y58 small_airport \n",
"50034 26332 Y63 small_airport \n",
"50035 26333 Y71 small_airport \n",
"50036 26334 Y73 small_airport \n",
"50037 26335 Y76 small_airport \n",
"50038 26336 Y77 small_airport \n",
"50039 26337 Y87 small_airport \n",
"50040 26338 Y88 small_airport \n",
"50041 26339 Y91 small_airport \n",
"50042 26340 Y96 small_airport \n",
"50043 26341 Y98 small_airport \n",
"50044 26342 Y99 small_airport \n",
"51839 26345 Z13 small_airport \n",
"51840 26346 Z14 small_airport \n",
"51841 26347 Z17 small_airport \n",
"51846 45975 Z25 small_airport \n",
"51848 26350 Z40 small_airport \n",
"51849 26351 Z41 small_airport \n",
"51851 26353 Z47 small_airport \n",
"51852 26354 Z48 small_airport \n",
"51853 26355 Z52 small_airport \n",
"51854 26356 Z55 small_airport \n",
"51859 26361 Z81 small_airport \n",
"51860 26362 Z86 small_airport \n",
"51862 26364 Z90 small_airport \n",
"51863 26365 Z91 small_airport \n",
"51864 26366 Z92 small_airport \n",
"51865 26367 Z93 small_airport \n",
"51866 26368 Z95 small_airport \n",
"52170 26369 ZNC small_airport \n",
"\n",
" name latitude_deg \\\n",
"0 Total Rf Heliport 40.070801 \n",
"1 Aero B Ranch Airport 38.704022 \n",
"2 Lowell Field 59.949200 \n",
"3 Epps Airpark 34.864799 \n",
"5 Fulton Airport 34.942803 \n",
"6 Cordes Airport 34.305599 \n",
"7 Goldstone /Gts/ Airport 35.350498 \n",
"8 Williams Ag Airport 39.427188 \n",
"9 Kitchen Creek Helibase Heliport 32.727374 \n",
"11 Grass Patch Airport 28.645500 \n",
"12 Ringhaver Heliport 28.846600 \n",
"13 River Oak Airport 27.230900 \n",
"14 Lt World Airport 33.767502 \n",
"15 Caffrey Heliport 33.884201 \n",
"16 Kaupulehu Heliport 19.832500 \n",
"17 Delta Shores Airport 48.145302 \n",
"18 Goltl Airport 39.724028 \n",
"19 Bailey Generation Station Heliport 41.644501 \n",
"20 Hammer Airport 41.978401 \n",
"21 St Mary Medical Center Heliport 41.511398 \n",
"22 Hayenga's Cant Find Farms Airport 40.025600 \n",
"23 Hayden Farm Airport 38.727798 \n",
"24 Robbins Roost Airport 37.409401 \n",
"25 Shell Chemical East Site Heliport 30.191944 \n",
"26 Ac & R Components Heliport 39.665298 \n",
"27 Lejeune Airport 30.136299 \n",
"28 Slater Field 38.757099 \n",
"29 Dow Chemical Heliport 43.949402 \n",
"30 Battle Lake Municipal Airport 46.299999 \n",
"31 Cooper Flying Service Airport 37.202801 \n",
"... ... ... \n",
"50033 Sleepy Eye Municipal Airport 44.250000 \n",
"50034 Elbow Lake Municipal - Pride of the Prairie Ai... 45.986099 \n",
"50035 Elgin Municipal Airport 46.382801 \n",
"50036 Stambaugh Airport 46.078800 \n",
"50037 Morningstar Field 41.655499 \n",
"50038 Bayfield County Airport 46.576302 \n",
"50039 Empire Airport 44.787498 \n",
"50040 Green Lake Airport 44.606098 \n",
"50041 Home Acres Sky Ranch Airport 44.320301 \n",
"50042 Leo E. Goetz County Airport 45.370800 \n",
"50043 Grand Marais Airport 46.620800 \n",
"50044 Trulson Field 48.030602 \n",
"51839 Akiachak Airport 60.904800 \n",
"51840 Tazlina Airport 62.064800 \n",
"51841 Ophir Airport 63.146000 \n",
"51846 Tripod Airport 59.263146 \n",
"51848 Goose Bay Airport 61.394501 \n",
"51849 Lake Hood Strip 61.186901 \n",
"51851 Basin Creek Airport 64.679802 \n",
"51852 Bear Creek 3 Airport 63.573316 \n",
"51853 Johnsons Landing Airport 56.036701 \n",
"51854 Lake Louise Airport 62.293701 \n",
"51859 Salmon Lake Airport 64.909203 \n",
"51860 Clearwater Airport 63.026798 \n",
"51862 Stampede Airport 63.748699 \n",
"51863 Birch Creek Airport 66.274002 \n",
"51864 Harsens Island Airport 42.589699 \n",
"51865 Copper Center 2 Airport 61.941200 \n",
"51866 Cibecue Airport 34.003300 \n",
"52170 Nyac Airport 60.980701 \n",
"\n",
" longitude_deg elevation_ft continent iso_country iso_region \\\n",
"0 -74.933601 11.0 NaN US US-PA \n",
"1 -101.473911 3435.0 NaN US US-KS \n",
"2 -151.695999 450.0 NaN US US-AK \n",
"3 -86.770302 820.0 NaN US US-AL \n",
"5 -97.818019 1100.0 NaN US US-OK \n",
"6 -112.165001 3810.0 NaN US US-AZ \n",
"7 -116.888000 3038.0 NaN US US-CA \n",
"8 -121.763427 87.0 NaN US US-CA \n",
"9 -116.459742 3350.0 NaN US US-CA \n",
"11 -82.219002 53.0 NaN US US-FL \n",
"12 -82.345398 25.0 NaN US US-FL \n",
"13 -80.969200 35.0 NaN US US-FL \n",
"14 -84.068298 700.0 NaN US US-GA \n",
"15 -84.733902 957.0 NaN US US-GA \n",
"16 -155.981995 43.0 NaN US US-HI \n",
"17 -116.213997 2064.0 NaN US US-ID \n",
"18 -101.395994 3359.0 NaN US US-KS \n",
"19 -87.122803 600.0 NaN US US-IN \n",
"20 -89.560402 840.0 NaN US US-IL \n",
"21 -87.260597 634.0 NaN US US-IN \n",
"22 -89.122902 820.0 NaN US US-IL \n",
"23 -94.930496 1100.0 NaN US US-KS \n",
"24 -84.619698 1265.0 NaN US US-KY \n",
"25 -90.980833 15.0 NaN US US-LA \n",
"26 -89.705597 600.0 NaN US US-IL \n",
"27 -92.429398 12.0 NaN US US-LA \n",
"28 -75.753799 45.0 NaN US US-MD \n",
"29 -86.416702 588.0 NaN US US-MI \n",
"30 -95.700302 1365.0 NaN US US-MN \n",
"31 -94.412399 970.0 NaN US US-MO \n",
"... ... ... ... ... ... \n",
"50033 -94.716904 1004.0 NaN US US-MN \n",
"50034 -95.991997 1205.0 NaN US US-MN \n",
"50035 -101.845001 2355.0 NaN US US-ND \n",
"50036 -88.635498 1622.0 NaN US US-MI \n",
"50037 -93.644096 805.0 NaN US US-IA \n",
"50038 -91.458504 1143.0 NaN US US-WI \n",
"50039 -86.004303 944.0 NaN US US-MI \n",
"50040 -85.758400 866.0 NaN US US-MI \n",
"50041 -85.171204 1247.0 NaN US US-MI \n",
"50042 -84.224998 830.0 NaN US US-MI \n",
"50043 -85.916801 838.0 NaN US US-MI \n",
"50044 -101.953003 2105.0 NaN US US-ND \n",
"51839 -161.421997 23.0 NaN US US-AK \n",
"51840 -146.460999 2450.0 NaN US US-AK \n",
"51841 -156.529999 575.0 NaN US US-AK \n",
"51846 -158.557755 225.0 NaN US US-AK \n",
"51848 -149.845993 78.0 NaN US US-AK \n",
"51849 -149.964996 73.0 NaN US US-AK \n",
"51851 -165.300003 140.0 NaN US US-AK \n",
"51852 -156.149454 740.0 NaN US US-AK \n",
"51853 -160.266006 130.0 NaN US US-AK \n",
"51854 -146.578995 2450.0 NaN US US-AK \n",
"51859 -165.013000 490.0 NaN US US-AK \n",
"51860 -147.179001 2900.0 NaN US US-AK \n",
"51862 -150.330002 1850.0 NaN US US-AK \n",
"51863 -145.824005 450.0 NaN US US-AK \n",
"51864 -82.576401 578.0 NaN US US-MI \n",
"51865 -145.294006 1150.0 NaN US US-AK \n",
"51866 -110.444000 5037.0 NaN US US-AZ \n",
"52170 -159.994003 460.0 NaN US US-AK \n",
"\n",
" municipality scheduled_service gps_code iata_code local_code \\\n",
"0 Bensalem no 00A NaN 00A \n",
"1 Leoti no 00AA NaN 00AA \n",
"2 Anchor Point no 00AK NaN 00AK \n",
"3 Harvest no 00AL NaN 00AL \n",
"5 Alex no 00AS NaN 00AS \n",
"6 Cordes no 00AZ NaN 00AZ \n",
"7 Barstow no 00CA NaN 00CA \n",
"8 Biggs no 00CL NaN 00CL \n",
"9 Pine Valley no 00CN NaN 00CN \n",
"11 Bushnell no 00FA NaN 00FA \n",
"12 Riverview no 00FD NaN 00FD \n",
"13 Okeechobee no 00FL NaN 00FL \n",
"14 Lithonia no 00GA NaN 00GA \n",
"15 Hiram no 00GE NaN 00GE \n",
"16 Kailua/Kona no 00HI NaN 00HI \n",
"17 Clark Fork no 00ID NaN 00ID \n",
"18 McDonald no 00IG NaN 00IG \n",
"19 Chesterton no 00II NaN 00II \n",
"20 Polo no 00IL NaN 00IL \n",
"21 Hobart no 00IN NaN 00IN \n",
"22 Kings no 00IS NaN 00IS \n",
"23 Gardner no 00KS NaN 00KS \n",
"24 Stanford no 00KY NaN 00KY \n",
"25 Gonzales no 00LA NaN 00LA \n",
"26 Chatham no 00LL NaN 00LL \n",
"27 Esterwood no 00LS NaN 00LS \n",
"28 Federalsburg no 00MD NaN 00MD \n",
"29 Ludington no 00MI NaN 00MI \n",
"30 Battle Lake no 00MN NaN 00MN \n",
"31 Alba no 00MO NaN 00MO \n",
"... ... ... ... ... ... \n",
"50033 Sleepy Eye no Y58 NaN Y58 \n",
"50034 Elbow Lake no Y63 NaN Y63 \n",
"50035 Elgin no Y71 NaN Y71 \n",
"50036 Iron River no Y73 NaN Y73 \n",
"50037 Des Moines no Y76 NaN Y76 \n",
"50038 Iron River no Y77 NaN Y77 \n",
"50039 Empire no Y87 NaN Y87 \n",
"50040 Interlochen no Y88 NaN Y88 \n",
"50041 Lake City no Y91 NaN Y91 \n",
"50042 Onaway no Y96 NaN Y96 \n",
"50043 Grand Marais no Y98 NaN Y98 \n",
"50044 Plaza no Y99 NaN Y99 \n",
"51839 Akiachak yes Z13 KKI Z13 \n",
"51840 Tazlina no Z14 NaN Z14 \n",
"51841 Ophir no Z17 NaN Z17 \n",
"51846 Aleknagik no Z25 NaN Z25 \n",
"51848 Goose Bay no Z40 NaN Z40 \n",
"51849 Anchorage no Z41 NaN Z41 \n",
"51851 Basin Creek no Z47 NaN Z47 \n",
"51852 Bear Creek no Z48 BCC Z48 \n",
"51853 Bear Lake no Z52 NaN Z52 \n",
"51854 Lake Louise no Z55 NaN Z55 \n",
"51859 Salmon Lake no Z81 NaN Z81 \n",
"51860 Clearwater no Z86 NaN Z86 \n",
"51862 Kantishna no Z90 NaN Z90 \n",
"51863 Birch Creek yes Z91 KBC Z91 \n",
"51864 Harsens Island no Z92 NaN Z92 \n",
"51865 Copper Center no Z93 CZC Z93 \n",
"51866 Cibecue no Z95 NaN Z95 \n",
"52170 Nyac no ZNC ZNC ZNC \n",
"\n",
" home_link wikipedia_link keywords \n",
"0 NaN NaN NaN \n",
"1 NaN NaN NaN \n",
"2 NaN NaN NaN \n",
"3 NaN NaN NaN \n",
"5 NaN NaN NaN \n",
"6 NaN NaN NaN \n",
"7 NaN NaN NaN \n",
"8 NaN NaN NaN \n",
"9 NaN NaN NaN \n",
"11 NaN NaN NaN \n",
"12 NaN NaN NaN \n",
"13 NaN NaN NaN \n",
"14 NaN NaN NaN \n",
"15 NaN NaN NaN \n",
"16 NaN NaN NaN \n",
"17 NaN NaN NaN \n",
"18 NaN NaN NaN \n",
"19 NaN NaN NaN \n",
"20 NaN NaN NaN \n",
"21 NaN NaN NaN \n",
"22 NaN NaN NaN \n",
"23 NaN NaN NaN \n",
"24 NaN NaN NaN \n",
"25 NaN NaN NaN \n",
"26 NaN NaN NaN \n",
"27 NaN NaN NaN \n",
"28 NaN NaN NaN \n",
"29 NaN NaN NaN \n",
"30 NaN NaN NaN \n",
"31 NaN NaN 5K8 \n",
"... ... ... ... \n",
"50033 NaN NaN NaN \n",
"50034 NaN NaN NaN \n",
"50035 NaN NaN NaN \n",
"50036 NaN NaN NaN \n",
"50037 NaN NaN NaN \n",
"50038 NaN NaN NaN \n",
"50039 NaN NaN NaN \n",
"50040 NaN NaN NaN \n",
"50041 NaN NaN NaN \n",
"50042 NaN NaN NaN \n",
"50043 NaN NaN NaN \n",
"50044 NaN NaN NaN \n",
"51839 NaN https://en.wikipedia.org/wiki/Akiachak_Airport NaN \n",
"51840 NaN NaN NaN \n",
"51841 NaN NaN NaN \n",
"51846 NaN NaN NaN \n",
"51848 NaN NaN NaN \n",
"51849 NaN NaN NaN \n",
"51851 NaN NaN NaN \n",
"51852 NaN http://en.wikipedia.org/wiki/Bear_Creek_3_Airport NaN \n",
"51853 NaN NaN NaN \n",
"51854 NaN NaN NaN \n",
"51859 NaN NaN NaN \n",
"51860 NaN NaN NaN \n",
"51862 NaN NaN NaN \n",
"51863 NaN http://en.wikipedia.org/wiki/Birch_Creek_Airport NaN \n",
"51864 NaN NaN NaN \n",
"51865 NaN http://en.wikipedia.org/wiki/Copper_Center_Air... NaN \n",
"51866 NaN NaN NaN \n",
"52170 NaN http://en.wikipedia.org/wiki/Nyac_Airport NaN \n",
"\n",
"[20072 rows x 18 columns]"
]
},
"execution_count": 24,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"step2"
]
},
{
"cell_type": "code",
"execution_count": 25,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"\"\\nselect airport_ident, type, description, frequency_mhz\\nfrom airport_freq\\njoin airports\\non airport_freq.airport_ref = airports.id\\nwhere airports.ident = 'KLAX'\\n\""
]
},
"execution_count": 25,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"\"\"\"\n",
"select airport_ident, type, description, frequency_mhz\n",
"from airport_freq\n",
"join airports\n",
"on airport_freq.airport_ref = airports.id\n",
"where airports.ident = 'KLAX'\n",
"\"\"\""
]
},
{
"cell_type": "code",
"execution_count": 26,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" airport_ident | \n",
" type | \n",
" description | \n",
" frequency_mhz | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" KLAX | \n",
" APP | \n",
" SOCAL APP | \n",
" 36.07 | \n",
"
\n",
" \n",
" 1 | \n",
" KLAX | \n",
" APP | \n",
" SOCAL APP | \n",
" 124.30 | \n",
"
\n",
" \n",
" 2 | \n",
" KLAX | \n",
" ATIS | \n",
" ATIS | \n",
" 133.80 | \n",
"
\n",
" \n",
" 3 | \n",
" KLAX | \n",
" CLD | \n",
" CLNC DEL | \n",
" 121.40 | \n",
"
\n",
" \n",
" 4 | \n",
" KLAX | \n",
" DEP | \n",
" SOCAL DEP | \n",
" 124.30 | \n",
"
\n",
" \n",
" 5 | \n",
" KLAX | \n",
" GND | \n",
" GND | \n",
" 121.65 | \n",
"
\n",
" \n",
" 6 | \n",
" KLAX | \n",
" MISC | \n",
" CG | \n",
" 34.50 | \n",
"
\n",
" \n",
" 7 | \n",
" KLAX | \n",
" MISC | \n",
" CG | \n",
" 898.40 | \n",
"
\n",
" \n",
" 8 | \n",
" KLAX | \n",
" OPS | \n",
" AF | \n",
" 37.22 | \n",
"
\n",
" \n",
" 9 | \n",
" KLAX | \n",
" TWR | \n",
" TWR | \n",
" 119.80 | \n",
"
\n",
" \n",
" 10 | \n",
" KLAX | \n",
" UNIC | \n",
" UNICOM | \n",
" 122.95 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" airport_ident type description frequency_mhz\n",
"0 KLAX APP SOCAL APP 36.07\n",
"1 KLAX APP SOCAL APP 124.30\n",
"2 KLAX ATIS ATIS 133.80\n",
"3 KLAX CLD CLNC DEL 121.40\n",
"4 KLAX DEP SOCAL DEP 124.30\n",
"5 KLAX GND GND 121.65\n",
"6 KLAX MISC CG 34.50\n",
"7 KLAX MISC CG 898.40\n",
"8 KLAX OPS AF 37.22\n",
"9 KLAX TWR TWR 119.80\n",
"10 KLAX UNIC UNICOM 122.95"
]
},
"execution_count": 26,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"airport_freq.merge(\n",
" airports[airports.ident == 'KLAX'][['id']],\n",
" left_on='airport_ref', right_on='id', how='inner')\\\n",
" [['airport_ident', 'type', 'description', 'frequency_mhz']]"
]
},
{
"cell_type": "code",
"execution_count": 27,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" airport_ident | \n",
" type | \n",
" description | \n",
" frequency_mhz | \n",
" _merge | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" KLAX | \n",
" APP | \n",
" SOCAL APP | \n",
" 36.07 | \n",
" both | \n",
"
\n",
" \n",
" 1 | \n",
" KLAX | \n",
" APP | \n",
" SOCAL APP | \n",
" 124.30 | \n",
" both | \n",
"
\n",
" \n",
" 2 | \n",
" KLAX | \n",
" ATIS | \n",
" ATIS | \n",
" 133.80 | \n",
" both | \n",
"
\n",
" \n",
" 3 | \n",
" KLAX | \n",
" CLD | \n",
" CLNC DEL | \n",
" 121.40 | \n",
" both | \n",
"
\n",
" \n",
" 4 | \n",
" KLAX | \n",
" DEP | \n",
" SOCAL DEP | \n",
" 124.30 | \n",
" both | \n",
"
\n",
" \n",
" 5 | \n",
" KLAX | \n",
" GND | \n",
" GND | \n",
" 121.65 | \n",
" both | \n",
"
\n",
" \n",
" 6 | \n",
" KLAX | \n",
" MISC | \n",
" CG | \n",
" 34.50 | \n",
" both | \n",
"
\n",
" \n",
" 7 | \n",
" KLAX | \n",
" MISC | \n",
" CG | \n",
" 898.40 | \n",
" both | \n",
"
\n",
" \n",
" 8 | \n",
" KLAX | \n",
" OPS | \n",
" AF | \n",
" 37.22 | \n",
" both | \n",
"
\n",
" \n",
" 9 | \n",
" KLAX | \n",
" TWR | \n",
" TWR | \n",
" 119.80 | \n",
" both | \n",
"
\n",
" \n",
" 10 | \n",
" KLAX | \n",
" UNIC | \n",
" UNICOM | \n",
" 122.95 | \n",
" both | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" airport_ident type description frequency_mhz _merge\n",
"0 KLAX APP SOCAL APP 36.07 both\n",
"1 KLAX APP SOCAL APP 124.30 both\n",
"2 KLAX ATIS ATIS 133.80 both\n",
"3 KLAX CLD CLNC DEL 121.40 both\n",
"4 KLAX DEP SOCAL DEP 124.30 both\n",
"5 KLAX GND GND 121.65 both\n",
"6 KLAX MISC CG 34.50 both\n",
"7 KLAX MISC CG 898.40 both\n",
"8 KLAX OPS AF 37.22 both\n",
"9 KLAX TWR TWR 119.80 both\n",
"10 KLAX UNIC UNICOM 122.95 both"
]
},
"execution_count": 27,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"airport_freq.merge(\n",
" airports[airports.ident == 'KLAX'][['id']],\n",
" left_on='airport_ref', right_on='id', how='inner', indicator=True)\\\n",
" [['airport_ident', 'type', 'description', 'frequency_mhz', '_merge']]"
]
},
{
"cell_type": "code",
"execution_count": 28,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" name | \n",
" municipality | \n",
"
\n",
" \n",
" \n",
" \n",
" 27322 | \n",
" Los Angeles International Airport | \n",
" Los Angeles | \n",
"
\n",
" \n",
" 27347 | \n",
" Long Beach /Daugherty Field/ Airport | \n",
" Long Beach | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" name municipality\n",
"27322 Los Angeles International Airport Los Angeles\n",
"27347 Long Beach /Daugherty Field/ Airport Long Beach"
]
},
"execution_count": 28,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"\"\"\"\n",
"select name, municipality from airports\n",
"where ident = 'KLAX'\n",
"union all\n",
"select name, municipality from airports\n",
"where ident = 'KLGB'\n",
"\"\"\"\n",
"pd.concat([\n",
" airports[airports.ident == 'KLAX'][['name', 'municipality']],\n",
" airports[airports.ident == 'KLGB'][['name', 'municipality']]])"
]
},
{
"cell_type": "code",
"execution_count": 29,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" ident | \n",
" type | \n",
" name | \n",
" latitude_deg | \n",
" longitude_deg | \n",
" elevation_ft | \n",
" continent | \n",
" iso_country | \n",
" iso_region | \n",
" municipality | \n",
" scheduled_service | \n",
" gps_code | \n",
" iata_code | \n",
" local_code | \n",
" home_link | \n",
" wikipedia_link | \n",
" keywords | \n",
"
\n",
" \n",
" \n",
" \n",
" 27322 | \n",
" 3632 | \n",
" KLAX | \n",
" large_airport | \n",
" Los Angeles International Airport | \n",
" 33.942501 | \n",
" -118.407997 | \n",
" 125.0 | \n",
" NaN | \n",
" US | \n",
" US-CA | \n",
" Los Angeles | \n",
" yes | \n",
" KLAX | \n",
" LAX | \n",
" LAX | \n",
" http://www.iflylax.com/ | \n",
" http://en.wikipedia.org/wiki/Los_Angeles_Inter... | \n",
" NaN | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" id ident type name \\\n",
"27322 3632 KLAX large_airport Los Angeles International Airport \n",
"\n",
" latitude_deg longitude_deg elevation_ft continent iso_country \\\n",
"27322 33.942501 -118.407997 125.0 NaN US \n",
"\n",
" iso_region municipality scheduled_service gps_code iata_code local_code \\\n",
"27322 US-CA Los Angeles yes KLAX LAX LAX \n",
"\n",
" home_link \\\n",
"27322 http://www.iflylax.com/ \n",
"\n",
" wikipedia_link keywords \n",
"27322 http://en.wikipedia.org/wiki/Los_Angeles_Inter... NaN "
]
},
"execution_count": 29,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"airports[airports.ident == 'KLAX']"
]
},
{
"cell_type": "code",
"execution_count": 30,
"metadata": {},
"outputs": [],
"source": [
"\"\"\"\n",
"update airports\n",
"set home_link = 'http://www.lawa.org/welcomelax.aspx'\n",
"where ident == 'KLAX'\n",
"\"\"\"\n",
"airports.loc[\n",
" airports['ident'] == 'KLAX', 'home_link'] = \\\n",
" 'http://www.lawa.org/welcomelax.aspx'"
]
},
{
"cell_type": "code",
"execution_count": 31,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" airport_ref | \n",
" airport_ident | \n",
" type | \n",
" description | \n",
" frequency_mhz | \n",
"
\n",
" \n",
" \n",
" \n",
" 11852 | \n",
" 60767 | \n",
" 3632 | \n",
" KLAX | \n",
" APP | \n",
" SOCAL APP | \n",
" 36.07 | \n",
"
\n",
" \n",
" 11853 | \n",
" 60766 | \n",
" 3632 | \n",
" KLAX | \n",
" APP | \n",
" SOCAL APP | \n",
" 124.30 | \n",
"
\n",
" \n",
" 11854 | \n",
" 60768 | \n",
" 3632 | \n",
" KLAX | \n",
" ATIS | \n",
" ATIS | \n",
" 133.80 | \n",
"
\n",
" \n",
" 11855 | \n",
" 60769 | \n",
" 3632 | \n",
" KLAX | \n",
" CLD | \n",
" CLNC DEL | \n",
" 121.40 | \n",
"
\n",
" \n",
" 11856 | \n",
" 60770 | \n",
" 3632 | \n",
" KLAX | \n",
" DEP | \n",
" SOCAL DEP | \n",
" 124.30 | \n",
"
\n",
" \n",
" 11857 | \n",
" 60771 | \n",
" 3632 | \n",
" KLAX | \n",
" GND | \n",
" GND | \n",
" 121.65 | \n",
"
\n",
" \n",
" 11858 | \n",
" 60772 | \n",
" 3632 | \n",
" KLAX | \n",
" MISC | \n",
" CG | \n",
" 34.50 | \n",
"
\n",
" \n",
" 11859 | \n",
" 60773 | \n",
" 3632 | \n",
" KLAX | \n",
" MISC | \n",
" CG | \n",
" 898.40 | \n",
"
\n",
" \n",
" 11860 | \n",
" 60774 | \n",
" 3632 | \n",
" KLAX | \n",
" OPS | \n",
" AF | \n",
" 37.22 | \n",
"
\n",
" \n",
" 11861 | \n",
" 60775 | \n",
" 3632 | \n",
" KLAX | \n",
" TWR | \n",
" TWR | \n",
" 119.80 | \n",
"
\n",
" \n",
" 11862 | \n",
" 60776 | \n",
" 3632 | \n",
" KLAX | \n",
" UNIC | \n",
" UNICOM | \n",
" 122.95 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" id airport_ref airport_ident type description frequency_mhz\n",
"11852 60767 3632 KLAX APP SOCAL APP 36.07\n",
"11853 60766 3632 KLAX APP SOCAL APP 124.30\n",
"11854 60768 3632 KLAX ATIS ATIS 133.80\n",
"11855 60769 3632 KLAX CLD CLNC DEL 121.40\n",
"11856 60770 3632 KLAX DEP SOCAL DEP 124.30\n",
"11857 60771 3632 KLAX GND GND 121.65\n",
"11858 60772 3632 KLAX MISC CG 34.50\n",
"11859 60773 3632 KLAX MISC CG 898.40\n",
"11860 60774 3632 KLAX OPS AF 37.22\n",
"11861 60775 3632 KLAX TWR TWR 119.80\n",
"11862 60776 3632 KLAX UNIC UNICOM 122.95"
]
},
"execution_count": 31,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"lax_freq = airport_freq[airport_freq.airport_ident == 'KLAX']\n",
"lax_freq"
]
},
{
"cell_type": "code",
"execution_count": 32,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" airport_ref | \n",
" airport_ident | \n",
" type | \n",
" description | \n",
" frequency_mhz | \n",
"
\n",
" \n",
" \n",
" \n",
" 11852 | \n",
" 60767 | \n",
" 3632 | \n",
" KLAX | \n",
" APP | \n",
" SOCAL APP | \n",
" 36.07 | \n",
"
\n",
" \n",
" 11853 | \n",
" 60766 | \n",
" 3632 | \n",
" KLAX | \n",
" APP | \n",
" SOCAL APP | \n",
" 124.30 | \n",
"
\n",
" \n",
" 11854 | \n",
" 60768 | \n",
" 3632 | \n",
" KLAX | \n",
" ATIS | \n",
" ATIS | \n",
" 133.80 | \n",
"
\n",
" \n",
" 11855 | \n",
" 60769 | \n",
" 3632 | \n",
" KLAX | \n",
" CLD | \n",
" CLNC DEL | \n",
" 121.40 | \n",
"
\n",
" \n",
" 11856 | \n",
" 60770 | \n",
" 3632 | \n",
" KLAX | \n",
" DEP | \n",
" SOCAL DEP | \n",
" 124.30 | \n",
"
\n",
" \n",
" 11857 | \n",
" 60771 | \n",
" 3632 | \n",
" KLAX | \n",
" GND | \n",
" GND | \n",
" 121.65 | \n",
"
\n",
" \n",
" 11860 | \n",
" 60774 | \n",
" 3632 | \n",
" KLAX | \n",
" OPS | \n",
" AF | \n",
" 37.22 | \n",
"
\n",
" \n",
" 11861 | \n",
" 60775 | \n",
" 3632 | \n",
" KLAX | \n",
" TWR | \n",
" TWR | \n",
" 119.80 | \n",
"
\n",
" \n",
" 11862 | \n",
" 60776 | \n",
" 3632 | \n",
" KLAX | \n",
" UNIC | \n",
" UNICOM | \n",
" 122.95 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" id airport_ref airport_ident type description frequency_mhz\n",
"11852 60767 3632 KLAX APP SOCAL APP 36.07\n",
"11853 60766 3632 KLAX APP SOCAL APP 124.30\n",
"11854 60768 3632 KLAX ATIS ATIS 133.80\n",
"11855 60769 3632 KLAX CLD CLNC DEL 121.40\n",
"11856 60770 3632 KLAX DEP SOCAL DEP 124.30\n",
"11857 60771 3632 KLAX GND GND 121.65\n",
"11860 60774 3632 KLAX OPS AF 37.22\n",
"11861 60775 3632 KLAX TWR TWR 119.80\n",
"11862 60776 3632 KLAX UNIC UNICOM 122.95"
]
},
"execution_count": 32,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"lax_freq = lax_freq[lax_freq.type != 'MISC']\n",
"lax_freq"
]
},
{
"cell_type": "code",
"execution_count": 33,
"metadata": {},
"outputs": [],
"source": [
"# Alternatively, DROP rows by index"
]
},
{
"cell_type": "code",
"execution_count": 34,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" airport_ref | \n",
" airport_ident | \n",
" type | \n",
" description | \n",
" frequency_mhz | \n",
"
\n",
" \n",
" \n",
" \n",
" 11852 | \n",
" 60767 | \n",
" 3632 | \n",
" KLAX | \n",
" APP | \n",
" SOCAL APP | \n",
" 36.07 | \n",
"
\n",
" \n",
" 11853 | \n",
" 60766 | \n",
" 3632 | \n",
" KLAX | \n",
" APP | \n",
" SOCAL APP | \n",
" 124.30 | \n",
"
\n",
" \n",
" 11854 | \n",
" 60768 | \n",
" 3632 | \n",
" KLAX | \n",
" ATIS | \n",
" ATIS | \n",
" 133.80 | \n",
"
\n",
" \n",
" 11855 | \n",
" 60769 | \n",
" 3632 | \n",
" KLAX | \n",
" CLD | \n",
" CLNC DEL | \n",
" 121.40 | \n",
"
\n",
" \n",
" 11856 | \n",
" 60770 | \n",
" 3632 | \n",
" KLAX | \n",
" DEP | \n",
" SOCAL DEP | \n",
" 124.30 | \n",
"
\n",
" \n",
" 11857 | \n",
" 60771 | \n",
" 3632 | \n",
" KLAX | \n",
" GND | \n",
" GND | \n",
" 121.65 | \n",
"
\n",
" \n",
" 11860 | \n",
" 60774 | \n",
" 3632 | \n",
" KLAX | \n",
" OPS | \n",
" AF | \n",
" 37.22 | \n",
"
\n",
" \n",
" 11861 | \n",
" 60775 | \n",
" 3632 | \n",
" KLAX | \n",
" TWR | \n",
" TWR | \n",
" 119.80 | \n",
"
\n",
" \n",
" 11862 | \n",
" 60776 | \n",
" 3632 | \n",
" KLAX | \n",
" UNIC | \n",
" UNICOM | \n",
" 122.95 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" id airport_ref airport_ident type description frequency_mhz\n",
"11852 60767 3632 KLAX APP SOCAL APP 36.07\n",
"11853 60766 3632 KLAX APP SOCAL APP 124.30\n",
"11854 60768 3632 KLAX ATIS ATIS 133.80\n",
"11855 60769 3632 KLAX CLD CLNC DEL 121.40\n",
"11856 60770 3632 KLAX DEP SOCAL DEP 124.30\n",
"11857 60771 3632 KLAX GND GND 121.65\n",
"11860 60774 3632 KLAX OPS AF 37.22\n",
"11861 60775 3632 KLAX TWR TWR 119.80\n",
"11862 60776 3632 KLAX UNIC UNICOM 122.95"
]
},
"execution_count": 34,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"lax_freq.drop(lax_freq[lax_freq.type == 'MISC'].index)"
]
},
{
"cell_type": "code",
"execution_count": 35,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" airport_ref | \n",
" airport_ident | \n",
" type | \n",
" description | \n",
" frequency_mhz | \n",
"
\n",
" \n",
" \n",
" \n",
" 11852 | \n",
" 60767 | \n",
" 3632 | \n",
" KLAX | \n",
" APP | \n",
" SOCAL APP | \n",
" 36.07 | \n",
"
\n",
" \n",
" 11853 | \n",
" 60766 | \n",
" 3632 | \n",
" KLAX | \n",
" APP | \n",
" SOCAL APP | \n",
" 124.30 | \n",
"
\n",
" \n",
" 11854 | \n",
" 60768 | \n",
" 3632 | \n",
" KLAX | \n",
" ATIS | \n",
" ATIS | \n",
" 133.80 | \n",
"
\n",
" \n",
" 11855 | \n",
" 60769 | \n",
" 3632 | \n",
" KLAX | \n",
" CLD | \n",
" CLNC DEL | \n",
" 121.40 | \n",
"
\n",
" \n",
" 11856 | \n",
" 60770 | \n",
" 3632 | \n",
" KLAX | \n",
" DEP | \n",
" SOCAL DEP | \n",
" 124.30 | \n",
"
\n",
" \n",
" 11857 | \n",
" 60771 | \n",
" 3632 | \n",
" KLAX | \n",
" GND | \n",
" GND | \n",
" 121.65 | \n",
"
\n",
" \n",
" 11862 | \n",
" 60776 | \n",
" 3632 | \n",
" KLAX | \n",
" UNIC | \n",
" UNICOM | \n",
" 122.95 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" id airport_ref airport_ident type description frequency_mhz\n",
"11852 60767 3632 KLAX APP SOCAL APP 36.07\n",
"11853 60766 3632 KLAX APP SOCAL APP 124.30\n",
"11854 60768 3632 KLAX ATIS ATIS 133.80\n",
"11855 60769 3632 KLAX CLD CLNC DEL 121.40\n",
"11856 60770 3632 KLAX DEP SOCAL DEP 124.30\n",
"11857 60771 3632 KLAX GND GND 121.65\n",
"11862 60776 3632 KLAX UNIC UNICOM 122.95"
]
},
"execution_count": 35,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"lax_freq.drop(lax_freq.index[6:8])"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Top N"
]
},
{
"cell_type": "code",
"execution_count": 36,
"metadata": {},
"outputs": [],
"source": [
"# prepare the data first: counts of airports by country\n",
"by_country = airports\\\n",
" .groupby('iso_country')\\\n",
" .size()\\\n",
" .to_frame('airport_count')\\\n",
" .reset_index()"
]
},
{
"cell_type": "code",
"execution_count": 37,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" iso_country | \n",
" airport_count | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AD | \n",
" 2 | \n",
"
\n",
" \n",
" 1 | \n",
" AE | \n",
" 52 | \n",
"
\n",
" \n",
" 2 | \n",
" AF | \n",
" 62 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" iso_country airport_count\n",
"0 AD 2\n",
"1 AE 52\n",
"2 AF 62"
]
},
"execution_count": 37,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"by_country.head(3)"
]
},
{
"cell_type": "code",
"execution_count": 38,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" iso_country | \n",
" airport_count | \n",
"
\n",
" \n",
" \n",
" \n",
" 224 | \n",
" US | \n",
" 22209 | \n",
"
\n",
" \n",
" 29 | \n",
" BR | \n",
" 4186 | \n",
"
\n",
" \n",
" 35 | \n",
" CA | \n",
" 2771 | \n",
"
\n",
" \n",
" 12 | \n",
" AU | \n",
" 1955 | \n",
"
\n",
" \n",
" 184 | \n",
" RU | \n",
" 1019 | \n",
"
\n",
" \n",
" 54 | \n",
" DE | \n",
" 933 | \n",
"
\n",
" \n",
" 74 | \n",
" GB | \n",
" 848 | \n",
"
\n",
" \n",
" 72 | \n",
" FR | \n",
" 831 | \n",
"
\n",
" \n",
" 9 | \n",
" AR | \n",
" 827 | \n",
"
\n",
" \n",
" 152 | \n",
" MX | \n",
" 765 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" iso_country airport_count\n",
"224 US 22209\n",
"29 BR 4186\n",
"35 CA 2771\n",
"12 AU 1955\n",
"184 RU 1019\n",
"54 DE 933\n",
"74 GB 848\n",
"72 FR 831\n",
"9 AR 827\n",
"152 MX 765"
]
},
"execution_count": 38,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# top 10\n",
"\"\"\"\n",
"select iso_country from by_country\n",
"order by size desc\n",
"limit 10\n",
"\"\"\"\n",
"by_country.nlargest(10, columns='airport_count')"
]
},
{
"cell_type": "code",
"execution_count": 39,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" iso_country | \n",
" airport_count | \n",
"
\n",
" \n",
" \n",
" \n",
" 46 | \n",
" CO | \n",
" 706 | \n",
"
\n",
" \n",
" 105 | \n",
" IT | \n",
" 663 | \n",
"
\n",
" \n",
" 229 | \n",
" VE | \n",
" 592 | \n",
"
\n",
" \n",
" 170 | \n",
" PG | \n",
" 589 | \n",
"
\n",
" \n",
" 117 | \n",
" KR | \n",
" 539 | \n",
"
\n",
" \n",
" 239 | \n",
" ZA | \n",
" 480 | \n",
"
\n",
" \n",
" 43 | \n",
" CL | \n",
" 474 | \n",
"
\n",
" \n",
" 96 | \n",
" ID | \n",
" 462 | \n",
"
\n",
" \n",
" 65 | \n",
" ES | \n",
" 403 | \n",
"
\n",
" \n",
" 45 | \n",
" CN | \n",
" 382 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" iso_country airport_count\n",
"46 CO 706\n",
"105 IT 663\n",
"229 VE 592\n",
"170 PG 589\n",
"117 KR 539\n",
"239 ZA 480\n",
"43 CL 474\n",
"96 ID 462\n",
"65 ES 403\n",
"45 CN 382"
]
},
"execution_count": 39,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# next 10 after top 10\n",
"\"\"\"\n",
"select iso_country from by_country\n",
"order by size desc\n",
"limit 10 offset 10\n",
"\"\"\"\n",
"by_country.nlargest(10+10, columns='airport_count').tail(10)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Aggregate functions"
]
},
{
"cell_type": "code",
"execution_count": 40,
"metadata": {},
"outputs": [],
"source": [
"runways = pd.read_csv('data/runways.csv', usecols=['airport_ident', 'length_ft', 'surface', 'lighted', 'closed'])\n",
"runways = runways[runways['closed'] == 0]"
]
},
{
"cell_type": "code",
"execution_count": 41,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" airport_ident | \n",
" length_ft | \n",
" surface | \n",
" lighted | \n",
" closed | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 00A | \n",
" 80.0 | \n",
" ASPH-G | \n",
" 1 | \n",
" 0 | \n",
"
\n",
" \n",
" 1 | \n",
" 00AK | \n",
" 2500.0 | \n",
" GRVL | \n",
" 0 | \n",
" 0 | \n",
"
\n",
" \n",
" 2 | \n",
" 00AL | \n",
" 2300.0 | \n",
" TURF | \n",
" 0 | \n",
" 0 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" airport_ident length_ft surface lighted closed\n",
"0 00A 80.0 ASPH-G 1 0\n",
"1 00AK 2500.0 GRVL 0 0\n",
"2 00AL 2300.0 TURF 0 0"
]
},
"execution_count": 41,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"runways.head(3)"
]
},
{
"cell_type": "code",
"execution_count": 42,
"metadata": {},
"outputs": [],
"source": [
"\"\"\"\n",
"For the runways, find out:\n",
"- average length\n",
"- median length\n",
"\n",
"select max(length_ft), min(length_ft), mean(length_ft), median(length_ft)\n",
"from runways\n",
"\"\"\"\n",
"df = runways.agg({\n",
" 'length_ft': ['min', 'max', 'mean', 'median']\n",
"})"
]
},
{
"cell_type": "code",
"execution_count": 43,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" length_ft | \n",
"
\n",
" \n",
" \n",
" \n",
" min | \n",
" 0.000000 | \n",
"
\n",
" \n",
" max | \n",
" 120000.000000 | \n",
"
\n",
" \n",
" mean | \n",
" 3244.993736 | \n",
"
\n",
" \n",
" median | \n",
" 2719.500000 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" length_ft\n",
"min 0.000000\n",
"max 120000.000000\n",
"mean 3244.993736\n",
"median 2719.500000"
]
},
"execution_count": 43,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df"
]
},
{
"cell_type": "code",
"execution_count": 44,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" min | \n",
" max | \n",
" mean | \n",
" median | \n",
"
\n",
" \n",
" \n",
" \n",
" length_ft | \n",
" 0.0 | \n",
" 120000.0 | \n",
" 3244.993736 | \n",
" 2719.5 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" min max mean median\n",
"length_ft 0.0 120000.0 3244.993736 2719.5"
]
},
"execution_count": 44,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.T"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Inserting"
]
},
{
"cell_type": "code",
"execution_count": 45,
"metadata": {},
"outputs": [],
"source": [
"\"\"\"\n",
"create table heroes (id integer, name text);\n",
"\n",
"insert into heroes values (1, 'Harry Potter');\n",
"insert into heroes values (2, 'Ron Weasley');\n",
"insert into heroes values (3, 'Hermione Granger');\n",
"\n",
"\"\"\"\n",
"df1 = pd.DataFrame({'id': [1, 2], 'name': ['Harry Potter', 'Ron Weasley']})"
]
},
{
"cell_type": "code",
"execution_count": 46,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" name | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 1 | \n",
" Harry Potter | \n",
"
\n",
" \n",
" 1 | \n",
" 2 | \n",
" Ron Weasley | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" id name\n",
"0 1 Harry Potter\n",
"1 2 Ron Weasley"
]
},
"execution_count": 46,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df1"
]
},
{
"cell_type": "code",
"execution_count": 60,
"metadata": {},
"outputs": [],
"source": [
"df2 = pd.DataFrame({'id': [3], 'name': ['Hermione Granger']})"
]
},
{
"cell_type": "code",
"execution_count": 64,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" name | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 1 | \n",
" Harry Potter | \n",
"
\n",
" \n",
" 1 | \n",
" 2 | \n",
" Ron Weasley | \n",
"
\n",
" \n",
" 2 | \n",
" 3 | \n",
" Hermione Granger | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" id name\n",
"0 1 Harry Potter\n",
"1 2 Ron Weasley\n",
"2 3 Hermione Granger"
]
},
"execution_count": 64,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.concat([df1, df2]).reset_index(drop=True)"
]
},
{
"cell_type": "code",
"execution_count": 47,
"metadata": {},
"outputs": [],
"source": [
"## Plotting"
]
},
{
"cell_type": "code",
"execution_count": 60,
"metadata": {},
"outputs": [],
"source": [
"%matplotlib inline\n",
"\n",
"top_10 = by_country.nlargest(10, columns='airport_count')"
]
},
{
"cell_type": "code",
"execution_count": 61,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" iso_country | \n",
" airport_count | \n",
"
\n",
" \n",
" \n",
" \n",
" 224 | \n",
" US | \n",
" 22209 | \n",
"
\n",
" \n",
" 29 | \n",
" BR | \n",
" 4186 | \n",
"
\n",
" \n",
" 35 | \n",
" CA | \n",
" 2771 | \n",
"
\n",
" \n",
" 12 | \n",
" AU | \n",
" 1955 | \n",
"
\n",
" \n",
" 184 | \n",
" RU | \n",
" 1019 | \n",
"
\n",
" \n",
" 54 | \n",
" DE | \n",
" 933 | \n",
"
\n",
" \n",
" 74 | \n",
" GB | \n",
" 848 | \n",
"
\n",
" \n",
" 72 | \n",
" FR | \n",
" 831 | \n",
"
\n",
" \n",
" 9 | \n",
" AR | \n",
" 827 | \n",
"
\n",
" \n",
" 152 | \n",
" MX | \n",
" 765 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" iso_country airport_count\n",
"224 US 22209\n",
"29 BR 4186\n",
"35 CA 2771\n",
"12 AU 1955\n",
"184 RU 1019\n",
"54 DE 933\n",
"74 GB 848\n",
"72 FR 831\n",
"9 AR 827\n",
"152 MX 765"
]
},
"execution_count": 61,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"top_10"
]
},
{
"cell_type": "code",
"execution_count": 84,
"metadata": {},
"outputs": [
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAlsAAAGlCAYAAADNtTOFAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMS4wLCBo\ndHRwOi8vbWF0cGxvdGxpYi5vcmcvpW3flQAAIABJREFUeJzt3XuUXGWZ7/FvJ0GQIUAQRBAvIQxP\nOhmuQcClYAiIkejoeIZDwgAeEJGLyB0ZLmM4ggcZYEYZuSgEGMABVGSUm4iAoMIYGgUkxSMGUCIC\nw8hVaCBJnz9qNzRNdVKd9NvVKb6ftbJS9dauvZ+qpzv9y/vu2t3R09ODJEmSyhjV6gIkSZLamWFL\nkiSpIMOWJElSQYYtSZKkggxbkiRJBRm2JEmSChrT6gKkN7OI+DqwfXV3EvAQ8GJ1//2Z+WLDJw7+\nOKsA1wJfz8yrqrG/As4HNqP+H68jM/OHQ3G8Zazxb4EtMvPEBo/9HbB9Zh42DHWMBrqA7aj/G/md\nzNwpIsYArwDjMvPp0nUMUNu43noG8ZxXX09mPleorjnA1zLz7hL7l1Z0hi2phTLzC723I+Jh4B8y\n886hPEZEfAA4C9gY+Hqfh74M/DkzOyPivcAvIuLOzPzTUB5/ELYGVmv0QGZ+H/j+cBSRmYuAzQEi\nYiNgq+E4bpPexiDr6ft6CtqZ139tSerDsCWNYBHxIeBUYBXgZeC4zLwhIvYF/hewErA+sADYKzMf\na7CbLwBfBI7vN/53wKcAMvPhiLgJ2JV+PzQjYj3gXOphbTHwjcz8RkS8m3qIezfQAVyQmWdUAeXO\nzFyzev6r96u6Z1CfSZsAdAN7AWsC+wKjI+JZ4A/V+GrAn4HLgI9l5ier2Z2vAZOr1/9j4OjMXBQR\nJwGfAF4CngQ+nZmP93ktU4DvZub46v6NwB8yc5+IeCvwR+A9wLPAOOACYGxE/JrXQs5JEfF+6sHn\nlMw8p9/7NQZ4BvgG8FFgLHAkMBP4G+AR4BOZ+cIS+rs+8O/AWtVuf5CZs/vWk5mvC1ARMRH4N+Cv\ngHdSn82aCSyimpED/r7B+9rw62gp/b0R+B3wLuDnwNuByyPiH4DxwLHVcRcCR2Tmz5HexDxnSxqh\nImId4ArgoMzcDNgH+Hb1QxDgA8D+mTkJuAf410b7yczdMvP6Bg9tQP0Hf68F1Vh/5wK/ycyJ1TEP\niojxwH8AN2TmptSX3PaOiL9v4qV9CDggM/8GmEv9h/EvgPOASzPzn6rtOoEPNVgy+xpwe2ZOAbag\nHhIOqWo6EJiSmVsBN1GfLev7XnQBoyJiYrWM+tfAtOrhDwM/47VlXIC9gef6BZsHqmPvCpwREY3+\nHV0VeDgzNwG+Vb22g6kvFa8DfGwp/f0ccH9mbkl9mXlSRIwdoJ5e+wHnZeb7qQfZAKY32K7/+zrQ\n19GS+vse4ITMjMzcB3gC2K2alT0N+GzVgxOp91t6UzNsSSPX+6n/wL0TIDPvBf6L1354XZ+Zv6tu\nfwv4yCD33+j7f1GDsZ2Ab1Y1PFX9UP4f6kHm7Gr8aeozMR9t4rhzM/PR6vZdvDZ709/dA5xj9DHq\nge/X1GdvtgQ2oR4c5wF3RcQ/V8dpdA7aVVWdOwA/Ap6JiKA+I/a9Jur/dvX3r4G3Up+5aqR3X/Or\n1/KnzFwMPEz9NS+pv9cBMyPiGuCzwFFNnG91FPB0RHwROAdYl8bLsv3f1zd8HUXE6iy5vy9XtTZy\nGfDDiPgW9ffm9KXULbU9w5Y0cjX6/hxFfckH6ks0fccbBaUleQRYr8/9d1Kf3ervFeDVX6IaEROA\nNagvLTWqraffY2/pt13fmaP+2/b1/ADjo4G/y8zNqxmebYFDM3Mh9RmYfYCngDMjotEP+iuBXaiH\n0x9Xf6ZX938wwDH7eqVP7Syh/pcaPKevAfubmXdQX447D9gQmBsR2yylriuAz1D/kMXpwN0D1Nb/\nfW30dTSqwXP7fu29WAXHN8jML1Lvw13Ue/HziBjoPZLeFAxb0sh1OzA5IrYCiIhNqC/53FI9/uHq\nfCqoLzsN9pOE/0l96Ylq6erDwDUNtvsJ9eWr3k/D3UR9ZqYLOKAaXxPYk3pweQpYtZotgvq5Yc1Y\nyGs/zJfkR8BhEdFRfcryamD/iNiS+jLYfZn5FerLjZs1eP7PqC+lTa9e2w3AEdSXSp9qUNOYQmFh\nwP5WM3PHVB8M+AKQ1Jc8l1TPR4DZmXkF9X/b30c9mC7NG76OqpmsgfrbyEJgpYhYKSJ+Tz0wns1r\nS6eeH6w3NcOWNEJVJ3bvBpwdEfcCFwN7ZuaD1SYLqJ/jU6M+K3X4IA9xArBWRNxHPXAclpkPN9ju\nQGDTiLgbuBX4v9VH/GcB0yPiHuAO4LLMvDgz/wz8I3BDRMzl9TMnS3Ij8KmIaHjuWR8HUT/Z+17q\n4eou4PTMvIv6Jxa7IuJO6uHgiP5Prj6d9yPgqarWW4G1abyE+MfqOPOon3g+ZJbS338Bto6I3wB3\nUg9bV/StJyLW6LfLf6S+fHcn9ZPzfwps1EQpA30dNezvAPu4Cvgu9Rmtw4ErIuIu6ud97Z2ZjWb2\npDeNjp6enqVvJWlEqT7V97HM/GSra9GKy68jaXg4syVJklSQM1uSJEkFObMlSZJUkGFLkiSpIMOW\nJElSQSP62iddXV2eUCZJklYYU6ZMecN18EZ02AKYMmVKq0tQAbVajc7OzlaXoULsb3uzv+3L3i6f\nrq6uhuMuI0qSJBVk2JIkSSrIsCVJklSQYUuSJKkgw5YkSVJBhi1JkqSCDFuSJEkFjfjrbL3j5l8v\n9z4e22HzIahEkqSR7b3HXDMEe3nw1VsPnzJj0M8++eST2XvvvVl//fWHoBZ49NFHuf/++5k2bdqQ\n7G8wLrnkEvbYY4/l3o8zW5Ikacgcd9xxQxa0AO644w7uuuuuIdvfYJx99tlDsp8RP7MlSZJGpuef\nf57jjjuO5557jieeeILdd9+d6667jtmzZ3Pttdfyq1/9ihdeeIGTTz6Zo48+mnXWWYfHH3+c7bff\nnsMOO4wFCxZw7LHHsmjRIjo6Ojj++OOZOHEiO+ywAxtuuCETJkzg1ltvpbu7my222IIdd9yxYR1n\nnXUWN954I4sWLWLWrFnMnDmTOXPmcM011zBmzBi22morjjrqKM4880zWXnttZs2axfz585k9ezYX\nX3wxH//4x9l6663JTDo6OjjrrLO45JJLeOaZZ5g9ezazZ89ervfJsCVJkpbJ73//e2bMmMHOO+/M\n448/zp577sm666776uMbbrghxx9/PAsWLOCPf/wj559/PmPHjmX33Xfnvvvu49xzz2WvvfZip512\nolarceyxx3LllVfypz/9iSuvvJJx48YxceJEHnzwwQGD1rx587j11lv5zne+w6JFizjjjDPITK67\n7jouu+wyxowZw8EHH8zNN9884Ov4y1/+wowZMzjhhBM44ogjuPXWWznggAO45JJLljtogWFLkiQt\no7XXXpuLLrqIG264gdVWW42FCxe+7vHx48e/envixImsueaaAGy66aY89NBDzJ8/n/e9730AdHZ2\n8thjjwEwbtw4xo0b11QNDz30EJtuuimjR49m9OjRHHPMMVx33XVsttlmrLTSSgBstdVWPPDAA0vc\nz6RJkwBYb731eOmll5o6drM8Z0uSJC2TOXPmsPnmm3Paaacxffp0enp6Xvf4qFGvxYz58+fz4osv\nsmjRIu655x422mgjJkyYwJ133gnUfwn22muv/YbnjRo1isWLFw9Yw4Ybbsi8efNYvHgxr7zyCnvv\nvTfjx4/nnnvuYeHChfT09DB37lzGjx/PyiuvzH//938DcN99971uPx0dHW/Yd//Xs6yc2ZIkSctk\nhx124KSTTuLaa69l7NixjB49mpdffrnhtiuttBKHHHIITz75JNOnT2fixIkcffTRnHDCCcyZM4eF\nCxdy8sknv+F5G2+8MWeffTaTJ09mxow3fjqys7OT7bbbjlmzZrF48WJmzZrFxIkT+ehHP/rq2JQp\nU9hpp51YsGABhx56KHPnzmXy5MlLfX0TJkzgyCOP5LTTThv8m9NHx1Cltr4iYipwMzArMy/rM34P\nkMAk4MDM/Gk1fjFwW2Z+s+9+urq6emY8O3q56/HSDyNPrVajs7Oz1WWoEPvb3uxv+yrV2wULFnD4\n4YdzxRVXDPm+R5Kuri6mTJnyhimykjNb9wMzgcsAImIT4K+AvwB7AN+NiPcBs4Ce/kFLkiSp1+WX\nX87VV1/9hvHDDz+cLbbYogUVNa9k2LobiIhYIzOfoR6wLgXenZm/iogzge8CqwPbF6xDkiS10AYb\nbLDcs1q77bYbu+222xBVNLxKn7P1PeBTEXEhsDXwVeDd1WMXAicBX8nMF0oWUavVSu5ey6C7u9u+\ntDH7297sb/uyt2WUDlvfBs6mfu3/2/o9dj5wInBARFydmfeUKsJzC0Yez/lob/a3vdnf9mVvl09X\nV1fD8aKXfsjMB6mfp/UF4JLe8Yg4DFiYmf8MfA74j4hYtWQtkiRJrTAc19m6HHhXZv62uv8OYH9g\nP4DM/DFwHfBvw1CLJEnSsCqyjJiZtwC3VLfPBM6sbl8PXN9g+yNL1CFJktRqXkFekiSpIMOWJElS\nQYYtSZKkggxbkiRJBY34X0Tt7zWUJEkrMme2JEmSCjJsSZIkFWTYkiRJKsiwJUmSVJBhS5IkqSDD\nliRJUkGGLUmSpIIMW5IkSQUZtiRJkgoybEmSJBVk2JIkSSrIsCVJklSQYUuSJKkgw5YkSVJBhi1J\nkqSCDFuSJEkFGbYkSZIKMmxJkiQVNKbVBSzNT26aMCT72XHa/CHZjyRJ0mA4syVJklSQYUuSJKkg\nw5YkSVJBhi1JkqSCDFuSJEkFGbYkSZIKKn7ph4g4GjgMGJ+Z3RFxIbAl8GegA3gbcHpmXlC6FkmS\npOE2HDNbewCXATP7jB2dmVMz80PA9sBXIqJjGGqRJEkaVkXDVkRMBeYD5wAHDbDZO4DuzOwpWYsk\nSVIrlF5G3Bc4LzMzIl6KiG2q8VMj4jjgPcA8YNfCdVCr1UofQoPQ3d1tT9qY/W1v9rd92dsyioWt\niBgH7AK8PSIOBtYAPg8sor6MeH1E7AJ8lfrsV1GdnZ2lD6FBqNVq9qSN2d/2Zn/bl71dPl1dXQ3H\nSy4j7gGcn5k7Z+Z0YBtgZ2Cd3g0y81rgKuCbBeuQJElqmZJha1/g4t47mfkC8D3gw/22+zIwKSJm\nFKxFkiSpJYotI2bmZg3GDgQO7Df2MjC5VB2SJEmt5EVNJUmSCjJsSZIkFWTYkiRJKsiwJUmSVFDx\n3424vHacVvwSXJIkScU4syVJklSQYUuSJKkgw5YkSVJBhi1JkqSCDFuSJEkFGbYkSZIKMmxJkiQV\nZNiSJEkqyLAlSZJUkGFLkiSpIMOWJElSQYYtSZKkggxbkiRJBRm2JEmSCjJsSZIkFWTYkiRJKsiw\nJUmSVJBhS5IkqaAxrS5gaWbPnj0i9yVJktQMZ7YkSZIKMmxJkiQVZNiSJEkqyLAlSZJUkGFLkiSp\nIMOWJElSQUUv/RAR7wXuAe7qM3wTcGSfsVWA54FdM/OpkvVIkiQNt+G4zta8zJzae6cKYLv0G/t/\nwGeA04ahHkmSpGHT8mXEiOgA3gU4qyVJktrOcMxsTYqIW/rcP67P2FrAW4FLgYtKF1Kr1UofQk3q\n7u62H23M/rY3+9u+7G0ZrVpGnJeZUyPircAPgcczc2HpQjo7O0sfQk2q1Wr2o43Z3/Zmf9uXvV0+\nXV1dDcdbuoyYmS8C/wD8U0Rs1spaJEmSSmj5OVuZ+Tj1TyeeGxEtr0eSJGkoFV1GzMyHgW2bGLuU\n+nlbkiRJbcWZJEmSpIIMW5IkSQUZtiRJkgoybEmSJBU0HNfZWi6zZ89udQmSJEnLzJktSZKkggxb\nkiRJBRm2JEmSCjJsSZIkFWTYkiRJKsiwJUmSVJBhS5IkqSDDliRJUkGGLUmSpIIMW5IkSQUZtiRJ\nkgoybEmSJBVk2JIkSSrIsCVJklSQYUuSJKkgw5YkSVJBhi1JkqSCDFuSJEkFjWl1AUuz4Jjbhmxf\nG5yy3ZDtS5IkqRnObEmSJBVk2JIkSSrIsCVJklSQYUuSJKkgw5YkSVJBhi1JkqSCil36ISI2BE4F\nNgBeAF4EjgZ2BXYHHq2O/yywe2Y+XaoWSZKkVikysxURqwI/AE7PzG0zcxpwIvCNapMzMnNqZn4Q\n+DWwb4k6JEmSWq3UMuLHgZsy8/begcz8JbBDg23HAU8UqkOSJKmlSi0jjgd+13snIv4TWANYD7gN\n2D0iZgJrVX9OLlTH69RqteE4jJrQ3d1tP9qY/W1v9rd92dsySoWtR4Cteu9k5icAIuKO6phnZOY5\n1dg+wIXAToVqeVVnZ2fpQ6hJtVrNfrQx+9ve7G/7srfLp6urq+F4qWXE/wR2iohtewciYiPqJ8v3\n9Nv2EeAtheqQJElqqSIzW5n5fER8HDglItarjrMIOAyYDBxeLSMuBFYFDilRhyRJUqsVu/RDZj4M\nzGzw0HeA2aWOK0mSNJJ4UVNJkqSCDFuSJEkFGbYkSZIKMmxJkiQVVOwE+aGywSnbtboESZKkZebM\nliRJUkGGLUmSpIIMW5IkSQUZtiRJkgoybEmSJBVk2JIkSSrIsCVJklSQYUuSJKkgw5YkSVJBhi1J\nkqSCDFuSJEkFGbYkSZIKMmxJkiQVZNiSJEkqyLAlSZJUkGFLkiSpIMOWJElSQYYtSZKkggxbkiRJ\nBY1pdQFLc/puHxvyfR5x+dVDvk9JkqRGnNmSJEkqyLAlSZJUkGFLkiSpoKbCVkQcGRHrlC5GkiSp\n3TR7gvzzwPcj4jHgfOD6zOwpV5YkSVJ7aCpsZeY5wDkRMRk4Djg3IuYAX8vMpxo9JyKmAlcA84AO\nYCXgX4FfAvcAd/V7yo6ZuWhZXoQkSdJI1VTYiog1gZnAXsDTwCHAaOBq4ANLeOpNmTmz2sdqwE+B\nzwDzMnPqspctSZK0Ymh2GXEucAkwMzP/0DsYEVs0e6DMfD4izgWOHFyJkiRJK65mw9YPM/PE/oOZ\nedwgj/c4sDYwKSJu6TPelZlHDHJfy6xWqw3XoTSA7u5u+9DG7G97s7/ty96W0WzY6oyINTPz6eU8\n3nuAnwFrtnIZsbOzs1WHVqVWq9mHNmZ/25v9bV/2dvl0dXU1HG82bE0CnoyIJ4EeoCcz1x9MARGx\nOvBZ4O+Bof8dPJIkSSNQs2Hrg5n5SO+diJjY5POmVcuFi6pjfQl4iTcuIwLsnZkPNblfSZKkFcIS\nw1ZE/A3wTuCrEXEU9Us4jAJOATZf0nMz8xbg7QM8vPqgK5UkSVoBLW1maxz1Sz6sC+xejS0GzipZ\nlCRJUrtYYtjKzNuA2yJiy8zsfxFSSZIkLUWz52y9LSKuBVbpHcjMaWVKkiRJah/Nhq1/AQ4FHlna\nhpIkSXpNs2HrD5l5Y9FKJEmS2lCzYeuJiDgH+BX162yRmd8sVlUfR1x+9XAcRpIkqYhmw1bv9a/e\nUaoQSZKkdtRs2LqgaBWSJEltqtmwdTn15cNRwHjgAeCDpYqSJElqF02Frcx8f+/tiFgTGJbztSRJ\nklZ0o5bhOc8AGw51IZIkSe2oqZmtiLid+jJiB7AO8OOSRUmSJLWLZs/ZmtnndndmPl6iGEmSpHbT\n7DLiIuA04FrgnIh4b7GKJEmS2kizYetbwMXAB4CLgPOLVSRJktRGml1GXCUzf1DdvioiDitVkCRJ\nUjtpdmZrTERsAtD7tyRJkpau2ZmtLwBzImI94FFgv3IlSZIktY9mZ7bmAftl5gbAV4D7ypUkSZLU\nPpoNW5cCm1e3N6Z+krwkSZKWotmw9c7MvAAgM08F1itXkiRJUvtoNmz1RMTGABExARhdriRJkqT2\n0ewJ8ocCl0fEutRPkP9cuZIkSZLaR1NhKzN/CWzRfzwivpSZJw55VZIkSW2i2ZmtgXxoSKpYgm/s\nf1OxfR90zrRi+5YkSYLmz9kaSMeQVCFJktSmljds9QxJFZIkSW1qecOWJEmSlsBlREmSpIKaOkE+\nIkZTv9zDZOC3wNmZ+TKwV8HaJEmSVnjNfhrxm8AzwI+pfwLxPGCvzHxkoCdExFTgCuq/V7EHWB14\nEDgO+PfM3LbPtvsD78jM2YN/CZIkSSNXs2HrrzNz++r2VRHxiyafd1Nmzuy9ExHfBv52MAVKkiSt\nyJo9Z2uViFgVICLeyjL8up6IeAv136n41GCfK0mStKJqdmbra8DdEfEbYBLwpSafNy0ibgHeDiym\nvhz5E+CzDbYd9stI1Gq14T6kKt3d3b7/bcz+tjf7277sbRnN/rqeSyPiOmBD4MHM/HOT+78pM2dG\nxNuon+/1EPAisHK/7VarxodVZ2fncB9SlVqt5vvfxuxve7O/7cveLp+urq6G400tI0bETsDWwLrA\n3IjYfTAHz8z/AfagfmL9KGBsREyq9j0a+DAwdzD7lCRJWhE0e87WycADwMHAB4D9B3ugzJwHfL36\n83+AOdWJ9ncAt2XmzYPdpyRJ0kjX7DlbLwCPAwsz87GIWOr5VZl5C3BLv7GT+9zdFkmSpDbX7MzW\ns8D1wOURcSD14CVJkqSlWGLYioh9q5vzgEeBTmAa8LvCdUmSJLWFpS0j9l4h/v7qD0CWK0eSJKm9\nLDFsZeaPqr8vGp5yJEmS2kuz52xJkiRpGTT7acSWOeicaa0uQZIkaZk5syVJklSQYUuSJKkgw5Yk\nSVJBhi1JkqSCDFuSJEkFGbYkSZIKMmxJkiQVZNiSJEkqyLAlSZJUkGFLkiSpIMOWJElSQYYtSZKk\nggxbkiRJBRm2JEmSCjJsSZIkFWTYkiRJKsiwJUmSVJBhS5IkqaAxrS5gaWoTO1tdwqB13l9rdQmS\nJGmEcGZLkiSpIMOWJElSQYYtSZKkggxbkiRJBRm2JEmSCioetiLi6Ij4U0SsUt2/MCKm99vmsdJ1\nSJIktcJwzGztAVwGzByGY0mSJI0oRcNWREwF5gPnAAeVPJYkSdJIVPqipvsC52VmRsRLEbHNANv1\nFK5jWNVqXtR0abq7u32f2pj9bW/2t33Z2zKKha2IGAfsArw9Ig4G1gA+DzwPrDxcdbRCZ+eKd9X7\n4Var1Xyf2pj9bW/2t33Z2+XT1dXVcLzkMuIewPmZuXNmTge2AXYGHgQ+1btRRGwHzCtYhyRJUsuU\nnFHaF9iz905mvhAR3wNWBZ6PiF8DzwEvA/sVrEOSJKllioWtzNyswdiBpY4nSZI0EnlRU0mSpIIM\nW5IkSQUZtiRJkgoybEmSJBVk2JIkSSpoxF9MtPN+r2QrSZJWXM5sSZIkFWTYkiRJKsiwJUmSVJBh\nS5IkqSDDliRJUkGGLUmSpIIMW5IkSQUZtiRJkgoybEmSJBVk2JIkSSrIsCVJklSQYUuSJKkgw5Yk\nSVJBhi1JkqSCDFuSJEkFGbYkSZIKMmxJkiQVZNiSJEkqaEyrC1iaTS7apNUltNy9n7631SVIkqRl\n5MyWJElSQYYtSZKkggxbkiRJBRm2JEmSCjJsSZIkFVT004gRMRk4FVgVWA24FpidmT0R8b+BC4C/\nzsxHS9YhSZLUKsVmtiJiTeAy4NDM3AHYFtgE+Fy1yWeBrwP7lapBkiSp1UouI34CuCkzHwDIzEXA\nXsCciBgPrAV8FdgzIlYqWIckSVLLlFxGXB94sO9AZj4PEBGfAeZk5tMRcTvwKeDygrWs0Gq1WqtL\nGHLd3d1t+bpUZ3/bm/1tX/a2jJJh6/fAln0HqhmtdwN7AA9FxMepz3B9HsPWgDo7O1tdwpCr1Wpt\n+bpUZ3/bm/1tX/Z2+XR1dTUcL7mMeDUwPSImAFRLhWcAmwNzM3OHzJyemVsD60bEpgVrkSRJaoli\nYSsznwU+DXwrIm4B7gDuBnYCLu63+XnUZ7ckSZLaStFLP2RmFzCtie1OLVmHJElSq3hRU0mSpIIM\nW5IkSQUZtiRJkgoybEmSJBVk2JIkSSqo6KcRh8K9n7631SVIkiQtM2e2JEmSCjJsSZIkFWTYkiRJ\nKsiwJUmSVJBhS5IkqSDDliRJUkGGLUmSpIIMW5IkSQUZtiRJkgoybEmSJBVk2JIkSSrIsCVJklSQ\nYUuSJKkgw5YkSVJBhi1JkqSCDFuSJEkFGbYkSZIKMmxJkiQVNKbVBSzV7DVaXYF6zX6m1RVIkrTC\ncWZLkiSpIMOWJElSQYYtSZKkggxbkiRJBRm2JEmSCir2acSImApcAcwDOoCVgQOAQ4AtgT9X428D\nTs/MC0rVIkmS1CqlL/1wU2bOBIiInYEvA08CR2fm9dX4WsB9EXFhZvYUrkeSJGlYDecy4jjgiQbj\n7wC6DVqSJKkdlZ7ZmhYRt1BfQtwM+CSwO3BqRBwHvIf6MuOuhevQEKjVakO2r+7u7iHdn0YW+9ve\n7G/7srdlDOcyYgC3Az+mWkaMiF2ArwLzC9ehIdDZ2Tlk+6rVakO6P40s9re92d/2ZW+XT1dXV8Px\n4VxGfLz/QGZeC1wFfHMY65AkSRo2pcPWtIi4JSJ+AtwAHA682G+bLwOTImJG4VokSZKGXbFlxMy8\nBXh7g4cu7Lfdy8DkUnVIkiS1khc1lSRJKsiwJUmSVJBhS5IkqSDDliRJUkGGLUmSpIJKX9R0+c1+\nptUVSJIkLTNntiRJkgoybEmSJBVk2JIkSSrIsCVJklSQYUuSJKkgw5YkSVJBhi1JkqSCDFuSJEkF\nGbYkSZIKMmxJkiQVZNiSJEkqyLAlSZJUkGFLkiSpIMOWJElSQYYtSZKkggxbkiRJBRm2JEmSCjJs\nSZIkFTSm1QUszXuPuabVJaiYB1tdgIqyv+3N/rav9urtw6fMaHUJzmxJkiSVZNiSJEkqyLAlSZJU\nkGFLkiSpIMOWJElSQcU+jRgRU4H9M3Nmn7FTgPuBHuDTQAfwFuDEzLyhVC2SJEmt0oqZrTWAE4Dp\nmbkDsCswJyKcZZMkSW2nFQHnJeqzWQdExITMfBSYkJmLW1CLJElSUa24qOmLwDTgUOD6iHgLcApw\ndgtqkSRJbaxWq7W6hKJh60Vg5X5jq1E/X+utmfl5gIjYmHro+llm3luwHkmS9CbT2dk5bMfq6upq\nOF5yGbEGbBER6wFExCrA9tRFLJpWAAAEDklEQVRPkL8kIsZW2/0eeBJ4uWAtkiRJLVEsbGXms8Dh\nwDUR8TPgVuDMzPwlcCZwa0T8oho/LzOzVC2SJEmtUvScrcy8Eriywfh5wHkljy1JkjQSeLkFSZKk\nggxbkiRJBRm2JEmSCjJsSZIkFWTYkiRJKqgVV5AflIdPmdHqElRArVYb1gvNaXjZ3/Zmf9uXvS3D\nmS1JkqSCDFuSJEkFGbYkSZIKMmxJkiQVZNiSJEkqyLAlSZJUkGFLkiSpIMOWJElSQR09PT2trmFA\nXV1dI7c4SZKkfqZMmdLRf2xEhy1JkqQVncuIkiRJBRm2JEmSChpxv4g6IkYBZwGbAS8B+2bm71pb\nlQYjIu4Cnq3uPgScC3wNWAjckJknDtTniNi2/7bD/gL0BhGxDfDVzJwaERsBFwI9wG+AgzJzcUR8\nCZhBvXeHZuYvB7PtsL8ovapff7cArgYeqB4+OzMvt78rnohYCZgDvBdYGTgJmIffv8NuJM5sfRJY\nJTPfDxwDnN7iejQIEbEK0JGZU6s/ewPnALsDHwS2qf4xH6jPjbZVC0XE0cB5wCrV0BnA8Zm5HdAB\nfCIitgQ+BGwDzAS+sQzbqgUa9HcKcEaf7+HL7e8Kaw/gf6r+TAf+Db9/W2Ikhq0PAtcDZOYdwFat\nLUeDtBmwakTcEBE3RcT2wMqZOT8ze4AfATvRoM8RsfoA26q15gOf6nN/CvDT6vZ1vNbPGzKzJzP/\nAIyJiHUGua1ao1F/Z0TErRFxfkSMxf6uqL4DnFDd7qA+E+X3bwuMxLC1OvBMn/uLImLELXdqQC8A\npwEfAfYHLqjGej0HrEGDPldjzzbYVi2Umd8DXukz1FGFYRi4n73jg9lWLdCgv78EjsrM7YEHgS9h\nf1dImfl8Zj5XBebvAsfj929LjMSw9Swwts/9UZm5sFXFaNB+C1xS/a/nt9S/Kdfq8/hY4Gka9LnB\nWO+2GlkW97k9UD97xwezrUaG72dmV+9tYAvs7worIt4F3AxcnJnfxu/flhiJYevnwC4A1cnS97a2\nHA3SPlTnX0XE+sCqwF8iYkJEdFCf8bqNBn3OzGeBlxtsq5HlVxExtbr9UV7r50ciYlREvJv6f5Ke\nHOS2Ghl+FBFbV7d3BLqwvyukiFgXuAH4YmbOqYb9/m2Bkbg8933gwxHxC+przHu3uB4NzvnAhRHx\nM+qfYNmH+v+OLgVGU1/r/6+ImEvjPu/ff9vhfgFaqiOAb0XEW4Aa8N3MXBQRtwG3U/9P3EHLsK1G\nhgOAMyPiFeAxYL/MfNb+rpCOBcYBJ0RE77lbhwBf9/t3eHkFeUmSpIJG4jKiJElS2zBsSZIkFWTY\nkiRJKsiwJUmSVJBhS5IkqSDDliRJUkGGLUmSpIIMW5IkSQX9f0ozVtWxQkq+AAAAAElFTkSuQmCC\n",
"text/plain": [
""
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"ax = top_10.plot(x='iso_country', \n",
" y='airport_count',\n",
" kind='barh',\n",
" figsize=(10, 7),\n",
" title='Top 10 countries with most airports')"
]
},
{
"cell_type": "code",
"execution_count": 69,
"metadata": {},
"outputs": [],
"source": [
"countries = pd.read_csv('data/countries.csv')"
]
},
{
"cell_type": "code",
"execution_count": 70,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" code | \n",
" name | \n",
" continent | \n",
" wikipedia_link | \n",
" keywords | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 302672 | \n",
" AD | \n",
" Andorra | \n",
" EU | \n",
" http://en.wikipedia.org/wiki/Andorra | \n",
" NaN | \n",
"
\n",
" \n",
" 1 | \n",
" 302618 | \n",
" AE | \n",
" United Arab Emirates | \n",
" AS | \n",
" http://en.wikipedia.org/wiki/United_Arab_Emirates | \n",
" UAE,مطارات في الإمارات العربية المتحدة | \n",
"
\n",
" \n",
" 2 | \n",
" 302619 | \n",
" AF | \n",
" Afghanistan | \n",
" AS | \n",
" http://en.wikipedia.org/wiki/Afghanistan | \n",
" NaN | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" id code name continent \\\n",
"0 302672 AD Andorra EU \n",
"1 302618 AE United Arab Emirates AS \n",
"2 302619 AF Afghanistan AS \n",
"\n",
" wikipedia_link \\\n",
"0 http://en.wikipedia.org/wiki/Andorra \n",
"1 http://en.wikipedia.org/wiki/United_Arab_Emirates \n",
"2 http://en.wikipedia.org/wiki/Afghanistan \n",
"\n",
" keywords \n",
"0 NaN \n",
"1 UAE,مطارات في الإمارات العربية المتحدة \n",
"2 NaN "
]
},
"execution_count": 70,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"countries.head(3)"
]
},
{
"cell_type": "code",
"execution_count": 75,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" code | \n",
" name | \n",
" continent | \n",
" wikipedia_link | \n",
" keywords | \n",
"
\n",
" \n",
" \n",
" \n",
" 9 | \n",
" 302789 | \n",
" AR | \n",
" Argentina | \n",
" SA | \n",
" http://en.wikipedia.org/wiki/Argentina | \n",
" Aeropuertos de Argentina | \n",
"
\n",
" \n",
" 12 | \n",
" 302764 | \n",
" AU | \n",
" Australia | \n",
" OC | \n",
" http://en.wikipedia.org/wiki/Australia | \n",
" NaN | \n",
"
\n",
" \n",
" 29 | \n",
" 302791 | \n",
" BR | \n",
" Brazil | \n",
" SA | \n",
" http://en.wikipedia.org/wiki/Brazil | \n",
" Brasil, Brasilian | \n",
"
\n",
" \n",
" 35 | \n",
" 302730 | \n",
" CA | \n",
" Canada | \n",
" NaN | \n",
" http://en.wikipedia.org/wiki/Canada | \n",
" NaN | \n",
"
\n",
" \n",
" 54 | \n",
" 302681 | \n",
" DE | \n",
" Germany | \n",
" EU | \n",
" http://en.wikipedia.org/wiki/Germany | \n",
" Flughäfen in Deutschland | \n",
"
\n",
" \n",
" 72 | \n",
" 302687 | \n",
" FR | \n",
" France | \n",
" EU | \n",
" http://en.wikipedia.org/wiki/France | \n",
" Aéroports de France | \n",
"
\n",
" \n",
" 74 | \n",
" 302688 | \n",
" GB | \n",
" United Kingdom | \n",
" EU | \n",
" http://en.wikipedia.org/wiki/United_Kingdom | \n",
" Great Britain | \n",
"
\n",
" \n",
" 153 | \n",
" 302747 | \n",
" MX | \n",
" Mexico | \n",
" NaN | \n",
" http://en.wikipedia.org/wiki/Mexico | \n",
" Aeropuertos de México | \n",
"
\n",
" \n",
" 187 | \n",
" 302714 | \n",
" RU | \n",
" Russia | \n",
" EU | \n",
" http://en.wikipedia.org/wiki/Russia | \n",
" Soviet, Sovietskaya, Sovetskaya, Аэропорты России | \n",
"
\n",
" \n",
" 228 | \n",
" 302755 | \n",
" US | \n",
" United States | \n",
" NaN | \n",
" http://en.wikipedia.org/wiki/United_States | \n",
" America | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" id code name continent \\\n",
"9 302789 AR Argentina SA \n",
"12 302764 AU Australia OC \n",
"29 302791 BR Brazil SA \n",
"35 302730 CA Canada NaN \n",
"54 302681 DE Germany EU \n",
"72 302687 FR France EU \n",
"74 302688 GB United Kingdom EU \n",
"153 302747 MX Mexico NaN \n",
"187 302714 RU Russia EU \n",
"228 302755 US United States NaN \n",
"\n",
" wikipedia_link \\\n",
"9 http://en.wikipedia.org/wiki/Argentina \n",
"12 http://en.wikipedia.org/wiki/Australia \n",
"29 http://en.wikipedia.org/wiki/Brazil \n",
"35 http://en.wikipedia.org/wiki/Canada \n",
"54 http://en.wikipedia.org/wiki/Germany \n",
"72 http://en.wikipedia.org/wiki/France \n",
"74 http://en.wikipedia.org/wiki/United_Kingdom \n",
"153 http://en.wikipedia.org/wiki/Mexico \n",
"187 http://en.wikipedia.org/wiki/Russia \n",
"228 http://en.wikipedia.org/wiki/United_States \n",
"\n",
" keywords \n",
"9 Aeropuertos de Argentina \n",
"12 NaN \n",
"29 Brasil, Brasilian \n",
"35 NaN \n",
"54 Flughäfen in Deutschland \n",
"72 Aéroports de France \n",
"74 Great Britain \n",
"153 Aeropuertos de México \n",
"187 Soviet, Sovietskaya, Sovetskaya, Аэропорты России \n",
"228 America "
]
},
"execution_count": 75,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"countries_top10 = countries[countries.code.isin(top_10.iso_country)]\n",
"countries_top10"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.6.5"
}
},
"nbformat": 4,
"nbformat_minor": 1
}