<jupyter_start><jupyter_text>Time series**Prerequisites**- [Python functions](../python_fundamentals/functions.ipynb)  - [GroupBy](groupby.ipynb)  **Outcomes**- Know how pandas handles dates  - Understand how to parse strings into `datetime` objects  - Know how to write dates as custom formatted strings  - Be able to access day, month, year, etc. for a `DateTimeIndex` and  a column with `dtype` `datetime`  - Understand both rolling and re-sampling operations and the difference  between the two  **Data**- Bitcoin to USD exchange rates from March 2014 to the present    Outline- [Time series](Time-series)    - [Intro](Intro)    - [Parsing Strings as Dates](Parsing-Strings-as-Dates)    - [Date Formatting](Date-Formatting)    - [Extracting Data](Extracting-Data)    - [Accessing Date Properties](Accessing-Date-Properties)    - [Leads and Lags: `df.shift`](Leads-and-Lags:-`df.shift`)    - [Rolling Computations: `.rolling`](Rolling-Computations:-`.rolling`)    - [Changing Frequencies: `.resample`](Changing-Frequencies:-`.resample`)    - [Optional: API keys](Optional:-API-keys)    - [Exercises](Exercises)<jupyter_code># Uncomment following line to install on colab
#! pip install qeds
import os
import pandas as pd
import matplotlib.pyplot as plt
import quandl
import qeds

# see section on API keys at end of lecture!
quandl.ApiConfig.api_key = os.environ.get("QUANDL_AUTH", "Dn6BtVoBhzuKTuyo6hbp")
start_date = "2014-05-01"

