Filters and sorting
In short: Filtering in pandas keeps only the rows where a mask is True (or isin, between, query), and sort_values orders them by a column.
Problem
The list for the supplier
The food supplier comes tomorrow. The manager wants a list: the food products we have fewer than 20 of, most urgent first. Then they also ask about the orders over the year: how many were cancelled and how many customers buy from us regularly.
You can scan 25 products by eye. The orders for the year are 11,116, and there your eyes are not enough. You need a way to tell the table “give me only these rows” and “order them like this”.
The idea
A mask on a table
The mask from NumPy works in pandas too. products['stock'] < 20 compares every row and gives a column (a Series) of True and False, with the table’s index. Put inside square brackets, the mask keeps the rows with True:
low.sum() counts the Trues: 9 products are under 20 in stock. This is called a filter: a table with only the rows for which the condition is true.
Look at the index on the left: 3, 6, 7, 12… The rows keep their labels from products, so you always know where each one came from. Inside [], a column name picks a column, and a mask picks rows.
The idea
Two conditions: &, | and ~
Under 20 in stock and under €5 are two conditions together. With and you get the same as in NumPy:
and wants one True or False, and the column has 25. So here too you write & for “and”, | for “or” and ~ for “not”, with every condition in brackets:
Under 20 in stock and under €5 are the banitsa and the cucumbers, both sold out. Under €1 or over €20 are the butter roll (€0.80), the water (€0.90), the frying pan (€24.90) and the knife set (€39). The gift card is exactly €20 and is not “over 20”.
Try it
The dearer dairy
Which dairy products (category 4) cost over €3? Put the table of them in pricey_dairy.
Solution
import pandas as pd
products = pd.read_csv('products.csv')
pricey_dairy = products[(products['category_id'] == 4) & (products['price'] > 3)]
pricey_dairy[['name', 'price']]products[(products['category_id'] == 4) & (products['price'] > 3)] gives 3 products: the white cheese (€7.90), the kashkaval (€9.50) and the butter (€3.20). With | all dairy and everything over €3 gets in, and without the brackets the code fails with an error. The mask on its own, without products[...], is only a column of True and False.
The idea
From a list and between two numbers: isin and between
Food is in four categories: 3, 4, 5 and 6. Instead of four conditions with | you write isin, which asks “is it in this list?”:
There are 18 food products. With ~ you get the other 7: the six home products and the gift card. The card has no category (NaN), and NaN is in no list, so ~ catches it.
For a range there is between. between(2, 5) is “from 2 to 5 inclusive”: both ends are in:
It gives 9 products, as many as (prices >= 2) & (prices <= 5) in the NumPy lesson.
More precisely, if you are curious
between(2, 5, inclusive='neither') leaves out both ends, and 'left' and 'right' keep only one of them. The default is 'both'.
The idea
Text that contains: str.contains
“Show me all the breads.” The names include “White bread” and “Wholegrain bread”, but you cannot write == 'bread': no name is exactly “bread”. str.contains asks whether the text appears anywhere in the name:
.str opens the text methods for the whole column. You will see it in more detail in the lesson on types.
The search tells capitals from small letters. “cheese” finds “White cheese” and “Kashkaval cheese”, so the first number is 2. “Cheese banitsa” starts with a capital C and is left out. With case=False the letters are not told apart and you get 3.
The idea
The condition as text: query
Long masks with products['...'] everywhere get hard to read. query takes the condition as text, where the column names stand on their own:
In query you write and, or and not. The text is not computed by Python but by pandas, and pandas knows these are columns. Under 20 in stock and over €5 are 7 products.
A Python variable goes in with @: category_id in @food is the same as isin(food). That gives the five food products under 20 in stock, which you will turn into the list for the supplier.
The idea
Sorting: sort_values and nlargest
sort_values('price') orders the rows by price, from small to large. With ascending=False it is the other way round. For “the top N” there is nlargest(N, column) and nsmallest:
The most expensive are the knife set (€39), the frying pan (€24.90) and the gift card (€20). nlargest(3, 'price') gives the same three in one line.
You can also sort by several columns: first by category, and within it by price. ascending is then a list, one for each column. The categories go up and the prices inside them go down: the banitsa (€2.50) is first in bread, the kashkaval (€9.50) in dairy.
More precisely, if you are curious
Sorting moves the rows but does not change their labels: the index in the output is 22, 21, 24… If you need the numbers 0, 1, 2 in the new order, add .reset_index(drop=True).
The idea
How many of each: value_counts
“How many orders were cancelled?” is a counting question. value_counts counts how many times each value appears in a column, the most frequent on top:
Of the 11,116 orders for the year, 10,254 were delivered and 657 cancelled. With normalize=True you get a share instead of a count: 0.059, that is 5.9% of orders were cancelled.
The same on the customer number tells you who ordered how many times:
1,390 customers have ordered. The most loyal, customer 1, ordered 121 times, and 224 customers ordered only once. The index of the result is the customer numbers, and the values are how many orders each one made.
Try it
The open orders
Orders with the status new, paid or shipped have not reached the customer yet. Put the table of them in open_orders.
Solution
import pandas as pd
year = pd.read_csv('year_orders.csv')
open_orders = year[year['status'].isin(['new', 'paid', 'shipped'])]
print(len(open_orders))year[year['status'].isin(['new', 'paid', 'shipped'])] gives 205 orders: 90 new, 63 paid and 52 shipped, exactly as value_counts says. != 'delivered' would also let in the 657 cancelled ones, and those will not ship.
Exercise
The list and the regular customers
Make the list for the supplier: the food products (categories 3, 4, 5 and 6) with stock under 20, sorted by stock from the smallest. With equal stock, the dearer product goes first. Put the table in restock.
Solution
import pandas as pd
products = pd.read_csv('products.csv')
food = [3, 4, 5, 6]
restock = products[products['category_id'].isin(food) & (products['stock'] < 20)]
restock = restock.sort_values(['stock', 'price'], ascending=[True, False])
restock[['name', 'price', 'stock']]The filter isin(food) & (stock < 20) gives 5 food products. sort_values(['stock', 'price'], ascending=[True, False]) orders them: the cucumbers (€2.80, 0 in stock), the banitsa (€2.50, 0 in stock), the kashkaval (12), the coffee (15) and the white cheese (18). Without isin the frying pan, the knife set, the cutting board and the laundry powder get in too, and with ascending=False for both columns the most urgent go to the bottom.
How many customers ordered at least 20 times in the year? Put their number in regulars.
Solution
import pandas as pd
year = pd.read_csv('year_orders.csv')
per_customer = year['customer_id'].value_counts()
regulars = (per_customer >= 20).sum()
print(regulars)per_customer = year['customer_id'].value_counts() gives the number of orders of each customer, and (per_customer >= 20).sum() counts the customers with at least 20: there are 119. (year['customer_id'] >= 20).sum() gives 9,471, because it compares customer numbers, not their orders. With > 20 you get 110: you miss those with exactly 20.
Test