Removing Duplicate Rows From a CSV With Shell Tools

Duplicate rows arrive from re-run exports, overlapping date ranges and merged files. Removing them is one line in the shell, and each of the one-liners has a different answer to three questions: what happens to the header, which of the duplicates survives, and what counts as "the same". Those three decide whether you end up with a clean file or one that is quietly missing real rows. This article runs the options on a small file, then on 2,000,000 rows, and shows the case where a popular one-liner deletes data. It is one stop on the tour of working with CSV in the shell; counting what is duplicated before you remove it is covered in counting rows and unique values, and the awk caveats in the awk article. The CSV quoting rules are in RFC 4180.

The file

cat -A dups.csv

id,email,name$
1,[email protected],Ana$
2,[email protected],Bo$
1,[email protected],Ana$
3,[email protected] ,Ana Silva$
4,[email protected],Cy$
2,[email protected],Bo$

cat -A shows the line endings ($) and any trailing spaces. Records 1 and 2 each appear twice, exactly. Record 3, [email protected] with a capital A, a capital E and a trailing space, is the same email address as record 1 once you clean it, and a different string until you do.

sort -u: the quick one, with two side effects

sort -u sorts the lines and removes duplicates in one pass:

sort -u dups.csv

1,[email protected],Ana
2,[email protected],Bo
3,[email protected] ,Ana Silva
4,[email protected],Cy
id,email,name

The duplicates are gone, and the output has two problems for a CSV. The header id,email,name is now the last line, because it sorts after the digits. And the original order is lost, which matters when the file is in time order. It is fine for a list of values with no header, and I use it that way. For a CSV, the next option is better.

awk: keep the first, keep the order

awk '!seen[$0]++' prints a line only the first time it is seen:

awk '!seen[$0]++' dups.csv

id,email,name
1,[email protected],Ana
2,[email protected],Bo
3,[email protected] ,Ana Silva
4,[email protected],Cy

The header stays on top, rows 1 and 2 appear once each in their original positions, and the near duplicate is still there, since as text it is different. The idea behind it: seen[$0] is zero the first time (so !seen[$0] is true and the line is printed) and ++ makes it one for the next time. It keeps the first occurrence and needs memory for every distinct line, so it handles a file of any length whose distinct lines fit in memory.

To see which rows are duplicated instead of removing them:

tail -n +2 dups.csv | sort | uniq -d

1,[email protected],Ana
2,[email protected],Bo

sort | uniq -d printed the lines that occur more than once: record 1 and record 2. That is the report to run before you delete anything, so that you know what you are deleting.

Duplicates by key

Often "duplicate" means "same email", not "same whole line". awk can key on a column:

awk -F, '!seen[$2]++' dups.csv

id,email,name
1,[email protected],Ana
2,[email protected],Bo
3,[email protected] ,Ana Silva
4,[email protected],Cy

awk -F, '!seen[$2]++' removed the exact duplicates and kept record 3, because its email differs from record 1 in case and trailing space. If your intent was to treat those as the same person, the key needs normalizing first:

awk -F, 'NR == 1 { print; next } { k = tolower($2); gsub(/^ +| +$/, "", k); if (!seen[k]++) print }' dups.csv

id,email,name
1,[email protected],Ana
2,[email protected],Bo
4,[email protected],Cy

With tolower and a trim of leading and trailing spaces, the key for record 3 equals the key for record 1, so it is dropped. The output is 1, 2, 4. Whether [email protected] and [email protected] should be merged is a question about your data, not awk; for email addresses it is usually yes, and for most other text it is not. Decide, and write the rule where the next person can find it.

Where awk deletes good data

awk -F, does not understand quotes, and a key on a column that can contain a comma is wrong. A three-row file with two different people who share a surname:

printf 'id,name\n1,"Smith, Jane"\n2,"Smith, John"\n3,Bo\n' > smiths.csv
awk -F, '!seen[$2]++' smiths.csv

id,name
1,"Smith, Jane"
3,Bo

The rows were 1,"Smith, Jane", 2,"Smith, John" and 3,Bo. The result has rows 1 and 3. Row 2, a distinct person, is gone. For awk, field 2 of both Smith rows is "Smith, so the second looked like a duplicate of the first and was dropped. No error, no warning, a file that looks fine and has lost a record. This is the most expensive version of the quoting trap, because the loss is permanent if you overwrite the input. The quote check is the same as elsewhere: grep -c '"' before you trust a column-keyed awk.

qsv and Miller: CSV-aware

Both parse quotes, so the key is the real field:

qsv dedup dups.csv
echo ---
qsv dedup -s email dups.csv
echo --- dupes
qsv dedup --dupes-output /tmp/dupes.csv dups.csv > /dev/null; cat /tmp/dupes.csv

id,email,name
1,[email protected],Ana
2,[email protected],Bo
3,[email protected] ,Ana Silva
4,[email protected],Cy
2
---
id,email,name
3,[email protected] ,Ana Silva
1,[email protected],Ana
2,[email protected],Bo
4,[email protected],Cy
2
--- dupes
2
id,email,name
1,[email protected],Ana
2,[email protected],Bo

qsv dedup removes exact duplicates; the number printed after each block of output is the count of duplicates it removed, 2. Note that its output is sorted, not in the original order: with -s email the capital-A address came first. That is the same trade as sort -u, with the header handled for you. To keep what it removed, --dupes-output writes the discarded rows to a file, which in my run held records 1 and 2. And on the quoted file:

