<jupyter_start><jupyter_text>The impact of extreme weather events using hurricane Sandy https://www.tableau.com/solutions/gallery/storm-tracking Hypothesis- H1: The are significant differences between the weather data compared to the year before.- H2: Cancellation explained through independent variables. Which variable influence it at most?- H3: The shorter the airports distance to the storm the more cancellations.- H4: Significant changes in diverted flights with destinations to the 6 airports.- H5: There is a threshold (average temperature, wind, rain) until the flights are cancelled.ToDo:- Show the course of hurricane Sandy- Mark the airports with dates Get flights and weather data EDA + Visualization 		cancelled		delays		diverted- Add dates of hurricane Sandy:  Questions to answer and plot - Diverted flights with destination of the airports:    - Boston (BOS)	- Miami (MIA)	- Newark (EWR)	- New York (JFK)	- Philadelphia (PHL)	- Purto Rico (SJU)  1. Download flights data from a website<jupyter_code># Import all necessary libraries
import pandas as pd
import numpy as np
import psycopg2
import requests #package for getting data from the web
from zipfile import * #package for unzipping zip files
from sql import get_engine #adjust this as necessary to match your sql.py connection methods
# Specifies path for saving file
path ='data/' 
# Create the data folder
!mkdir {path}
# Download flights data

years = [2011, 2012] # list of years you want to look at, specify one year
months = [10, 11, 12] # list of months you want to look at, specify one month
# Here: October, November, October 2011 and 2012 / 1 file ca. 23 mb

# Loop through months
for year in years:
    for month in months:
        # Get the file from the website https://transtats.bts.gov
        zip_file = f'On_Time_Reporting_Carrier_On_Time_Performance_1987_present_{year}_{month}.zip'
        csv_file = f'On_Time_Reporting_Carrier_On_Time_Performance_(1987_present)_{year}_{month}.csv'
        url = (f'https://transtats.bts.gov/PREZIP/{zip_file}')
        # Download the database
        r = requests.get(f'{url}', verify=False)
        # Save database to local file storage
        with open(path+zip_file, 'wb') as f:
            f.write(r.content)
# Unzip your files (220Mb/per file)
for year in years:
    for month in months:
        zip_files = f'On_Time_Reporting_Carrier_On_Time_Performance_1987_present_{year}_{month}.zip'
        with ZipFile(path+zip_files, 'r') as zip_ref:
            zip_ref.extractall(path)
# Read in your data DOESNT WORK YET
# df = pd.DataFrame(data=None)
# for year in years:
#     for month in months:
#         appended_flights = pd.read_csv(f'data/On_Time_Reporting_Carrier_On_Time_Performance_(1987_present)_{year}_{month}.csv', low_memory = False)
#         df.append(appended_flights)

# pd.concat([all_df], axis=0)
# Read in csv data in df
flights_df_2011_10 = pd.read_csv(f'data/On_Time_Reporting_Carrier_On_Time_Performance_(1987_present)_2011_10.csv', low_memory = False)
flights_df_2011_11 = pd.read_csv(f'data/On_Time_Reporting_Carrier_On_Time_Performance_(1987_present)_2011_11.csv', low_memory = False)
flights_df_2011_12 = pd.read_csv(f'data/On_Time_Reporting_Carrier_On_Time_Performance_(1987_present)_2011_12.csv', low_memory = False)
flights_df_2012_10 = pd.read_csv(f'data/On_Time_Reporting_Carrier_On_Time_Performance_(1987_present)_2012_10.csv', low_memory = False)
flights_df_2012_11 = pd.read_csv(f'data/On_Time_Reporting_Carrier_On_Time_Performance_(1987_present)_2012_11.csv', low_memory = False)
flights_df_2012_12 = pd.read_csv(f'data/On_Time_Reporting_Carrier_On_Time_Performance_(1987_present)_2012_12.csv', low_memory = False)
# Combine your date, use a function
flights_df1 = flights_df_2011_10.append(flights_df_2011_11)
flights_df2 = flights_df1.append(flights_df_2011_12)
flights_df3 = flights_df2.append(flights_df_2012_10)
flights_df4 = flights_df3.append(flights_df_2012_11)
df = flights_df4.append(flights_df_2012_12)<jupyter_output><empty_output><jupyter_text>2. Prepare the csv file for further processing In the next step, we clean and prepare our dataset.<jupyter_code># Columns from downloaded file that are to be kept

