Overview

Teaching: 10 min
Exercises: 0 min

Questions

• How do I maninpulate tabular data in Python?

Objectives

• Arrive at the HackWeek with a basic overview of the Pandas library for tabular data manipulation.

Data - an introduction to the world of Pandas

Note: This is an edited version of Cliburn Chan’s original tutorial, as part of his Stat-663 course at Duke. All changes remain licensed as the original, under the terms of the MIT license.

Additionally, sections have been merged from Chris Fonnesbeck’s Pandas tutorial from the NGCM Summer Academy, which are licensed under CC0 terms (aka ‘public domain’).

Resources

• The Introduction to Pandas chapter in the Python for Data Analysis book by Wes McKinney is essential reading for this topic. This is the companion notebook for that chapter.
• Pandas documentation
• QGrid

Pandas

pandas is a Python package providing fast, flexible, and expressive data structures designed to work with relational or labeled data both. It is a fundamental high-level building block for doing practical, real world data analysis in Python.

pandas is well suited for:

• Tabular data with heterogeneously-typed columns, as you might find in an SQL table or Excel spreadsheet
• Ordered and unordered (not necessarily fixed-frequency) time series data.
• Arbitrary matrix data with row and column labels

Virtually any statistical dataset, labeled or unlabeled, can be converted to a pandas data structure for cleaning, transformation, and analysis.

Key features

• Easy handling of missing data
• Size mutability: columns can be inserted and deleted from DataFrame and higher dimensional objects
• Automatic and explicit data alignment: objects can be explicitly aligned to a set of labels, or the data can be aligned automatically
• Powerful, flexible group by functionality to perform split-apply-combine operations on data sets
• Intelligent label-based slicing, fancy indexing, and subsetting of large data sets
• Intuitive merging and joining data sets
• Flexible reshaping and pivoting of data sets
• Hierarchical labeling of axes
• Robust IO tools for loading data from flat files, Excel files, databases, and HDF5
• Time series functionality: date range generation and frequency conversion, moving window statistics, moving window linear regressions, date shifting and lagging, etc.

%matplotlib inline
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd

from pandas import Series, DataFrame

plt.style.use('seaborn-dark')

Working with Series

• A pandas Series is a generationalization of 1d numpy array
• A series has an index that labels each element in the vector.
• A Series can be thought of as an ordered key-value store.

np.array(range(5,10))

array([5, 6, 7, 8, 9])

x = Series(range(5,10))

x

0    5
1    6
2    7
3    8
4    9
dtype: int64

We can treat Series objects much like numpy vectors

x.sum(), x.mean(), x.std()

(35, 7.0, 1.5811388300841898)

x**2

0    25
1    36
2    49
3    64
4    81
dtype: int64

x[x >= 8]

3    8
4    9
dtype: int64

Series can also contain more information than numpy vectors

You can always use standard positional indexing

x[1:4]

1    6
2    7
3    8
dtype: int64

Series index

But you can also assign labeled indexes.

x.index = list('abcde')
x

a    5
b    6
c    7
d    8
e    9
dtype: int64

Note that with labels, the end index is included

x['b':'d']

b    6
c    7
d    8
dtype: int64

Even when you have a labeled index, positional arguments still work

x[1:4]

b    6
c    7
d    8
dtype: int64

Working with missing data

Missing data is indicated with NaN (not a number).

y = Series([10, np.nan, np.nan, 13, 14])
y

0    10.0
1     NaN
2     NaN
3    13.0
4    14.0
dtype: float64

Concatenating two series

z = pd.concat([x, y])
z

a     5.0
b     6.0
c     7.0
d     8.0
e     9.0
0    10.0
1     NaN
2     NaN
3    13.0
4    14.0
dtype: float64

Reset index to default

z = z.reset_index(drop=True)
z

0     5.0
1     6.0
2     7.0
3     8.0
4     9.0
5    10.0
6     NaN
7     NaN
8    13.0
9    14.0
dtype: float64

