<jupyter_start><jupyter_text>Time Series About the DataIn this notebook, we will be working with 5 data sets:- (CSV) Facebook's stock price daily throughout 2018 (obtained using the [`stock_analysis` package](https://github.com/stefmolin/stock-analysis)).- (CSV) Facebook's OHLC stock data from May 20, 2019 - May 24, 2019 per minute from [Nasdaq.com](https://www.nasdaq.com/symbol/fb/interactive-chart).- (CSV) melted stock data for Facebook from May 20, 2019 - May 24, 2019 per minute from [Nasdaq.com](https://www.nasdaq.com/symbol/fb/interactive-chart).- (DB) stock opening prices by the minute for Apple from May 20, 2019 - May 24, 2019 altered to have seconds in the time from [Nasdaq.com](https://www.nasdaq.com/symbol/aapl/interactive-chart).- (DB) stock opening prices by the minute for Facebook from May 20, 2019 - May 24, 2019 from [Nasdaq.com](https://www.nasdaq.com/symbol/fb/interactive-chart). Setup<jupyter_code>import numpy as np
import pandas as pd

fb = pd.read_csv('data/fb_2018.csv', index_col='date', parse_dates=True).assign(
    trading_volume=lambda x: pd.cut(x.volume, bins=3, labels=['low', 'med', 'high'])
)
fb.head()<jupyter_output><empty_output><jupyter_text>Time-based selection and filteringRemember, when we have a `DatetimeIndex`, we can use datetime slicing. We can provide a range of dates. We only get three days back because the stock market is closed on the weekends:<jupyter_code>fb['2018-10-11':'2018-10-15']<jupyter_output><empty_output><jupyter_text>We can select ranges of months and quarters:<jupyter_code>fb['2018-q1'].equals(fb['2018-01':'2018-03'])<jupyter_output><empty_output><jupyter_text>The `first()` method will give us a specified length of time from the beginning of the time series. Here, we ask for a week. January 1, 2018 was a holiday—meaning the market was closed. It was also a Monday, so the week here is only four days:<jupyter_code>fb.first('1W')<jupyter_output><empty_output><jupyter_text>The `last()` method will take from the end:<jupyter_code>fb.last('1W')<jupyter_output><empty_output><jupyter_text>For the next few examples, we need datetimes, so we will read in the stock data per minute file:<jupyter_code>stock_data_per_minute = pd.read_csv(
    'data/fb_week_of_may_20_per_minute.csv', index_col='date', parse_dates=True, 
    date_parser=lambda x: pd.to_datetime(x, format='%Y-%m-%d %H-%M')
)

stock_data_per_minute.head()<jupyter_output><empty_output><jupyter_text>We can use the `Grouper` to roll up our data to the daily level along with `first` and `last`:<jupyter_code>stock_data_per_minute.groupby(pd.Grouper(freq='1D')).agg({
    'open': 'first',
    'high': 'max', 
    'low': 'min', 
    'close': 'last', 
    'volume': 'sum'
})<jupyter_output><empty_output><jupyter_text>The `at_time()` method allows us to pull out all datetimes that match a certain time. Here, we can grab all the rows from the time the stock market opens (9:30 AM):<jupyter_code>stock_data_per_minute.at_time('9:30')<jupyter_output><empty_output><jupyter_text>We can use `between_time()` to grab data for the last two minutes of trading daily:<jupyter_code>stock_data_per_minute.between_time('15:59', '16:00')<jupyter_output><empty_output><jupyter_text>On average, are more shares traded within the first 30 minutes of trading or in the last 30 minutes? We can combine `between_time()` with `Groupers` and `filter()` from the [3-aggregation.ipynb notebook](https://github.com/stefmolin/Hands-On-Data-Analysis-with-Pandas/blob/master/ch_04/3-aggregations.ipynb) to answer this question. For the week in question, more are traded on average around opening time than closing time:<jupyter_code>shares_traded_in_first_30_min = stock_data_per_minute\
    .between_time('9:30', '10:00')\
    .groupby(pd.Grouper(freq='1D'))\
    .filter(lambda x: (x.volume > 0).all())\
    .volume.mean()

shares_traded_in_last_30_min = stock_data_per_minute\
    .between_time('15:30', '16:00')\
    .groupby(pd.Grouper(freq='1D'))\
    .filter(lambda x: (x.volume > 0).all())\
    .volume.mean()

shares_traded_in_first_30_min - shares_traded_in_last_30_min<jupyter_output><empty_output><jupyter_text>In cases where time doesn't matter, we can normalize the times to midnight:<jupyter_code>pd.DataFrame(
    dict(before=stock_data_per_minute.index, after=stock_data_per_minute.index.normalize())
).head()<jupyter_output><empty_output><jupyter_text>Note that we can also use `normalize()` on a `Series` object after accessing the `dt` attribute:<jupyter_code>stock_data_per_minute.index.to_series().dt.normalize().head()<jupyter_output><empty_output><jupyter_text>Shifting for lagged dataWe can use `shift()` to create some lagged data. By default, the shift will be one period. For example, we can use `shift()` to create a new column that indicates the previous day's closing price. From this new column, we can calculate the price change due to after hours trading (after the close one day right up to the open the following day):<jupyter_code>fb.assign(
    prior_close=lambda x: x.close.shift(),
    after_hours_change_in_price=lambda x: x.open - x.prior_close,
    abs_change=lambda x: x.after_hours_change_in_price.abs()
).nlargest(5, 'abs_change')<jupyter_output><empty_output><jupyter_text>The `tshift()` method will shift the `DatetimeIndex` rather than the data. However, if the goal is to to add/subtract time we can use `pd.Timedelta`:<jupyter_code>pd.date_range('2018-01-01', freq='D', periods=5) + pd.Timedelta('9 hours 30 minutes')<jupyter_output><empty_output><jupyter_text>When working with stock data, we only have data for the dates the market was open. We can use `first_valid_index()` to give us the index of the first non-null entry in our data. For September 2018, this is September 4th:<jupyter_code>fb['2018-09'].first_valid_index()<jupyter_output><empty_output><jupyter_text>Conversely, we can use `last_valid_index()` to get the last entry of non-null data. For September 2018, this is September 28th:<jupyter_code>fb['2018-09'].last_valid_index()<jupyter_output><empty_output><jupyter_text>We can use `asof()` to find the last non-null data before the point we are looking for, if it isn't in the index. From the previous result, we know that the market was not open on September 30th. It also isn't in the index:<jupyter_code>fb.index.contains('2018-09-30')<jupyter_output><empty_output><jupyter_text>If we ask for it, we will get the data from the index we got from `fb['2018-09'].last_valid_index()`, which was September 28th:<jupyter_code>fb.asof('2018-09-30')<jupyter_output><empty_output><jupyter_text>Differenced dataUsing the `diff()` method is a quick way to calculate the difference between the data and a lagged version of it. By default, it will yield the result of `data - data.shift()`:<jupyter_code>(
    fb.drop(columns='trading_volume') 
    - fb.drop(columns='trading_volume').shift()
).equals(
    fb.drop(columns='trading_volume').diff()
)<jupyter_output><empty_output><jupyter_text>We can use this to see how Facebook stock changed day-over-day:<jupyter_code>fb.drop(columns='trading_volume').diff().head()<jupyter_output><empty_output><jupyter_text>We can specify the number of periods, can be any positive or negative integer:<jupyter_code>fb.drop(columns='trading_volume').diff(-3).head()<jupyter_output><empty_output><jupyter_text>ResamplingSometimes the data is at a granularity that isn't conducive to our analysis. Consider the case where we have data per minute for the full year of 2018. Let's see what happens if we try to plot this.*Plotting will be covered [next chapter](https://github.com/stefmolin/Hands-On-Data-Analysis-with-Pandas/tree/master/ch_05), so don't worry too much about the code.*First, we import `matplotlib` for plotting:<jupyter_code>import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>Then we will look at the plot at the minute level and at the daily aggregated level (summed):<jupyter_code>np.random.seed(0)
index = pd.date_range('2018-01-01', freq='T', periods=365*24*60)
raw = pd.DataFrame(
    np.random.uniform(0, 10, size=index.shape[0]), index=index
)

fig, axes = plt.subplots(1, 2, figsize=(15, 5))
raw.plot(legend=False, ax=axes[0], title='raw data')
raw.resample('1D').sum().plot(legend=False, ax=axes[1], title='daily totals')
for ax in axes:
    ax.set_xlabel('date')
    ax.set_ylabel('events')
    
plt.suptitle('Raw versus Resampled Data')
plt.show()<jupyter_output><empty_output><jupyter_text>The plot on the left has so much data we can't see anything. However, when we aggregate to the daily totals, we see the data. We can alter the granularity of the data we are working with using resampling. Recall our minute-by-minute stock data:<jupyter_code>stock_data_per_minute.head()<jupyter_output><empty_output><jupyter_text>We can resample this to get to a daily frequency:<jupyter_code>stock_data_per_minute.resample('1D').agg({
    'open': 'first',
    'high': 'max', 
    'low': 'min', 
    'close': 'last', 
    'volume': 'sum'
})<jupyter_output><empty_output><jupyter_text>We can downsample to quarterly data:<jupyter_code>fb.resample('Q').mean()<jupyter_output><empty_output><jupyter_text>We can also use `apply()`. Here, we show the quarterly change from start to end:<jupyter_code>fb.drop(columns='trading_volume').resample('Q').apply(
    lambda x: x.last('1D').values - x.first('1D').values
)<jupyter_output><empty_output><jupyter_text>Consider the following melted stock data by the minute. We don't see the OHLC data directly:<jupyter_code>melted_stock_data = pd.read_csv('data/melted_stock_data.csv', index_col='date', parse_dates=True)
melted_stock_data.head()<jupyter_output><empty_output><jupyter_text>We can use the `ohlc()` method after resampling to recover the OHLC columns:<jupyter_code>melted_stock_data.resample('1D').ohlc()['price']<jupyter_output><empty_output><jupyter_text>Alternatively, we can upsample to increase the granularity. Note this will introduce `NaN` values:<jupyter_code>fb.resample('6H').asfreq().head()<jupyter_output><empty_output><jupyter_text>There are many ways to handle these `NaN` values. We can forward-fill with `pad()`:<jupyter_code>fb.resample('6H').pad().head()<jupyter_output><empty_output><jupyter_text>We can specify a specific value or a method with `fillna()`:<jupyter_code>fb.resample('6H').fillna('nearest').head()<jupyter_output><empty_output><jupyter_text>We can use `asfreq()` and `assign()` to specify the action per column:<jupyter_code>fb.resample('6H').asfreq().assign(
    volume=lambda x: x.volume.fillna(0), # put 0 when market is closed
    close=lambda x: x.close.fillna(method='ffill'), # carry forward
    # take the closing price if these aren't available
    open=lambda x: np.where(x.open.isnull(), x.close, x.open),
    high=lambda x: np.where(x.high.isnull(), x.close, x.high),
    low=lambda x: np.where(x.low.isnull(), x.close, x.low)
).head()<jupyter_output><empty_output><jupyter_text>MergingWe saw merging examples the [1-querying_and_merging.ipynb notebook](https://github.com/stefmolin/Hands-On-Data-Analysis-with-Pandas/blob/master/ch_04/1-querying_and_merging.ipynb). However, they all matched based on keys. With time series, it is possible that they are so granular that we never have the same time for multiple entries. Let's work with some stock data at different granularities:<jupyter_code>import sqlite3

with sqlite3.connect('data/stocks.db') as connection:
    fb_prices = pd.read_sql(
        'SELECT * FROM fb_prices', connection, 
        index_col='date', parse_dates=['date']
    )
    aapl_prices = pd.read_sql(
        'SELECT * FROM aapl_prices', connection, 
        index_col='date', parse_dates=['date']
    )<jupyter_output><empty_output><jupyter_text>The Facebook prices are at the minute granularity:<jupyter_code>fb_prices.index.second.unique()<jupyter_output><empty_output><jupyter_text>However, the Apple prices have information for the second:<jupyter_code>aapl_prices.index.second.unique()<jupyter_output><empty_output><jupyter_text>We can perform an `asof` merge to try to line these up the best we can. We specify how to handle the mismatch with the `direction` and `tolerance` parameters. We will fill in with the `direction` of `nearest` and a `tolerance` of 30 seconds. This will place the Apple data with the minute that it is closest to, so 9:31:52 will go with 9:32 and 9:37:07 will go with 9:37. Since the times are on the index, we pass `left_index` and `right_index`, as we did with merges earlier this chapter:<jupyter_code>pd.merge_asof(
    fb_prices, aapl_prices, 
    left_index=True, right_index=True, # datetimes are in the index
    # merge with nearest minute
    direction='nearest', tolerance=pd.Timedelta(30, unit='s')
).head()<jupyter_output><empty_output><jupyter_text>If we don't want to lose the seconds information with the Apple data, we can use `pd.merge_ordered()` instead, which will interleave the two. Note this is an outer join by default (`how` parameter). The only catch here is that we need to reset the index in order to join on it:<jupyter_code>pd.merge_ordered(
    fb_prices.reset_index(), aapl_prices.reset_index()
).set_index('date').head()<jupyter_output><empty_output><jupyter_text>We can pass a `fill_method` to handle `NaN` values:<jupyter_code>pd.merge_ordered(
    fb_prices.reset_index(), aapl_prices.reset_index(),
    fill_method='ffill'
).set_index('date').head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12181493071077336, 'fraction_numerical': 0.02108478617195649, 'mean_word_length': 6.031430068098481, 'pattern_counts': {'":': 0, '<': 157, '<?xml version=': 0, '>': 159, 'https://': 8, 'lorem ipsum': 0, 'www.': 4, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '39', 'dir': 'jupyter-structured-clean-dedup', 'id': '547595', 'license': 'MIT', 'n_tokens_mistral': 4802, 'n_tokens_neox': 4495, 'n_words': 1271, 'path': 'ch_04/4-time_series.ipynb', 'repo_name': 'rvmuthukumar/Hands-On-Data-Analysis-with-Pandas', 'size': '13422'}