# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# ![Callysto.ca Banner](https://github.com/callysto/curriculum-notebooks/blob/master/callysto-notebook-banner-top.jpg?raw=true)

# # Data Challenge: Using Pandas Dataframes With Commuting Data
#
# Now that we are familiar with data structures, let's tackle a problem with real data. We'll be working data from the 2016 Canadian Census. 
#
# Four years ago, an Alberta scientist used DNA extracted from an [Albertosaurus](https://en.wikipedia.org/wiki/Albertosaurus) fossil to create a living dinosaur. It escaped from her lab, and a mutation caused it to eat cars and bicycles.
#
# Local governments are asking you to use open data to determine how many people commute by car and bicycle so they can send targeted warning messages.

# **[Data Science Tip]** Cite your source.
#
# ✅Statistics Canada. 2017. Lethbridge [Census metropolitan area], Alberta and Saskatchewan [Province] (table). Census Profile. 2016 Census. Statistics Canada Catalogue no. 98-316-X2016001. Ottawa. Released November 29, 2017.
# https://www12.statcan.gc.ca/census-recensement/2016/dp-pd/prof/index.cfm?Lang=E (accessed February 13, 2020).

# ## Names
# Double-click this cell to edit it.
#
# Names of group members: ✏️
#
# Name of school: ✏️

# In the next section we will use Pandas dataframes to explore this dataset and learn more about where people in southwestern Alberta work, how they get to work, and how long it takes them to commute.
#
# Let's create a few lists with data categories that are found in our dataset. 

# +
# Build data subsets
place_of_work_data = [
 'Worked at home',
 'Worked outside Canada',
 'No fixed workplace address',
 'Worked at usual place']

commuting_employed_data = [
 'Commute within census subdivision (CSD) of residence',
 'Commute to a different census subdivision (CSD) within census division (CD) of residence',
 'Commute to a different census subdivision (CSD) and census division (CD) within province or territory of residence',
 'Commute to a different province or territory']

mode_commute_data = [
 'Car, truck, van - as a driver',
 'Car, truck, van - as a passenger',
 'Public transit',
 'Walked',
 'Bicycle',
 'Other method']

commuting_duration_data =[
 'Less than 15 minutes',
 '15 to 29 minutes',
 '30 to 44 minutes',
 '45 to 59 minutes',
 '60 minutes and over']

leave_for_work_data = [
 'Between 5 a.m. and 5:59 a.m.',
 'Between 6 a.m. and 6:59 a.m.',
 'Between 7 a.m. and 7:59 a.m.',
 'Between 8 a.m. and 8:59 a.m.',
 'Between 9 a.m. and 11:59 a.m.',
 'Between 12 p.m. and 4:59 a.m.']

print('Data subsets successfully created.')
# -

# Let's now create a dictionary using the lists above. 

# Combo datasets
work_environment_dictionary = {"Place of work":place_of_work_data,
                              "Commuting":commuting_employed_data,
                              "Mode of commute":mode_commute_data,
                              "Commuting duration":commuting_duration_data,
                              "Leaving for work time":leave_for_work_data}
print('Data dictionary created.')

# ### 📗Challenge 1 (Exploratory)
#
# Use the `work_environment_dictionary` to access any of the categories that peak your attention. Practice using multiple keys, and try accessing different values within each category using indexing notation.

# ✏️your response here


# Next we will import a number of libraries to help us. Run the cell below to import the libraries. 

# +
# Import libraries, get data subsets
import pandas as pd
import os, sys, glob, zipfile
from ipywidgets import widgets
from io import BytesIO
from urllib.request import urlopen

# Override RuntimeWarning: numpy.dtype size changed, may indicate binary incompatibility
import warnings
warnings.filterwarnings("ignore", message="numpy.dtype size changed")
warnings.filterwarnings("ignore", message="numpy.ufunc size changed")

# Pandas settings
pd.set_option('display.max_rows', 800)
pd.set_option('display.max_columns', 800)

# load "cufflinks" library under short name "cf"
import cufflinks as cf

# command to display graphics correctly in Jupyter notebook
cf.go_offline()

def enable_plotly_in_cell():
    import IPython
    from plotly.offline import init_notebook_mode
    display(IPython.core.display.HTML('''<script src="/static/components/requirejs/require.js"></script>'''))
    init_notebook_mode(connected=False)
get_ipython().events.register('pre_run_cell', enable_plotly_in_cell)

print("Success! Libraries imported as expected and graphing is enabled.")
# -

# Now to download the data from Statistics Canada.
#
# There is a lot of information in the cell below, but basically what we are doing is:
#
# 1. Downloading the data 
# 2. Uncompressing the data
# 3. Selecting the downloaded file
# 4. Reading the file as a [Pandas](https://pandas.pydata.org) dataframe
#
# The last step is important, as the `Pandas` code library transforms the contents of the file into a data structure that we can manipulate.
#
# Run the cell below. It will take a minute or two, so be patient.

# +
print("Downloading data. Please wait...")
# Link to zipped data
link_csv = "https://www12.statcan.gc.ca/census-recensement/2016/dp-pd/prof/details/download-telecharger/comp/GetFile.cfm?Lang=E&FILETYPE=CSV&GEONO=069"
 
# Unzip data in local directory
r = urlopen(link_csv).read()
z = zipfile.ZipFile(BytesIO(r))

print("Download complete. Extracting data files.")
z.extractall()

def hasNumbers(inputString):
    return any(char.isdigit() for char in inputString)

