<jupyter_start><jupyter_text>Setup and Imports<jupyter_code>import pandas as pd
import numpy as np
import warnings
from pandas.io import gbq
from sklearn.model_selection import train_test_split<jupyter_output><empty_output><jupyter_text>Get the Data<jupyter_code>%%time
query = """
            SELECT incident_number, timestamp_of_call, incident_group, property_category, 
                    address_qualifier, borough_name, first_pump_arriving_attendance_time as first_time,
                    num_stations_with_pumps_attending as station_pumps, num_pumps_attending as pumps_attending,
                    FROM `gdac-327115.LondonFire.fire_brigade`
        """

london = gbq.read_gbq(query, project_id = "gdac-327115", location="eu")
def fetch_london_data(query_string, project_id, location):
    """
    This function fetches the london fire brigade data from Google BigQuery
    
    params:
        query_string - the string the contains the SQL code to query the data
        project-id - the unique project id for the Google BigQuery data
        location - where the data is being saved in Google BigQuery
        
    returns:
        data - queueried data
    """
    
    data = gbq.read_gbq(query_string, project_id = project_id, location=location)
    
    return data
#Check the shape
london.shape
#View the first few rows
london.head()
london.dtypes
#Check for missing values
london.isnull().sum()<jupyter_output><empty_output><jupyter_text>Split the Data into Training and Test Sets Since we are going to be predicting if the call is a false alarm, we'll want to our test observations to be the newest in the data set.<jupyter_code>london["timestamp_of_call"].dt.year.value_counts()<jupyter_output><empty_output><jupyter_text>We see that all of our observations are from the same year, therefore we can just randomly split the data. However, let's first check the proportion of calls that ended up being a false alarm.<jupyter_code>london["incident_group"].value_counts()<jupyter_output><empty_output><jupyter_text>Not all incidents were either a Fire or False Alarm. There is a third category, Special Service, as well. Since the entire purpose of this project is to reduce the number of false alarms that the department wastes its resources on, we'll combine Special Services and the Fire into one category called Emergency. We could have dropped all incidents with Special Service. However, the fire department has to respond to those types of calls so it's important to include them.<jupyter_code>emergency = ["Special Service", "Fire"]

london["incident_group"].replace(emergency, "Emergency", inplace = True)
def make_emergencies(df):
    """
    This function combines the two different emergencies found in the data set. It then creates a new indicator column that
    contains 1 if the observation is a real emergency and 0 if not
    
    params:
        df - dataframe with the necessary columns
    """
    emergency = ["Special Service", "Fire"]
    df["incident_group"].replace(emergency, "Emergency", inplace = True)
    df["Emergency"] = (df["incident_group"] == "Emergency").astype(np.int64)
    
london["incident_group"].value_counts() / london.shape[0]
#Finally split the data
london_train, london_test = train_test_split(london, test_size = .16, stratify = london["incident_group"], random_state=42)
print(london_train.shape)
print(london_test.shape)
london_train.reset_index(drop=True, inplace = True)
london_train.head()<jupyter_output><empty_output><jupyter_text>Clean the Data  Make indicator variable for Emergency outcome<jupyter_code>%%capture --no-stdout 
london_train["Emergency"] = (london_train["incident_group"] == "Emergency").astype(np.int64)
#Change the objects variables to categorical
#london_train.loc[:, london_train.dtypes == "object"] = london_train.select_dtypes(["object"]).apply(lambda x: x.astype("category"))<jupyter_output><empty_output><jupyter_text>Check for missing values<jupyter_code>london_train.isnull().sum()<jupyter_output><empty_output><jupyter_text>Incident Number<jupyter_code>#Number of observations
london_train.shape[0]
#Number of incidents
len(london_train["incident_number"].unique())<jupyter_output><empty_output><jupyter_text>As we'd hope, each observation in our data is a different reponse and there are no duplicates.   Time<jupyter_code>london_train["timestamp_of_call"].head()<jupyter_output><empty_output><jupyter_text>Create a Month variable<jupyter_code>%%capture --no-stdout
london_train["Month"] = london_train["timestamp_of_call"].dt.month
london_train["Month"].value_counts().reset_index().rename(columns={"index":"Month", "Month":"Count"}).sort_values(by="Month")
def create_month(df):
    """
    This function creates a column in the dataframe containing the month
    
    params:
        df - dataframe with necessary columns
    """
    df["Month"] = df["timestamp_of_call"].dt.month<jupyter_output><empty_output><jupyter_text>Create a Hour variable<jupyter_code>%%capture --no-stdout
