pythontojob
Log inSign up
Chapter 09

Adding columns — making the numbers the file does not have

Building new columns from the ones already there: whole-column arithmetic and why the index decides where each value lands, assign, np.where and np.select, map, pd.cut, .str and .dt, transform for comparing a row with its group, and apply as the last resort.

50 minPython 3.12
  1. 1Encounter
  2. 2Understand
  3. 3Worked
  4. 4Predict
  5. 5Apply
  6. 6Stretch

The problem we are solving

The shop owner sends a message: "For every order, I want to see how much money it brought in, whether it was a big order, which manager is responsible for it, and what day of the week it was. And for each order, what share of its branch's money is that?"

You open orders.csv. It has a quantity and a price, a branch (which of the three shops) and a date. Not one of the things she asked for is in the file. Every one of them has to be made from columns that are already there.

If you come from plain Python, your hands already know what to type — a loop over the rows:

python
import pandas as pd

orders = pd.read_csv("orders.csv")

revenues = []
for i in range(len(orders)):
    revenues.append(orders.loc[i, "quantity"] * orders.loc[i, "price"])
orders["revenue"] = revenues

print(orders[["product", "quantity", "price", "revenue"]])
text
product  quantity  price  revenue
0       pen        12   15.0    180.0
1  notebook         5   60.0    300.0
2       bag         2  850.0   1700.0
3    bottle         7  120.0    840.0
4    eraser        30    8.0    240.0
5       bag         1  850.0    850.0
6       pen        20   15.0    300.0
7    bottle         4  120.0    480.0

It works. It is also the habit this chapter exists to replace, for three reasons that only show up later.

It is slow: Python runs the loop body once per row, so a file of a million rows means a million trips through the interpreter. It is long: four lines for one multiplication, and the asked-for list has five more columns. And it is fragile in a way that is hard to see. The loop counts positions 0, 1, 2, … and then looks them up as labels with .loc. That only works while the two happen to be the same. Filter the table first — just the north branch's orders, as in chapter five — and they no longer are:

python
north = orders[orders["branch"] == "north"]
print(north.index.tolist())

revenues = []
for i in range(len(north)):
    revenues.append(north.loc[i, "quantity"] * north.loc[i, "price"])
text
[0, 2, 4, 7]
KeyError: 1

The north rows keep their original labels 0, 2, 4, 7. The loop asks for label 1, which is a south order and is not in north, so it crashes. Worse versions of this bug do not crash at all — they quietly read the wrong row.

Pandas has a different way of thinking about this: you do not compute a column one row at a time; you compute it once, for the whole column. That one idea is what this chapter is about, and once it clicks, every column the shop owner asked for is one line.

By the end of this chapter you can

  • Make a new column from arithmetic on whole columns, and explain why no loop is needed
  • Predict how pandas lines values up by index when you assign a Series, and avoid the NaN that misalignment causes
  • Add columns without touching the original table using assign()
  • Choose between two or more values per row with np.where and np.select
  • Look values up with .map(), cut numbers into ranges with pd.cut, and pull pieces out of text and dates with .str and .dt
  • Compare each row with its group using groupby(...).transform(...)
  • Recognise when apply(axis=1) is justified, and why it is the last resort
  • Explain what Copy-on-Write in pandas 3 means for adding columns to a filtered table

Prerequisites: Grouping and aggregation.

Make the file first

Every example in this chapter reads orders.csv, the same file used since chapter four. If you do not have it, create it in your project folder with exactly these lines:

text
order_id,date,branch,product,category,quantity,price
1001,2024-03-01,north,pen,stationery,12,15.0
1002,2024-03-01,south,notebook,stationery,5,60.0
1003,2024-03-02,north,bag,accessories,2,850.0
1004,2024-03-02,east,bottle,accessories,7,120.0
1005,2024-03-03,north,eraser,stationery,30,8.0
1006,2024-03-03,south,bag,accessories,1,850.0
1007,2024-03-04,east,pen,stationery,20,15.0
1008,2024-03-04,north,bottle,accessories,4,120.0

Some examples also use NumPy, which was installed together with pandas — import numpy as np is all it takes.

Before you write the code

A new column is a small program. Five minutes of planning saves the hour spent wondering where a NaN came from.

1. Say the question in one sentence, per column. Each column the shop owner asked for becomes one line of the plan:

  • revenue — how much money the order brought in. Made from quantity × price.
  • size — "big" if revenue is at least 500, otherwise "small". Made from revenue.
  • manager — who runs the branch the order came from. Made from branch, through a lookup table.
  • weekday — the day of the week the order was placed. Made from date.
  • branch_share — this order's revenue as a share of its branch's total. Made from revenue and branch.

Notice the one thing they all have in common: one value per row. A new column is always exactly as long as the table. That is the difference from chapter eight, where groupby(...).sum() gave one value per group. If the thing you are computing has fewer values than the table has rows, it is not a column yet.

2. Look at the input before touching it. The tools you can use depend on the types of the columns you start from:

python
import pandas as pd

orders = pd.read_csv("orders.csv")

print(orders.shape)
print(orders.dtypes)
text
(8, 7)
order_id      int64
date            str
branch          str
product         str
category        str
quantity      int64
price       float64
dtype: object

Eight rows; that number must still be eight at the end. quantity is int64 and price is float64, so multiplying them will do real arithmetic. date is str — text that looks like a date. Text has no weekday, so it will need converting before .dt can be used on it. Writing this down now is what stops the AttributeError later.

3. Sketch the output, and work one row out by hand. Before running anything, decide what two of the rows should become — an ordinary one and an extreme one:

text
product  quantity  price  revenue  size
pen            12   15.0    180.0  small
bag             2  850.0   1700.0  big

12 × 15.0 = 180.0, and 180 is under 500, so small. A hand-checked row is a test: after the code runs, if row 0 does not say 180.0 and small, the code is wrong, however reasonable it looks.

4. Pick the tool that fits the shape of the rule. Almost every new column falls into one of these:

  1. Arithmetic on columns or a number → + - * / on whole columns. Example: quantity * price.
  2. One of two values, by a condition → np.where. Example: "big" or "small".
  3. One of several values, by conditions checked in order → np.select. Example: "bulk", "normal", "few".
  4. A value looked up from a fixed table → .map(dict). Example: branch → manager.
  5. Which range a number falls into → pd.cut. Example: quantity → band.
  6. A piece of a text → the .str methods. Example: the first letters of the category.
  7. A piece of a date → pd.to_datetime, then .dt. Example: the weekday.
  8. A comparison with the row's own group → groupby(...).transform(...). Example: share of the branch total.
  9. None of the above → apply(axis=1). Example: a rule too irregular for the rest.

Read the list from the top and stop at the first line that fits. The order is deliberate: the first eight work on whole columns at once; the last goes back to one Python call per row.

5. Decide what you will check afterwards. Three checks, every time: the row count has not changed; the new column has the type you expected; and isna().sum() on it is zero, or exactly the number you can explain. Plus the hand-checked row.

Arithmetic on whole columns

Here is the revenue column the pandas way:

python
import pandas as pd

orders = pd.read_csv("orders.csv")

orders["revenue"] = orders["quantity"] * orders["price"]

print(orders[["product", "quantity", "price", "revenue"]])
text
product  quantity  price  revenue
0       pen        12   15.0    180.0
1  notebook         5   60.0    300.0
2       bag         2  850.0   1700.0
3    bottle         7  120.0    840.0
4    eraser        30    8.0    240.0
5       bag         1  850.0    850.0
6       pen        20   15.0    300.0
7    bottle         4  120.0    480.0

Row 0 says 180.0 — the hand-checked value. No loop, no index arithmetic.

Why this works. orders["quantity"] is a Series of eight numbers; so is orders["price"]. When you put * between two Series, pandas pairs their values by index label — label 0 with label 0, label 1 with label 1 — and multiplies each pair. The loop still happens, but inside pandas' compiled code, not in Python. That is why it is fast, and it is also why it cannot read the wrong row: there is no counter to get out of step.

Then orders["revenue"] = ... does one of two things, depending on the name:

  • if revenue does not exist, it adds a new column at the right-hand end;
  • if it does exist, it replaces it — silently. There is no "are you sure?".

The result type follows the arithmetic: int64 × float64 gives float64, which is why revenue shows 180.0 rather than 180.

A column can also be combined with a single number. The number is used for every row — pandas calls this broadcasting:

python
orders["price_with_vat"] = (orders["price"] * 1.15).round(2)

print(orders[["product", "price", "price_with_vat"]].head(4))
text
product  price  price_with_vat
0       pen   15.0           17.25
1  notebook   60.0           69.00
2       bag  850.0          977.50
3    bottle  120.0          138.00

The brackets matter: .round(2) applies to the whole result of the multiplication, which is again a Series. Every step here takes a column and returns a column, so you can keep chaining.

The index decides which value goes where

That pairing by label is not only for arithmetic. Assigning a Series to a column also lines up by label. Most of the time you never notice, because the new values come from the same table and the labels match exactly. You notice the day they do not.

Suppose you want a column holding the quantity for north-branch orders only:

python
north = orders[orders["branch"] == "north"]

orders["north_qty"] = north["quantity"]

print(orders[["branch", "quantity", "north_qty"]])
text
branch  quantity  north_qty
0  north        12       12.0
1  south         5        NaN
2  north         2        2.0
3   east         7        NaN
4  north        30       30.0
5  south         1        NaN
6   east        20        NaN
7  north         4        4.0

north has labels 0, 2, 4, 7. Pandas put each value against the row with the same label, and every other row got NaN — "no value for this label". And because NaN is a float, the column became float64 even though quantities are whole numbers. That may be exactly what you wanted, or the start of a long search for missing data that you created yourself. The way to tell is to know the rule: assignment matches labels, not positions.

The same rule protects you. Sorting (chapter seven) reorders a Series but keeps each value with its label, so assigning a sorted Series back puts every value in its original row:

python
by_revenue = orders["revenue"].sort_values()
print(by_revenue.head(3))

orders["revenue_copy"] = by_revenue
print(orders[["product", "revenue", "revenue_copy"]].head(3))
print((orders["revenue_copy"] == orders["revenue"]).all())
text
0    180.0
4    240.0
6    300.0
Name: revenue, dtype: float64
    product  revenue  revenue_copy
0       pen    180.0         180.0
1  notebook    300.0         300.0
2       bag   1700.0        1700.0
True

The sorted Series runs in the order of labels 0, 4, 6 — the second value, 240.0, belongs to row 4, not row 1. And yet revenue_copy is identical to revenue row for row, which the last line confirms for all eight rows. Pandas used the labels and ignored the order. Had it gone by position, row 1 (the notebook, 300.0) would have received 240.0.

A plain Python list has no labels, so it is used by position — and then its length must match exactly:

python
try:
    orders["rating"] = [5, 4, 3]
except ValueError as error:
    print("ValueError:", error)
text
ValueError: Length of values (3) does not match length of index (8)

Three values for eight rows is refused rather than guessed at. That is the safest possible behaviour, and it is worth seeing once so the message is familiar.

We do not need the experiment columns any more. drop(columns=...) returns the table without them:

python
orders = orders.drop(columns=["north_qty", "revenue_copy", "price_with_vat"])
print(list(orders.columns))
text
['order_id', 'date', 'branch', 'product', 'category', 'quantity', 'price', 'revenue']

assign(): new columns without changing the original

orders["revenue"] = ... changes orders itself. Often that is what you want. Sometimes it is not — you want to try a few columns out, or build a report table, and keep the original exactly as it was read. assign() returns a new DataFrame with the extra columns and leaves the original alone:

python
import numpy as np
import pandas as pd

orders = pd.read_csv("orders.csv")

report = orders.assign(
    revenue=orders["quantity"] * orders["price"],
    vat=lambda d: (d["revenue"] * 0.15).round(2),
)

print(report[["product", "revenue", "vat"]].head(3))
print("revenue" in orders.columns)
text
product  revenue    vat
0       pen    180.0   27.0
1  notebook    300.0   45.0
2       bag   1700.0  255.0
False

Three things to notice. (The import numpy as np at the top is for the next section.)

The argument names become the column names — revenue=... makes a column called revenue. That is why the column names must be valid Python names when you use assign().

vat is written as a lambda. It has to be: vat needs revenue, and revenue does not exist on orders — it only exists on the new table being built. A lambda d: ... receives that new table as d, so d["revenue"] is already there. Inside one assign(), columns are added from top to bottom, and each lambda sees the ones above it.

And the last line prints False: orders has no revenue column. Nothing was changed behind your back.

np.where: one of two values

"big if revenue is at least 500, otherwise small" is an if/else for every row. Writing an if would mean going back to a loop, because an if needs one True or False, and a comparison of a whole column gives eight of them (that is the "truth value of a Series is ambiguous" error from chapter five).

NumPy's where is an if/else that works on a whole column: where the condition is True, take the first value; elsewhere, the second.

python
report["size"] = np.where(report["revenue"] >= 500, "big", "small")

