Getting Started With csvkit for Everyday CSV Tasks

csvkit is a set of command line tools for CSV, written in Python, and it is the one I would install first on a machine where I will handle other people's data. What it gets right is the thing the Unix tools get wrong: every command has a real CSV parser, so quoted commas, doubled quotes and line breaks inside fields are handled the way RFC 4180 says. What it costs is speed and some opinionated type inference. This article covers the everyday commands, the two places that inference bites, and where the speed stops being tolerable. It is one stop in the tour of working with CSV in the shell.

Installing

csvkit is a Python package, and the installation is the usual one. I installed it with pip in the container; a virtual environment or pipx keeps its dependencies out of your projects:

pip install csvkit

It provides a set of commands that all start with csv, plus in2csv and sql2csv. Check the version before you rely on a flag; this article used 2.2.0:

csvcut --version

csvcut 2.2.0

Looking at the shape

Start with the column names, numbered, because most csvkit commands take a column by name or by position:

csvcut -n orders-small.csv

  1: id
  2: customer
  3: city
  4: qty
  5: price
  6: status

csvcut -n printed the six names with positions 1 to 6. csvcut -c selects by either, and it understands the quoting:

csvcut -c customer,price orders-small.csv | head -n 3

customer,price
Ana Silva,9.50
"Smith, Jane",2.25

The output keeps "Smith, Jane" whole and quoted again where it needs to be. That is the difference from cut, explained in the cut article, and it holds for every command here. To read the data comfortably, pipe into csvlook, which draws a table:

csvcut -c id,customer,qty,price orders-small.csv | csvlook

| id | customer      | qty |  price |
| -- | ------------- | --- | ------ |
|  1 | Ana Silva     |   3 |   9.50 |
|  2 | Smith, Jane   |  10 |   2.25 |
|  3 | Bo Chen       |   2 | 100.00 |
|  4 | Cy Dubois     |     |  15.75 |
|  5 | Doe "JD" John |   7 |   3.10 |
|  6 | Ed Zürich     |   1 |   8.00 |
|  7 | Flo           |  12 |   1.05 |
|  8 | Gus           |   5 |  20.00 |

Numbers are right-aligned, Smith, Jane is one cell, and the doubled quotes in Doe "JD" John are shown as the name. The preview behavior, with its speed warning, is covered in the terminal preview article.

Filtering

csvgrep filters rows by a column. -m matches exactly, -r takes a regular expression, and -c names the column:

csvgrep -c status -m refunded orders-small.csv

id,customer,city,qty,price,status
2,"Smith, Jane",Boston,10,2.25,refunded
8,Gus,Riga,5,20.00,refunded

The two refunded orders, header included, are returned as a valid CSV. With a regular expression and a following csvcut:

csvgrep -c customer -r '^[A-C]' orders-small.csv | csvcut -c id,customer

id,customer
1,Ana Silva
3,Bo Chen
4,Cy Dubois

Customers whose name starts with A to C: Ana, Bo and Cy. The commands compose in a pipeline because every one reads and writes CSV, which is the design that makes the toolkit pleasant. The filtering equivalents in Miller, with expressions on several columns, are in the Miller article.

Statistics

csvstat profiles columns: type, nulls, unique values, range, sum, mean, median and the most common values:

csvstat -c qty,price orders-small.csv

  4. "qty"

	Type of data:          Number
	Contains null values:  True (excluded from calculations)
	Non-null values:       7
	Unique values:         8
	Smallest value:        1
	Largest value:         12
	Sum:                   40
	Mean:                  5.714
	Median:                5
	StDev:                 4.152
	Most decimal places:   0
	Most common values:    3 (1x)
	                       10 (1x)
	                       2 (1x)
	                       None (1x)
	                       7 (1x)

  5. "price"

	Type of data:          Number
	Contains null values:  False
	Non-null values:       8
	Unique values:         8
	Smallest value:        1.05
	Largest value:         100
	Sum:                   159.65
	Mean:                  19.956
	Median:                8.75
	StDev:                 33.02
	Most decimal places:   2
	Most common values:    9.5 (1x)
	                       2.25 (1x)
	                       100 (1x)
	                       15.75 (1x)
	                       3.1 (1x)

Row count: 8

For qty it found a Number column with a null (7 non-null values of 8), a range of 1 to 12, a sum of 40, a mean of 5.714 and a median of 5. For price, no nulls, a range of 1.05 to 100, a sum of 159.65 and a median of 8.75. One column's frequency table is a flag away:

csvstat -c status --freq orders-small.csv

{ "paid": 4, "refunded": 2, "pending": 1, "failed": 1 }

--freq on status printed { "paid": 4, "refunded": 2, "pending": 1, "failed": 1 }. This is the most useful five seconds in the toolkit on a new file, on a small file. On a big one, see the timing below.

Sorting and converting

csvsort -c price -r sorts numerically, descending, because csvkit inferred the column as a number:

csvsort -c price -r orders-small.csv | csvcut -c id,customer,price | head -n 4

id,customer,price
3,Bo Chen,100.00
8,Gus,20.00
4,Cy Dubois,15.75

100.00, 20.00 and 15.75, numerically. csvjson converts to JSON, and shows the inference at work:

csvcut -c id,customer,price orders-small.csv | head -n 3 | csvjson --indent 2

[
  {
    "id": 1.0,
    "customer": "Ana Silva",
    "price": 9.5
  },
  {
    "id": 2.0,
    "customer": "Smith, Jane",
    "price": 2.25
  }
]

The id came out as 1.0 and 2.0, not 1 and 2. csvkit's inference reads a column of whole numbers as a number, and it was written back out as a float. This is the first place the inference bites, and the second is worse:

printf 'zip,n\n02134,1\n90210,2\n' > zips.csv
csvjson zips.csv
csvjson --no-inference zips.csv

[{"zip": 2134.0, "n": 1.0}, {"zip": 90210.0, "n": 2.0}][{"zip": "02134", "n": "1"}, {"zip": "90210", "n": "2"}]

The first command, on a file with ZIP codes 02134 and 90210, printed {"zip": 2134.0, "n": 1.0} and {"zip": 90210.0, ...}. The leading zero was lost and the number became a float, a mangling that happens with any tool that infers types, as the pandas ZIP article describes. The second command, with --no-inference, printed "zip": "02134" as text and "n": "1" as text too. If a column is an identifier, turn inference off for the run and convert what you need, or you will learn the lesson from a customer.

SQL on the file

csvsql --query loads one or more files into an in-memory SQLite database and runs a query:

csvsql --tables orders --query "SELECT status, COUNT(*) AS n, ROUND(SUM(qty * price), 2) AS value FROM orders GROUP BY status ORDER BY n DESC" orders-small.csv

status,n,value
paid,4,262.8
refunded,2,122.5
pending,1,
failed,1,8.0

The result is a grouped summary: paid 4 orders worth 262.8, refunded 2 worth 122.5, pending 1 with an empty value and failed 1 worth 8.0. The pending value is empty because that order has no quantity, so qty * price is NULL and a group whose only row is NULL sums to NULL. It is correct SQL and easy to misread as a zero. My first attempt used the file name as the table and failed:

OperationalError: (sqlite3.OperationalError) no such table: orders_small

csvsql names the table after the file stem, orders-small, and the hyphen made it an invalid name in the query. --tables orders sets one explicitly, which is what the working command uses. For heavier SQL on files, DuckDB does the same job faster and without the loading step.

The reverse direction is in2csv, which converts other formats, JSON in this example, to CSV:

printf '[{"id":1,"name":"Ana"},{"id":2,"name":"Bo, Jr."}]' | in2csv -f json

id,name
1,Ana
2,"Bo, Jr."

Two JSON objects became a header and two rows, with "Bo, Jr." quoted correctly. It also reads Excel files and fixed-width files, which I did not test here.

The speed cost

Parsing and inference in Python are slow, and for a 200,000 row file the numbers are the thing to know before you pick csvkit for a job:

cd /batches/csv-0001-0200/work
for c in "csvcut -c amount,country orders-200k.csv" "csvgrep -c status -m refunded orders-200k.csv" "csvstat -c amount orders-200k.csv" "csvsort -c amount orders-200k.csv" "qsv select amount,country orders-200k.csv" "qsv search -s status refunded orders-200k.csv" "qsv stats --select amount orders-200k.csv" "qsv sort --select amount orders-200k.csv"; do
  /usr/bin/time -f "%es" bash -c "$c > /dev/null" 2>&1 | tail -1 | tr '\n' ' '; echo "$c"
done

0.56s csvcut -c amount,country orders-200k.csv
0.53s csvgrep -c status -m refunded orders-200k.csv
59.08s csvstat -c amount orders-200k.csv
64.52s csvsort -c amount orders-200k.csv
0.03s qsv select amount,country orders-200k.csv
0.03s qsv search -s status refunded orders-200k.csv
0.04s qsv stats --select amount orders-200k.csv
0.18s qsv sort --select amount orders-200k.csv

cd /batches/csv-0001-0200/work
for c in "csvsort -c amount orders-200k.csv" "qsv select amount,country orders-200k.csv" "qsv search -s status refunded orders-200k.csv" "qsv stats --select amount orders-200k.csv" "qsv sort --select amount orders-200k.csv" "csvstat --sum -c amount orders-200k.csv"; do
  /usr/bin/time -f "%es" bash -c "$c > /dev/null" 2>&1 | tail -1 | tr '\n' ' '; echo "$c"
done

61.41s csvsort -c amount orders-200k.csv
0.03s qsv select amount,country orders-200k.csv
0.03s qsv search -s status refunded orders-200k.csv
0.03s qsv stats --select amount orders-200k.csv
0.18s qsv sort --select amount orders-200k.csv
75.65s csvstat --sum -c amount orders-200k.csv

csvcut -c amount,country took 0.56 s and csvgrep 0.53 s. Those are fine. csvstat -c amount took 59.08 s and csvsort -c amount 64.52 s, and a repeat gave 61.41 s for the sort and 75.65 s for csvstat --sum. Other jobs were running on the same machine for some of these, so the exact figures are noisy; the order of magnitude is not. The qsv equivalents for the same work took 0.03 s for the select and search, 0.03 s for the stats and 0.18 s for the sort, a factor of about 350 on the sort. At that rate a 2,000,000 row file would take far longer than I was willing to wait, so I did not run it. The qsv article covers the fast tool.

How to tell it worked

Confirm the number of rows in and out with csvstat --count or wc -l on a file with no embedded newlines. Check the types csvstat reports against what the columns mean, and turn inference off if an identifier was read as a number. Look at one row with a quote in it. If a command is slower than you expected, run it on head -n 10000 first.

When csvkit is too slow

For files up to a few tens of thousands of rows, csvkit is a pleasant interface and the speed does not matter. For anything in the hundreds of thousands, use it for the cheap commands (csvcut, csvgrep, in2csv) and not for csvstat, csvsort or csvsql. For millions of rows, reach for qsv, Miller or DuckDB for the heavy steps, and keep csvkit for converting formats, where nothing else is as complete. It earns its place on the machine as the thing that gets a file into CSV and tells you the truth about it.