New columns

In short: A new column in pandas is computed from the other columns at once: arithmetic, np.where or map; assign returns a new table, and apply is the slowest way.

Problem

What each line is worth

order_items.csv tells you, for every line of an order, which product it is, how many units and at what price. What the whole line was worth is not written anywhere:

Pythondata: the shop tables

import pandas as pd

items = pd.read_csv('order_items.csv')
print(items.shape)
items.head()

import pandas as pd items = pd.read_csv('order_items.csv') print(items.shape) items.head()

▶ RunRestore the code

The manager wants the revenue, but without cancelled orders. They also want to know which lines are large, and the report should have category names, not numbers. For all of this you need columns that are not in the file. You will compute them from the ones that are.

The idea

A column from arithmetic

A column is a Series, and arithmetic with Series is vectorised, as with NumPy arrays. Units times price gives the value of all 152 lines at once. Store the result under a new name and the table gets a new column:

Pythondata: the shop tables

import pandas as pd

items = pd.read_csv('order_items.csv')
items['total'] = items['quantity'] * items['unit_price']
print(items.head())
print(round(items['total'].sum(), 2))

import pandas as pd items = pd.read_csv('order_items.csv') items['total'] = items['quantity'] * items['unit_price'] print(items.head()) print(round(items['total'].sum(), 2))

▶ RunRestore the code

total appeared on the right: the first line is 4 units at €7.90, that is €31.60. If the name already exists, the column is replaced with the new values.

The sum of all lines is €1,963.50. That is not the revenue yet: these lines also include orders that customers cancelled.

See it

Revenue without the cancelled

A cancelled order is not paid, so it does not count towards revenue. The status is in orders.csv, and the lines are in order_items.csv. You take the numbers of the cancelled orders with loc, and then isin from the last lesson finds their lines:

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('orders.csv')
items = pd.read_csv('order_items.csv')
items['total'] = items['quantity'] * items['unit_price']

cancelled = orders.loc[orders['status'] == 'cancelled', 'id']
print(cancelled.tolist())
kept = items[~items['order_id'].isin(cancelled)]
print(len(items), len(kept))
print(round(kept['total'].sum(), 2))

import pandas as pd orders = pd.read_csv('orders.csv') items = pd.read_csv('order_items.csv') items['total'] = items['quantity'] * items['unit_price'] cancelled = orders.loc[orders['status'] == 'cancelled', 'id'] print(cancelled.tolist()) kept = items[~items['order_id'].isin(cancelled)] print(len(items), len(kept)) print(round(kept['total'].sum(), 2))

▶ RunRestore the code

7 orders were cancelled. Without their lines, 131 of 152 remain, and the revenue is €1,697.00. The rule holds for the whole course: when the question is about revenue, cancelled orders do not count.

Try it

Money on the shelves

Add a stock_value column to products: how much money is held in stock for each product (price times stock). Then put in top3 the three products with the largest stock_value, without the gift card (id 25).

Pythondata: the shop tables

import pandas as pd

products = pd.read_csv('products.csv')
products['stock_value'] = ...
top3 = ...

import pandas as pd products = pd.read_csv('products.csv') products['stock_value'] = ... top3 = ...

Check▶ Run

The idea

A new table with assign

items['total'] = … changes the table itself. assign does the opposite: it returns a new table with one more column, and the old one stays the same. So you can go straight on with a filter:

Pythondata: the shop tables

import pandas as pd

items = pd.read_csv('order_items.csv')
big = items.assign(total=items['quantity'] * items['unit_price']).query('total >= 50')
print(big)
print(items.columns.tolist())

import pandas as pd items = pd.read_csv('order_items.csv') big = items.assign(total=items['quantity'] * items['unit_price']).query('total >= 50') print(big) print(items.columns.tolist())

▶ RunRestore the code

6 lines are worth €50 or more. The last line shows that items has no total: assign did not touch it. This is Copy-on-Write from the lesson on selecting: a change gives a new table, and the original does not change.

Inside assign you can pass a function instead of a column. lambda d: … gets the table at that moment, so the second assign sees total from the first:

Pythondata: the shop tables

import pandas as pd

items = pd.read_csv('order_items.csv')
(items
    .assign(total=lambda d: d['quantity'] * d['unit_price'])
    .assign(with_vat=lambda d: (d['total'] * 1.2).round(2))
    .head(3))

import pandas as pd items = pd.read_csv('order_items.csv') (items .assign(total=lambda d: d['quantity'] * d['unit_price']) .assign(with_vat=lambda d: (d['total'] * 1.2).round(2)) .head(3))

▶ RunRestore the code

A sequence of methods like this, each working on the result of the one before, is called a chain. The brackets around it let you write it over several lines.

The idea

One or the other: np.where

A “large” line is worth €20 or more. np.where from the NumPy lesson works with columns too: for every row it takes one value or the other:

Pythondata: the shop tables

import pandas as pd
import numpy as np

items = pd.read_csv('order_items.csv')
items['total'] = items['quantity'] * items['unit_price']
items['size'] = np.where(items['total'] >= 20, 'large', 'small')
print(items.head())
items['size'].value_counts()

import pandas as pd import numpy as np items = pd.read_csv('order_items.csv') items['total'] = items['quantity'] * items['unit_price'] items['size'] = np.where(items['total'] >= 20, 'large', 'small') print(items.head()) items['size'].value_counts()

▶ RunRestore the code

There are 35 large lines and 117 small ones. The result has one value for every row, so it becomes a column.

The idea

Number to name: map

In products the category is a number, and the manager wants a name. map with a dictionary replaces every value with the one that matches it in the dictionary:

Pythondata: the shop tables

import pandas as pd

products = pd.read_csv('products.csv')
categories = pd.read_csv('categories.csv')
names = {3: 'Bread and pastry', 4: 'Dairy', 5: 'Fruit and vegetables', 6: 'Drinks', 7: 'Cleaning', 8: 'Kitchen'}
products['category'] = products['category_id'].map(names)
print(products[['name', 'category_id', 'category']].tail(3))

lookup = categories.set_index('id')['name']
products['category_id'].map(lookup).head(3)

import pandas as pd products = pd.read_csv('products.csv') categories = pd.read_csv('categories.csv') names = {3: 'Bread and pastry', 4: 'Dairy', 5: 'Fruit and vegetables', 6: 'Drinks', 7: 'Cleaning', 8: 'Kitchen'} products['category'] = products['category_id'].map(names) print(products[['name', 'category_id', 'category']].tail(3)) lookup = categories.set_index('id')['name'] products['category_id'].map(lookup).head(3)

▶ RunRestore the code

3 becomes “Bread and pastry”, 8 becomes “Kitchen”. The gift card has no category, there is no key for it in the dictionary, and map leaves NaN.

You do not have to type the dictionary by hand. categories.set_index('id')['name'] is a Series with the numbers as its index and the names as its values, and map works with such a Series too: it looks the value up in its index.

1. The dictionary names has no key for the gift card. What does map give for it?

The old value, the NaN from category_id, turned into textNaN: there is no value for a missing keyA KeyError

Check

The idea

Your own function: apply

The shop gives a discount on a large line: 10% from €50 up and 5% from €20 up. The rule is a Python function with if. apply runs it for every value in the column:

Pythondata: the shop tables

import pandas as pd

items = pd.read_csv('order_items.csv')
items['total'] = items['quantity'] * items['unit_price']

def discount(total):
    if total >= 50:
        return 0.10
    if total >= 20:
        return 0.05
    return 0.0

items['discount'] = items['total'].apply(discount)
print(items['discount'].value_counts())
print(round((items['total'] * items['discount']).sum(), 2))

import pandas as pd items = pd.read_csv('order_items.csv') items['total'] = items['quantity'] * items['unit_price'] def discount(total): if total >= 50: return 0.10 if total >= 20: return 0.05 return 0.0 items['discount'] = items['total'].apply(discount) print(items['discount'].value_counts()) print(round((items['total'] * items['discount']).sum(), 2))

▶ RunRestore the code

117 lines get no discount, 29 get 5% and 6 get 10%. All the discounts together come to €84.14.

apply is handy when the rule is already a function that works with one value. This rule could also be written vectorised, with two np.where one inside the other. On 152 lines you will not feel the difference. On many lines you will.

See it

What apply costs

Here is the same arithmetic done two ways over all 26,619 lines of the orders for the year. apply(…, axis=1) runs the function for every row, and time.perf_counter() measures how much time passes:

Pythondata: the shop tables

import pandas as pd
import numpy as np
import time

year_items = pd.read_csv('year_items.csv')
print(len(year_items))

start = time.perf_counter()
slow = year_items.apply(lambda row: row['quantity'] * row['unit_price'], axis=1)
slow_ms = (time.perf_counter() - start) * 1000

start = time.perf_counter()
fast = year_items['quantity'] * year_items['unit_price']
fast_ms = (time.perf_counter() - start) * 1000

print(f'apply: {slow_ms:.0f} ms, vectorised: {fast_ms:.1f} ms')
print(np.allclose(slow, fast))
print(slow_ms > 20 * fast_ms)

import pandas as pd import numpy as np import time year_items = pd.read_csv('year_items.csv') print(len(year_items)) start = time.perf_counter() slow = year_items.apply(lambda row: row['quantity'] * row['unit_price'], axis=1) slow_ms = (time.perf_counter() - start) * 1000 start = time.perf_counter() fast = year_items['quantity'] * year_items['unit_price'] fast_ms = (time.perf_counter() - start) * 1000 print(f'apply: {slow_ms:.0f} ms, vectorised: {fast_ms:.1f} ms') print(np.allclose(slow, fast)) print(slow_ms > 20 * fast_ms)

▶ RunRestore the code

The result is the same (True on the second-to-last line). In our runs apply was over a hundred times slower than the vectorised arithmetic. The exact numbers depend on the machine, but the gap stays.

The rule: first you look for vectorised arithmetic, np.where or map. apply is left for a rule that cannot be written any other way.

More precisely, if you are curious

apply with axis=1 makes a small Series out of every row and calls your Python function 26,619 times. The vectorised arithmetic goes over the column once, in a C loop inside NumPy. That is why the gap grows with the number of rows: on 152 rows it is invisible, and on a million it is the difference between a second and a minute.

The idea

The report: rename and drop

Before you send the table to the manager, you remove the columns they do not need and give names they understand. drop(columns=[…]) removes columns, and rename(columns={old: new}) renames them:

Pythondata: the shop tables

import pandas as pd

items = pd.read_csv('order_items.csv')
items['total'] = items['quantity'] * items['unit_price']
report = items.drop(columns=['product_id']).rename(columns={'quantity': 'units', 'unit_price': 'price', 'total': 'amount'})
print(report.columns.tolist())
report.head(3)

import pandas as pd items = pd.read_csv('order_items.csv') items['total'] = items['quantity'] * items['unit_price'] report = items.drop(columns=['product_id']).rename(columns={'quantity': 'units', 'unit_price': 'price', 'total': 'amount'}) print(report.columns.tolist()) report.head(3)

▶ RunRestore the code

Both return a new table, as assign does. Columns that are not in the rename dictionary keep their old name.

Exercise

The revenue for the year

What is the revenue for the whole year? Add a total column to year_items (units times price), remove the lines of cancelled orders and put the sum, rounded to 2 decimal places, in revenue.

Pythondata: the shop tables

import pandas as pd

year = pd.read_csv('year_orders.csv')
year_items = pd.read_csv('year_items.csv')
year_items['total'] = ...
cancelled = ...
kept = ...
revenue = ...

import pandas as pd year = pd.read_csv('year_orders.csv') year_items = pd.read_csv('year_items.csv') year_items['total'] = ... cancelled = ... kept = ... revenue = ...

Check▶ Run

How much did coffee beans (product 17) bring in over the year, without cancelled orders? Put the sum, rounded to 2 decimal places, in coffee_revenue.

Pythondata: the shop tables

import pandas as pd

year = pd.read_csv('year_orders.csv')
year_items = pd.read_csv('year_items.csv')
cancelled = year.loc[year['status'] == 'cancelled', 'id']
coffee = ...
coffee_revenue = ...

import pandas as pd year = pd.read_csv('year_orders.csv') year_items = pd.read_csv('year_items.csv') cancelled = year.loc[year['status'] == 'cancelled', 'id'] coffee = ... coffee_revenue = ...

Check▶ Run

Test

Check yourself

1. You run items.assign(total=...) and do not store the result anywhere. What is in items afterwards?

The same 4 columns, without totalNothing: assign deletes the tableThe five columns, total included

2. Which is better for the value of a line over 26,619 lines?

items["quantity"] * items["unit_price"]Both are equally fastitems.apply(lambda row: row["quantity"] * row["unit_price"], axis=1)

3. When is apply with your own function the right choice?

When the rule is Python code with no vectorised form, and the data is not largeAlways, because the code is shorter than np.whereNever: pandas does not allow functions

4. Coffee cost €13.90 until 1 March, and now it is €14.90. Which price do you use for the coffee revenue for the year?

price from products: today’s priceunit_price from the line: the price at the moment of purchaseThe mean of the two prices

Check

Start the lesson over

Related lessons

• Builds on: Summaries and missing values · Series and DataFrame · Selecting: [], loc and iloc · Filters and sorting
• Continues in: Types and missing values · Groups: groupby · Joining: merge · Wide and long: pivot_table and melt
• Tasks on this topic: Tables with pandas