<jupyter_start><jupyter_text>Exploratory Data AnalysisUse this notebook to get familiar with the datasets we have. There is 10 questions we need to answer during the EDA.Create 1 slide with informative visualization for each question. Explore data using SQL and Python but use Tableau for final visualization.Don't limit your EDA to these 10 questions. Let's be creative :).  Set up connection to database<jupyter_code>import psycopg2
import pandas as pd

host = 'mid-term-project.ca2jkepgjpne.us-east-2.rds.amazonaws.com'
port = "5432"
user = 'lhl_student'
pwd = '<PASSWORD>'
database = 'mid_term_project'

conn = psycopg2.connect(user = user,
                              password = <PASSWORD>,
                              host = host,
                              port = port,
                              database = database)
def query(query, conn=conn):
    return pd.read_sql(query, conn)<jupyter_output><empty_output><jupyter_text>***   **Task 1**: Test the hypothesis that the delay is from Normal distribution. and that **mean** of the delay is 0. Be careful about the outliers.    Query ARR_DELAY from the flights table<jupyter_code>df_arr_delay = query('select ARR_DELAY from flights')<jupyter_output><empty_output><jupyter_text>Find how many NULLs in the dataset as a percentage of the total number of rows<jupyter_code>df_arr_delay.isnull().sum() / df_arr_delay.shape[0]<jupyter_output><empty_output><jupyter_text>A very small percentage, so we can probably delete them.<jupyter_code>df_arr_delay.dropna(inplace=True)<jupyter_output><empty_output><jupyter_text>Visualize outliers<jupyter_code>import seaborn as sns
import matplotlib.pyplot as plt
sns.set_style('whitegrid')

sns.boxplot(data=df_arr_delay)
plt.show()

sns.histplot(df_arr_delay, bins=100)
plt.show()
import scipy.stats as stats
import numpy as np
print('Mean: ', df_arr_delay['arr_delay'].mean())
print('Median: ', df_arr_delay['arr_delay'].median())
df_arr_delay.describe()<jupyter_output><empty_output><jupyter_text>Remove outliers using Z-scores for reach observation. Keep only rows with Z-scores less than 3.<jupyter_code>#find absolute value of z-score for each observation
z = np.abs(stats.zscore(df_arr_delay))

#only keep rows in dataframe with all z-scores less than absolute value of 3 
df_arr_delay_z = df_arr_delay[(z<3).all(axis=1)]<jupyter_output><empty_output><jupyter_text>Visualize dataset without outliers and observe for normality.<jupyter_code>sns.boxplot(data=df_arr_delay_z)
plt.show()

sns.histplot(df_arr_delay_z, bins=100)
plt.show()

import pylab 
stats.probplot(df_arr_delay_z['arr_delay'], dist="norm", plot=pylab)
pylab.show()<jupyter_output><empty_output><jupyter_text>The graphs seem to show that the distribution is not normal.    Statistical tests for normality.<jupyter_code>print('Mean: ', df_arr_delay_z['arr_delay'].mean())
print('Median: ', df_arr_delay_z['arr_delay'].median())<jupyter_output>Mean:  1.0646501473382453
Median:  -6.0<jupyter_text>Mean and median are rather different. Therefore, distribution is not normal.<jupyter_code>stats.shapiro(df_arr_delay_z)
stats.normaltest(df_arr_delay_z)
stats.skewtest(df_arr_delay_z)
stats.kurtosistest(df_arr_delay_z)<jupyter_output><empty_output><jupyter_text>The p-values are all the tests = 0.0, therefore we can reject the null hypothesis that the distribution is normal. The distribution is not normal.    Statistical test that mean = 0<jupyter_code>stats.ttest_1samp(df_arr_delay_z, 0)<jupyter_output><empty_output><jupyter_text>The p-value = 0.0, therefore we can reject the null hypothesis that the mean of the distribution is equal to zero.   ***   **Task 2**: Is average/median monthly delay different during the year? If yes, which are months with the biggest delays and what could be the reason?   ***   **Task 3**: Does the weather affect the delay? Use the API to pull the weather information for flights. There is no need to get weather for ALL flights. We can choose the right representative sample. Let's focus on four weather types:- sunny- cloudy- rainy- snow.Test the hypothesis that these 4 delays are from the same distribution. If they are not, which ones are significantly different?   ***   **Task 4**: How taxi times changing during the day? Does higher traffic lead to bigger taxi times?  Query necessary data from flights<jupyter_code>df_flights = query('select fl_date, origin_airport_id, taxi_out, taxi_in from flights')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09166479442821837, 'fraction_numerical': 0.012581442372500562, 'mean_word_length': 5.323863636363637, 'pattern_counts': {'":': 0, '<': 47, '<?xml version=': 0, '>': 46, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '557128', 'license': 'MIT', 'n_tokens_mistral': 1466, 'n_tokens_neox': 1389, 'n_words': 434, 'path': 'EDA/_jimi_exploratory_analysis_task_01.ipynb', 'repo_name': 'Einhard6176/FlightDelayPredictor', 'size': '4445'}