Introduction to Pandas
In [1]:
%matplotlib inline
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
pd.options.display.max_rows = 8
In [2]:
df = pd.read_csv("data/titanic.csv")
In [3]:
df.head()
Out[3]:
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?
In [4]:
df['Age'].hist()
Out[4]:
How does the survival rate of the passengers differ between sexes?
In [5]:
df.groupby('Sex')[['Survived']].aggregate(lambda x: x.sum() / len(x))
Out[5]:
Or how does it differ between the different classes?
In [6]:
df.groupby('Pclass')['Survived'].aggregate(lambda x: x.sum() / len(x)).plot(kind='bar')
Out[6]:
All the needed functionality for the above examples will be explained throughout this tutorial.
AirBase (The European Air quality dataBase): hourly measurements of all air quality monitoring stations from Europe
Starting from these hourly data for different stations:
In [7]:
data = pd.read_csv('data/20000101_20161231-NO2.csv', sep=';', skiprows=[1], na_values=['n/d'], index_col=0, parse_dates=True)
In [8]:
data.head()
Out[8]:
to answering questions about this data in a few lines of code:
Does the air pollution show a decreasing trend over the years?
In [9]:
data['1999':].resample('M').mean().plot(ylim=[0,120])
Out[9]:
In [10]:
data['1999':].resample('A').mean().plot(ylim=[0,100])
Out[10]:
What is the difference in diurnal profile between weekdays and weekend?
In [11]:
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()
Out[11]:
We will come back to these example, and build them up step by step.
For data-intensive work in Python the Pandas library has become essential.
What is pandas?
R's data.frame in Python.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, ...):
DataFrame and SeriesA 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.
In [12]:
df
Out[12]:
In [13]:
df.index
Out[13]:
In [14]:
df.columns
Out[14]:
To check the data types of the different columns:
In [15]:
df.dtypes
Out[15]:
An overview of that information can be given with the info() method:
In [16]:
df.info()
Also a DataFrame has a values attribute, but attention: when you have heterogeneous data, all values will be upcasted:
In [17]:
df.values
Out[17]:
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:
In [18]:
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
Out[18]:
In [19]:
df['Age']
Out[19]:
In [20]:
age = df['Age']
In [21]:
age.index
Out[21]:
You can access the underlying numpy array representation with the .values attribute:
In [22]:
age.values[:10]
Out[22]:
We can access series values via the index, just like for NumPy arrays:
In [23]:
age[0]
Out[23]:
Unlike the NumPy array, though, this index can be something other than integers:
In [24]:
df = df.set_index('Name')
df
Out[24]:
In [25]:
age = df['Age']
age
Out[25]:
In [26]:
age['Dooley, Mr. Patrick']
Out[26]:
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:
In [27]:
age * 1000
Out[27]:
A range of methods:
In [28]:
age.mean()
Out[28]:
Fancy indexing, like indexing with a list or boolean indexing:
In [29]:
age[age > 70]
Out[29]:
But also a lot of pandas specific methods, e.g.
In [30]:
df['Embarked'].value_counts()
Out[30]:
In [31]:
df['Fare'].max()
Out[31]:
In [32]:
df['Fare'].median()
Out[32]:
In [33]:
# df['Survived'].sum() / len(df['Survived'])
df['Survived'].mean()
Out[33]:
A wide range of input/output formats are natively supported by pandas:
In [34]:
#pd.read
In [35]:
#df.to
Very powerful csv reader:
In [36]:
pd.read_csv?
Luckily, if we have a well formed csv file, we don't need many of those arguments:
In [37]:
df = pd.read_csv("data/titanic.csv")
In [38]:
df.head()
Out[38]:
In [39]:
no2 = pd.read_csv('data/20000101_20161231-NO2.csv', sep=';', skiprows=[1], na_values=['n/d'], index_col=0, parse_dates=True)
In [40]:
no2
Out[40]:
Some useful methods:
head and tail
In [41]:
no2.head(3)
Out[41]:
In [42]:
no2.tail()
Out[42]:
info()
In [43]:
no2.info()
Getting some basic summary statistics about the data with describe:
In [44]:
no2.describe()
Out[44]:
Quickly visualizing the data
In [45]:
no2.plot(kind='box', ylim=[0,250])
Out[45]:
In [46]:
no2['BASCH'].plot(kind='hist', bins=50)
Out[46]:
In [47]:
df['Age'].hist()
Out[47]:
The default plot (when not specifying kind) is a line plot of all columns:
In [48]:
no2.plot(figsize=(12,6))
Out[48]:
This does not say too much ..
We can select part of the data (eg the latest 500 data points):
In [49]:
no2[-500:].plot(figsize=(12,6))
Out[49]:
Or we can use some more advanced time series features -> see further in this notebook!
In [50]:
df = pd.read_csv("data/titanic.csv")
For a DataFrame, basic indexing selects the columns.
Selecting a single column:
In [51]:
df['Age']
Out[51]:
or multiple columns:
In [52]:
df[['Age', 'Fare']]
Out[52]:
But, slicing accesses the rows:
In [53]:
df[10:15]
Out[53]:
In [54]:
df = df.set_index('Name')
In [55]:
df.loc['Bonnell, Miss. Elizabeth', 'Fare']
Out[55]:
In [56]:
df.loc['Bonnell, Miss. Elizabeth':'Andersson, Mr. Anders Johan', :]
Out[56]:
Selecting by position with iloc works similar as indexing numpy arrays:
In [57]:
df.iloc[0:2,1:3]
Out[57]:
The different indexing methods can also be used to assign data:
In [59]:
df.loc['Braund, Mr. Owen Harris', 'Survived'] = 100
In [60]:
df
Out[60]:
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.
In [61]:
df['Fare'] > 50
Out[61]:
In [62]:
df[df['Fare'] > 50]
Out[62]:
In [63]:
df = pd.read_csv("data/titanic.csv")
In [64]:
df[df['Sex'] == 'male']
Out[64]:
In [65]:
df.loc[df['Sex'] == 'male', 'Age'].mean()
Out[65]:
In [66]:
df.loc[df['Sex'] == 'female', 'Age'].mean()
Out[66]:
In [67]:
len(df[df['Age'] > 70])
Out[67]:
In [68]:
(df['Age'] > 70).sum()
Out[68]:
In [69]:
df = pd.DataFrame({'key':['A','B','C','A','B','C','A','B','C'],
'data': [0, 5, 10, 5, 10, 15, 10, 15, 20]})
df
Out[69]:
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:
In [70]:
df['data'].sum()
Out[70]:
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:
In [71]:
for key in ['A', 'B', 'C']:
print(key, df[df['key'] == key]['data'].sum())
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.
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:
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:
In [72]:
df.groupby('key').sum()
Out[72]:
In [73]:
df.groupby('key').aggregate(np.sum) # 'sum'
Out[73]:
And many more methods are available.
In [74]:
df.groupby('key')['data'].sum()
Out[74]:
We go back to the titanic passengers survival data:
In [75]:
df = pd.read_csv("data/titanic.csv")
In [76]:
df.head()
Out[76]:
In [77]:
df.groupby('Sex')['Age'].mean()
Out[77]:
In [78]:
# df['Survived'].sum() / len(df['Survived'])
df['Survived'].mean()
Out[78]:
In [79]:
df25 = df[df['Age'] <= 25]
df25['Survived'].sum() / len(df25['Survived'])
Out[79]:
In [80]:
# df.groupby('Sex')[['Survived']].aggregate(lambda x: x.sum() / len(x))
df.groupby('Sex')[['Survived']].mean()
Out[80]:
In [81]:
df.groupby('Pclass')['Survived'].mean().plot(kind='bar')
Out[81]:
In [82]:
df['AgeClass'] = pd.cut(df['Age'], bins=np.arange(0,90,10))
In [83]:
df.groupby('AgeClass')['Fare'].mean().plot(kind='bar', rot=0)
Out[83]:
In [84]:
no2 = pd.read_csv('data/20000101_20161231-NO2.csv', sep=';', skiprows=[1], na_values=['n/d'], index_col=0, parse_dates=True)
When we ensure the DataFrame has a DatetimeIndex, time-series related functionality becomes available:
In [85]:
no2.index
Out[85]:
Indexing a time series works with strings:
In [86]:
no2["2010-01-01 09:00": "2010-01-01 12:00"]
Out[86]:
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:
In [87]:
no2['2012-01':'2012-03']
Out[87]:
Time and date components can be accessed from the index:
In [88]:
no2.index.hour
Out[88]:
In [89]:
no2.index.year
Out[89]:
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:
In [90]:
no2.plot()
Out[90]:
The time series has a frequency of 1 hour. I want to change this to daily:
In [91]:
no2.head()
Out[91]:
In [92]:
no2.resample('D').mean().head()
Out[92]:
Above I take the mean, but as with groupby I can also specify other methods:
In [93]:
no2.resample('D').max().head()
Out[93]:
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:
In [94]:
no2.resample('M').mean().plot() # 'A'
Out[94]:
In [95]:
# no2['2012'].resample('D').plot()
In [96]:
no2.loc['2009':, 'VERS'].resample('M').agg(['mean', 'median']).plot()
Out[96]:
In [97]:
no2.resample('A').mean().plot()
no2.mean(axis=1).resample('A').mean().plot(color='k', linestyle='--', linewidth=4)
Out[97]:
First, we add a column to the dataframe that indicates the month (integer value of 1 to 12):
In [98]:
no2['month'] = no2.index.month
Now, we can calculate the mean of each month over the different years:
In [99]:
no2.groupby('month').mean()
Out[99]:
In [100]:
no2.groupby('month').mean().plot()
Out[100]:
In [101]:
no2.groupby(no2.index.hour).mean().plot()
Out[101]:
Add a column indicating the weekday:
In [102]:
no2.index.weekday?
In [103]:
no2['weekday'] = no2.index.weekday
Add a column indicating week/weekend
In [104]:
no2['weekend'] = no2['weekday'].isin([5, 6])
Now we can groupby the hour of the day and the weekend (or use pivot_table):
In [105]:
data_weekend = no2.groupby(['weekend', no2.index.hour]).mean()
data_weekend.head()
Out[105]:
In [106]:
data_weekend_BASCH = data_weekend['BASCH'].unstack(level=0)
data_weekend_BASCH.head()
Out[106]:
In [107]:
data_weekend_BASCH.plot()
Out[107]:
In [108]:
no2['hour'] = no2.index.hour
no2.pivot_table(columns='weekend', index='hour', values='BASCH')
Out[108]:
In [109]:
exceedances = no2 > 200
In [110]:
# group by year and count exceedances (sum of boolean)
exceedances = exceedances.groupby(exceedances.index.year).sum()
In [111]:
ax = exceedances.loc[2005:].plot(kind='bar')
ax.axhline(18, color='k', linestyle='--')
Out[111]:
pd.concatpd.mergepivot_table, melt, stack, unstackisnull, dropna, interpolate, ...Pandas documentation: http://pandas.pydata.org/pandas-docs/stable/
Books
Tutorials (many good online tutorials!)
Tom Augspurger's blog