clipivot

clipivot 是一个从命令行创建交叉表的工具。它设计得既快速又内存高效，因此可以用于聚合大数据集，并且易于使用和调试。

以下指南将提供有关安装和使用此工具的详细说明。想看看它的工作原理而不用阅读文档吗？使用此交互式工具创建自己的交叉表，然后下载结果或复制您在终端中运行的命令。

目录

• 安装
• 为什么需要交叉表？
• 为什么选择 clipivot？
• 为什么不应该使用 clipivot
• 使用指南
  • 基本使用
  • 行名
  • 函数
  • 排序
  • 分隔符
  • 标题
  • 空值
  • 错误处理
  • 其他信息
• 贡献者
• 开发者指南
• 联系我

安装

您应该能够从该存储库的发行版页面下载 Windows、Linux 和 MacOS 的二进制文件。

或者，您可以使用 Rust 的包管理器 cargo 编译程序：

$ cargo install clipivot

或者，您可以直接从源代码下载。

为什么需要交叉表？

在基本层面上，交叉表作为一种聚合数据和从数据集中提取意义的方式而存在。

例如，假设您有一份员工的薪资列表。每条记录都有一个唯一的员工标识符、员工的薪资以及员工所在的部门。而且，由于我是一个经常对员工数据库示例感到无聊的记者，还有一个标记员工是否最近被解雇的字段。

数据集看起来像这样

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

(您可以在 test_csvs/layoffs.csv 中看到该文件本身。)

使用此数据，您可能想知道有多少员工被公司解雇，以及有多少员工仍在职。您可以用交叉表轻松做到这一点。下面是 clipivot 中的语法：

$ clipivot count test_csvs/layoffs.csv --rows=was_fired --val=id

这将打印以下内容到您的终端：

,total
true,3
false,2

这告诉您有三位员工被解雇，还有两位仍在职。

如果您熟悉 SQL，您会注意到这与运行 GROUP BY 查询类似。事实上，您可以在 SQL 中运行我刚才所做的相同操作。

SELECT was_fired, COUNT(id)
FROM my_table
GROUP BY was_fired;

在数据透视表中，它们相对于 GROUP BY 查询的优势在于，它们允许您轻松控制输出列和行。

如果您想在 layoffs.csv 数据集中找到被解雇的员工的工资总额，并且按部门和是否被解雇进行汇总。您可以在 SQL 中做到这一点

SELECT department, was_fired, SUM(salary)
FROM my_table
GROUP BY department, was_fired;

这将创建一个类似下面的表格

department,was_fired,sum
engineering,true,75000
engineering,false,175000
sales,false,85000
sales,true,90000

但是，您可能希望将 was_fired 字段中的值设置为输出列，而不是作为行。这在 SQL 中会更困难。（坦白说，我不知道如何做，但也不奇怪它可能可行。）

然而，使用数据透视表就很简单了。以下是 clipivot 中的语法看起来是怎样的

$ clipivot sum test_csvs/layoffs.csv --rows=department --cols=was_fired --val=salary

这将给出以下输出

,false,true
sales,85000,90000
engineering,175000,75000

换句话说，数据透视表提供了方便且易于使用的方式来汇总数据集。

为什么你应该使用 clipivot？

在许多情况下，clipivot 并不一定比现有的创建数据透视表的工具更好。在绝大多数情况下，您可以使用 Python 中的 pandas 或 R 来轻松地完成 clipivot 可以做到的事情。在许多情况下，您还可以使用 SQL 或现有的 CSV 工具包，如 csvtk 或 xsv。您通常还可以使用 Excel，尽管 Excel 没有提供很好的方法来帮助您记录您的工作或对数据透视表进行排序。

尽管如此，使用 clipivot 与这些工具相比还有一些好处。

在创建数据透视表时，clipivot 比我所知的任何 CSV 工具包都容易使用，因为它专门设计用来创建数据透视表。它可以接受标准输入和文件路径的输入，并将输出打印到标准输出，这使得您可以将它导入和导出到其他命令行程序。