print(report[["product", "revenue", "size"]])
text
product  revenue   size
0       pen    180.0  small
1  notebook    300.0  small
2       bag   1700.0    big
3    bottle    840.0    big
4    eraser    240.0  small
5       bag    850.0    big
6       pen    300.0  small
7    bottle    480.0  small

Row 0 is small, row 2 is big — both match the sketch. The condition is the same kind of boolean mask you built in chapter five; np.where simply turns each True/False into a value. The edge is worth checking: >= 500 means an order of exactly 500 is big. Decide that on purpose, not by accident.

More than two values: np.select

Three tiers by quantity — 20 or more is "bulk", 5 or more is "normal", anything else is "few". np.select takes a list of conditions and a list of matching values, and a default for rows that match none:

python
conditions = [
    report["quantity"] >= 20,
    report["quantity"] >= 5,
]
labels = ["bulk", "normal"]

report["tier"] = np.select(conditions, labels, default="few")

print(report[["product", "quantity", "tier"]])
text
product  quantity    tier
0       pen        12  normal
1  notebook         5  normal
2       bag         2     few
3    bottle         7  normal
4    eraser        30    bulk
5       bag         1     few
6       pen        20    bulk
7    bottle         4     few

The first condition that is True wins. That is why the strictest condition comes first. Swap the order and see what happens to the eraser order of 30:

python
wrong = np.select(
    [report["quantity"] >= 5, report["quantity"] >= 20],
    ["normal", "bulk"],
    default="few",
)
print(wrong)
text
['normal' 'normal' 'few' 'normal' 'normal' 'few' 'normal' 'few']

Every order is normal or few; nothing is bulk, because 30 and 20 are also >= 5 and that condition was checked first. No error, just a wrong column — which is why the plan's hand-checked row should include an extreme value.

.map(): looking values up

The manager is not calculated from anything. It is a fact you know: Asha runs the north branch, Omar the south, Lina the east. When the rule is "look it up", write the lookup table as a dictionary and .map() the column through it:

python
manager_of = {
    "north": "Asha",
    "south": "Omar",
    "east": "Lina",
}

report["manager"] = report["branch"].map(manager_of)

print(report[["branch", "manager"]])
print(report["manager"].isna().sum())
text
branch manager
0  north    Asha
1  south    Omar
2  north    Asha
3   east    Lina
4  north    Asha
5  south    Omar
6   east    Lina
7  north    Asha
0

Each branch was replaced by its value in the dictionary — the manager's name. The second print is the check from the plan, and it matters more than it looks, because of what .map() does with a key it cannot find. Imagine the dictionary was written before the east branch opened:

python
old_managers = {"north": "Asha", "south": "Omar"}

print(report["branch"].map(old_managers).isna().sum())
text
2

No error. The two east orders simply got NaN. On eight rows you would spot it; on eighty thousand you would not, and a later groupby("manager") would quietly leave those orders out (chapter eight: NaN keys are dropped). Every .map() gets an isna().sum() right after it.

pd.cut: which range a number falls into

The shop wants each order banded by quantity: up to 5 items is small, 6 to 15 is medium, more than 15 is large. You could write that with np.select, but "which range" has its own tool. pd.cut takes the edges of the ranges and a label for each range:

python
report["band"] = pd.cut(
    report["quantity"],
    bins=[0, 5, 15, np.inf],
    labels=["small", "medium", "large"],
)

print(report[["product", "quantity", "band"]])
text
product  quantity    band
0       pen        12  medium
1  notebook         5   small
2       bag         2   small
3    bottle         7  medium
4    eraser        30   large
5       bag         1   small
6       pen        20   large
7    bottle         4   small

Four edges make three ranges: (0, 5], (5, 15] and (15, ∞]. The round bracket means "not including", the square bracket means "including" — so each range includes its right edge. That is why the notebook order of exactly 5 is small, not medium. np.inf is infinity, a top edge no quantity can exceed.

The result has a type you have not met yet:

python
print(report["band"].dtype)
print(report["band"].cat.categories.tolist())
text
category
['small', 'medium', 'large']

A category column remembers that small < medium < large. So sorting by band gives that order rather than alphabetical order — usually what a report wants.

Text and dates: .str and .dt

Pieces of text

Chapter five used .str.contains to filter. The same .str accessor builds columns: every string method you know from Python, applied to every value at once. Suppose each order needs a short code like STA-1001 — the first three letters of the category, in capitals, a dash, then the order number:

python
report["code"] = (
    report["category"].str[:3].str.upper()
    + "-"
    + report["order_id"].astype(str)
)

print(report[["category", "order_id", "code"]].head(3))
text
category  order_id      code
0   stationery      1001  STA-1001
1   stationery      1002  STA-1002
2  accessories      1003  ACC-1003

.str[:3] slices every value, .str.upper() capitalises every value, and + between text columns joins them row by row. order_id is a number, and a number cannot be added to text, so .astype(str) turns it into text first.

Pieces of a date

The plan already warned that date is str. Convert it with pd.to_datetime, and the .dt accessor opens up:

python
report["date"] = pd.to_datetime(report["date"])
print(report["date"].dtype)

report["weekday"] = report["date"].dt.day_name()

print(report[["date", "weekday"]].head(4))
text
datetime64[us]
        date   weekday
0 2024-03-01    Friday
1 2024-03-01    Friday
2 2024-03-02  Saturday
3 2024-03-02  Saturday

The column is now datetime64 — a real date, not text. (The [us] is the precision pandas 3 chose for it; you can ignore it.) From a real date, .dt gives you .dt.day_name(), .dt.month, .dt.year, .dt.day and more. And the conversion is done once, assigned back to date, so every later step gets a date, not a string.

transform: comparing a row with its group

The last request — each order's share of its branch's revenue — needs two things on the same row: the order's revenue, and its branch's total. Chapter eight gave us the totals:

python
print(report.groupby("branch")["revenue"].sum())
text
branch
east     1140.0
north    2600.0
south    1150.0
Name: revenue, dtype: float64

But that is three values, one per branch, and the table has eight rows. You cannot divide eight numbers by three. What you need is the branch total repeated on every row of that branch. That is exactly what transform returns:

python
branch_total = report.groupby("branch")["revenue"].transform("sum")
print(branch_total)
text
0    2600.0
1    1150.0
2    2600.0
3    1140.0
4    2600.0
5    1150.0
6    1140.0
7    2600.0
Name: revenue, dtype: float64

Same grouping, same "sum", but the result has eight values with the table's own labels — every north row carries 2600, every south row 1150. Now it is ordinary column arithmetic:

python
report["branch_share"] = (report["revenue"] / branch_total).round(3)

print(report[["branch", "product", "revenue", "branch_share"]])
text
branch   product  revenue  branch_share
0  north       pen    180.0         0.069
1  south  notebook    300.0         0.261
2  north       bag   1700.0         0.654
3   east    bottle    840.0         0.737
4  north    eraser    240.0         0.092
5  south       bag    850.0         0.739
6   east       pen    300.0         0.263
7  north    bottle    480.0         0.185

Check it: the bag order in the north branch is 1700 / 2600 = 0.654. And the shares within each branch must add up to 1:

python
print(report.groupby("branch")["branch_share"].sum())
text
branch
east     1.0
north    1.0
south    1.0
Name: branch_share, dtype: float64

The rule to remember: agg/sum shrink a group to one row; transform gives one value back for every row. Use the first for a summary table, the second for a new column.

apply(axis=1): the last resort

Sometimes the rule really is irregular. The delivery fee is: at the north branch — the main one, next to the warehouse — free when revenue is at least 500, otherwise 60; at the other branches, 120, but free above 1000. You can write that as an ordinary Python function and apply it to every row:

python
def delivery_fee(row):
    if row["branch"] == "north":
        return 0 if row["revenue"] >= 500 else 60
    return 0 if row["revenue"] > 1000 else 120


report["fee"] = report.apply(delivery_fee, axis=1)

print(report[["branch", "revenue", "fee"]])
text
branch  revenue  fee
0  north    180.0   60
1  south    300.0  120
2  north   1700.0    0
3   east    840.0  120
4  north    240.0   60
5  south    850.0  120
6   east    300.0  120
7  north    480.0   60

axis=1 means "give the function one row at a time". Inside, row is a Series whose labels are the column names, so row["branch"] works.

It is easy to read, and that is its whole appeal. The cost is that it is the loop from the start of the chapter again: pandas builds a Series for every row and calls your Python function once per row. On eight rows that is nothing; on a million rows it is the difference between a blink and a coffee break.

So before reaching for apply, try to say the rule with whole-column tools. This rule is really "three cases, checked in order" — np.select:

python
in_north = report["branch"] == "north"

