Lesson

Pandas Crash Course

Learn Pandas Crash Course in SQLPad's Data Science in Action course with practical examples and guided lessons.

This course is based on materials from Pandas 10-minute guide and is utilized under the terms of the BSD 3-Clause License.

This is a short introduction to pandas, geared mainly for new users.

Basic data structures in pandas

Pandas provides two types of classes for handling data:

  1. Series: a one-dimensional labeled array holding data of any type such as integers, strings, Python objects etc.
  2. DataFrame: a two-dimensional data structure that holds data like a two-dimension array or a table with rows and columns.

Object creation

Creating a Series by passing a list of values, letting pandas create a default RangeIndex.

import pandas as pd
s = pd.Series([1, 3, 5, np.nan, 6, 8])
s

Creating a DataFrame by passing a NumPy array with a datetime index using ---date_range and labeled columns:

dates = pd.date_range("20130101", periods=6)
dates
df = pd.DataFrame(np.random.randn(6, 4), index=dates, columns=list("ABCD"))
df

Creating a DataFrame by passing a dictionary of objects where the keys are the column labels and the values are the column values.

df2 = pd.DataFrame(
    {
        "A": 1.0,
        "B": pd.Timestamp("20130102"),
        "C": pd.Series(1, index=list(range(4)), dtype="float32"),
        "D": np.array([3] * 4, dtype="int32"),
        "E": pd.Categorical(["test", "train", "test", "train"]),
        "F": "foo",
    }
)
df2

The columns of the resulting DataFrame have different dtypes <basics.dtypes>:

df2.dtypes

If you're using IPython, tab completion for column names (as well as public attributes) is automatically enabled. Here's a subset of the attributes that will be completed:

@verbatim In [1]: df2.<TAB> # noqa: E225, E999 df2.A df2.bool df2.abs df2.boxplot df2.add df2.C df2.add_prefix df2.clip df2.add_suffix df2.columns df2.align df2.copy df2.all df2.count df2.any df2.combine df2.append df2.D df2.apply df2.describe df2.applymap df2.diff df2.B df2.duplicated

As you can see, the columns A, B, C, and D are automatically tab completed. E and F are there as well; the rest of the attributes have been truncated for brevity.

Viewing data

Use DataFrame.head and DataFrame.tail to view the top and bottom rows of the frame respectively:

df.head()
df.tail(3)

Display the :attr:DataFrame.index or :attr:DataFrame.columns:

df.index
df.columns

Return a NumPy representation of the underlying data with DataFrame.to_numpy without the index or column labels:

df.to_numpy()

NumPy arrays have one dtype for the entire array while pandas DataFrames have one dtype per column. When you call DataFrame.to_numpy, pandas will find the NumPy dtype that can hold all of the dtypes in the DataFrame. If the common data type is object, DataFrame.to_numpy will require copying data.

df2.dtypes
df2.to_numpy()

~DataFrame.describe shows a quick statistic summary of your data:

df.describe()

Transposing your data:

df.T

DataFrame.sort_index sorts by an axis:

df.sort_index(axis=1, ascending=False)

DataFrame.sort_values sorts by values:

df.sort_values(by="B")

Selection

While standard Python / NumPy expressions for selecting and setting are intuitive and come in handy for interactive work, for production code, we recommend the optimized pandas data access methods, DataFrame.at, DataFrame.iat, DataFrame.loc and DataFrame.iloc.

Getitem

For a DataFrame, passing a single label selects a columns and yields a Series equivalent to df.A:

df["A"]

For a DataFrame, passing a slice : selects matching rows:

df[0:3]
df["20130102":"20130104"]

Selection by label

Selecting a row matching a label:

df.loc[dates[0]]

Selecting all rows (:) with a select column labels:

df.loc[:, ["A", "B"]]

For label slicing, both endpoints are included:

df.loc["20130102":"20130104", ["A", "B"]]

Selecting a single row and column label returns a scalar:

df.loc[dates[0], "A"]

For getting fast access to a scalar (equivalent to the prior method):

df.at[dates[0], "A"]

Selection by position

Select via the position of the passed integers:

df.iloc[3]

Integer slices acts similar to NumPy/Python:

df.iloc[3:5, 0:2]

Lists of integer position locations:

df.iloc[[1, 2, 4], [0, 2]]

For slicing rows explicitly:

df.iloc[1:3, :]

