INTRO TO PANDAS &

NUMPY
In this section we’ll introduce Pandas & NumPy, two critical Python
libraries that help
structure data in arrays & DataFrames and contain built-in functions for
data analysis

TOPICS WE’LL GOALS FOR THIS SECTION:

COVER:
• Convert Python lists to NumPy arrays,
and create new arrays from scratch using
functions
• Apply array indexing, slicing, methods, and
functions to perform operations on NumPy
arrays

• Understand the concepts of vectorization

and broadcasting, which are critical in
making NumPy and Pandas more
efficient than base Python
 MEET
PANDAS

Pandas is Python’s most widely used library for data analysis, and
contains
functions for accessing, aggregating, joining, and analyzing data
Its data structure, the DataFrame, is analogous to SQL tables or
Excel worksheets
Custom indices &
column titles make
working with datasets
more intuitive!
 MEET
NUMPY

NumPy is an open-source library that is the universal standard for

working with
numerical data in Python, and forms the foundation of other libraries
like Pandas
Pandas DataFrames are built on NumPy arrays and can leverage
The indices, column
NumPy
names, and datafunctions
columns
are all stored as NumPy
arrays
Pandas just adds
convenient wrappers and
functions!
 NUMPY
ARRAYS
NumPy arrays are fixed-size containers of items that are more
efficient than Python lists or tuples for data processing
• They only store a single data type (mixed data types are stored as a string)
• They can be one dimensional or multi-dimensional
• Array elements can be modified, but the array size cannot change

‘np’ is the standard alias for the NumPy

library

NumPy’s array function

converts Python lists
and tuples into NumPy
arrays
 ARRAY
PROPERTIES
NumPy arrays have these key properties:
• ndim – the number of dimensions (axes) in
the array
• shape – the size of the array for each
dimension
• size – the total number of elements in the
array
• dtype – the data type of the elements in
the array
NumPy arrays are a ndarray Python data
type, which stands for n-dimensional array

The sales_array has 1

dimension The dimension has
a size of 10 The array has
10 elements total
The elements are stored as
64-bit integers
 ARRAY
PROPERTIES
NumPy arrays have these key properties:
• ndim – the number of dimensions (axes) in
the array
• shape – the size of the array for each
dimension
• size – the total number of elements in the
array
• dtype – the data type of the elements in Converting a nested list creates a multi-
the array dimensional array, where each nested list is a
dimension
NOTE: The nested lists must be of equal length

The sales_array has 2 dimensions

The first dimension has a size of 2 (rows) and the second a size of 5
(columns)
It haselements
The 10 elements total as 64-bit
are stored
integers
 ARRAY
CREATION
As an alternative to converting lists, you can create arrays
using functions

Creates an array of ones of a given size, as float by [Link]((rows, cols),

default dtype)

Creates an array of zeros of a given size, as float by [Link]((rows, cols),

default dtype)
Creates an array of integers with given start & stop values,
and a step size (only stop is required, and is not [Link](start, stop,
inclusive) step)
Creates an array of floats with given start & stop values
with n elements, separated by a consistent step size [Link](start,
(stop is inclusive) stop, n)

Changes an array into the specified dimensions, if compatible [Link](rows,

cols)
 ARRAY
CREATION
As an alternative to converting lists, you can create arrays
using functions
[Link]((rows, cols), dtype) [Link](start, stop, start is 0 and step is 1 by
default
step)

stop is not
inclusive
[Link]((rows, cols), [Link](start,
dtype) stop, n)

stop is inclusive

[Link](rows, cols)
Practice Time
 INDEXING & SLICING
ARRAYS
Indexing & slicing one-dimensional arrays is the same as base
Python
• array[index] – indexing to access a single element (0-indexed)
• array[start:stop:step size] – slicing to access a series of elements
(stop is not inclusive)

This grabs the second and

last
elements of product_array

This grabs the first five

elements of product_array

This starts at the sixth

element and grabs every
other element until the end
of product_array
 INDEXING & SLICING
ARRAYS
Indexing & slicing two-dimensional arrays requires an extra
index or slice
• array[row index, column index] – indexing to access a single element
(0-indexed)
• array[start:stop:step size, start:stop:step size] – slicing to access a
series of elements

This goes to the second

row and grabs the third
element

This goes to all rows and

grabs all the elements
starting from the third in
each row

This goes to the second

row and grabs all its
elements
 ARRAY
OPERATIONS
Arithmetic operators can be used to perform array
operations
Array operations are applied
via vectorization and
broadcasting, which
eliminates the need to loop
through the array's
elements

This adds 2 to
every
element in the
array

This assigns all the elements in the first row to 'quantity'

Then assigns all the elements in the second row to 'price'
Finally, it multiplies the corresponding elements in each
array: (0*0, 5*1827, 155*616, 0*317, and 518*325)
 FILTERING
ARRAYS
You can filter arrays by indexing them with a logical test
• Only the array elements in positions where the logical test returns True
are returned

Performing a logical test on a NumPy array

returns a Boolean array with the results of
the logical test on each array element

Indexing an array with a Boolean array returns an array

with
the elements where the Boolean value is True
 FILTERING
ARRAYS
You can filter arrays with multiple
logical tests
• Use | for or conditions and & for and
conditions

This returns an array with

elements equal to 616 or less
than 100

This returns an array with

elements greater than 100 and
less than 500

PRO TIP: Store

complex filtering
criteria in a variable
(known as a Boolean
mask)
 FILTERING
ARRAYS
You can filter arrays based on values in other arrays
• Use the Boolean array returned from the other array to index the array
you want to filter

This returns the elements from

product_array
where values in sales_array are greater
than 0
 MODIFYING ARRAY
VALUES
You can modify array values by assigning
new ones

This assigns a single value via

indexing

This filters the zero values in sales_array

and assigns them a new value of 5
 THE WHERE
FUNCTION
The where() NumPy function performs a logical test and returns a
given value if the test is True, or another if the test is False

A logical expression that

[Link](logical test, evaluates to True or False

value if True,
Value to return when
value if False) the expression is True

Calls the Value to return when

NumPy the expression is
function library False
 THE WHERE
FUNCTION
The where() NumPy function performs a logical test and returns a
given value if the test is True, or another if the test is False

If inventory is zero or
negative, assign ‘Out of
Stock’, otherwise assign ‘In
Stock’

If inventory is zero or
negative, assign ‘Out of
Stock’, otherwise assign the
product_array value
 ARRAY AGGREGATION
METHODS
Array aggregation methods let you calculate metrics like sum,
mean, and max

[Link] Returns the sum of all values in an [Link] Returns the largest value in an
array array
m() x()

[Link]() Returns the average of the values in an

[Link] Returns the smallest value in an
array
array n()
 ARRAY AGGREGATION
METHODS
You can also aggregate across rows or
columns

[Link] Returns the sum of all values in an [Link](axi Aggregates across

array rows
m() s=0)

[Link](axi Aggregates across

columns
s=1)
 ARRAY
FUNCTIONS
Array functions let you perform other aggregations like median
and percentiles

[Link](arr Returns the median value in an

array
ay)

[Link](array, n) Returns a value in the nth percentile in

an array

This uses linear interpolation by

default
 ARRAY
FUNCTIONS
You can also return a unique list of values or the square root
for each number

[Link](arr Returns the unique values in an

array
ay)

[Link](arr Returns the square root of each value in an

array
ay)
 SORTING
ARRAYS
The sort() method will sort arrays in place
• Use the axis argument to specify the dimension
to sort by

axis=1 by default, which sorts a

two-dimensional array row by
row

axis=0 will sort by

columns
 KEY
TAKEAWAYS
NumPy forms the foundation for
Pandas
• As an analyst, it's important to be comfortable working with NumPy arrays & functions in order to
use Pandas data structures & functions properly

NumPy arrays are more efficient than base Python

lists
• and
They tuples
are semi-mutable data structures capable of storing many data
types
• Their values can be modified, but their size cannot

Array operations let you aggregate, filter,

and• sort dataand vectorization make these operations convenient and efficient without the use
Broadcasting
of loops
• The syntax for NumPy array operations is very similar to Pandas
 SERIE
S
In this section we’ll introduce Pandas Series, the Python equivalent of a
column of data,
and cover their basic properties, creation, manipulation, and useful
functions for analysis

TOPICS WE’LL GOALS FOR THIS SECTION:

COVER:
• Understand the relationship between
Pandas Series and NumPy arrays

• Use the .loc() and .iloc() methods to

access Series data by their indices or
values

• Learn to sort, filter, and aggregate

Pandas Series using methods and
functions

• Apply custom functions using conditional

logic to Pandas Series
 PANDAS
SERIES
Series are Pandas data structures built on top of NumPy arrays
• Series also contain an index and an optional name, in addition to the
array of data
• They can be created from other data types, but are usually imported from
external sources
• Two or more Series grouped together form a Pandas DataFrame
‘pd’ is the standard alias for the Pandas
library

Pandas’ Series function converts Python

lists
and NumPy arrays into Pandas Series
The index is an array The name argument lets you specify a
of integers starting at name
0 by default, but it
can be modified

The series name and data type are stored

as well
 SERIES
PROPERTIES
Pandas Series have these key properties:
• values – the data array in the Series
• index – the index array in the Series
• name – the optional name for the Series (useful for accessing columns in a
DataFrame)
• dtype – the data type of the elements in the values array

The index is a
range of
integers from 0
to 10
 PANDAS DATA
TYPES
Pandas data types mostly expand on their base Python and
NumPy equivalents
Numeri Object /
c: Text:
bool Boolean True/False 8 object Any Python object
int64 (default) Whole numbers 8, 16, 32, string Only contains strings or text
64
category Maps categorical data to a numeric array for
float64 (default) Decimal numbers 8, 16, 32, efficiency
64
boolean Nullable Boolean 8
True/False Time
Int64 (default) Nullable whole numbers 8, 16, 32, Series:
64
A single moment in time
*Gray = NumPy
Float64 data Nullable
(default) type decimal numbers 32, 64 datetime64
(January 4, 2015, 2:00:00 PM)
*Yellow = Pandas data
type The duration between two dates or times
timedelta64
(10 days, 3 seconds, etc.)
We'll review the nuances of data A span of time
types period
(a day, a week, etc.)
in depth when covering
DataFrames
 TYPE
CONVERSION
You can convert the data type in a Pandas Series by using
the .astype() method and specifying the desired data type (if
compatible)

These are This converts them to

integers floats

This converts them to This attempts to convert them to

Booleans (0 is False, others the
are True) Datetime datatype, but isn’t
compatible
 THE
INDEX
The index lets you easily access “rows” in a Pandas Series or
DataFrame

Here we’re using the

default integer
index, which is
preferred

You can index and slice Series like

other sequence data types, but
we’ll learn a better method
 CUSTOM
INDICES
There are cases where it’s applicable to use a custom index for
accessing rows

Custom indices can be assigned

when
creating the series or by assignment

This will become more

relevant when working
with datetimes (covered
later in the course!)
 CUSTOM
INDICES
There are cases where it’s applicable to use a custom index for
accessing rows

You can still index and slice

to retrieve Series values
using the custom indices

Note that slicing custom

indices makes the stop point
inclusive
 THE ILOC
METHOD
The .iloc[] method is the preferred way to access values by their
positional index
• This method works even when Series have a custom, non-integer index
• It is more efficient than slicing and is recommended by Pandas’ creators

[Link][row position, column position]

Series or DataFrame The row position(s) for the The column position(s) for
to access values value(s) you want to the value(s) you want to
from access access

Examples:
• 0 (single row) We’ll use the column
• [5, 9] (multiple position argument once we
rows) start working with Pandas
• [0:11] (range of DataFrames
rows)
 THE ILOC
METHOD
The .iloc[] method is the preferred way to access values by their
positional index
• This method works even on Series with a custom, non-integer index
• It is more efficient than slicing and is recommended by Pandas’ creators

Note that this

Series
has a custom
index

This returns the value in the 3rd position (0-indexed),

even
though the custom index for that value is “tea”

This returns the values from the 3rd to the 4th position
(stop is non-inclusive)
 THE LOC
METHOD
The .loc[] method is the preferred way to access values by their
custom labels

[Link][row label, column label]

Series or DataFrame The custom row index for The custom column index for
to access values the value(s) you want to the value(s) you want to
from access access

Examples:
• "pizza" (single row)
• ["mike", "ike"]
(multiple rows)
• ["jan":"dec"] (range
of rows)
 THE LOC
METHOD
The .loc[] method is the preferred way to access values by their
custom labels

The .loc[] method works

The custom even when the indices are
indices integers, but if they are
are the labels custom integers not
ordered from 0 to n-1, the
rows will be returned
based on the labels
themselves and NOT
their numeric position

Note that slices are

inclusive
when using custom labels
 DUPLICATE INDEX
VALUES
It is possible to have duplicate index values in a Pandas Series
or DataFrame
• Accessing these indices by their label using .loc[] returns all corresponding
rows

Note that ‘coffee’ is used as an index value Warning! Duplicate index values
twice are generally not advised, but
there are some edge cases
where they are useful

This returns both rows with the same

label
 RESETTING THE
INDEX
You can reset the index in a Pandas Series or DataFrame back
to the default range of integers by using the .reset_index()
method
• By default, the existing index will become a new column in a DataFrame

Use drop=True when resetting

the index if you don’t want the
This returns a DataFrame by previous index values stored
default, with the previous
index values stored as a
new column
 FILTERING
SERIES
You can filter a Series by passing a logical test into the .loc[]
accessor (like arrays!)

This returns all rows from

sales_series with a value greater
than 0

This uses a mask to store

complex logic and returns all
rows from sales_series with a
greater than 0 and an index
equal to “coffee”
 LOGICAL OPERATORS &
METHODS
You can use these operators & methods to create Boolean filters
for logical tests

Equal == .eq()

Not Equal != .ne()

Less Than or Equal <= .le()

Less Than < .lt() Python Operator: Pandas Method:

Greater Than or Equal >= .ge()

Greater Than > .gt()

Membership Test in .isin()

Inverse Membership Test not in ~.isin()

 LOGICAL OPERATORS &
METHODS
You can use these operators & methods to create Boolean filters
for logical tests

Equal == .eq()

Not Equal != .ne()

Less Than or Equal <= .le()

Less Than < .lt()

Greater Than or Equal >= .ge()

Greater Than > .gt()

Membership Test in .isin()

Inverse Membership not in ~.isin()

Test The tilde ‘~’ inverts Boolean
values!
The Python operators ‘in’ and ‘not in’ won’t work for many
operations, so the Pandas method must be used
 SORTING
SERIES
You can sort Series by their values or their index
1. The .sort_values() method sorts a Series by its values in
ascending order

Specify ascending=False
to sort in descending
order

2. The .sort_index() method sorts a Series by its index in ascending order

ARITHMETIC OPERATORS &
METHODS
You can use these operators & methods to perform numeric
operations on Series

Addition + .add()

.sub(),
Subtraction -
.subtract()
.mul(),
Multiplication *
.multiply()
These both add two to every
.div(), row
Division / .truediv(),
.divide()

Floor Division // .floordiv()

Modulo % .mod() This uses string arithmetic to add a dollar

