Skip to content
Bamboo Weekly

pandas assign

Add or replace columns inside a method chain, without mutating the original data frame.

How do you add a column in the middle of a method chain? That question is the whole reason assign exists, and the answer changes how you write Pandas. The familiar df['millions'] = ... is a statement, not an expression: it produces no value, so it cannot sit between two dots, and it modifies the frame you already have. assign returns a new data frame with the column added, which means the chain keeps flowing and the frame you read from disk stays exactly as it was.

That is the style I teach and the style nearly every Bamboo Weekly solution is written in: assign to a variable once, when you load the data, and after that let each step hand its result to the next. assign is the leg of that chain that grows new columns, which is why it turns up in more solutions than any other method on the site.

Official documentation: DataFrame.assign

The forms worth knowing

assign takes no arguments of its own. Everything you pass it is a keyword argument whose name becomes a column name and whose value becomes that column:

# One new column
df.assign(millions=pd.col('Hours Viewed') / 1_000_000)

# Several at once -- evaluated in order, left to right
df.assign(millions=pd.col('Hours Viewed') / 1_000_000,
          rounded=pd.col('millions').round(0))

# Replace an existing column by assigning to its own name
df.assign(millions=pd.col('millions').round(1))

# A scalar is broadcast down the whole column
df.assign(source='netflix')

# A name that is not a valid Python identifier needs the ** form
df.assign(**{'Hours (millions)': pd.col('Hours Viewed') / 1_000_000})

The second form is worth pausing on. assign evaluates its keyword arguments in order, so a later column can refer to one created earlier in the same call. Reverse those two lines and Pandas tells you so:

ValueError: Column 'millions' not found in given DataFrame.

Hint: did you mean one of ['Title', 'Available Globally?', 'Release Date',
'Hours Viewed'] instead?

That ordering guarantee is why one assign can often do the work of three.

pd.col, and the callable it replaces

The value you pass has to describe a column of a frame that does not exist yet — the frame as it will be at that point in the chain. Pandas gives you two ways to say that. The old way is a callable, which Pandas invokes with the current frame:

df.assign(millions=lambda df_: df_['Hours Viewed'] / 1_000_000)

The Pandas 3 way is pd.col, a lazy reference to "the column named this, in whatever frame this lands on":

df.assign(millions=pd.col('Hours Viewed') / 1_000_000)

Both produce the same column. The second is shorter, reads like the arithmetic it is, and avoids the lambda df_: noise that used to be unavoidable. pd.col supports arithmetic, comparisons, and the accessors and methods you would call on a series — .dt.year, .str.replace(), .round(), .where(). Prefer it. Keep the callable for the cases pd.col cannot reach: when you need the whole frame at once, or a function that takes the frame as an argument.

One thing to know about pd.col: it is lazy, and it will not tell you when you use it in the wrong place.

(pd.col('Hours Viewed') / 1_000_000).head()

(col('Hours Viewed') / 1000000).head()

No error, no data — just a bigger unevaluated expression. pd.col only becomes a column when a data frame gets hold of it, inside assign, loc, or a similar method.

A worked example, on real data

Netflix published an engagement report listing every title watched over six months. Bamboo Weekly #45 used it, and it is a good frame for showing a whole chain rather than a single call:

import pandas as pd
import plotly.express as px

url = ('https://www.bambooweekly.com/content/files/4cd45et68cgf/1HyknFM84ISQpeua6TjM7A/'
       '97a0a393098937a8f29c9d29c48dbfa8/'
       'what_we_watched_a_netflix_engagement_report_2023jan-jun.xlsx')

netflix = pd.read_excel(url, skiprows=5,
                        usecols=['Title', 'Available Globally?',
                                 'Release Date', 'Hours Viewed'])

That is 18,214 titles, and the only variable this page creates. Hours viewed run into the hundreds of millions, which is hard to read, so start by deriving something friendlier:

(
    netflix
    .assign(millions=pd.col('Hours Viewed') / 1_000_000,
            worldwide=pd.col('Available Globally?') == 'Yes',
            rounded=pd.col('millions').round(0))
    .sort_values('millions', ascending=False)
    .head(4)
    [['Title', 'worldwide', 'millions', 'rounded']]
)

                                Title  worldwide  millions  rounded
0           The Night Agent: Season 1       True     812.1    812.0
1           Ginny & Georgia: Season 2       True     665.1    665.0
2  The Glory: Season 1 // 더 글로리: 시즌 1       True     622.8    623.0
3                 Wednesday: Season 1       True     507.7    508.0

Three columns created in one call, and rounded is computed from millions, which did not exist a line earlier. netflix itself is untouched — it still has the four columns it was read with.

Now the full shape of a Bamboo Weekly solution: read, clean, assign, group, assign again, sort, and hand the result to a chart. The question is whether Netflix's viewing has shifted toward titles it releases worldwide.

(
    netflix
    .dropna(subset='Release Date')
    .assign(year=pd.col('Release Date').dt.year,
            millions=pd.col('Hours Viewed') / 1_000_000,
            worldwide=pd.col('Available Globally?') == 'Yes',
            worldwide_millions=pd.col('millions').where(pd.col('worldwide'), 0))
    .groupby('year')
    .agg(titles=('Title', 'count'),
         millions=('millions', 'sum'),
         worldwide_millions=('worldwide_millions', 'sum'))
    .assign(pct_worldwide=pd.col('worldwide_millions') / pd.col('millions') * 100)
    .loc[pd.col('titles') >= 25]
    .sort_index()
    .reset_index()
    .pipe(px.line, x='year', y='pct_worldwide', markers=True,
          title='Share of Netflix viewing hours from globally available titles')
)

Look at the fourth keyword in that first assign: worldwide_millions uses both millions and worldwide, and neither existed when the call began. Then a second assign, after the groupby, does the arithmetic that only makes sense once the rows have been aggregated. Splitting the work across two assign calls in different parts of the chain is the normal case, not a compromise.

Drop the .reset_index() and the .pipe() and you can see the numbers:

      titles  millions  worldwide_millions  pct_worldwide
year
2014      28     251.6               102.2           40.6
2015      79     615.4               298.4           48.5
2016     192    1259.6               831.8           66.0
2017     361    2304.5              1422.4           61.7
2018     595    2757.3              2055.4           74.5
2019     698    3696.8              3005.2           81.3
2020     779    5013.6              3995.8           79.7
2021     814    6551.3              5273.9           80.5
2022     956   13313.1             11458.6           86.1
2023     329   17153.3             15622.5           91.1

Forty percent in 2014, ninety-one percent in 2023. The global-first catalog is not a slogan; it is a nine-year trend line.

pipe is what keeps the chart inside the chain: it hands the data frame to px.line as its first argument and passes the keyword arguments straight through. assign and pipe together are why these chains never need an intermediate variable.

Four mistakes people make

Assigning with brackets inside a chain. This is the concrete reason to use assign, not a stylistic preference. Under Pandas 3 and Copy-on-Write, setting a column on a temporary produces a ChainedAssignmentError:

netflix.dropna(subset='Release Date')['millions'] = netflix['Hours Viewed'] / 1e6

ChainedAssignmentError: A value is being set on a copy of a DataFrame or Series
through chained assignment. Such chained assignment never works to update the
original DataFrame or Series, because the intermediate object on which we are
setting values always behaves as a copy (due to Copy-on-Write).

Read the class name carefully, because this is the part that bites: despite ending in Error, ChainedAssignmentError is a subclass of Warning. Your program does not stop. It prints that paragraph, throws the column away, and carries on — 'millions' in netflix.columns is False afterward. A warning you have learned to scroll past is worse than an exception, and assign is how you never see it again.

Referring to a new column by the outer frame's name. Inside a single assign, the frame under construction is not the frame in your variable. Reach for netflix['millions'] while creating millions in the same call and you get a KeyError: 'millions', because netflix never had that column. pd.col, or a callable, is what points at the frame being built.

