<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 <!--<img width=700px; src="../img/logoUPSayPlusCDS_990.png"> -->
<p style="margin-top
Step1:  1. Let's start with a showcase
Case 1
Step2:  Starting from reading this dataset, to answering questions about this data in a few lines of code
Step3:  How does the survival rate of the passengers differ between sexes?
Step4:  Or how does it differ between the different classes?
Step5:  All the needed functionality for the above examples will be explained throughout this tutorial.
Case 2
Step6:  to answering questions about this data in a few lines of code
Step7:  What is the difference in diurnal profile between weekdays and weekend?
Step8:  We will come back to these example, and build them up step by step.
2. Pandas
Step9:  Attributes of the DataFrame
A DataFrame has besides a index attribute, also a columns attribute
Step10:  To check the data types of the different columns
Step11:  An overview of that information can be given with the info() method
Step12:  Also a DataFrame has a values attribute, but attention
Step13:  Apart from importing your data from an external source (text file, excel, database, ..), one of the most common ways of creating a dataframe is from a dictionary of arrays or lists.
Note that in the IPython notebook, the dataframe will display in a rich HTML view
Step14:  One-dimensional data
Step15:  Attributes of a Series
Step16:  You can access the underlying numpy array representation with the .values attribute
Step17:  We can access series values via the index, just like for NumPy arrays
Step18:  Unlike the NumPy array, though, this index can be something other than integers
Step19:  but with the power of numpy arrays. Many things you can do with numpy arrays, can also be applied on DataFrames / Series.
Eg element-wise operations
Step20:  A range of methods
Step21:  Fancy indexing, like indexing with a list or boolean indexing
Step22:  But also a lot of pandas specific methods, e.g.
Step23:  <div class="alert alert-success">
<b>EXERCISE</b>
Step24:  <div class="alert alert-success">
<b>EXERCISE</b>
Step25:  3. Data import and export
A wide range of input/output formats are natively supported by pandas
Step26:  Very powerful csv reader
Step27:  Luckily, if we have a well formed csv file, we don't need many of those arguments
Step28:  <div class="alert alert-success">
<b>EXERCISE</b>
Step29:  4. Exploration
Some useful methods
Step30:  info()
Step31:  Getting some basic summary statistics about the data with describe
Step32:  Quickly visualizing the data
Step33:  <div class="alert alert-success">
<b>EXERCISE</b>
Step34:  The default plot (when not specifying kind) is a line plot of all columns
Step35:  This does not say too much ..
We can select part of the data (eg the latest 500 data points)
Step36:  Or we can use some more advanced time series features -> see further in this notebook!
5. Selecting and filtering data
<div class="alert alert-warning">
<b>ATTENTION!</b>
Step37:  df[] provides some convenience shortcuts
For a DataFrame, basic indexing selects the columns.
Selecting a single column
Step38:  or multiple columns
Step39:  But, slicing accesses the rows
Step40:  Systematic indexing with loc and iloc
When using [] like above, you can only select from one axis at once (rows or columns, not both). For more advanced indexing, you have some extra attributes
Step41:  Selecting by position with iloc works similar as indexing numpy arrays
Step42:  The different indexing methods can also be used to assign data
Step43:  Boolean indexing (filtering)
Often, you want to select rows based on a certain condition. This can be done with 'boolean indexing' (like a where clause in SQL) and comparable to numpy. 
The indexer (or boolean mask) should be 1-dimensional and the same length as the thing being indexed.
Step44:  <div class="alert alert-success">
<b>EXERCISE</b>
Step45:  <div class="alert alert-success">
<b>EXERCISE</b>
Step46:  6. The group-by operation
Some 'theory'
Step47:  Recap
Step48:  However, in many cases your data has certain groups in it, and in that case, you may want to calculate this statistic for each of the groups.
For example, in the above dataframe df, there is a column 'key' which has three possible values
Step49:  This becomes very verbose when having multiple groups. You could make the above a bit easier by looping over the different values, but still, it is not very convenient to work with.
What we did above, applying a function on different groups, is a "groupby operation", and pandas provides some convenient functionality for this.
Groupby
Step50:  And many more methods are available.
Step51:  Application of the groupby concept on the titanic data
We go back to the titanic passengers survival data
Step52:  <div class="alert alert-success">
<b>EXERCISE</b>
Step53:  <div class="alert alert-success">
<b>EXERCISE</b>
Step54:  <div class="alert alert-success">
<b>EXERCISE</b>
Step55:  <div class="alert alert-success">
<b>EXERCISE</b>
Step56:  <div class="alert alert-success">
<b>EXERCISE</b>
Step57:  <div class="alert alert-success">
<b>EXERCISE</b>
Step58:  7. Working with time series data
Step59:  When we ensure the DataFrame has a DatetimeIndex, time-series related functionality becomes available
Step60:  Indexing a time series works with strings
Step61:  A nice feature is "partial string" indexing, so you don't need to provide the full datetime string.
E.g. all data of January up to March 2012
Step62:  Time and date components can be accessed from the index
Step63:  Converting your time series with resample
A very powerfull method is resample
Step64:  The time series has a frequency of 1 hour. I want to change this to daily
Step65:  Above I take the mean, but as with groupby I can also specify other methods
Step66:  The string to specify the new time frequency
Step67:  <div class="alert alert-success">
<b>EXERCISE</b>
Step68:  <div class="alert alert-success">
<b>EXERCISE</b>
Step69:  Now, we can calculate the mean of each month over the different years
Step70:  <div class="alert alert-success">
<b>EXERCISE</b>
Step71:  <div class="alert alert-success">
<b>EXERCISE</b>
Step72:  Add a column indicating week/weekend
Step73:  Now we can groupby the hour of the day and the weekend (or use pivot_table)
Step74:  <div class="alert alert-success">
<b>EXERCISE</b>
<ASSISTANT_TASK:>
Python Code:
%matplotlib inline
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
pd.options.display.max_rows = 8
Explanation: <!--<img width=700px; src="../img/logoUPSayPlusCDS_990.png"> -->
<p style="margin-top: 3em; margin-bottom: 2em;"><b><big><big><big><big>Introduction to Pandas</big></big></big></big></b></p>
End of explanation
df = pd.read_csv("data/titanic.csv")
df.head()
Explanation: 1. Let's start with a showcase
Case 1: titanic survival data
End of explanation
df['Age'].hist()
Explanation: Starting from reading this dataset, to answering questions about this data in a few lines of code:
What is the age distribution of the passengers?
End of explanation
df.groupby('Sex')[['Survived']].aggregate(lambda x: x.sum() / len(x))
Explanation: How does the survival rate of the passengers differ between sexes?
End of explanation
df.groupby('Pclass')['Survived'].aggregate(lambda x: x.sum() / len(x)).plot(kind='bar')
Explanation: Or how does it differ between the different classes?
End of explanation
data = pd.read_csv('data/20000101_20161231-NO2.csv', sep=';', skiprows=[1], na_values=['n/d'], index_col=0, parse_dates=True)
data.head()
Explanation: All the needed functionality for the above examples will be explained throughout this tutorial.
Case 2: air quality measurement timeseries
AirBase (The European Air quality dataBase): hourly measurements of all air quality monitoring stations from Europe
Starting from these hourly data for different stations:
End of explanation
data['1999':].resample('M').mean().plot(ylim=[0,120])
data['1999':].resample('A').mean().plot(ylim=[0,100])
Explanation: to answering questions about this data in a few lines of code:
Does the air pollution show a decreasing trend over the years?
End of explanation
data['weekday'] = data.index.weekday
data['weekend'] = data['weekday'].isin([5, 6])
data_weekend = data.groupby(['weekend', data.index.hour])['BASCH'].mean().unstack(level=0)
data_weekend.plot()
Explanation: What is the difference in diurnal profile between weekdays and weekend?
End of explanation
df
Explanation: We will come back to these example, and build them up step by step.
2. Pandas: data analysis in python
For data-intensive work in Python the Pandas library has become essential.
What is pandas?
Pandas can be thought of as NumPy arrays with labels for rows and columns, and better support for heterogeneous data types, but it's also much, much more than that.
Pandas can also be thought of as R's data.frame in Python.
Powerful for working with missing data, working with time series data, for reading and writing your data, for reshaping, grouping, merging your data, ...
It's documentation: http://pandas.pydata.org/pandas-docs/stable/
 When do you need pandas? 