z**2

0     25.0
1     36.0
2     49.0
3     64.0
4     81.0
5    100.0
6      NaN
7      NaN
8    169.0
9    196.0
dtype: float64

pandas aggregate functions ignore missing data

z.sum(), z.mean(), z.std()

(72.0, 9.0, 3.2071349029490928)

Selecting missing values

z[z.isnull()]

6   NaN
7   NaN
dtype: float64

Selecting non-missing values

z[z.notnull()]

0     5.0
1     6.0
2     7.0
3     8.0
4     9.0
5    10.0
8    13.0
9    14.0
dtype: float64

Replacement of missing values

z.fillna(0)

0     5.0
1     6.0
2     7.0
3     8.0
4     9.0
5    10.0
6     0.0
7     0.0
8    13.0
9    14.0
dtype: float64

z.fillna(method='ffill')

0     5.0
1     6.0
2     7.0
3     8.0
4     9.0
5    10.0
6    10.0
7    10.0
8    13.0
9    14.0
dtype: float64

z.fillna(method='bfill')

0     5.0
1     6.0
2     7.0
3     8.0
4     9.0
5    10.0
6    13.0
7    13.0
8    13.0
9    14.0
dtype: float64

z.fillna(z.mean())

0     5.0
1     6.0
2     7.0
3     8.0
4     9.0
5    10.0
6     9.0
7     9.0
8    13.0
9    14.0
dtype: float64

Working with dates / times

We will see more date/time handling in the DataFrame section.

z.index = pd.date_range('01-Jan-2016', periods=len(z))

z

2016-01-01     5.0
2016-01-02     6.0
2016-01-03     7.0
2016-01-04     8.0
2016-01-05     9.0
2016-01-06    10.0
2016-01-07     NaN
2016-01-08     NaN
2016-01-09    13.0
2016-01-10    14.0
Freq: D, dtype: float64

Intelligent aggregation over datetime ranges

z.resample('W').sum()

2016-01-03    18.0
2016-01-10    54.0
Freq: W-SUN, dtype: float64

