Skip to content
Bamboo Weekly

pandas ffill

Carry the last known value forward, and be honest about what you just invented.

What belongs in a hole? ffill has one answer: whatever was there last. It walks down the column and writes the most recent real value into every gap until another one turns up. bfill walks in reverse.

That answer is right for a quantity that holds its value between observations — a price, a policy rate, an account status, a group label that appears once at the top of a block. It is wrong for a measurement, because a measurement nobody took has no last value. It has an absence. Both cases leave a column with no NaN in it, and afterward they look exactly alike.

Official documentation: DataFrame.ffill, Series.ffill, GroupBy.ffill and DataFrame.bfill.

The arguments that earn their keep

df.ffill(limit=2,              # fill at most two consecutive gaps
         limit_area='inside')  # only between real values, never past the last one

There are only four; the other two are axis and inplace. Reach for limit by habit — it caps how far one observation may travel, which turns silent hole-filling into a rule you could defend. limit_area='inside' stops the fill running past your last real reading, so a series that ended in 2019 does not become a flat line reaching to today.

A worked example, on real data

Our World in Data's CO2 and energy file, one row per country per year. Keeping only rows with an ISO code drops the regions and income groups, and 1990 through 2023 leaves 816 gaps in energy use per person:

import pandas as pd

url = 'https://nyc3.digitaloceanspaces.com/owid-public/data/co2/owid-co2-data.csv'

energy = (
    pd.read_csv(url,
                usecols=['country', 'iso_code', 'year', 'energy_per_capita'],
                storage_options={'User-Agent': 'Mozilla/5.0'})
    .loc[pd.col('iso_code').notna() & pd.col('year').between(1990, 2023)]
    .sort_values(['country', 'year'])
)

energy['energy_per_capita'].isna().sum()     # 816

Now fill them the obvious way, and look at South Sudan:

(energy
 .assign(filled=pd.col('energy_per_capita').ffill())
 .loc[(pd.col('country') == 'South Sudan') & pd.col('year').between(2009, 2013)])

           country  year iso_code  energy_per_capita     filled
43015  South Sudan  2009      SSD                NaN  66698.023
43016  South Sudan  2010      SSD                NaN  66698.023
43017  South Sudan  2011      SSD                NaN  66698.023
43018  South Sudan  2012      SSD            557.737    557.737
43019  South Sudan  2013      SSD            568.443    568.443

South Sudan's first real reading is 558 kWh per person. The value filled in above it is 66,698 — South Korea's figure for 2023, because the frame is sorted by country and South Korea comes just before South Sudan in the alphabet. ffill does not know there is a country column. It walked off the end of one nation's history straight into the next one's, and the count of missing values afterward is zero. A clean audit, and a hundredfold lie.

The fix is to fill inside each group:

energy.groupby('country')['energy_per_capita'].ffill().isna().sum()            # 566
energy.groupby('country')['energy_per_capita'].ffill(limit=1).isna().sum()     # 691

Those three South Sudan rows stay NaN, as they should. 566 gaps survive, every one a leading gap that no earlier reading can honestly reach, and 250 interior gaps get filled. The limit=1 figure tells you something the first version hid: every interior hole in this file is one or two years wide.

Mistakes people make

Filling before sorting. ffill follows row order, not time order. On a frame that arrived in upload order, "the last known value" can be next March's. Sort first, always.

Forgetting the group. Any stacked panel — country and year, ticker and date, sensor and timestamp — has the failure mode above, and it never raises. Reach for df.groupby(key)[col].ffill() by default.

Expecting limit_area on a groupby. Series.ffill(limit_area='inside') works; GroupBy.ffill(limit_area='inside') raises TypeError: GroupBy.ffill() got an unexpected keyword argument 'limit_area'. Inside a group, limit is what you have.

Where it shows up in Bamboo Weekly

The most common ffill in Bamboo Weekly has nothing to do with time. It repairs a file format.

Bamboo Weekly #111: State taxes reads a spreadsheet where each state's name is written once against the first of its tax brackets and left blank below. .assign(state=lambda df_: df_[('Unnamed: 0_level_0', 'State')].ffill()) puts the name back on every row, and only then can you set_index on it.

Bamboo Weekly #159: State of the Union is the same repair after read_html, where a president spans as many rows as he had terms. One ffill, and the merged cell is gone.

Bamboo Weekly #181: Housing costs is the reporting-lag case, for subscribers: take the last four quarters of an OECD table, ffill so a country that has not filed yet keeps its previous number, then average. A judgment call, made deliberately, on four rows.

Practice it

Work through an ffill exercise, with instant feedback and no signup required: practice.lernerpython.com/bamboo-weekly/ffill/

Go deeper

ffill is one of three answers to a gap, and interpolate is where the choice gets argued out — a staircase, a straight line, or a constant from fillna. Before you fill anything, isna tells you how much you are about to invent, and dropna is the option of not inventing it.

More Pandas videos on Python and Pandas with Reuven Lerner.

Bamboo Weekly is the practice. If you want the structured version — full courses with downloadable Jupyter notebooks, plus live Pandas office hours when you get stuck — that is LernerPython+Data. A paid Bamboo Weekly subscription is included with it.

See it on real data

Below are the 5 Bamboo Weekly exercises that use ffill on real-world data — try each one, then study the worked solution.

Part of the Pandas Methods Index. See also practice by skill.