When working with tabular or structured data (like R dataframe, SQL table, Excel spreadsheet, ...):
Import data
Clean up messy data
Explore data, gain insight into data
Process and prepare your data for analysis
Analyse your data (together with scikit-learn, statsmodels, ...)
<div class="alert alert-warning">
<b>ATTENTION!</b>: <br><br>
Pandas is great for working with heterogeneous and tabular 1D/2D data, but not all types of data fit in such structures!
<ul>
<li>When working with array data (e.g. images, numerical algorithms): just stick with numpy</li>
<li>When working with multidimensional labeled data (e.g. climate data): have a look at [xarray](http://xarray.pydata.org/en/stable/)</li>
</ul>
</div>
2. The pandas data structures: DataFrame and Series
A DataFrame is a tablular data structure (multi-dimensional object to hold labeled data) comprised of rows and columns, akin to a spreadsheet, database table, or R's data.frame object. You can think of it as multiple Series object which share the same index.
<img align="left" width=50% src="img/schema-dataframe.svg">
End of explanation
df.index
df.columns
Explanation: Attributes of the DataFrame
A DataFrame has besides a index attribute, also a columns attribute:
End of explanation
df.dtypes
Explanation: To check the data types of the different columns:
End of explanation
df.info()
Explanation: An overview of that information can be given with the info() method:
End of explanation
df.values
Explanation: Also a DataFrame has a values attribute, but attention: when you have heterogeneous data, all values will be upcasted:
End of explanation
data = {'country': ['Belgium', 'France', 'Germany', 'Netherlands', 'United Kingdom'],
        'population': [11.3, 64.3, 81.3, 16.9, 64.9],
        'area': [30510, 671308, 357050, 41526, 244820],
        'capital': ['Brussels', 'Paris', 'Berlin', 'Amsterdam', 'London']}
df_countries = pd.DataFrame(data)
df_countries
Explanation: Apart from importing your data from an external source (text file, excel, database, ..), one of the most common ways of creating a dataframe is from a dictionary of arrays or lists.
Note that in the IPython notebook, the dataframe will display in a rich HTML view:
End of explanation
df['Age']
age = df['Age']
Explanation: One-dimensional data: Series (a column of a DataFrame)
A Series is a basic holder for one-dimensional labeled data.
End of explanation
age.index
Explanation: Attributes of a Series: index and values
The Series has also an index and values attribute, but no columns
End of explanation
age.values[:10]
Explanation: You can access the underlying numpy array representation with the .values attribute:
End of explanation
age[0]
Explanation: We can access series values via the index, just like for NumPy arrays:
End of explanation
df = df.set_index('Name')
df
age = df['Age']
age
age['Dooley, Mr. Patrick']
Explanation: Unlike the NumPy array, though, this index can be something other than integers:
End of explanation
age * 1000
Explanation: but with the power of numpy arrays. Many things you can do with numpy arrays, can also be applied on DataFrames / Series.
Eg element-wise operations:
End of explanation
age.mean()
Explanation: A range of methods:
End of explanation
age[age > 70]
Explanation: Fancy indexing, like indexing with a list or boolean indexing:
End of explanation
df['Embarked'].value_counts()
Explanation: But also a lot of pandas specific methods, e.g.
End of explanation
df['Fare'].max()
df['Fare'].median()
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>What is the maximum Fare that was paid? And the median?</li>
</ul>
</div>
End of explanation
# df['Survived'].sum() / len(df['Survived'])
df['Survived'].mean()
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Calculate the average survival ratio for all passengers (note: the 'Survived' column indicates whether someone survived (1) or not (0)).</li>
</ul>
</div>
End of explanation
#pd.read
#df.to
Explanation: 3. Data import and export
A wide range of input/output formats are natively supported by pandas:
CSV, text
SQL database
Excel
HDF5
json
html
pickle
sas, stata
(parquet)
...
End of explanation
pd.read_csv?
Explanation: Very powerful csv reader:
End of explanation
df = pd.read_csv("data/titanic.csv")
df.head()
Explanation: Luckily, if we have a well formed csv file, we don't need many of those arguments:
End of explanation
no2 = pd.read_csv('data/20000101_20161231-NO2.csv', sep=';', skiprows=[1], na_values=['n/d'], index_col=0, parse_dates=True)
no2
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>: Read the `data/20000101_20161231-NO2.csv` file into a DataFrame `no2`
<br><br>
Some aspects about the file:
 <ul>
  <li>Which separator is used in the file?</li>
  <li>The second row includes unit information and should be skipped (check `skiprows` keyword)</li>
  <li>For missing values, it uses the `'n/d'` notation (check `na_values` keyword)</li>
  <li>We want to parse the 'timestamp' column as datetimes (check the `parse_dates` keyword)</li>
</ul>
</div>
End of explanation
no2.head(3)
no2.tail()
Explanation: 4. Exploration
Some useful methods:
head and tail
End of explanation
no2.info()
Explanation: info()
End of explanation
no2.describe()
Explanation: Getting some basic summary statistics about the data with describe:
End of explanation
no2.plot(kind='box', ylim=[0,250])
no2['BASCH'].plot(kind='hist', bins=50)
Explanation: Quickly visualizing the data
End of explanation
df['Age'].hist()
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>: 
 <ul>
  <li>Plot the age distribution of the titanic passengers</li>
</ul>
</div>
End of explanation
no2.plot(figsize=(12,6))
Explanation: The default plot (when not specifying kind) is a line plot of all columns:
End of explanation
no2[-500:].plot(figsize=(12,6))
Explanation: This does not say too much ..
We can select part of the data (eg the latest 500 data points):
End of explanation
df = pd.read_csv("data/titanic.csv")
Explanation: Or we can use some more advanced time series features -> see further in this notebook!
5. Selecting and filtering data
<div class="alert alert-warning">
<b>ATTENTION!</b>: <br><br>
One of pandas' basic features is the labeling of rows and columns, but this makes indexing also a bit more complex compared to numpy. <br><br> We now have to distuinguish between:
 <ul>
  <li>selection by **label**</li>
  <li>selection by **position**</li>
</ul>
</div>
End of explanation
df['Age']
Explanation: df[] provides some convenience shortcuts
For a DataFrame, basic indexing selects the columns.
Selecting a single column:
End of explanation
df[['Age', 'Fare']]
Explanation: or multiple columns:
End of explanation
df[10:15]
Explanation: But, slicing accesses the rows:
End of explanation
df = df.set_index('Name')
df.loc['Bonnell, Miss. Elizabeth', 'Fare']
df.loc['Bonnell, Miss. Elizabeth':'Andersson, Mr. Anders Johan', :]
Explanation: Systematic indexing with loc and iloc
When using [] like above, you can only select from one axis at once (rows or columns, not both). For more advanced indexing, you have some extra attributes:
loc: selection by label
iloc: selection by position
End of explanation
df.iloc[0:2,1:3]
Explanation: Selecting by position with iloc works similar as indexing numpy arrays:
End of explanation
df.loc['Braund, Mr. Owen Harris', 'Survived'] = 100
df
Explanation: The different indexing methods can also be used to assign data:
End of explanation
df['Fare'] > 50
df[df['Fare'] > 50]
Explanation: Boolean indexing (filtering)
Often, you want to select rows based on a certain condition. This can be done with 'boolean indexing' (like a where clause in SQL) and comparable to numpy. 
The indexer (or boolean mask) should be 1-dimensional and the same length as the thing being indexed.
End of explanation
df = pd.read_csv("data/titanic.csv")
df[df['Sex'] == 'male']
df.loc[df['Sex'] == 'male', 'Age'].mean()
df.loc[df['Sex'] == 'female', 'Age'].mean()
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Based on the titanic data set, select all rows for male passengers and calculate the mean age of those passengers. Do the same for the female passengers</li>
</ul>
</div>
End of explanation
len(df[df['Age'] > 70])
(df['Age'] > 70).sum()
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Based on the titanic data set, how many passengers older than 70 were on the Titanic?</li>
</ul>
</div>
End of explanation
df = pd.DataFrame({'key':['A','B','C','A','B','C','A','B','C'],
                   'data': [0, 5, 10, 5, 10, 15, 10, 15, 20]})
df
Explanation: 6. The group-by operation
Some 'theory': the groupby operation (split-apply-combine)
End of explanation
df['data'].sum()
Explanation: Recap: aggregating functions
When analyzing data, you often calculate summary statistics (aggregations like the mean, max, ...). As we have seen before, we can easily calculate such a statistic for a Series or column using one of the many available methods. For example:
End of explanation
for key in ['A', 'B', 'C']:
    print(key, df[df['key'] == key]['data'].sum())
Explanation: However, in many cases your data has certain groups in it, and in that case, you may want to calculate this statistic for each of the groups.
For example, in the above dataframe df, there is a column 'key' which has three possible values: 'A', 'B' and 'C'. When we want to calculate the sum for each of those groups, we could do the following:
End of explanation
df.groupby('key').sum()
df.groupby('key').aggregate(np.sum)  # 'sum'
Explanation: This becomes very verbose when having multiple groups. You could make the above a bit easier by looping over the different values, but still, it is not very convenient to work with.
What we did above, applying a function on different groups, is a "groupby operation", and pandas provides some convenient functionality for this.
Groupby: applying functions per group
The "group by" concept: we want to apply the same function on subsets of your dataframe, based on some key to split the dataframe in subsets
This operation is also referred to as the "split-apply-combine" operation, involving the following steps:
Splitting the data into groups based on some criteria
Applying a function to each group independently
Combining the results into a data structure
<img src="img/splitApplyCombine.png">
Similar to SQL GROUP BY
Instead of doing the manual filtering as above
df[df['key'] == "A"].sum()
df[df['key'] == "B"].sum()
...
pandas provides the groupby method to do exactly this:
End of explanation
df.groupby('key')['data'].sum()
Explanation: And many more methods are available.
End of explanation
df = pd.read_csv("data/titanic.csv")
df.head()
Explanation: Application of the groupby concept on the titanic data
We go back to the titanic passengers survival data:
End of explanation
df.groupby('Sex')['Age'].mean()
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Calculate the average age for each sex again, but now using groupby.</li>
</ul>
</div>
End of explanation
# df['Survived'].sum() / len(df['Survived'])
df['Survived'].mean()
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Calculate the average survival ratio for all passengers.</li>
</ul>
</div>
End of explanation
df25 = df[df['Age'] <= 25]
df25['Survived'].sum() / len(df25['Survived'])
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Calculate this survival ratio for all passengers younger that 25 (remember: filtering/boolean indexing).</li>
</ul>
</div>
End of explanation
# df.groupby('Sex')[['Survived']].aggregate(lambda x: x.sum() / len(x))
df.groupby('Sex')[['Survived']].mean()
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>What is the difference in the survival ratio between the sexes?</li>
</ul>
</div>
End of explanation
df.groupby('Pclass')['Survived'].mean().plot(kind='bar')
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Or how does it differ between the different classes? Make a bar plot visualizing the survival ratio for the 3 classes.</li>
</ul>
</div>
End of explanation
df['AgeClass'] = pd.cut(df['Age'], bins=np.arange(0,90,10))
df.groupby('AgeClass')['Fare'].mean().plot(kind='bar', rot=0)
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Make a bar plot to visualize the average Fare payed by people depending on their age. The age column is devided is separate classes using the `pd.cut` function as provided below.</li>
</ul>
</div>
End of explanation
no2 = pd.read_csv('data/20000101_20161231-NO2.csv', sep=';', skiprows=[1], na_values=['n/d'], index_col=0, parse_dates=True)
Explanation: 7. Working with time series data
End of explanation
no2.index
Explanation: When we ensure the DataFrame has a DatetimeIndex, time-series related functionality becomes available:
End of explanation
no2["2010-01-01 09:00": "2010-01-01 12:00"]
Explanation: Indexing a time series works with strings:
End of explanation
no2['2012-01':'2012-03']
Explanation: A nice feature is "partial string" indexing, so you don't need to provide the full datetime string.
E.g. all data of January up to March 2012:
End of explanation
no2.index.hour
no2.index.year
Explanation: Time and date components can be accessed from the index:
End of explanation
no2.plot()
Explanation: Converting your time series with resample
A very powerfull method is resample: converting the frequency of the time series (e.g. from hourly to daily data).
Remember the air quality data:
End of explanation
no2.head()
no2.resample('D').mean().head()
Explanation: The time series has a frequency of 1 hour. I want to change this to daily:
End of explanation
no2.resample('D').max().head()
Explanation: Above I take the mean, but as with groupby I can also specify other methods:
End of explanation
no2.resample('M').mean().plot() # 'A'
# no2['2012'].resample('D').plot()
no2.loc['2009':, 'VERS'].resample('M').agg(['mean', 'median']).plot()
Explanation: The string to specify the new time frequency: http://pandas.pydata.org/pandas-docs/dev/timeseries.html#offset-aliases
These strings can also be combined with numbers, eg '10D'.
Further exploring the data:
End of explanation
no2.resample('A').mean().plot()
no2.mean(axis=1).resample('A').mean().plot(color='k', linestyle='--', linewidth=4)
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>: The evolution of the yearly averages with, and the overall mean of all stations
 <ul>
  <li>Use `resample` and `plot` to plot the yearly averages for the different stations.</li>
  <li>The overall mean of all stations can be calculated by taking the mean of the different columns (`.mean(axis=1)`).</li>
</ul>
</div>
End of explanation
no2['month'] = no2.index.month
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>: how does the *typical monthly profile* look like for the different stations?
 <ul>
  <li>Add a 'month' column to the dataframe.</li>
  <li>Group by the month to obtain the typical monthly averages over the different years.</li>
</ul>
</div>
First, we add a column to the dataframe that indicates the month (integer value of 1 to 12):
End of explanation
no2.groupby('month').mean()
no2.groupby('month').mean().plot()
Explanation: Now, we can calculate the mean of each month over the different years:
End of explanation
no2.groupby(no2.index.hour).mean().plot()
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>: The typical diurnal profile for the different stations
 <ul>
  <li>Similar as for the month, you can now group by the hour of the day.</li>
</ul>
</div>
End of explanation
no2.index.weekday?
no2['weekday'] = no2.index.weekday
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>: What is the difference in the typical diurnal profile between week and weekend days for the 'BASCH' station.
 <ul>
  <li>Add a column 'weekday' defining the different days in the week.</li>
  <li>Add a column 'weekend' defining if a days is in the weekend (i.e. days 5 and 6) or not (True/False).</li>
  <li>You can groupby on multiple items at the same time. In this case you would need to group by both weekend/weekday and hour of the day.</li>
</ul>
</div>
Add a column indicating the weekday:
End of explanation
no2['weekend'] = no2['weekday'].isin([5, 6])
Explanation: Add a column indicating week/weekend
End of explanation
data_weekend = no2.groupby(['weekend', no2.index.hour]).mean()
data_weekend.head()
data_weekend_BASCH = data_weekend['BASCH'].unstack(level=0)
data_weekend_BASCH.head()
data_weekend_BASCH.plot()
no2['hour'] = no2.index.hour
no2.pivot_table(columns='weekend', index='hour', values='BASCH')
Explanation: Now we can groupby the hour of the day and the weekend (or use pivot_table):
End of explanation
# re-reading the data to have a clean version
no2 = pd.read_csv('data/20000101_20161231-NO2.csv', sep=';', skiprows=[1], na_values=['n/d'], index_col=0, parse_dates=True)
exceedances = no2 > 200
# group by year and count exceedances (sum of boolean)
exceedances = exceedances.groupby(exceedances.index.year).sum()
ax = exceedances.loc[2005:].plot(kind='bar')
ax.axhline(18, color='k', linestyle='--')
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>: What are the number of exceedances of hourly values above the European limit 200 µg/m3 ?
Count the number of exceedances of hourly values above the European limit 200 µg/m3 for each year and station after 2005. Make a barplot of the counts. Add an horizontal line indicating the maximum number of exceedances (which is 18) allowed per year?
<br><br>
Hints:
 <ul>
  <li>Create a new DataFrame, called `exceedances`, (with boolean values) indicating if the threshold is exceeded or not</li>
  <li>Remember that the sum of True values can be used to count elements. Do this using groupby for each year.</li>
  <li>Adding a horizontal line can be done with the matplotlib function `ax.axhline`.</li>
</ul>
</div>
End of explanation
<END_TASK>