#!/usr/bin/env python
# coding: utf-8

# <p><img style="padding: 0 15px; float: left;" src="Images/FTLogo300.jpg" alt="FT Crusader Logo" Width='140' Height= '250'/></p> 
# <p> <h2> Frogtown, MN, Crime Map 06/17/20</h2> <a name="tc"></a>
# <p> <h3> By Frogtown Crusader (Abu Nayeem)</h3>

# In[ ]:


## Table of contents <a name="content"></a>
* [Purpose](#purpose)
* [Data](#data)
* [Audience Participation](#audience)
* [Overview](#overview)
    * [Plot: Minneapolis Rental Building Age by Building Size](#age)
    * [Map: Minneapolis Tier 2 & 3 geo-coordinates](#tier)
* [Landlord/ Housing Provider Breakdown](#owner)
    * [List: Dominant Providers](#big)
    * [Map: Properties by Single Owner](#prop)
* [Plot: New Rental Housing Construction by Community](#new)
    * [Map: New Rental Housing Construction](#newmap)
* [Interactive Community Data](#comm)
    * [Map: Powderhorn](#powder)
    * [Map: Northeast](#Northeast)
    * [Map: Near North](#Near North)
    * [Map: Camden](#Camden)
    * [Map: Calhoun Isle](#Calhoun Isle)
    * [Map: University](#University)
    * [Map: Southwest](#Southwest)
    * [Map: Central](#Central)
    * [Map: Nokomis](#Nokomis)
    * [Map: Longfellow](#Longfellow)
    * [Map: Phillips](#Phillips)
* [Audience Participation Reminder](#reminder)
* [Functions_Run](#runall)


# ## Table of contents <a name="content"></a>
# * [Purpose](#purpose)
# * [Data](#data)
# * [Frogtown All Crime](#all_crime)
#     * [Graph: Frogtown Longitudinal Crime Uptodate](#fgd_crime)
#     * [Table: Frogtown Longitudinal Crime Uptodate by Month](#fgd_month)
#     * [Graph: Saint Paul Longitudinal Crime Uptodate](#sp_crime)
#     * [Map: Frogtown 2018 Crime Map](#2018map)
#     * [Map: Frogtown 2019 to Present Crime Map w/ Proactive visits](#2019map)
#     * [Map: Frogtown Violent Crime 2017 to Present Crime Map](#violent)
#     * [Map: Frogtown Multicrime Map](#multi)
# * [Frogtown Hotspots w/ map](#hotspot)
# * [Frogtown Shooting Report/ include Covid Breakdown](#discharge)
#     * [Map: Frogtown Longitudinal Discharge Map Uptodate](#fgd_dis)
#     * [Map: Frogtown Longitudinal Discharge Map on Daytime Uptodate](#fgd_day)
#     * [Map: Frogtown Longitudinal Annual Discharge Map](#fgd_ann)
#     * [Graph: Frogtown Longitudinal Firearms Uptodate](#firearm) 
# * [Saint Paul Shooting Report](#sp_discharge)    
# * [Concluding Remarks](#conclude)
# * [Functions](#func)
# * [Functions_Run](#runall)
# 

# ### Purpose <a name="purpose"></a>
# 
# As a Frogtown resident, it is important that residents are aware on what is happening around our community and how we address it. Every year, there is commotion about safety. Some community members feel that the community is getting worse, while others are saying its getting better. While the city and police department might be saying something totally different. In our current political climate, it's controversial to talk about crime given it's strong correlation with poverty. Unfortunately, this can disenfranchise some community members whom are victims of crime and live in heavy crime areas on a daily basis. These reports and graphs are met to just shed light on the issue and encourage community members to see and interact with the data themselves. Hopefully, the community and/or agencies will take action. **Note:** Parts of Midway, Rondo, and Union City are included.  
# 
# Some questions that you may consider:
# * What are vulnerable areas or hotspots in the community? 
# * Are certain crimes more frequent in your area, and how should you or the community address it?
# * What are some trends in the your community?
# * What is the frequency of calling the police in nearby area? Is there over-reporting/ under-reporting?
# * How can we address these issues as a community (i.e. organizing; etc)?

# ### Open Source Data Initiative
# 
# It is important that data is accessible and provided to the public. This report and others will be available on Github allowing others to contribute, replicate, and use code for their own respective neighborhood. If anyone is interested in mapping out East Side, Payne Phalen, etc., please reach out to me. 
# 
# You can use the data provided by this report, but understand that I'm not an official agency and not liable for incorrect data.

# ### About the Dataset: <a name="data"></a>
# 
# The [Crime Incident Report - Dataset](https://information.stpaul.gov/Public-Safety/Crime-Incident-Report-Dataset/gppb-g9cg) was obtained from the Saint Paul Website. It is publicly available. The report contains incidents from Aug 14 2014 through the most recent date, as released by the Saint Paul Police Department.
# 
# A few notes about the dataset:
# * The dataset have several human errors such as mis-categorizing addresses and address being designated to the wrong police grid.
# * The dataset **DOES NOT PROVIDE EXACT ADDRESSES/ GEO-COORDINATES**. However, I've constructed an algorithm that get a reliable proxy address for most entries. 
#     * The process included entering coordinate manually; if any residents are interested in mapping out their own community please feel free to connect with me.
#     * Google Maps geocoder was used
#     * The algorithm **does not cover** the intersections of Capitol Heights and Mt. Airy region. Some data was excluded from region due to inadequate mapping. They have many intersections and curved streets; it would take time to geo-code it.
# * The analysis/algorithm is restricted near the Frogtown area.
# 
# 

# ### Saint Paul Police grid w/ total crime numbers all years [excluding proactive visits] <a name="all_crime"></a>
# 
# The dataset comprise this area.
# 
# ![title](Images/gridnum1.jpg)

# #### Display Total Incidents of all years
# 
# **NOTE:** The following changes were made in the crime category to consolidate categories
# * Graffiti was combined into Vandalism
# * Violent Crimes combined the categories: Rape, Homicide, Aggressive Assault
# * Domestic Assault includes both Simple and Aggressive Assaults 
# * Community Engagement Events and Proactive Police Visit are not crimes. Proactive Police Visit is not response to 911 call. They will be excluded for vast majority of the analysis

# In[3]:


print('List of Events in Frogtown (nearby) from 2019 to Present')
fg.query('Year>=2019')['Incident'].value_counts()


# ### Frogtown Yearly Crime Comparison Up to Current Date <a name="fgd_crime"></a>
# 
# How much crime is there so far in comparison to the previous year on this date? Are certain areas increasing or decreasing? **Note:** the function can choose any day prior to the current date.
# 
# We choose the max date for the dataset. We can choose an earlier date if desired

# In[6]:


plot_toDate_Year_Crime(Incident='All',Day=Max)


# ### Frogtown Total Crimes toDate by Month <a name="fgd_month"></a>
# 
# What are some monthly trends

# In[5]:


table_toDate_Month_Crime(Incident='All',Day=Max)


# ### Saint Paul Yearly Crime Comparison Up to Current Date <a name="sp_crime"></a>
# 
# How do we compare to our neighbors and what are some trends?

# In[7]:


plot_toDate_Days_SPCrime_Norm(Incident='Discharge',Day=Max)


# ## Frogtown Annual Crime Map (Default 2020) <a name="2018map"></a>
# At least four crimes must occur in one location to be displayed
# 
# **NOTE: It's interactive, click on the circles for more details on the total number and type of crimes committed**

# In[6]:


plot_Frogtown_year(2020)


# ## Frogtown Violent Crimes Map from 2018 to Present  <a name="violent"></a>
# 
# Violent crimes are less salient or apparent to community members. We can see which areas/ blocks are safer.  
# 
# * Orange= 2018
# * Green= 2019
# * Purple= 2020

# In[8]:


plot_Frogtown_long_crime('Violent')


# ### Frogtown 2020 Multi-Crime Map  <a name="multi"></a>
# 
# * Purple= Discharge
# * Green= Autotheft
# * Orange= Theft
# * Blue= Burglary
# * Brown= Narcotics
# * Red= Vandalism

# In[9]:


plot_multicrime_byYear(2020)


# ### Hotspots near Frogtown from 2019 to present <a name="hotspot"></a>
# 
# Notice the address codes are masked and count is limited to 15

# In[48]:


B=fgc.query('Year in [2019,2020]')
B=B[['Block','Count']].groupby(['Block']).sum().reset_index()
B.columns=['Block_Intersection','Count']
B.query('Count>14').sort_values(['Count'], ascending=False)


# ### Frogtown Hotspot Interactive map from 2019 to present <a name="hotspot"></a>
# 
# * The bubbles are **not to scale** across the different categories to make this map more accessible. You  may need to zoom in to distinguish points
# * The graph is **Interactive**; if you click on a point, it will list the number and  type of incidents in that area
# * Legend
#     * Orange: Number of Incidents greater than 10 and less than 20
#     * Green: Number of Incidents greater than 20 and less than 50
#     * Purple: Number of Incident greater than 50 
# 

# In[49]:


plot_Frogtown_hot_spot()


# ## Frogtown Gun Discharge Reports<a name="discharge"></a>
# 
# 
# We hear it **all** the time from many people that our awareness of shootings is based on connection to social media and officials suggesting it is safer now than years prior. Is this true? Well, let's find out. It's worth noting that weather conditions can influence the frequency of when shootings occur. Finally, the discharge category excludes a firearm being used to assist in another crime.  
# 
# **Why focus on shootings?**
# 
# Shootings can harm innocent bystanders and creates a significant sense of fear because it can be heard from a distance. It is one of the more salient crimes. 
# 
# **How is a discharge report determined?** 
# 
# If a police report was made, that means there were 2 or more witnesses and/or evidence was found (spent shell casings, bullet holes in cars, homes, etc).
# 
# First let's construct a table showing the total number of shootings up to current date in comparison to previous years. Notice that many grids are experiencing different trends, so it can be true that some neighbors are exposed to more shootings this year and others less.
# 

# In[40]:


plot_toDate_Year_Crime(Incident='Discharge',Day=Max)


# As a results of the the Covid outbreak and civil unrest, I have broken the data into three stages: preCovid, Covid only, and post_GeorgeFloyd murder 

# In[33]:


#PreCovid
plot_toDate_Days_FGCrime(Incident='Discharge',Day=CV, CDate=CV)


# In[34]:


#Covid Only
plot_toDate_Days_FGCrime(Incident='Discharge',Day=GE-CV, CDate=GE)


# In[15]:


#post-George Floyd
plot_toDate_Days_FGCrime(Incident='Discharge',Day=Max-GE, CDate=Max)


# ## Shootings within last 120 days

# In[17]:


plot_Frogtown_long_crime_todate_2020(Incident='Discharge',Day=60, CDate=Max)


# The table below displays the number of shootings by grid in Frogtown broken by month over the last two years

# In[196]:


table_toDate_Month_Crime(Incident='Discharge',Day=Max)


# ### Frogtown UptoDate gun discharge map  2020 to Present (interactive) <a name="fgd_dis"></a>
# 
# A police Grid can cover a lot of area. From the map we can spot parts of the community that are currently vulnerable and those in the past.
# 
# * Orange= 2018
# * Green= 2019
# * Purple= 2020

# In[50]:


plot_Frogtown_long_crime_todate(Incident='Discharge',Day=Max)


# ### Frogtown UptoDate Daytime Discharge Map <a name="fgd_day"></a>
# 
# Daytime shooting are particularly concerning to our community as there as there are greater frequency of innocent bystanders being harmed and can influence community members behavior to go outside or parents decision to let their kids play. 
# 
# * Orange= 2018
# * Green= 2019
# * Purple= 2020

# In[42]:


plot_Frogtown_long_crime_daytime_todate(Incident='Discharge',Day=Max)


# ### Frogtown yearly gun discharge from 2018 to Present  <a name="fgd_ann"></a>
# 
# * Orange= 2018
# * Green= 2019
# * Purple= 2020

# In[20]:


plot_Frogtown_long_crime(Incident='Discharge')


# ### Firearm Usage (Any crime involving it) <a name="firearm"></a>
# 
# Often firearms are used when committing another crime, but does not need to be fired. 

# In[114]:


print('The total crimes involving firearms in Frogtown Area')
fgc_fire['Incident'].value_counts()


# In[40]:


plot_toDate_Year_Firearm(Day=Max)


# In[21]:


plot_toDate_Year_SPFirearm()


# ## Saint Paul Discharge Incidents  <a name="sp_discharge"></a>
# 
# Similar to the Frogtown analysis, we will construct a normalized trend graph, and break the data to the Covid and civil unrest events
# 
# What are some trends at the city-wide level? (normalized per 10000)

# In[22]:


plot_toDate_Days_SPCrime_Norm(Incident='Discharge')


# In[237]:


#PreCovid
plot_toDate_Days_SPCrime(Incident='Discharge',Day=CV, CDate=CV)


# In[233]:


#Covid
plot_toDate_Days_SPCrime(Incident='Discharge',Day=GE-CV, CDate=GE)


# In[234]:


#Post: GeorgeFloyd
plot_toDate_Days_SPCrime(Incident='Discharge',Day=Max-GE, CDate=Max)


# In[ ]:





# ## Concluding Remarks <a name="conclude"></a>
# 
# From the data above, we can visually spot sections in the community and specific locations where crime is more frequent. It is important that we have a community discussion before taking any further action. If you have any question in regards to visualization and other things, please feel free to reach out to me.

# In[ ]:





# In[ ]:





# ## Functions <a name="func"></a>

# #### Plot Multiple Crimes by Year

# In[24]:


def plot_multicrime_byYear(Year=2019):
    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    B= fgc[(fgc['Year'] == Year)]
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge']

    T=B.query('Theft==1')
    T=T[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()

    V=B.query('Vandalism==1')
    V=V[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()

    N=B.query('Narcotics==1')
    N=N[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()

    A=B.query('Incident=="Auto Theft"')
    A=A[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()

    D=B.query('Discharge==1')
    D=D[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()

    Br=B.query('Burglary==1')
    Br=Br[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()

#Create Frogtown GeoMap
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")

    for index, row in T.iterrows(): 
        popup_text = "Address: {}<br> total theft incidents: {}"
        popup_text = popup_text.format(row["Block"],row['Count']) 
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count'] +3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)

    for index, row in V.iterrows(): 
        popup_text = "Address: {}<br> total vandalism incidents: {}"
        popup_text = popup_text.format(row["Block"],row['Count']) 
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count'] +3,
                            color="#FF0000",
                            popup=popup_text,
                            fill=True).add_to(FG_map)    

    for index, row in N.iterrows(): 
        popup_text = "Address: {}<br> total narcotics incidents: {}"
        popup_text = popup_text.format(row["Block"],row['Narcotics']) 
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Narcotics'] + 3,
                            color="#654321",
                            popup=popup_text,
                            fill=True).add_to(FG_map)     

    for index, row in A.iterrows(): 
        popup_text = "Address: {}<br> total autotheft incidents: {}"
        popup_text = popup_text.format(row["Block"],row['Count']) 
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count'] +3,
                            color='#007849',
                            popup=popup_text,
                            fill=True).add_to(FG_map)
        
    for index, row in D.iterrows(): 
        popup_text = "Address: {}<br> total discharge incidents: {}"
        popup_text = popup_text.format(row["Block"],row['Count']) 
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count'] +3,
                            color="#800080",
                            popup=popup_text,
                            fill=True).add_to(FG_map)    

    for index, row in Br.iterrows(): 
        popup_text = "Address: {}<br> total burglary incidents: {}"
        popup_text = popup_text.format(row["Block"],row['Count']) 
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count'] +3,
                            color="#0000ff",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
    
    return FG_map


# #### Plot Crime Map by Year

# In[25]:


def plot_Frogtown_year(Year=2018):
    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    B= fgc[(fgc['Year'] == Year)]
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge'\
            ,'Robbery','Domestic Assault','Violent','Arson']
    B=B[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()
    B=B.query('Count>3')
    
    # for each row in the data, add a cicle marker
    for index, row in B.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}<br> Theft: {}<br> Vandalism: {}\
                            <br> Narcotics: {}<br> Auto Theft: {}<br> Burglary: {}<br> Discharge: {}<br> Robbery: {}\
                            <br> Domestic Assault: {}<br> Violent: {}<br> Arson: {}"
        popup_text = popup_text.format(str(Year),row["Block"], row['Count'], row['Theft'], row['Vandalism'], row['Robbery'],\
                            row['Auto Theft'], row['Burglary'], row['Discharge'], row['Domestic Assault'],\
                                  row['Domestic Assault'],row['Violent'],row['Arson'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
        
    return FG_map


# In[72]:


#interact(plot_Frogtown_year, Year=widgets.IntSlider(min=2014,max=2019,step=1,value=2018));


# #### Plot Crime Map by Year w/ proactive visits

# In[26]:


def plot_Frogtown_year_proactive(Year=2018):
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    B= fgc[(fgc['Year'] == Year)]
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge'\
            ,'Robbery','Domestic Assault','Violent','Arson']
    B=B[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()
    
    A= fgp[(fgp['Year'] == Year)]
    A= A.groupby(['Block','Latitude','Longitude']).size().reset_index()
    A.columns=['Block','Latitude','Longitude','Count'] 
    
    for index, row in B.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}<br> Theft: {}<br> Vandalism: {}\
                            <br> Narcotics: {}<br> Auto Theft: {}<br> Burglary: {}<br> Discharge: {}<br> Robbery: {}\
                            <br> Domestic Assault: {}<br> Violent: {}<br> Arson: {}"
        popup_text = popup_text.format(str(Year),row["Block"], row['Count'], row['Theft'], row['Vandalism'], row['Robbery'],\
                            row['Auto Theft'], row['Burglary'], row['Discharge'], row['Domestic Assault'],\
                                  row['Domestic Assault'],row['Violent'],row['Arson'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)

    for index, row in A.iterrows(): 
        popup_text = "Address: {}<br> total proactive calls: {}"
        popup_text = popup_text.format(row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#007849",
                            popup=popup_text,
                            fill=True).add_to(FG_map)  
        
    return FG_map


# #### Plot Longitudinal Crime Compared by Year

# In[27]:


def plot_Frogtown_long_crime(Incident='Discharge'):
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge'\
            ,'Robbery','Domestic Assault','Violent','Arson','Year']

    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    B= fgc[(fgc['Incident'] == Incident)]
    B17=B.query('Year == 2017')
    B17=B17[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()
    B18=B.query('Year == 2018')
    B18=B18[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()
    B19=B.query('Year == 2019')
    B19=B19[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()

    for index, row in B17.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
    
    for index, row in B18.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"], row["Longitude"]),
                            radius=row['Count']+3,
                            color="#007849",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
    
    for index, row in B19.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#800080",
                            popup=popup_text,
                            fill=True).add_to(FG_map)    
    
    return FG_map


# In[13]:


#interact(plot_Frogtown_long_crime, Incident=['Discharge','Violent','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Robbery','Domestic Assault','Arson'])


# #### Plot Longitudinal Crime Map Compared by Year; todate

# In[247]:


def plot_Frogtown_long_crime_todate(Incident='All',Day=Max):
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge'\
            ,'Robbery','Domestic Assault','Violent','Arson','Year']

    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    if Incident=='All':
        B= fgc
    else: 
        B= fgc[(fgc['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    B17=B.query('Year == 2017')
    B17=B17[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()
    B18=B.query('Year == 2018')
    B18=B18[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()
    B19=B.query('Year == 2019')
    B19=B19[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()
    print('This map displays {} incidents up to {}20XX'.format(Incident,Date))
    for index, row in B17.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
    
    for index, row in B18.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"], row["Longitude"]),
                            radius=row['Count']+3,
                            color="#007849",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
    
    for index, row in B19.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#800080",
                            popup=popup_text,
                            fill=True).add_to(FG_map)    
    
    return FG_map


# #### Plot Longitudinal Daytime Crime Map Compared by Year; todate

# In[1]:


def plot_Frogtown_long_crime_daytime_todate(Incident='All',Day=Max):
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge'\
            ,'Robbery','Domestic Assault','Violent','Arson','Year']

    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    if Incident=='All':
        B= fgc
    else: 
        B= fgc[(fgc['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    B= B.query('Hour >= 6 and Hour <20')
    B17=B.query('Year == 2017')
    B17=B17[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()
    B18=B.query('Year == 2018')
    B18=B18[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()
    B19=B.query('Year == 2019')
    B19=B19[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()
    print('This map displays {} incidents up to {}20XX from 7AM to 8PM'.format(Incident,Date))
    for index, row in B17.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
    
    for index, row in B18.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"], row["Longitude"]),
                            radius=row['Count']+3,
                            color="#007849",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
    
    for index, row in B19.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#800080",
                            popup=popup_text,
                            fill=True).add_to(FG_map)    
    
    return FG_map


# #### Crime Table toDate by Month 

# In[158]:


#convert MOnth
def Convert_Month(num):
    Months= ['January','February','March','April','May','June','July','August','September','October','November','December']
    for i in range(12):
        if num==(i+1):
            Xi=Months[i]
    return Xi 

def table_toDate_Month_Crime(Incident='All',Day=Max):
    if Incident=='All':
        B= fgc
    else: 
        B= fgc[(fgc['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    B=B.query('Year >= 2017')
    Index= ['Year','Grid','Count','Month']
    C= B[Index].groupby(['Year','Grid','Month']).sum().reset_index()
    C['Month_Name']= C.Month.apply(Convert_Month) 
    C['Community']= C.Grid.apply(commun)
    print('This table maps {} incidents up to Day {}20XX'.format(Incident,Date))
    return pd.pivot_table(C, values='Count', index=['Community','Grid'], columns=['Month', 'Year'], fill_value=0)



# #### Crime Graph  toDate by Year

# In[272]:


#Crime Plot
import matplotlib.pyplot as plt
import seaborn as sns


def plot_toDate_Year_Crime(Incident='All',Day=Max):
    if Incident=='All':
        B= fgc
    else: 
        B= fgc[(fgc['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B=B.query('Year >= 2017')
    B= B[(B['DayYear'] <= Day)]
    Index= ['Year','Grid','Count']
    C= B[Index].groupby(['Year','Grid']).sum().reset_index()
    C['Community']= C.Grid.apply(commun) 
    print('This graph maps {} incidents up to {}20XX'.format(Incident,Date))
    sns.set()
    pd.pivot_table(C, values='Count', index=['Community','Grid'], columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= Incident + ' Yearly Total Incidents UptoDate: '+ str(Date)+'20XX')
    return plt.show()

# Firearm Plot
def plot_toDate_Year_Firearm(Day=Max):
    B= fgc_fire[(fgc_fire['DayYear'] <= Day)]
    Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B.query('Year>2016')
    Index= ['Year','Grid','Count']
    C= B[Index].groupby(['Year','Grid']).sum().reset_index()
    C['Community']= C.Grid.apply(commun) 
    print('This graph maps Firearm incidents up to {}20XX'.format(Date))
    sns.set()
    pd.pivot_table(C, values='Count', index=['Community','Grid'], columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= 'Firearm Yearly Total Incidents UptoDate: ' + str(Date)+'20XX')
    return plt.show()


# #### Latenight Crime Plot by Year

# In[7]:


def plot_Frogtown_year_Latenight(Year=2018):
    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    B= fgc[(fgc['Year'] == Year)]
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge'\
            ,'Robbery','Domestic Assault','Violent','Arson']
    BM=B.query('LateNight ==0')
    BM=BM[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()
    BM=BM.query('Count>2')
    BL=B.query('LateNight ==1')
    BL=BL[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()
    BL=BL.query('Count>2')
    
    # for each row in the data, add a cicle marker
    for index, row in BM.iterrows(): 
        popup_text = "Year: {} Non-late<br> Address: {}<br> total incidents: {}<br> Theft: {}<br> Vandalism: {}\
                            <br> Narcotics: {}<br> Auto Theft: {}<br> Burglary: {}<br> Discharge: {}<br> Robbery: {}\
                            <br> Domestic Assault: {}<br> Violent: {}<br> Arson: {}"
        popup_text = popup_text.format(str(Year),row["Block"], row['Count'], row['Theft'], row['Vandalism'], row['Robbery'],\
                            row['Auto Theft'], row['Burglary'], row['Discharge'], row['Domestic Assault'],\
                                  row['Domestic Assault'],row['Violent'],row['Arson'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)

    for index, row in BL.iterrows(): 
        popup_text = "Year: {} Late<br> Address: {}<br> total incidents: {}<br> Theft: {}<br> Vandalism: {}\
                            <br> Narcotics: {}<br> Auto Theft: {}<br> Burglary: {}<br> Discharge: {}<br> Robbery: {}\
                            <br> Domestic Assault: {}<br> Violent: {}<br> Arson: {}"
        popup_text = popup_text.format(str(Year),row["Block"], row['Count'], row['Theft'], row['Vandalism'], row['Robbery'],\
                            row['Auto Theft'], row['Burglary'], row['Discharge'], row['Domestic Assault'],\
                                  row['Domestic Assault'],row['Violent'],row['Arson'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#007849",
                            popup=popup_text,
                            fill=True).add_to(FG_map)        
    return FG_map


# In[16]:


#plot_Frogtown_year_Latenight(2018)


# #### Frogtown  Plot by Year and month

# In[6]:


def plot_Frogtown_yearmonth(Year=2018,Month=1):
    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    B= fgc[(fgc['Year'] == Year)]
    B= B[(B['Month'] == Month)]
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge']
    B=B[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()
    

    # for each row in the data, add a cicle marker
    for index, row in B.iterrows(): 
        Other= row['Count'] - row['Theft'] - row['Vandalism'] - row['Narcotics'] -row['Auto Theft'] - row['Burglary'] - row['Discharge']
        popup_text = "Year: {} Month: {}<br> Address: {}<br> total incidents: {}<br> Theft: {}<br> Vandalism: {}\
                            <br> Narcotics: {}<br> Auto Theft: {}<br> Burglary: {}<br> Discharge: {}<br> Other: {}"
        popup_text = popup_text.format(str(Year), str(Month),row["Block"], row['Count'], row['Theft'], row['Vandalism'], row['Narcotics'],\
                            row['Auto Theft'], row['Burglary'], row['Discharge'], Other) 
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
        
    return FG_map


# In[10]:


#plot_Frogtown_yearmonth(2018,1)
interact(plot_Frogtown_yearmonth, Year=widgets.IntSlider(min=2014,max=2019,step=1,value=2018),\
         Month=[('January', 1), ('February', 2), ('March',3), ('April',4), ('May',5) ,('June',6),('July',7)]);


# #### Frogtown plot by year and hour

# In[5]:


def plot_Frogtown_yearhour(Year=2018,Hour=0):
    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    B= fgc[(fgc['Year'] == Year)]
    B= B[(B['Hour'] == Hour)]
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge']
    B=B[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()
    

    # for each row in the data, add a cicle marker
    for index, row in B.iterrows(): 
        Other= row['Count'] - row['Theft'] - row['Vandalism'] - row['Narcotics'] -row['Auto Theft'] - row['Burglary'] - row['Discharge']
        popup_text = "Year: {} Hour: {}<br> Address: {}<br> total incidents: {}<br> Theft: {}<br> Vandalism: {}\
                            <br> Narcotics: {}<br> Auto Theft: {}<br> Burglary: {}<br> Discharge: {}<br> Other: {}"
        popup_text = popup_text.format(str(Year), str(Hour),row["Block"], row['Count'], row['Theft'], row['Vandalism'], row['Narcotics'],\
                            row['Auto Theft'], row['Burglary'], row['Discharge'], Other) 
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
        
    return FG_map


# #### Frogtown Hotspot Map

# In[ ]:


def plot_Frogtown_hot_spot(Year=2018):
    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    B= fgc[(fgc['Year'] >= Year)]
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge'\
            ,'Robbery','Domestic Assault','Violent','Arson']
    B=B[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()
    C=B.query('Count>9 and Count < 21')
    D=B.query('Count>20 and Count < 51')
    E=B.query('Count>50')
    
    # for each row in the data, add a cicle marker
    for index, row in C.iterrows(): 
        popup_text = "Year: 2018/19<br> Address: {}<br> total incidents: {}<br> Theft: {}<br> Vandalism: {}\
                            <br> Narcotics: {}<br> Auto Theft: {}<br> Burglary: {}<br> Discharge: {}<br> Robbery: {}\
                            <br> Domestic Assault: {}<br> Violent: {}<br> Arson: {}"
        popup_text = popup_text.format(str(Year),row["Block"], row['Count'], row['Theft'], row['Vandalism'], row['Robbery'],\
                            row['Auto Theft'], row['Burglary'], row['Discharge'], row['Domestic Assault'],\
                                  row['Domestic Assault'],row['Violent'],row['Arson'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']/2,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
 
    for index, row in D.iterrows(): 
        popup_text = "Year: 2018/19<br> Address: {}<br> total incidents: {}<br> Theft: {}<br> Vandalism: {}\
                            <br> Narcotics: {}<br> Auto Theft: {}<br> Burglary: {}<br> Discharge: {}<br> Robbery: {}\
                            <br> Domestic Assault: {}<br> Violent: {}<br> Arson: {}"
        popup_text = popup_text.format(str(Year),row["Block"], row['Count'], row['Theft'], row['Vandalism'], row['Robbery'],\
                            row['Auto Theft'], row['Burglary'], row['Discharge'], row['Domestic Assault'],\
                                  row['Domestic Assault'],row['Violent'],row['Arson'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']/5,
                            color="#007849",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
        
    for index, row in E.iterrows(): 
        popup_text = "Year: 2018/19<br> Address: {}<br> total incidents: {}<br> Theft: {}<br> Vandalism: {}\
                            <br> Narcotics: {}<br> Auto Theft: {}<br> Burglary: {}<br> Discharge: {}<br> Robbery: {}\
                            <br> Domestic Assault: {}<br> Violent: {}<br> Arson: {}"
        popup_text = popup_text.format(str(Year),row["Block"], row['Count'], row['Theft'], row['Vandalism'], row['Robbery'],\
                            row['Auto Theft'], row['Burglary'], row['Discharge'], row['Domestic Assault'],\
                                  row['Domestic Assault'],row['Violent'],row['Arson'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']/9,
                            color="#800080",
                            popup=popup_text,
                            fill=True).add_to(FG_map)        

    return FG_map


# #### Saint Paul Data Load and Crimetable to Date

# In[ ]:


#Read Data
df = pd.read_csv('Datasets/Crime_Incident_Report_-_Dataset.csv')
cols= ['Case','Date','Time','Code','IncType','Incident','Grid','NNum','Neighborhood','Block','CallDispCode','CallDisposition', 'Count']
df.columns= cols

#Add Time Variables
df= df[df.Case != 18254093] #messed up time variable
df['Date']= pd.to_datetime(df['Date'])
df['Year']= df['Date'].dt.year
df=df.query('Year > 2016')
df['DayYear'] = df['Date'].dt.dayofyear
df['Community']= df['Grid'].apply(commun)
df= df.query('Code not in [9954,9959] and Community !="NaN"')

def plot_toDate_Year_SPCrime(Incident='All',Day=Max):
    if Incident=='All':
        B= df
    else: 
        B= df[(df['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    Index= ['Year','Community','Count']
    C= B[Index].groupby(['Year','Community']).sum().reset_index()
    print('This graph maps {} incidents of Saint Paul neighborhoods up to {}20XX'.format(Date))
    sns.set()
    pd.pivot_table(C, values='Count', index=['Community'], columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= Incident + ' Yearly UptoDate Total Incidents')
    return plt.show()



# In[ ]:





# ## Functions all <a name="runall"></a>
# 
# Run to activate all functions

# In[1]:


from __future__ import print_function
from ipywidgets import interact, interactive, fixed, interact_manual
import ipywidgets as widgets
import pandas as pd
import numpy as np
get_ipython().run_line_magic('matplotlib', 'inline')
import folium
import warnings
warnings.filterwarnings('ignore')

#Upload Data
fg = pd.read_csv('Datasets\FGCrime_Final.csv')

#Set max limit for uptodate function
Max= fg.loc[1,'Day_Max']

# Set a friendly Date variable
fg['FDate']=fg['Month'].astype(str) + '/' + fg['Day'].astype(str) + '/'

fgp= fg.query('Code in [9954]') # Specify proactive calls
fgc= fg.query('Code not in [9954,9959]') #specify all crime related police visits
fgc_Date= fgc[(fgc['DayYear'] <= Max)] #this specifies to date df
fgc_fire=fgc[fgc['IncType'].str.contains("Firearm")] #specifies all firearm

def plot_multicrime_byYear(Year=2019):
    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    B= fgc[(fgc['Year'] == Year)]
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge']

    T=B.query('Theft==1')
    T=T[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()

    V=B.query('Vandalism==1')
    V=V[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()

    N=B.query('Narcotics==1')
    N=N[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()

    A=B.query('Incident=="Auto Theft"')
    A=A[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()

    D=B.query('Discharge==1')
    D=D[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()

    Br=B.query('Burglary==1')
    Br=Br[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()

#Create Frogtown GeoMap
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")

    for index, row in T.iterrows(): 
        popup_text = "Address: {}<br> total theft incidents: {}"
        popup_text = popup_text.format(row["Block"],row['Count']) 
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count'] +3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)

    for index, row in V.iterrows(): 
        popup_text = "Address: {}<br> total vandalism incidents: {}"
        popup_text = popup_text.format(row["Block"],row['Count']) 
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count'] +3,
                            color="#FF0000",
                            popup=popup_text,
                            fill=True).add_to(FG_map)    

    for index, row in N.iterrows(): 
        popup_text = "Address: {}<br> total narcotics incidents: {}"
        popup_text = popup_text.format(row["Block"],row['Narcotics']) 
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Narcotics'] + 3,
                            color="#654321",
                            popup=popup_text,
                            fill=True).add_to(FG_map)     

    for index, row in A.iterrows(): 
        popup_text = "Address: {}<br> total autotheft incidents: {}"
        popup_text = popup_text.format(row["Block"],row['Count']) 
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count'] +3,
                            color='#007849',
                            popup=popup_text,
                            fill=True).add_to(FG_map)
        
    for index, row in D.iterrows(): 
        popup_text = "Address: {}<br> total discharge incidents: {}"
        popup_text = popup_text.format(row["Block"],row['Count']) 
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count'] +3,
                            color="#800080",
                            popup=popup_text,
                            fill=True).add_to(FG_map)    

    for index, row in Br.iterrows(): 
        popup_text = "Address: {}<br> total burglary incidents: {}"
        popup_text = popup_text.format(row["Block"],row['Count']) 
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count'] +3,
                            color="#0000ff",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
    
    return FG_map

def plot_Frogtown_year(Year=2018):
    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    B= fgc[(fgc['Year'] == Year)]
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge'\
            ,'Robbery','Domestic Assault','Violent','Arson']
    B=B[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()
    B=B.query('Count>3')
    
    # for each row in the data, add a cicle marker
    for index, row in B.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}<br> Theft: {}<br> Vandalism: {}\
                            <br> Narcotics: {}<br> Auto Theft: {}<br> Burglary: {}<br> Discharge: {}<br> Robbery: {}\
                            <br> Domestic Assault: {}<br> Violent: {}<br> Arson: {}"
        popup_text = popup_text.format(str(Year),row["Block"], row['Count'], row['Theft'], row['Vandalism'], row['Robbery'],\
                            row['Auto Theft'], row['Burglary'], row['Discharge'], row['Domestic Assault'],\
                                  row['Domestic Assault'],row['Violent'],row['Arson'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
        
    return FG_map


def plot_Frogtown_year_proactive(Year=2018):
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    B= fgc[(fgc['Year'] == Year)]
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge'\
            ,'Robbery','Domestic Assault','Violent','Arson']
    B=B[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()
    
    A= fgp[(fgp['Year'] == Year)]
    A= A.groupby(['Block','Latitude','Longitude']).size().reset_index()
    A.columns=['Block','Latitude','Longitude','Count'] 
    
    for index, row in B.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}<br> Theft: {}<br> Vandalism: {}\
                            <br> Narcotics: {}<br> Auto Theft: {}<br> Burglary: {}<br> Discharge: {}<br> Robbery: {}\
                            <br> Domestic Assault: {}<br> Violent: {}<br> Arson: {}"
        popup_text = popup_text.format(str(Year),row["Block"], row['Count'], row['Theft'], row['Vandalism'], row['Robbery'],\
                            row['Auto Theft'], row['Burglary'], row['Discharge'], row['Domestic Assault'],\
                                  row['Domestic Assault'],row['Violent'],row['Arson'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)

    for index, row in A.iterrows(): 
        popup_text = "Address: {}<br> total proactive calls: {}"
        popup_text = popup_text.format(row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#007849",
                            popup=popup_text,
                            fill=True).add_to(FG_map)  
        
    return FG_map

def plot_Frogtown_long_crime(Incident='Discharge'):
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge'\
            ,'Robbery','Domestic Assault','Violent','Arson','Year']

    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    B= fgc[(fgc['Incident'] == Incident)]
    B17=B.query('Year == 2018')
    B17=B17[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()
    B18=B.query('Year == 2019')
    B18=B18[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()
    B19=B.query('Year == 2020')
    B19=B19[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()

    for index, row in B17.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
    
    for index, row in B18.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"], row["Longitude"]),
                            radius=row['Count']+3,
                            color="#007849",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
    
    for index, row in B19.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#800080",
                            popup=popup_text,
                            fill=True).add_to(FG_map)    
    
    return FG_map

def plot_Frogtown_long_crime_todate(Incident='All',Day=Max):
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge'\
            ,'Robbery','Domestic Assault','Violent','Arson','Year']

    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    if Incident=='All':
        B= fgc
    else: 
        B= fgc[(fgc['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    B17=B.query('Year == 2018')
    B17=B17[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()
    B18=B.query('Year == 2019')
    B18=B18[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()
    B19=B.query('Year == 2020')
    B19=B19[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()
    print('This map displays {} incidents up to {}20XX'.format(Incident,Date))
    for index, row in B17.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
    
    for index, row in B18.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"], row["Longitude"]),
                            radius=row['Count']+3,
                            color="#007849",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
    
    for index, row in B19.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#800080",
                            popup=popup_text,
                            fill=True).add_to(FG_map)    
    
    return FG_map

# Latest Shooting Data 


def plot_Frogtown_long_crime_todate_2020(Incident='All',Day=Max, CDate=Max):
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge'\
            ,'Robbery','Domestic Assault','Violent','Arson','Year']

    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    if Incident=='All':
        B= fgc
    else: 
        B= fgc[(fgc['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == CDate)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    B=B.query('Year == 2020')
    B=B[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()
    print('This map displays {} incidents within {} days from date {}20XX'.format(Incident,Day,Date))

    
    for index, row in B.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#800080",
                            popup=popup_text,
                            fill=True).add_to(FG_map)    
    
    return FG_map

def plot_toDate_Days_SPCrime(Incident='All',Day=Max, CDate=Max):
    if Incident=='All':
        B= df
    else: 
        B= df[(df['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == CDate)]['FDate'][0:1].iloc[0,]
    Bd= CDate-Day
    B= B[(B['DayYear'] >= Bd)]
    B= B[(B['DayYear'] <= CDate)]



#convert MOnth
def Convert_Month(num):
    Months= ['January','February','March','April','May','June','July','August','September','October','November','December']
    for i in range(12):
        if num==(i+1):
            Xi=Months[i]
    return Xi

#Community function
def commun(x): 
    if x in [67,68,87,88,89,90,91,92]:
        return 'Thomas-Frogtown'
    elif x in [5,6,7,8,25,26,27,28,45,46,47,48]:
        return 'Como'
    elif x in [107, 108, 109, 110,127,128,129,130]: 
        return 'Summit-University'
    elif x in [101,102,103,104,105,106,122,123,124,125,126]: 
        return 'Union Park'
    elif x in [63,64,65,66,83,84,85,86]: 
        return 'Midway' 
    elif x in [142,143,144,145,146,162,163,164,165,166]:
        return 'Macalester_Groveland'
    elif x in [182,183,184,185,186,202,203,204,205,206,223,224,225,242,243,244,245,246]:
        return 'Highland Park'
    elif x in [147,148,149,167,168]:
        return 'Summit Hill'
    elif x in [1,2,21,22,43,44,61,62,81,82]:
        return 'St. Anthony'
    elif x in [226,207,187,188,189,267,268,169,170,171,249,150,151,230]:
        return 'West 7th'
    elif x in [209,210,211,212,213,214,215,192,193,194,195,172,173,174,175]:
        return 'West Side'
    elif x in [111,112,131,132,133,152,153]:
        return 'Capital_River'
    elif x in [98,99,100,118,119,119,120,137,138,139,140,160,197,180,200,240,280]:
        return 'Battle_Creek'
    elif x in [76,95,96,97,115,116,117,138,114,136]:
        return 'Dayton Bluff' 
    elif x in [9,10,11,12,29,30,31,32,49,50,51,52,269,69,70,71,72]:
        return 'North End'
    elif x in [13,14,15,16,33,34,35,36,53,54,55,56,73,74,75,93,94]:
        return 'Payne-Phalen'
    elif x in [17,18,19,20,37,38,39,40,56,57,58,59,60,77,78,79,80]:
        return 'Greater East Side'
    else: 
        return 'NaN'

def table_toDate_Month_Crime(Incident='All',Day=Max):
    if Incident=='All':
        B= fgc
    else: 
        B= fgc[(fgc['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    B=B.query('Year >= 2018')
    Index= ['Year','Grid','Count','Month']
    C= B[Index].groupby(['Year','Grid','Month']).sum().reset_index()
    C['Month_Name']= C.Month.apply(Convert_Month) 
    C['Community']= C.Grid.apply(commun)
    print('This table maps {} incidents up to Day {}20XX'.format(Incident,Date))
    return pd.pivot_table(C, values='Count', index=['Community','Grid'], columns=['Month', 'Year'], fill_value=0)


import matplotlib.pyplot as plt
import seaborn as sns


def plot_toDate_Year_Crime(Incident='All',Day=Max):
    if Incident=='All':
        B= fgc
    else: 
        B= fgc[(fgc['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B=B.query('Year >= 2018')
    B= B[(B['DayYear'] <= Day)]
    Index= ['Year','Grid','Count']
    C= B[Index].groupby(['Year','Grid']).sum().reset_index()
    C['Community']= C.Grid.apply(commun) 
    print('This graph maps {} incidents up to {}20XX'.format(Incident,Date))
    sns.set()
    pd.pivot_table(C, values='Count', index=['Community','Grid'], columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= Incident + ' Yearly Total Incidents UptoDate: '+ str(Date)+'20XX')
    return plt.show()




# Firearm Plot
def plot_toDate_Year_Firearm(Day=Max):
    B= fgc_fire[(fgc_fire['DayYear'] <= Day)]
    Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B.query('Year>2017')
    Index= ['Year','Grid','Count']
    C= B[Index].groupby(['Year','Grid']).sum().reset_index()
    C['Community']= C.Grid.apply(commun) 
    print('This graph maps Firearm incidents up to {}20XX'.format(Date))
    sns.set()
    pd.pivot_table(C, values='Count', index=['Community','Grid'], columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= 'Firearm Yearly Total Incidents UptoDate: ' + str(Date)+'20XX')
    return plt.show()

#4
def plot_toDate_Year_Firearm(Day=Max):
    B= fgc_fire[(fgc_fire['DayYear'] <= Day)]
    Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B.query('Year>2017')
    Index= ['Year','Grid','Count']
    C= B[Index].groupby(['Year','Grid']).sum().reset_index()
    C['Community']= C.Grid.apply(commun) 
    print('This graph maps Firearm incidents up to {}20XX'.format(Date))
    sns.set()
    pd.pivot_table(C, values='Count', index=['Community','Grid'], columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= 'Firearm Yearly Total Incidents UptoDate: ' + str(Date)+'20XX')
    return plt.show()


def plot_Frogtown_year_Latenight(Year=2018):
    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    B= fgc[(fgc['Year'] == Year)]
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge'\
            ,'Robbery','Domestic Assault','Violent','Arson']
    BM=B.query('LateNight ==0')
    BM=BM[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()
    BM=BM.query('Count>2')
    BL=B.query('LateNight ==1')
    BL=BL[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()
    BL=BL.query('Count>2')
    
    # for each row in the data, add a cicle marker
    for index, row in BM.iterrows(): 
        popup_text = "Year: {} Non-late<br> Address: {}<br> total incidents: {}<br> Theft: {}<br> Vandalism: {}\
                            <br> Narcotics: {}<br> Auto Theft: {}<br> Burglary: {}<br> Discharge: {}<br> Robbery: {}\
                            <br> Domestic Assault: {}<br> Violent: {}<br> Arson: {}"
        popup_text = popup_text.format(str(Year),row["Block"], row['Count'], row['Theft'], row['Vandalism'], row['Robbery'],\
                            row['Auto Theft'], row['Burglary'], row['Discharge'], row['Domestic Assault'],\
                                  row['Domestic Assault'],row['Violent'],row['Arson'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)

    for index, row in BL.iterrows(): 
        popup_text = "Year: {} Late<br> Address: {}<br> total incidents: {}<br> Theft: {}<br> Vandalism: {}\
                            <br> Narcotics: {}<br> Auto Theft: {}<br> Burglary: {}<br> Discharge: {}<br> Robbery: {}\
                            <br> Domestic Assault: {}<br> Violent: {}<br> Arson: {}"
        popup_text = popup_text.format(str(Year),row["Block"], row['Count'], row['Theft'], row['Vandalism'], row['Robbery'],\
                            row['Auto Theft'], row['Burglary'], row['Discharge'], row['Domestic Assault'],\
                                  row['Domestic Assault'],row['Violent'],row['Arson'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#007849",
                            popup=popup_text,
                            fill=True).add_to(FG_map)        
    return FG_map


def plot_Frogtown_yearmonth(Year=2018,Month=1):
    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    B= fgc[(fgc['Year'] == Year)]
    B= B[(B['Month'] == Month)]
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge']
    B=B[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()
    

    # for each row in the data, add a cicle marker
    for index, row in B.iterrows(): 
        Other= row['Count'] - row['Theft'] - row['Vandalism'] - row['Narcotics'] -row['Auto Theft'] - row['Burglary'] - row['Discharge']
        popup_text = "Year: {} Month: {}<br> Address: {}<br> total incidents: {}<br> Theft: {}<br> Vandalism: {}\
                            <br> Narcotics: {}<br> Auto Theft: {}<br> Burglary: {}<br> Discharge: {}<br> Other: {}"
        popup_text = popup_text.format(str(Year), str(Month),row["Block"], row['Count'], row['Theft'], row['Vandalism'], row['Narcotics'],\
                            row['Auto Theft'], row['Burglary'], row['Discharge'], Other) 
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
        
    return FG_map


def plot_Frogtown_yearhour(Year=2018,Hour=0):
    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    B= fgc[(fgc['Year'] == Year)]
    B= B[(B['Hour'] == Hour)]
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge']
    B=B[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()
    

    # for each row in the data, add a cicle marker
    for index, row in B.iterrows(): 
        Other= row['Count'] - row['Theft'] - row['Vandalism'] - row['Narcotics'] -row['Auto Theft'] - row['Burglary'] - row['Discharge']
        popup_text = "Year: {} Hour: {}<br> Address: {}<br> total incidents: {}<br> Theft: {}<br> Vandalism: {}\
                            <br> Narcotics: {}<br> Auto Theft: {}<br> Burglary: {}<br> Discharge: {}<br> Other: {}"
        popup_text = popup_text.format(str(Year), str(Hour),row["Block"], row['Count'], row['Theft'], row['Vandalism'], row['Narcotics'],\
                            row['Auto Theft'], row['Burglary'], row['Discharge'], Other) 
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
        
    return FG_map


def plot_Frogtown_long_crime_daytime_todate(Incident='All',Day=Max):
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge'\
            ,'Robbery','Domestic Assault','Violent','Arson','Year']

    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    if Incident=='All':
        B= fgc
    else: 
        B= fgc[(fgc['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    B= B.query('Hour >= 6 and Hour <20')
    B17=B.query('Year == 2018')
    B17=B17[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()
    B18=B.query('Year == 2019')
    B18=B18[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()
    B19=B.query('Year == 2020')
    B19=B19[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()
    print('This map displays {} incidents up to {}20XX from 7AM to 8PM'.format(Incident,Date))
    for index, row in B17.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
    
    for index, row in B18.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"], row["Longitude"]),
                            radius=row['Count']+3,
                            color="#007849",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
    
    for index, row in B19.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#800080",
                            popup=popup_text,
                            fill=True).add_to(FG_map)    
    
    return FG_map

#hotspot map

def plot_Frogtown_hot_spot(Year=2019):
    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    B= fgc[(fgc['Year'] >= Year)]
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge'\
            ,'Robbery','Domestic Assault','Violent','Arson']
    B=B[Index].groupby(['Block','Latitude','Longitude']).sum().reset_index()
    C=B.query('Count>9 and Count < 21')
    D=B.query('Count>20 and Count < 51')
    E=B.query('Count>50')
    
    # for each row in the data, add a cicle marker
    for index, row in C.iterrows(): 
        popup_text = "Year: 2019/20{}<br> Address: {}<br> total incidents: {}<br> Theft: {}<br> Vandalism: {}\
                            <br> Narcotics: {}<br> Auto Theft: {}<br> Burglary: {}<br> Discharge: {}<br> Robbery: {}\
                            <br> Domestic Assault: {}<br> Violent: {}<br> Arson: {}"
        popup_text = popup_text.format(str(Year),row["Block"], row['Count'], row['Theft'], row['Vandalism'], row['Robbery'],\
                            row['Auto Theft'], row['Burglary'], row['Discharge'], row['Domestic Assault'],\
                                  row['Domestic Assault'],row['Violent'],row['Arson'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']/2,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
 
    for index, row in D.iterrows(): 
        popup_text = "Year: 2019/20<br> Address: {}<br> total incidents: {}<br> Theft: {}<br> Vandalism: {}\
                            <br> Narcotics: {}<br> Auto Theft: {}<br> Burglary: {}<br> Discharge: {}<br> Robbery: {}\
                            <br> Domestic Assault: {}<br> Violent: {}<br> Arson: {}"
        popup_text = popup_text.format(str(Year),row["Block"], row['Count'], row['Theft'], row['Vandalism'], row['Robbery'],\
                            row['Auto Theft'], row['Burglary'], row['Discharge'], row['Domestic Assault'],\
                                  row['Domestic Assault'],row['Violent'],row['Arson'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']/5,
                            color="#007849",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
        
    for index, row in E.iterrows(): 
        popup_text = "Year: 2019/20<br> Address: {}<br> total incidents: {}<br> Theft: {}<br> Vandalism: {}\
                            <br> Narcotics: {}<br> Auto Theft: {}<br> Burglary: {}<br> Discharge: {}<br> Robbery: {}\
                            <br> Domestic Assault: {}<br> Violent: {}<br> Arson: {}"
        popup_text = popup_text.format(str(Year),row["Block"], row['Count'], row['Theft'], row['Vandalism'], row['Robbery'],\
                            row['Auto Theft'], row['Burglary'], row['Discharge'], row['Domestic Assault'],\
                                  row['Domestic Assault'],row['Violent'],row['Arson'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']/9,
                            color="#800080",
                            popup=popup_text,
                            fill=True).add_to(FG_map)        

    return FG_map



#Saint Paul Data

#Socarata upload Method
from sodapy import Socrata

#New Upload Method Get Information from Socrata API
client = Socrata("information.stpaul.gov", None)
#Easier to bulk upload
results = client.get("gppb-g9cg", limit=1000000)
df = pd.DataFrame.from_records(results)

#rename columns [Note the order of Columns have changed]
cols= ['Block','CallDispCode','CallDisposition','Case','Code', 'Count','Date','Grid','Incident','IncType','Neighborhood','NNum','Time']
df.columns= cols
df=df.dropna()
df = df.astype({"Case": int, "Code": int, "Grid":float, "NNum":int,"Count":int})

#Add Time Variables
df= df[df.Case != 18254093] #messed up time variable
df['Date']= pd.to_datetime(df['Date'])
df['Year']= df['Date'].dt.year
df=df.query('Year > 2017')

df['DayYear'] = df['Date'].dt.dayofyear
df['Community']= df['Grid'].apply(commun)
df= df.query('Code not in [9954,9959] and Community !="NaN"')

from datetime import datetime

df['DateTime']= pd.to_datetime(df['Date']) # Create new column called DateTime
df['Year']= df['DateTime'].dt.year #create year column
df['DayofWeek']=df['DateTime'].dt.dayofweek #create day of the week column where default 0=Monday
df['Weekend'] = df['DayofWeek'].apply(lambda x: 1 if (x>4)  else 0) #Create a weekend category
df['Month'] = df['DateTime'].dt.month # Create Month Category
df['Day'] = df['DateTime'].dt.day #Create Day of the Current month
df['DayYear'] = df['DateTime'].dt.dayofyear  #Create Day of the year (0-365)
df['Day_Max'] = df.iloc[0,-1] #selects uptodate day; NOTE: the data is sorted chronologically

#Hour Data
df['TimeHour']= pd.to_datetime(df['Time'])
df['Hour'] = df['TimeHour'].dt.hour.astype(int) #Create Hour Colum
df['LateNight'] = df['Hour'].apply(lambda x: 1 if (x>21 or x<5)  else 0) #Latenight designation from 10Pm to 6PM


df.Incident.loc[(df['Incident'] == 'Simple Asasult Dom.')] = 'Simple Assault Dom.'
df.Incident.loc[(df['Incident'] == 'Graffiti')] = 'Vandalism'
df.Incident.loc[df["Incident"].isin([ "Rape","Agg. Assault",'Homicide'])]= 'Violent'
df.Incident.loc[df["Incident"].isin(["Simple Assault Dom.","Agg. Assault Dom."])]= 'Domestic Assault'


#Current Date Numeric 
Max= df.iloc[0,14]
#GeorgeFloyd Death date
GE= 146
#Covid start Date
CV= 73


#Add Time Variables
df= df[df.Case != 18254093] #messed up time variable
df['Date']= pd.to_datetime(df['Date'])
df['Year']= df['Date'].dt.year
df=df.query('Year > 2017')
df['DayYear'] = df['Date'].dt.dayofyear
df['Community']= df['Grid'].apply(commun)
df= df.query('Code not in [9954,9959] and Community !="NaN"')
df_fire=df[df['IncType'].str.contains("Firearm")]

#Normalized Population
P1 = {'Community': ['Battle_Creek','Greater East Side','West Side','Dayton Bluff','Payne-Phalen','North End','Thomas-Frogtown','Summit-University','West 7th','Como','Midway','St. Anthony','Union Park','Macalester_Groveland','Highland Park','Summit Hill','Capital_River'],
        'Pop': [23492,30101,15459,18294,32170,24677,15454,17935,11671,16739,12688,8239,16726,19885,23867,6961,8442]
        }
Norm = pd.DataFrame(P1, columns = ['Community', 'Pop'])



def plot_toDate_Days_SPCrime(Incident='All',Day=Max, CDate=Max):
    if Incident=='All':
        B= df
    else: 
        B= df[(df['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == CDate)]['FDate'][0:1].iloc[0,]
    Bd= CDate-Day
    B= B[(B['DayYear'] >= Bd)]
    B= B[(B['DayYear'] <= CDate)]
    Index= ['Year','Community','Count']
    C= B[Index].groupby(['Year','Community']).sum().reset_index()
    print('This graph displays total {} incidents of Saint Paul neighborhoods within {} days from date {}20XX'.format(Incident,Day,Date))
    sns.set()
    pd.pivot_table(C, values='Count', index=['Community'], columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= Incident + ' Yearly UptoDate Total Incidents')    
    return plt.show()

def plot_toDate_Days_FGCrime(Incident='All',Day=Max, CDate=Max):
    if Incident=='All':
        B= fgc
    else: 
        B= fgc[(fgc['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == CDate)]['FDate'][0:1].iloc[0,]
    Bd= CDate-Day
    B= B[(B['DayYear'] >= Bd)]
    B= B[(B['DayYear'] <= CDate)]
    Index= ['Year','Grid','Count']
    C= B[Index].groupby(['Year','Grid']).sum().reset_index()
    C1=C.query('Year>2017')
    C1['Community']= C1.Grid.apply(commun) 
    print('This graph displays total {} incidents of the Frogtown Grid within {} days from date {}20XX'.format(Incident,Day,Date))
    sns.set()
    pd.pivot_table(C1, values='Count', index=['Community','Grid'], columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= Incident + ' Yearly UptoDate Total Incidents')    
    return plt.show()




#Firearm

def plot_toDate_Year_SPFirearm(Day=Max):
    B= df_fire
    Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    Index= ['Year','Community','Count']
    C= B[Index].groupby(['Year','Community']).sum().reset_index()
    print('This graph maps Firearm incidents of Saint Paul neighborhoods up to {}20XX'.format(Date))
    sns.set()
    pd.pivot_table(C, values='Count', index=['Community'], columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= 'Firearm Yearly UptoDate Total Incidents')
    return plt.show()


def plot_toDate_Days_SPCrime_Norm(Incident='All',Day=Max, CDate=Max):
    if Incident=='All':
        B= df
    else: 
        B= df[(df['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == CDate)]['FDate'][0:1].iloc[0,]
    Bd= CDate-Day
    B= B[(B['DayYear'] >= Bd)]
    B= B[(B['DayYear'] <= CDate)]
    Index= ['Year','Community','Count']
    C= B[Index].groupby(['Year','Community']).sum().reset_index()
    C1=pd.merge(C, Norm, on='Community', how='left').reset_index()
    C1['Norm_Count']= (C1.Count/C1.Pop) *10000
    print('This graph displays total {} normalized incidents of Saint Paul neighborhoods within {} days from date {}20XX'.format(Incident,Day,Date))
    sns.set()
    pd.pivot_table(C1, values='Norm_Count', index=['Community'], columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= Incident + ' Yearly UptoDate Total Normalized per 10000 Persons')    
    return plt.show()




# In[7]:


df_auto=df[df['IncType'].str.contains("Auto Accessories")]
df_auto['Amount']='Between $500-1000'
df_auto.Amount.loc[(df_auto['IncType'].str.contains("500"))] = 'Under $500'
df_auto.Amount.loc[(df_auto['IncType'].str.contains("Over"))] = 'Over $1000'
#df_auto.query('Year>2019')

import matplotlib.pyplot as plt
import seaborn as sns

def plot_SPAutoTheft(Day=Max):
    B= df_auto.query('Year>2019')
    #Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    #B= B[(B['DayYear'] <= Day)]
    Index= ['Amount','Community','Count']
    C= B[Index].groupby(['Amount','Community']).sum().reset_index()
    print('This graph displays Auto Accessory Theft incidents by cost for Saint Paul neighborhoods since 2020')
    sns.set()
    pd.pivot_table(C, values='Count', index=['Community'], columns=['Amount'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= 'Auto Accessory Theft Incidents')
    return plt.show()


plot_SPAutoTheft()

#fg.Incident.loc[(fg['Incident'] == 'Simple Asasult Dom.')] = 'Simple Assault Dom.'


# In[2]:


plot_toDate_Days_SPCrime_Norm('Discharge')


# In[3]:


plot_toDate_Days_SPCrime(Incident='Discharge')


# In[8]:


#a=df.query("Incident=='Auto Theft'")
Wa.IncType.unique()


# In[5]:


def plot_toDate_Year_SPCityCrime(Incident='All',Day=Max):
    if Incident=='All':
        B= df
    else: 
        B= df[(df['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    Index= ['Year','Count']
    C= B[Index].groupby(['Year']).sum().reset_index()
    print('This graph maps {} annual incidents of Saint Paul within {} days from {}20XX'.format(Incident,Day,Date))
    sns.set()
    pd.pivot_table(C, values='Count', index=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(6,4),title='St. Paul Total ' + Incident + ' Incidents up to 9/29/20XX')
    return plt.show()


plot_toDate_Year_SPCityCrime("Discharge")


# In[6]:


df.Incident.loc[(df['Incident'] == 'Simple Asasult Dom.')] = 'Simple Assault Dom.'
df.Incident.loc[(df['Incident'] == 'Graffiti')] = 'Vandalism'
df.Incident.loc[df["Incident"].isin([ "Rape","Agg. Assault",'Homicide'])]= 'Violent'
df.Incident.loc[df["Incident"].isin(["Simple Assault Dom.","Agg. Assault Dom."])]= 'Domestic Assault'

def plot_toDate_Days_SPTotalCrime(Day=Max, CDate=Max):
    B=df
    Date= fgc[(fgc['DayYear'] == CDate)]['FDate'][0:1].iloc[0,]
    Bd= CDate-Day
    B= B[(B['DayYear'] >= Bd)]
    B= B[(B['DayYear'] <= CDate)]
    mask = B['Incident'].isin(['Proactive Foot Patrol', 'Other'])
    B=B[~mask]
    Index= ['Year','Incident','Count']
    C= B[Index].groupby(['Year','Incident']).sum().reset_index()
    print('This graph displays total incidents in Saint Paul within {} days from date {}20XX'.format(Day,Date))
    sns.set()
    pd.pivot_table(C, values='Count', index=['Incident'], columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= 'Yearly UptoDate Total Incidents')    
    return plt.show()

plot_toDate_Days_SPTotalCrime()


# In[8]:


a=df[df['IncType'].str.contains("Automobile")]
a['Incident']= 'Car Jacking'

def plot_toDate_Days_SPTotalCrime(Day=Max, CDate=Max):
    B=a
    Date= fgc[(fgc['DayYear'] == CDate)]['FDate'][0:1].iloc[0,]
    Bd= CDate-Day
    B= B[(B['DayYear'] >= Bd)]
    B= B[(B['DayYear'] <= CDate)]
    Index= ['Year','Incident','Count']
    C= B[Index].groupby(['Year','Incident']).sum().reset_index()
    print('This graph displays total car jacking incidents in Saint Paul within {} days from date {}20XX'.format(Day,Date))
    sns.set()
    pd.pivot_table(C, values='Count', index=['Incident'], columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= 'Yearly UptoDate Total Incidents')    
    return plt.show()

plot_toDate_Days_SPTotalCrime()


# In[17]:


#df['TimeHour']= pd.to_datetime(df['Time'])
#df['Hour'] = df['TimeHour'].dt.hour.astype(int) #Create Hour Colum
#df['LateNight'] = df['Hour'].apply(lambda x: 1 if (x>21 or x<5)  else 0) #Latenight designation from 10Pm to 6PM
df['LateNight'] = df['Hour'].apply(lambda x: 1 if (x>21 or x<5)  else 0) #Latenight designation from 10Pm to 6PM


a= df[(df['DayYear'] < 224)]
a= a[(a['Incident'] =='Discharge' )]
a= a[(a['Year'] == 2021)] 

a=a[['Hour','Count']].groupby(['Hour']).sum().reset_index()
a.columns=['Hour','Count']


b= df[(df['DayYear'] < 224)]
b= b[(b['Incident'] =='Discharge' )]
b= b[(b['Year'] == 2019)]


b=b[['LateNight','Count']].groupby(['LateNight']).sum().reset_index()
b.columns=['LateNight','Count']
b



# In[15]:


df


# In[7]:


from geopy.geocoders import Nominatim # convert an address into latitude and longitude values
import matplotlib.cm as cm
import matplotlib.colors as colors
import folium # map rendering library

sp_geo = r'Geo-Json\Saint Paul Police Grid - Shapefile.geojson'

df_auto=df[df['IncType'].str.contains("Auto Accessories")]
df_auto=df_auto.query("Year>2020")
#list(df_auto.columns)

Features= ['Grid','Count']
df1=df_auto[Features]

# Create a sum and divide by Count; and 
N= df1.groupby(['Grid']).sum()

N=N.reset_index()
N.Grid = N.Grid.astype(int)
N.Grid = N.Grid.astype(str)
N

#Create Saint Paul Population Map
FG_map = folium.Map(location=[44.958326, -93.132926], zoom_start=12,tiles="OpenStreetMap")


FG_map.choropleth(
    geo_data=sp_geo,
    data=N,
    columns=['Grid','Count'],
    #nan_fill_color='purple',
    #nan_fill_opacity=0.4,
    key_on="feature.properties.gridnum",
    fill_color='YlOrRd', 
    fill_opacity=0.7, 
    line_opacity=0.2,
    legend_name='Auto Accessory Thefts since 2021',
    highlight= True,
    name= 'Clean Map'
    
)
# display map
FG_map


# In[10]:


#Auto Accessories
fgc_auto=fgc[fgc['IncType'].str.contains("Auto Accessories")]
fgc_auto['Amount']='Medium'
fgc_auto.Amount.loc[(fgc_auto['IncType'].str.contains("500"))] = 'Small'
fgc_auto.Amount.loc[(fgc_auto['IncType'].str.contains("Over"))] = 'Large'
#df_auto.query('Year>2019')

def plot_AutoAccesoriesTheft_todate(Day=Max):
    Index =['Block','Latitude','Longitude', 'Count','Amount','Year']

    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    #Date= fgc_auto[(fgc_auto['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= fgc_auto
    B= B[(B['DayYear'] <= Day)]
    B17=B.query('Amount == "Small"')
    B17=B17[Index].groupby(['Amount','Block','Latitude','Longitude']).sum().reset_index()
    B18=B.query('Amount == "Medium"')
    B18=B18[Index].groupby(['Amount','Block','Latitude','Longitude']).sum().reset_index()
    B19=B.query('Amount == "Large"')
    B19=B19[Index].groupby(['Amount','Block','Latitude','Longitude']).sum().reset_index()
    print('This map displays Auto Accessory incidents in 2021')
    for index, row in B17.iterrows(): 
        popup_text = "Amount: Small<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
    
    for index, row in B18.iterrows(): 
        popup_text = "Amount: Medium<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"], row["Longitude"]),
                            radius=row['Count']+3,
                            color="#007849",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
    
    for index, row in B19.iterrows(): 
        popup_text = "Amount: Large<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#800080",
                            popup=popup_text,
                            fill=True).add_to(FG_map)    
    
    return FG_map

plot_AutoAccesoriesTheft_todate(20)


# In[4]:


def plot_AutoAccesoriesTheft():
    Index =['Block','Latitude','Longitude', 'Count','Amount','Year']

    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    #Date= fgc_auto[(fgc_auto['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= fgc_auto.query('Year>2019')
    B17=B.query('Amount == "Small"')
    B17=B17[Index].groupby(['Amount','Block','Latitude','Longitude']).sum().reset_index()
    B18=B.query('Amount == "Medium"')
    B18=B18[Index].groupby(['Amount','Block','Latitude','Longitude']).sum().reset_index()
    B19=B.query('Amount == "Large"')
    B19=B19[Index].groupby(['Amount','Block','Latitude','Longitude']).sum().reset_index()
    print('This map displays Auto Accessory incidents for Frogtown/Midway/Rondo neighborhood since 2020')
    for index, row in B17.iterrows(): 
        popup_text = "Amount: Small<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#E37222",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
    
    for index, row in B18.iterrows(): 
        popup_text = "Amount: Medium<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"], row["Longitude"]),
                            radius=row['Count']+3,
                            color="#007849",
                            popup=popup_text,
                            fill=True).add_to(FG_map)
    
    for index, row in B19.iterrows(): 
        popup_text = "Amount: Large<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#800080",
                            popup=popup_text,
                            fill=True).add_to(FG_map)    
    
    return FG_map

plot_AutoAccesoriesTheft()


# In[3]:


from __future__ import print_function
from ipywidgets import interact, interactive, fixed, interact_manual
import ipywidgets as widgets
import pandas as pd
import numpy as np
get_ipython().run_line_magic('matplotlib', 'inline')
import folium
import warnings
warnings.filterwarnings('ignore')


#Saint Paul Data Socrata
from sodapy import Socrata

#New Upload Method Get Information from Socrata API
client = Socrata("information.stpaul.gov", None)
#Easier to bulk upload
results = client.get("gppb-g9cg", limit=1000000)
df = pd.DataFrame.from_records(results)

#rename columns [Note the order of Columns have changed]
cols= ['Block','CallDispCode','CallDisposition','Case','Code', 'Count','Date','Grid','Incident','IncType','Neighborhood','NNum','Time']
df.columns= cols
df=df.dropna()
df = df.astype({"Case": int, "Code": int, "Grid":float, "NNum":int,"Count":int})




#convert MOnth
def Convert_Month(num):
    Months= ['January','February','March','April','May','June','July','August','September','October','November','December']
    for i in range(12):
        if num==(i+1):
            Xi=Months[i]
    return Xi

#Community function
def commun(x): 
    if x in [67,68,87,88,89,90,91,92]:
        return 'Thomas-Frogtown'
    elif x in [5,6,7,8,25,26,27,28,45,46,47,48]:
        return 'Como'
    elif x in [107, 108, 109, 110,127,128,129,130]: 
        return 'Summit-University'
    elif x in [101,102,103,104,105,106,122,123,124,125,126]: 
        return 'Union Park'
    elif x in [63,64,65,66,83,84,85,86]: 
        return 'Midway' 
    elif x in [142,143,144,145,146,162,163,164,165,166]:
        return 'Macalester_Groveland'
    elif x in [182,183,184,185,186,202,203,204,205,206,223,224,225,242,243,244,245,246]:
        return 'Highland Park'
    elif x in [147,148,149,167,168]:
        return 'Summit Hill'
    elif x in [1,2,21,22,43,44,61,62,81,82]:
        return 'St. Anthony'
    elif x in [226,207,187,188,189,267,268,169,170,171,249,150,151,230]:
        return 'West 7th'
    elif x in [209,210,211,212,213,214,215,192,193,194,195,172,173,174,175]:
        return 'West Side'
    elif x in [111,112,131,132,133,152,153]:
        return 'Capital_River'
    elif x in [98,99,100,118,119,119,120,137,138,139,140,160,197,180,200,240,280]:
        return 'Battle_Creek'
    elif x in [76,95,96,97,115,116,117,138,114,136]:
        return 'Dayton Bluff' 
    elif x in [9,10,11,12,29,30,31,32,49,50,51,52,269,69,70,71,72]:
        return 'North End'
    elif x in [13,14,15,16,33,34,35,36,53,54,55,56,73,74,75,93,94]:
        return 'Payne-Phalen'
    elif x in [17,18,19,20,37,38,39,40,56,57,58,59,60,77,78,79,80]:
        return 'Greater East Side'
    else: 
        return 'NaN'

#Add Time Variables
df= df[df.Case != 18254093] #messed up time variable
df['Date']= pd.to_datetime(df['Date'])
df['Year']= df['Date'].dt.year
df=df.query('Year > 2017')
df['DayYear'] = df['Date'].dt.dayofyear
df['Community']= df['Grid'].apply(commun)
df= df.query('Code not in [9954,9959] and Community !="NaN"')
df['Month'] = df['Date'].dt.month # Create Month Category
df['Day'] = df['Date'].dt.day #Create Day of the Current month


df
#Current Date Numeric 
Max= df.iloc[0,14]
#GeorgeFloyd Death date
GE= 146
#Covid start Date
CV= 73


def plot_toDate_Days_SPCrime(Incident='All',Day=Max, CDate=Max):
    if Incident=='All':
        B= df
    else: 
        B= df[(df['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == CDate)]['FDate'][0:1].iloc[0,]
    Bd= CDate-Day
    B= B[(B['DayYear'] >= Bd)]
    B= B[(B['DayYear'] <= CDate)]
    Index= ['Year','Community','Count']
    C= B[Index].groupby(['Year','Community']).sum().reset_index()
    print('This graph displays total {} incidents of Saint Paul neighborhoods within {} days from date {}20XX'.format(Incident,Day,Date))
    sns.set()
    pd.pivot_table(C, values='Count', index=['Community'], columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= Incident + ' Yearly UptoDate Total Incidents')    
    return plt.show()





# #  Minneapolis

# In[12]:


#Minneapolis
import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings('ignore')
import matplotlib as mpl
import matplotlib.pyplot as plt
get_ipython().run_line_magic('matplotlib', 'inline')
from IPython.display import HTML
from IPython.display import display
import requests # library to handle requests
import matplotlib.pyplot as plt
import seaborn as sns

#Load Data
mp_crime19 = pd.read_csv('Datasets/MCrime_2019.csv')
mp_crime20 = pd.read_csv('Datasets/MCrime_2020.csv')
mp_crime21 = pd.read_csv('Datasets/MCrime_2021.csv')

mp_crime= pd.concat([mp_crime19, mp_crime20, mp_crime21], axis=0)

#Index=['precinct', 'neighborhood', 'offense','description','reportedDate']
#mp_crime=mp_crime[Index]
mp_crime


# In[13]:


print(mp_crime.columns)


# In[62]:


#Check Out the variables

df=mp_crime[mp_crime['description'].str.contains("THEFT")]
df['Type']='Blank'
df.Type.loc[(df['description'].str.contains("THEFT FROM MOTR VEHC"))] = 'Theft_InsideVehicle'
df.Type.loc[(df['description'].str.contains("THEFT-MOTR VEH PARTS"))] = 'Theft_MotorParts'
df.Type.loc[(df['description'].str.contains("AUTOMOBILE THEFT"))] = 'Theft_Auto'
#mp_crime.offense.unique()
#df[df['IncType'].str.contains("Theft")].IncType.unique()
#mp_crime[mp_crime['description'].str.contains("THEFT")]
df


# In[14]:


#a.description.unique()
mp_crime['Date']= pd.to_datetime(mp_crime['reportedDate'])
mp_crime['Year']= mp_crime['Date'].dt.year
mp_crime['DayYear'] = mp_crime['Date'].dt.dayofyear
mp_crime['Month'] = mp_crime['Date'].dt.month # Create Month Category
mp_crime['Day'] = mp_crime['Date'].dt.day #Create Day of the Current month
mp_crime['Count']=1
df=mp_crime[mp_crime['description'].str.contains("THEFT-MOTR VEH PARTS")]
df_auto =df

# Set a friendly Date variable
df_auto['FDate']=df_auto['Month'].astype(str) + '/' + df_auto['Day'].astype(str) + '/'

def plot_toDate_Year_MPAutoTheft(Day=29):
    B= df_auto.query('Year>2018')
    Date= df_auto[(df_auto['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    Index= ['Year','Count']
    C= B[Index].groupby(['Year']).sum().reset_index()
    print('This graph displays AutoMobile Theft of Minneapolis up to {}20XX'.format(Date))
    sns.set()
    pd.pivot_table(C, values='Count', columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= 'Motor Vehicles Parts Theft Incidents')
    return plt.show()

plot_toDate_Year_MPAutoTheft()

#df_auto.query('Year==2021 & precint==1')


# In[16]:


def plot_toDate_Year_MPAutoTheft(Day=31):
    B= df_auto.query('Year>2018')
    Date= df_auto[(df_auto['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    Index= ['Year','Count']
    C= B[Index].groupby(['Year']).sum().reset_index()
    print('This graph displays AutoMobile Theft of Minneapolis up to {}20XX'.format(Date))
    sns.set()
    pd.pivot_table(C, values='Count', columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= 'Motor Vehicles Parts Theft Incidents')
    return plt.show()

plot_toDate_Year_MPAutoTheft(181)


# In[17]:


Index1= ['Holland', 'Powderhorn Park', 'Central', 'Ventura Village',
       'Seward', 'Minnehaha', 'Cooper', 'Standish', 'Elliot Park',
       'Whittier', 'Phillips West', 'Corcoran', 'Hawthorne']

df_auto1=df_auto[df_auto['neighborhood'].isin(Index1)]


Index2=['Lowry Hill East', 'Jordan', 'Near - North',
       'Mid - City Industrial', 'Prospect Park - East River Road',
       'Longfellow', 'Waite Park', 'Northrop', 'Marshall Terrace']

df_auto2=df_auto[df_auto['neighborhood'].isin(Index2)]

Index3=['Lowry Hill', 'Marcy Holmes', 'Bryant', 'Armatage', 'Logan Park',
       'Como', 'Hiawatha', 'East Phillips', 'ECCO', 'Midtown Phillips',
       'Downtown West', 'Harrison', 'Field', 'Cleveland', 'North Loop']

df_auto3=df_auto[df_auto['neighborhood'].isin(Index3)]

Index4=['Ericsson', 'Keewaydin', 'Windom', 'East Isles', 'Willard - Hay',
       'Loring Park', 'Bryn - Mawr', 'CARAG',
       "Steven's Square - Loring Heights", 'Lyndale', 'Diamond Lake']

df_auto4=df_auto[df_auto['neighborhood'].isin(Index4)]

Index5=['Bottineau', 'Nicollet Island - East Bank', 'Howe', 'McKinley',
       'Morris Park', 'Page', 'Webber - Camden', 'Cedar Riverside',
       'King Field', 'Folwell', 'Bancroft', 'Regina', 'Audubon Park']

df_auto5=df_auto[df_auto['neighborhood'].isin(Index5)]

Index6=['Wenonah', 'Windom Park', 'Tangletown', 'Victory', 'Lynnhurst',
       'East Harriet', 'Hale', 'Fulton', 'West Calhoun', 'Lind - Bohanon',
       'St. Anthony East', 'Cedar - Isles - Dean', 'Camden Industrial']

df_auto6=df_auto[df_auto['neighborhood'].isin(Index6)]

Index7=['Shingle Creek', 'Northeast Park', 'St. Anthony West', 'Kenny',
       'Columbia Park', 'Kenwood', 'Beltrami', 'Sheridan',
       'Downtown East', 'Linden Hills', 'Sumner - Glenwood',
       'University of Minnesota']

df_auto7=df_auto[df_auto['neighborhood'].isin(Index7)]


# In[56]:


c=df_auto.query('Year<2020')
c.query('neighborhood=="Shingle Creek"')


# In[18]:


def plot_SPAutoTheftYear(Day=365):
    B= df_auto7.query('Year<2021')
    #Date= df_auto[(df_auto['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    Index= ['Year','neighborhood','Count']
    C= B[Index].groupby(['Year','neighborhood']).sum().reset_index()
    print('This graph displays "Motor Vehicles Parts Theft" incidents of Minneapolis neighborhoods.')
    sns.set()
    pd.pivot_table(C, values='Count', index=['neighborhood'], columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= 'Motor Vehicles Parts Theft Yearly Total Incidents')
    return plt.show()

plot_SPAutoTheftYear()


# In[28]:


import matplotlib.pyplot as plt
import seaborn as sns

def plot_SPAutoTheftYear(Day=365):
    B= df_auto.query('Year<2021')
    Date= df_auto[(df_auto['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    #B= B[(B['DayYear'] <= Day)]
    Index= ['Year','neighborhood','Count']
    C= B[Index].groupby(['Year','neighborhood']).sum().reset_index()
    print('This graph displays "Auto Accessory Thefts" incidents of Saint Paul neighborhoods.')
    sns.set()
    pd.pivot_table(C, values='Count', index=['neighborhood'], columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= 'Auto Accessory Thefts Yearly Total Incidents')
    return plt.show()

plot_SPAutoTheftYear()


# In[36]:


df_auto.query('Day>31')


# In[29]:


def plot_toDate_Year_MPAutoTheft(Day=31):
    B= df_auto.query('Year>2018')
    Date= df_auto[(df_auto['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    Index= ['Year','Count']
    C= B[Index].groupby(['Year']).sum().reset_index()
    print('This graph displays Motor Vehicles Parts Theft of Minneapolis up to {}20XX'.format(Date))
    sns.set()
    pd.pivot_table(C, values='Count', columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= 'Motor Vehicles Parts Theft Incidents')
    return plt.show()

plot_toDate_Year_MPAutoTheft(365)


# In[56]:


df_auto=df[df['IncType'].str.contains("Auto Accessories")]
df_auto['Amount']='Between $500-1000'
df_auto.Amount.loc[(df_auto['IncType'].str.contains("500"))] = 'Under $500'
df_auto.Amount.loc[(df_auto['IncType'].str.contains("Over"))] = 'Over $1000'
#df_auto.query('Year>2019')

import matplotlib.pyplot as plt
import seaborn as sns

def plot_SPAutoTheft(Day=Max):
    B= df_auto.query('Year>2019')
    #Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    #B= B[(B['DayYear'] <= Day)]
    Index= ['Amount','Community','Count']
    C= B[Index].groupby(['Amount','Community']).sum().reset_index()
    print('This graph displays Auto Accessory Theft incidents by cost for Saint Paul neighborhoods since 2020')
    sns.set()
    pd.pivot_table(C, values='Count', index=['Community'], columns=['Amount'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= 'Auto Accessory Theft Incidents')
    return plt.show()


plot_SPAutoTheft()

#fg.Incident.loc[(fg['Incident'] == 'Simple Asasult Dom.')] = 'Simple Assault Dom.'


# In[33]:





# In[9]:


# Set a friendly Date variable
df_auto['FDate']=df_auto['Month'].astype(str) + '/' + df_auto['Day'].astype(str) + '/'

def plot_toDate_Year_SPAutoTheft(Day=Max):
    B= df_auto.query('Year>2018')
    Date= df_auto[(df_auto['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    Index= ['Year','Community','Count']
    C= B[Index].groupby(['Year','Community']).sum().reset_index()
    print('This graph displays the Auto Accessory Theft of Saint Paul neighborhoods from {}20XX'.format(Date))
    sns.set()
    pd.pivot_table(C, values='Count', index=['Community'], columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= 'Auto Accessory Theft Yearly Total Incidents')
    return plt.show()

plot_toDate_Year_SPAutoTheft()


# In[9]:


# Set a friendly Date variable
df_auto['FDate']=df_auto['Month'].astype(str) + '/' + df_auto['Day'].astype(str) + '/'

def plot_toDate_Year_SPAutoTheft(Day=Max):
    B= df_auto.query('Year>2018')
    Date= df_auto[(df_auto['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    Index= ['Year','Count']
    C= B[Index].groupby(['Year']).sum().reset_index()
    print('This graph displays Auto Accessory Theft of Saint Paul up to {}20XX'.format(Date))
    sns.set()
    pd.pivot_table(C, values='Count', columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= 'Auto Accessory Theft Incidents')
    return plt.show()

plot_toDate_Year_SPAutoTheft()


# In[70]:


def plot_toDate_Year_SPAutoTheft(Day=366):
    B= df_auto.query('Year>2018')
    #Date= df_auto[(df_auto['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    Index= ['Year','Count']
    C= B[Index].groupby(['Year']).sum().reset_index()
    return C

plot_toDate_Year_SPAutoTheft(365)


# # Link All

# In[46]:


def plot_SPAutoTheftYear(Day=Max):
    B= df_auto.query('Year<2021')
    Date= df_auto[(df_auto['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    #B= B[(B['DayYear'] <= Day)]
    Index= ['Year','Community','Count']
    C= B[Index].groupby(['Year','Community']).sum().reset_index()
    print('This graph displays "Auto Accessory Thefts" incidents of Saint Paul neighborhoods.')
    sns.set()
    pd.pivot_table(C, values='Count', index=['Community'], columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= 'Auto Accessory Thefts Yearly Total Incidents')
    return plt.show()

plot_SPAutoTheftYear()


# In[5]:


#Saint Paul Data Socrata
from sodapy import Socrata

#New Upload Method Get Information from Socrata API
client = Socrata("information.stpaul.gov", None)
#Easier to bulk upload
results = client.get("gppb-g9cg", limit=1000000)
df = pd.DataFrame.from_records(results)

#rename columns [Note the order of Columns have changed]
cols= ['Block','CallDispCode','CallDisposition','Case','Code', 'Count','Date','Grid','Incident','IncType','Neighborhood','NNum','Time']
df.columns= cols
df=df.dropna()
df = df.astype({"Case": int, "Code": int, "Grid":float, "NNum":int,"Count":int})

#Add Time Variables
df= df[df.Case != 18254093] #messed up time variable
df['Date']= pd.to_datetime(df['Date'])
df['Year']= df['Date'].dt.year
df=df.query('Year > 2017')
df['DayYear'] = df['Date'].dt.dayofyear
df['Community']= df['Grid'].apply(commun)
df= df.query('Code not in [9954,9959] and Community !="NaN"')


#df.at[0,'DayYear']


# ## [Link to runall](#content)

# In[11]:


plot_toDate_Days_SPCrime(Incident='Discharge') #All Days


# In[13]:


#PreCovid
plot_toDate_Days_SPCrime(Incident='Discharge',Day=CV, CDate=CV)


# In[18]:


#Covid to Floyd's death
plot_toDate_Days_SPCrime(Incident='Discharge',Day=GE-CV, CDate=GE)


# In[19]:


#Post: GeorgeFloyd
plot_toDate_Days_SPCrime(Incident='Discharge',Day=Max-244, CDate=Max)


# In[20]:


def plot_toDate_Year_SPCityCrime(Incident='All',Day=Max):
    if Incident=='All':
        B= df
    else: 
        B= df[(df['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    Index= ['Year','Count']
    C= B[Index].groupby(['Year']).sum().reset_index()
    print('This graph maps {} annual incidents incidents of Saint Paul  up to {}20XX'.format(Incident,Date))
    sns.set()
    pd.pivot_table(C, values='Count', index=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(6,4),title= Incident + ' Annual Total Incidents')
    return plt.show()


# In[39]:


plot_toDate_Year_SPCityCrime('Discharge',344)


# In[36]:


def plot_toDate_Days_SPCrime_Norm(Incident='All',Day=Max, CDate=Max):
    if Incident=='All':
        B= df
    else: 
        B= df[(df['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == CDate)]['FDate'][0:1].iloc[0,]
    Bd= CDate-Day
    B= B[(B['DayYear'] >= Bd)]
    B= B[(B['DayYear'] <= CDate)]
    Index= ['Year','Community','Count']
    C= B[Index].groupby(['Year','Community']).sum().reset_index()
    C1=pd.merge(C, Norm, on='Community', how='left').reset_index()
    C1['Norm_Count']= (C1.Count/C1.Pop) *10000
    print('This graph displays total {} incidents per 10000 residents within {} days from date {}20XX'.format(Incident,Day,Date))
    sns.set()
    pd.pivot_table(C1, values='Norm_Count', index=['Community'], columns=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(12,10),title= Incident + ' Yearly UptoDate Normalized Total Incidents')    
    return plt.show()


plot_toDate_Days_SPCrime_Norm(Incident='Discharge', Day= Max-244)


# In[32]:


B= df.query("Incident=='Discharge'")
B= B.query('Hour >= 6 and Hour <20')
Index= ['Year','Count']
C= B[Index].groupby(['Year']).sum().reset_index()
C


# In[30]:


df.Incident.unique()


# In[33]:


df.Incident.loc[(df['Incident'] == 'Simple Asasult Dom.')] = 'Simple Assault Dom.'
df.Incident.loc[(df['Incident'] == 'Graffiti')] = 'Vandalism'
df.Incident.loc[df["Incident"].isin([ "Rape","Agg. Assault",'Homicide'])]= 'Violent'
df.Incident.loc[df["Incident"].isin(["Simple Assault Dom.","Agg. Assault Dom."])]= 'Domestic Assault'


# In[38]:


plot_toDate_Days_SPCrime(Incident='Auto Theft',Day=Max, CDate=Max)


# In[41]:


df


# In[45]:


#dayshooting
df['TimeHour']= pd.to_datetime(df['Time'])
df['Hour'] = df['TimeHour'].dt.hour.astype(int) #Create Hour Colum
B= df.query("Incident=='Discharge'")
B= B.query('Hour >= 6 and Hour <20')
Index= ['Year','Count']
C= B[Index].groupby(['Year']).sum().reset_index()
C


# In[47]:


def plot_toDate_Year_SPDayCrime(Incident='All',Day=Max):
    if Incident=='All':
        B= df
        B= B.query('Hour >= 6 and Hour <20')
    else: 
        B= df[(df['Incident'] == Incident)]
        B= B.query('Hour >= 6 and Hour <20')
    Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    Index= ['Year','Count']
    C= B[Index].groupby(['Year']).sum().reset_index()
    print('This graph displays daytime(7am to 8pm) {} annual incidents incidents of Saint Paul up to {}20XX'.format(Incident,Date))
    sns.set()
    pd.pivot_table(C, values='Count', index=['Year'], 
                          fill_value=0).plot(kind= 'barh', 
                                             figsize=(6,4),title= Incident + ' Annual Total Incidents')
    return plt.show()

plot_toDate_Year_SPDayCrime(Incident='Discharge', Day=Max)


# In[51]:


def plot_Frogtown_long_crime_todate1(Incident='All',Day=Max):
    Index =['Block','Latitude','Longitude', 'Count','Theft','Vandalism','Narcotics','Auto Theft','Burglary','Discharge'\
            ,'Robbery','Domestic Assault','Violent','Arson','Year']

    # generate a new map
    FG_map = folium.Map(location=[44.958326, -93.122926], zoom_start=14,tiles="OpenStreetMap")
    
    #setup
    if Incident=='All':
        B= fgc
    else: 
        B= fgc[(fgc['Incident'] == Incident)]
    Date= fgc[(fgc['DayYear'] == Day)]['FDate'][0:1].iloc[0,]
    B= B[(B['DayYear'] <= Day)]
    B19=B.query('Year == 2020')
    B19=B19[Index].groupby(['Year','Block','Latitude','Longitude']).sum().reset_index()
    print('This map displays {} incidents up to {}20XX'.format(Incident,Date))
    for index, row in B19.iterrows(): 
        popup_text = "Year: {}<br> Address: {}<br> total incidents: {}"
        popup_text = popup_text.format(row["Year"],row["Block"], row['Count'])
        folium.CircleMarker(location=(row["Latitude"],row["Longitude"]),
                            radius=row['Count']+3,
                            color="#800080",
                            popup=popup_text,
                            fill=True).add_to(FG_map)    
    
    return FG_map


plot_Frogtown_long_crime_todate1(Incident='Discharge',Day=Max)


# In[ ]: