Profile picture

Co-founder @ RMOTR

Real Python: Pandas Tricks & Features You May Not Know

This is an interactive view of @realpython's post about Pandas (, to allow people play online with the code examples.

Last updated: December 31st, 20182018-12-31Project preview

Python Pandas: Tricks & Features You May Not Know

Pandas is a foundational library for analytics, data processing, and data science. It’s a huge project with tons of optionality and depth.

This tutorial will cover some lesser-used but idiomatic Pandas capabilities that lend your code better readability, versatility, and speed, à la the Buzzfeed listicle.

If you feel comfortable with the core concepts of Python’s Pandas library, hopefully you’ll find a trick or two in this article that you haven’t stumbled across previously. (If you’re just starting out with the library, 10 Minutes to Pandas is a good place to start.)

Note: The examples in this article are tested with Pandas version 0.23.2 and Python 3.6.6. However, they should also be valid in older versions.

In [ ]:
import pandas as pd

def start():
    options = {
        'display': {
            'max_columns': None,
            'max_colwidth': 25,
            'expand_frame_repr': False,  # Don't wrap to multiple pages
            'max_rows': 14,
            'max_seq_items': 50,         # Max length of printed sequence
            'precision': 4,
            'show_dimensions': False
        'mode': {
            'chained_assignment': None   # Controls SettingWithCopyWarning

    for category, option in options.items():
        for op, value in option.items():
            pd.set_option(f'{category}.{op}', value)  # Python 3.6+


If you launch an interpreter session, you’ll see that everything in the startup script has been executed, and Pandas is imported for you automatically with your suite of options:

In [ ]:
In [ ]:

2. Make Toy Data Structures With Pandas’ Testing Module

Hidden way down in Pandas’ testing module are a number of convenient functions for quickly building quasi-realistic Series and DataFrames:

In [ ]:
import pandas.util.testing as tm

tm.N, tm.K = 15, 3  # Module-level default rows/columns
In [ ]:
import numpy as np

In [ ]:
In [ ]:

There are around 30 of these, and you can see the full list by calling dir() on the module object. Here are a few:

In [ ]:
[i for i in dir(tm) if i.startswith('make')]

These can be useful for benchmarking, testing assertions, and experimenting with Pandas methods that you are less familiar with.

3. Take Advantage of Accessor Methods

Perhaps you’ve heard of the term accessor, which is somewhat like a getter (although getters and setters are used infrequently in Python). For our purposes here, you can think of a Pandas accessor as a property that serves as an interface to additional methods.

Pandas Series have three of them:

In [ ]:

Yes, that definition above is a mouthful, so let’s take a look at a few examples before discussing the internals.

.cat is for categorical data, .str is for string (object) data, and .dt is for datetime-like data. Let’s start off with .str: imagine that you have some raw city/state/ZIP data as a single field within a Pandas Series.

Pandas string methods are vectorized, meaning that they operate on the entire array without an explicit for-loop:

In [ ]:
addr = pd.Series([
    'Washington, D.C. 20003',
    'Brooklyn, NY 11211-1755',
    'Omaha, NE 68154',
    'Pittsburgh, PA 15211'

In [ ]:
addr.str.count(r'\d')  # 5 or 9-digit zip?

For a more involved example, let’s say that you want to separate out the three city/state/ZIP components neatly into DataFrame fields.

You can pass a regular expression to .str.extract() to “extract” parts of each cell in the Series. In .str.extract(), .str is the accessor, and .str.extract() is an accessor method:

In [ ]:
regex = (r'(?P<city>[A-Za-z ]+), '      # One or more letters
         r'(?P<state>[A-Z]{2}) '        # 2 capital letters
         r'(?P<zip>\d{5}(?:-\d{4})?)')  # Optional 4-digit extension

addr.str.replace('.', '').str.extract(regex)

This also illustrates what is known as method-chaining, where .str.extract(regex) is called on the result of addr.str.replace('.', ''), which cleans up use of periods to get a nice 2-character state abbreviation.

It’s helpful to know a tiny bit about how these accessor methods work as a motivating reason for why you should use them in the first place, rather than something like addr.apply(re.findall, ...).

Each accessor is itself a bona fide Python class:

  • .str maps to StringMethods.
  • .dt maps to CombinedDatetimelikeProperties.
  • .cat routes to CategoricalAccessor. These standalone classes are then “attached” to the Series class using a CachedAccessor. It is when the classes are wrapped in CachedAccessor that a bit of magic happens.

CachedAccessor is inspired by a “cached property” design: a property is only computed once per instance and then replaced by an ordinary attribute. It does this by overloading the .get() method, which is part of Python’s descriptor protocol.

Note: If you’d like to read more about the internals of how this works, see the Python Descriptor HOWTO and this post on the cached property design. Python 3 also introduced functools.lru_cache(), which offers similar functionality. There are examples all over the place of this pattern, such as in the aiohttp package.

The second accessor, .dt, is for datetime-like data. It technically belongs to Pandas’ DatetimeIndex, and if called on a Series, it is converted to a DatetimeIndex first:

In [ ]:
daterng = pd.Series(pd.date_range('2017', periods=9, freq='Q'))
In [ ]:
In [ ]:
# Second-half of year only
daterng[daterng.dt.quarter > 2]
In [ ]:

The third accessor, .cat, is for Categorical data only, which you’ll see shortly in its own section.

4. Create a DatetimeIndex From Component Columns

Speaking of datetime-like data, as in daterng above, it’s possible to create a Pandas DatetimeIndex from multiple component columns that together form a date or datetime:

In [ ]:
from itertools import product
datecols = ['year', 'month', 'day']

df = pd.DataFrame(list(product([2017, 2016], [1, 2], [1, 2, 3])),
df['data'] = np.random.randn(len(df))
In [ ]:
df.index = pd.to_datetime(df[datecols])

Finally, you can drop the old individual columns and convert to a Series:

In [ ]:
df = df.drop(datecols, axis=1).squeeze()
In [ ]:

The intuition behind passing a DataFrame is that a DataFrame resembles a Python dictionary where the column names are keys, and the individual columns (Series) are the dictionary values. That’s why pd.to_datetime(df[datecols].to_dict(orient='list')) would also work in this case. This mirrors the construction of Python’s datetime.datetime, where you pass keyword arguments such as datetime.datetime(year=2000, month=1, day=15, hour=10).

5. Use Categorical Data to Save on Time and Space

One powerful Pandas feature is its Categorical dtype.

Even if you’re not always working with gigabytes of data in RAM, you’ve probably run into cases where straightforward operations on a large DataFrame seem to hang up for more than a few seconds.

Pandas object dtype is often a great candidate for conversion to category data. (object is a container for Python str, heterogeneous data types, or “other” types.) Strings occupy a significant amount of space in memory:

In [ ]:
colors = pd.Series([
    'mint green',
    'burnt orange',
    'burnt orange',
    'mint green',

import sys

Note: I used sys.getsizeof() to show the memory occupied by each individual value in the Series. Keep in mind these are Python objects that have some overhead in the first place. (sys.getsizeof('') will return 49 bytes.)

There is also colors.memory_usage(), which sums up the memory usage and relies on the .nbytes attribute of the underlying NumPy array. Don’t get too bogged down in these details: what is important is relative memory usage that results from type conversion, as you’ll see next.

Now, what if we could take the unique colors above and map each to a less space-hogging integer? Here is a naive implementation of that:

In [ ]:
mapper = {v: k for k, v in enumerate(colors.unique())}
In [ ]:
as_int =
In [ ]:

Note: Another way to do this same thing is with Pandas’ pd.factorize(colors):

In [ ]:

Either way, you are encoding the object as an enumerated type (categorical variable).

You’ll notice immediately that memory usage is just about cut in half compared to when the full strings are used with object dtype.

Earlier in the section on accessors, I mentioned the .cat (categorical) accessor. The above with mapper is a rough illustration of what is happening internally with Pandas’ Categorical dtype:

“The memory usage of a Categorical is proportional to the number of categories plus the length of the data. In contrast, an object dtype is a constant times the length of the data.” (Source)

In colors above, you have a ratio of 2 values for every unique value (category):

len(colors) / colors.nunique()

As a result, the memory savings from converting to Categorical is good, but not great:

Not a huge space-saver to encode as Categorical

In [ ]:
colors.memory_usage(index=False, deep=True)

colors.astype('category').memory_usage(index=False, deep=True)

However, if you blow out the proportion above, with a lot of data and few unique values (think about data on demographics or alphabetic test scores), the reduction in memory required is over 10 times:

In [ ]:
manycolors = colors.repeat(10)
len(manycolors) / manycolors.nunique()  # Much greater than 2.0x

manycolors.memory_usage(index=False, deep=True)

manycolors.astype('category').memory_usage(index=False, deep=True)

A bonus is that computational efficiency gets a boost too: for categorical Series, the string operations are performed on the .cat.categories attribute rather than on each original element of the Series.

In other words, the operation is done once per unique category, and the results are mapped back to the values. Categorical data has a .cat accessor that is a window into attributes and methods for manipulating the categories:

In [ ]:
ccolors = colors.astype('category')

In fact, you can reproduce something similar to the example above that you did manually:

In [ ]:

All that you need to do to exactly mimic the earlier manual output is to reorder the codes:

In [ ]:

Notice that the dtype is NumPy’s int8, an 8-bit signed integer that can take on values from -127 to 128. (Only a single byte is needed to represent a value in memory. 64-bit signed ints would be overkill in terms of memory usage.) Our rough-hewn example resulted in int64 data by default, whereas Pandas is smart enough to downcast categorical data to the smallest numerical dtype possible.

Most of the attributes for .cat are related to viewing and manipulating the underlying categories themselves:

In [ ]:
[i for i in dir( if not i.startswith('_')]

There are a few caveats, though. Categorical data is generally less flexible. For instance, if inserting previously unseen values, you need to add this value to a .categories container first:

In [ ]:
ccolors.iloc[5] = 'a new color'
In [ ]:
ccolors =['a new color'])
ccolors.iloc[5] = 'a new color'  # No more ValueError

If you plan to be setting values or reshaping data rather than deriving new computations, Categorical types may be less nimble.

6. Introspect Groupby Objects via Iteration

When you call df.groupby('x'), the resulting Pandas groupby objects can be a bit opaque. This object is lazily instantiated and doesn’t have any meaningful representation on its own.

You can demonstrate with the abalone dataset from example 1:

In [ ]:
abalone['ring_quartile'] = pd.qcut(abalone.rings, q=4, labels=range(1, 5))
grouped = abalone.groupby('ring_quartile')


Alright, now you have a groupby object, but what is this thing, and how do I see it?

Before you call something like grouped.apply(func), you can take advantage of the fact that groupby objects are iterable:

In [ ]:

Each “thing” yielded by grouped.iter() is a tuple of (name, subsetted object), where name is the value of the column on which you’re grouping, and subsetted object is a DataFrame that is a subset of the original DataFrame based on whatever grouping condition you specify. That is, the data gets chunked by group:

In [ ]:
for idx, frame in grouped:
    print(f'Ring quartile: {idx}')
    print('-' * 16)
    print(frame.nlargest(3, 'weight'), end='\n\n')

Relatedly, a groupby object also has .groups and a group-getter, .get_group():

In [ ]:
In [ ]:

This can help you be a little more confident that the operation you’re performing is the one you want:

In [ ]:
grouped['height', 'weight'].agg(['mean', 'median'])

No matter what calculation you perform on grouped, be it a single Pandas method or custom-built function, each of these “sub-frames” is passed one-by-one as an argument to that callable. This is where the term “split-apply-combine” comes from: break the data up by groups, perform a per-group calculation, and recombine in some aggregated fashion.

If you’re having trouble visualizing exactly what the groups will actually look like, simply iterating over them and printing a few can be tremendously useful.

7. Use This Mapping Trick for Membership Binning

Let’s say that you have a Series and a corresponding “mapping table” where each value belongs to a multi-member group, or to no groups at all:

In [ ]:
countries = pd.Series([
    'United States',
    'United Kingdom',

groups = {
    'North America': ('United States', 'Canada', 'Mexico', 'Greenland'),
    'Europe': ('France', 'Germany', 'United Kingdom', 'Belgium')

In other words, you need to map countries to the following result:

0    North America
1    North America
2    North America
3           Europe
4           Europe
5            other
dtype: object

What you need here is a function similar to Pandas’ pd.cut(), but for binning based on categorical membership. You can use, which you already saw in example #5, to mimic this:

In [ ]:
from typing import Any

def membership_map(s: pd.Series, groups: dict,
                   fillvalue: Any=-1) -> pd.Series:
    # Reverse & expand the dictionary key-value pairs
    groups = {x: k for k, v in groups.items() for x in v}

This should be significantly faster than a nested Python loop through groups for each country in countries.

Here’s a test drive:

In [ ]:
membership_map(countries, groups, fillvalue='other')

Let’s break down what’s going on here. (Sidenote: this is a great place to step into a function’s scope with Python’s debugger, pdb, to inspect what variables are local to the function.)

The objective is to map each group in groups to an integer. However, will not recognize 'ab'—it needs the broken-out version with each character from each group mapped to an integer. This is what the dictionary comprehension is doing:

In [ ]:
groups = dict(enumerate(('ab', 'cd', 'xyz')))
{x: k for k, v in groups.items() for x in v}

This dictionary can be passed to to map or “translate” its values to their corresponding group indices.

8. Understand How Pandas Uses Boolean Operators

You may be familiar with Python’s operator precedence, where and, not, and or have lower precedence than arithmetic operators such as <, <=, >, >=, !=, and ==. Consider the two statements below, where < and > have higher precedence than the and operator:

In [ ]:
# Evaluates to "False and True"
4 < 3 and 5 > 4
In [ ]:
# Evaluates to 4 < 5 > 4
4 < (3 and 5) > 4

Note: It’s not specifically Pandas-related, but 3 and 5 evaluates to 5 because of short-circuit evaluation:

“The return value of a short-circuit operator is the last evaluated argument.” (Source)

Pandas (and NumPy, on which Pandas is built) does not use and, or, or not. Instead, it uses &, |, and ~, respectively, which are normal, bona fide Python bitwise operators.

These operators are not “invented” by Pandas. Rather, &, |, and ~ are valid Python built-in operators that have higher (rather than lower) precedence than arithmetic operators. (Pandas overrides dunder methods like .ror() that map to the | operator.) To sacrifice some detail, you can think of “bitwise” as “elementwise” as it relates to Pandas and NumPy:

In [ ]:
pd.Series([True, True, False]) & pd.Series([True, False, False])

It pays to understand this concept in full. Let’s say that you have a range-like Series:

In [ ]:
s = pd.Series(range(10))

I would guess that you may have seen this exception raised at some point:

In [ ]:
s % 2 == 0 & s > 3

What’s happening here? It’s helpful to incrementally bind the expression with parentheses, spelling out how Python expands this expression step by step:

s % 2 == 0 & s > 3                      # Same as above, original expression
(s % 2) == 0 & s > 3                    # Modulo is most tightly binding here
(s % 2) == (0 & s) > 3                  # Bitwise-and is second-most-binding
(s % 2) == (0 & s) and (0 & s) > 3      # Expand the statement
((s % 2) == (0 & s)) and ((0 & s) > 3)  # The `and` operator is least-binding

The expression s % 2 == 0 & s > 3 is equivalent to (or gets treated as) ((s % 2) == (0 & s)) and ((0 & s) > 3). This is called expansion: x < y <= z is equivalent to x < y and y <= z.

Okay, now stop there, and let’s bring this back to Pandas-speak. You have two Pandas Series that we’ll call left and right:

In [ ]:
left = (s % 2) == (0 & s)
right = (0 & s) > 3
left and right  # This will raise the same ValueError

You know that a statement of the form left and right is truth-value testing both left and right, as in the following:

In [ ]:
bool(left) and bool(right)

The problem is that Pandas developers intentionally don’t establish a truth-value (truthiness) for an entire Series. Is a Series True or False? Who knows? The result is ambiguous:

In [ ]:

The only comparison that makes sense is an elementwise comparison. That’s why, if an arithmetic operator is involved, you’ll need parentheses:

In [ ]:
(s % 2 == 0) & (s > 3)

In short, if you see the ValueError above pop up with boolean indexing, the first thing you should probably look to do is sprinkle in some needed parentheses.

9. Load Data From the Clipboard

It’s a common situation to need to transfer data from a place like Excel or Sublime Text to a Pandas data structure. Ideally, you want to do this without going through the intermediate step of saving the data to a file and afterwards reading in the file to Pandas.

You can load in DataFrames from your computer’s clipboard data buffer with pd.read_clipboard(). Its keyword arguments are passed on to pd.read_table().

This allows you to copy structured text directly to a DataFrame or Series. In Excel, the data would look something like this:

Excel Clipboard Data

Its plain-text representation (for example, in a text editor) would look like this:

a   b           c       d
0   1           inf     1/1/00
2   7.389056099 N/A     5-Jan-13
4   54.59815003 nan     7/24/18
6   403.4287935 None    NaT

Simply highlight and copy the plain text above, and call pd.read_clipboard():

In [ ]:
df = pd.read_clipboard(na_values=[None], parse_dates=['d'])
In [ ]:

10. Write Pandas Objects Directly to Compressed Format

This one’s short and sweet to round out the list. As of Pandas version 0.21.0, you can write Pandas objects directly to gzip, bz2, zip, or xz compression, rather than stashing the uncompressed file in memory and converting it. Here’s an example using the abalone data from trick #1:

In [ ]:
abalone.to_json('df.json.gz', orient='records',
                lines=True, compression='gzip')

In this case, the size difference is 11.6x:

In [ ]:
import os.path

abalone.to_json('df.json', orient='records', lines=True)
os.path.getsize('df.json') / os.path.getsize('df.json.gz')
Notebooks AI
Notebooks AI Profile20060