pandas · Chapter 6 of 22

Filtering rows with masks and query

By the end of this chapter you can keep only the rows you want from a DataFrame: with a comparison that gives a mask, with several conditions joined by &, | and ~, with isin and between, and with query, and you can say which labels the rows you kept still have. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Selecting and cleaning
  • 4 tasks
  • pandas
  • In the all labs plan

The first 2 chapters of pandas are free. This one comes with the all labs plan.

Why you need this

Most questions about Kargha's orders start with "only the ones that...". Shabnam wants the big delivered orders from a few cities she is thinking of opening pop-up stalls in. Harpreet wants the marketplace orders that came back from one city, because the marketplace refunds its commission on returns and she claims it city by city. Gopal wants his test orders on the new checkout gone before anybody adds up April.

With what you know so far, you can pick rows by their labels with loc, but first you have to find the labels. So you would walk the rows in a Python loop and collect them, here for the returned lines of more than one piece:

lines = pd.read_csv("/workspace/data/orders_site.csv")
keep = []
for label, status, qty in zip(lines.index, lines["status"], lines["qty"]):
    if status == "returned" and qty > 1:
        keep.append(label)
two_back = lines.loc[keep]
print(len(two_back))
98

It works, but it is five lines for one question, a new loop for every question, and a loop in Python visits 14,128 lines one at a time. Timed in IPython, with the loop inside a function by_loop(lines), against the one line of this chapter that does the same:

In [4]: %timeit by_loop(lines)
7.73 ms ± 149 μs per loop (mean ± std. dev. of 7 runs, 100 loops each)

In [5]: %timeit lines[(lines["status"] == "returned") & (lines["qty"] > 1)]
406 μs ± 21.6 μs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)

About 19 times faster, and it reads like the question. That one line is this chapter.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    big_orders.py: Shabnam's big delivered orders

    Write big_orders(lines, cities, min_price) in big_orders.py. lines is the website's lines, as read_csv reads orders_site.csv; cities is a list of city names; min_price is a number.

    Keep the lines whose status is "delivered", whose city is one of cities, written exactly as in the list (a city typed "pune" or " Pune" is not kept: that is chapter 10), and whose price_rs is over min_price (a price equal to min_price is not over). Return a DataFrame with only the columns order_id, city, sku and price_rs, in this order, one row for each line kept, in the table's order, with the lines' own labels from lines (not 0, 1, 2).

    The Check tries the website's lines with ["Pune", "Jaipur", "Lucknow"] and 10000, with ["Varanasi", "Patna"] and 20000, and with ["Pune"] and 40000, which no line is over: then the result has no rows but still has the four columns. It also tries a small table of six lines with a price exactly on min_price and a city typed in small letters.

  2. Task 2

    city_returns.py: one city's marketplace returns, with query

    Write city_returns(market, city) in city_returns.py, using the DataFrame's query method (the Check reads your code for it). market is the marketplace's orders, as read_excel reads the Orders sheet of orders_marketplace.xlsx with sheet_name="Orders" and skiprows=2; city is a city in capital letters, as the marketplace writes it, such as "LUCKNOW".

    Return a DataFrame of the orders whose Status is "Returned" and whose Ship City is city, with every column of market, in the table's order, with the orders' own labels from market.

    The Check tries "LUCKNOW", "BOMBAY", "NAGPUR" (no orders: the result has no rows but every column) and a small table of four orders labelled 7 to 10.

  3. Task 3

    test_orders.py: Gopal's test orders out

    Write two functions in test_orders.py. lines is the website's lines, as read_csv reads orders_site.csv.

    without_tests(lines) returns a DataFrame of every line whose customer_id is not "TEST", with every column, in the table's order, labelled 0, 1, 2 and so on again, like a new table.

    count_tests(lines) returns how many lines have the customer_id "TEST", as one whole number.

    The Check tries the website's lines, a small table of six lines with one test line labelled 42, and the same small table without it (then the count is 0 and every line is kept).

  4. Task 4 · interview question

    Interview: cod_orders.py, and between two conditions

    Harpreet's big_cod(lines, min_price) should return the website's lines paid by COD with a price_rs of min_price or more, but it stops with ValueError: The truth value of a Series is ambiguous. Use a.empty, a.item(), a.any() or a.all(). Fix it so it returns a DataFrame of the lines whose payment is "COD" and whose price_rs is at least min_price, with every column, in the table's order, with the lines' own labels. lines is the website's lines, as read_csv reads orders_site.csv.

    The Check tries 20000, 30000, a small table with prices exactly on min_price, and 50000, which no line reaches (then the result has no rows but every column).

    Devika will ask: why does "and" between two conditions raise this error, and what does & need that "and" does not?

What you will learn

  • A mask: a comparison on a column gives a bool Series, one True or False for each row, with the table's labels.
  • table[mask]: keeps the rows where the mask is True, with their own labels.
  • &, | and ~: and, or and not for masks, with brackets round each comparison.
  • .loc[mask, columns]: the rows of a mask and only the columns you name.
  • isin and between: a column against a list of values, and against a range.
  • query: the same filter written as text, with @ for a Python variable and backticks for a column name with a space.

More in Selecting and cleaning

Build "Filtering rows with masks and query" yourself

Kargha, a Varanasi handloom brand, sells through a website, a marketplace and a store. You make one true picture of its sales. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of pandas are free. This one comes with the all labs plan.