1 Reading and Writing
Data with Pandas
Functions to read data are all named pd.read_* where
* is the file type. Series and DataFrames can be saved to
disk using their to_* method.
Usage Patterns
Use pd.read_clipboard() for one-off data extractions.
Use the other pd.read_* methods in scripts for repeatable
analyses.
Reading Text Files into a DataFrame
Colors highlight how different arguments map from the data file
to a DataFrame.
Other arguments: Possible values of parse_dates:
• names: Set or override column names [0, 2]: Parse columns 0 and 2 as separate dates
• parse_dates: Accepts multiple argument types [[0, 2]]: Group columns 0 and 2 and parse as single date
• converters: Manually process each element in a column {'Date': [0, 2]}: Group columns 0 and 2, parse as single date
• comment: Character indicating commented line in a column named Date
• chunksize: Read only a certain number of rows each time Dates are parsed after the converters have been applied.
Parsing Tables from the Web
Writing Data Structures to Disk Writing Data Structures from and to a Database
Write data structures to disk: Read, using SQLAlchemy. Supports multiple databases:
>>> s_df.to_csv(filename) >>> from sqlalchemy import create_engine
>>> s_df.to_excel(filename) >>> engine = create_engine(database_url)
Write multiple DataFrames to single Excel file: >>> conn = engine.connect()
>>> writer = pd.ExcelWriter(filename) >>> df = pd.read_sql(query_str_or_table_name, conn)
>>> df1.to_excel(writer, sheet_name='First') Write:
>>> df2.to_excel(writer, sheet_name='Second') >>> df.to_sql(table_name, conn)
>>> writer.save()
Take your Pandas skills to the next level! Register at enthought.com/pandas-mastery-workshop
©2022 Enthought, Inc., licensed under the Creative Commons Attribution – Non-Commercial, No Derivatives 4.0
International License. To view a copy of this license, visit creativecommons.org/licenses/by-nc-nd/4.0/ www.enthought.com
2
s
Pandas Data Structures:
Series and DataFrames
Within Pandas, there are two primary data structures: Series (s)
and DataFrames (df).
A Series, which maps an index to values. It can be
thought of as an ordered dictionary or a Numpy array
len(s_df)
s_df.head() and s_df.tail()
s.unique()
s_df.describ
df.info()
Number of rows
First/last rows
Series of unique values
Summary stats
Memory usage
with row labels and a name.
df A DataFrame, which maps index and column labels to Indexing and Slicing
values. It is like a dictionary of Series (columns) sharing Use these attributes on Series and DataFrames for indexing,
the same index, or like a 2D Numpy array with row and slicing, and assignments:
column labels. s_df.loc[ ] Refers only to the index labels
s_df.iloc[ ] Refers only to the integer location,
s_df Applies to both Series and DataFrames.
similar to lists or Numpy arrays
Manipulations of Pandas objects usually return copies. s_df.xs(key, level=L) Select rows with label key in level L
of an object with MultiIndex.
Creating Series and DataFrames
Values Masking and Boolean Indexing
Series Create masks with comparisons:
n1 'Cary' 0
>>> pd.Series(values, index=index, name=name) mask = df['X'] < 0
n2 'Lynn' 1
>>> pd.Series({'idx1' : val1,'idx2' : val2})
n3 'Sam 2 Or isin, for membership mask:
Where values, index, and name are sequences or arrays.
Index mask = df['X'].isin(list_of_valid_values)
DataFrame Use masks for indexing:
Age Gender Columns
>>> pd.DataFrame(values, df.loc[mask] = 0
'Cary' 32 M
index=index, columns=col_names)
'Lynn' 18 F Combine multiple masks with bitwise operators — and (&), or (|),
>>> pd.DataFrame({'col1' :
'Sam 26 M or (^), not (~) — and group them with parentheses:
series1_or _seq,
Index mask = (df['X'] < 0) & (df['Y'] == 0)
'col2': series2_or _seq})
Where values is a sequence of sequences or a 2D array. Common Indexing and Slicing Patterns
rows and cols can be values, lists, Series, or masks.
Manipulating Series and DataFrames
s_df.loc[rows] Some rows (all columns in a DataFrame)
Manipulating Columns df.loc[:, cols_list] All rows, some columns
df.rename(columns={old_name:new_name}) Renames column df.loc[rows, cols] Subset of rows and columns
df.drop(name_or_names, axis='columns') Drops column name s_df.loc[mask] Boolean mask of rows (all columns)
Manipulating Index df.loc[mask, cols] Boolean mask of rows, some columns
s_df.reindex(new_index) Conform to new index
s_df.drop(labels_to_drop) Drops index labels Using [ ] on Series and DataFrames
s_df.rename
On Series, [] refers to the index labels, or to a slice:
(index={old_label: new_label}) Renames index labels
s['a'] Value
s_df.sort_index() Sorts index labels
s[:2] Series, first two rows
df.set_index(column_name_or_names)
s_df.reset_index() Inserts index into columns, resets On DataFrames, [] refers to columns labels:
index to default integer index df['X'] Series
df[['X', 'Y']] DataFrame
Manipulating Values df['new_or_old_col'] = series_or_array
All row values and the index will follow:
df.sort_values(col_name, ascending=True) Except with a slice or mask, as shown below:
df.sort_values(['X','Y'], ascending=[False, True]) df[:2] DataFrame, first two rows
df[mask] DataFrame, rows where mask is True
Important Attributes and Methods
Never chain brackets
s_df.index Array-like row labels
NO >>> df[mask]['X'] = 1
df.columns Array-like column labels
SettingWithCopyWarning
s_df.values Numpy array, data
YES >>> df.loc[mask, 'X'] = 1
s_df.shape (n_rows, n_cols)
s.dtype, df.dtypes Type of Series or of each column
Take your Pandas skills to the next level! Register at enthought.com/pandas-mastery-workshop
©2022 Enthought, Inc., licensed under the Creative Commons Attribution – Non-Commercial, No Derivatives 4.0
International License. To view a copy of this license, visit creativecommons.org/licenses/by-nc-nd/4.0/ www.enthought.com
3 Computation with
Series and DataFrames
Pandas objects do not behave exactly like Numpy arrays. They follow three main rules of binary operations.
Rule 1: Operations between multiple Pandas objects
implement auto-alignment based on index first.
Apply a Function to Each Series
Apply series_to_* function to every column by default
> s1 + s2 > s1.add(s2, fill_value=0) (across rows):
s1 s2 s1 s2 df.apply(series_to_value) → Series
a 11 NaN
NaN a NaN
NaN a 1 0 a 1
df.apply(series_to_series) → DataFrame
b 2
2 +
+ b 4
4 =
= b 4
4 b 2 + b 4 = b 6
To apply the function to every row (across columns), set axis=1:
NaN
NaN c 5
5 c NaN
NaN 0 c 5 c 5 df.apply(series_to_series, axis=1)
Use add, sub, mul, and div, to set fill value.
Apply a Function to a DataFrame
Rule 2: Mathematical operators (+ - * / exp, log, ...) Apply a function that receives a DataFrame and returns a Series,
apply element by element on the values.
a DataFrame, or a single value:
df + 1 df.abs() np.log(df)
df.pipe(df_to_series) → Series
df.pipe(df_to_df) → DataFrame
X Y X Y X Y X Y
df.pipe(df_to_value) → Value
a -2 -2 a -1 -1 a 1 1 a 0 0
b -2 -2 b -1 -1 b 1 1 b 0 0 What Happens with Missing Values?
Missing values are represented by NaN (not a number) or NaT
c -2 -2 c -1 -1 c 1 1 c 0 0
(not a time).
• They propagate in operations across Pandas objects
Rule 3: Reduction operations (mean, std, skew, kurt,
sum, prod, ...) are applied column by column by default. (1 + NaN → NaN).
> df.sum() Series • They are ignored in a "sensible" way in computations;
They equal 0 in sum, they're ignored in mean, etc.
X Y > df.sum() X
a → Y
• They stay NaN with mathematical operations such
as np.log(NaN) → NaN.
b
c
count: Number of non-null observations
Operates across rows by default (axis=0, or axis='rows').
sum: Sum of values
Operate across columns with axis=1 or axis='columns'.
mean: Mean of values
Differences Between mad: Mean absolute deviation
Pandas Objects and Numpy Arrays median: Arithmetic median of values
When it comes to Pandas objects and Numpy arrays, aligning min: Minimum
objects on the index (or columns) before calculations might be max: Maximum
the most important difference. There are built-in methods for mode: Mode
most common statistical operations, such as mean or sum, and prod: Product of values
they apply across one-dimension at a time. To apply custom std: Bessel-corrected sample standard deviation
functions, use one of three methods to do tablewise (pipe), row var: Unbiased variance
or column-wise (apply), or elementwise (applymap) operations. sem: Standard error of the mean
skew: Sample skewness (3rd moment)
Apply a Function to Each Value kurt: Sample kurtosis (4th moment)
Apply a function to each value in a Series or DataFrame: quartile: Sample quantile (Value at %)
s.apply(value_to_value) → Series value_counts: Count of unique values
df.applymap(value_to_value) → DataFrame
Take your Pandas skills to the next level! Register at enthought.com/pandas-mastery-workshop
©2022 Enthought, Inc., licensed under the Creative Commons Attribution – Non-Commercial, No Derivatives 4.0
International License. To view a copy of this license, visit creativecommons.org/licenses/by-nc-nd/4.0/ www.enthought.com
4 Plotting with Pandas
Series and DataFrames
Pandas uses Matplotlib to generate figures. Once a figure is generated with Pandas, all of Matplotlib's functions can be
used to modify the title, labels, legend, etc. In a Jupyter notebook, all plotting calls for a given plot should be in the same cell.
Parts of a Figure Setup
An Axes object is what we Import packages:
think of as a “plot”. It has a >>> import pandas as pd
title and two Axis objects >>> import matplotlib.pyplot as plt
that define data limits. Each Execute this at IPython prompt to display figures in new windows:
Axis can have a label. There >>> %matplotlib
can be multiple Axes objects Use this in Jupyter notebooks to display static images inline:
in a Figure. >>> %matplotlib inline
Use this in Jupyter notebooks to display zoomable images inline:
> %matplotlib notebook
Plotting with Pandas Objects
Series DataFrame Labels
With a Series, Pandas plots values against With a DataFrame, Pandas creates one Use Matplotlib to override or add annotations:
the index: line per column: >>> ax.set_xlabel('Time')
>>> ax = s.plot() >>> ax = df.plot() >>> ax.set_ylabel('Value')
>>> ax.set_title('Experiment A')
Note: When plotting the results of complex manipulations with groupby, it's often useful to Pass labels if you want to override the col-
stack/unstack the resulting DataFrame to fit the one-line-per-column assumption. umn names and set the legend location:
>>> ax.legend(labels, loc='best')
Useful Arguments to Plot
• subplots=True: One subplot per column, instead of one line
• figsize: Set figure size, in inches
• x and y: Plot one column against another
Kinds of Plots
+
df.plot.scatter(x, y) df.plot.bar() df.plot.hist() df.plot.box()
Take your Pandas skills to the next level! Register at enthought.com/pandas-mastery-workshop
©2022 Enthought, Inc., licensed under the Creative Commons Attribution – Non-Commercial, No Derivatives 4.0
International License. To view a copy of this license, visit creativecommons.org/licenses/by-nc-nd/4.0/ www.enthought.com
5 Manipulating
Dates and Times
Use a Datetime index for easy time-based indexing and slicing, as well as for powerful resampling and data alignment.
Pandas makes a distinction between timestamps, called Datetime objects, and time spans, called Period objects.
Converting Objects to Time Objects Creating Ranges of Timestamps
Convert different types like strings, lists, or arrays to Datetime with: >>> pd.date_range(start=None, end=None,
>>> pd.to_datetime(value) periods=None, freq=offset,
Convert timestamps to time spans and set the period “duration” tz='Europe/London')
with frequency offset. Specify either a start or end date, or both. Set number of "steps"
>>> date_obj.to_period(freq=freq_offset) with periods. Set "step size" with freq. Specify time zones with tz.
Frequency Offsets Save Yourself Some Pain: Use ISO 8601 Format
Used by date_range, period_range and resample: To be consistent and minimize the risk of error or confusion, use
• B: Business day • A: Year end ISO format YYYY-MM-DD when entering dates:
• D: Calendar day • AS: Year start NO >>> pd.to_datetime('12/01/2000') # 1st December
• W: Weekly • H: Hourly Timestamp('2000-12-01 00:00:00')
• M: Month end • S: Secondly NO >>> pd.to_datetime('13/01/2000') # 13th January!
• MS: Month start • L, ms: Milliseconds Timestamp('2000-01-13 00:00:00')
• BM: Business month end • U, us: Microseconds YES >>> pd.to_datetime('2000-01-13') # 13th January
• Q: Quarter end • N: Nanoseconds Timestamp('2000-01-13 00:00:00')
Creating Ranges of Periods
For more, look up "Pandas Offset Aliases" or check out the pandas.
>>> pd.period_range(start=None, end=None,
tseries.offsets and pandas.tseries.holiday modules.
periods=None, freq=offset)
Timestamps vs Periods
Resampling
>>> s_df.resample(freq_offset).mean()
resample returns a groupby-like object that must be aggregated
with mean, sum, std, apply, etc. (See also the Split-Apply-Combine
cheatsheet.)
VECTORIZED STRING OPERATIONS
Pandas implements vectorized string operations named Splitting and Replacing
after Python's string methods. Access them through the Split returns a Series of lists:
str attribute of string Series. >>> s.str.split()
Some String Methods Access an element of each list with get:
>>> s.str.split(char).str.get(1)
>>> s.str.lower() >>> s.str.strip()
>>> s.str.isupper() >>> s.str.normalize() Return a DataFrame instead of a list:
>>> s.str.split(expand=True)
>>> s.str.len()
Find and replace with string or regular expressions:
Index by character position:
>>> s.str.replace(str_or_regex, new)
>>> s.str[0]
>>> s.str.extract(regex)
True if a regular expression pattern or string is in a Series:
>>> s.str.findall(regex)
>>> s.str.contains(str_or_pattern)
Take your Pandas skills to the next level! Register at enthought.com/pandas-mastery-workshop
©2022 Enthought, Inc., licensed under the Creative Commons Attribution – Non-Commercial, No Derivatives 4.0
International License. To view a copy of this license, visit creativecommons.org/licenses/by-nc-nd/4.0/ www.enthought.com
6 Combining
DataFrames
There are numerous tools for combining Series and
DataFrames together, with SQL-type joins and concat-
enation. Use join if merging on indices, otherwise use
merge.
Join on Index
>>> df.join(other)
Merge DataFrames on indexes. Set on=columns to join on index of
other and on columns of df. join uses pd.merge under the covers.
Merge on Column Values Concatenating DataFrames
>>> pd.merge(left, right, how='inner', on='id')
>>> pd.concat(df_list)
Ignores index, unless on=None. See the section on the how keyword.
“Stacks” DataFrames on top of each other.
Use on if merging on same column in both DataFrames, otherwise use Set ignore_index=True to replace index with RangeIndex.
left_on, right_on. Note: Faster than repeated df.append(other_df).
MERGE TYPES: THE HOW KEYWORD
left left_on='X' right_on='Y' right
LONG X LONG X Y SHORT Y SHORT
0 aaaa a 0 aaaa a — — 0 b bb
left right how="outer"
1 bbbb b 1 bbbb b b bb 1 c cc
2 — — c cc
LONG X LONG X Y SHORT Y SHORT
left right how="inner" 0 aaaa a 0 bbbb b b bb 0 b bb
1 bbbb b 1 c cc
LONG X LONG X Y SHORT Y SHORT
left right how="left" 0 aaaa a 0 aaaa a — — 0 b bb
1 bbbb b 1 bbbb b b bb 1 c cc
LONG X LONG X Y SHORT Y SHORT
left right how="right" 0 aaaa a 0 bbbb b b bb 0 b bb
1 bbbb b 1 — — c cc 1 c ctc
CLEANING DATA WITH MISSING VALUES
Pandas represents missing values To find missing values, use:
as NaN (Not a Number), which comes >>> s_df.isnull() or >>> pd.isnull(obj)
from Numpy and is of type float64. >>> s_df.notnull() or >>> pd.notnull(obj)
To find and replace these missing To replace missing values, use:
values, you can use any number s_df.loc[s_df.isnull()] = 0 Use mask to replace NaN
of methods. s_df.interpolate(method='linear') Interpolate using different methods
s_df.fillna(method='ffill') Fill forward (last valid value)
s_df.fillna(method='bfill') Or backward (next valid value)
s_df.dropna(how='any') Drop rows if any value is NaN
s_df.dropna(how='all') Drop rows if all values are NaN
s_df.dropna(how='all', axis=1) Drop across columns instead of rows
Take your Pandas skills to the next level! Register at enthought.com/pandas-mastery-workshop
©2022 Enthought, Inc., licensed under the Creative Commons Attribution – Non-Commercial, No Derivatives 4.0
International License. To view a copy of this license, visit creativecommons.org/licenses/by-nc-nd/4.0/ www.enthought.com
7
1. Split the data based on
some criteria.
Split / Apply / Combine
with DataFrames
2. Apply a function to each group
to aggregate, transform, or filter.
Apply/Combine: Aggregation
Perform computations on each group.
The shape changes; the categories in the
grouping columns become the index. Can
3. Combine the results. use built-in aggregation methods: mean,
sum, size, count, std, var, sem, describe,
The apply and combine steps are first, last, nth, min, max, for example:
typically done together in Pandas. >>> g.mean()
Apply/Combine: Filtering ... or aggregate using custom function:
Split: Group By Returns a group only if condition is true. >>> g.agg(series_to_value)
Group by a single column: >>> g.filter(lambda x: len(x)>1) ... or aggregate with multiple functions at
>>> g = df.groupby(col_name) once:
Grouping with list of column names cre- >>> g.agg([s_to_v1, s_to_v2])
ates a DataFrame with a MultiIndex: ... or use different functions on different
>>> g = df.groupby(list_col_names) columns:
Pass a function to group based on the index: >>> g.agg({'Y': s_to_v1,
>>> g = df.groupby(function) ... 'Z': s_to_v2})
Z
0 a
2 a
Split/Apply/Combine Z
1 b a
3 b b
c
Z
4 c
Other Groupby-Like Operations:
Apply/Combine: Window Functions
General Tool: apply
• resample, rolling, and ewm (exponen-
apply is more general than agg, trans-
tial weighted function) methods behave
form, and filter. It can aggregate, Split Apply Combine
like GroupBy objects. They keep track of
transform or filter. The resulting dimen- • Groupby • Apply which row is in which “group.” Results
sions can change, for example: • Window • Group-specific
Functions transformations must be aggregated with sum, mean,
>>> g.apply(lambda x: x.describe()) • Aggregation count, etc.
• Group-specific Filter-
Apply/Combine: Transformation ing • resample is often used before roll-
ing, expanding, and ewm when using a
The shape and the index do not change.
>>> g.transform(df_to_df)
Split: What’s a GroupBy Object? DateTime index.
Example, normalization: It keeps track of which rows are part of
>>> def normalize(grp): which group.
... return ( >>> g.groups → Dictionary, where keys
... (grp - grp.mean()) are group names, and values are indices
... / grp.var() of rows in a given group.
... ) It is iterable:
>>> for group, sub_df in g:
>>> def normalize(grp): ... ...
... return ((grp - grp.mean())
... / grp.var())
Take your Pandas skills to the next level! Register at enthought.com/pandas-mastery-workshop
©2022 Enthought, Inc., licensed under the Creative Commons Attribution – Non-Commercial, No Derivatives 4.0
International License. To view a copy of this license, visit creativecommons.org/licenses/by-nc-nd/4.0/ www.enthought.com
8 Reshaping DataFrames
and Pivot Tables
Let’s explore some tools for reshaping DataFrames from the wide to the long format and back. The long format can be
tidy, which means that each variable is a column, each observation is a row. It is easier to filter, aggregate, transform,
sort, and pivot. Reshaping operations often produces multi-level indices or columns, which can be sliced and indexed.
MultiIndex: A Multi-Level Hierarchical Index Long to Wide Format and Back with stack()
and unstack()
Often created as a result of:
>>> df.groupby(list_of_columns) Pivot column level to index, i.e. “stacking the columns” (wide to long):
>>> df.set_index(list_of_columns) >>> df.stack()
Contiguous labels are displayed together but apply to each row. Pivot index level to columns, “unstack the columns” (long to wide):
The concept is similar to multi-level columns. >>> df.unstack()
A MultiIndex allows indexing and slicing one or multiple levels at If there are multiple indices or column levels, use level number or
once. Using the Long example from the right: name to stack/unstack:
long.loc[1900] All 1900 rows >>> df.unstack(1) or >>> df.unstack('Month')
long.loc[(1900, 'March')] Value 2 A common use case for unstacking, plotting group data vs index
long.xs('March', level='Month') All March rows after groupby:
>>> (df.groupby(['A', 'B])['relevant'].mean()
Simpler than using boolean indexing, for example: ... .unstack().plot())
>>> long[long.Month == 'March']
Pivot Tables
>>> pd.pivot_table(df,
... index=cols, keys to group by for index
... columns=cols2, keys to group by for columns
... values=cols3, columns to aggregate
... aggfunc='mean') what to do with repeated values From Wide to Long with melt
Specify which columns are identifiers (id_vars, values will be
Omitting index, columns, or values will use all remaining
repeated for each row) and which are “measured variables”
columns of df. You can “pivot” a table manually using groupby,
(value_vars, will become values in variable column. All remain-
stack, and unstack.
ing columns by default).
>>> pd.melt(df, id_vars=id_cols, value_vars=value_columns)
>>> pd.melt(team, id_vars=['Color'],
... value_vars=['A', 'B', 'C'],
... var_name='Team',
... value_name='Score')
df.pivot() vs pd.pivot_table
df.pivot() Does not deal with repeated values in index.
It's a declarative form of stack and unstack.
pd.pivot_table() Use if you have repeated values in index
(specify aggfunc argument).
Take your Pandas skills to the next level! Register at enthought.com/pandas-mastery-workshop
©2022 Enthought, Inc., licensed under the Creative Commons Attribution – Non-Commercial, No Derivatives 4.0
International License. To view a copy of this license, visit creativecommons.org/licenses/by-nc-nd/4.0/ www.enthought.com