For slicing columns explicitly:

df.iloc[:, 1:3]

For getting a value explicitly:

df.iloc[1, 1]

For getting fast access to a scalar (equivalent to the prior method):

df.iat[1, 1]

Boolean indexing

Select rows where df.A is greater than 0.

df[df["A"] > 0]

Selecting values from a DataFrame where a boolean condition is met:

df[df > 0]

Using ---~Series.isin method for filtering:

df2 = df.copy()
df2["E"] = ["one", "one", "two", "three", "four", "three"]
df2
df2[df2["E"].isin(["two", "four"])]

Setting

Setting a new column automatically aligns the data by the indexes:

s1 = pd.Series([1, 2, 3, 4, 5, 6], index=pd.date_range("20130102", periods=6))
s1
df["F"] = s1

Setting values by label:

df.at[dates[0], "A"] = 0

Setting values by position:

df.iat[0, 1] = 0

Setting by assigning with a NumPy array:

df.loc[:, "D"] = np.array([5] * len(df))

The result of the prior setting operations:

df

A where operation with setting:

df2 = df.copy()
df2[df2 > 0] = -df2
df2

Missing data

For NumPy data types, np.nan represents missing data. It is by default not included in computations.

Reindexing allows you to change/add/delete the index on a specified axis. This returns a copy of the data:

df1 = df.reindex(index=dates[0:4], columns=list(df.columns) + ["E"])
df1.loc[dates[0] : dates[1], "E"] = 1
df1

DataFrame.dropna drops any rows that have missing data:

df1.dropna(how="any")

DataFrame.fillna fills missing data:

df1.fillna(value=5)

isna gets the boolean mask where values are nan:

pd.isna(df1)

Operations

Stats

Operations in general exclude missing data.

Calculate the mean value for each column:

df.mean()

Calculate the mean value for each row:

df.mean(axis=1)

Operating with another Series or DataFrame with a different index or column will align the result with the union of the index or column labels. In addition, pandas automatically broadcasts along the specified dimension and will fill unaligned labels with np.nan.

s = pd.Series([1, 3, 5, np.nan, 6, 8], index=dates).shift(2)
s
df.sub(s, axis="index")

User defined functions

DataFrame.agg and DataFrame.transform applies a user defined function that reduces or broadcasts its result respectively.

df.agg(lambda x: np.mean(x) * 5.6)
df.transform(lambda x: x * 101.2)

Value Counts

s = pd.Series(np.random.randint(0, 7, size=10))
s
s.value_counts()

String Methods

Series is equipped with a set of string processing methods in the str attribute that make it easy to operate on each element of the array, as in the code snippet below.

s = pd.Series(["A", "B", "C", "Aaba", "Baca", np.nan, "CABA", "dog", "cat"])
s.str.lower()

Merge

Concat

pandas provides various facilities for easily combining together Series`` andDataFrame` objects with various kinds of set logic for the indexes and relational algebra functionality in the case of join / merge-type operations.

Concatenating pandas objects together row-wise with

concat

df = pd.DataFrame(np.random.randn(10, 4))
df

# break it into pieces
pieces = [df[:3], df[3:7], df[7:]]

pd.concat(pieces)

Adding a column to a DataFrame is relatively fast. However, adding a row requires a copy, and may be expensive. We recommend passing a pre-built list of records to the DataFrame constructor instead of building a DataFrame by iteratively appending records to it.

Join

merge enables SQL style join types along specific columns.

left = pd.DataFrame({"key": ["foo", "foo"], "lval": [1, 2]})
right = pd.DataFrame({"key": ["foo", "foo"], "rval": [4, 5]})
left
right
pd.merge(left, right, on="key")

merge on unique keys:

left = pd.DataFrame({"key": ["foo", "bar"], "lval": [1, 2]})
right = pd.DataFrame({"key": ["foo", "bar"], "rval": [4, 5]})
left
right
pd.merge(left, right, on="key")

Grouping

By "group by" we are referring to a process involving one or more of the following steps:

  • Splitting the data into groups based on some criteria
  • Applying a function to each group independently
  • Combining the results into a data structure
df = pd.DataFrame(
    {
        "A": ["foo", "bar", "foo", "bar", "foo", "bar", "foo", "foo"],
        "B": ["one", "one", "two", "three", "two", "two", "one", "three"],
        "C": np.random.randn(8),
        "D": np.random.randn(8),
    }
)
df

Grouping by a column label, selecting column labels, and then applying the

~pandas.core.groupby.DataFrameGroupBy.sum function to the resulting groups:

df.groupby("A")[["C", "D"]].sum()

Grouping by multiple columns label forms MultiIndex.

df.groupby(["A", "B"]).sum()

Reshaping

Stack

arrays = [
   ["bar", "bar", "baz", "baz", "foo", "foo", "qux", "qux"],
   ["one", "two", "one", "two", "one", "two", "one", "two"],
]
index = pd.MultiIndex.from_arrays(arrays, names=["first", "second"])
df = pd.DataFrame(np.random.randn(8, 2), index=index, columns=["A", "B"])
df2 = df[:4]
df2

The ~DataFrame.stack method "compresses" a level in the DataFrame's columns:

stacked = df2.stack(future_stack=True)
stacked

With a "stacked" DataFrame or Series (having a MultiIndex as the index), the inverse operation of ~DataFrame.stack is ~DataFrame.unstack, which by default unstacks the last level:

stacked.unstack()
stacked.unstack(1)
stacked.unstack(0)

Pivot tables

df = pd.DataFrame(
    {
        "A": ["one", "one", "two", "three"] * 3,
        "B": ["A", "B", "C"] * 4,
        "C": ["foo", "foo", "foo", "bar", "bar", "bar"] * 2,
        "D": np.random.randn(12),
        "E": np.random.randn(12),
    }
)
df

pivot_tablepivots aDataFrame` specifying the values, index and columns