sign, converts to float to add decimals
Exponentiation ** .pow() (cents), then converts back to a string
 STRING
METHODS
The Pandas str accessor lets you access many
string methods
• These methods all return a Series (split returns multiple
series)
.strip(), .lstrip(), Removes all leading and/or trailing
.rstrip() characters (spaces by default)

.upper(), .lower() Converts all characters to upper or lower

case
.slice(start:stop:ste Applies a slice to the strings in a Series
p)
The str accessor lets you
.count("string") Counts all instances of a given string access the string
methods
.contains("string") Returns True if a given string is found; False
if not
.replace("a", "b") Replaces instances of string "a" with string
"b"
.split("delimit Splits strings based on a given delimiter
er", string, and returns a DataFrame with a This is removing the dollar
expand=True) Series for each split sign, then converting to float
.len() Returns the length of each string in a Series

.startswith("string") Returns True if a string starts or ends

, with given string; False if not
 NUMERIC SERIES
AGGREGATION
You can use these methods to aggregate
numerical Series

.count() Returns the number of items

.first(), .last() Returns the first or last item

.mean(), .median() Calculates the mean or median

.min(), .max() Returns the smallest or largest value

.argmax(), .argmin Returns the index for the smallest or largest

() values
.std(), .var() Calculates the standard deviation or variance

.mad() Calculates the mean absolute deviation

.prod() Calculates the product of all the items

.sum() Calculates the sum of all the items

.quantile() Returns a specified percentile, or list of

percentiles
CATEGORICAL SERIES
AGGREGATION
You can use these methods to aggregate
categorical Series

.unique() Returns an array of unique items in a Series

.nunique() Returns the number of unique items

.value_counts() Returns a Series of unique items and their

frequency

Specify normalize=True to
return the percentage of
total for each category
 MISSING
DATA
Missing data in Pandas is often represented by NumPy “NaN”
values
• This is more efficient than Python’s “None” data type
• Pandas treats NaN values as a float, which allows them to be used in
vectorized operations

[Link] creates a NaN value

These are rarely created by
hand, and typically appear when
reading in data from external
sources

If NaN was not present

here, the data type would
be int64

Most operation methods

Arithmetic operations
include a ‘fill_value’ argument
performed
that lets you pass a value
on NaN values will return
instead of NaN
NaN
 MISSING
DATA
Pandas released its own missing data type, NA, in December
2020
• This allows missing values to be stored as integers, instead of needing to
convert to float
• This is still a new feature, but most bugs end up converting the data to
NumPy’s NaN

[Link] creates an NA
value Note that if
dtype=“Int16”
wasn’t specified, the
values
At this time, would be stored
neither as
[Link] nor [Link]
are perfect, objects
but [Link] functionality should
continue to improve, and having a nullable
integer is usually worth it (more on that in the
next section!)
 IDENTIFYING MISSING
DATA
The .isna() and .value_counts() methods let you identify missing
data in a Series
• The .isna() method returns True if a value is missing, and False otherwise
.isna().sum() returns the
count of NaN values

You can use this

as a Boolean
mask!

• The .value_counts() method returns unique values and their frequency

Most methods ignore NaN

values, so you need to
specify dropna=False to
return the count of NaN
values
 HANDLING MISSING
DATA
The .dropna() and .fillna() methods let you handle missing
data in a Series
• The .dropna() method removes NaN values from your Series or
DataFrame
Note that the index has gaps,
so you can use .reset_index()
to restore the range of
integers

• The .fillna(value) method replaces NaN values with a

specified value
 HANDLING MISSING
DATA
It’s important to be thoughtful and deliberate in how you
handle missing data
EXAMPL Handling missing values from product
E sales
Do you keep them? Do you remove Do you replace them with
them? zeros?

Do you impute them with the

mean?
PRO TIP: These operations can
dramatically impact the results of an
analysis, so make sure you understand
these impacts and talk to a data SME
to understand why data is missing
 THE WHERE
METHOD
Pandas’ .where() method lets you manipulate data based on a
logical condition

A logical expression that

[Link](logical test, evaluates to True or False

value if False,
Value to return when
inplace=False) the expression is
False

Series or DataFrame Whether to perform

to evaluate data from the operation in
place Heads up! This is
(default is False) different from NumPy’s
where function
 THE WHERE
METHOD
Pandas’ .where() method lets you manipulate data based on a
logical condition

This expression returns False if the price is greater

than 20, and the value if false statement for the
discount is applied
 THE WHERE
METHOD
Pandas’ .where() method lets you manipulate data based on a
logical condition

You can use a tilde ‘~’ to invert the

Boolean values and turn this into a
“value if True”
 CHAINING
WHERE
You can chain .where() methods to combine logical
expressions

The first where method applies a 90% discount if a price is greater

than 20 The second applies a value of 0 when a price is NOT
greater than 10
 NUMPY VS. PANDAS
WHERE
NumPy’s where function is often more convenient & useful than
Pandas’ method

Note that this returns a NumPy array

that
you’d need to convert into a Pandas
Series
 KEY
TAKEAWAYS
Pandas Series add an index & title to
NumPy arrays
• Pandas Series form the columns for DataFrames, which we will cover in the
next section

The .loc() & .iloc() methods are key in working with Pandas
data
• structures
These methods allow you to access rows in Series (and later columns in DataFrames), either
by their positional index or by their labels

Pandas & NumPy have similar operations for filtering, sorting

& aggregating
• Use built-in Pandas and NumPy functions and methods to take advantage of vectorization,
which is much more efficient than writing for loops in base Python

Pandas lets you easily handle

missing datato understand the impact dropping or imputing might have on your analysis,
• It’s important
so make
sure you consult an expert about the root cause of missing data
 DATAFRAME
S
In this section we’ll introduce Pandas DataFrames, the Python equivalent
of an Excel or
SQL table which we’ll use to store and analyze data

TOPICS WE’LL GOALS FOR THIS SECTION:

COVER:
• Learn to read in DataFrames, explore their
contents, and access data within them

• Manipulate DataFrames by filtering &

sorting rows,
handling missing data, and applying Pandas
functions

• Understand the different data types in

Pandas DataFrames, as well as how to
optimize memory consumption by
converting & downcasting them
 THE PANDAS
DATAFRAME
DataFrames are Pandas “tables” made up from columns and
rows
• Each column of data in a DataFrame is a Pandas Series that shares the
same row index
• The column headers work as a column index that contains the Series
names
The column
index points to
each individual
The row index Series
points to the
corresponding row (axis = 1)
in each Series
(axis = 0)

Each column is a Pandas

Series
 DATAFRAME
PROPERTIES
DataFrames have these key properties:
• shape – the number of rows and columns in a DataFrame (the index is not
considered a column)
• index – the row index in a DataFrame, represented as a range of integers
(axis=0)
• columns – the column index in a DataFrame, represented by the Series names
(axis=1)
• axes – the row and column indices in a DataFrame
• dtypes – the data type for each Series in a DataFrame (they can be different!)

Pandas will try to guess the

data types when creating a
DataFrame
(we’ll modify them later!)
 CREATING A
DATAFRAME
You can create a DataFrame from a Python dictionary or NumPy
array by using the Pandas DataFrame() function

This creates a DataFrame from a Python

dictionary Note that the keys are used as
column names
 CREATING A
DATAFRAME
You’ll more likely create a DataFrame by reading in a flat file (csv,
txt, or tsv) with Pandas read_csv() function

While the read_csv() function has more arguments

for manipulating the data during the import
process, all you need is the file path and name
to get started!

PRO TIP: Pandas is a

great alternative to
Excel when dealing
with large datasets!
 EXPLORING A
DATAFRAME
You can explore a DataFrame with these Pandas
methods:

Returns the first n rows of the DataFrame (5 by default) [Link](nro

ws)

Returns the last n rows of the DataFrame (5 by default) [Link](nrow

s)

Returns n rows from a random sample (1 by default) [Link](n)r

ows

Returns key details on a DataFrame’s size , columns, and memory [Link]

usage ()
Returns descriptive statistics for the columns in a DataFrame (only
numeric [Link](inclu
columns by default; use the ‘include’ argument to specify more de)
columns)
 HEAD &
TAIL
The .head() and .tail() methods return the top or bottom rows in
a DataFrame
• This is a great way to QA data upon import!

This returns the top 5 rows by

default

You can specify the number of

rows
to return, in this case the
bottom 3
 SAMPL
E
The .sample() method returns a random sample of rows from
a DataFrame

This returns 1 row by

default

You can specify the number of

rows to return, in this case 5, and
set a random_state to ensure your
sample can be reproduced later
in needed
 INF
O
The .info() method returns details on a DataFrame’s properties and
memory usage

Rows & columns

in the
DataFrame

Position, name,
and data type
for each
column

Memory
usage
 INF
O
The .info() method returns details on a DataFrame’s properties and
memory usage

The .info() method will show non-null

counts on a DataFrame with less than
~1.7m rows, but you can specify
show_counts=True to ensure they are
always displayed
This is a great way to quickly identify
missing values – if the non-null count is
less than the total number of rows, then
the difference is the number of NaN
values in that column!
(In this case there are none)
 DESCRIB
E
The .describe() method returns key statistics on a
DataFrame’s columns

Only numeric columns by

default
Non-null values

Mean &
standard
deviation values

Quartile values
 DESCRIB
E
The .describe() method returns key statistics on a
DataFrame’s columns

Use include=“all” to return

statistics for all columns, or choose
a specific data type to include
Note that the .round() method
suppresses scientific notation
and makes the output more
Unique values,
readable
most common
value (top),
and its
frequency
ACCESING DATAFRAME
COLUMNS
You can access a DataFrame column by using bracket or dot
notation
• Dot notation only works for valid Python variable names (no spaces, special
characters, etc.),
and if the column name is not the same as an existing variable or method

PRO TIP: Even though you’ll see many examples of dot

notation in use, stick
to bracket notation for single columns of data as it is less likely
to cause issues
ACCESSING DATAFRAME
COLUMNS
You can use Series operations on DataFrame columns (each
column is a Series!)

Number of unique values in a Mean of values in a

column column

First 5 unique values in a column with their Rounded sum of values in a

frequencies column
ACCESSING DATAFRAME
COLUMNS
You can select multiple columns with a list of column names
between brackets
• This is ideal for selecting non-consecutive columns in a DataFrame

PRO TIP: Use .loc() to access more than

one column of data – column bracket
notation should primarily be used for
creating new columns and quick exploration
 ACCESING DATA WITH
ILOC
The .iloc() accessor filters DataFrames by their row and
column indices
• The first parameter accesses rows, and the second accesses
columns
First 5
rows, all
columns
 ACCESING DATA WITH
ILOC
The .iloc() accessor filters DataFrames by their row and
column indices
• The first parameter accesses rows, and the second accesses
columns
All rows,
2-4
columns
 ACCESING DATA WITH
ILOC
The .iloc() accessor filters DataFrames by their row and
column indices
• The first parameter accesses rows, and the second accesses
columns
First 5
rows,
2-4
columns
 ACCESING DATA WITH
LOC
The .loc() accessor filters DataFrames by their row and
column labels
• The first parameter accesses rows, and the second accesses
columns
All rows,
“date”
column
Wrap single
This is a columns in
Series brackets to return
a DataFrame
 ACCESING DATA WITH
LOC
The .loc() accessor filters DataFrames by their row and
column labels
• The first parameter accesses rows, and the second accesses
columns
All rows, All rows,
“date” & “date” through
“sales” “sales”
columns columns
(list of (slice of
columns) columns)
 DROPPING ROWS &
COLUMNS
The .drop() method drops rows and columns from a
DataFrame
• Specify axis=0 to drop rows by label, and axis=1 to drop
columns

This returns the first 5 rows of the

retail_df
DataFrame without the “id” column

You can specify inplace=True to

permanently
remove rows or columns from a
DataFrame

PRO TIP: Drop unnecessary columns early in your

workflow to save memory and make DataFrames
more manageable (ideally, they shouldn’t be imported –
more on that later!)
 DROPPING ROWS &
COLUMNS
The .drop() method drops rows and columns from a
DataFrame
• Specify axis=0 to drop rows by label, and axis=1 to drop
columns

This returns the first 5 rows of the

retail_df
DataFrame after removing the first row
Note that the row label is passed as a
list

You can pass a range to remove rows

with consecutive labels, in this case 0-
4

You’ll typically drop rows via slicing or

filtering, but
it’s worth being aware that .drop() can be
used as well
 IDENTIFYING DUPLICATE
ROWS
The .duplicated() method identifies duplicate rows of
data
• Specify subset=column(s) to look for duplicates across a subset
of columns

If the number of unique values for a

column is less than the total number of
rows, then that column contains
duplicate values
 IDENTIFYING DUPLICATE
ROWS
The .duplicated() method identifies duplicate rows of
data
• Specify subset=column(s) to look for duplicates across a subset
of columns

The .duplicated() method returns True for the

second
row here because it is a duplicate of the first
row
 IDENTIFYING DUPLICATE
ROWS
The .duplicated() method identifies duplicate rows of
data
• Specify subset=column(s) to look for duplicates across a subset
of columns

Specifying subset=‘product’ will

only look for duplicates in that
column
In this case rows 2 and 3 are
duplicates of the first row
(“Dairy”)
 DROPPING DUPLICATE
ROWS
The .drop_duplicates() method drops duplicate rows from
a DataFrame
• Specify subset=column(s) to look for duplicates across a subset of
columns

This removed the second row from the

product_df DataFrame, as it is a duplicate of
the first row
Note that the row index now has a gap
between 0 & 2
 DROPPING DUPLICATE
ROWS
The .drop_duplicates() method drops duplicate rows from
a DataFrame
• Specify subset=column(s) to look for duplicates across a subset of
columns

How does this code work?

• subset=“product” will
look for duplicates in the
product column (index 0, 1,
and 2 for “Dairy”)
• keep=“last” will keep the
final duplicate row, and
drop the rest
• ignore_index=True will
reset the index so there are
no gaps
 IDENTIFYING MISSING
DATA
You can identify missing data by column using the .isna()
and .sum() methods
• The .info() method can also help identify null values
The .isna() method returns a
DataFrame with Boolean
values (True for NAs, False
This is a for others)
Series The .sum() method adds these for
each column (True=1, False=0)
and returns the summarized
results

The difference between the total

entries and non-null values for
each column gives you the
missing values in each
 HANDLING MISSING
DATA
Like with Series, the .dropna() and .fillna() methods let you handle
missing data
in a DataFrame by either removing them or replacing them with
other values

Use a dictionary
to specify a
value for each
column

This drops any Use subset to drop

row rows with missing
with missing values in specified
values columns
 FILTERING
DATAFRAMES
You can filter the rows in a DataFrame by passing a logical
test into the .loc[]
accessor, just like filtering a Series or a NumPy array

This filters the retail_df

DataFrame and only returns rows
where the date is equal to
“2016-10-28”
 FILTERING
DATAFRAMES
You can filter the columns in a DataFrame by passing them
into the .loc[]
accessor as a list or a slice

Row Column
filter filter This filters the retail_df
DataFrame to the columns
selected, and only returns rows
where the date is equal to
“2016-10-28”
 FILTERING