london_train["Hour"] = london_train["timestamp_of_call"].dt.hour
london_train["Hour"].value_counts().reset_index().rename(columns={"index":"Hour", "Hour":"Count"}).sort_values(by="Hour")
def create_hour(df):
    """
    This function creates a column in the dataframe containing the hour
    
    params:
        df - dataframe with necessary columns
    """
    df["Hour"] = df["timestamp_of_call"].dt.hour<jupyter_output><empty_output><jupyter_text>Property Category<jupyter_code>london_train["property_category"].value_counts()
warnings.filterwarnings('ignore')
res = ["Dwelling", "Other Residential"]
london_train["property_category"].replace(res, "Residential", inplace = True)

vehicle = ["Road Vehicle", "Aircraft", "Boat", "Rail Vehicle"]
london_train["property_category"].replace(vehicle, "Vehicle", inplace = True)

london_train["property_category"].replace("Outdoor Structure", "Outdoor", inplace = True)
def merge_property(df):
    """
    This function combines categories found in the property category variable
    
    params:
        df - dataframe with the necessary variable
        
    """
    res = ["Dwelling", "Other Residential"]
    df["property_category"].replace(res, "Residential", inplace = True)

    vehicle = ["Road Vehicle", "Aircraft", "Boat", "Rail Vehicle"]
    df["property_category"].replace(vehicle, "Vehicle", inplace = True)

    df["property_category"].replace("Outdoor Structure", "Outdoor", inplace = True)
    
london_train["property_category"].value_counts()<jupyter_output><empty_output><jupyter_text>Address Qualifier<jupyter_code>london_train["address_qualifier"].value_counts()<jupyter_output><empty_output><jupyter_text>There are not too many different categories for the address qualifier variable. We'll combine a few of them based on logical relationships.<jupyter_code>warnings.filterwarnings('ignore')
street = ["In street outside gazetteer location", "In street remote from gazetteer location", 
          "In street close to gazetteer location", "Open land/water - nearest gazetteer location"]
london_train["address_qualifier"].replace(street, "Gazetter", inplace = True)

nearby = ["Nearby address - no building in street", "Nearby address - street not listed in gazetteer"]
london_train["address_qualifier"].replace(nearby, "Nearby Address", inplace = True)

other = ["On motorway / elevated road", "Railway land or rolling stock"]
london_train["address_qualifier"].replace(other, "Other", inplace = True)
london_train["address_qualifier"].value_counts()
def merge_address(df):
    """
    This function combines some of the categories together in the address qualifier variable.
    
    params:
        df - dataframe with the necessary columns
        
    """
    street = ["In street outside gazetteer location", "In street remote from gazetteer location", 
              "In street close to gazetteer location", "Open land/water - nearest gazetteer location"]
    df["address_qualifier"].replace(street, "Gazetter", inplace = True)

    nearby = ["Nearby address - no building in street", "Nearby address - street not listed in gazetteer"]
    df["address_qualifier"].replace(nearby, "Nearby Address", inplace = True)

    other = ["On motorway / elevated road", "Railway land or rolling stock"]
    df["address_qualifier"].replace(other, "Other", inplace = True)<jupyter_output><empty_output><jupyter_text>Borough Name<jupyter_code>london_train["borough_name"].value_counts()<jupyter_output><empty_output><jupyter_text>We see that there is one category, "NOT GEO_CODED" that needs to be cleaned up for the borough name variable. Let's check if it has a corresponding postcode.  We see that the postcode won't be useful in finding the correct borough for the missing values. Let's look at the ward.<jupyter_code>london_train.loc[london_train["borough_name"] == " NOT GEO-CODED", ["borough_name"]].value_counts()<jupyter_output><empty_output><jupyter_text>We also see that every missing value for borough also has a corresponding missing value for ward. Therefore, we'll just drop the "NOT GEO-CODED" from borough.<jupyter_code>%%capture --no-stdout
london_train["borough_name"].replace(" NOT GEO-CODED", np.nan, inplace=True)
london_train.dropna(subset=["borough_name"], inplace = True)
london_train["borough_name"].isnull().sum()
#Total number of boroughs
len(london_train["borough_name"].unique())
def drop_boroughs(df):
    """
    This function drops the boroughs that have missing values
    """
    df["borough_name"].replace(" NOT GEO-CODED", np.nan, inplace=True)
    df.dropna(subset=["borough_name"], inplace = True)<jupyter_output><empty_output><jupyter_text>First Pump Arriving Time<jupyter_code>london_train["first_time"].isnull().sum()<jupyter_output><empty_output><jupyter_text>Before we impute the missing values with either a mean/median, we should look at the distribution to get a sense of the spread in arriving time.<jupyter_code>london_train["first_time"].describe()<jupyter_output><empty_output><jupyter_text>We can see that mean and median are relatively close which is good, not too much skew. However, we see that the max value is 1,196 seconds which is more than 6 standard deviations away so quite unlikely. To be conservative, lets see how many arriving times are more than 4 standard deviations above the mean.<jupyter_code>pump_mean = np.mean(london_train["first_time"])