fee_fast = np.select(
    [in_north & (report["revenue"] >= 500), in_north, report["revenue"] > 1000],
    [0, 60, 0],
    default=120,
)

print((fee_fast == report["fee"]).all())
text
True

Identical result, no Python call per row. Keep apply(axis=1) for rules that genuinely cannot be written this way — calling an outside function, parsing a messy string with several steps — and treat reaching for it as a signal to think once more.

Adding a column to a filtered table: Copy-on-Write

You will often filter first and then add a column to the result. In pandas 3 that is simple, and the reason it is simple is worth understanding because older tutorials say otherwise.

python
import pandas as pd

orders = pd.read_csv("orders.csv")
orders["revenue"] = orders["quantity"] * orders["price"]

north = orders[orders["branch"] == "north"]
north["revenue_k"] = north["revenue"] / 1000

print(north[["product", "revenue", "revenue_k"]])
print(list(orders.columns))
text
product  revenue  revenue_k
0     pen    180.0       0.18
2     bag   1700.0       1.70
4  eraser    240.0       0.24
7  bottle    480.0       0.48
['order_id', 'date', 'branch', 'product', 'category', 'quantity', 'price', 'revenue']

No warning, and orders did not get a revenue_k column. Pandas 3 uses Copy-on-Write: every result of filtering or selecting behaves as its own independent table. Changing north can never change orders.

If you read older code or answers online, you will see this exact pattern followed by a SettingWithCopyWarning ("A value is trying to be set on a copy of a slice from a DataFrame"), and the advice to write orders[...].copy(). That warning existed because older pandas sometimes did share data between the two tables, and nobody could easily tell when. Pandas 3 removed the uncertainty, and the warning with it. Writing .copy() is now unnecessary, though harmless.

What Copy-on-Write does not allow is changing orders through a chain of two steps. If you want to change values in the original table for some rows, this does nothing:

python
orders[orders["branch"] == "north"]["revenue"] = 0

print(orders["revenue"].tolist())
text
[180.0, 300.0, 1700.0, 840.0, 240.0, 850.0, 300.0, 480.0]
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).

Try using '.loc[row_indexer, col_indexer] = value' instead, to perform the assignment in a single step.

See the documentation for a more detailed explanation: https://pandas.pydata.org/pandas-docs/stable/user_guide/copy_on_write.html#chained-assignment

The first bracket makes a new table, the second bracket sets values in that new table, and the new table is thrown away. Pandas warns you with a ChainedAssignmentError, and the list shows revenue unchanged — the north orders still say 180.0, 1700.0, 240.0 and 480.0. (Warnings go to the error stream, so in your terminal the warning may appear above the list rather than below it.) To change the original, do it in one step with .loc[rows, column]:

python
orders.loc[orders["branch"] == "north", "note"] = "main branch"

print(orders[["branch", "note"]])
text
branch         note
0  north  main branch
1  south          NaN
2  north  main branch
3   east          NaN
4  north  main branch
5  south          NaN
6   east          NaN
7  north  main branch

Rows that match get the value; the column is created if needed; rows that do not match get NaN. One step, the original table, no warning.

A complete example

enrich.py answers the shop owner's whole message, following the plan: read, check, add columns, check again.

python
import numpy as np
import pandas as pd

MANAGER_OF = {
    "north": "Asha",
    "south": "Omar",
    "east": "Lina",
}

orders = pd.read_csv("orders.csv")
rows_before = len(orders)

# 1. Conversions first, so later steps get the right types.
orders["date"] = pd.to_datetime(orders["date"])

# 2. New columns, one rule each.
orders["revenue"] = orders["quantity"] * orders["price"]
orders["size"] = np.where(orders["revenue"] >= 500, "big", "small")
orders["manager"] = orders["branch"].map(MANAGER_OF)
orders["weekday"] = orders["date"].dt.day_name()
branch_total = orders.groupby("branch")["revenue"].transform("sum")
orders["branch_share"] = (orders["revenue"] / branch_total).round(3)

# 3. Checks: same rows, nothing missing, the hand-worked row is right.
new_columns = ["revenue", "size", "manager", "weekday", "branch_share"]
assert len(orders) == rows_before
assert orders[new_columns].isna().sum().sum() == 0
assert orders.loc[0, "revenue"] == 180.0

