Lecture 10: Working with time-series data [SUGGESTED SOLUTIONS]

Time-series data are very common in finance and economics. Date and time systems have their own set of rules and logic, which makes them potentially complicated but also powerful. Both pandas and python have very powerful sets of tools to handle dates and time. We will work through the basics here. As always, the internet if full of more detailed applications and examples.

In this workbook we will cover

1. DateTime

1. Changing frequency, e.g., converting daily data to monthly

1. Plotting time-series data

1. Slicing dates

1. Moving-window calculations

1. DateTime (top)¶

Python uses the datetime type of object to hold date and time data. This allows for very sophisticated handling of time series data. Most of this is beyond the scope of our work, but if you ever find yourself having to deal with time zones or which week of the month a date lies in, datetime has you covered. [Python was developed to do a lot more than data analysis...]

We will mostly need to create datatime objects to hold dates. We pass datetime objects to methods that need to know about dates. We begin by importing the datetime module and cleverly call it dt.

Now for an example:

What type of object is time_1?

Ladies and gentlemen, we have a new object: datetime!

For those of you scoring at home, the datetime function we used above is actually datetime.datetime() but we called the package dt. Silly programmers. We can convert datetime objects to different string outputs using the strftime() method which takes codes as arguments and you can format the string however you wish. For instance, I added a comma -- can you find it? A list of codes is in table 11-3 of McKinney.

We might need some more datetime functionality, but that should be enough for now. Let's start working with some data!

The file 'VIXCLSD.csv' contains daily end-of-trading values of the 'VIX,' a measure of expected market volatility as implied by S&P 500 options. Business-news types like to refer to it as the 'fear index'. [That's a bit too dramatic for my tastes, but those guys have ads to sell.] The idea is that expected volatility rises when people are worried about the future.

The data look about like I would expect. What kind of variables do we have?

The 'DATE' variable is stored as string right now. This often happens because the computer sees things like hyphens and slashes among numbers and assumes the data are strings. Let's convert DATE column to a datetime object. Let's start by converting the dates and saving them to a new variable so that we can inspect it. Fortunately, pandas has a datetime method so we'll use that.

So the to_datetime() conversion creates datetime64[ns] objects. Let's convert the 'DATE' column to datetime, save it in the DataFrame, and set the index to the dates:

2. Changing frequency (top)¶

We can resample the data to change its frequency. We need to specify how to deal with the vix data. In this case, I want to average it. If we had gdp data, I would want to sum it.

We are downsampling data if we are reducing the number of observations (daily$\rightarrow$monthly) and we are upsampling the data if we are increasing the number of observations (monthly$\rightarrow$daily). In my work, I have downsampled a lot. I do not think I have ever upsampled.

We had 7,502 daily observations. Now we have 1501 weekly observations. Note the weeks are Sunday-based. Let's downsample some more.

3. Plotting time-series data (top)¶

Now that we have our index set as datetime objects, we can plot the data in sensible ways. In particular, matplotlib can gracefully handle data at different frequencies.

If we zoom in tight enough, the time axis will relabel in a natural way to reflect the changed time scale. When we refer to coordinates in a time series figure, we pass the x-coordinates as a datetime object. Below, we use datetimes to limit the x-axis and to make some annotations.

Practice: Dates ¶

Take a few minutes and try the following. Feel free to chat with those around if you get stuck.

1. Read the file 'osk.csv' into a DataFrame. The file contains daily closing prices for OshKosh Corp. and the S&P500.
2. Set the index to the date. Make sure the dates are datetime objects!

1. Normalize each series by dividing every observation by the value at 01-02-2018. Try

   prices['osk_n'] = prices['osk'] / prices.loc['2018-01-02','osk']
   

   where I called my dataframe prices. Do something similar to the S&P500.

1. Plot the two normalized series

1. Resample the data to a weekly frequency creating average weekly prices. (I would recommend creating a new DataFrame)
2. Create new indexes, normalizing by the first week of the year.
3. Add the weekly data to your figure from part 4. Use the alpha parameter to lighten up the daily data lines.
4. How does your plot look? Are the titles correct? Is there a legend or some other way of identifying the lines?

4. Slicing dates (top)¶

Our old way of referencing a value:

Our date-specific way:

An alternative way to 'zoom in' on the data is to plot a slice of the DataFrame. With an index of datetime, this is super easy.

Let's use the VIX data as an example to develop intuition for what's going on when we slice dates:

1. We can grab every observation with a year of 2008 with '2008'.

1. We can grab every observation between Jan 1, 2008 and Jan 31, 2008 by slicing with '2008/01/01':'2008/01/31'. Python is smart enough to understand references to dates that are not in the DataFrame. There is no January 1 or January 31 observation.

5. Moving-window calculations (top)¶

The rolling() method of DataFrame allows methods like mean() and var() to be calculated over a rolling window of the data. We often use moving averages to extract trends from noisy data.

Calling

vix['VIXCLS'].rolling(60)

generates a Series of rolling observations. In this case, I am asking for a 60-day window. We can directly call a method like mean() or var() to compute rolling calculations.

vix['VIXCLS'].rolling(60).mean()

A couple important things to note:

• Rolling windows only make sense with time-series data so the index must be datetime.

• We strip out any missing values in the raw data using dropna() before building the rolling windows. Otherwise, rolling() will only work on the complete 60-day windows.

Practice: Constructing and Plotting Moving Averages ¶

Take a few minutes and try the following. Feel free to chat with those around if you get stuck.

1. Compute and plot the 30-day rolling standard deviation of the Osh Kosh and S&P 500 normalized daily prices.

1. Plot only the standard deviation for the first half of the year. Use a slice on the index to do it.