nyrdata = 'http://ibm.biz/nyrestaurantsdata'

"""
Explanation: The Watson Studio service inside Cloud Pak for Data is an interactive, collaborative, cloud-based environment where data scientists can use multiple tools to activate their insights.  Data scientists can use the best of open source, tap into IBM's unique features, grow their capabilities, and share their successes.  In addition to all the features in the current preview, many new capabilities are being added including the ability to ingest Object Storage data with a single click, an enhanced user interface for version control, a facility to comment or chat about a notebook with others, and many more!
New York State Restaurant Inspections Notebook
This notebook will provide insights from official restaurant inspection records for most of New York state and provide visualizations of that data.  This data is available at <a href="https://health.data.ny.gov/Health/Food-Service-Establishment-Last-Inspection/cnih-y5dw" target="_blank">New York State Food Service Establishment: Last Inspection</a>.  A raw extract was taken in October of 2016 and is located at http://ibm.biz/nyrestaurantsdata.
End of explanation
"""


import pandas as pd
nyr = pd.read_csv(nyrdata)
nyr.head()

"""
Explanation: The csv (comma separated values) data will be read into a Pandas dataframe (nyr) and the first 5 records are displayed using the 'head()' method.<br>
End of explanation
"""


nyrcols = nyr[['FACILITY','TOTAL # CRITICAL VIOLATIONS','Location1']]
nyrcols.head()

"""
Explanation: Ingesting data can be as simple as using one line of code.  Similarly, data can be ingested from Cloudant, DashDB, Object Storage, relational databases, and many others.
Another dataframe will be created that will only contain the columns that are pertinent.  The 'head()' method will display the first 5 records of this dataframe.
End of explanation
"""


nyrDF = spark.createDataFrame(nyrcols)
nyrDF.registerTempTable("nyrDF")

"""
Explanation: The data will be transformed into a Spark dataframe 'nyrDF' and a table will be registered.  Spark dataframes are conceptually equivalent to a table in a relational database or a dataframe in R/Python, but with richer optimizations under the hood.  A table that is registered can be used in subsequent SQL statements.
End of explanation
"""


query = """
select 
    FACILITY, 
    trim(regexp_extract(location1, '(\\\()(.*),(.*)(\\\))',2)) as lat, 
    trim(regexp_extract(location1, '(\\\()(.*),(.*)(\\\))',3)) as lon,
    cast(`TOTAL # CRITICAL VIOLATIONS` as int) as Violations
from nyrDF 
order by Violations desc
limit 1000
"""
nyvDF = sqlContext.sql(query)
nyvDF.show(10)

"""
Explanation: Now, a Spark dataframe 'nyvDF' will be created using SQL that will contain the restaurant name (FACILITY), latitude, longitude and violations.  Note that the latitude and longitude are combined in the final column (Location1) of the retrieved data.  They will be extracted separately using regular expressions in the SQL.  The results are ordered by number of violations in descending order and the top 10 are displayed.
End of explanation
"""


import brunel
nyvPan = nyvDF.toPandas()
%brunel map ('NY') + data('nyvPan') x(lon) y(lat) color(Violations) tooltip(FACILITY)

"""
Explanation: Brunel visualization will be used to map the latitude and longitude to a New York state map.  Colors represent the number of violations as noted in the key.
End of explanation
"""


from pixiedust.display import *
display(nyvDF)

"""
Explanation: One of the many key strengths of Watson Studio is the ability to easily search and quickly learn about various topics.  For example, to find articles, tutorials or notebooks on Brunel, click on the 'link' icon on the top right hand corner of this web page ('Find Resources in the Commuity').  A side palette will appear where you can enter 'Brunel' or other topics of interest.  Related articles, tutorials, notebooks, data cards will appear.
Pixiedust provides charting and visualization.  It is an open source Python library that works as an add-on to Jupyter notebooks to improve the user experience of working with data.  Please execute the next cell for a tabular view of the data.
End of explanation
"""