columns_to_keep = [
                'FlightDate',
                'DepTime',
                'CRSDepTime',
                'DepDelay',
                'ArrTime',
                'CRSArrTime',
                'ArrDelay',
                'Reporting_Airline',
                'Tail_Number',
                'Flight_Number_Reporting_Airline',
                'Origin',
                'Dest',
                'AirTime',
                'Distance',
                'Cancelled',
                'Diverted'
]
# set up your database connection
engine = get_engine()
# The columns in the DB have different naming as in the source csv files. Lets get the names from the DB
table_name_sql = '''SELECT COLUMN_NAME 
                    FROM INFORMATION_SCHEMA.COLUMNS 
                    WHERE TABLE_NAME = 'flights'
                    AND TABLE_SCHEMA ='public'
                    ORDER BY ordinal_position'''
c_names = engine.execute(table_name_sql).fetchall()
c_names
# we can clean up the results into a clean list
new_column_names=[]
for name in c_names:
    new_column_names.append(name[0])
new_column_names        
# Just in case the above fails here are the results
new_column_names_alternate = ['flight_date',
                              'dep_time',
                              'sched_dep_time',
                              'dep_delay',
                              'arr_time',
                              'sched_arr_time',
                              'arr_delay',
                              'airline',
                              'tail_number',
                              'flight_number',
                              'origin',
                              'dest',
                              'air_time',
                              'distance',
                              'cancelled',
                              'diverted' ]<jupyter_output><empty_output><jupyter_text>b) With the next function, we make our csv file ready to be uploaded to SQL.  We only keep to above specified columns and convert the datatypes.<jupyter_code>def clean_airline_df(df):
    '''
    Transforms a df made from BTS csv file into a df that is ready to be uploaded to SQL
    Set rows=0 for no filtering
    '''

    # Build dataframe including only the columns you want to keep
    df_airline = df.loc[:,columns_to_keep]
     
    # Clean data types and NULLs
    df_airline['FlightDate']= pd.to_datetime(df_airline['FlightDate'], yearfirst=True)
    df_airline['CRSArrTime']= pd.to_numeric(df_airline['CRSArrTime'], downcast='integer', errors='coerce')
    df_airline['Cancelled']= pd.to_numeric(df_airline['Cancelled'], downcast='integer')
    df_airline['Diverted']= pd.to_numeric(df_airline['Diverted'], downcast='integer')
    
    # Rename columns
    df_airline.columns = new_column_names
    
    return df_airline
# Call function and check resulting dataframe
df_clean = clean_airline_df(df)
df_clean.head()<jupyter_output><empty_output><jupyter_text>If you decide to only look at specific airports, it is a good decision to filter for them in advance.  This function does the filtering.<jupyter_code># Specify the airports you are interested in and put them as a list in the function.
def select_airport(df, airports):
    ''' Helper function for filtering airline df for a subset of airports'''
    df_out = df.loc[(df.origin.isin(airports)) | (df.dest.isin(airports))]
    return df_out
# Execute function, filtering for New York area airports
airports=['BOS', 'EWR', 'JFK', 'MIA', 'PHI', 'SJU']
if len(airports) > 0:
    df_selected_airports = select_airport(df_clean, airports)
else:
    df_selected_airports = df_clean
    
df_selected_airports.head()<jupyter_output><empty_output><jupyter_text>3. Push the prepared data to a table in the database<jupyter_code># Specify which table within your database you want to push your data to. Give your table an unambiguous name.
# Example: flights_sp for Sina's flights table
table_name = 'group4_flights_data'
# If the specified table doesn't exist yet, it will be created
# With 'replace', your data will be replaced if the table already exists.
# This will take a minute or two...

# Write records stored in a dataframe to SQL database
if engine!=None:
    try:
        df_selected_airports.to_sql(name=table_name, # Name of SQL table
                        con=engine, # Engine or connection
                        if_exists='replace', # Drop the table before inserting new values 
                        index=False, # Write DataFrame index as a column
                        chunksize=5000, # Specify the number of rows in each batch to be written at a time
                        method='multi') # Pass multiple values in a single INSERT clause
        print(f"The {table_name} table was imported successfully.")
    # Error handling
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
        engine = None
# Check the number of rows match
table_name_sql = f'''SELECT count(*) 
                    FROM {table_name}
                    '''
engine.execute(table_name_sql).fetchall()[0][0] == df_selected_airports.shape[0]<jupyter_output><empty_output><jupyter_text>4. Download weather_data with API<jupyter_code># Import libraries

# Import python packages 
import pandas as pd
import datetime as dt
import matplotlib.pyplot as plt
import seaborn as sns

from IPython.display import display

# Import from own modules
import sql as gd
import api # connection
from sql import get_conn, get_data