DATAFRAMES
You can apply multiple filters by joining the logical tests with
an “&” operator
• Try creating a Boolean mask for creating filters with complex logic

The Boolean mask here is

filtering the DataFrame for
rows where the family is
“CLEANING” or “DAIRY”, and
the sales are greater than 0
 PRO TIP:
QUERY
The .query() method lets you use SQL-like syntax to filter
DataFrames
• You can specify any number of filtering conditions by using the “and” &
“or” keywords

This query filters rows where the

family is “CLEANING” or
“DAIRY”, and the sales are
greater than 0
Note that you don’t need to call
the DataFrame name repeatedly,
saving keystrokes and making the
filter easier to interpret
 PRO TIP:
QUERY
The .query() method lets you use SQL-like syntax to filter
DataFrames
• You can specify any number of filtering conditions by using the “and” &
“or” keywords
• You can reference variables by using the “@” symbol

This query filters rows where the

family is “CLEANING” or “DAIRY”,
and the sales are greater than
the avg_sales value (from the
variable!)

… output
truncated
 SORTING DATAFRAMES BY
INDICES
You can sort a DataFrame by its indices using
the .sort_index() method
• This sorts rows (axis=0) by default, but you can specify axis=1 to
sort the columns
This creates a sample DataFrame by
filtering rows for the 3 specified
product families, and grabbing 5
random rows

This sorts the sample DataFrame

in
descending order by its row index
+
(it sorts in ascending order by
default)

-
SORTING DATAFRAMES BY
INDICES
You can sort a DataFrame by its indices using
the .sort_index() method
• This sorts rows (axis=0) by default, but you can specify axis=1 to
sort the columns

Remember that DataFrame

methods don’t sort in place by
default, allowing you to chain
multiple methods together

This sorts the sample DataFrame

in ascending order by its column
- + index, and modifies the
underlying values
SORTING DATAFRAMES BY
VALUES
You can sort a DataFrame by its values using
the .sort_values() method
• You can sort by a single column or by multiple columns

This sorts the sample DataFrame

by the values in the store_nbr
- column in ascending order by
default

This sorts the sample DataFrame by the

values in the family column in ascending
- + order, then by the values in the sales
column in descending order within each
family
+ -
 RENAMING
COLUMNS
Rename columns in place via assignment using the
“columns” property

Simply assign a list with the new

column names using the columns
property

Use a list comprehension to

clean or standardize column
titles using methods like .upper()
 RENAMING
COLUMNS
You can also rename columns with
the .rename() method

Use a dictionary to map the

new column names to the
old names

Note that the .rename()

method doesn’t rename in
place by default, so you can
chain methods together

Use lambda functions to

clean or standardize column
titles using methods
like .upper()
 REORDERING
COLUMS
Reorder columns with the .reindex() method when sorting
won’t suffice

Pass a list of the existing columns in

their desired order, and specify
axis=1
 ARITHMETIC COLUMN
CREATION
You can create columns with arithmetic by assigning them
Series operations
• Simply specify the new column name and assign the operation of interest

This creates a new tax_amount column equal to sales *

0.05
This creates a new total column equal to sales +
tax_amount

The new columns are added to

the end of the DataFrame by
default
 BOOLEAN COLUMN
CREATION
You can create Boolean columns by assigning them
a logical test

This creates a new taxable_category

column with Boolean values – True if the
family is not “BABY CARE”, and False if
it is

This creates a new tax_amount column

by
leveraging both Boolean logic &
arithmetic:
If the family is not “BABY CARE”,
then calculate the sales tax (sales *
0.05 * 1), otherwise return zero
(sales * 0.05 * 0)
 PRO TIP: NUMPY
SELECT
NumPy’s select() function lets you create columns based on
multiple conditions
• This is more flexible than NumPy’s where() function or Pandas’ .where()
method
Specify a set of
conditions and outcomes
(choices) for each
condition
Then use [Link] and
pass in the conditions, the
choices, and an optional
default outcome if none of
the conditions are met to
the new Sale_Name
column

The first condition is met,

so
the first choice is
returned
 MAPPING VALUES TO
COLUMNS
The .map() method maps values to a column or an entire
DataFrame
• You can pass a dictionary with existing values as the keys, and new
values as the values

This creates a new Vegan?

column by mapping the
dictionary keys to the
values in the product
column and returning the
corresponding dictionary
The dictionary keys will be values in each row
mapped
to the values in the column
selected

Key Value
s s
 MAPPING VALUES TO
COLUMNS
The .map() method maps values to a column or an entire
DataFrame
• You can pass a dictionary with existing values as the keys, and new
values as the values
• You can apply lambda functions (and others!)
This overwrites the price
column by adding a dollar
sign to each of the previous
values
PRO TIP: COLUMN CREATION WITH
ASSIGN
The .assign() method creates multiple columns at once and
returns a DataFrame
• This can be chained together with other data processing methods
To create a column using .assign(),
simply specify the column name and
assign its values as you normally
would
(arithmetic, Boolean logic, mapping,
etc.)

To create multiple columns

using .assign(), simply separate them
using commas
Note that this is chained
with .query() at the end to filter the
DataFrame once the new columns
are created
PRO TIP: COLUMN CREATION WITH
ASSIGN
How does this code
work?

Let’s create some columns for the sample_df

DataFrame!
PRO TIP: COLUMN CREATION WITH
ASSIGN
How does this code
work?

First, a column called

tax_amount:
• Equal to sales * 0.05
• Rounded to 2 decimals
• Starting with “$”
PRO TIP: COLUMN CREATION WITH
ASSIGN
How does this code
work?

Next, a column called

on_promotion_flag:
• If onpromotion > 0 return True
• Otherwise, return False
PRO TIP: COLUMN CREATION WITH
ASSIGN
How does this code
work?

Next, a last column called year:

• Equal to the first 4
characters on the date
column
• Converted to an integer
PRO TIP: COLUMN CREATION WITH
ASSIGN
How does this code
work?

Finally, return the DataFrame and filter

it:
• For rows where family is
“DAIRY”
 REVIEW: PANDAS DATA
TYPES
Pandas data types mostly expand on their base Python and
NumPy equivalents
Numeri Object /
c: Text:
bool Boolean True/False 8 object Any Python object
int64 (default) Whole numbers 8, 16, 32, string Only contains strings or text
64
category Maps categorical data to a numeric array for
float64 (default) Decimal numbers 8, 16, 32, efficiency
64
boolean Nullable Boolean 8
True/False Time
Int64 (default) Nullable whole numbers 8, 16, 32, Series:
64
A single moment in time
*Gray = NumPy
Float64 data Nullable
(default) type decimal numbers 32, 64 datetime
(January 4, 2015, 2:00:00 PM)
*Yellow = Pandas data
type The duration between two dates or times
timedelta
(10 days, 3 seconds, etc.)
A span of time
period
(a day, a week, etc.)
 THE CATEGORICAL DATA
TYPE
The Pandas categorical data type stores text data with repeated
values efficiently
• Python maps each unique category to an integer to save space
• As a rule of thumb, only consider this data type when unique categories <
number of rows / 2

These are now stored

0
as
1 integers in the
backend
1

The categorical data type has some quirks during some data manipulation
operations that will
force it back into an object data type , but it’s not something we’ll cover in
depth in this course
 TYPE
CONVERSION
Like Series, you can convert data types in a DataFrame by
using the .astype()
method and specifying the desired data type (if compatible)
This creates a new ‘sales_int’
column by converting ‘sales’ to
integers

You can use the .astype() method

on the entire DataFrame and
pass a dictionary with the
columns as keys and the desired
data type as values
 TYPE
CONVERSION
Like Series, you can convert data types in a DataFrame by
using the .astype()
method and specifying the desired data type (if compatible)

.astype() will return a

ValueError if the data type is
incompatible

But applying cleaning steps

to the data can make it
work
PRO TIP: MEMORY
OPTIMIZATION
DataFrames are stored entirely in memory, so memory
optimization is key in working with large datasets in Pandas

Memory Optimization Best Practices (in order):

1. Drop unnecessary columns (when possible, avoid reading them in at all)
2. Convert object types to numeric or datetime datatypes where possible
3. Downcast numeric data to the smallest appropriate bit size
4. Use the categorical datatype for columns where the number of unique
values < rows / 2

The .memory_usage() method returns the

memory used by each column in a DataFrame
(in bytes), and deep=True provides more
accurate results

PRO TIP: A good rule of thumb is to have

around
5-10 times the RAM as the size of your
DataFrame
The total memory usage is 1,472
bytes
 STEP 1: DROP
COLUMNS
Dropping unnecessary columns is an easy way to free up
significant space
• You may not know which columns are important when reading in a dataset
for the first time
• If you do, you can limit the columns you read in to begin with (more on that
later!)

Note that the id

column
is identical to the
index

By dropping the ‘id’ column, around

40
bytes were freed (~4% of memory
use)

Note that the memory

usage
went down from 1,470
bytes
STEP 2: CONVERTING OBJECT DATA
TYPES
Try to convert object data types to numeric or datetime
whenever possible

Note that the missing values in ‘price’

and ‘students_enrolled’ are not NaN
values

Use memory_usage=“deep”
with the .info() method to
get total memory usage
along with the column Text data, usually including
data types dates, is read in as an object
by default

Numeric data is usually read in as

64-bit (like ‘class_level’) but errors in
the data can cause it to be read in
as an object
STEP 2: CONVERTING OBJECT DATA
TYPES
Try to convert object data types to numeric or datetime
whenever possible

Note that by converting ‘start_date’

to a datetime data type, around 295
bytes were freed (~20% of memory
use)
On the other hand, nothing changed
by
converting ‘title’ to a string data
type
STEP 2: CONVERTING OBJECT DATA
TYPES
Try to convert object data types to numeric or datetime
whenever possible

Note that additional methods were

chained to
convert ‘price’ and ‘students_enrolled’ to
floats:
• “-” was replaced by NaN values on
both
• “$” was stripped on ‘price’

This is now only 619

bytes!
STEP 3: DOWNCAST NUMERIC
DATA
Integers and floats are cast as 64-bit by default to handle any
possible value, but
you can downcast numeric data to a smaller bit size to save
space if possible
• 8-bits = -128 to 127
• 16-bits = -32,768 to 32,767
• 32-bits = -2,147,483,648 to 2,147,483,647
• 64-bits = -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Based on the ranges above, these

columns can be downcast as
follows:
• ‘class_level’ to Int8
• ‘price’ to Int16
• ‘students_enrolled’ to Int16
STEP 3: DOWNCAST NUMERIC
DATA
Integers and floats are cast as 64-bit by default to handle any
possible value, but
you can downcast numeric data to a smaller bit size to save
space if possible

Note that, since these are Pandas’

Nullable data types, the NumPy
NaN values are now Pandas NA
values

This is now only 539

bytes!
STEP 4: USING CATEGORICAL DATA
TYPES
Use the categorical data type if you have string columns where
the number of
unique values is less than half of the total number of rows

In this case, there are five unique

categories in five rows, so this just
added overhead
This went up to 716 (the demo will show the memory
bytes! reduction)
 RECAP: MEMORY
OPTIMIZATION
BEFORE AFTER

63% less
memory!
 A NOTE ON
EFFICIENCY
“Premature optimization is the root of all evil” – Don Knuth

• Data analysis is an iterative process

• It’s normal to not know the best or most efficient version of a dataset from the
beginning
• Once you understand the data and have an analysis path, then you can optimize

• Efficiency’s importance depends on the use case

• If all you need is a quick analysis, fully optimizing your code will only waste time
• If you’re building a pipeline that will run frequently, efficiency & optimization are
critical

• Build efficient habits & workflows

• As you work more with Python and Pandas, take time to review your code and
note areas for improvement – you’ll be able to incorporate better practices next
time!
 KEY
TAKEAWAYS
Pandas DataFrames are data tables with rows
& columns
• They are technically collections of Pandas Series that share an index, and are the primary data
structure that data analysts work with in Python

Use exploration methods to quickly understand the data in

a DataFrame
• The head, tail, describe, and info methods let you get a glimpse of the data and its characteristics
to identify the cleaning steps needed

You can easily filter, sort, and modify DataFrames with

methods & functions
• DataFrames rows & columns can be sorted by index or values, and filtered using multiple
conditions
• Columns can be created with arithmetic or complex logic, and multiple columns can be created
with .assign()
Memory optimization is critical in working with large
datasets
• Once in
you Pandas
understand the data, dropping unnecessary columns, converting object data types,
downcasting numerical data types, and using the categorical data types when possible will help
save significant memory
 AGGREGATING &
RESHAPING
In this section we’ll cover aggregating & reshaping DataFrames,
including grouping
columns, performing aggregation calculations, and pivoting &
unpivoting data

TOPICS WE’LL GOALS FOR THIS SECTION:

COVER:
• Group DataFrames by one or more
columns and calculate aggregate
statistics by group
• Learn to access multi-index DataFrames
and reset them to return to a single index

• Create Excel-style PivotTables to

summarize data

• Melt “wide” tables of data into a “long”

tabular form
 AGGREGATING
DATAFRAMES
You can aggregate a DataFrame column by using aggregation
methods (like Series!)

But what if you want multiple aggregate statistics, or summarized statistics by groups?
 GROUPING
DATAFRAMES
Grouping a DataFrame allows you to aggregate the data at a
different level
• For example, transform daily data into monthly, roll up transaction level
data by store, etc.

Original DataFrame Grouped by Family

Raw sales data by Total sales by

transaction family
 GROUPING
DATAFRAMES
To group data, use the .groupby() method and specify a
column to group by
• The grouped column becomes the index by default

Just specify the columns to group

by

This returns a “groupby”

object

To return the groups created, you

need to calculate aggregate statistics:

Using single 1. Specify the column for the

brackets calculations
returns a Series 2. Apply an aggregation method
 GROUPING
DATAFRAMES
To group data, use the .groupby() method and specify a
column to group by
• The grouped column becomes the index by default

Just specify the columns to group

by

This returns a “groupby”

object

To return the groups created, you

need to calculate aggregate statistics:

Using double 1. Specify the column for the

brackets returns a calculations
DataFrame 2. Apply an aggregation method
GROUPING BY MULTIPLE
COLUMNS
You can group by multiple columns by passing the list of columns
into .groupby()
• This creates a multi-index object with an index for each column the data was
grouped by

This returns the sum of sales for

each
combination of ‘family’ and
‘store_nbr’

This is a multi-index
DataFrame
GROUPING BY MULTIPLE
COLUMNS
You can group by multiple columns by passing the list of columns
into .groupby()
• This creates a multi-index object with an index for each column the data was
grouped by
• Specify as_index=False to prevent the grouped columns from becoming
indices

This still returns the sum of sales

for each combination of ‘family’
and ‘store_nbr’, but keeps a
numeric index
 ASSIGNMENT: GROUPBY MULTIPLE
COLUMNS
Results Preview
NEW MESSAGE
August 4, 2022

From: Phoebe Product (Merchandising)

Subject: Transactions by Store and
Month
Hi there, it’s Phoebe again!

Taking this analysis a layer deeper...

Can you get me the total transactions by store and

month?

Sort the table from first month to last, then

by highest transactions to lowest within
each month.

I’ll likely analyze this data further in a