pump_std = np.std(london_train["first_time"])
#Any pump times above this should be flagged
cutoff_time = pump_mean + 4*pump_std
cutoff_time
london_train.loc[london_train["first_time"] > cutoff_time].shape
london_train.loc[london_train["first_time"] > cutoff_time].head()<jupyter_output><empty_output><jupyter_text>We can go ahead and drop those times that are more than 4 standard deviations above the mean arriving time.<jupyter_code>london_train = london_train.loc[(london_train["first_time"] < cutoff_time) | london_train["first_time"].isnull()]
london_train.shape<jupyter_output><empty_output><jupyter_text>Now we can go ahead and impute the missing values.<jupyter_code>london_train.loc[london_train["first_time"].notnull()].head()<jupyter_output><empty_output><jupyter_text>We can impute the mean by using which station the first pump came from.<jupyter_code>#Group by property type and summarize by the Emergency variable
pump_time_grp = london_train.groupby("borough_name")["first_time"]\
                        .aggregate(["median","mean", "std", "max", "min","count"])\
                        .sort_values(by="mean", ascending=False)
pump_time_grp.reset_index(inplace = True)
pump_time_grp
london_train["first_time"] = london_train["first_time"].astype(np.float64)
london_train["first_time"] = london_train.groupby("borough_name")["first_time"].\
                                                transform(lambda grp: grp.fillna(np.mean(grp)))
london_train["first_time"].isnull().sum()
london_train.head()
def arriving_time(df):
    """
    This function removes the values that are more than 4 standard deviations above the mean and imputes the missing values
    based on the station mean
    """
    pump_mean = np.mean(df["first_time"])
    pump_std = np.std(df["first_time"])
    #Any pump times above this should be flagged
    cutoff_time = pump_mean + 4*pump_std
    
    df = df.loc[(df["first_time"] < cutoff_time) | df["first_time"].isnull()]
    
    df["first_time"] = df["first_time"].astype(np.float64)
    df["first_time"] = df.groupby("borough_name")["first_time"].\
                                                transform(lambda grp: grp.fillna(np.mean(grp)))
    return df<jupyter_output><empty_output><jupyter_text>Number of Stations with Pumps Attending<jupyter_code>london_train["station_pumps"].isnull().sum()
london_train["station_pumps"].describe()
london_train["station_pumps"].value_counts()<jupyter_output><empty_output><jupyter_text>We see that only a very small amount of stations had more than 5 pumps attending. Therefore, we'll go ahead and drop any observations that have more than 5 pumps attending.<jupyter_code>london_train = london_train.loc[(london_train["station_pumps"] < 5) | (london_train["station_pumps"].isnull())]<jupyter_output><empty_output><jupyter_text>Now we can impute the missing values using the mean from which station the attending pumps came from.<jupyter_code>#Group by property type and summarize by the Emergency variable
station_pump_grp = london_train.groupby("borough_name")["station_pumps"]\
                        .aggregate(["median","mean", "std", "max", "min","count"])\
                        .sort_values(by="mean", ascending=False)
station_pump_grp.reset_index(inplace = True)
station_pump_grp
london_train["station_pumps"] = london_train["station_pumps"].astype(np.float64)
london_train["station_pumps"] = london_train.groupby("borough_name")["station_pumps"].\
                                                transform(lambda grp: grp.fillna(np.mean(grp)))
london_train["station_pumps"].isnull().sum()
def station_pumps(df):
    """
    This function removes any stations that have more than 5 pumps attending and imputes the missing values with the
    mean number of pump by stations.
    
    params:
        df - dataframe with the necessary columns
    """
    df = df.loc[(df["station_pumps"] < 5) | (df["station_pumps"].isnull())]
    df["station_pumps"] = df["station_pumps"].astype(np.float64)
    df["station_pumps"] = df.groupby("borough_name")["station_pumps"].\
                                                transform(lambda grp: grp.fillna(np.mean(grp)))
    df["station_pumps"] = df["station_pumps"].astype(np.str)
    return df<jupyter_output><empty_output><jupyter_text>Number of Pumps Attending<jupyter_code>london_train["pumps_attending"].isnull().sum()
london_train["pumps_attending"].describe()
london_train["pumps_attending"].value_counts()<jupyter_output><empty_output><jupyter_text>We'll go ahead and drop any pumps that are greater than 4.<jupyter_code>london_train = london_train.loc[(london_train["pumps_attending"] < 5) | (london_train["pumps_attending"].isnull())]
#Group by property type and summarize by the Emergency variable
pump_grp = london_train.groupby("borough_name")["pumps_attending"]\
                        .aggregate(["median","mean", "std", "max", "min","count"])\
                        .sort_values(by="mean", ascending=False)
