<jupyter_start><jupyter_text>04 - Pandas: Working with time series data> *© 2021, <NAME> and <NAME>  (, ). Licensed under [CC BY 4.0 Creative Commons](http://creativecommons.org/licenses/by/4.0/)*---<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

plt.style.use('ggplot')<jupyter_output><empty_output><jupyter_text>Introduction: `datetime` module Standard Python contains the `datetime` module to handle date and time data:<jupyter_code>import datetime
dt = datetime.datetime(year=2016, month=12, day=19, hour=13, minute=30)
dt
print(dt) # .day,...
print(dt.strftime("%d %B %Y"))<jupyter_output><empty_output><jupyter_text>Dates and times in pandas  The ``Timestamp`` object Pandas has its own date and time objects, which are compatible with the standard `datetime` objects, but provide some more functionality to work with.  The `Timestamp` object can also be constructed from a string:<jupyter_code>ts = pd.Timestamp('2016-12-19')
ts<jupyter_output><empty_output><jupyter_text>Like with `datetime.datetime` objects, there are several useful attributes available on the `Timestamp`. For example, we can get the month (experiment with tab completion!):<jupyter_code>ts.month<jupyter_output><empty_output><jupyter_text>There is also a `Timedelta` type, which can e.g. be used to add intervals of time:<jupyter_code>ts + pd.Timedelta('5 days')<jupyter_output><empty_output><jupyter_text>Parsing datetime strings ![](http://imgs.xkcd.com/comics/iso_8601.png) Unfortunately, when working with real world data, you encounter many different `datetime` formats. Most of the time when you have to deal with them, they come in text format, e.g. from a `CSV` file. To work with those data in Pandas, we first have to *parse* the strings to actual `Timestamp` objects. REMEMBER: To convert string formatted dates to Timestamp objects: use the `pandas.to_datetime` function<jupyter_code>pd.to_datetime("2016-12-09")
pd.to_datetime("09/12/2016")
pd.to_datetime("09/12/2016", dayfirst=True)
pd.to_datetime("09/12/2016", format="%d/%m/%Y")<jupyter_output><empty_output><jupyter_text>A detailed overview of how to specify the `format` string, see the table in the python documentation: https://docs.python.org/3/library/datetime.htmlstrftime-and-strptime-behavior  `Timestamp` data in a Series or DataFrame column<jupyter_code>s = pd.Series(['2016-12-09 10:00:00', '2016-12-09 11:00:00', '2016-12-09 12:00:00'])
s<jupyter_output><empty_output><jupyter_text>The `to_datetime` function can also be used to convert a full series of strings:<jupyter_code>ts = pd.to_datetime(s)
ts<jupyter_output><empty_output><jupyter_text>Notice the data type of this series has changed: the `datetime64[ns]` dtype. This indicates that we have a series of actual datetime values. The same attributes as on single `Timestamp`s are also available on a Series with datetime data, using the **`.dt`** accessor:<jupyter_code>ts.dt.hour
ts.dt.dayofweek<jupyter_output><empty_output><jupyter_text>To quickly construct some regular time series data, the [``pd.date_range``](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.date_range.html) function comes in handy:<jupyter_code>pd.Series(pd.date_range(start="2016-01-01", periods=10, freq='3H'))<jupyter_output><empty_output><jupyter_text>Time series data: `Timestamp` in the index  River discharge example data For the following demonstration of the time series functionality, we use a sample of discharge data of the Maarkebeek (Flanders) with 3 hour averaged values, derived from the [Waterinfo website](https://www.waterinfo.be/).<jupyter_code>data = pd.read_csv("data/vmm_flowdata.csv")
data.head()<jupyter_output><empty_output><jupyter_text>We already know how to parse a date column with Pandas:<jupyter_code>data['Time'] = pd.to_datetime(data['Time'])<jupyter_output><empty_output><jupyter_text>With `set_index('datetime')`, we set the column with datetime values as the index, which can be done by both `Series` and `DataFrame`.<jupyter_code>data = data.set_index("Time")
data<jupyter_output><empty_output><jupyter_text>The steps above are provided as built-in functionality of `read_csv`:<jupyter_code>data = pd.read_csv("data/vmm_flowdata.csv", index_col=0, parse_dates=True)<jupyter_output><empty_output><jupyter_text>REMEMBER: `pd.read_csv` provides a lot of built-in functionality to support this kind of transactions when reading in a file! Check the help of the read_csv function...  The DatetimeIndex When we ensure the DataFrame has a `DatetimeIndex`, time-series related functionality becomes available:<jupyter_code>data.index<jupyter_output><empty_output><jupyter_text>Similar to a Series with datetime data, there are some attributes of the timestamp values available:<jupyter_code>data.index.day
data.index.dayofyear
data.index.year<jupyter_output><empty_output><jupyter_text>The `plot` method will also adapt its labels (when you zoom in, you can see the different levels of detail of the datetime labels):<jupyter_code>%matplotlib widget
data.plot()
# switching back to static inline plots (the default)
%matplotlib inline<jupyter_output><empty_output><jupyter_text>We have too much data to sensibly plot on one figure. Let's see how we can easily select part of the data or aggregate the data to other time resolutions in the next sections.  Selecting data from a time series We can use label based indexing on a timeseries as expected:<jupyter_code>data[pd.Timestamp("2012-01-01 09:00"):pd.Timestamp("2012-01-01 19:00")]<jupyter_output><empty_output><jupyter_text>But, for convenience, indexing a time series also works with strings:<jupyter_code>data["2012-01-01 09:00":"2012-01-01 19:00"]<jupyter_output><empty_output><jupyter_text>A nice feature is **"partial string" indexing**, where we can do implicit slicing by providing a partial datetime string.E.g. all data of 2013:<jupyter_code>data['2013':]<jupyter_output><empty_output><jupyter_text>Or all data of January up to March 2012:<jupyter_code>data['2012-01':'2012-03']<jupyter_output><empty_output><jupyter_text>EXERCISE:   select all data starting from 2012<jupyter_code>data['2012':]<jupyter_output><empty_output><jupyter_text>EXERCISE:   select all data in January for all different years<jupyter_code>data[data.index.month == 1]<jupyter_output><empty_output><jupyter_text>EXERCISE:   select all data in April, May and June for all different years<jupyter_code>data[data.index.month.isin([4, 5, 6])]<jupyter_output><empty_output><jupyter_text>EXERCISE:   select all 'daytime' data (between 8h and 20h) for all days<jupyter_code>data[(data.index.hour > 8) & (data.index.hour < 20)]<jupyter_output><empty_output><jupyter_text>The power of pandas: `resample` A very powerfull method is **`resample`: converting the frequency of the time series** (e.g. from hourly to daily data).The time series has a frequency of 1 hour. I want to change this to daily:<jupyter_code>data.resample('D').mean().head()<jupyter_output><empty_output><jupyter_text>Other mathematical methods can also be specified:<jupyter_code>data.resample('D').max().head()<jupyter_output><empty_output><jupyter_text>REMEMBER: The string to specify the new time frequency: http://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.htmloffset-aliases These strings can also be combined with numbers, eg `'10D'`...<jupyter_code>data.resample('M').mean().plot() # 10D<jupyter_output><empty_output><jupyter_text>EXERCISE:   Plot the monthly standard deviation of the columns<jupyter_code>data.resample('M').std().plot() # 'A'<jupyter_output><empty_output><jupyter_text>EXERCISE:   Plot the monthly mean and median values for the years 2011-2012 for 'L06_347'__Note__ Did you know agg to derive multiple statistics at the same time?<jupyter_code>subset = data['2011':'2012']['L06_347']
subset.resample('M').agg(['mean', 'median']).plot()<jupyter_output><empty_output><jupyter_text>EXERCISE:   plot the monthly mininum and maximum daily average value of the 'LS06_348' column<jupyter_code>daily = data['LS06_348'].resample('D').mean() # daily averages calculated
daily.resample('M').agg(['min', 'max']).plot() # monthly minimum and maximum values of these daily averages<jupyter_output><empty_output><jupyter_text>EXERCISE:   Make a bar plot of the mean of the stations in year of 2013<jupyter_code>data['2013':'2013'].mean().plot(kind='barh')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11536626103555238, 'fraction_numerical': 0.031973276067764254, 'mean_word_length': 7.162609542356377, 'pattern_counts': {'":': 1, '<': 132, '<?xml version=': 0, '>': 133, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, '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': '32', 'dir': 'jupyter-structured-clean-dedup', 'id': '1684', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 2925, 'n_tokens_neox': 2696, 'n_words': 779, 'path': '_solved/pandas_04_time_series_data.ipynb', 'repo_name': 'jorisvandenbossche/FLAMES-python-data-wrangling', 'size': '8405'}