지도를 펴고
읽는다

import duckdb
conn = duckdb.connect()

conn.execute('''
    CREATE TABLE flights
    as
    SELECT * FROM read_csv_auto('flights.csv')
''')

conn.execute('''
    CREATE TABLE airports
    as
    SELECT * FROM read_csv_auto('airports.csv')
''')

conn.execute('''
    CREATE TABLE airlines
    as
    SELECT * FROM read_csv('airlines.csv')
''')

display(conn.execute('SHOW TABLES').df())

!pip install folium

import folium

mymap = folium.Map(location = [47.116386, -101.299591],
                   width = 950,
                   height = 550,
                   zoom_start = 3,
                   tiles = 'openstreetmap')
mymap

import folium
mymap = folium.Map(location = [47.116386, -101.299591],
                   width = 950,
                   height = 550,
                   zoom_start = 3,
                   tiles = 'openstreetmap')

folium.TileLayer('cartodbpositron',
                 attr = 'cartodbpositron',
                 show = False).add_to(mymap)
folium.TileLayer('cartodbdark_matter',
                 attr = 'cartodbdark_matter',
                 show = False).add_to(mymap)

folium.LayerControl().add_to(mymap)

mymap

df = conn.execute('''
    SELECT
        latitude as lat,
        longitude as lng,
        airport as airport
    FROM airports
    WHERE
        (lat is not null) or
        (lng is not null)
''').df()

import math

for lat, lng, airport in zip(df['lat'], df['lng'], df['airport']):
    airport = folium.CircleMarker(
            location = [lat, lng], # 마커의 위치
            radius = 4,            # 마커의 크기
            color = 'red',         # 테두리 색
            fill = True,           # 내부를 채운다
            fill_color = 'yellow', # 채움 색
            fill_opacity = 0.5,     # 반투명하게
            popup = airport)       # 말풍선에 넣을 공항 이름

    # 원 마커를 지도에 얹는다
    airport.add_to(mymap)
mymap

for lat, lng, airport in zip(df['lat'], df['lng'], df['airport']):
    airport = folium.Marker(
        location = [lat, lng],
        popup = airport,
        icon = folium.Icon(color = 'lightgray',
                           icon = 'plane-arrival',
                           prefix = 'fa'),
        )

    airport.add_to(mymap)
mymap

!pip install shapely

import pandas as pd
df = pd.read_csv('airports.csv')

from shapely.geometry import Point