Formatting datetime objects (see http://strftime.org)

z.index.strftime('%b %d, %Y')

array(['Jan 01, 2016', 'Jan 02, 2016', 'Jan 03, 2016', 'Jan 04, 2016',
       'Jan 05, 2016', 'Jan 06, 2016', 'Jan 07, 2016', 'Jan 08, 2016',
       'Jan 09, 2016', 'Jan 10, 2016'],
      dtype='<U12')

DataFrames

Inevitably, we want to be able to store, view and manipulate data that is multivariate, where for every index there are multiple fields or columns of data, often of varying data type.

A DataFrame is a tabular data structure, encapsulating multiple series like columns in a spreadsheet. It is directly inspired by the R DataFrame.

Titanic data

url = 'https://raw.githubusercontent.com/mwaskom/seaborn-data/master/titanic.csv'
titanic = pd.read_csv(url)
titanic.head()

titanic.shape

(891, 15)

titanic.size

13365

titanic.columns

Index(['survived', 'pclass', 'sex', 'age', 'sibsp', 'parch', 'fare',
       'embarked', 'class', 'who', 'adult_male', 'deck', 'embark_town',
       'alive', 'alone'],
      dtype='object')

# For display purposes, we will drop some columns
titanic = titanic[['survived', 'sex', 'age', 'fare',
                   'embarked', 'class', 'who', 'deck', 'embark_town',]]

titanic.dtypes

survived         int64
sex             object
age            float64
fare           float64
embarked        object
class           object
who             object
deck            object
embark_town     object
dtype: object

Summarizing a data frame

titanic.describe()

titanic.head(20)

titanic.tail(5)

titanic.columns

Index(['survived', 'sex', 'age', 'fare', 'embarked', 'class', 'who', 'deck',
       'embark_town'],
      dtype='object')

titanic.index

RangeIndex(start=0, stop=891, step=1)

Indexing

The default indexing mode for dataframes with df[X] is to access the DataFrame’s columns:

titanic[['sex', 'age', 'class']].head()

Using the iloc helper for indexing

titanic.head(3)

titanic.iloc

<pandas.core.indexing._iLocIndexer at 0x106cecb70>

titanic.iloc[0]

survived                 0
sex                   male
age                     22
fare                  7.25
embarked                 S
class                Third
who                    man
deck                   NaN
embark_town    Southampton
Name: 0, dtype: object

titanic.iloc[0:5]

titanic.iloc[ [0, 10, 1, 5] ]

titanic.iloc[10:15]['age']

10     4.0
11    58.0
12    20.0
13    39.0
14    14.0
Name: age, dtype: float64

titanic.iloc[10:15][  ['age'] ]

titanic[titanic. < 2]

  File "<ipython-input-47-0c88d841b460>", line 1
    titanic[titanic. < 2]
                     ^
SyntaxError: invalid syntax

titanic["new column"] = 0

titanic["new column"][:10]

0    0
1    0
2    0
3    0
4    0
5    0
6    0
7    0
8    0
9    0
Name: new column, dtype: int64

titanic[titanic.age < 2].index

Int64Index([78, 164, 172, 183, 305, 381, 386, 469, 644, 755, 788, 803, 827,
            831],
           dtype='int64')

df = pd.DataFrame(dict(name=['Alice', 'Bob'], age=[20, 30]), 
                  columns = ['name', 'age'],  # enforce column order
                  index=pd.Series([123, 989], name='id'))
df

.iloc vs .loc

These are two accessors with a key difference:

• .iloc indexes positionally
• .loc indexes by label

#df[0]  # error
#df[123] # error
df.iloc[0]

name    Alice
age        20
Name: 123, dtype: object

df.loc[123]

name    Alice
age        20
Name: 123, dtype: object

df.loc[ [123] ]

Sorting and ordering data

titanic.head()

The sort_index method is designed to sort a DataFrame by either its index or its columns:

titanic.sort_index(ascending=False).head()

Since the Titanic index is already sorted, it’s easier to illustrate how to use it for the index with a small test DF:

df = pd.DataFrame([1, 2, 3, 4, 5], index=[100, 29, 234, 1, 150], columns=['A'])
df

df.sort_index() # same as df.sort_index('index')

Pandas also makes it easy to sort on the values of the DF:

titanic.sort_values('age', ascending=True).head()

And we can sort on more than one column in a single call:

titanic.sort_values(['survived', 'age'], ascending=[True, True]).head()

Note: both the index and the columns can be named:

t = titanic.sort_values(['survived', 'age'], ascending=[True, False])
t.index.name = 'id'
t.columns.name = 'attributes'
t.head()

Grouping data

sex_class = titanic.groupby(['sex', 'class'])

What is a GroubBy object?

sex_class

<pandas.core.groupby.DataFrameGroupBy object at 0x101c3b860>

from IPython.display import display

for name, group in sex_class:
    print('name:', name, '\ngroup:\n')
    display(group.head(2))

name: ('female', 'First') 
group:

name: ('female', 'Second') 
group:

name: ('female', 'Third') 
group:

name: ('male', 'First') 
group:

name: ('male', 'Second') 
group:

name: ('male', 'Third') 
group:

sex_class.get_group(('female', 'Second')).head()

The GroubBy object has a number of aggregation methods that will then compute summary statistics over the group members, e.g.:

sex_class.count()

Why Kate Winslett survived and Leonardo DiCaprio didn’t

sex_class.mean()[['survived']]

Of the females who were in first class, count the number from each embarking town

sex_class.get_group(('female', 'First')).groupby('embark_town').count()

Since count counts non-missing data, we’re really interested in the maximum value for each row, which we can obtain directly:

sex_class.get_group(('female', 'First')).groupby('embark_town').count().max('columns')

embark_town
Cherbourg      43
Queenstown      1
Southampton    48
dtype: int64

Cross-tabulation

pd.crosstab(titanic.survived, titanic['class'])

We can also get multiple summaries at the same time

The agg method is the most flexible, as it allows us to specify directly which functions we want to call, and where:

def my_func(x):
    return np.max(x)

mapped_funcs = {'embarked': 'count', 
                'age': ('mean', 'median', my_func), 
                'survived': sum}

sex_class.get_group(('female', 'First')).groupby('embark_town').agg(mapped_funcs)

Making plots with pandas

Note: you may need to run

pip install pandas-datareader

to install the specialized readers.

from pandas_datareader import data as web
import datetime

try:
    apple = pd.read_csv('data/apple.csv', index_col=0, parse_dates=True)
except:
    apple = web.DataReader('AAPL', 'yahoo', 
                            start = datetime.datetime(2015, 1, 1),
                            end = datetime.datetime(2015, 12, 31))
    # Let's save this data to a CSV file so we don't need to re-download it on every run:
    apple.to_csv('data/apple.csv')

apple.head()

apple.tail()

Let’s save this data to a CSV file so we don’t need to re-download it on every run:

f, ax = plt.subplots()
apple.plot.line(y='Close', marker='o', markersize=3, linewidth=0.5, ax=ax);

f.suptitle("Apple stock in 2015")
f

# Zoom in on large drop in August
aug = apple['2015-08-01':'2015-08-30']
aug.head()

aug.plot.line(y=['High', 'Low', 'Open', 'Close'], marker='o', markersize=10, linewidth=1);

/Users/fperez/usr/conda/envs/s159/lib/python3.6/site-packages/pandas/plotting/_core.py:1714: UserWarning: Pandas doesn't allow columns to be created via a new attribute name - see https://pandas.pydata.org/pandas-docs/stable/indexing.html#attribute-access
  series.name = label

Data conversions

One of the nicest features of pandas is the ease of converting tabular data across different storage formats. We will illustrate by converting the titanic dataframe into multiple formats.

CSV

titanic.to_csv('titanic.csv', index=False)

t1 = pd.read_csv('titanic.csv')
t1.head(2)

Excel

You may need to first install openpyxl:

pip install openpyxl

t1.to_excel('titanic.xlsx')

t2 = pd.read_excel('titanic.xlsx')
t2.head(2)

Relational Database

import sqlite3

con = sqlite3.connect('titanic.db')
t2.to_sql('titanic', con, index=False, if_exists='replace')

/Users/fperez/usr/conda/envs/s159/lib/python3.6/site-packages/pandas/core/generic.py:1534: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.
  chunksize=chunksize, dtype=dtype)