dashboard software, but
this will help me set up special seasonal
displays.
section04_aggregations.i
pynb
Thanks!
 SOLUTION: GROUPBY MULTIPLE
COLUMNS
Solution Code
NEW MESSAGE
August 4, 2022

From: Phoebe Product (Merchandising)

Subject: Transactions by Store and
Month
Hi there, it’s Phoebe again!

Taking this analysis a layer deeper...

Can you get me the total transactions by store and

month?

Sort the table from first month to last, then

by highest transactions to lowest within
each month.

I’ll likely analyze this data further in a

dashboard software, but
this will help me set up special seasonal
displays.
section04_aggregations.i
pynb
Thanks!
 MULTI-INDEX
DATAFRAMES
Multi-index DataFrames are generally created through
aggregation operations
• They are stored as a list of tuples, with an item for each layer of the index
ACCESSING MULTI-INDEX
DATAFRAMES
The .loc[] accessor lets you access multi-index DataFrames in
different ways:

1. Access rows via the outer index only

All rows from

“AUTOMOTIVE
” to “BEAUTY”
(inclusive)
All rows for “AUTOMOTIVE”
(note that ‘family’ is
dropped)
ACCESSING MULTI-INDEX
DATAFRAMES
The .loc[] accessor lets you access multi-index DataFrames in
different ways:

2. Access rows via the outer & inner indices

All rows for “AUTOMOTIVE” and “12”

(note that ‘family’ and ‘store_nbr’ are
dropped)

All rows from “AUTOMOTIVE” and

“11” to “BEAUTY” and “11”
(inclusive)

….All rows in Automotive and Baby Care

MODIFYING MULTI-INDEX
DATAFRAMES
There are several ways to modify multi-index
DataFrames:
Reset the index Swap the index Drop an index
Moves the index levels level level
back to DataFrame Changes the Drops an index
columns hierarchy for the level from the
index levels DataFrame entirely

PRO TIP: In most cases it’s best to reset the

index and Be careful! You may
lose important
avoid multi-index DataFrames – they’re not very
information
intuitive!
 THE AGG
METHOD
The .agg() method lets you perform multiple aggregations on a
“groupby” object

The .agg() method will

perform the aggregation
on all compatible
columns, in this case
‘sales’ and
‘onpromotion’, which
are numeric
 MULTIPLE
AGGREGATIONS
You can perform multiple aggregations by passing a list of
aggregation functions

This creates two levels in the column

index, one for the original column names,
and another for the aggregations
performed
 MULTIPLE
AGGREGATIONS
You can perform specific aggregations by column by passing a
dictionary with column names as keys, and lists of aggregation
functions as values

The calculates the sum and mean for

‘sales’ and the min and max for
‘onpromotion’
 NAMED
AGGREGATIONS
You can name aggregated columns upon creation to avoid multi-
index columns

Specify the new column name

and assign it a tuple with
the column you want to
aggregate and the
aggregation to perform A single column
index!
 ASSIGNMENT: THE AGG
METHOD
Results Preview
NEW MESSAGE
August 6, 2022
By Store:
From: Chandler Capital (Accounting)
Subject: Bonus Rate and Bonus
Payable
Hey again,

I’m performing some further analysis on our bonuses. By Month:

Can you create a table that has the average
number of days each store hit the target?

Calculate the total bonuses payable to each store

and sort the DataFrame from highest bonus owed
to lowest. By Weekday:
Then do the same for day of week

and month. Thanks!

NOTE: Only the top 5 rows for each DataFrame are

section04_aggregations.ipynb
included here
 SOLUTION: THE AGG
METHOD
Solution Code
NEW MESSAGE
August 6, 2022
By Store:
From: Chandler Capital (Accounting)
Subject: Bonus Rate and Bonus
Payable
Hey again,
By Month:
I’m performing some further analysis on our bonuses.

Can you create a table that has the average

number of days each store hit the target?

Calculate the total bonuses payable to each store

and sort the DataFrame from highest bonus owed By Weekday:
to lowest.

Then do the same for day of week

and month. Thanks!

section04_aggregations.ipynb
 PRO TIP:
TRANSFORM
The .transform() method can be used to perform aggregations
without reshaping
• This is useful for calculating group-level statistics to perform row-level
analysis
This uses .assign() to create a new DataFrame column,
and .transform() calculates the sum of ‘sales’ by
‘store_nbr’ and applies the corresponding value to
each row

The value for rows with store 48 is the

same!
 PIVOT
TABLES
The .pivot_table() method let’s you create Excel-style
pivot tables

Unlike Excel, Pandas pivot tables don’t have

a “filter” argument”, but you can filter your
DataFrame before pivoting to return a
filtered pivot table
 PIVOT TABLE
ARGUMENTS
The .pivot_table() method has these arguments:
• index: returns a row index with distinct values from the specified
column
• columns: returns a column index with distinct values from the
specified column
• values: the column, or columns, to perform the aggregations on
• aggfunc: defines the aggregation function, or functions, to perform
on the “values”
• margins: returns row and column totals This
whenreturns distinct
True ‘family’
(False byvalues as
default)
rows, distinct ‘store_nbr’ values as
columns, and sums the ‘sales’ for each
combination of ‘family’ and ‘str_nbr’ as
the values
 PIVOT TABLE
ARGUMENTS
The .pivot_table() method has these arguments:
• index: returns a row index with distinct values from the specified
column
• columns: returns a column index with distinct values from the
specified column
• values: the column, or columns, to perform the aggregations on
• aggfunc: defines the aggregation function, or functions, to perform
on the “values”
• margins: returns row and column totals when True (False by default)

Specifying margins=True adds row

and column totals based on the
aggregation
(the corner represents the grand
total)
MULTIPLE AGGREGATION
FUNCTIONS
Multiple aggregation functions can be passed to the
“aggfunc” argument
• The new values are added as additional columns

The functions are passed as a

tuple

There is a column for each store_nbr min

and max (this can create a very wide dataset
very quickly)
MULTIPLE AGGREGATION
FUNCTIONS
Multiple aggregation functions can be passed to the
“aggfunc” argument
• The new values are added as additional columns

Use a dictionary to
apply specific
functions to specific
columns
 PIVOT TABLES VS.
GROUPBY
If the column argument isn’t specified in a pivot table, it will return
a table that’s
identical to one grouped by the index columns
 PIVOT TABLES VS.
GROUPBY
If the column argument isn’t specified in a pivot table, it will return
a table that’s
identical to one grouped by the index columns

PRO TIP: Use groupby if you don’t

need columns in the pivot, as you
can use named aggregations to
flatten the column index
 MEL
T
The .melt() method will unpivot a DataFrame, or convert columns
into rows

How does this code work?

• The original column
names (country, 2000, etc.)
are turned into a single
“variable” column
• The values for each
original column are
placed on a single
“value” column next to
Note thatits
the corresponding
resulting table isn’t perfect,
as .melt()column
unpivotsname
a DataFrame around
its index, while ideally you’d want to pivot
this around the country values
 MEL
T
Use the “id_vars” argument to specify the column to unpivot the
DataFrame by

How does this code work?

• The “id_vars” column
(country) is kept in the
DataFrame
• The rest of the DataFrame
columns are “melted” around
the countries in matching
variable/value pairs
 MEL
T
You can also select the columns to melt and name the “variable” &
“value” columns

How does this code work?

• id_vars melts the DataFrame
around the “country” column
• value_vars selects 2001,
2002, and 2003 as the
columns to melt (omitting
2000)
• var_name & value_name
set “year” and “GDP” as
column names instead of
“variable” and “value”
 KEY
TAKEAWAYS
Use the .groupby() method to aggregate a DataFrame by
specific
• You columns
also need to specify a column of values to aggregate and an aggregate
function

