from sqlalchemy import create_engine
import pandas as pd

sf_conn = create_engine('sqlite:///sf_data.sqlite')

sql_query = """
select Applicant, count(distinct locationid) from mobile_food_permits
JOIN mobile_food_schedule ON mobile_food_schedule.permit = mobile_food_permits.permit
group by Applicant
order by count(distinct locationid) desc
"""

pd.read_sql_query(sql_query, sf_conn).head()

import geopandas as gpd
#from mpl_toolkits.basemap import Basemap


shp_fn = 'cb_2015_us_zcta510_500k.shp'  #shapefile
cities = gpd.read_file(shp_fn)

zips = cities[cities.ZCTA5CE10.str.startswith("9")]
zips = zips[pd.to_numeric(zips.ZCTA5CE10) <= 96199]

# get the prices from the different regions in the zillow db
region_median_price = pd.read_sql_query("select RegionName, avg(zillow.MedianSoldPricePerSqft_AllHomes) median_sqft from zillow group by RegionName", sf_conn)
region_median_price.tail()

new_zips = zips.reset_index()

# join the shapefile columns with the zillow df to associate price with the map region
new_df = pd.merge(new_zips, region_median_price, how = 'right', left_on = pd.to_numeric(zips.ZCTA5CE10), right_on = 'RegionName')
new_df.head()

%matplotlib inline
import matplotlib.pyplot as plt

fig, ax = plt.subplots(1, figsize=(4,6))
plt.title("Median Price per Sq. Ft in San Francisco")
plt.ylabel("Latitude")
fig.autofmt_xdate()

new_df.plot(ax = ax, column='median_sqft', scheme='QUANTILES', legend = True)

# get the districts sorted by the number of crime incidences over all time
pd.read_sql_query("select crime.PdDistrict, count(crime.IncidntNum) from crime where crime.Category != 'NON-CRIMINAL' group by crime.PdDistrict order by count(crime.IncidntNum) desc", sf_conn)

# looking at recent years, 2015 onwards
query = """
select crime.PdDistrict, count(crime.IncidntNum) as '# of Crime Incidences 2015-2016' from crime 
where crime.Category != 'NON-CRIMINAL' and crime.Datetime > '2015-01-01'
group by crime.PdDistrict
order by count(crime.IncidntNum) desc
"""
pd.read_sql_query(query, sf_conn)

# see what types of crimes are most common in the top-crime regions
crime_type_counts = pd.read_sql_query("""select PdDistrict, Category, count(Category) from crime 
where crime.PdDistrict in ('SOUTHERN', 'NORTHERN', 'MISSION') and Datetime > '2015-01-01'
group by PdDistrict, crime.Category
order by count(crime.Category) desc""", sf_conn)
crime_type_counts.head(3)

import seaborn as sns

g = sns.factorplot(x="Category", y="count(Category)", hue="PdDistrict", data=crime_type_counts.head(21),
                   size=6, kind="bar", palette="muted")

plt.xticks(rotation=30)
plt.xlabel("Crime Category")
plt.title("Crime Distribution Across Southern, Northern, Mission 2015-2016")

g.despine(left=True)
g.set_ylabels("Count")

# don't look at non-criminal
datetimes = pd.read_sql_query("select crime.Datetime from crime where crime.Category != 'NON-CRIMINAL'", sf_conn)
#datetimes.head()
datetimes = list(datetimes["Datetime"])

# extract out the times crimes usually occur at

from datetime import datetime

times = [i[11:] for i in datetimes] # strip off the dates and only get the times
times = [datetime.strptime(entry, '%H:%M:%S') for entry in times] # convert to time type

plt.xticks(rotation=15)
plt.hist(times, bins = 24)
plt.xlabel("Time")
plt.ylabel("Frequency")
plt.title("Time Distribution of Crimes in San Francisco, by Hour")
plt.show()

# initial look at entries that listed "mobile_food_facility" as the type of noise
food_noise = pd.read_sql_query("select Datetime, Lat, Lon, Neighborhood from noise where Type == 'mobile_food_facility'", sf_conn)
food_noise # there's only 7 entries out of thousands!

# generate points for all the food truck locations
truck_locs = pd.read_sql_query("select Latitude, Longitude from mobile_food_locations where Latitude > 30", sf_conn)

import shapely.geometry as geom

lonlat = [geom.Point(lon, lat) for lon, lat in zip(truck_locs.Longitude, truck_locs.Latitude)]
noise_points = truck_locs.drop(["Latitude", "Longitude"], axis = 1)

noise_points = gpd.GeoDataFrame(noise_points, geometry = lonlat)

# generate zip codes based off longitude and latitude
import geocoder

noise = pd.read_sql_query("select Lon, Lat, Neighborhood, Type from noise where Type in ('mobile_food_facility', 'other_excessive_noise') and Lon is not NULL", sf_conn)

