Records stay intact; filters hide, they do not delete

Three operations that touch every row

Sorting, filtering and de-duplicating rearrange, hide or remove rows — and each has one way to destroy the data set silently. The rule that protects you is the same in every case: a row is a record, and whatever you do must move, hide or drop the whole record.

Worked example — sort by province, then by amount

To see each province's largest orders first, sort orders by prov ascending and, within province, by amount descending. Select the entire table including the header and add two sort levels; both Sheets and Excel let you name the column and direction for each. The result starts AB 1320, AB 980, BC 640 … and every order keeps its own channel, units and date. Select only column D and sort that, and the amounts reorder while the other columns stay put: 1320 now sits beside order 1001, ON, Online. Nothing looks wrong. A sorted selection scrambles records.

R indexes rows instead of moving them. order() returns the row positions that would put its arguments in order, and those positions go inside the square brackets; a minus sign reverses a numeric key:

> orders[order(orders$prov, -orders$amount), ]
   order_id prov channel amount units order_date
5      1005   AB  Online   1320     8 2025-02-03
10     1010   AB   Store    980     6 2025-03-11
8      1008   BC   Store    640     4 2025-02-27
12     1012   BC  Online    470     3 2025-03-25
4      1004   BC  Online    250     2 2025-01-21
3      1003   ON   Store    760     5 2025-01-15

(six of twelve rows shown; the left column is the original row number). sort(orders$amount, decreasing = TRUE) is the sorted-column mistake in R: it returns 1320 980 760 … on its own, detached from every other column. sort() sorts a vector; order() sorts a data frame.

Filter: hidden is not gone

A filter — the funnel on the header row — hides rows that fail a criterion. Filter amount > 500 and five rows remain visible. The hidden seven are still there: a SUM(D2:D13) typed underneath still returns 6140, because ordinary functions see hidden rows. SUBTOTAL(109, D2:D13) sums only the visible ones — 4210 (760 + 1320 + 510 + 640 + 980) — which is the function a filtered total needs. For the matching rows as a new range rather than a view, Sheets and Excel 365 have FILTER:

=FILTER(A2:F13, D2:D13>500)

range first, then a condition the same height as the range; it spills five rows. R's subset() reads almost the same:

> subset(orders, amount > 500)
   order_id prov channel amount units order_date
3      1003   ON   Store    760     5 2025-01-15
5      1005   AB  Online   1320     8 2025-02-03
6      1006   ON  Online    510     4 2025-02-10
8      1008   BC   Store    640     4 2025-02-27
10     1010   AB   Store    980     6 2025-03-11

The row numbers 3, 5, 6, 8, 10 are the original positions — the subset kept whole records and remembers where they came from. orders[orders$amount > 500, ] is the same thing written with a logical vector.

Duplicates: dedupe on the key

An export appends order 1004 a second time; the sheet has 13 rows. Remove duplicates on order_id — the identifier from Lesson 2.1 — and the second copy goes, leaving 12. Remove duplicates on prov instead and the tool keeps one row per province: four rows, eight real orders deleted. In R, duplicated() marks the second and later occurrences:

> duplicated(o2$order_id)
 [1] FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
[13]  TRUE
> nrow(o2[!duplicated(o2$order_id), ])
[1] 12
> sum(duplicated(orders$prov))
[1] 8

!duplicated() keeps the first occurrence of each key. Both tools do the same, so if the later copy is the corrected one, sort it to the top first.

Type three, then answer the scramble question

Type the FILTER, the order() subset and the subset(). The questions turn on which tool keeps records whole.

CRISP-DM: filtering rows is data preparation → select data; removing duplicates is clean data; sorting changes nothing about the data and is preparation for reading it.