t3 = pd.read_sql('select * from titanic', con)
t3.head(2)

JSON

t3.to_json('titanic.json')

t4 = pd.read_json('titanic.json')
t4.head(2)

t4 = t4[t3.columns]
t4.head(2)

HDF5

The HDF5 format was designed in the Earth Sciences community but it can be an excellent general purpose tool. It’s efficient and type-safe, so you can store complex dataframes in it and recover them back without information loss, using the to_hdf method:

t4.to_hdf('titanic.h5', 'titanic')

/Users/fperez/usr/conda/envs/s159/lib/python3.6/site-packages/pandas/core/generic.py:1471: PerformanceWarning: 
your performance may suffer as PyTables will pickle object types that it cannot
map directly to c-types [inferred_type->mixed,key->block1_values] [items->['sex', 'embarked', 'class', 'who', 'deck', 'embark_town']]

  return pytables.to_hdf(path_or_buf, key, self, **kwargs)

t5 = pd.read_hdf('titanic.h5', 'titanic')
t5.head(2)

Feather

You may need to install the Feather support first:

conda install -c conda-forge feather-format

t6 = t5.reset_index(drop=True)
t6.head()

t6.to_feather('titanic.feather')

t7 = pd.read_feather('titanic.feather')
t7.head()

Key Points

• With Pandas, Python provides an excellent environment for the analysis of tabular data.