Final project, Data 601, Spring 2019 - Analysis of New York State Fire Department Fallen Heroes Data¶
- Dataset 1: New York State Fallen Firefighters Memorial Roll of Honor data
- Dataset 2: Fire Department Directory for New York State
- Merging column: Fire Department Name
About the data:¶
- My final project combines two different datasets. The datasets cover different aspects of New York State Fire Departments.
- The first dataset; "New York State Fallen Firefighters Memorial Roll of Honor", contains the names, ranks department names, dates of death and locations on Memorial Wall of Firefighters who have sacrificed their lives in order to save others.
- The second dataset; "Fire Department Directory for New York State", contains an annually update list of names, location, phone numbers and Division of Homeland Security and Emergency Services (DHSES) ID for Fire Departments in New York State. The data is collected and maintained by the Office of Fire Prevention & Control (OFPC) within DHSES.
- I found this data through the suggestions for project data ideas on Blackboard.
My story:¶
- Firefighters are one of the most amazing and selfless people in the world. I beleive that most humans, given a choice, would always save themselves. Firefighters on the other hand are the brave few people in the world who would jump into a fire to save another person.
- There have been many tragedies in this world. However, only a handful of them have had an impact as big as the attack on September 11th, 2001.
- When I saw these two datasets, I thought I could combine the data to identify patterns about such incidents from temporal data of fatalities in fire departments of New York State. I think this data analysis will help me see the impact of that one incident in comparison to others.
- While exploring patterns in the combined dataset, following questions came to my mind:
- Everyone knows about September 11th, 2001 and that people died but can it be determined from the combined data, which county most of the firefighters came from?
- Key Observation - Combining the two datasets I am able to infer that on September 11, 2001, New York County happened to face a severe crisis, in which hundreds of firefighters gave their lives.
- The firefighters that sacrificed their lives on 9/11 came from New York County.
- Story time - 9/11 was one of the biggest terrorist attacks in American History on American soil killing thousands of people.
- This link specifies that on that day:
- "Of the 2,977 victims killed in the September 11 attacks, 412 were emergency workers in New York City who responded to the World Trade Center."
- "This included: 343 firefighters (including a chaplain and two paramedics) of the New York City Fire Department (FDNY)".
- My dataframe - top_date_of_death_county_df shows that on 9/11, 2001 New York County had in-fact had 343 fatalities.
- During 9/11 was there a fire department where most of the fatalities occurred?
- During 9/11 the fire department located at "9 metrotech center", Brooklyn, New York seems to be the station from which most of the firefighters operated.
- Intuitively this looks reasonable as the World Trade Center is in lower Manhattan that is very close to Brooklyn by waterways.
- It is understandable that there will be few high ranking officials directly involved in rescue operations. Does the combined data tell us the ranks of the firefighters that sacrificed their lives?
- I was able to determine that most fatalities on 9/11 happened for the "Firefighter" rank.
- The second highest fatalities were for "Lieutenant" rank followed by "Captain" and "Battallion Chief".
- Is there any incident comparable to 9/11 in terms of fire fighter fatalities? Where did such an incident occur?
- Using my analysis I was also able to determine that there is no incident, as per the datasets, that can compare to 9/11, in terms of fatalities. However, it looks like on August 2nd, 1978 there was an incident that might have caused some fatalities. After googling around, I found that the Waldbaum Fire of August 2, 1978 had killed six firefighters.
- Which fire departments across the state of New York have had the most fatalities? Can the top ten fatal locations be represented visually with information about the most fatal incident for that location?
- I was also able to visualize using a map of New York State, the dates of the most fatal incident, alongwith count of fatalities. I was able to visualize the most fatal incident ranked by top ten most fatal locations across the state of New York.
- Everyone knows about September 11th, 2001 and that people died but can it be determined from the combined data, which county most of the firefighters came from?
Data properties and access information:¶
- Data available through Fire Department Directory Data from NYS Open Data site and New York State Fallen Firefighters Memorial Roll of Honor.
- Download Link 1 and Link 2 for data source.
- Downloaded file named: "New_York_State_Fallen_Firefighters_Memorial_Roll_of_Honor.csv" and "Fire_Department_Directory_for_New_York_State.csv".
- These two data sources can be merged based on the Fire Department's name column that can be found in both files.
- There is no cost to accessing this data.
- Accessing this data does not require creation of an account.
- Accessing this data does not violate any laws.
- This data do not appear to have been previously analyzed based on a Google search.
- A preliminary survey of the data in New_York_State_Fallen_Firefighters_Memorial_Roll_of_Honor.csv indicates there are 2549 rows, 5 columns, and the file is 196 KB. The Fire_Department_Directory_for_New_York_State.csv has 1773 rows, 12 columns, and the file is 245 KB.
In [1]:
!pip install folium
!pip install nltk
import numpy as np
import pandas as pd
import time
import datetime
from datetime import datetime
import calendar
import chardet
import missingno as msno
import matplotlib
import matplotlib.pyplot as plt
import seaborn as sns
import os
import random
import re
import folium
import nltk
nltk.download('stopwords')
from nltk.corpus import stopwords
nltk.download('punkt')
from nltk.tokenize import word_tokenize
import string
import warnings
warnings.filterwarnings("ignore")
start_time = time.time()
print('\n\nPandas',pd.__version__)
print('Matplotlib',matplotlib.__version__)
print('Seaborn',sns.__version__)
print('File Size In KB for New_York_State_Fallen_Firefighters_Memorial CSV: ',(os.path.getsize('New_York_State_Fallen_Firefighters_Memorial_Roll_of_Honor.csv')/1000),' KB')
print('File Size In KB for Fire_Department CSV : ',(os.path.getsize('Fire_Department_Directory_for_New_York_State.csv')/1000),' KB')
NY = 'New York State'
Exploring data¶
Encoding check for the input CSV files to ensure data is in right format
In [2]:
with open('New_York_State_Fallen_Firefighters_Memorial_Roll_of_Honor.csv','rb') as fraw:
file_content = fraw.read()
In [3]:
chardet.detect(file_content)
Out[3]:
Character encoding of the CSV file is utf-8 and confidence level is 0.99(99%). Hence I will provide encoding='utf-8' while loading the file into Pandas DataFrame using read_csv()
In [4]:
with open('Fire_Department_Directory_for_New_York_State.csv','rb') as fraw:
file_content = fraw.read()
In [5]:
chardet.detect(file_content)
Out[5]:
Character encoding of the CSV file is ascii and confidence level is 1(100%).
Exploring file contents from the CSVs:
In [6]:
!head -n 3 New_York_State_Fallen_Firefighters_Memorial_Roll_of_Honor.csv
In [7]:
!head -n 3 Fire_Department_Directory_for_New_York_State.csv
Next, I will extract the data from CSV files and insert into dataframes for processing
In [8]:
pd.options.display.max_rows = 40
start_time_before_load_file1 = time.time()
fire_fighters_df = pd.read_csv("New_York_State_Fallen_Firefighters_Memorial_Roll_of_Honor.csv",encoding ='utf-8')
print('Time taken to load fire fighters data : ',time.time() - start_time_before_load_file1,'seconds')
print('Before Shape of fire_fighters_df : ',fire_fighters_df.shape)
print('Dropping rows with duplicate data from fire_fighters_df.')
fire_fighters_df.drop_duplicates()
print('After Shape of fire_fighters_df : ',fire_fighters_df.shape)
start_time_before_load_file2 = time.time()
fire_department_df = pd.read_csv("Fire_Department_Directory_for_New_York_State.csv")
print('\n\nTime taken to load fire department data : ',time.time() - start_time_before_load_file2,'seconds')
print('Before Shape of fire_department_df : ',fire_department_df.shape)
print('Dropping rows with duplicate data from fire_department_df.')
fire_department_df.drop_duplicates()
print('After Shape of fire_department_df : ',fire_department_df.shape)
- The dataframes do not have any duplicate data.
The New_York_State_Fallen_Firefighters_Memorial_Roll_of_Honor.csv/fire_fighters_df dataframe contains 2549 rows and 5 columns.
The Fire_Department_Directory_for_New_York_State.csv/fire_department_df dataframe contains 1773 rows and 12 columns.
Let us explore the data a bit using head(), tail(), info(), describe() for both dataframes
In [9]:
fire_fighters_df.head()
Out[9]:
In [10]:
fire_fighters_df.tail()
Out[10]:
In [11]:
fire_fighters_df.info()
In [12]:
fire_fighters_df.describe()
Out[12]:
In [13]:
fire_department_df.head()
Out[13]:
In [14]:
fire_department_df.tail()
Out[14]:
In [15]:
fire_department_df.info()
In [16]:
fire_department_df.describe()
Out[16]:
In [17]:
fire_department_df.describe(include='all')
Out[17]:
In [18]:
fire_department_df.describe(include='object')
Out[18]:
Data Cleanup and Pre-processing before joining the dataframes¶
In [19]:
print('Number of firefighter names in fire_fighters_df : ',fire_fighters_df['Name'].count())
print('Number of unique firefighter names in fire_fighters_df : ',fire_fighters_df['Name'].nunique())
print('\n')
print('Number of unique Fire Department Names in fire_department_df : ',fire_department_df['Fire Department Name'].nunique())
print('Number of unique Fire Department Codes in fire_department_df : ',fire_department_df['Fire Department Code'].nunique())
print('Number of rows in fire_department_df : ',fire_department_df.shape[0])
Obsevation 1
- In fire_fighters_df 14 people have same name which is fine.
Obsevation 2
- Number of unique Fire Department Names are lesser than Fire Department Codes in fire_department_df.
- This indicates that there are some Fire Department Names with different Fire Department Codes in fire_deparment_df. This will cause duplicate row entries in merged dataframe after joining.
- Hence, I need to drop data from fire_deparment_df that have the same department name but different department codes. From these multiple entries I can only keep one entry. As the fire_fighters_df does not specify a code or address for department, I can simply match by name and choose to keep one. I will fix this in step 1 below.
Obsevation 3
- Number of rows in fire_department_df is 1773 but unique Fire Department Codes in fire_department_df is 1772.
- This indicates that there are at least two rows with same Fire Department Code. This will create duplicate entries in merged dataframe.
- Hence, I need to check whether the department with same department code exist in fire_fighters_df. If it does not exist then removing those department codes will not affect the analysis.
- Thus I will remove this data discrepancy in fire_department_df without any effect on anything else. I will fix this in step 2 below.
Step 1
In [20]:
'''
Dropping data to keep one entry for fire departments that have same department names but different department codes from fire_deparment_df.
'''
print('Index with same department name but different department code in fire_deparment_df:')
for department in list(fire_department_df['Fire Department Name'].value_counts().index):
if fire_department_df[fire_department_df['Fire Department Name']==department]['Fire Department Name'].value_counts().values > 1:
index_list_of_same_department_name=list(fire_department_df[fire_department_df['Fire Department Name']==department].index)
print(index_list_of_same_department_name)
for indx in index_list_of_same_department_name[:-1]:
fire_department_df.drop(index=indx,inplace=True)
Step 2
In [21]:
fire_department_df['Fire Department Code'].value_counts().head()
Out[21]:
In [22]:
'''
Looking for the rows with same department code
'''
fire_department_df[fire_department_df['Fire Department Code']==42004]
Out[22]:
In [23]:
'''
Checking whether the department name with duplicate deparment code exists in fire_fighters_df
'''
fighters_department_list=list(fire_fighters_df['Fire Department'].apply(lambda x : x.split(' ')[0].lower()))
exits='N'
for fighters_department in fighters_department_list:
if (fighters_department == 'brunswick') or (fighters_department =='brushton') :
exits = 'Y'
break
if exits=='Y' :
print('Yes, the department name with duplicate deparment code exists in fire_fighters_df.')
else:
print('No, the department name with duplicate deparment code does not exists in fire_fighters_df.')
In [24]:
'''
As, the department name with duplicate deparment code does not exist in fire_fighters_df, dropping this data from fire_deparment_df will not
affect the analysis. Therefore, I am dropping it.
'''
for department_code in list(fire_department_df['Fire Department Code'].value_counts().index):
if fire_department_df[fire_department_df['Fire Department Code']==department_code]['Fire Department Code'].value_counts().values > 1:
index_list_of_same_department_code=list(fire_department_df[fire_department_df['Fire Department Code']==department_code].index)
print('Index list of same department code : ',index_list_of_same_department_code)
for indx in index_list_of_same_department_code:
fire_department_df.drop(index=indx,inplace=True)
print('Dropped index : ',indx)
In [25]:
print('After cleanup shape of fire_department_df : ',fire_department_df.shape)
print('Number of unique Fire Department Name in fire_department_df : ',fire_department_df['Fire Department Name'].nunique())
print('Number of unique Fire Department Code in fire_department_df : ',fire_department_df['Fire Department Code'].nunique())
Now, the fire_department_df dataframe contains 1753 rows and 12 columns.
Pre-processing Messy Deparment Names¶
In [26]:
def department_preprocess(department):
"""
Takes in a string of department name, then performs the following:
1. Convert department name to lowercase
2. Split department name in words
3. Remove all punctuations
4. Remove empty strings from department name
5. Remove all stopwords
6. Remove repetitive filler words from department name
Replace words that can cause issues while join is performed based on department name.
7. Join cleaned department name tokens back to a phrase
8. Complete spelling corrections of department name
9. Return clean department name
"""
en_stops = set(stopwords.words('english'))
"""
Convert department name to lowercase
"""
department_lowercase = department.lower()
"""
Split department name in words
"""
list_of_words = word_tokenize(department_lowercase)
"""
Remove all punctuations
Check characters to see if they are in punctuation
"""
list_of_words_without_punctuation=[''.join(this_char for this_char in this_string if (this_char not in string.punctuation))for this_string in list_of_words]
"""
Remove empty strings from department name
"""
list_of_words_without_punctuation = list(filter(None, list_of_words_without_punctuation))
"""
Remove any stopwords
"""
filtered_word_list = [w for w in list_of_words_without_punctuation if w not in en_stops]
"""
Remove repetitive filler words from department name
"""
ignore_lst=['fire','department','city','vol','volunteer','no','district',
'dept','deparment','company','co','engine']
filtered_word_list = [w for w in filtered_word_list if w not in ignore_lst]
"""
Join cleaned department name tokens back to a phrase
"""
clean_department=' '.join(filtered_word_list)
"""
Complete spelling corrections of department name
"""
clean_department = clean_department.replace('centre','center')
clean_department = clean_department.replace('ctr','center')
clean_department = clean_department.replace('cliff','clift')
clean_department = clean_department.replace('springs','spring')
clean_department = clean_department.replace('sq','square')
clean_department = clean_department.replace('tact','tract')
"""
Return clean department name
"""
return clean_department
In [27]:
'''
For fire_fighters_df :
Convert column data to lowercase
Create new column with 'Department' as column name
'''
fire_fighters_df = fire_fighters_df.applymap(lambda s:s.lower() if type(s) == str else s)
fire_fighters_df['Department']=fire_fighters_df['Fire Department']
fire_fighters_df.head()
Out[27]:
In [28]:
'''
Applying department_preprocess() to the new key column of fire_fighters_df
'''
fire_fighters_df['Department']=fire_fighters_df['Department'].apply(department_preprocess)
fire_fighters_df.head()
Out[28]:
In [29]:
'''
For fire_department_df :
Convert column data to lowercase
Create new column with 'Department' as column name
'''
fire_department_df = fire_department_df.applymap(lambda s:s.lower() if type(s) == str else s)
fire_department_df['Department']=fire_department_df['Fire Department Name']
fire_department_df.head()
Out[29]:
In [30]:
'''
Applying department_preprocess() to the new key column of fire_department_df
'''
fire_department_df['Department']=fire_department_df['Department'].apply(department_preprocess)
fire_department_df.head()
Out[30]:
In [31]:
'''
Check after creation and key column preprocess for fire_department_df if there are departments that have same key.
'''
for department in list(fire_department_df['Department'].value_counts().index):
if fire_department_df[fire_department_df['Department']==department]['Department'].value_counts().values > 1:
print(department)
In [32]:
'''
Viewing rows of fire_department_df with same Department key
'''
fire_department_df[fire_department_df['Department'].isin(['lincoln','johnson','lakeside'])]
Out[32]:
After joining if the above departments creates duplicate rows in merge df I will need to take care of that.
Merging the two dataframes into one dataframe
In [33]:
merge_df = pd.merge(fire_fighters_df,fire_department_df,how='inner',on='Department')
print('Shape of merge_df : ',merge_df.shape)
print('Number of unique name in merge_df : ',merge_df['Name'].nunique())
print('Number of unique Fire Department Name in merge_df : ',merge_df['Fire Department Name'].nunique())
print('Percentage of records lost in merge_df after processing data from fire_fighters_df : ',
((fire_fighters_df.shape[0]-merge_df.shape[0])/fire_fighters_df.shape[0])*100)
print('\n\n')
merge_df.head()
Out[33]:
- Earlier I observed that in fire_fighters_df 14 people have same name. Here I see 2462 - 2447 = 15 people have same name. So there is one duplicate record.
Next I will be checking for duplicate records in merge_df and removing it in meaningfull way
In [34]:
'''
Function to concatenate of values passed as parameters
'''
def new_col(cols):
return cols[0]+cols[1]+cols[2]+str(cols[3])+cols[4]
In [35]:
'''
Applying new_col function on those columns of the merge_df which are from fire_fighter_df
'''
merge_df['firefighter']=merge_df[['Name','Rank','Fire Department','Date of Death','Location on Memorial']].apply(new_col,axis=1)
In [36]:
"""
Checking whether any duplicate entries from fire_fighter_df exists or not in merge_df after merging.
If it exists I will delete the duplicate after matching with original Fire Department and Fire Department Name column
"""
for this_firefighter in list(merge_df['firefighter'].value_counts().index):
if merge_df[merge_df['firefighter']==this_firefighter]['firefighter'].value_counts().values > 1:
index_list_of_repeated_firefighter=list(merge_df[merge_df['firefighter']==this_firefighter].index)
print('Index list of repeated fire fighter data in merge_df : ',index_list_of_repeated_firefighter)
remove_index=merge_df[(merge_df['firefighter']==this_firefighter)
& (merge_df['Fire Department']!=merge_df['Fire Department Name'])].index
print('Index to remove : ',remove_index.values)
merge_df.drop(index=remove_index.values,inplace=True)
Reseting Index of merge_df
In [37]:
'''
Reseting Index of merge_df after droping indexes
'''
merge_df=merge_df.reset_index(drop=True)
'''
The firefighter column is no longer needed so, I am dropping the column.
'''
merge_df.drop(['firefighter'], axis=1,inplace=True)
print('After cleanup the shape of merge_df is : ',merge_df.shape)
After cleanup the merge_df has 2461 rows and 18 columns
I have my final data analysis dataframe now.
Let's explore the data a bit using head(), tail(), info(), describe() on merge_df
In [38]:
merge_df.head()
Out[38]:
In [39]:
merge_df.tail()
Out[39]:
In [40]:
merge_df.info()
In [41]:
merge_df.describe()
Out[41]:
In [42]:
merge_df.describe(include='all')
Out[42]:
In [43]:
merge_df.describe(include='object')
Out[43]:
Next, I will explore the column metadata...
- What are the data types for the columns in our data?
- How many unique entries are there in each column where type is object?
- Below I will exlpore the first five rows of each column where type is object?
- Why am I exploring unique entries for objects?
- Because there could possibly be categorical data or datetime data in an object column.
- After finishing the data exploration I will transform these object type columns with categorical data into 'category' type and object type columns with datetime data into 'datetime' type
In [44]:
first_n_entries=5
print('Total rows in the dataframe:', merge_df.shape[0])
for col, col_type in merge_df.dtypes.iteritems():
if(col_type=='object'):
print(col, 'has', merge_df[col].nunique(), 'unique entries')
print('First', first_n_entries, 'entries are')
print(merge_df[col][0:first_n_entries])
print('')
- In the data set, there are thirteen object type columns: Name, Rank, Fire Department, Date of Death, Location on Memorial, Department, Fire Department Name, Address, City, State, Zip Code, County Name, Location 1
Data Type Transformation¶
- Now, I will count the frequency of these unique values per column and print frequency of top five most frequent elements.
- I will check if a column with object data type has categorical data or not?
- I will check if a column with object data type has datetime data or not?
- If and when necessary, I will perform some transformations on the data.
In [45]:
for this_column in merge_df.columns:
print('====', this_column, 'has', merge_df[this_column].nunique(), 'unique entries ====')
print(merge_df[this_column].value_counts().head(5))
print('')
- After exploring the data I observed that Rank, Fire Department, Location on Memorial, Department, Fire Department Name, City, State, Zip Code, County Name columns contain categorical data.
- I will transform these columns into 'category' data type.
- Also Date of Death column contain datetime data.
- I will transform the above column into 'datetime' data type.
In [46]:
"""
Next, I transform the object data type for Rank to 'category' data type
"""
merge_df['Rank'] = merge_df['Rank'].astype('category')
merge_df['Rank'].dtype
Out[46]:
In [47]:
"""
Next, I transform the object data type for Fire Department to 'category' data type
"""
merge_df['Fire Department'] = merge_df['Fire Department'].astype('category')
merge_df['Fire Department'].dtype
Out[47]:
In [48]:
"""
Next, I transform the object data type for Location on Memorial to 'category' data type
"""
merge_df['Location on Memorial'] = merge_df['Location on Memorial'].astype('category')
merge_df['Location on Memorial'].dtype
Out[48]:
In [49]:
"""
Next, I transform the object data type for Department to 'category' data type
"""
merge_df['Department'] = merge_df['Department'].astype('category')
merge_df['Department'].dtype
Out[49]:
In [50]:
"""
Next, I transform the object data type for Fire Department Name to 'category' data type
"""
merge_df['Fire Department Name'] = merge_df['Fire Department Name'].astype('category')
merge_df['Fire Department Name'].dtype
Out[50]:
In [51]:
"""
Next, I transform the object data type for City to 'category' data type
"""
merge_df['City'] = merge_df['City'].astype('category')
merge_df['City'].dtype
Out[51]:
In [52]:
"""
Next, I transform the object data type for State to 'category' data type
"""
merge_df['State'] = merge_df['State'].astype('category')
merge_df['State'].dtype
Out[52]:
In [53]:
"""
Next, I transform the object data type for Zip Code to 'category' data type
"""
merge_df['Zip Code'] = merge_df['Zip Code'].astype('category')
merge_df['Zip Code'].dtype
Out[53]:
In [54]:
"""
Next, I transform the object data type for County Name to 'category' data type
"""
merge_df['County Name'] = merge_df['County Name'].astype('category')
merge_df['County Name'].dtype
Out[54]:
In [55]:
"""
Next, I transform the object data type for Date of Death to 'datetime' data type
"""
merge_df['Date of Death']=pd.to_datetime(
merge_df['Date of Death'],
format='%m/%d/%Y')
Let us look at the data types of columns after transformation¶
In [56]:
merge_df.dtypes
Out[56]:
Now the dataframe has...¶
- Three object type columns: Name, Address and Location 1
- One datetime Type columns: Date of Death
- Nine categorical columns: Rank, Fire Department, Location on Memorial, Department, Fire Department Name, City, State, Zip Code, County Name
- Five numerical columns: Fire Department Code, County Code with data type int64,Phone Number, Latitude, Longitude with data type float64
Data clean up, Missing data detection and Fill up¶
Black = filled; white = empty
In [57]:
"""
Searching for missing data in sample set of 200 randomly selected data points
"""
_=msno.matrix(merge_df.sample(200))
plt.xlabel('Features in data',fontsize=16)
plt.ylabel('Gaps in data',fontsize=16)
plt.show()
In [58]:
"""
Searching for missing data in sample set of 2000 randomly selected data points
"""
_=msno.matrix(merge_df.sample(2000))
plt.xlabel('Features in data',fontsize=16)
plt.ylabel('Gaps in data',fontsize=16)
plt.show()
From the graphs one can see the data has some missing values for few columns like Date of Death, Phone Number, Latitude, Longitude, Location 1.
Data Clean up¶
I will perform the following tasks:
- Drop all rows and columns where entire row or column is NaN.
- Drop columns with duplicate data or with 50% missing value.
- Drop columns where all rows have the same value.
- Such columns have no data variety and nothing useful to contribute to my data analysis.
In [59]:
print('Shape of data frame before Cleanup :',merge_df.shape)
print('Drop all rows and columns where entire row or column is NaN.')
merge_df.dropna(how='all',axis=0,inplace=True) # rows
merge_df.dropna(how='all',axis=1,inplace=True) # columns
print('Drop columns with duplicate data or with 50% missing value.')
half_count = len(merge_df)*.5
merge_df = merge_df.dropna(thresh=half_count, axis=1)
merge_df = merge_df.drop_duplicates()
print('Drop columns where all rows have the same value.')
for this_column in merge_df.columns:
if (merge_df[this_column].nunique()==1):
unique_entry=merge_df.iloc[0][this_column]
print('Drop column ',this_column,' where all rows have the same value : ', unique_entry)
merge_df.drop([this_column],axis=1,inplace=True)
print('Shape of data frame after cleanup :',merge_df.shape)
Through the above process I was able to conclude that in my dataset...¶
- There are no rows and columns where entire row or column is NaN.
- There are no columns with duplicate data and with 50% missing value.
- There is one column, State where all rows have the same value.
- Hence, I will be dropping the column State as it has no data variety and nothing useful to contribute to my data analysis.
Missing data detection and fill up using random sampling in a meaningful way¶
That is get data from the same County Name
In [60]:
merge_df.head().T
Out[60]:
In [61]:
"""
Counting null data per column
"""
merge_df.isnull().sum()
Out[61]:
In [62]:
"""
Percentage of missing data per column
"""
(merge_df.isnull().sum()/len(merge_df)).sort_values(ascending=False)
Out[62]:
I was able to find that Location 1, Latitude, Longitude, Phone Number, Date of Death columns have some missing data¶
Filling up missing data of Location 1, Latitude and Longitude through sampling of data in same County Name
In [63]:
print("Data index for missing Location 1 : ",list(merge_df[merge_df['Location 1'].isnull()].index))
print("Data index for missing Latitude : ",list(merge_df[merge_df['Latitude'].isnull()].index))
print("Data index for missing Longitude : ",list(merge_df[merge_df['Longitude'].isnull()].index))
In [64]:
'''
County Name based sampling for Location 1, Latitude and Longitude data
'''
location1_smapling_dict={}
latitude_smapling_dict={}
longitude_smapling_dict={}
null_index=list(merge_df[merge_df['Location 1'].isnull()].index)
print(null_index)
for indx in null_index:
print('index :',indx)
this_county=merge_df.iloc[indx]['County Name']
print(this_county)
sample_location1=random.choice(list(merge_df[(merge_df['County Name']==this_county)
& (merge_df['Location 1'].notnull())]['Location 1']))
sample_latitude=float(sample_location1.split(',')[0][1:])
sample_longitude=float(sample_location1.split(',')[1][:-1].strip())
location1_smapling_dict[indx]=sample_location1
latitude_smapling_dict[indx]=sample_latitude
longitude_smapling_dict[indx]=sample_longitude
print(location1_smapling_dict)
print(latitude_smapling_dict)
print(longitude_smapling_dict)
In [65]:
'''
Filling up the missing values of Location 1, Latitude and Longitude with sampled data
'''
merge_df['Location 1'].fillna(location1_smapling_dict,inplace=True)
merge_df['Latitude'].fillna(latitude_smapling_dict,inplace=True)
merge_df['Longitude'].fillna(longitude_smapling_dict,inplace=True)
Filling up missing data of Phone Number through sampling of data in same County Name
In [66]:
print("Data index for missing Phone Number : ",list(merge_df[merge_df['Phone Number'].isnull()].index))
In [67]:
'''
County Name based sampling for Phone Number data
'''
phone_number_smapling_dict={}
null_phone_number_index=list(merge_df[merge_df['Phone Number'].isnull()].index)
print(null_phone_number_index)
for indx in null_phone_number_index:
print('index :',indx)
this_county=merge_df.iloc[indx]['County Name']
print(this_county)
sample_phone_number=random.choice(list(merge_df[(merge_df['County Name']==this_county)
& (merge_df['Phone Number'].notnull())]['Phone Number']))
phone_number_smapling_dict[indx]=sample_phone_number
print(phone_number_smapling_dict)
In [68]:
'''
Filling up the missing values of Phone Number with sampled data
'''
merge_df['Phone Number'].fillna(phone_number_smapling_dict,inplace=True)
Filling up missing data of Date of Death through sampling of data in same County Name
In [69]:
print("Data index for missing Date of Death : ",list(merge_df[merge_df['Date of Death'].isnull()].index))
In [70]:
'''
County Name based sampling for Date of Death data
'''
null_date_of_death_index=list(merge_df[merge_df['Date of Death'].isnull()].index)
print('null_date_of_death_index : ',null_date_of_death_index)
this_county=merge_df.iloc[null_date_of_death_index]['County Name'].values
print('this_county : ',this_county)
sample_date_of_death=datetime.strftime(random.choice(list(merge_df[(merge_df['County Name']==this_county) &
(merge_df['Date of Death'].notnull())]['Date of Death'])),'%m/%d/%Y')
print('sample_date_of_death : ',sample_date_of_death)
sample_date_of_death_in_datetime=datetime.strptime(sample_date_of_death,'%m/%d/%Y')
print('sample_date_of_death_in_datetime : ',sample_date_of_death_in_datetime)
In [71]:
'''
Filling up the missing values of Date of Death with sampled data
'''
merge_df['Date of Death'].fillna(sample_date_of_death_in_datetime,inplace=True)
In [72]:
merge_df.isnull().sum()
Out[72]:
Missing data have been filled up successfully for Location 1, Latitude, Longitude, Phone Number and Date of Death columns
Start of data analysis - Visualization and Exploratory Data Analysis¶
... for merged data of New_York_State_Fallen_Firefighters_Memorial_Roll_of_Honor, Fire_Department_Directory_for_New_York_State in New York State
Let's ask our data some questions about fire fighter fatalities in New York State.
- Let's start with some basic questions that combines the fatality count from one dataset with the county/city data from another.
Which county and/or city had a high fatality count?
Top ten fatality count by county for New York State fire departments
In [73]:
merge_df['County Name']=merge_df['County Name'].apply(lambda x : x.strip())
merge_df.groupby('County Name')['Name'].count().sort_values(ascending=False).head(10)
Out[73]:
- Top ten fatality count by city for fire departments in New York State
In [74]:
merge_df['City']=merge_df['City'].apply(lambda x : x.strip())
merge_df.groupby('City')['Name'].count().sort_values(ascending=False).head(10)
Out[74]:
The answer to the question is Brooklyn, New York County, New York has had some very high fatality counts. I will now dive deeper into the data to find more about patterns in fatalities in New York State's fire departments.
Method defined to compute normalized value for a column of dataframe
Formula for normalization used is as follows:
$\mathbf{X_{new}} = {X - X_{min} \over X_{max} - X_{min}}$
In [75]:
'''
This method will return the value normalized between 0 and 1, given a number, maximum value and minimum value of the column in the dataframe
'''
def compute_norm(number, max_val, min_val):
return (number - min_val)/(max_val - min_val)
In [76]:
'''
This method will take a dataframe and return a dataframe with one extra column of normalized values for the colum of dataframe that
needs to be normalized. Created as I will reuse this a number of times.
'''
def get_normalized_value_df(df_to_process, name_of_column_to_normalize):
norm_df = df_to_process
normalized_value_list = []
for num in np.array(norm_df[name_of_column_to_normalize]):
normalized_value_list.append(compute_norm(number=float(num),
max_val=float(norm_df[name_of_column_to_normalize].nlargest(1)),
min_val=float(norm_df[name_of_column_to_normalize].nsmallest(1))
)
)
norm_df['normalized_'+name_of_column_to_normalize] = normalized_value_list
return norm_df
Processing date time to extract year, month of death
In [77]:
'''
Counting the number of fatalities in each of the top five fatal years
'''
merge_df['Year'] = merge_df['Date of Death'].apply(lambda time: time.year)
merge_df['Year'].value_counts().head()
Out[77]:
In [78]:
'''
Counting the number of fatalities by month
'''
merge_df['Month'] = (merge_df['Date of Death'].dt.month).apply(lambda x : calendar.month_abbr[int(x)])
months=['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
merge_df['Month'] = pd.Categorical(merge_df['Month'],
categories=months,
ordered=True)
merge_df['Month'].value_counts()
Out[78]:
Above two analysis indicates that there was some significant incident that happened in the month of September. It also indicates that 2001 was a dangerous year for firefighters
- As I have two different datasets, one with records of dates of an incident and the other has relevant location information, if I combine the two datasets I can infer when and where such an incident occurred!
- Hence, I am trying to visualize dates by fatality count for top five fatal years and top five fatal counties combination.
Exploring fatalities by date of death for a county in New York State¶
- Extracting the top five fatal counties by fatality count for processing
In [79]:
top_counties_of_fire_fighter_death = merge_df.groupby(['County Name'])['Name'].count().sort_values(ascending=False).head().index.values
top_counties_of_fire_fighter_death
Out[79]:
In [80]:
top_counties_of_fire_fighter_death_df = merge_df[merge_df['County Name'].isin(top_counties_of_fire_fighter_death)]
- Extracting the top five years by fatality count for processing
In [81]:
top_five_year_of_fire_fighter_death = merge_df.groupby(['Year'])['Name'].count().sort_values(ascending=False).head().index.values
top_five_year_of_fire_fighter_death
Out[81]:
- Extracting the top five years from top five fatal counties by fatality count for processing
In [82]:
top_death_county_year_df = top_counties_of_fire_fighter_death_df[
top_counties_of_fire_fighter_death_df['Year'].isin(top_five_year_of_fire_fighter_death)]
In [83]:
top_date_of_death_county_df=pd.DataFrame(list(top_death_county_year_df.groupby(['Date of Death','County Name'])['Name']
.count().sort_values(ascending=False).head(10).items()),
columns=['date_of_death_county_combo','death_count'])
In [84]:
top_date_of_death_county_df.plot.bar(x='date_of_death_county_combo',y='death_count',figsize=(15,10))
plt.title('Number of fatalities by date of death in top five fatal counties of '+ NY,fontsize=18)
plt.setp(plt.gca().get_xticklabels(), rotation=90, fontsize=12)
plt.setp(plt.gca().get_yticklabels(), fontsize=12)
plt.xlabel('Combination of date of death and county',fontsize=16)
plt.ylabel('Number of fatalities',fontsize=16)
plt.show()
In [85]:
'''
Computing the normalized count of fatalities by date of death for top five fatal years in top five fatal counties
I am computing normalized count to detect if 2001-09-11 combined with New York County is an outlier in terms of number of fatalities.
'''
top_date_of_death_county_norm_df = get_normalized_value_df(df_to_process=top_date_of_death_county_df,
name_of_column_to_normalize='death_count')
top_date_of_death_county_norm_df
Out[85]:
In [86]:
top_date_of_death_county_norm_df.plot.bar(x='date_of_death_county_combo', y='normalized_death_count', figsize=(15,10))
plt.setp(plt.gca().get_xticklabels(), rotation=90, fontsize=12)
plt.setp(plt.gca().get_yticklabels(), fontsize=12)
plt.xlabel('Combination of date of death and county',fontsize=16)
plt.ylabel('Normalized count of fatalities',fontsize=16)
plt.title('Normalized count of fatalities by date in top five fatal years and top five fatal counties',fontsize=18)
plt.show()
Key observation:¶
Combining the two datasets I am able to infer that on September 11, 2001, New York County happened to face a severe crisis, in which hundreds of firefighters gave their lives.
Answering our question 1 - Everyone knows about September 11th, 2001 and that people died but can it be determined from the combined data, which county most of the firefighters came from?
- The firefighters that sacrificed their lives on 9/11 they came from New York County.
Story time
9/11 was one of the biggest terrorist attacks in American History on American soil killing thousands of people.
This link specifies that on that day:
- "Of the 2,977 victims killed in the September 11 attacks, 412 were emergency workers in New York City who responded to the World Trade Center."
- "This included: 343 firefighters (including a chaplain and two paramedics) of the New York City Fire Department (FDNY)".
- The top_date_of_death_county_df shows that on 9/11, 2001 New York County had 343 fatalities.
Thus by combining two datasets I am able to prove that using the power of data analysis one may make fairly accurate inferences about real-life incidents.
Next, I will try to find the answer to my second question - During 9/11 was there a fire department where most of the fatalities occurred?
- For that purpose I will try and extract the location data for the department address and combine that with fatality count. Both these pieces of data are present in my merged dataframe and will help me determine the exact department.
Top ten fire department location for incident
In [87]:
top_death_year_df=merge_df[merge_df['Year'].isin(top_five_year_of_fire_fighter_death)]
top_death_date_address_wise_df=top_death_year_df.groupby(['Date of Death','Address'])['Name'].count().sort_values(ascending=False).head(21)
In [88]:
top_death_date_address_df=pd.DataFrame(list(top_death_date_address_wise_df.items()),
columns=['date_of_death_address_combo','death_count'])
In [89]:
top_death_date_address_df.plot.bar(x='date_of_death_address_combo',y='death_count',figsize=(15,10))
plt.setp(plt.gca().get_xticklabels(), rotation=90, fontsize=12)
plt.setp(plt.gca().get_yticklabels(), fontsize=12)
plt.xlabel('Combination of date of death and address',fontsize=16)
plt.ylabel('Number of fatalities',fontsize=16)
plt.title('Number of fatalities by date of death and address',fontsize=18)
plt.show()
In [90]:
'''
Computing the normalized count of fatalities for addresses
I am computing normalized counts to detect if address and 2001-09-01 has outliers for number of fatalities.
'''
top_death_date_address_norm_df = get_normalized_value_df(df_to_process=top_death_date_address_df,
name_of_column_to_normalize='death_count')
In [91]:
top_death_date_address_norm_df.plot.bar(x='date_of_death_address_combo', y='normalized_death_count', figsize=(15,10))
plt.setp(plt.gca().get_xticklabels(), rotation=90, fontsize=12)
plt.setp(plt.gca().get_yticklabels(), fontsize=12)
plt.xlabel('Combination of date of death and address',fontsize=16)
plt.ylabel('Normalized count of fatalities',fontsize=16)
plt.title('Normalized count of fatalities by date of death and address',fontsize=20)
plt.show()
- From the chart above I was able to determine that "9 metrotech center" of Brooklyn New York is the station from which most of the firefighters operated.
- Intuitively this looks reasonable as the World Trade Center is in lower Manhattan that is very close to Brooklyn by waterways.
- Thus, I have answered the second question.
Our third question is about ranks of firefighters who passed away on 9/11 - It is understandable that there will be few high ranking officials directly involved in rescue operations. Does the combined data tell us the ranks of the firefighters that sacrificed their lives?
- I do have rank information in the firefighter dataset. I can combine the date of deaths with county of fire department in the other dataset to determine the ranks of firefighters that died on 9/11.
Analyzing fatalities by county and firefighter ranks
In [92]:
top_five_rank_of_fire_fighter_death = merge_df.groupby(['Rank'])['Name'].count().sort_values(ascending=False).head().index.values
top_five_rank_of_fire_fighter_death
Out[92]:
In [93]:
top_death_county_year_rank_df = top_death_county_year_df[
top_death_county_year_df['Rank'].isin(top_five_rank_of_fire_fighter_death)]
In [94]:
top_death_date_of_death_county_rank_df=pd.DataFrame(list(top_death_county_year_rank_df.groupby(['Date of Death','County Name','Rank'])['Name']
.count().sort_values(ascending=False).head(10).items()),
columns=['date_of_death_county_rank_combo','death_count'])
In [95]:
top_death_date_of_death_county_rank_df.plot.bar(x='date_of_death_county_rank_combo',y='death_count',figsize=(15,10))
plt.title('Number of fatalities by date of top five fatal years, counties and ranks in ' + NY,fontsize=18)
plt.setp(plt.gca().get_xticklabels(), rotation=90, fontsize=12)
plt.setp(plt.gca().get_yticklabels(), fontsize=12)
plt.xlabel('Combination of date of death, county and rank',fontsize=16)
plt.ylabel('Number of fatalities',fontsize=16)
plt.show()
In [96]:
'''
Computing the normalized count of fatalities for top five fatal counties and rank
I am computing normalized counts to detect if new york, 2001-09-11, firefighter has outliers for number of fatalities.
'''
top_death_date_of_death_county_rank_norm_df = get_normalized_value_df(df_to_process=top_death_date_of_death_county_rank_df,
name_of_column_to_normalize='death_count')
In [97]:
top_death_date_of_death_county_rank_norm_df.plot.bar(x='date_of_death_county_rank_combo', y='normalized_death_count', figsize=(15,10))
plt.setp(plt.gca().get_xticklabels(), rotation=90, fontsize=12)
plt.setp(plt.gca().get_yticklabels(), fontsize=12)
plt.xlabel('Combination of date of death, county and rank',fontsize=16)
plt.ylabel('Normalized count of fatalities',fontsize=16)
plt.title('Normalized count of fatalities by date of death, county and rank',fontsize=20)
plt.show()
- From the chart above I was able to determine that most fatalities on 9/11 happened for the "Firefighter" rank.
- The second highest fatalities were for "Lieutenant" rank followed by "Captain" and "Battallion Chief".
- This link shows that a "Firefighter" is in-fact the lowest ranked official in the Fire Department of New York(FDNY).
This answers my third question.
The chart above can also help answer the fourth question I had - Is there any incident comparable to 9/11 in terms of fire fighter fatalities? Where did such an incident occur?
- No, there is no incident, as per the datasets that can compare to 9/11, in terms of fatalities. However, it looks like on August 2nd, 1978 there was an incident that might have caused some fatalities. After googling around, I found that the Waldbaum Fire on August 2, 1978 had killed six firefighters.
- My fifth and final question was about trying to visualize fatalities across the whole state of New York - Which fire departments across the state of New York have had the most fatalities? Can the top ten fatal locations be represented visually with information about the most fatal incident for that location?
- I will try to visualize that now.
- I have not normalized the data because I want to show the count of fatalities.
In [98]:
'''
For this visualizaton I am using previously created dataframe for top ten fatal location to get the address, date_of_death and death_counts
value in different columns.I am breaking up the tuple in column date_of_death_address_combo to obtain the individual columns mentioned.
'''
top_death_count_date_address_list=[]
for date_address_tuple in list(top_death_date_address_df['date_of_death_address_combo']):
death_count_date_address_dict ={}
death_count_date_address_dict['death_count'] =top_death_date_address_df[top_death_date_address_df['date_of_death_address_combo']==date_address_tuple].iloc[0]['death_count']
death_count_date_address_dict['date_of_death'] =date_address_tuple[0]
death_count_date_address_dict['address'] =date_address_tuple[1]
top_death_count_date_address_list.append(death_count_date_address_dict)
top_death_count_date_address_df=pd.DataFrame(top_death_count_date_address_list)
top_death_count_date_address_df
Out[98]:
In [99]:
location_list=[]
all_loc_dict = {}
indx = 0
for address in list(top_death_count_date_address_df['address']):
county=merge_df[merge_df['Address']==address].iloc[0]['County Name']
city=merge_df[merge_df['Address']==address].iloc[0]['City']
zip_code=merge_df[merge_df['Address']==address].iloc[0]['Zip Code']
full_address=address + ", " + county + " county, " + city + " city, " + NY + ", " + zip_code
latitude=merge_df[merge_df['Address']==address].iloc[0]['Latitude']
longitude=merge_df[merge_df['Address']==address].iloc[0]['Longitude']
location1=merge_df[merge_df['Address']==address].iloc[0]['Location 1']
date_of_death=top_death_count_date_address_df.iloc[indx]['date_of_death']
death_count=top_death_count_date_address_df.iloc[indx]['death_count']
'''
Ignoring a location if it is repeating in the list.
As top_death_count_date_address_df is sorted by count of fatalities I will get the highest fatality count for an address
'''
if address not in list(all_loc_dict.keys()):
location_dict={}
location_dict['latitude']=latitude
location_dict['longitude']=longitude
location_dict['location']=location1
location_dict['lat_long']=[latitude,longitude]
location_dict['full_address']=full_address
location_dict['date_of_death']=date_of_death
location_dict['death_count']=death_count
all_loc_dict[address] = location_dict
indx += 1
for address_key, location_value in all_loc_dict.items():
location_list.append(location_value)
location_df = pd.DataFrame(location_list)
location_df
Out[99]:
In [100]:
def regioncolors(num_of_death):
if num_of_death > 100:
return 'red'
else:
return 'darkgreen'
location_df["color"] = location_df['death_count'].apply(regioncolors)
location_df
Out[100]:
Using the Folium library and Link let's take a look at where the incident locations are in New York State!
In [101]:
folium_map = folium.Map(location=location_df.iloc[0]['lat_long'],
zoom_start=7,
tiles='Stamen Terrain')
for curr_loc in list(location_df.index):
folium.Marker(location=location_df.iloc[curr_loc]['lat_long'],
popup="Date of most fatal incident : "+
datetime.strftime(location_df.iloc[curr_loc]['date_of_death'],'%B,%d,%Y')+
" Number of deaths on that date : "+
str(location_df.iloc[curr_loc]['death_count'])+
" Location : "+
location_df.iloc[curr_loc]['full_address'],
icon=folium.Icon(color=location_df.iloc[curr_loc]['color'],
icon_color='white',
icon='male',
angle=0,
prefix='fa')
).add_to(folium_map)
folium_map
Out[101]:
- If you click on each of the markers you can see the dates of the most fatal incident, alongwith number of fatalities.
- The visualization above shows the data for the most fatal incident ranked by top ten most fatal locations across the state of New York.
In [102]:
print('Total Time taken:',time.time() - start_time,'seconds')
Conclusions from Analysis of New York State Fire Department Fallen Heroes Data¶
- Key Observation - Combining the two datasets I am able to infer that on September 11, 2001, New York County happened to face a severe crisis, in which hundreds of firefighters gave their lives.
- The firefighters that sacrificed their lives on 9/11 they came from New York County.
- Story time - 9/11 was one of the biggest terrorist attacks in American History on American soil killing thousands of people.
- This link specifies that on that day:
- "Of the 2,977 victims killed in the September 11 attacks, 412 were emergency workers in New York City who responded to the World Trade Center."
- "This included: 343 firefighters (including a chaplain and two paramedics) of the New York City Fire Department (FDNY)".
- My dataframe - top_date_of_death_county_df shows that on 9/11, 2001 New York County had in-fact had 343 fatalities.
- During 9/11 the fire department located at "9 metrotech center", Brooklyn, New York seems to be the station from which most of the firefighters operated.
- Intuitively this looks reasonable as the World Trade Center is in lower Manhattan that is very close to Brooklyn by waterways.
- I was able to determine that most fatalities on 9/11 happened for the "Firefighter" rank.
- The second highest fatalities were for "Lieutenant" rank followed by "Captain" and "Battallion Chief".
- Using my analysis I was also able to determine that there is no incident, as per the datasets, that can compare to 9/11, in terms of fatalities. However, it looks like on August 2nd, 1978 there was an incident that might have caused some fatalities. After googling around, I found that the Waldbaum Fire of August 2, 1978 had killed six firefighters.
- I was also able to visualize using a map of New York State, the dates of the most fatal incident, alongwith count of fatalities. I was able to visualize the most fatal incident ranked by top ten most fatal locations across the state of New York.
Learnings from Final Project for Data 601¶
- I learnt about how to merge data from two different datasets and perform meaningful analysis from it.
- I learnt how to deal with messy data and carry out pre-processing in order to find proper key of joining.
- I used my knowledge from project two where I learnt about Folium library that allowed me to visualize data about fatalities from "Fire Department" locations on an actual map and infer occurrences of significant incidents.
- I was able to visualize the impact of an incident like 9/11 and compare that to smaller incidents like the Waldbaum Fire of August 2, 1978.