<jupyter_start><jupyter_text>Data Collection Note: Few cells within this notebook are hidden because they contain sensitve information about accessing my SQL db2 server account on IBM Cloud.<jupyter_code># The code was removed by Watson Studio for sharing.<jupyter_output><empty_output><jupyter_text>Install the necessary package.<jupyter_code>!pip install watson-developer-cloud==1.5
!pip install --user ipython-sql 
!pip install --user ibm_db 
!pip install --user ibm_db_sa
!pip install matplotlib<jupyter_output>Requirement already satisfied: watson-developer-cloud==1.5 in /opt/conda/envs/Python36/lib/python3.6/site-packages (1.5.0)
Requirement already satisfied: Twisted>=13.2.0 in /opt/conda/envs/Python36/lib/python3.6/site-packages (from watson-developer-cloud==1.5) (19.2.1)
Requirement already satisfied: autobahn>=0.10.9 in /opt/conda/envs/Python36/lib/python3.6/site-packages (from watson-developer-cloud==1.5) (19.7.1)
Requirement already satisfied: python-dateutil>=2.5.3 in /opt/conda/envs/Python36/lib/python3.6/site-packages (from watson-developer-cloud==1.5) (2.7.5)
Requirement already satisfied: pyOpenSSL>=16.2.0 in /opt/conda/envs/Python36/lib/python3.6/site-packages (from watson-developer-cloud==1.5) (19.0.0)
Requirement already satisfied: service-identity>=17.0.0 in /opt/conda/envs/Python36/lib/python3.6/site-packages (from watson-developer-cloud==1.5) (18.1.0)
Requirement already satisfied: requests<3.0,>=2.0 in /opt/conda/envs/Python36/lib/python3.6/site-packages (from watson-devel[...]<jupyter_text>Import the necessary libraries<jupyter_code>import requests
import urllib.request
import time
import datetime
import csv
import re
import unicodedata
import ibm_db
from bs4 import BeautifulSoup
import pandas as pd 
import scipy as sp
import numpy as np
import matplotlib.pyplot as plt
import glob
import seaborn as sns<jupyter_output><empty_output><jupyter_text>I've pre-defined some redundant functions here so as to reduce the complexity and size of the code<jupyter_code>#function to check if we have access to the website
def access_to_server(a):
    if(access.status_code == 200):
        print("Access to PlaneCrashInfo.com: Granted")
    else:
        print("Access to PlaneCrashInfo.com: Denied")
        
#function to pause for a little so that the website server does not flag us as a spammer
def take_a_break():
    time.sleep(0.1)      #we pause for 3 seconds

#function to access the website
def access_website(url):
    access = None
    access = requests.get(url,timeout=10)
    return access   

def get_data(search_string, regex):
    pattern = re.compile(regex)
    matches = pattern.finditer(search_string)

    for match in matches:
        match = match.span()

    span = search_string[match[0]:match[1]]
    return span

#predefine a dataframe to store out clean data
#headers = ['Date','Time','Location','Operator','Flight#','Route','AC Type','Registration','cn/ln','Total Aboard','Passengers Aboard','Crew Aboard','Total Fatalities','Passenger Fatalities','Crew Fatalities','Ground','Summary']
df = pd.DataFrame(columns=['Date','Time','Location','Operator','Flight_Number','Route','AC_Type','Registration','cn/ln','Total_Aboard','Passengers_Aboard','Crew_Aboard','Total_Fatalities','Passenger_Fatalities','Crew_Fatalities','Ground','Summary'])
clean_data= df
clean_data<jupyter_output><empty_output><jupyter_text>Next, we set the url to the Plane Crash Info website and access the site with our requests library. We also set the base url to create a dictionary with years as the key.<jupyter_code>base_url = "http://www.planecrashinfo.com"
access = requests.get('http://www.planecrashinfo.com/database.htm')
access_to_server(access)
source = BeautifulSoup(access.text, 'html.parser')<jupyter_output>Access to PlaneCrashInfo.com: Granted<jupyter_text>Here we build a dictionary with the url stores as the value and the year as the key<jupyter_code>a_tags = source.find_all("a")
years = {a.text.strip(): {"url": base_url + a["href"] if a["href"][0] == "/" else base_url + "/" + a["href"]} for a in a_tags if a.text.strip().isdigit()}<jupyter_output><empty_output><jupyter_text>Here we pause for a little to avoid getting flagged as a spammer.<jupyter_code>take_a_break()<jupyter_output><empty_output><jupyter_text>Now we start collecting the unstructured data<jupyter_code>done = 0
for year, database in years.items():       #first we loop for each year from 1920 until 2019
    print(year)
    #if year == '1921':
    #    break
    take_a_break()
    response_year = access_website(database["url"])
    access_to_server(response_year)
    parser_year = BeautifulSoup(response_year.content, 'html.parser')
    a_tags2 = parser_year.find_all("a")
    a_tags2 = [a for a in a_tags2 if "Return to Home" not in a.text]
    for a in a_tags2:
            # request to crash detail page
            response_crash = access_website(base_url + "/" + year + a["href"] if a["href"][0] == "/" else base_url + "/" + year + "/" + a["href"])
            if (response_crash.status_code == 404):
                continue
            parser_crash = BeautifulSoup(response_crash.content, 'html.parser')
            tr_tags = parser_crash.find_all("tr")
            tr_tags = tr_tags[1:]

            database = [tr.find_all("td")[1].text.strip() for tr in tr_tags]  
            x = database
            #Splitting the data in the aboard row
            aboard_row = x[9]
            #remove \xa0 from the string
            aboard_row = unicodedata.normalize("NFKD", aboard_row)
            #remove duplicate spaces from the string
            aboard_row = re.sub(' +', ' ',aboard_row)
            #split the string to extract the data easily
            split_aboard_row = aboard_row.split(" ")
            #find the total number of people aboard
            total_aboard = split_aboard_row[0]
            #total_aboard = int(total_aboard)
            #find the total number of passengers aboard
            passengers_aboard = split_aboard_row[1]
            passengers_aboard = passengers_aboard[-1]
            #find the total number of crew members aboard
            crew_aboard = split_aboard_row[2]
            crew_aboard = crew_aboard[-2]

            #Splitting the data in the fatalities row
            fatalities_row = x[10]
            #remove \xa0 from the string
            fatalities_row = unicodedata.normalize("NFKD", fatalities_row)
            #remove duplicate spaces from the string
            fatalities_row = re.sub(' +', ' ',fatalities_row)
            #split the string to extract the data easily
            split_fatalities_row = fatalities_row.split(" ")
            #find the total number of fatalities
            total_fatalities = split_fatalities_row[0]
            #find the total number of passenger fatalities
            passenger_fatalities = split_fatalities_row[1]
            passenger_fatalities = passenger_fatalities[-1]
            #find the total number of crew fatalities
            crew_fatalities = split_fatalities_row[2]
            crew_fatalities = crew_fatalities[-2]

            #save data for reorganization
            ground = x[11]
            summary = x[12]
            x[9] = total_aboard
            x[10] = passengers_aboard
            x[11] = crew_aboard
            x[12] = total_fatalities

            u= [passenger_fatalities, crew_fatalities, ground, summary]
            x.extend(u)
            #appending the values to the previously generated dataframe
            #df1 = pd.DataFrame(x) 
            #q= df1.T
            #clean_data = clean_data.append(q, ignore_index=True, sort=False)
            #df = pd.DataFrame(columns=['Date','Time','Location','Operator','Flight#','Route','AC Type','Registration','cn/ln','Total Aboard','Passengers Aboard','Crew Aboard','Total Fatalities','Passenger Fatalities','Crew Fatalities','Ground','Summary'])
            clean_data = clean_data.append({'Date':x[0],'Time':x[1],'Location':x[2],'Operator':x[3],'Flight_Number':x[4],'Route':x[5],'AC_Type':x[6],'Registration':x[7],'cn/ln':x[8],'Total_Aboard':x[9],'Passengers_Aboard':x[10],'Crew_Aboard':x[11],'Total_Fatalities':x[12],'Passenger_Fatalities':x[13],'Crew_Fatalities':x[14],'Ground':x[15],'Summary':x[16]}, ignore_index=True)
data_copy = clean_data
data_copy
#total_aboard
#Save the dataframe as a CSV to Object Storage
#Commented out after saving once. 
project.save_data(data=data_copy.to_csv(index=False),file_name='planecrashinfo_data1711.csv',overwrite=True)<jupyter_output><empty_output><jupyter_text>Saving the dataframe as CSV file, I loaded it into IBM db2 database as backup and to allow future access. Below is just a test code to access that SQL databse table. This test code has been used again in the other Jupyter notebooks for the explolatory data analysis.<jupyter_code>#perform simplified database access using SQL "magic"
#%load_ext sql
# %sql ibm_db_sa://my-username:my-password@dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net:50000/BLUDB
#the cell after this one is hidden because it contains senstive information that provides access to my IBM Cloud account. The code is similar to the line above except for the username and password.
# The code was removed by Watson Studio for sharing.
#%sql select * from PlaneCrashInfoDatabase<jupyter_output><empty_output><jupyter_text>Data Cleaning<jupyter_code>#just another copy of data
crash_data = data_copy
#First I have replaced all the ? with NaN so that we can change the datatypes of the columns easily)
crash_data = crash_data.replace('?', np.NaN)
#crash_data = crash_data.dropna(subset=['Total_Fatalities'],how='any', inplace=True)
#project.save_data(data=data_copy.to_csv(index=False),file_name='planecrashinfo_wNaN.csv',overwrite=True)
crash_data<jupyter_output><empty_output><jupyter_text>Exploratory Data Analysis  Total_Fatalities vs Date<jupyter_code>#I start with creating another copy of the dataframe called cr with just the two required columns
cr = crash_data.loc[:, ['Date','Total_Fatalities']]
#convert the 'Date' column to Datetime dtype
cr['Date'] =  pd.to_datetime(cr['Date'],format='%B %d, %Y')
#replace all the '?' values in both the columns with NaN
cr = cr[cr.Total_Fatalities != 'NaN']
#we now find out the number of rows that contain NaN values
cr.isna().sum()
#from the solution above, the column 'Date' has 0 NaN rows and the column 'Total_Fatalities' has 8 NaN rows. 
#Out of the 4964 observations, 8 observations only constitute of 0.16% of the data. Therefore, I have omitted these NaN values from the Total_Fatalities vs Date table.
cr=cr.dropna(axis=0)
#we check for rows with NaN values again, just to confirm.
cr.isna().sum()
#Now I convert the Total_Fatalities column to 'int32' dtype.
cr = cr.astype({'Total_Fatalities': 'int32'})
import seaborn as sns
# Use seaborn style defaults and set the default figure size
#sns.set(rc={'figure.figsize':(16, 4)})
#cr['Total_Fatalities'].plot(linewidth=0.5);
cr.plot(kind='line',x='Date',y='Total_Fatalities',color='m')
#now I have created another copy of cr because the above plot was ugly and I was looking for a visually appealing visualization.
cr2 = cr
cr2['year'] = pd.DatetimeIndex(cr2['Date']).year
cr2.describe()
#The plot below groups the 'Total_Fatalities' by year and then plots it; thereby, showing a better trend of crashes.
cr2.groupby("year")['Total_Fatalities'].count().plot(kind='line')
#Let us look at the amount of data that useful
#First, I duplicate the dataframe
plane_data = data_copy
#Replace '?' with NaN in all columns
plane_data = plane_data.replace('?', np.NaN)
#Display the total number of NaN values in each of the columns
plane_data.isna().sum()<jupyter_output><empty_output><jupyter_text>Based on the above, if I decided to delete all the rows with NaN Flight_Number, I would lose 73.5% of my data! Following a similar logic, I can see that only the columns of Date, Location, Operator, AC Type, Total Aboard, Total Fatalities, and Summary are useful. From these columns, only Summary has the highest number of NaNs of 64 observations. That accounts for 1.2% of my observations. Essentially I am not loosing a lot of data.<jupyter_code>#Now I create a dataframe with only the nececssary columns. 
cr3 = plane_data.loc[:, ['Date','Location','Operator','AC_Type','Total_Aboard','Total_Fatalities','Summary']]
#Delete all the rows that have NaN values
cr3=cr3.dropna(axis=0)
#Check for NaN
cr3.isna().sum()
#the clean CSV file
project.save_data(data=data_copy.to_csv(index=False),file_name='planecrashinfo_clean.csv',overwrite=True)
#Here I have changed the dtype of Date to datetime and that of Total_Aboard and Total_Fatalities to int32.
cr3 = cr3.astype({'Total_Aboard': 'int32','Total_Fatalities': 'int32'})
cr3['Date'] =  pd.to_datetime(cr3['Date'],format='%B %d, %Y')
#A test to verify the dtype conversion of the selected columns
cr3.dtypes
#create a back-up of cr3 for future use.
cp = cr3
cr3['year'] = pd.DatetimeIndex(cr3['Date']).year
#Plot total fatalities by year
cr3.groupby("year")['Total_Fatalities'].count().plot(kind='line')
#Plot the mean of the total fatalities by year 
cr3.groupby("year")['Total_Fatalities'].mean().plot(kind='line',color='k')<jupyter_output><empty_output><jupyter_text>Crashes by month<jupyter_code>cr3['Month'] = pd.DatetimeIndex(cr3['Date']).month
cr3.groupby("Month")['Total_Fatalities'].count().plot(kind='bar')
cr3.groupby("Month")['Total_Fatalities'].count().plot(kind='line',color='k',linestyle=':')
plt.xlabel('Months')
plt.ylabel('Crashes')
plt.title('Total number of crashes by month')
#sns.despine(ax=ax, top=True, right=True, left=True, bottom=False);<jupyter_output><empty_output><jupyter_text>WordCloud to analyse the summary column<jupyter_code>!pip install wordcloud
combine = cr3['Summary'].str.cat(sep=' ')
text = combine

from wordcloud import WordCloud, STOPWORDS, ImageColorGenerator
# Create and generate a word cloud image:
wordcloud = WordCloud().generate(text)

# Create stopword list:
stopwords = set(STOPWORDS)
stopwords.update(["plane", "crashed", "crash", "aircraft", "pilot","crew","plane crashed","aircraft crashed","airport","helicopter","killed",\
                  "cargo","route","approach","flight","runway","en","captain","caused","feet","water","shortly","taking","one","attempting land",\
                  "due","passenger","took","area","aboard","resulted","causing","groundattempting land","stalled","poor weather","ground","two"])

# Generate a word cloud image
wordcloud = WordCloud(max_font_size=50,stopwords=stopwords, background_color="white").generate(text)

# Display the generated image:
# the matplotlib way:
plt.imshow(wordcloud, interpolation='bilinear')
plt.axis("off")
plt.show()
plt.savefig("word_cloud.png", format="png")<jupyter_output><empty_output><jupyter_text>Alternative version of the plots above (Prettier versions)<jupyter_code># prepare plot
sns.set_style('white')
fig, ax = plt.subplots(figsize=(12,6))

# plot
cr3.set_index('Date').resample('Y').size().plot(label='Total per Year', color='grey', ax=ax)
cr3.set_index('Date').resample('Y').size().rolling(window=10).mean()\
                           .plot(color='darkorange', linewidth=5, label='10-Months Moving Average', ax=ax)

ax.set_title('Total number of plane crashes per Year', fontsize=16, fontweight='bold')
ax.set(ylabel='Total Crash Count\n', xlabel='Years')
ax.legend(bbox_to_anchor=(1.1, 1.1), frameon=False)

# remove all spines
sns.despine(ax=ax, top=True, right=True, left=True, bottom=False);
monthly_count = cr3['Date'].dt.month.value_counts().sort_index(ascending=False)

# prepare plot
sns.set_style('white')
fig, ax = plt.subplots(figsize=(12,5))

# plot
ax.bar(monthly_count.index, monthly_count.values, color='lightsteelblue')
ax.plot(monthly_count, linestyle=':', color='black')
ax.set_title('\nTotal number of plane crashes per Month\n', fontsize=16, fontweight='bold')
ax.set(ylabel='\nTotal Monthly Counts',xlabel='Months')

# remove all spines
sns.despine(ax=ax, top=True, right=True, left=True, bottom=True);<jupyter_output><empty_output><jupyter_text>What percent of people died?<jupyter_code>pc=cr3
t1 = pc.loc[:, ['Total_Fatalities','Total_Aboard']]
t_aboard = t1.Total_Aboard.sum()
t_deaths = t1.Total_Fatalities.sum()
t_survivors = t_aboard-t_deaths
slices = [t_survivors,t_deaths]
total = t_aboard
labels = ["Total Survivors", "Total Fatalities"]
cmap = plt.cm.Dark2_r

slices = sorted(slices)

fig = plt.figure(figsize=[7,7])
ax1 = fig.add_subplot(111)

pie_wedge_collection = ax1.pie(slices, labels=labels, labeldistance=1.05, autopct='%.2f');

for pie_wedge in pie_wedge_collection[0]:
    pie_wedge.set_edgecolor('white')

#rcParams['font.size'] = 20.0
#ax1.set_title("Out of all the people aboard a crashing plane...")
cr3<jupyter_output><empty_output><jupyter_text>AC Type that has crashed the most<jupyter_code>t2 = pc.loc[:, ['Total_Fatalities','AC_Type']]
Group_AC = t2.groupby('AC_Type')
t_groupAC = Group_AC.agg({'AC_Type':{'total':'count'}}).reset_index()
t_groupAC.columns = ['AC_Type','Total']
t_groupAC = t_groupAC.sort_values(by=['Total'], ascending=False)
t3 = t_groupAC[:10]
# prepare plot
sns.set_style('white')
fig, ax = plt.subplots(figsize=(7,4))

# plot
ax.barh(t3.AC_Type, t3.Total, color='lightsteelblue')
ax.set_title('\nTop 10 aircraft types causing crashes\n', fontsize=16, fontweight='bold')
ax.set(ylabel='\nAircraft Types',xlabel='Crashes')

# remove all spines
sns.despine(ax=ax, top=True, right=True, left=True, bottom=True);<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12932563061259508, 'fraction_numerical': 0.01755991534633644, 'mean_word_length': 4.959100204498977, 'pattern_counts': {'":': 1, '<': 68, '<?xml version=': 0, '>': 73, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 1, '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': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '84503', 'license': 'Apache-2.0', 'n_tokens_mistral': 5935, 'n_tokens_neox': 5690, 'n_words': 1468, 'path': 'Plane Crash Info Assignment.ipynb', 'repo_name': 'maulinamin/PlaneCrashInfo', 'size': '17483'}