|0.3.5||Mar 16, 2023|
|0.3.4||Nov 5, 2020|
|0.2.0||Apr 6, 2020|
|0.1.0||Sep 15, 2019|
#380 in Command line utilities
46 downloads per month
clipivot is a tool for creating pivot tables from the command line. It's designed to be fast and memory-efficient so it can be used to aggregate large datasets, and it's designed to be easy to use and easy to debug.
This guide below will provide you with detailed instructions for installing and using the tool. Want to see what it does without reading the documentation? Build your own pivot tables using this interactive tool, and either download the results or copy the commands you'd run in your terminal.
Table of Contents
- Why Pivot Tables?
- Why shouldn't you use
- Usage Guide
- Developer Guide
- Contact Me
You should be able to currently download binaries for Windows, Linux and MacOS on the Releases page of this repository.
Alternatively, you can compile the program using Rust's package manager,
$ cargo install clipivot
Or you can download directly from source.
Why Pivot Tables?
At a basic level, pivot tables exist as a way to aggregate data and extract meaning from datasets.
Say, for example, you have a list of salaries for employees. Each record has a unique identifier for the employee, the employee's salary, and the department the employee worked for. And let's say, because I'm a journalist who's often bored by employee database examples, there's also a field marking whether or not the employee was recently fired.
The dataset looks like this:
id,was_fired,salary,department 1,true,25000,sales 2,true,75000,engineering 3,false,175000,engineering 4,true,65000,sales 5,false,85000,sales
(You can see the file itself in
With this data, you might want to know the number of employees
who were fired from the company, as well as the number employees who remain. You can do that easily with pivot tables. Here's what
that syntax looks like in
$ clipivot count test_csvs/layoffs.csv --rows=was_fired --val=id
That will print this out in your terminal:
,total true,3 false,2
Which tells you that three employees were fired and that two remain.
If you're familiar with SQL, you'll notice that this is similar
GROUP BY queries. In fact, you could run the same thing
I just did in SQL:
SELECT was_fired, COUNT(id) FROM my_table GROUP BY was_fired;
Where pivot tables really provide an advantage over
GROUP BY queries
is in their ability to allow you to control the output columns and rows
If you want to find the total salary of the employees in the
layoffs.csv dataset, aggregated both by the department and by
whether or not they were fired. You could do this in SQL:
SELECT department, was_fired, SUM(salary) FROM my_table GROUP BY department, was_fired;
Which will create a table like this:
department,was_fired,sum engineering,true,75000 engineering,false,175000 sales,false,85000 sales,true,90000
But you might want to set the values from the
was_fired field as columns in the output, instead of as rows. That's trickier to do in SQL.
(I frankly don't know how to do it, but I wouldn't be surprised if it's
With pivot tables, however, it's easy. Here's what that syntax looks like
$ clipivot sum test_csvs/layoffs.csv --rows=department --cols=was_fired --val=salary
Which will give you this output:
,false,true sales,85000,90000 engineering,175000,75000
In other words, pivot tables provide convenient and easy-to-use ways to aggregate datasets.
Why should you use
In a lot of cases,
clipivot isn't necessarily going to be any better
than existing tools for creating pivot tables. In the vast majority of
cases, you can easily do what
clipivot does using
pandas in Python or using R.
And in a number of cases, you can use SQL or existing CSV toolkits like
xsv. You can often use Excel, too, although Excel
doesn't offer good ways to help you document your work or sort your
There are a couple of benefits to using
clipivot over these tools, though.
clipivot is easier to use than any CSV toolkit I'm aware of when it comes to creating pivot tables, because it's narrowly and specifically designed to create pivot tables. And it accepts input
from standard input and file paths and prints to standard output,
allowing you to pipe it into and out of other command-line programs.
clipivot also makes it easy to perform analyses on large datasets, including datasets that exceed the RAM on your computer.
I used the tool to analyze the 80 GB ARCOS dataset the Washington Post acquired on my laptop, which has 16 GB of RAM. In all, it took me about 10 minutes (with the data stored in an HDD external drive) to create a CSV of the total number of oxycodone and
hydrocodone pills flowing into each ZIP code in the United States between 2006 and 2012. And I didn't have to change any settings to get it to work, like I would've had to in
Beyond that, if you're already working at the command line, it can simply be convenient to stay there.
Why shouldn't you use
clipivot isn't always going to be the best tool to use.
Command-line programs are necessarily harder to configure than
libraries in programming languages, so if you need an aggregation
function that isn't supported by
clipivot, it's going to be easier
to use a data science library like
pandas than it will be to configure
clipivot for your use case. (As in, configuring
require you to make significant changes to the source code of
clipivot isn't designed for cleaning data. It has a limited number
of functions that will parse your data, but the parsing is mostly useful
for already well-formed data.
For basic syntax, I recommend that you use the help message provided with the binary:
$ clipivot --help clipivot 0.2.0 Max Lee <email@example.com> A tool for creating pivot tables from the command line. For more information, visit https://www.github.com/maxblee/clipivot USAGE: clipivot [FLAGS] [OPTIONS] <aggfunc> --val <value> [--] [filename] FLAGS: -A, --asc-rows Displays the rows in sorted, ascending order (default is index order). -R, --desc-cols Display column names in sorted, descending order (default is ascending) -D, --desc-rows Displays the rows in sorted, descending order (default is index order). -e Ignores empty/null values ('', NULL, NaN, NONE, NA, N/A) -h, --help Prints help information -I, --index-cols Display column names in index order. Defaults to sorted, ascending order. --no-header Skip the header row of the CSV file. -N Parse values as numeric data. This is only necessary for min, max, and minmax, which can parse strings. -t Set the delimiter of the file to a tab. -V, --version Prints version information OPTIONS: -c, --cols <columns>... The name of the column(s) to aggregate on. Accepts string fieldnames or 0-indexed fields. -d, --delim <delim> The delimiter used to separate fields. Defaults to ','. -F <format> The format of a date field (e.g. %Y-%m-%d for dates like 2010-09-21) -r, --rows <rows>... The name of the index(es) to aggregate on. Accepts string fieldnames or 0-indexed fields. -v, --val <value> ARGS: <aggfunc> The function you use to run across the pivot table. - count counts the number of matching records. - countunique counts the number of unique matching records. - max returns the maximum value of the records given a specified data type. - mean returns the mean. - median returns the median value. Requires numeric data. - min returns the minimum value of the records given a specified data type. - minmax returns both the minimum and maximum values of the records, split by a hyphen. - mode returns the most commonly appearing value. - range returns the difference between the minimum and maximum values. Returns the number of days in the case of dates. - stddev returns the sample standard deviation. - sum returns the sum of the values. [values: count, countunique, max, mean, median, min, minmax, mode, range, stddev, sum] <filename> The path to the file you want to create a pivot table from
That should provide you with a decent overview of the usage of
clipivot. But let me provide a little bit more information.
The basic syntax of
clipivot is simple. Every command needs to have
a function and a values column connected to it. That values column
clipivot which column it needs to apply an aggregation
clipivot needs a data source. This can either be explicitly typed after the name of the function, or it can be in the form of standard input. So the following commands are all equivalent:
$ clipivot count mydata.csv --val id $ cat mydata.csv | clipivot count --val id $ clipivot count --val id < mydata.csv
Finally, you can apply the
--rows options to aggregate
by column. If you don't pass anything to those options, you will have
one row and/or one column named "total" that aggregates over
every single value in your dataset.
There are a variety of names you can give to the
Say we have a header row that looks like this:
In order to access the first column, we can type the following things:
col1: This will grab the first column named
0: This will grab the first column, regardless of the name. (The numbers throughout
clipivotare 0-indexed to conform with standards in most programming languages.)
col1: This will grab the first column named
In order to access the third column, we can type the following things:
0in the above example, this will grab the third column, regardless of the name.
col1: This will grab the second column named
Finally, for the
--cols options, we can grab multiple values. There are several equivalent ways of doing this:
-c col1 -c col2
-c col1 col2
--cols col1 col2
Once we know what columns we want to aggregate on, we need to choose a function. Different functions accept different types of data, so it's important to understand the distinction between them.
At a basic level, functions fit into three categories.
One category interprets every item as text. It will validate that your text is valid UTF-8 but won't do any parsing on top of that. Because of that, most data you encounter should be able to be parsed without error if you are using one of these methods.
In case your data cannot be properly parsed by
clipivot using one of these functions, you can change
the encoding of your file on most Unix-based systems by using
iconv. (The actual process of doing so may be a bit tricky, since figuring out
your file encoding is tricky and inexact, but
chardetect both work pretty well in most cases.) (Note: You will
likely have to install
requires Python and can be installed using
pip, Python's package manager.
uchardet can be installed using Homebrew in Mac or
apt for Linux.)
The functions that parse things as text are
countunique. You can also technically use
minmax to parse text,
but that's primarily aimed at reading through dates, so we'll talk more
about that later.
Some functions only parse numeric data. The following formats all work for numeric data, regardless of the aggregation function:
However, currency markers like dollar signs and thousands separators
cannot be parsed using
clipivot. (If you want to parse those from the
command line, I recommend
These functions are:
stddev (or the sample standard deviation), and
With all of these functions, I have paid special attention to numerical
mean both use Decimal addition in order to avoid
truncation errors, while
stddev uses a numerically stable algorithm. Furthermore, the mean and standard deviation algorithms are both tested
against the Statistical Reference Datasets from the Nation Institute of Standards and Technology.
Numerical or date functions
There are four algorithms designed to work with either numerical data or with dates. They are the minimum, the maximum, minmax (which outputs the minimum and maximum separated by a hyphen) and the range.
In the case of numerical data, the definitions for these terms should be obvious. The minimum refers to the smallest number in the aggregation, the maximum refers to the largest number, the range refers to the difference between the minimum and the maximum, and the minmax outputs the smallest number followed by a hyphen followed by the largest number.
Note: In order to parse
minmax as numeric data,
you must type the
With dates, the minimum refers to the earliest date, so an aggregation containing the dates April 1, 2019 and March 31, 2019 would have a minimum of March 31, 2019. The maximum date is then the most recent date, while the range is the difference between the earliest date and the most recent date, in days.
In order to parse dates as date objects, you must pass the
-F flag, along with a specification for how your datetimes are formatted.
This uses the string formatting options from Rust's
chrono crate, which can be found
clipivot, you can choose how to sort the columns and rows of your pivot table -- by the order in which they appear,
in ascending, alphabetic order, or in descending, alphabetic order. By default, the columns will appear in sorted
ascending error, while the rows will appear in index order. However, you can override those defaults.
--asc-rows, the rows will appear in ascending order; by using
--desc-rows, they will appear in descending order. By using
--desc-cols, the columns will appear in descending order; by using
--index-cols, they will appear in the order in which they appear.
You can also tell
clipivot to use something other than commas
as a field delimiter. By default,
clipivot will assume that files
ending with the
.tab extensions are tab-delimited,
while other files are assumed to be comma-separated. However, both of those can be overridden. You can select any other
single-byte UTF-8 character as a delimiter using the
-d option, or you can use the
-t flag to choose to read tabs as the file dilimiter.
Note: The file extension tool only works when
directly reading a file. If it is receiving tab-delimited data
from standard input, you need to use the
-t flag or the
If you don't have a header row, you can use the
clipivot read the first row as a record, rather than as a header line.
Alternatively, if you have a header row, but it is not on the first
line of your file, you can use
tail -n + to have
clipivot read everything but the nth row. For instance, if the header row of your CSV file
bad_csv.csv is on the fifth line, you can type
tail bad_csv.csv -n +5 | clipivot countunique -v 0
To count the number of unique values in the first column of your bad CSV file.
You can have
clipivot ignore empty values. If you use the
clipivot will skip past any cells that match (case- or whitespace-insensitively) to any of these strings:
- "": an empty string
As this article eloquently
explains, this can be overly aggressive, so you should make sure
this is a reasonable approach for parsing your data. In particular,
I'd recommend spot-checking your data to see which points
interprets as null before using the
Which brings me to:
I've tried to make error handling clear and helpful in
In all, there are four errors you might wind up seeing.
- The first is a simple IO error. It looks like this:
No such file or directory (os error 2)
If you see this error, it probably means you had a typo when you tried to spell the name of your file.
- The second type of error you might see is a configuration error. Configuration errors can take a number of forms, each of which should have a detailed error message providing you with specific information debugging information. One example looks like this:
Could not properly configure the aggregator: Column selection must be between 0 <= selection < 42
If you see that error, there's a decent chance you simply forgot
that fields in
clipivot are zero-indexed.
- The third type of error you might see is a CSV error, from the CSV
clipivotuses. Those errors look like this:
CSV error: record 1 (line: 2, byte: 597): found record with 4 fields, but the previous record has 1 fields
These errors can either come because of malformed CSVs or because
you forgot to specify the correct delimiter (for instance, forgetting
to use the
-t flag when piping in a TSV file from standard input).
- Finally, you might get a parsing error that looks like this:
Could not parse record `NA` with index 167: Failed to parse as numeric
This can be a sign that your file has some null or empty values in it, or that it is not as well-formatted as you might have hoped.
It can also be a sign that
clipivot is trying to parse your data in a different format than you expected (for instance, that it is trying to parse a bunch of strings as dates for the range function, when
you want it to parse everything as a number.)
These errors will all provide you with the string value of the record
clipivot couldn't parse, the index of the record (where the first non-header record has an index of 0), and the type of data that it tried to parse your data into — all of which should make it easier for you to debug.
(As a side note, I recommend pairing this utility with
xsv slice -i, which prints out a row from a CSV file at a given line.)
clipivottechnically allows you to parse the
minmaxfunctions as strings, or text. (In fact, this is the default.) This is almost completely intended to speed up the processing of dates in formats like YYYY-MM-DD that sort alphabetically.
- In cases where there is more than 1 true mode, the mode algorithm here simply returns the value that first reached the maximum number of occurrences (so, if you have a set of values "a, b, b, a", it would return "b", because the second occurrence of "b" happened earlier than the second occurrence of "a.")
- The standard deviation returns the sample standard deviation.
The design for the sorting comes from this issue.
The error handling I've used here comes directly from
this fantastic guide to error handling in Rust. I've additionally
used design components and code snippets from
csv crate in Rust.
A number of other
guides were useful toward getting me to write code in Rust. I've tried to
document all of the guides and source code that helped me develop
clipivot in inline comments and docstrings within the source code.
Other CSV toolkits also helped me design this program. The most direct
connection between these toolkits is probably the approach I've taken to
parsing null values, which is directly inspired by the approach taken
in Python, which serves as the backbone of
And I'm sure there are other, subtler ways in which existing CSV
toolkits have inspired the design of this project. The main toolkits I use are the previously mentioned
xsv and the excellent
csvtk. If you're
interested in doing more things with CSV files from the commmand line,
I strongly recommend them both.
And finally, the CSV files I've used to validate the numerical accuracy
of the mean and standard deviation functions (in
tests/test_numerical_accuracy.rs) are from the Statistical Reference Datasets from the Nation Institute of Standards and Technology.
Outside of the core Rust code, I used code from this guide for uploading release assets and this template for dealing with Rust binaries.
If you want to make changes to
clipivot, I recommend you look at the developer guide, which provides an overview of the design of the code along with some suggestions of things I'd like to see
improved. The guide is designed to allow people with no coding experience,
people who have written code but haven't written any Rust, and people who
have written code in Rust to help. So don't by any means feel like you're not
qualified to improve this project.
If you have any questions about
clipivot or if you have identified any bugs in the program or you want
to contribute to it, please send me an email at firstname.lastname@example.org or contact me through Twitter.
I'm @maxblee. And if you wind up using
clipivot in any of your projects,
I'd love to know.