Expecting the callable to see the original rows. The function you pass gets the frame as it exists at that point in the chain, filters and all. That is usually what you want, and occasionally a nasty surprise:

(
    netflix
    .assign(millions=pd.col('Hours Viewed') / 1e6)
    .loc[pd.col('Available Globally?') == 'Yes']
    .assign(share_of_shown=lambda df_: df_['millions'] / df_['millions'].sum(),
            share_of_all=netflix['Hours Viewed'] / netflix['Hours Viewed'].sum())
    .head(3)
    [['Title', 'share_of_shown', 'share_of_all']]
)

                                Title  share_of_shown  share_of_all
0           The Night Agent: Season 1        0.016870      0.008690
1           Ginny & Georgia: Season 2        0.013816      0.007117
2  The Glory: Season 1 // 더 글로리: 시즌 1        0.012938      0.006664

Two shares for the same row, differing by a factor of two, because one denominator counts the 4,514 titles that survived the filter and the other counts all 18,214. Both are legitimate answers to different questions. Decide which one you meant.

Building the keyword dictionary with lambdas in a comprehension. The ** form is genuinely useful for column names that are not valid Python identifiers — df.assign(Hours Viewed=...) is a syntax error, and so is df.assign(2024=...). It is just as useful for generating many columns at once. But write those values as lambda and Python's closure rules quietly ruin it:

df = pd.DataFrame({'Price': ['1,100', '2,200'],
                   'High': ['3,300', '4,400'],
                   'Low': ['5,500', '6,600']})

rewriting = {c: lambda df_: df_[c].str.replace(',', '').astype(float)
             for c in ['Price', 'High', 'Low']}

df.assign(**rewriting)

    Price    High     Low
0  5500.0  5500.0  5500.0
1  6600.0  6600.0  6600.0

Every lambda captured the variable c, not its value, and by the time assign called them the comprehension had finished with c set to 'Low'. Three columns, one column's data, no error. Writing the same dictionary with pd.col fixes it outright, because pd.col(c) evaluates c immediately:

rewriting = {c: pd.col(c).str.replace(',', '').astype(float)
             for c in ['Price', 'High', 'Low']}

    Price    High     Low
0  1100.0  3300.0  5500.0
1  2200.0  4400.0  6600.0

If you must use a lambda, bind the value with a default argument: lambda df_, c=c: .... This is the strongest single argument for pd.col I know: it is not only shorter, it is not a closure.

Where it shows up in Bamboo Weekly

I counted the 122 solution posts on the site: 91 of them call assign, 514 times in all. No other method comes close — groupby and sort_values appear in 65 each, apply in 30. If you want to read Bamboo Weekly solutions fluently, this is the method to learn first.

#76: Aging legislators creates two columns in one call, each subtracting a birthday from a date column, and then assigns a boolean comparison onto the result of a pivot_table — the same second-assign-after-a-reshape move as the Netflix chain above.

#71: Holidays is the best illustration of replacing a column with itself: .assign(holiday=lambda df_: df_['holiday'].str.replace(...)), chained twice to strip two different patterns out of the same names. Cleaning a column without ever leaving the chain is most of what assign does in practice.

#78: Stock markets is where I built the keyword arguments programmatically, as a dictionary comprehension over three price columns unpacked with ** — the trick described above, and the post to read for why the ** form is more than a workaround for awkward names.

#45: Netflix is the source of the data on this page.

All four are free to read.

Watch it

assign is one leg of the method-chaining style: Method chaining in Pandas: Cleaner queries with assign, loc, and lambda.

Practice it

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

Go deeper

assign rarely travels alone. pd.col is what you put inside it in Pandas 3. loc filters rows in the same chain, and takes pd.col in exactly the same way. pipe hands the whole frame to a function of yours — including a Plotly Express function, which is how a chart becomes the last line of a chain rather than a new statement. groupby is the step that usually sits between two assign calls. And when the column you want genuinely needs a Python function per row, apply explains why that is rarer, and slower, than people expect.

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 117 Bamboo Weekly exercises that use assign on real-world data — try each one, then study the worked solution.

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