noise["zip"] = [(geocoder.google([lat, lon], method='reverse')).postal for lon,lat in zip(noise.Lon, noise.Lat)]

# generate a count for how many noise complaints are in each zip area
noise_counts = pd.DataFrame(pd.Series(noise["zip"]).value_counts()).reset_index()
noise_counts.head()
# because of value_counts, the column names are off

sf_zips = noise_counts['index'] # for later use. stores (almost) all zips in the SF region
sf_zips = pd.to_numeric(sf_zips)

# merge shapefile df with noise complaints df
noise_locs = pd.merge(new_zips, noise_counts, how = 'right', left_on = pd.to_numeric(zips.ZCTA5CE10), right_on = pd.to_numeric(noise_counts['index']))

# plot num of noise complaints with food truck points
base = noise_locs.plot(column='zip', scheme='QUANTILES', legend = True, cmap = 'GnBu')
plt.title("Number of Noise Complaints and Food Truck Locations")
plt.ylabel("Latitude")
plt.xlabel("Longitude")
fig.autofmt_xdate()

noise_points.plot(ax = base, color = "orangered", markersize = 4, alpha = 0.5)

query = """select StartHour, EndHour, Latitude as Lat, Longitude as Lon from mobile_food_schedule
JOIN mobile_food_locations ON mobile_food_locations.locationid = mobile_food_schedule.locationid
where latitude > 30
group by mobile_food_schedule.locationid"""

truck_time_loc = pd.read_sql_query(query, sf_conn)

lonlat = [geom.Point(lon, lat) for lon, lat in zip(truck_time_loc.Lon, truck_time_loc.Lat)]
truck_points = truck_time_loc.drop(["Lat", "Lon"], axis = 1)

truck_points = gpd.GeoDataFrame(truck_points, geometry = lonlat)

import numpy as np

def hour_range(start, end):
    '''
    Return a numpy array with the range of hours a specific food truck is open.
    '''
    if start < end:
        return np.arange(start, end + 1)
    
    # when a food truck is open past midnight
    else:
        end = end + 24
        arr = np.arange(start, end + 1)
        arr = np.remainder(arr, 24)
        return arr

open_hours = [hour_range(StartHour, EndHour) for StartHour, EndHour in zip(truck_points.StartHour, truck_points.EndHour)]
open_hours[0:5]

all_hours = np.concatenate(open_hours)
plt.hist(all_hours)
plt.title("Open Hours for all Food Trucks in SF")
plt.ylabel("Count")
plt.xlabel("Hour of the Day")

# plot locations of all food trucks
SF_shapes = new_zips.loc[pd.to_numeric(new_zips['ZCTA5CE10']).isin(sf_zips)]
base = SF_shapes.plot(color = 'white')
truck_points.plot(ax = base, markersize = 5, alpha = 0.5, color = 'coral')
plt.title("Food Truck Locations in SF")
plt.ylabel("Latitude")
plt.xlabel("Longitude")

private_school_loc = pd.read_sql_query("select Lat, Lon from schools where Category == 'Independent / Private'", sf_conn)

lonlat = [geom.Point(lon, lat) for lon, lat in zip(private_school_loc.Lon, private_school_loc.Lat)]
private_school_loc = gpd.GeoDataFrame(private_school_loc, geometry = lonlat, crs = {'init' :'epsg:4326'})

new_df = new_df.loc[pd.to_numeric(new_df['ZCTA5CE10']).isin(sf_zips)]

base = new_df.plot(column='median_sqft', scheme='QUANTILES', legend = True, cmap = 'YlGnBu')

private_school_loc = private_school_loc.to_crs(new_df.crs)
private_school_loc.plot(ax = base, markersize = 5, alpha = 0.5, color = 'coral')

plt.title("Home Prices and Private Schools Across SF")
plt.ylabel("Latitude")
plt.xlabel("Longitude")

query = "select Lat, Lon from crime where Datetime > '2015-01-01' and Category != 'NON-CRIMINAL'"
crime_locs = pd.read_sql_query(query, sf_conn)
lonlat = [geom.Point(lon, lat) for lon, lat in zip(crime_locs.Lon, crime_locs.Lat)]
crime_locs = gpd.GeoDataFrame(crime_locs, geometry = lonlat)

# looking at recent years, 2015 onwards
query = """
select crime.PdDistrict, count(crime.IncidntNum) as '# of Crime Incidences 2015-2016' from crime 
where crime.Category != 'NON-CRIMINAL' and crime.Datetime > '2015-01-01'
group by crime.PdDistrict
order by count(crime.IncidntNum) desc
"""
pd.read_sql_query(query, sf_conn)

base = new_df.plot(column='median_sqft', scheme='QUANTILES', legend = True, cmap = 'YlGnBu')

#crime_locs.plot(ax = base, markersize = 5, alpha = 0.5, color = 'coral')

plt.title("Home Prices Across SF")
plt.ylabel("Latitude")
plt.xlabel("Longitude")