<jupyter_start><jupyter_text>Sanitation Data This notebook was loaded with:```bashPYSPARK_DRIVER_PYTHON=jupyter PYSPARK_DRIVER_PYTHON_OPTS=notebook ./dse/bin/dse pyspark --num-executors 5 --driver-memory 6g --executor-memory 6g```The general plan is to do some exploration and cleaning in jupyter notebooks, then run our actual models by submitting python scripts and letting the jobs run as we'd expect.We'll clean the data and load them into cassandra tables to be used by the jobs.<jupyter_code>%pylab inline 
from pyspark.sql import Row
from pyspark.sql.types import *
from pyspark.sql.functions import isnull, count, datediff, lag, sum, coalesce, rank, lit, when,col, udf, to_date, year, mean, month, date_format, array
from pyspark.sql.types import StructType, StructField, IntegerType, StringType, FloatType, DateType
from pyspark.ml.feature import StringIndexer
from datetime import datetime
from pyspark.sql.window import Window
import pyspark
import matplotlib
import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>We're going to work with this in pandas, too.<jupyter_code>import pandas as pd
df_sanitation = pd.read_csv('data/311_Service_Requests_-_Sanitation_Code_Complaints.csv')
# Transform column headers to lower case
df_sanitation.columns = map(str.lower, df_sanitation.columns)

# Replace whitespace in headers
df_sanitation.columns = [x.strip().replace(' ', '_') for x in df_sanitation.columns]
df_sanitation = df_sanitation.drop('location', 1)
# Exclude NA's
df_sanitation = df_sanitation[~pd.isnull(df_sanitation['latitude'])]
# Exclude NA's
df_sanitation = df_sanitation[~pd.isnull(df_sanitation['longitude'])]
print "Shape of the data: ", df_sanitation.shape
print "Available data:\n", df_sanitation.columns

# View first few rows
df_sanitation.head()
#--------  Bar Plot
# A function to create a barplot
# Input: 
#      df (panda data frame)
#      var_name (name of the variable)
#      alpha (alpha value [0, 1])
#      title (title of the function)
#      color (color of the barplot)
#      edgecolor (cor of the outer barplotlines)
#      figsize (size of the figure)

def bar_plot(my_df, var_name, color='darkblue', alpha=0.6, title='', edgecolor='white', figsize=(18, 6)):
    
    ## Analyse crime types
    my_df['count'] = 1
    group = my_df.groupby(var_name)
    group = group.sum()
    group = group.sort_values('count', ascending=False)
    group = group['count']

    # Plot
    plt = group.plot(kind='bar', figsize=figsize, color=color, alpha=alpha, edgecolor=edgecolor, title=title)
    plt.grid()
#--------  Scatter Plot
# A function that visualizes the data
# Input: 
#      x (variable on the x-axis)
#      y (variable on the y_axis)
#      df (panda data frame)
#      group (grouping variable, i.e., variable to plot)
#      col (vector of colors)
#      alpha (alpha value for colors)
#      size (size of the dots)
#      title (title of the plot)
#      marker (shape of the marker, default to 'o')
#      add_line (add a line plot)
#      linewidth (thinkness of the added line)
#      figsize (size of the figure)

def scatter_plot(x, y, my_df, group, col, alpha, size, title, marker='o', add_line=False, linewidth=1, figsize=(15, 10)):
    
    # Define plot
    fig = plt.figure(figsize=figsize)
    ax = plt.axes()

    # Extract unique categories
    cat = my_df[group].unique()

    # Loop trough categories
    for i in range(0, len(cat)) :
            ax.scatter(my_df[x][my_df[group] == cat[i]].values,
                       my_df[y][my_df[group] == cat[i]].values,
                       c=col[i], alpha=alpha, edgecolors="None", s=size,
                       label=cat[i],
                       marker=marker)
    # Add line plot
    if add_line:
            for i in range(0, len(cat)) :
                ax.plot(my_df[x][my_df[group] == cat[i]].values,
                           my_df[y][my_df[group] == cat[i]].values,
                           c=col[i], alpha=alpha, linewidth=linewidth)

    # Add legend, grid etc.
    ax.legend(loc=0, scatterpoints = 1) # Legend with just one dot
    ax.set_xlabel(x); ax.set_ylabel(y)
    ax.set_title(title)
    plt.grid()
bar_plot(my_df=df_sanitation, var_name='police_district', color='purple', alpha=0.6,
         title='Plot VII: Number of sanitation code complaints according to police district', edgecolor='white', figsize=(18, 6))<jupyter_output><empty_output><jupyter_text>Santitation complaints look similar to the crime stats.<jupyter_code># Plot the data
col = ["darkred", "darkblue"]
scatter_plot(x='longitude', y='latitude', my_df=df_sanitation, group='type_of_service_request',
             col=col, alpha=0.05, size=20, marker='o',
             title="Plot VIII: Plot of the sanitation code violations in Chicago")
bar_plot(my_df=df_sanitation, var_name='what_is_the_nature_of_this_code_violation?', color='black', alpha=0.6,
         title='Plot IX: Nature of the code violation', edgecolor='white', figsize=(18, 6))<jupyter_output><empty_output><jupyter_text>These are interesting to include in the model. Would certain types of santitation complaints be more influential in the predictive power of the food inspections model?<jupyter_code>df_sanitation = df_sanitation.drop("count", 1)
df_sanitation = df_sanitation.drop("x_coordinate", 1)
df_sanitation = df_sanitation.drop("y_coordinate", 1)

df_sanitation.creation_date = df_sanitation.creation_date.astype(str)
df_sanitation.status = df_sanitation.status.astype(str)
df_sanitation.completion_date = df_sanitation.completion_date.astype(str)
df_sanitation.service_request_number = df_sanitation.service_request_number.astype(str)
df_sanitation['what_is_the_nature_of_this_code_violation?'] = df_sanitation['what_is_the_nature_of_this_code_violation?'].astype(str)
df_sanitation.street_address = df_sanitation.street_address.astype(str)

df_sanitation.dtypes<jupyter_output><empty_output><jupyter_text>another interesting piece of data is the avg number of days between creation and completion of service request<jupyter_code>df = sqlContext.createDataFrame(df_sanitation)
df.dtypes<jupyter_output><empty_output><jupyter_text>```cqlCREATE  KEYSPACE chicago_data    WITH REPLICATION = {'class' : 'SimpleStrategy', 'replication_factor' : 1};``````cqlCREATE TABLE chicago_data.sanitation (    creation_date text,    status text,    completion_date text,    service_request_number text,    type_of_service_request text,    "what_is_the_nature_of_this_code_violation?" text,    street_address text,    zip_code text,    ward text,    police_district double,    community_area double,    latitude double,    longitude double,    PRIMARY KEY (service_request_number));```<jupyter_code>df.write\
    .format("org.apache.spark.sql.cassandra")\
    .mode('append')\
    .options(table="sanitation", keyspace="chicago_data")\
    .save()
df.count()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11224341243266851, 'fraction_numerical': 0.006260008734895909, 'mean_word_length': 4.354637568199532, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '66150', 'license': 'CNRI-Python', 'n_tokens_mistral': 2293, 'n_tokens_neox': 2210, 'n_words': 572, 'path': 'Step4-SanitationData.ipynb', 'repo_name': 'angelok1/chicago-inspections-dse', 'size': '6869'}