<jupyter_start><jupyter_text>Working with Time* One of the most powerful features of Pandas is its time series functionality* Dates and time are a Python and Pandas data type (like integers and strings)* By using the `datetime` data types you can do advanced, time-centric analysis* One thing to remember about computers is they are *very* specific    * *Time stamps* - a specific moment in time (July 4th, 2017 at 7:52am and 34 seconds)    * *Time intervals* - a length of time with start and end points (The year 2017)    * *Time duration* - a specific length of time (a year, a month, a day)        * Python has data structures for working with time as part of the standard library<jupyter_code># Datetime in pure Python
import datetime

date = datetime.datetime(year=2017, month=6, day=13)
date
# A more specific datetime in pure Python
import datetime

date = datetime.datetime(year=2017, month=6, day=13, hour=4, minute=45, second=3, microsecond=13)
date
type(date)
# what is that date's month?
date.month
# what is that date's day?
date.day<jupyter_output><empty_output><jupyter_text>* Python comes with a handle utility for parsing date strings into `datetime` objects called `dateutil.parser`<jupyter_code># import the parser function from the datautil library to parse human date strings
from dateutil import parser
date = parser.parse("4th of July, 2017")
date
# get the month
date.month<jupyter_output><empty_output><jupyter_text>ExerciseTry some different date strings, see how smart Python can be.<jupyter_code>my_date = parser.parse("<your date string here>")
my_date
stringy = "January 1st 2900 at 3:15pm 45seconds"
parser.parse(stringy)<jupyter_output><empty_output><jupyter_text>Formatting Dates and Times * You can use [*string format codes*](https://docs.python.org/3/library/datetime.htmlstrftime-strptime-behavior) for printing dates and time in different formats (especially useful for making human readable dates)* Pass a format string to the `strftime()` method to print out a pretty date<jupyter_code># Get the weekday 
date.strftime("%A")
date.strftime("%B")
date.strftime("%A, %B %d %f")
date.strftime("The ear of ar ord %Y")
## Try some of the different string format codes and see what happens
date.strftime("%P")
import locale
locale.getlocale()
## Try combining a few of them together with punctuation too
date.strftime("%Y-%m-%d")<jupyter_output><empty_output><jupyter_text>Working with time in Pandas* Just like how Pandas has its own datatypes for numbers, Pandas has its own dates and times (to support more granularity)* If you have a lot of dates, it is often useful to use the Pandas functions over the native Python functions* Pandas is most powerful when you index by time using the `DatetimeIndex`<jupyter_code>import pandas as pd
# Create a Series with a DateTime index
index = pd.DatetimeIndex(['2014-03-04', '2014-08-04',
                          '2015-04-04', '2015-09-04',
                          '2016-01-01', '2016-02-16'])
data = pd.Series([10, 13, 24, 34, 34, 25], index=index)
data<jupyter_output><empty_output><jupyter_text>* Now that the index is made of DateTimes we can index using date strings* Note, this only works on strings<jupyter_code># grab the value for a specific day
data["2015-04-04"]
# grab a slice between two dates
data['2014-08-01':'2016-01']
# give me everything from 2015
data['2015']<jupyter_output><empty_output><jupyter_text>* Pandas has some functions to make parsing dates easy too<jupyter_code># use the to_datetime function instead of the parser function
date = pd.to_datetime("4th of July, 2017")
date
# use string format codes to get the weekday
date.strftime("%A")
# give me today's date
today = pd.to_datetime("today")
today<jupyter_output><empty_output><jupyter_text>* That is the day, but also the exact time... * Timestamps must always be a specific moment  Working with Time on Real Data* Let's look at the [311 data for the city of Pittsburgh](https://data.wprdc.org/dataset/311-data) from the WPRDC* Did you know, you can give the URL directly to Pandas!<jupyter_code># load the 311 data directly from the WPRDC
pgh_311_data = pd.read_csv("https://data.wprdc.org/datastore/dump/76fda9d0-69be-4dd5-8108-0de7907fc5a4")
pgh_311_data.head()
# Inspect the dataframe and Pandas automatic data type detection
pgh_311_data.info()<jupyter_output><empty_output><jupyter_text>* Ok, now we have the data, but we need it to be indexed by date* **What column has the date information?*** **What format do you think that column is currently in?*** **What function might we use to convert that column into dates?**<jupyter_code>pgh_311_data['CREATED_ON'].head()
# convert the "CREATED_ON" column to dates
pd.to_datetime(pgh_311_data['CREATED_ON']).head()<jupyter_output><empty_output><jupyter_text>* We can convert the "CREATED_ON" column to Pandas `datetime` objects* Now we have to set that to the dataframe's index<jupyter_code># set the index of pgh_311_data to be the parsed dates in the "CREATED_ON" column
pgh_311_data.index = pd.to_datetime(pgh_311_data['CREATED_ON'])
pgh_311_data.head()<jupyter_output><empty_output><jupyter_text>* Do'h, now we have CREATED_ON twice, that isn't very tidy* We can also skip this extra conversion step entirely by specifying the index column and date parsing in `read_csv()` function call.<jupyter_code># load the 311 data directly from the WPRDC and parse dates directly
pgh_311_data = pd.read_csv("https://data.wprdc.org/datastore/dump/76fda9d0-69be-4dd5-8108-0de7907fc5a4",
                           index_col="CREATED_ON", 
                           parse_dates=True)
pgh_311_data.head()
pgh_311_data.info()<jupyter_output><empty_output><jupyter_text>* Now that the dataframe has been indexed by time we can select 311 complains by time<jupyter_code># Select 311 complaints on January 1st, 2016
pgh_311_data['2016-01-01']
# Select the times just around the new years celebration
pgh_311_data["2015-12-31 20:00:00":"2016-01-01 02:00:00"]<jupyter_output><empty_output><jupyter_text>* Someone clearly had a very rowdy new years   Exercise* Using the timeseries index selection, select the complaints made today * Next, try and write your code so it will work on any day you execute it    * *hint*: try the `pd.datetime('today')`     * *Another hint*: Remember the DateTime gives you the exact time     * *Yet another hint*: Datetime indexing only works with string representations<jupyter_code># Write your code here
pgh_311_data[]
pgh_311_data['2018-10-24'].head()
# create a Pandas datetime for today
today = pd.to_datetime("today")
formatted_today_string = today.strftime("%Y-%m-%d")
print(today)
print(formatted_today_string)

# use Pandas date string indexing to retrieve all rows for this today's date
todays_311s = pgh_311_data[formatted_today_string]
todays_311s.head()<jupyter_output><empty_output><jupyter_text>Grouping time with the `resample` method* The `resample()` method is like `groupby()` but for time. * First you *split* your data into groupings of time* Then *apply* an aggregation functions to perform calculations<jupyter_code># count the number of complaints per month
pgh_311_data.resample("W").size()
# compute the mean of complaints per quarter...note this doesn't make sense, but works anyway
pgh_311_data.resample("Q").mean()<jupyter_output><empty_output><jupyter_text>* Ok, these data are *begging* to be visualized, so I'm going to give you a teaser of next week<jupyter_code># Tell matplotlib to render plots inline
%matplotlib inline
# Create a graph of the monthly complaint counts
pgh_311_data['REQUEST_ID'].resample("M").count().plot();<jupyter_output><empty_output><jupyter_text>Try the code above, but re-sampling based upon different date periods. The strings for specifying an offset are located [here](http://pandas.pydata.org/pandas-docs/stable/timeseries.htmloffset-aliases) and below:|Alias|Description||-----|-----------||B|business day frequency||C|custom business day frequency||D|calendar day frequency||W|weekly frequency||M|month end frequency||SM|semi-month end frequency (15th and end of month)||BM|business month end frequency||CBM|custom business month end frequency||MS|month start frequency||SMS|semi-month start frequency (1st and 15th)||BMS|business month start frequency||CBMS|custom business month start frequency||Q|quarter end frequency||BQ|business quarter end frequency||QS|quarter start frequency||BQS|business quarter start frequency||A, Y|year end frequency||BA, BY|business year end frequency||AS, YS|year start frequency||BAS, BYS|business year start frequency||BH|business hour frequency||H|hourly frequency||T, min|minutely frequency||S|secondly frequency||L, ms|milliseconds||U, us|microseconds||N|nanoseconds|<jupyter_code># Try a different resampling here


# Try yet another resampling here<jupyter_output><empty_output><jupyter_text>Exploring the 311 Data* Now we can use what we have learned to do some exploratory data analysis on the 311 data* First, lets use the `sample()` method to grab 10 random rows so we can get a feel for the data<jupyter_code># Sample 10 random rows from the dataframe
pgh_311_data.sample(10)
# Where are the requests generally coming from?
pgh_311_data['REQUEST_ORIGIN'].value_counts()
# What neighborhood complains the most
pgh_311_data.groupby('NEIGHBORHOOD').size().sort_values(ascending=False)
# Note, for just counting the groupby and value_counts are equivalent
# There is more than one way to skin the cat (or panda)
pgh_311_data['NEIGHBORHOOD'].value_counts()
# Count the different types of requests
pgh_311_data.groupby("REQUEST_TYPE").size().sort_values(ascending=False)<jupyter_output><empty_output><jupyter_text>* This categorical data is far too granular. * Fortunately, if we look at the [311 Data](https://data.wprdc.org/dataset/311-data) we can see there is a [311 Issue and Category Codebook](https://data.wprdc.org/dataset/311-data/resource/7794b313-33be-4a8b-bf80-41751a59b84a). * What we need to do is download the CSV from Google Sheets directly into a Pandas dataframe, which requires some fancy URL-ing<jupyter_code># I googled "pandas dataframe from google sheets"
# and found a solution on Stackoverflow
# https://stackoverflow.com/a/35246041
issue_category_mapping = pd.read_csv('https://docs.google.com/spreadsheets/d/' + 
                   '1DTDBhwXj1xQG1GCBKPqivlzHQaLh2HLd0SjN1XBPUw0' +
                   '/export?gid=0&format=csv')
issue_category_mapping.head(5)  # Same result as @TomAugspurger<jupyter_output><empty_output><jupyter_text>Exercise* We have two dataframes with one shared column. The `REQUEST_TYPE` column in `pgh_311_data` maps to the `Issue` column in `issue_category_mapping`* Use the `pd.merge()` function to create a new dataframe that combines the `pgh_311_data` with the `issue_category_mapping` so we can count the number of complaints per Category instead of Request type.    * *HINT*: You will need to specify the `left_on` and `right_on` parameters* Once you have created a new dataframe, do a `groupby` on the Category column and count the number of complaints    * *HINT*: Use the `size()` aggregation function and then `sort_values()` if you want it nicely ordered.<jupyter_code># Your code here


# create a new merged dataframe
merged_311_data = pd.merge(pgh_311_data, 
         issue_category_mapping,
         left_on="REQUEST_TYPE",
         right_on="Issue")

merged_311_data.head()
# get rid of redundant columns
merged_311_data.drop(['Definition','Department', 'Issue'], 
                     axis=1, 
                     inplace=True)
merged_311_data.head()
# Group by the Category and count the size of each group then sort by 
merged_311_data.groupby("Category").size().sort_values(ascending=False)<jupyter_output><empty_output><jupyter_text>* Where are are most 311 from the Greenfield neighborhood originating?<jupyter_code># Select only rows where NEIGHBORHOOD equals "Greenfield" and then count how many complaints came from each source
query_mask = merged_311_data['NEIGHBORHOOD'] == 'Greenfield'
merged_311_data[query_mask].groupby('REQUEST_ORIGIN').size().sort_values(ascending=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.096, 'fraction_numerical': 0.03571134020618557, 'mean_word_length': 5.498392282958199, 'pattern_counts': {'":': 1, '<': 82, '<?xml version=': 0, '>': 82, 'https://': 8, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '20', 'dir': 'jupyter-structured-clean-dedup', 'id': '553435', 'license': 'MIT', 'n_tokens_mistral': 4064, 'n_tokens_neox': 3662, 'n_words': 1315, 'path': 'day-two/extra-pandas/3-working-with-time.ipynb', 'repo_name': 'mcburton/lc-python-training', 'size': '12125'}