print("Extraction complete. Parsing data into pandas dataframe.")
# Get CSV files only from extracted data sets
os.chdir("./")
csv_file = []
for file in glob.glob("*.csv"):
    if hasNumbers(file):
        census_table = pd.read_csv(file)
    else:
        continue
    
print("Success!")
# -

# Next we are going to clean the data. Run the cell below. 

# +
# Data cleanup - remove unused columns
census_table = census_table.drop(['GEO_CODE (POR)','GEO_LEVEL', 'GNR','GNR_LF','DATA_QUALITY_FLAG',\
                                       'CSD_TYPE_NAME','ALT_GEO_CODE','Notes: Profile of Census Subdivisions (2247)',
                                       'Member ID: Profile of Census Subdivisions (2247)'], axis=1)

# Data cleanup - Rename columns
census_table = census_table.rename(columns={"Dim: Sex (3): Member ID: [1]: Total - Sex": "Total - Sex",\
                                            "Dim: Sex (3): Member ID: [2]: Male": "Male",\
                                           "Dim: Sex (3): Member ID: [3]: Female":"Female"})
print('Data cleanup complete.')
# -

# Time to use our data categories. Run the cell below to print the different categories you can experiment with.

# +
# Show categories
## Build widgets

# Region of interest
cities = ["Brooks", "Lethbridge","Medicine Hat"] 

style = {'description_width': 'initial'}

all_the_widgets = [widgets.Dropdown(
                    value = work_environment_dictionary["Place of work"],
                    options = work_environment_dictionary, 
                    description ='Data subsets:', 
                    style = style, 
                    disabled=False),widgets.Dropdown(
                    value = 'Lethbridge',
                    options = cities, 
                    description ='City:', 
                    style = style, 
                    disabled=False), widgets.Dropdown(
                    value = cities[1],
                    options = cities, 
                    description ='Data subsets:', 
                    style = style, 
                    disabled=False),widgets.Dropdown(
                    value = 'Lethbridge',
                    options = cities, 
                    description ='City:', 
                    style = style, 
                    disabled=False)]

display(all_the_widgets[0])
# -

# ### 📗Challenge 2 (Data Science)
#
# 1. Run the cell below. 
# 2. Look at the table and the graph. 
# 3. What are your observations about that category?
# 4. Use the menu above to help you remember the names of the keys. 
# 5. In the cell below, substitute "Place of work" for another category you are interested in. Then run the cell. 
# 6. Repeat steps 2 and 3. 
# 7. Double click on this cell to enter your observations about the data. Include numbers, how male and female compare in terms of the categories. 
#
# ✏️Your observations here:
#
#
#

# +
#✏️ Your answer here 
data_category = work_environment_dictionary["Place of work"]

# _____ Once that is complete, run the cell 

# Display dataset
# Get subsets of the data for Lethbridge and Lethbridge County


region = census_table[census_table["GEO_NAME"]==cities[1]]

# Set index to Profile of Census Subdivisions
region.set_index('DIM: Profile of Census Subdivisions (2247)', inplace=True)

var = data_category
display(region.loc[var])

# Drop Census Year and Geo name as they are the same for this subset 
vis_data = region.loc[var].drop(["CENSUS_YEAR","GEO_NAME"],axis=1)
# Visualize data 
vis_data.iplot(kind="bar",values="Dim: Sex (3): Member ID: [1]: Total - Sex",\
                                       labels="Dim: Sex (3): Member ID: [1]: Total - Sex",
                                       title="Workers conditions in " + cities[1])
# -

# ## BONUS: Comparing how people in Lethbridge, Brooks, and Medicine Hat get to work
#
# Continue exploring the data for three cities: Lethbridge, Brooks, and Medicine Hat. Use the dictionary as you did in previous exercises. 

# +
data_combo_widget = [widgets.Dropdown(
                    value = work_environment_dictionary["Place of work"],
                    options = work_environment_dictionary, 
                    description ='Data subsets:', 
                    style = style, 
                    disabled=False)]

display(data_combo_widget[0])

# +
var = work_environment_dictionary["Place of work"]

rows = []
for i in range(len(cities)):
    city = census_table[(census_table["GEO_NAME"]==cities[i])]
    for i in range(len(var)):
        
        row = city[city["DIM: Profile of Census Subdivisions (2247)"]==var[i]]
        rows.append(row)
        
result = pd.concat(rows)
display(result)

result["Male"] = result["Male"].astype(int)
result["Female"] = result["Female"].astype(int)
by_region = result.pivot_table(columns=["GEO_NAME"],\
                               index="DIM: Profile of Census Subdivisions (2247)",\
                               values=["Male","Female"])
by_region.iplot(kind='bar',title="Workers conditions in " + cities[0] +", " + cities[1] + " and "+ cities[2])
# -

# # Conclusions
#
# Double click this cell and enter your final conclusions regarding how people in southwestern Alberta get to work, as well as your recommendations about warning messages.
# ✏️
#
#

# ## Reflections
#
# Write about some or all of the following questions, either individually in separate markdown cells or as a group.
# - What is something you learned through this process?
# - How well did your group work together? Why do you think that is?
# - What were some of the hardest parts?
# - What are you proud of? What would you like to show others?
# - Are you curious about anything else related to this? Did anything surprise you?
# - How can you apply your learning to future activities?
#
# ✏️
#
#

# [![Callysto.ca License](https://github.com/callysto/curriculum-notebooks/blob/master/callysto-notebook-banner-bottom.jpg?raw=true)](https://github.com/callysto/curriculum-notebooks/blob/master/LICENSE.md)

StarcoderdataJupyterScriptsDedupFiltered