%matplotlib inline
# activate plot theme
import qeds
qeds.themes.mpl_style();<jupyter_output><empty_output><jupyter_text>Intropandas has extensive support for handling dates and times.We will loosely refer to data with date or time information as timeseries data.In this lecture, we will cover the most useful parts of pandas’ timeseries functionality.Among these topics are:- Parsing strings as dates  - Writing `datetime` objects as (inverse operation of previous point)  - Extracting data from a DataFrame or Series with date information in  the index  - Shifting data through time (taking leads or lags)  - Re-sampling data to a different frequency and rolling operations  However, even more than with previous topics, we will skip a lot of thefunctionality pandas offers, and we urge you to refer to the [officialdocumentation](https://pandas.pydata.org/pandas-docs/stable/timeseries.html)for more information.  Parsing Strings as DatesWhen working with time series data, we almost always receive the datawith dates encoded as strings.Hopefully, the date strings follow a structured format or pattern.One common pattern is `YYYY-MM-DD`: 4 numbers for the year, 2 for themonth, and 2 for the day with each section separated by a `-`.For example, we write Christmas day 2017 in this format as<jupyter_code>christmas_str = "2017-12-25"<jupyter_output><empty_output><jupyter_text>To convert a string into a time-aware object, we use the`pd.to_datetime` function.<jupyter_code>christmas = pd.to_datetime(christmas_str)
print("The type of christmas is", type(christmas))
christmas<jupyter_output>The type of christmas is <class 'pandas._libs.tslibs.timestamps.Timestamp'><jupyter_text>The `pd.to_datetime` function is pretty smart at guessing the formatof the date…<jupyter_code>for date in ["December 25, 2017", "Dec. 25, 2017",
             "Monday, Dec. 25, 2017", "25 Dec. 2017", "25th Dec. 2017"]:
    print("pandas interprets {} as {}".format(date, pd.to_datetime(date)))<jupyter_output>pandas interprets December 25, 2017 as 2017-12-25 00:00:00
pandas interprets Dec. 25, 2017 as 2017-12-25 00:00:00
pandas interprets Monday, Dec. 25, 2017 as 2017-12-25 00:00:00
pandas interprets 25 Dec. 2017 as 2017-12-25 00:00:00
pandas interprets 25th Dec. 2017 as 2017-12-25 00:00:00<jupyter_text>However, sometimes we will need to give pandas a hint.For example, that same time (midnight on Christmas) would be reported onan Amazon transaction report as<jupyter_code>christmas_amzn = "2017-12-25T00:00:00+ 00 :00"<jupyter_output><empty_output><jupyter_text>If we try to pass this to `pd.to_datetime`, it will fail. ```pythonpd.to_datetime(christmas_amzn)``` To parse a date with this format, we need to specify the `format`argument for `pd.to_datetime`.<jupyter_code>amzn_strftime = "%Y-%m-%dT%H:%M:%S+ 00 :00"
pd.to_datetime(christmas_amzn, format=amzn_strftime)<jupyter_output><empty_output><jupyter_text>Can you guess what `amzn_strftime` represents?Let’s take a closer look at `amzn_strftime` and `christmas_amzn`.<jupyter_code>print(amzn_strftime)
print(christmas_amzn)<jupyter_output>%Y-%m-%dT%H:%M:%S+ 00 :00
2017-12-25T00:00:00+ 00 :00<jupyter_text>Notice that both of the strings have a similar form, but that instead of actual numerical values, `amzn_strftime` has *placeholders*.Specifically, anywhere the `%` shows up is a signal to the `pd.to_datetime`function that it is where relevant information is stored.For example, the `%Y` is a stand-in for a four digit year, `%m` isfor 2 a digit month, and so on…The official [Pythondocumentation](https://docs.python.org/3/library/datetime.htmlstrftime-and-strptime-behavior) contains a complete list of possible `%`something patterns that are acceptedin the `format` argument.> See exercise 1 in the [*exercise list*](exerciselist-0)  Multiple DatesIf we have dates in a Series (e.g. column of DataFrame) or a list, wecan pass the entire collection to `pd.to_datetime` and get acollection of dates back.We’ll just show an example of that here as the mechanics are the same asa single date.<jupyter_code>pd.to_datetime(["2017-12-25", "2017-12-31"])<jupyter_output><empty_output><jupyter_text>Date FormattingWe can use the `%`pattern format to have pandas write `datetime`objects as specially formatted strings using the `strftime` (stringformat time) method.For example,<jupyter_code>christmas.strftime("We love %A %B %d (also written %c)")<jupyter_output><empty_output><jupyter_text>> See exercise 2 in the [*exercise list*](exerciselist-0)  Extracting DataWhen the index of a DataFrame has date information and pandasrecognizes the values as `datetime` values, we can leverage someconvenient indexing features for extracting data.The flexibility of these features is best understood through example,so let’s load up some data and take a look.<jupyter_code>btc_usd = quandl.get("BCHARTS/BITSTAMPUSD", start_date=start_date)
btc_usd.info()
btc_usd.head()<jupyter_output><class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 2085 entries, 2014-05-01 to 2020-01-14
Data columns (total 7 columns):
Open                 2085 non-null float64
High                 2085 non-null float64
Low                  2085 non-null float64
Close                2085 non-null float64
Volume (BTC)         2085 non-null float64
Volume (Currency)    2085 non-null float64
Weighted Price       2085 non-null float64
dtypes: float64(7)
memory usage: 130.3 KB<jupyter_text>Here, we have the Bitcoin (BTC) to US dollar (USD) exchange rate fromMarch 2014 until today.Notice that the type of index is `DateTimeIndex`.This is the key that enables things like…Extracting all data for the year 2015 by passing `"2015"` to `.loc`.<jupyter_code>btc_usd.loc["2015"]<jupyter_output><empty_output><jupyter_text>We can also narrow down to specific months.<jupyter_code># By month's name
btc_usd.loc["August 2017"]
# By month's number
btc_usd.loc["08/2017"]<jupyter_output><empty_output><jupyter_text>Or even a day…<jupyter_code># By date name
btc_usd.loc["August 1, 2017"]
# By date number
btc_usd.loc["08-01-2017"]<jupyter_output><empty_output><jupyter_text>What can we pass as the `.loc` argument when we have a`DateTimeIndex`?Anything that can be converted to a `datetime` using`pd.to_datetime`, *without* having to specify the format argument.When that condition holds, pandas will return *all* rows whose date inthe index “belong” to that date or period.We can also use the range shorthand notation to give a start and enddate for selection.<jupyter_code>btc_usd.loc["April 1, 2015":"April 10, 2015"]<jupyter_output><empty_output><jupyter_text>> See exercise 3 in the [*exercise list*](exerciselist-0)  Accessing Date PropertiesSometimes, we would like to directly access a part of the date/time.If our date/time information is in the index, we can to `df.index.XX`where `XX` is replaced by `year`, `month`, or whatever we wouldlike to access.<jupyter_code>btc_usd.index.year
btc_usd.index.day<jupyter_output><empty_output><jupyter_text>We can also do the same if the date/time information is stored in acolumn, but we have to use a slightly different syntax. ```pythondf["column_name"].dt.XX```<jupyter_code>btc_date_column = btc_usd.reset_index()
btc_date_column.head()
btc_date_column["Date"].dt.year.head()
btc_date_column["Date"].dt.month.head()<jupyter_output><empty_output><jupyter_text>Leads and Lags: `df.shift`When doing time series analysis, we often want to compare data at onedate against data at another date.pandas can help us with this if we leverage the `shift` method.Without any additional arguments, `shift()` will move all data*forward* one period, filling the first row with missing data.<jupyter_code># so we can see the result of shift clearly
btc_usd.head()
btc_usd.shift().head()<jupyter_output><empty_output><jupyter_text>We can use this to compute the percent change from one day to the next.(Quiz: Why does that work? Remember how pandas uses the index to *align*data.)<jupyter_code>((btc_usd - btc_usd.shift()) / btc_usd.shift()).head()<jupyter_output><empty_output><jupyter_text>Setting the first argument to `n` tells pandas to shift the data down`n` rows (apply an `n` period lag).<jupyter_code>btc_usd.shift(3).head()<jupyter_output><empty_output><jupyter_text>A negative value will shift the data *up* or apply a lead.<jupyter_code>btc_usd.shift(-2).head()
btc_usd.shift(-2).tail()<jupyter_output><empty_output><jupyter_text>> See exercise 4 in the [*exercise list*](exerciselist-0)  Rolling Computations: `.rolling`pandas has facilities that enable easy computation of *rollingstatistics*.These are best understood by example, so we will dive right in.<jupyter_code># first take only the first 6 rows so we can easily see what is going on
btc_small = btc_usd.head(6)
btc_small<jupyter_output><empty_output><jupyter_text>Below, we compute the 2 day moving average (for all columns).<jupyter_code>btc_small.rolling("2d").mean()<jupyter_output><empty_output><jupyter_text>To do this operation, pandas starts at each row (date) then looks*backwards* the specified number of periods (here 2 days) and thenapplies some aggregation function (`mean`) on all the data in thatwindow.If pandas cannot look back the full length of the window (e.g. whenworking on the first row), it fills as much of the window as possibleand then does the operation. Notice that the value at 2014-05-01 isthe same in both DataFrames.Below, we see a visual depiction of the rolling maximum on a 21 daywindow for the whole dataset.<jupyter_code>fig, ax = plt.subplots(figsize=(10, 4))
btc_usd["Open"].plot(ax=ax, linestyle="--", alpha=0.8)
btc_usd.rolling("21d").max()["Open"].plot(ax=ax, alpha=0.8, linewidth=3)
ax.legend(["Original", "21 day max"])<jupyter_output><empty_output><jupyter_text>We can also ask pandas to `apply` custom functions, similar to what wesaw when studying GroupBy.<jupyter_code>def is_volatile(x):
    "Returns a 1 if the variance is greater than 1, otherwise returns 0"
    if x.var() > 1.0:
        return 1.0
    else:
        return 0.0
btc_small.rolling("2d").apply(is_volatile)<jupyter_output>/home/ubuntu/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: FutureWarning: Currently, 'apply' passes the values as ndarrays to the applied function. In the future, this will change to passing it as Series objects. You need to specify 'raw=True' to keep the current behaviour, and you can pass 'raw=False' to silence this warning
  """Entry point for launching an IPython kernel.<jupyter_text>> See exercise 5 in the [*exercise list*](exerciselist-0)To make the optimal decision, we need to know the maximumdifference between the close price at the end of the window and the openprice at the start of the window.> See exercise 6 in the [*exercise list*](exerciselist-0)  Changing Frequencies: `.resample`In addition to computing rolling statistics, we can also change thefrequency of the data.For example, instead of a monthly moving average, suppose that we wantedto compute the average *within* each calendar month.We will use the `resample` method to do this.Below are some examples.<jupyter_code># business quarter
btc_usd.resample("BQ").mean()<jupyter_output><empty_output><jupyter_text>Note that unlike with `rolling`, a single number is returned foreach column for each quarter.The `resample` method will alter the frequency of the data and thenumber of rows in the result will be different from the number of rowsin the input.On the other hand, with `rolling`, the size and frequency of the resultare the same as the input.We can sample at other frequencies and aggregate with multiple aggregationsfunction at once.<jupyter_code># multiple functions at 2 start-of-quarter frequency
btc_usd.resample("2BQS").agg(["min", "max"])<jupyter_output><empty_output><jupyter_text>As with `groupby` and `rolling`, you can also provide customfunctions to `.resample(...).agg` and `.resample(...).apply`> See exercise 7 in the [*exercise list*](exerciselist-0)To make the optimal decision we need to, for each month,compute the maximum value of the close price on any day minus the openprice on the first day of the month.> See exercise 8 in the [*exercise list*](exerciselist-0)  Optional: API keysRecall above that we had the line of code: ```pythonquandl.ApiConfig.api_key = "<KEY>"``` This line told the `quandl` library that when obtaining making requests for data, it should use the *API key* `<KEY>`.An API key is a sort of password that web services (like the Quandl API) require you to provide when you make requests.Using this password, we were able to make a request to Quandl to obtain data directly from them.The API key used here is one that we requested on behalf of this course.If you plan to use Quandl more extensively, you should obtain your own personal API key from [their website](https://docs.quandl.com/docssection-authentication) and re-run the `quandl.ApiConfig.api_key...` line of code with your new API key on the right-hand side.  Exercises**Exercise 1**By referring to table found at the link above, figure out the correct argument topass as `format` in order to parse the dates in the next three cells below.Test your work by passing your format string to `pd.to_datetime`.<jupyter_code>christmas_str2 = "2017:12:25"
dbacks_win = "M:11 D:4 Y:2001 9:15 PM"
america_bday = "America was born on July 4, 1776"<jupyter_output><empty_output><jupyter_text>([*back to text*](exercise-0))**Exercise 2**Use `pd.to_datetime` to express the birthday of one of your friendsor family members as a `datetime` object.Then use the `strftime` method to write a message of the format: ```pythonNAME's birthday is June 10, 1989 (a Saturday)``` (where the name and date are replaced by the appropriate values)([*back to text*](exercise-1))**Exercise 3**For each item in the list, extract the specified data from `btc_usd`:- July 2017 through August 2017 (inclusive)  - April 25, 2015 to June 10, 2016  - October 31, 2017  ([*back to text*](exercise-2))**Exercise 4**Using the `shift` function, determine the week with the largest percent changein the volume of trades (the `"Volume (BTC)"` column).Repeat the analysis at the bi-weekly and monthly frequencies.Hint 1: We have data at a *daily* frequency and one week is `7` days.Hint 2: Approximate a month by 30 days.<jupyter_code># your code here<jupyter_output><empty_output><jupyter_text>([*back to text*](exercise-3))**Exercise 5**Imagine that you have access to the [DeLorean time machine](https://en.wikipedia.org/wiki/DeLorean_time_machine)from "Back to the Future".You are allowed to use the DeLorean only once, subject to the followingconditions:- You may travel back to any day in the past.  - On that day, you may purchase one bitcoin *at market open*.  - You can then take the time machine 30 days into the future and sell your bitcoin *at market close*.  - Then you return to the present, pocketing the profits.  How would you pick the day?Think carefully about what you would need to compute to make theoptimal choice. Try writing it out in the markdown cell below so youhave a clear description of the *want* operator that we will apply afterthe exercise.(Note: **Don't** look too far below, because in the next non-empty cellwe have written out our answer.)To make this decision, we want to know ...**Your answer here**([*back to text*](exercise-4))**Exercise 6**Do the following:1. Write a pandas function that implements your strategy.  1. Pass it to the `agg` method of `rolling_btc`.  1. Extract the `"Open"` column from the result.  1. Find the date associated with the maximum value in that column.  How much money did you make? Compare with your neighbor.<jupyter_code>def daily_value(df):
    # DELETE `pass` below and replace it with your code
    pass

rolling_btc = btc_usd.rolling("30d")

# do steps 2-4 here<jupyter_output><empty_output><jupyter_text>([*back to text*](exercise-5))**Exercise 7**Now suppose you still have access to the DeLorean, but the conditions areslightly different.You may now:- Travel back to the *first day* of any month in the past.  - On that day, you may purchase one bitcoin *at market open*.  - You can then travel to any day *in that month* and sell the bitcoin *at market close*.  - Then return to the present, pocketing the profits.  To which month would you travel? On which day of that month would you returnto sell the bitcoin?Discuss with your neighbor what you would need to compute to make theoptimal choice. Try writing it out in the markdown cell below so youhave a clear description of the *want* operator that we will apply afterthe exercise.(Note: **Don't** look too many cells below, because we have written outour answer.)To make the optimal decision we need ...**Your answer here**([*back to text*](exercise-6))**Exercise 8**Do the following:1. Write a pandas function that implements your strategy.  1. Pass it to the `agg` method of `resampled_btc`.  1. Extract the `"Open"` column from the result.  1. Find the date associated with the maximum value in that column.  How much money did you make? Compare with your neighbor.Was this strategy more profitable than the previous one? By how much?<jupyter_code>def monthly_value(df):
    # DELETE `pass` below and replace it with your code
    pass

resampled_btc = btc_usd.resample("MS")

# Do steps 2-4 here<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09251642540462582, 'fraction_numerical': 0.026440895251322045, 'mean_word_length': 5.259445001671682, 'pattern_counts': {'":': 1, '<': 120, '<?xml version=': 0, '>': 129, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '30', 'dir': 'jupyter-structured-clean-dedup', 'id': '546649', 'license': 'MIT', 'n_tokens_mistral': 6102, 'n_tokens_neox': 5567, 'n_words': 2286, 'path': 'code_notebooks/1.1-quantecon/pandas/timeseries.ipynb', 'repo_name': 'jacqueshbartlett/data-science-methods', 'size': '18751'}