df['geometry'] = df.apply(
    lambda row: Point(row['LONGITUDE'], row['LATITUDE'])…
df

# ① Shapely로 만든 DataFrame을 올리는 경우
conn.execute("CREATE TABLE airports_2 AS SELECT *  FROM df")

# ② DuckDB의 spatial 확장으로 변환하는 경우
conn.execute('INSTALL spatial;')
conn.execute('LOAD spatial;')

conn.execute('''
    DROP TABLE IF EXISTS airports_2 ;
    CREATE TABLE airports_2 as
    SELECT
        *,
        ST_AsText(ST_Point(LONGITUDE,LATITUDE)) as geometry
    FROM airports
''')

!pip install leafmap
!pip install mapclassify
!pip install geopandas

import leafmap
df_airports_gdf = leafmap.df_to_gdf(
    conn.execute('SELECT * FROM airports_2').df(),
    geometry = 'geometry',
    src_crs="EPSG:4326",
    dst_crs="EPSG:4326")

df_airports_gdf.explore()

conn.execute('INSTALL spatial;')
conn.execute('LOAD spatial;')

# 마이애미
LOCATION_LNGLAT = (-80.2706578, 25.7824017)

# 3도 안
df_airports_near_miami = conn.sql(f"""
    SELECT *
    FROM airports_2
    WHERE ST_DWithin(
        ST_GeomFromText(geometry),
        ST_GeomFromText('POINT ({LOCATION_LNGLAT…
        3);
""").df()
df_airports_near_miami

df_airports_near_miami = conn.sql(f"""
    SELECT *,
        ST_Distance(ST_GeomFromText(geometry),
        ST_GeomFromText('POINT (
            {LOCATION_LNGLAT[0]}
            {LOCATION_LNGLAT[1]}))') as distance
    FROM airports_2;
""").df()

df_airports_near_miami = conn.sql(f"""
    SELECT *,
        ST_Distance(ST_GeomFromText(geometry),
        ST_GeomFromText('POINT (
            {LOCATION_LNGLAT[0]}
            {LOCATION_LNGLAT[1]}))') as distance
    FROM airports_2
    ORDER BY distance
    LIMIT 3
""").df()

import leafmap

df_airports_near_miami_gdf = leafmap.df_to_gdf(df_airports_near_miami, …)
folium_map = df_airports_near_miami_gdf.explore()
folium_map

import folium

# 마이애미 지점에 말풍선을 얹는다
folium.Marker(location = [LOCATION_LNGLAT[1],LOCATION_LNGLAT[0]],
              popup='Miami').add_to(folium_map)
folium_map

df_city_state = conn.execute('''
    SELECT *
    FROM airports
    ORDER BY STATE, CITY
''').df()

# 다중 인덱스 DataFrame으로 만든다
df_city_state.set_index(['STATE','CITY'], inplace=True)
df_city_state

# 캘리포니아의 모든 공항
df_city_state.loc['CA']

# 캘리포니아 샌프란시스코의 공항
df_city_state.loc[('CA','San Francisco')]

df_airports_state = conn.execute('''
    SELECT
        STATE,
        count(*) as COUNT
    FROM airports
    GROUP BY STATE
    ORDER BY Count DESC
''').df()
df_airports_state.head()

import matplotlib.pyplot as plt
import seaborn

# 공항의 총수
total = df_airports_state['COUNT'].sum()

def fmt(x):
    return '{:.1f}%\n({:.0f} airports)'.format(x, total*x/100)

palette_color = seaborn.color_palette('pastel')
plt.figure(figsize = (7, 7))

plt.pie(df_airports_state['COUNT'],
        labels = df_airports_state['STATE'],
        colors = palette_color,
        autopct = fmt)

plt.legend(df_airports_state['STATE'], loc = "best")

# 막대 차트를 만든다
plt.bar(df_airports_state['STATE'],
        df_airports_state['COUNT'],
        color='skyblue')

plt.xlabel('State')
plt.ylabel('Number of Airports')
plt.title('Top 10 States with Most Airports')
plt.xticks(rotation = 45)

conn.execute('''
    SELECT
        ORIGIN_AIRPORT, COUNT(ORIGIN_AIRPORT) as COUNT
    FROM flights
    GROUP BY ORIGIN_AIRPORT
    ORDER BY COUNT DESC
''').df()

conn.execute('''
    SELECT
        ORIGIN_AIRPORT, DESTINATION_AIRPORT,
        COUNT(*) as COUNT
    FROM flights
    GROUP BY ORIGIN_AIRPORT, DESTINATION_AIRPORT
    ORDER BY COUNT DESC
''').df()

conn.execute('''
    SELECT
        ORIGIN_AIRPORT, DESTINATION_AIRPORT,
        COUNT(*) as COUNT
    FROM flights
    WHERE AIRLINE='DL'
    GROUP BY ORIGIN_AIRPORT, DESTINATION_AIRPORT
    ORDER BY COUNT DESC
''').df()

-- ① 노선별 결항 편수
conn.execute('''
    SELECT
        ORIGIN_AIRPORT, DESTINATION_AIRPORT,
        COUNT(*) as COUNT
    FROM flights
    WHERE AIRLINE='DL' AND CANCELLED = 1
    GROUP BY ORIGIN_AIRPORT, DESTINATION_AIRPORT
    ORDER BY COUNT DESC
''').df()

-- ② 노선별 결항 비율
conn.execute('''
    SELECT
        ORIGIN_AIRPORT, DESTINATION_AIRPORT,
        (SUM(CANCELLED) * 100.0) / COUNT(*) as CANCELLED_PERCENT
    FROM flights
    WHERE AIRLINE = 'DL'
    GROUP BY ORIGIN_AIRPORT, DESTINATION_AIRPORT
    ORDER BY CANCELLED_PERCENT DESC
''').df()

-- ③ 항공사 전체의 결항 비율
conn.execute('''
    SELECT
        (SUM(CANCELLED) * 100.0) / COUNT(*) as CANCELLED_PERCENT
    FROM flights
    WHERE AIRLINE = 'DL'
''').df()

import matplotlib.pyplot as plt

df = conn.execute('''
    SELECT AIRLINE,
        (SUM(CANCELLED) * 100.0) / COUNT(*) as CANCELLED_PERCENT
    FROM flights
    -- WHERE AIRLINE = 'DL'
    GROUP BY AIRLINE
    ORDER BY CANCELLED_PERCENT DESC
''').df()
display(df)

df = conn.execute('''
    SELECT
        a.AIRLINE,
        (SUM(f.CANCELLED) * 100.0) / COUNT(*) as CANCELLED_PERCENT
    FROM flights f
    JOIN airlines a ON f.AIRLINE = a.IATA_CODE
    GROUP BY a.AIRLINE
    ORDER BY CANCELLED_PERCENT DESC
''').df()

df.plot(kind='bar', x='AIRLINE', y='CANCELLED_PERCENT')
plt.xlabel('Airlines')
plt.ylabel('Cancellation Percentage')
plt.title('Cancellation Percentage for Different Airlines')

df_flights_day_of_week = conn.execute('''
    SELECT
        day_of_week,
        origin_airport,
        destination_airport,
        COUNT(*) AS flight_count
    FROM
        flights
    WHERE
        CANCELLED = 0 -- 결항편은 제외한다
    GROUP BY
        day_of_week,
        origin_airport,
        destination_airport
    ORDER BY
        day_of_week,
        origin_airport,
        destination_airport;
''').df()

df_flights_day_of_week

from_airport = 'SFO'
to_airport = 'LAX'

df_flights_result = df_flights_day_of_week.query(
    f'ORIGIN_AIRPORT=="{from_airport}" & DESTINATION_AIRPORT=="{to_airport}"')

df_flights_result.plot(kind='bar',
                       x = 'DAY_OF_WEEK',
                       y = 'flight_count',
                       legend = False)

plt.xlabel('Day of Week')
plt.ylabel('Number of Flights')
plt.title(f'Number of Flights from {from_airport} …')
plt.xticks(df_flights_result['DAY_OF_WEEK'] - 1,
           ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'])
plt.show()

df_flights_weekday = conn.execute('''
    with t1 as (
        SELECT
            day_of_week,
            origin_airport,
            destination_airport,
            COUNT(*) AS flight_count_per_weekday
        FROM flights
        WHERE CANCELLED = 0 -- 결항편 제외
        GROUP BY
            day_of_week, origin_airport, destination_airport
        ORDER BY
            day_of_week, origin_airport, destination_airport
    ),
    t2 as (
        SELECT
            origin_airport,
            destination_airport,
            count(*) as total_flight_count
        FROM flights
        WHERE CANCELLED = 0 -- 결항편 제외
        GROUP BY
            origin_airport, destination_airport
    )
    SELECT
        t1.origin_airport,
        t1.destination_airport,
        t1.day_of_week,
        t2.total_flight_count,
        100. * (t1.flight_count_per_weekday / t2.total_flight_count) as
            percent_flights_on_weekday
    FROM t1
    JOIN t2
    ON
        t1.origin_airport = t2.origin_airport AND
        t1.destination_airport = t2.destination_airport
''').df()

df_delays_by_week = conn.execute('''
SELECT
    DAY_OF_WEEK,
    CASE
        WHEN SCHEDULED_DEPARTURE BETWEEN '0000' A…
        WHEN SCHEDULED_DEPARTURE BETWEEN '0600' A…
        WHEN SCHEDULED_DEPARTURE BETWEEN '1200' A…
        WHEN SCHEDULED_DEPARTURE BETWEEN '1800' A…
        ELSE 'Other'
    END AS DEPARTURE_TIME_INTERVAL,
    AVG(ARRIVAL_DELAY) AS AVG_ARRIVAL_DELAY
FROM flights
WHERE ARRIVAL_DELAY > 0
GROUP BY
    DAY_OF_WEEK,
    CASE
        -- SELECT 절과 같은 CASE 식을 되풀이한다
        ELSE 'Other'
    END
ORDER BY
    DAY_OF_WEEK, DEPARTURE_TIME_INTERVAL;
''').df()

df_delays_by_week_pivot = df_delays_by_week.pivot(
    index = 'DAY_OF_WEEK',
    columns = 'DEPARTURE_TIME_INTERVAL',
    values = 'AVG_ARRIVAL_DELAY')
df_delays_by_week_pivot

# 막대 차트를 그린다
df_delays_by_week_pivot.plot(kind='bar',
                              stacked=False,
                              figsize=(10, 6))

# x축 눈금을 요일로 바꾼다
days_of_week = ['Monday', 'Tuesday', 'Wednesday',
                'Thursday', 'Friday', 'Saturday', 'Sunday']
plt.xticks(ticks=range(len(days_of_week)), labels=days_of_week)

plt.title('Average Arrival Delay by Departure Time …')
plt.xlabel('Day of Week')
plt.ylabel('Average Arrival Delay (minutes)')
plt.legend(title='Departure Time Interval', bbox_…
plt.tight_layout() # 잘림을 피하도록 배치를 조정한다
plt.show()

df_most_delays = conn.execute('''
    SELECT
        count(airlines.AIRLINE) as Count,
        airlines.AIRLINE
    FROM flights, airlines
    WHERE airlines.IATA_CODE = flights.AIRLINE AN…
    GROUP BY airlines.AIRLINE
    ORDER BY COUNT DESC
''').df()
df_most_delays

df_percent_delay = conn.execute('''
    WITH flight_delays AS (
        SELECT
            AIRLINE,
            1.0 * count(*) as TotalFlights,
            1.0 * sum(case when ARRIVAL_DELAY > 0…
            (1.0 * sum(case when ARRIVAL_DELAY >…
            count(*)) * 100 as Percentage
        FROM flights
        GROUP BY AIRLINE
    )
    SELECT
        flight_delays.Percentage,
        airlines.IATA_CODE,
        airlines.AIRLINE
    FROM flight_delays
    JOIN airlines ON airlines.IATA_CODE = flight_delays.AIRLINE
    ORDER BY flight_delays.Percentage DESC;
''').df()

df_percent_on_time = conn.execute('''
    WITH flight_delays AS (
        SELECT
            AIRLINE,
            1.0 * count(*) as TotalFlights,
            1.0 * sum(case when ARRIVAL_DELAY <=…
                OnTimeFlights,
            (1.0 * sum(case when ARRIVAL_DELAY <=…
                count(*)) * 100 as Percentage
        FROM flights
        GROUP BY AIRLINE
    )
    SELECT
        flight_delays.Percentage,
        airlines.IATA_CODE,
        airlines.AIRLINE
    FROM flight_delays
    JOIN airlines ON airlines.IATA_CODE = flight_delays.AIRLINE
    ORDER BY flight_delays.Percentage DESC;
''').df()