Avoid working with multi-index DataFrames

whenever possible
• Multi-index DataFrames are created by default when grouping by more than one column
• It’s worth knowing how to access multi-index DataFrames, but it’s advised to avoid them by resetting
the index

Use the .agg() method to specify multiple aggregation functions

when grouping
• Named aggregations allow you to set intuitive column names and prevent multi-index
columns

The .pivot_table() and .melt() methods let you pivot and

unpivot DataFrames
• Pandas pivot tables work just like Excel, and make data “wide” by converting unique row values
into columns
• With .melt(), you can make “wide” tables “long” in order to analyze the data traditionally
 IMPORTING & EXPORTING
DATA
In this section we’ll cover importing & exporting data in Pandas,
including reading in data
from flat files and SQL tables, applying processing steps on import, and
writing back out

TOPICS WE’LL GOALS FOR THIS SECTION:

COVER:
• Apply data processing steps like
converting data types, setting an index,
and handling missing data during the
import process
• Read & write data from different flat files
and
multiple Excel worksheets

• Connect to SQL Databases and create

DataFrames from custom SQL queries
 READ_CSV
REVISITED
The read_csv() function only needs a file path to read in a file, but
it also has many capabilities for preprocessing the data using other
arguments:
• file = “path/[Link]” – file path & name to read in (can also be a URL)
• sep = “/” – character used to separate each column of values (default is comma)
• header = 0 – row number to use as the column names (default is “infer”)
• names = [“date”, “sales”] – list of column names to override the existing ones
• index_col = “date” – column name to be used as the DataFrame index
• usecols = [“date”, “sales”] – list of columns to keep in the DataFrame
• dtype = {“date”: “datetime64”, “sales” : “Int32”} – dictionary with column names and
data types
• parse_dates = True – converts date strings into datetimes when True
• infer_datetime_format = True – makes parsing dates more efficient
• na_values = [“-”, “#N/A!”] – strings to recognize as NaN values
• nrows = 100 – number of rows to read in
• skiprows = [0, 2] – line numbers to skip (accepts lambda functions)
For a full list of arguments, visit: • converters = {“sales”: lambda x: f"${x}") – dictionary with column names and
 COLUMN
NAMES
Pandas will try to infer the column names in your file by default,
but there are several options to override this behavior:
• Specify header=None to keep all the rows in the file as data, and use integers
as column names
• Specify header=0 and use the names argument to pass a list of desired
column names

The first row is

kept in the
DataFrame

The first row is used for

the column headers by
default, and a suffix of
“.1” was added to avoid The new
duplicates column
names are
used
 SETTING AN
INDEX
You can set the index for the DataFrame with the
“index_col” argument
• Pass a list of column names to create a multi-index DataFrame (not
recommended!)
• Specify parse_dates=True to convert index date column to a
datetime data type

This is a synonym for

datetime64
 SELECTING
COLUMNS
You can select the columns to read in with the
“use_cols” argument
• This can save a lot of processing time and memory
 SELECTING
ROWS
You can select the rows to read in from the top with the “nrows”
argument, and specify any rows to skip with “skiprows”

This skips these

four odd-
numbered rows

Reading in the first few

rows is great for peeking
This lambda function
at big files before reading
skips all odd-numbered
them in completely
rows
 MISSING
VALUES
You can specify strings (or other values) to treat as missing
values with the
“na_values” argument
• They are replaced with NumPy NaN values
 PARSING
DATES
Dates are read in as object data types by default, but you can parse
dates with the
“parse_dates” argument to convert them to datetime64
• Specifying infer_datetime_format=True will speed up the date parsing
 DATA
TYPES
You can set the data type for each column with the “dtype”
argument by passing in
a dictionary with column names as keys and desired data types as
values
• Get your data into its most efficient format from the start!
 PRO TIP:
CONVERTERS
You can apply functions to columns of data by using the
“converters” argument
• Pass a dictionary with the column names as keys and the functions as
values

Assign the functions to

variables to make the code
easier to read
 READING TXT
FILES
The read_csv() function can also read in .txt files, and other
types of flat files
• Simply use the “sep” argument to specify the delimiter
• You can also read in .tsv (tab separated values) files and URLs pointing to
text files

This represents a
tab

Pandas is looking
for comma
separators
 READING EXCEL
FILES
The read_excel() function is used to read in Excel files in Pandas
• You can specify worksheets by passing the sheet name or position to the
“sheet_name” argument

0-
indexed!
 PRO TIP: APPENDING
SHEETS
You can use Pandas’ concat() function to append data from
multiple sheets
• We’ll cover combining DataFrames in the next section, but this is a
sneak peek!
sheet_name=None makes Pandas read
every sheet and store them as a
dictionary, which is appended
using .concat()
ignore_index=True makes sure each
row has a unique index

The DataFrame has

the data from both
sheets!
 EXPORTING TO FLAT
FILES
The to_csv() and to_excel() functions let you export
DataFrames to flat files

PRO
TIP:

Multiple
tabs!
CONNECTING TO SQL
DATABASES
The SQLAlchemy library lets you connect to SQL
databases
• All major SQL implementations can be accessed (MySQL, Oracle,
MS-SQL, etc.)
The create_engine() function creates the
database connection, in this case to a
local database, and
inspect().get_table_names() is used to
view the database contents

This example shows a connection to an

Oracle database by providing a username
and password, as well as the server
location
 QUERYING SQL
DATABASES
The read_sql() function lets you create a DataFrame from a
SQL query

This query is being performed on the SQL

database connection set up previously,
and assigned to a DataFrame named
league_df

PRO TIP: In most cases, it’s more

efficient to perform tasks like joins
and filters in your database –
databases are designed for these
tasks and tend to have more
resources than your local machine
 WRITING TO SQL
DATABASES
The to_sql() function lets you create a SQL table from your
DataFrame
• This lets you clean data using Pandas before storing it in a SQL
Database
• You will likely need permission from your database administrator
This creates a new table called
(DBA) to do this “pl_games”
on the existing database connection
• if_exists=“append” will append
the data to the table if it
already exists
• index=False is leaving the default
index in the SQL table, but you can
select a column from the
DataFrame to use instead

Now you can query

the table you
created!
 ADDITIONAL
FORMATS
Pandas has functions to read & write these
additional formats
• These functions work similarly to read_csv() and to_csv()

Short for Java Script Open Notation, a common format

JSON read_json to_json
returned by APIs
(similar to a nested dictionary in Python)
Feather is a relatively new file format - designed to read,
Feather read_feather to_feather
write, and store DataFrames as efficiently as possible

Can be used to read and write data in webpage formats,

HTML read_html to_html
which makes it nice for scraping tables on sites like
Wikipedia
A serialized storage format that allows for quick
Pickle read_pickle to_pickle
reproduction of DataFrames. Mostly used in Machine
Learning workflows
You can convert many Python data types to DataFrames, and
Python [Link] to_dict
can convert them to Python dictionaries with to_dict()
Dictionary
 KEY
TAKEAWAYS
The read_csv() function is capable of significant data
preprocessing
• Once you’ve cleaned your data, take the time to incorporate those steps into the import phase to
save time and
memory the next time you work with the it

Pandas lets you easily read in & write to flat files like
CSV• and Excel
Just make sure to specify the correct column delimiter for .tsv and .txt files, and the desired Excel
worksheet

You can create a DataFrame from any

SQL• query
SQLAlchemy lets you create a connection to any SQL database, and the read_sql() function lets
you pass a
query into the database to create a DataFrame – this is what real-world Pandas workflows are
built on!
Many additional formats can be read in
using
• Pandas
Check the documentation or Google some examples when encountering a format you haven’t
worked with