clipivot 还使您能够轻松地对大型数据集进行分析，包括超过您计算机 RAM 的数据集。我使用这个工具在我的笔记本电脑上分析了华盛顿邮报获取的 80 GB ARCOS 数据集，该笔记本电脑有 16 GB 的 RAM。总的来说，我用大约 10 分钟的时间（数据存储在外部 HDD 硬盘中）创建了一个 CSV 文件，其中包含了 2006 年至 2012 年间流入美国每个 ZIP 码的 oxycodone 和 hydrocodone 药丸的总数。而且我根本不需要更改任何设置就能让它工作，就像我必须在 pandas 中做的那样。

除此之外，如果您已经在命令行中工作，那么简单地留在那里会方便得多。

为什么你不应该使用 clipivot？

clipivot 并不是总是最好的工具。

命令行程序在配置上必然比编程语言中的库更困难，所以如果您需要一个 clipivot 不支持的聚合函数，使用数据科学库如 pandas 比配置 clipivot 来满足您的需求要容易得多。（也就是说，配置 clipivot 将需要您对 clipivot 的源代码进行重大更改。）

并且 clipivot 并不设计用于数据清洗。它具有有限的功能来解析数据，但解析主要适用于已经格式良好的数据。

使用指南

基本使用

对于基本语法，我建议您使用二进制程序提供的帮助信息。

$ clipivot --help
clipivot 0.2.0
Max Lee <[email protected]>
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

这应该为您提供 clipivot 的使用概览。但让我提供更多一点信息。

clipivot 的基本语法很简单。每个命令都需要一个函数和一个与之相连的值列。这个值列告诉 clipivot 需要对哪个列应用聚合函数。

此外，clipivot 需要一个数据源。这可以是在函数名称后显式指定的类型，或者它可以是标准输入的形式。因此，以下命令都是等效的：

$ clipivot count mydata.csv --val id
$ cat mydata.csv | clipivot count --val id
$ clipivot count --val id < mydata.csv

最后，您可以使用 --cols 或 --rows 选项按列进行聚合。如果您不向这些选项传递任何内容，您将有一个名为 "total" 的行和/或列，它会聚合数据集中的每个值。

行名

您可以为 --rows、--cols 和 --val 选项提供各种名称。

假设我们有一行标题看起来像这样：

col1,col2,col1,col3

为了访问第一列，我们可以输入以下内容：

• col1：这将获取名为 col1 的第一列。
• 0：这与上面的示例中的 0 类似，这将获取第一列，无论其名称如何。（在 clipivot 中的数字是 0 索引，以符合大多数编程语言的标准。）
• col1[0]：这将获取名为 col1 的第一列。

为了访问第三列，我们可以输入以下内容：

• 2：与上面的示例中的 0 类似，这将获取第三列，无论其名称如何。
• col1[1]：这将获取名为 col1 的第二列。

最后，对于 --rows 和 --cols 选项，我们可以获取多个值。有几种等效的方法来做这件事：

• --cols=col1,col2
• -c=col1,col2
• -c col1-c col2
• -c col1 col2
• --cols col1 col2

函数

一旦我们知道了想要聚合的列，我们需要选择一个函数。不同的函数接受不同类型的数据，因此理解它们之间的区别很重要。

在基本层面上，函数可以分为三类。

文本函数

其中一类将每个项目解释为文本。它将验证您的文本是否是有效的 UTF-8，但不会进行任何解析。正因为如此，您遇到的大多数数据在使用这些方法时应该能够无错误地进行解析。

如果您的数据无法通过以下函数之一被 clipivot 正确解析，您可以通过使用 iconv 在大多数基于 Unix 的系统上更改文件的编码。 (这个过程可能有点棘手，因为确定文件的编码既困难又不精确，但 uchardet 和 chardetect 在大多数情况下都表现不错。) (注意：您可能需要安装 uchardet 和 chardetect。 chardetect 需要 Python，可以使用 Python 的包管理器 pip 进行安装。 uchardet 可以在 Mac 上使用 Homebrew 或在 Linux 上使用 apt 进行安装。)

将内容解析为文本的函数有 count 和 countunique。您也可以在技术上使用 min、max 和 minmax 来解析文本，但主要目的是读取日期，我们稍后再详细讨论。

数值函数

一些函数只能解析数值数据。以下格式都可以用于数值数据，无论聚合函数如何

• 100
• 1.2
• 1e-6
• 1E-6
• -1.5

然而，货币符号（如美元符号和千位分隔符）无法使用 clipivot 进行解析。（如果您想在命令行中解析这些，我建议使用 csvtk replace。）

这些函数包括：mean、median、stddev（或样本标准差）和 sum。

对于所有这些函数，我都特别注意了数值精度。《sum》和《mean》都使用十进制加法来避免截断错误，而《stddev》使用一种数值稳定的算法。此外，均值和标准差算法都经过美国国家标准与技术研究院的《统计参考数据集》测试。

数值或日期函数

有四种算法旨在与数值数据或日期一起使用。它们是最小值、最大值、minmax（输出由连字符分隔的最小值和最大值）以及范围。

在数值数据的情况下，这些术语的定义应该是显而易见的。最小值是指聚合中最小的数字，最大值是指最大的数字，范围是指最小值和最大值之间的差异，minmax 输出最小的数字，然后是连字符，最后是最大的数字。

注意：为了将 min、max 或 minmax 解析为数值数据，您必须输入 -N 标志。

在日期的情况下，最小值是指最早的日期，因此包含 2019 年 4 月 1 日和 2019 年 3 月 31 日的聚合的最小值是 2019 年 3 月 31 日。然后最大日期是最近的日期，而范围是最早的日期和最近的日期之间的差异（以天为单位）。

为了将日期解析为日期对象，您必须传递 -F 标志，以及关于您的日期时间格式的说明。这使用了 Rust 的 chrono crate 的字符串格式化选项，可以在 这里 找到。

排序

使用 clipivot，您可以选择如何排序交叉表的列和行——按它们出现的顺序、升序或按字母顺序，或者按降序或字母顺序。默认情况下，列将按升序排序，而行将按索引顺序显示。但是，您可以覆盖这些默认设置。

使用 -A 或 --asc-rows，行将按升序显示；使用 -D 或 --desc-rows，它们将按降序显示。使用 -R 或 --desc-cols，列将按降序显示；使用 -I 或 --index-cols，它们将按显示的顺序显示。

分隔符

您还可以告诉 clipivot 使用除逗号之外的其他字符作为字段分隔符。默认情况下，clipivot 假设以 .tsv 或 .tab 扩展名结尾的文件是制表符分隔的，而其他文件则假定为逗号分隔。但是，这两者都可以被覆盖。您可以使用 -d 选项选择任何其他单字节 UTF-8 字符作为分隔符，或者您可以使用 -t 标志来选择将制表符作为文件分隔符。

注意：文件扩展名工具仅在 clipivot 直接读取文件时才有效。如果它是从标准输入接收制表符分隔的数据，则需要使用 -t 标志或 -d 选项。

标题

如果您没有标题行，您可以使用 --no-header 标志来让 clipivot 将第一行读取为记录，而不是标题行。

或者，如果您有标题行，但它不在文件的第一行，您可以使用 tail -n + 来让 clipivot 读取除了第 n 行之外的所有内容。例如，如果您的 CSV 文件 bad_csv.csv 的标题行在第五行，您可以输入

tail bad_csv.csv -n +5 | clipivot countunique -v 0

来计算您的 bad CSV 文件第一列中唯一值的数量。

空值

您可以让 clipivot 忽略空值。如果您使用 -e 标志，clipivot 将跳过与以下任何字符串（不区分大小写或空格）匹配的任何单元格

• "": 一个空字符串
• "na"
• "nan"
• "n/a"
• "none"
• "null"

正如这篇文章所巧妙解释的，这可能会过于激进，因此您应该确保这是解析您的数据的合理方法。特别是，我建议在您使用 -e 标志之前，检查一下哪些点被 clipivot 解释为 null。

这让我想到

错误处理

我已经尽力在 clipivot 中使错误处理清晰并富有帮助。