pump_grp.reset_index(inplace = True)
pump_grp
london_train["pumps_attending"] = london_train["pumps_attending"].astype(np.float64)
london_train["pumps_attending"] = london_train.groupby("borough_name")["pumps_attending"].\
                                                transform(lambda grp: grp.fillna(np.mean(grp)))
def pumps_attending(df):
    """
    This function removes any pumps that are greater than 5 and imputes the missing number of pumps with the mean number of 
    pumps per station
    
    params:
        df - dataframe with necessary columns
    """
    df = df.loc[(df["pumps_attending"] < 5) | (df["pumps_attending"].isnull())]
    df["pumps_attending"] = df["pumps_attending"].astype(np.float64)
    df["pumps_attending"] = df.groupby("borough_name")["pumps_attending"].\
                                                transform(lambda grp: grp.fillna(np.mean(grp)))
    df["pumps_attending"] = df["pumps_attending"].astype(np.str)
    return df
london_train["pumps_attending"].isnull().sum()<jupyter_output><empty_output><jupyter_text>Create a Wrapper Function<jupyter_code>def clean_london(df, add_emergencies=True, make_month=True, make_hour=True, clean_property=True,
                clean_address=True, clean_boroughs=True, clean_arriving_time=True, clean_station_pumps=True, 
                 clean_pumps_attending=True, verbose=False):
    
    if verbose: print("Cleaning London Data Started...\n")
        
    if add_emergencies:
        make_emergencies(df)
        if verbose: print("Sucessfully Added Emergency Column!")
            
    if make_month:
        create_month(df)
        if verbose: print("Sucessfully Added Month Column!")
            
    if make_hour:
        create_hour(df)
        if verbose: print("Sucessfully Added Hour Column!")
            
    if clean_property:
        merge_property(df)
        if verbose: print("Sucessfully Cleanded Property Category!")
            
    if clean_address:
        merge_address(df)
        if verbose: print("Sucessfully Cleaned Address!")
            
    if clean_boroughs:
        drop_boroughs(df)
        if verbose: print("Sucessfully Cleaned Borough Names!")
            
    if clean_arriving_time:
        df = arriving_time(df)
        if verbose: print("Sucessfully Cleaned Station Arriving Time!")
            
    if clean_station_pumps:
        df = station_pumps(df)
        if verbose: print("Sucessfully Cleaned Number of Stations with Pumps!")
            
    if clean_pumps_attending:
        df = pumps_attending(df)
        if verbose: print("Sucessfully Cleaned Number of Pumps Attending!")
            
    df = df.drop(["incident_number", "incident_group", "timestamp_of_call"], axis = 1, inplace = False)
    if verbose: print("Sucessfully Dropped Unecessary Columns!")
    
    if verbose: print("\nSucessfully Cleaned London Data!")
        
    return df<jupyter_output><empty_output><jupyter_text>Clean the Training Data<jupyter_code>%%time
query = """
            SELECT incident_number, timestamp_of_call, incident_group, property_category, 
                    address_qualifier, borough_name, first_pump_arriving_attendance_time as first_time,
                    num_stations_with_pumps_attending as station_pumps, num_pumps_attending as pumps_attending,
                    FROM `gdac-327115.LondonFire.fire_brigade`
        """

london = gbq.read_gbq(query, project_id = "gdac-327115", location="eu")
london_train, london_test = train_test_split(london, test_size = .16, stratify = london["incident_group"], random_state=42)
london_train.shape
warnings.filterwarnings('ignore')
london_train = clean_london(df = london_train, verbose = True)
london_train.shape
london_train.isnull().sum()<jupyter_output><empty_output><jupyter_text>Clean the Test Data Before we move on to data visualizations, let's make sure we can clean the test data. We should expect it to work since both the training and test data come from the same source.<jupyter_code>#Check the number of missing values
london_test.isnull().sum()
london_test.shape
london_test = clean_london(df = london_test, verbose = True)
london_test.shape
london_test.isnull().sum()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10943744236089763, 'fraction_numerical': 0.0036889025514909315, 'mean_word_length': 4.6331890331890335, 'pattern_counts': {'":': 4, '<': 131, '<?xml version=': 0, '>': 127, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '31', 'dir': 'jupyter-structured-clean-dedup', 'id': '104398', 'license': 'MIT', 'n_tokens_mistral': 6119, 'n_tokens_neox': 5918, 'n_words': 1493, 'path': 'Data Cleaning.ipynb', 'repo_name': 'AustinYunker/London-Fire-Brigade', 'size': '19518'}