pandas · Chapter 9 of 22

Duplicates

By the end of this chapter you can find rows that appear more than once, exact copies or copies on a key, look at every copy before you delete anything, keep the first or the last of each, and say whether a repeated value is a mistake or the way the data is meant to be. 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

On 2 March the website's export job ran twice from noon, so every order line placed that afternoon is in the file twice, exactly. Nothing in the file says so, and every total that includes March is too high: the sarees and dupattas of that afternoon count double in Shabnam's report and in the weavers' pay. The marketplace has the opposite habit. When an order's status changes, it sends the order again, so some Order Ids appear twice: first as Shipped, later with the final status. Harpreet needs one row per order there, the latest one, or the same sale is settled twice.

And a repeated value is not always a mistake. An order with three products is three order lines with one order_id, on purpose. Before this chapter, the only way to spot a repeat was to read the rows, and on 14,128 lines nobody does. This chapter finds them for you, and shows how a careless "remove duplicates" deletes real sales.

What you build: 4 tasks

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

  1. Task 1

    exact_duplicates.py: the export that ran twice

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

    without_duplicates(lines) returns a DataFrame of lines with each exact copy taken out: of two rows equal in every column, the first stays. It has every column, in the table's order, with the rows' own labels from lines (not 0, 1, 2).

    duplicate_count(lines) returns how many rows without_duplicates takes out, as one whole number.

    Two lines of one order share an order_id but are different products: only a row equal in every column is a copy. The Check tries the website's lines, a small table of six lines labelled 60 to 65 with one line there three times, and the same table without its copies (then nothing is taken out and the count is 0).

  2. Task 2

    last_status.py: each marketplace order's last status

    Write last_status(market) in last_status.py. market is the marketplace's orders, as read_excel reads the Orders sheet of orders_marketplace.xlsx with sheet_name="Orders" and skiprows=2. The marketplace sends an order again when its status changes, so some Order Ids appear more than once, and the rows are in the order they were sent: a lower row is a later status.

    Return a DataFrame with one row for each Order Id, the last row the marketplace sent for it, with every column, in the table's order, with the rows' own labels from market.

    The Check tries the marketplace's orders and a small table of five rows labelled 30 to 34, with one order sent three times.

  3. Task 3

    order_counts.py: orders, not lines, by payment type

    Write orders_by_payment(lines, payments) in order_counts.py. lines is the website's lines, as read_csv reads orders_site.csv; payments is a list of payment types, such as ["UPI", "Card", "COD", "NetBanking"].

    Return a Series labelled by the payment types, in the list's order, whose values are how many different orders (different order_id values) were paid that way, as whole numbers. An order counts for a payment type when a line of it has that payment; a few lines have a blank payment, and they count for no type. A type with no orders has 0. For three lines of order KS-1 and one line of KS-2, all paid by UPI, the value for "UPI" is 2.

    An order of three lines is one order: count the orders without dropping any line. The Check tries the website's lines with the four types and with ["COD", "UPI"], and a small table of two orders with ["Card", "COD", "UPI"], where nobody paid COD.

  4. Task 4 · interview question

    Interview: revenue.py, the revenue that fell too far

    Harpreet's revenue(lines) takes the duplicates out with drop_duplicates(subset=["order_id"]) and then adds up price_rs times qty, but the total fell by far more than the copied lines were worth. Fix it so it returns one decimal number: the sum of price_rs times qty over every line, counting each exact copy only once (a row equal in every column to an earlier one). Lines with a blank qty add nothing, as sum skips them, and every status counts. lines is the website's lines, as read_csv reads orders_site.csv.

    The Check tries the website's lines and a small table of two orders labelled 60 to 65, with one line there three times.

    Devika will ask: why did drop_duplicates on order_id make the revenue fall by more than the duplicates?

What you will learn

  • duplicated: a mask, True for each row that repeats an earlier one; keep= chooses which copy counts as the original.
  • drop_duplicates: the table without the repeats.
  • subset=: duplicates on a key (one or a few columns) instead of the whole row.
  • is_unique and nunique: whether a column repeats, and how many different values it has.
  • Look before you delete: every copy side by side with keep=False.
  • A line or an order: when a repeated key is the data, not a mistake.

More in Selecting and cleaning

Build "Duplicates" 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.