pd.pivot_table(df, values="D", index=["A", "B"], columns=["C"])

Time series

pandas has simple, powerful, and efficient functionality for performing resampling operations during frequency conversion (e.g., converting secondly data into 5-minutely data). This is extremely common in, but not limited to, financial applications.

rng = pd.date_range("1/1/2012", periods=100, freq="S")
ts = pd.Series(np.random.randint(0, 500, len(rng)), index=rng)
ts.resample("5Min").sum()

Series.tz_localize localizes a time series to a time zone:

rng = pd.date_range("3/6/2012 00:00", periods=5, freq="D")
ts = pd.Series(np.random.randn(len(rng)), rng)
ts
ts_utc = ts.tz_localize("UTC")
ts_utc

Series.tz_convert converts a timezones aware time series to another time zone:

ts_utc.tz_convert("US/Eastern")

Adding a non-fixed duration (~pandas.tseries.offsets.BusinessDay) to a time series:

rng
rng + pd.offsets.BusinessDay(5)

Categoricals

pandas can include categorical data in a DataFrame.

df = pd.DataFrame(
     {"id": [1, 2, 3, 4, 5, 6], "raw_grade": ["a", "b", "b", "a", "a", "e"]}
)

Converting the raw grades to a categorical data type:

df["grade"] = df["raw_grade"].astype("category")
df["grade"]

Rename the categories to more meaningful names:

new_categories = ["very good", "good", "very bad"]
df["grade"] = df["grade"].cat.rename_categories(new_categories)

Reorder the categories and simultaneously add the missing categories (methods under Series.cat return a new Series by default):

df["grade"] = df["grade"].cat.set_categories(
  ["very bad", "bad", "medium", "good", "very good"]
)
df["grade"]

Sorting is per order in the categories, not lexical order:

df.sort_values(by="grade")

Grouping by a categorical column with observed=False also shows empty categories:

df.groupby("grade", observed=False).size()

CSV

Writing to a csv file: <io.store_in_csv> using DataFrame.to_csv

df = pd.DataFrame(np.random.randint(0, 5, (10, 5)))
df.to_csv("foo.csv")

:Reading from a csv file: <io.read_csv_table> using ---read_csv

pd.read_csv("foo.csv")

Parquet

Writing to a Parquet file:

df.to_parquet("foo.parquet")

Reading from a Parquet file Store using ---read_parquet:

pd.read_parquet("foo.parquet")

Excel

Writing to an excel file using DataFrame.to_excel:

df.to_excel("foo.xlsx", sheet_name="Sheet1")

Reading from an excel file using read_excel:

pd.read_excel("foo.xlsx", "Sheet1", index_col=None, na_values=["NA"])

Gotchas

If you are attempting to perform a boolean operation on a Series or DataFrame you might see an exception like:

if pd.Series([False, True, False]):
  print("I was true")