printf 'id,name,note\n1,"Smith, Jane",a\n2,"Smith, Jane",b\n3,Bo,c\n' > q.csv
awk -F, '!seen[$2]++' q.csv
echo --- qsv
qsv dedup -s name q.csv

id,name,note
1,"Smith, Jane",a
3,Bo,c
--- qsv
id,name,note
3,Bo,c
2,"Smith, Jane",b
1

On a file where two rows share the name "Smith, Jane", awk's output kept rows 1 and 3, and qsv dedup -s name kept rows 3 and 2: it treated both Smiths as one key, correctly, because they are the same name, and it kept the later of the two. Which of a set of duplicates a tool keeps is not something to assume, so when it matters (latest record wins), sort first or use SQL with an explicit order. Miller keeps the first, in order:

mlr --csv uniq -a dups.csv
echo ---
mlr --csv head -n 1 -g email dups.csv
echo ---
mlr --icsv --opprint uniq -a -c dups.csv

id,email,name
1,[email protected],Ana
2,[email protected],Bo
3,[email protected] ,Ana Silva
4,[email protected],Cy
---
id,email,name
1,[email protected],Ana
2,[email protected],Bo
3,[email protected] ,Ana Silva
4,[email protected],Cy
---
count id email             name
2     1  [email protected]  Ana
2     2  [email protected]   Bo
1     3  [email protected]  Ana Silva
1     4  [email protected]   Cy

mlr --csv uniq -a removes whole-record duplicates, head -n 1 -g email keeps the first record for each email, and uniq -a -c adds a count column showing how many times each record occurred: 2 for ids 1 and 2, 1 for the rest. The counts are a good audit: a dedupe that reports what it removed is one you can check.

Line endings can hide duplicates

A line that ends \r\n is not the same string as one that ends \n:

printf 'id,name\r\n1,Ana\r\n1,Ana\n2,Bo\r\n' > mixed-eol.csv
awk '!seen[$0]++' mixed-eol.csv | od -c | head -n 4
echo ---
tr -d '\r' < mixed-eol.csv | awk '!seen[$0]++'

0000000   i   d   ,   n   a   m   e  \r  \n   1   ,   A   n   a  \r  \n
0000020   1   ,   A   n   a  \n   2   ,   B   o  \r  \n
0000034
---
id,name
1,Ana
2,Bo

The first dump shows 1,Ana\r\n and 1,Ana\n both kept: awk '!seen[$0]++' saw two different lines. After tr -d '\r' the second block has one 1,Ana. Files assembled from several sources are where this happens, and so do invisible differences like trailing spaces and byte order marks at the start of the first line. If a dedupe leaves an obvious duplicate, run cat -A on the two lines.

Speed on 2,000,000 rows

Deduplicating the orders file by customer_id, which has 249,916 distinct values, one row kept for each:

cd /batches/csv-0001-0200/work
for c in "awk -F, '!seen[\$2]++' orders-2m.csv" "qsv dedup -s customer_id orders-2m.csv" "mlr --csv head -n 1 -g customer_id orders-2m.csv" "duckdb -c \"COPY (SELECT * FROM 'orders-2m.csv' QUALIFY row_number() OVER (PARTITION BY customer_id ORDER BY order_id) = 1) TO '/dev/stdout' (FORMAT csv)\""; do
  /usr/bin/time -f "%es" bash -c "$c | wc -l > /tmp/n.txt" 2>&1 | tail -1 | tr '\n' ' '; printf "%s rows  " "$(cat /tmp/n.txt)"; echo "$c" | cut -c1-70
done

2.92s 249917 rows  awk -F, '!seen[$2]++' orders-2m.csv
3.08s 249917 rows  qsv dedup -s customer_id orders-2m.csv
1.49s 249917 rows  mlr --csv head -n 1 -g customer_id orders-2m.csv
0.71s 249917 rows  duckdb -c "COPY (SELECT * FROM 'orders-2m.csv' QUALIFY row_number() OV

Each produced 249,917 lines, the header plus 249,916 records, which is the check that they agree. The times: awk 2.92 s, qsv dedup 3.08 s, Miller 1.49 s and DuckDB 0.71 s. The DuckDB command picks the row with the lowest order_id for each customer, a rule I wrote down in the query instead of leaving to the tool:

SELECT * FROM 'orders-2m.csv'
QUALIFY row_number() OVER (PARTITION BY customer_id ORDER BY order_id) = 1

QUALIFY filters on a window function, so the query reads as "keep the first order for each customer". The ORDER BY is where you say which duplicate wins: ORDER BY created_at DESC keeps the latest. For anything where the choice has consequences, I prefer this to a tool's default. The syntax is described in the DuckDB documentation, and the general approach in querying CSV with DuckDB.

How to tell it worked

Count the rows before and after, and confirm that the difference equals the number of duplicates you listed. Check the header is still line 1 and appears once. Compare the number of distinct keys before with the number of rows after: they should be equal for a key-based dedupe. Re-run the dedupe on its own output; it should remove nothing. And read the rows that were removed, not only the ones that were kept.

When "duplicate" needs a decision

Exact duplicates are the easy case and are safe to remove. Key-based duplicates are a rule about your data, and the rule can be wrong: two orders for the same customer on the same day are not duplicates, and two customers named John Smith are not either. Near-duplicates, such as a typo in a name or an address written two ways, need fuzzy matching, which none of the shell tools here does. When a human would hesitate over a pair of rows, the rule is not ready. Keep the removed rows in a file, name it with the date, and let someone check a sample.