print(orders[["order_id", "branch", "manager", "weekday", "revenue", "size", "branch_share"]])
print()
print(orders[new_columns].dtypes)
text
order_id branch manager   weekday  revenue   size  branch_share
0      1001  north    Asha    Friday    180.0  small         0.069
1      1002  south    Omar    Friday    300.0  small         0.261
2      1003  north    Asha  Saturday   1700.0    big         0.654
3      1004   east    Lina  Saturday    840.0    big         0.737
4      1005  north    Asha    Sunday    240.0  small         0.092
5      1006  south    Omar    Sunday    850.0    big         0.739
6      1007   east    Lina    Monday    300.0  small         0.263
7      1008  north    Asha    Monday    480.0  small         0.185

revenue         float64
size                str
manager             str
weekday             str
branch_share    float64
dtype: object

Why it is written this way:

  • The conversion comes first. date is turned into a real date before anything uses it, so .dt cannot fail later and nobody downstream has to remember to convert.
  • One rule per line. Each line answers one line of the plan, so when a number looks wrong you know which single line made it.
  • The lookup table is a named constant at the top. When the shop opens a west branch, the change is one line in an obvious place, and the isna check fails loudly if someone forgets.
  • The checks are asserts, not prints. If the row count changes, a .map() misses a branch, or row 0 stops being 180, the program stops instead of printing a wrong report that looks fine.
  • dtypes is printed at the end because the type is part of the answer: revenue should be a float, branch_share a float, the text columns str.

When it breaks

KeyError: 'Price' A column name on the right-hand side is misspelt or has the wrong case. Print list(orders.columns) and copy the name from there. Watch the left-hand side too: orders["revenu"] = ... raises nothing — it creates a new, misspelt column next to the one you meant. If a column you "updated" has not changed, look for a twin with a typo.

ValueError: Length of values (3) does not match length of index (8) You assigned a list (or array) whose length is not the number of rows. A list has no labels, so pandas cannot line it up and refuses. Either build the values from the table's own columns, or check len(values) == len(df) first.

The new column is full of NaN in some rows, and the type turned into float64 You assigned a Series whose labels only partly match the table — usually one built from a filtered table. Assignment matches labels, not positions. Build the new column from the full table (with np.where or .loc[mask, "col"] = ...) instead of from a subset.

revenue contains things like 15.015.015.0…, or TypeError: can't multiply sequence by non-int of type 'float' One of the columns is text, not a number. Multiplying an int by a string repeats the string — the Python rule 3 * "ab" == "ababab" — and pandas applies it row by row without complaint:

python
import pandas as pd
from io import StringIO

RAW = """product,quantity,price
pen,12,15.0
bag,2,"1,200"
"""
bad = pd.read_csv(StringIO(RAW))

print(bad.dtypes)
print(bad["quantity"] * bad["price"])
text
product       str
quantity    int64
price         str
dtype: object
0    15.015.015.015.015.015.015.015.015.015.015.015.0
1                                          1,2001,200
dtype: str

The thousands comma in "1,200" made the whole price column str. This is why the plan says to look at dtypes before the arithmetic. Fix it where it is read, with thousands=",", or convert with pd.to_numeric:

python
good = pd.read_csv(StringIO(RAW), thousands=",")

print(good.dtypes["price"])
print(good["quantity"] * good["price"])
text
float64
0     180.0
1    2400.0
dtype: float64

AttributeError: Can only use .dt accessor with datetimelike values The column is still text. Convert it first with df["date"] = pd.to_datetime(df["date"]) and use .dt after that.

KeyError: 'quantity' from apply You forgot axis=1. Without it, apply passes whole columns to your function, so row["quantity"] looks for a row labelled quantity inside a column and fails. Add axis=1 — and then ask whether you need apply at all.

IntCastingNaNError: Cannot convert non-finite values (NA or inf) to integer You called .astype(int) on a column that contains NaN. A NumPy integer has no way to store "missing". Fill the gaps first (chapter six), or use pandas' nullable integer type, .astype("Int64") with a capital I, which can hold missing values.

ChainedAssignmentError: A value is being set on a copy of a DataFrame or Series through chained assignment. You wrote df[mask]["col"] = value. Under Copy-on-Write the first step makes a separate table, so the original never changes. Write it in one step: df.loc[mask, "col"] = value.

A .map() column has NaN where you expected a value A key in the column is not in the dictionary — a new branch, a different spelling, a trailing space. Find them with df.loc[df["new"].isna(), "key_column"].unique(), then fix the dictionary or the data.