总的来说，您可能会看到四种错误。

• 第一种是简单的 IO 错误。它看起来像这样

No such file or directory (os error 2)

如果您看到这个错误，可能意味着在您尝试拼写文件名时出了错。

• 你可能看到的第二种错误是配置错误。配置错误可能以多种形式出现，每种错误都应该有一个详细的错误信息，提供给你特定的调试信息。一个例子如下

Could not properly configure the aggregator: Column selection must be between 0 <= selection < 42

如果你看到这个错误，有相当大的可能性是你忘记在 clipivot 中的字段是从 0 开始索引的。

• 你可能看到的第三种错误是 CSV 错误，这是由 clipivot 使用的 CSV 解析库引起的。这些错误看起来像这样

CSV error: record 1 (line: 2, byte: 597): found record with 4 fields, but the previous record has 1 fields

这些错误可能是因为 CSV 格式错误，或者是因为你忘记指定正确的分隔符（例如，在从标准输入管道输入 TSV 文件时忘记使用 -t 标志）。

• 最后，你可能会遇到一个看起来像这样的解析错误

Could not parse record `NA` with index 167: Failed to parse as numeric

这可能表明你的文件中存在一些空值或空值，或者它没有你期望的格式。

这也可能表明 clipivot 正在尝试以你未预期的格式解析你的数据（例如，它试图将一些字符串解析为日期，而你希望将其解析为数字）。

这些错误将提供你无法解析的记录的字符串值、记录的索引（第一个非标题记录的索引为 0）以及它试图解析数据的数据类型——所有这些都应该使调试更容易。

（顺便说一句，我建议将此实用程序与 xsv slice -i 配对，它会打印出 CSV 文件中给定行的一行。）

其他信息

• clipivot 在技术上允许你以字符串或文本的形式解析 min、max 和 minmax 函数。事实上，这是默认行为。这几乎完全是为了加快类似 YYYY-MM-DD 这样按字母顺序排序的日期格式的处理速度。
• 在存在多个真实模式的情况下，这里的模式算法简单地返回第一个达到最大出现次数的值（因此，如果你有一组值 "a, b, b, a"，它将返回 "b"，因为 "b" 的第二次出现比 "a" 的第二次出现要早）。
• 标准差返回 样本 标准差。

贡献者

排序的设计来自 这个问题。

我在这里使用的错误处理直接来自 这个关于 Rust 错误处理的优秀指南。我还使用了来自 xsv 和 Rust 中的 csv crate 的设计组件和代码片段。

许多其他指南在帮助我用 Rust 编写代码方面非常有用。我试图在源代码的行内注释和文档字符串中记录所有帮助我开发 clipivot 的指南和源代码。

其他 CSV 工具包也帮助我设计了此程序。这些工具包之间最直接的联系可能是我对解析空值采取的方法，这种方法直接受到 Python 中的 agate 库的启发，它是 csvkit 的核心。

我相信，现有的CSV工具包在设计这个项目中也启发了其他更微妙的方法。我主要使用的工具包是之前提到的 xsv 和出色的 csvtk。如果您想从命令行做更多关于CSV文件的事情，我强烈推荐这两个。

最后，我用来验证均值和标准差函数数值准确性的CSV文件（在 tests/test_numerical_accuracy.rs）来自国家计量院的标准统计参考数据集。

除了核心Rust代码外，我还使用了这个指南来上传发布资产和这个模板来处理Rust二进制文件的代码。

开发者指南

如果您想修改 clipivot，我建议您查看 开发者指南，其中提供了代码设计的概述和一些我想改进的建议。该指南旨在帮助没有任何编程经验的人、编写过代码但没有编写过Rust的人以及编写过Rust代码的人。所以，请绝对不要觉得自己没有资格改进这个项目。

联系我

如果您对 clipivot 有任何问题，或者在程序中发现了任何错误，或者您想为其做出贡献，请通过[email protected]给我发邮件，或者通过Twitter联系我。我是 @maxblee。如果您在您的任何项目中使用了 clipivot，我很想了解。