# Change options
pd.set_option('display.max_columns', None)
# Download airport data from faa

# from https://www.faa.gov/airports/airport_safety/airportdata_5010/menu/#datadownloads
airports_df = pd.read_sql('SELECT * FROM airports_info', get_conn())

# filter only rows of the 6 airports we are interested in
locations = airports_df[airports_df['faa'].isin(['BOS', 'EWR', 'JFK', 'MIA', 'PHL', 'SJU'])]

# create a list of icao ids of the location to pass to the API
icao_list = locations['icao'].tolist()
# Download weather data from us and pr from meteostat and save csv file
# api.get_weather_stations('US', 'data/us_weather_stations.csv')
# api.get_weather_stations('PR', 'data/pr_weather_stations.csv')
# find the weather stations near the 6 relevant airports
us_stations_df = pd.read_csv('data/us_weather_stations.csv')

# create dataframe of weather stations in Puerto Rico
pr_stations_df = pd.read_csv('data/pr_weather_stations.csv')

# create a dataframe of all weather stations in US and Puerto Rico
all_weather_stations_df = pd.concat([us_stations_df, pr_stations_df], axis=0)

# filter dataframe on the icao identifiers of the 6 airports
weather_stations_df = all_weather_stations_df[all_weather_stations_df['icao'].isin(icao_list)]
display(weather_stations_df)
# airports from SQL 'BOS', 'EWR', 'JFK', 'MIA', 'PHL', 'SJU' 
airports = get_data('''SELECT city,
                   faa,
                   -- name,
                   lat,
                   lon,
                   alt
            FROM airports
            WHERE faa IN ( 'BOS', 'EWR', 'JFK', 'MIA', 'PHL', 'SJU' )
            LIMIT 5''')
# in order to use lat and lon values I am going to create a dictionary with:
    # key = icao
    # values = latitude, longitude
stations_dict = dict()
for i in range(len(weather_stations_df)):
    key = weather_stations_df['icao'].iloc[i]
    stations_dict[key] = weather_stations_df['latitude'].iloc[i], weather_stations_df['longitude'].iloc[i]
    
display(stations_dict)
# start = dt.datetime(2012, 10, 1)
# end = dt.datetime(2012, 12, 31) 
# api.get_weather_daily(stations_dict, start, end, 'data/weather_2012_4quarter.csv')
#start = dt.datetime(2011, 10, 1)
#end = dt.datetime(2011, 12, 31) 
#api.get_weather_daily(stations_dict, start, end, 'data/weather_2011_4quarter.csv')
# Code	Meaning
# TEMP	Air Temperature
# TAVG	Average Temperature
# TMIN	Minimum Temperature
# TMAX	Maximum Temperature
# DWPT	Dew Point
# PRCP	Total Precipitation
# WDIR	Wind Direction
# WSPD	Average Wind Speed
# WPGT	Wind Peak Gust
# RHUM	Relative Humidity
# PRES	Sea-Level Air Pressure
# SNOW	Snow Depth
# TSUN	Total Sunshine Duration
# COCO	Weather Condition Code
# create a dataframe with the weather data
weather_df_2011 = pd.read_csv('data/weather_2011_4quarter.csv')
weather_df_2012 = pd.read_csv('data/weather_2012_4quarter.csv')

# change time column to datatime data type
weather_df_2011['time'] = pd.to_datetime(weather_df_2011['time'])
weather_df_2012['time'] = pd.to_datetime(weather_df_2012['time'])

# drop the empty column wpgt
weather_df_2011.drop('wpgt', axis=1, inplace=True)
weather_df_2012.drop('wpgt', axis=1, inplace=True)

# create weather_df to upload the data of both years in one file
weather_df = pd.concat([weather_df_2011, weather_df_2012], axis=0)

display(weather_df_2011.head(2))
display(weather_df_2012.head(2))
# add an additional column with the 'icaoidentifier' to the weather df
weather_df = weather_df.merge(locations[['icao', 'faa']], how='left', on='icao')

# move 'faa' column to the first 2 position
weather_df = weather_df[['time', 'icao', 'faa', 'tavg', 'tmin', 'tmax', 'prcp', 'snow', 'wdir',
       'wspd', 'pres', 'tsun']]
# save weather data to database
# table_name = 'group4_weather_data'
# gd.save_to_db(weather_df, table_name)<jupyter_output><empty_output><jupyter_text>Join - Prepare Data for EDA<jupyter_code># SQL query to get the flights data
flights_df = pd.read_sql("SELECT * FROM public.group4_flights_data", get_conn())
flights_df.head(2)
# SQL query to get the weather data
# weather_df = pd.read_sql("SELECT * FROM public.group4_weather_data", get_conn())
# weather_df.head(2)
# Join flights with weather
flights_df['fk'] = flights_df['flight_date'].astype('str') + flights_df['origin'] # or dest?
weather_df['pk'] = weather_df['time'].astype('str') + weather_df['faa']

# Left join flight_df with weather
# flight_data = flights_df.merge(weather_df,
#                                     how='left',
#                                     left_on='fk',
#                                     right_on='pk')
# flight_data.info()
# Add column period month
flight_data['period_month'] = pd.to_datetime(flight_data["flight_date"]).dt.to_period('M')<jupyter_output><empty_output><jupyter_text>5. EDA<jupyter_code>display(flight_data.groupby('period_month')[['cancelled', 'diverted']].sum())
display(flight_data.groupby(['icao', 'period_month'])[['cancelled']].sum())
grouped_cancelled = flight_data[flight_data['flight_date'] >= '2012-10-01'].groupby('flight_date')[['cancelled']].sum()
grouped_cancelled.plot(figsize=(10,6))
flight_data.groupby('period_month').agg({'wspd': ['mean', 'min', 'max']})
# work in progress as fuck
r_cancelled = flight_data.groupby(['icao', 'period_month'])[['cancelled']].sum()
r_cancelled = r_cancelled.reset_index()
r_cancelled[r_cancelled['period_month'].dt.year == 2012]<jupyter_output><empty_output><jupyter_text>VISU<jupyter_code># time_axes = 
col_list = ['tavg', 'wspd', 'prcp']

for col in col_list:
    fig, ax = plt.subplots(figsize=(10,6))

    # x = 
    # y = 

    sns.lineplot(x=weather_df_2011['time'], y=weather_df_2011[col], color='r', ci=None, ax=ax, label='2011')
    sns.lineplot(x=weather_df_2011['time'], y=weather_df_2012[col], color='b', ci=None, ax=ax, label='2012')
    
    ax.set_title(col.capitalize())
    #ax.legend()
    plt.show()
column_list = weather_df.columns
for col in column_list[2:]:
    weather_df[weather_df['icao'] == 'TJSJ'].plot(kind='line', x='time', y=col, figsize=(8,6), rot=90)
# good to work with temperature and windspeed
# maybe perspiration

column_list = ['tavg', 'wspd', 'prcp']
for col in column_list:
    weather_df[weather_df['icao'] == 'TJSJ'].plot(kind='line', x='time', y=col, figsize=(8,6), rot=90)
display(stations_dict)
# create a list of airports ordered by the course of the hurricane
ordered_airports = ['TJSJ', 'KMIA', 'KPHL', 'KJFK', 'KEWR', 'KBOS']
# plot only the average temperature for all airports
for airport in ordered_airports:
    weather_df[weather_df['icao'] == airport].plot(kind='line', x='time', y='wspd', figsize=(8,6), rot=90)
# plot only the average temperature for all airports
for airport in ordered_airports:
    weather_df[weather_df['icao'] == airport].plot(kind='line', x='time', y='prcp', figsize=(8,6), rot=90)
from sklearn.linear_model import LinearRegression
from sklearn.model_selection import train_test_split
import statsmodels.formula.api as smf
flight_data.info()
results
corr = flight_data.corr()
sns.heatmap(corr);
sns.pairplot(flight_data[['dep_delay', 'cancelled', 'tavg', 'prcp', 'wspd']])
data_sub = flight_data[(flight_data['flight_date'].dt.year == 2012) & (flight_data['flight_date'].dt.month == 10) & (flight_data['flight_date'].dt.day >= 20) & (flight_data['flight_date'].dt.day <= 26)]
smf.ols(formula='cancelled ~ wspd', data=data_sub).fit().summary()
smf.ols(formula='cancelled ~ prcp', data=flight_data[(flight_data['flight_date'].dt.year == 2012) & (flight_data['flight_date'].dt.month == 10)]).fit().summary()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10962163416898793, 'fraction_numerical': 0.023328690807799445, 'mean_word_length': 3.835297418630752, 'pattern_counts': {'":': 0, '<': 38, '<?xml version=': 0, '>': 40, 'https://': 4, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 11, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '541223', 'license': 'MIT', 'n_tokens_mistral': 5994, 'n_tokens_neox': 5470, 'n_words': 1475, 'path': '05_final.ipynb', 'repo_name': 'sergioserge/us-airports-project', 'size': '17232'}