csvtk - a cross-platform, efficient and practical CSV/TSV toolkit

• Documentation: http://bioinf.shenwei.me/csvtk ( Usage, Tutorial and FAQs). 中文介绍
• Source code: https://github.com/shenwei356/csvtk
• Latest version:

Introduction

Like the FASTA/Q formats in bioinformatics, CSV/TSV formats are basic and ubiquitous file formats in both bioinformatics and data science.

People usually use spreadsheet software like MS Excel to process tabular data. However, this involves clicking and typing, which is not automated and is time-consuming to repeat, especially when you want to apply similar operations to different datasets or for different purposes.

You can also accomplish some CSV/TSV manipulations using shell commands, but more code is needed to handle the header line. Shell commands do not support selecting columns by name either.

csvtk is convenient for rapid data investigation and also easy to integrate into analysis pipelines. It can save you the time spent writing Python/R scripts.

Table of Contents

• Introduction
• Table of Contents
• Features
• Subcommands
• Installation
  • Method 1: Download binaries (latest stable/dev version)
  • Method 2: Install via Pixi
  • Method 3: Install via conda (latest stable version)
  • Method 4: Install via Homebrew
  • Method 5: For Go developers (latest stable/dev version)
  • Method 6: For ArchLinux AUR users (may not be the latest)
• Command-line completion
• Compared to csvkit
• Examples
• Acknowledgements
• Contact
• License
• Starchart

Features

• Cross-platform (Linux/Windows/Mac OS X/OpenBSD/FreeBSD)
• Lightweight and ready to use, no dependencies, no compilation, no configuration
• Fast, multiple CPUs supported (some commands)
• Practical functions provided by N subcommands
• Supports STDIN and gzipped input/output files for easy use in pipelines
• Seamless support for xz (.xz), zstd (.zst), Bzip2 (.bz2), LZ4 (.lz4) formats
• Most of the subcommands support unselecting fields and fuzzy fields, e.g. -f "-id,-name" for all fields except "id" and "name", -F -f "a.*" for all fields with prefix "a.".
• Supports some common plots (see usage)
• Seamless support for data with a metadata line (e.g., sep=,) declaring the separator used by MS Excel

Subcommands

59 subcommands in total.

Information

• headers: prints headers
• dim: dimensions of a CSV file
• nrow: print the number of records
• ncol: print the number of columns
• summary: summary statistics of selected numeric or text fields (grouped by group fields)
• watch: online monitoring and histogram of a selected field
• corr: calculate Pearson correlation between numeric columns

Format conversion

• pretty: converts CSV to a readable aligned table
• csv2tab: converts CSV to tabular format
• tab2csv: converts tabular format to CSV
• space2tab: converts space delimited format to TSV
• csv2html: converts CSV/TSV to a standalone responsive HTML table
• csv2md: converts CSV to Markdown format
• csv2rst: converts CSV to reStructuredText format
• csv2json: converts CSV to JSON format
• csv2xlsx: converts CSV/TSV files to an XLSX file
• xlsx2csv: converts XLSX to CSV format

Set operations

• head: prints the first N records
• concat: concatenates CSV/TSV files by rows
• paste: pastes CSV/TSV files by columns
• sample: sampling by proportion
• cut: select and arrange fields
• grep: greps data by selected fields with patterns/regular expressions
• uniq: deduplicate records by selected fields without sorting
• freq: frequencies of selected fields
• inter: intersection of multiple files
• filter: filters rows by values of selected fields with an arithmetic expression
• filter2: filters rows by awk-like arithmetic/string expressions
• join: join files by selected fields (inner, left and outer join)
• split: splits CSV/TSV by column values, rows per chunk, or number of chunks
• splitxlsx: splits an XLSX sheet into multiple sheets according to column values
• comb: compute combinations of items in each row

Edit

• fix: fix CSV/TSV with different numbers of columns in rows
• fix-quotes: fix malformed CSV/TSV caused by double-quotes
• del-quotes: remove extra double-quotes added by fix-quotes
• add-header: add column names
• del-header: delete column names
• rename: renames column names with new names
• rename2: renames column names using regular expressions
• replace: replaces data of selected fields using regular expressions
• round: round floats to n decimal places
• comma: make numbers more readable by adding commas
• mutate: creates new columns from selected fields using regular expressions
• mutate2: creates a new column from selected fields by awk-like arithmetic/string expressions
• mutate3: create a new column from selected fields with Go-like expressions
• fmtdate: format dates in selected fields

Transform

• transpose: transposes CSV data
• sep: separate a column into multiple columns
• gather: gather columns into key-value pairs, like tidyr::gather/pivot_longer
• spread: spread a key-value pair across multiple columns, like tidyr::spread/pivot_wider
• unfold: unfold multiple values in cells of one or more fields
• fold: fold multiple values of one or more fields into cells of groups
• long2matrix: convert the long format to a matrix
• matrix2long: convert a matrix to the long format

Ordering

• sort: sorts by selected fields
• shuf: shuffle rows

Plotting

• plot see usage
  • plot hist histogram
  • plot box boxplot
  • plot line line plot and scatter plot
  • plot bar plot bar chart

Misc

• cat stream a file and report progress
• version print version information and check for updates
• genautocomplete generate a shell autocompletion script (bash|zsh|fish|powershell)

Installation

Download Page

csvtk is implemented in the Go programming language. Executable binary files for most popular operating systems are freely available on the release page.

Method 1: Download binaries (latest stable/dev version)

Download the compressed executable file for your operating system, and decompress it with tar -zxvf *.tar.gz or another tool. Then:

1. For Linux-like systems

   1. If you have root privileges, simply copy it to /usr/local/bin:

      sudo cp csvtk /usr/local/bin/
      

   2. Or copy it to a directory listed in the environment variable PATH:

      mkdir -p $HOME/bin/; cp csvtk $HOME/bin/
      

2. For Windows, just copy csvtk.exe to C:\WINDOWS\system32.

Method 2: Install via Pixi

pixi global install csvtk

Method 3: Install via conda (latest stable version)

# >= v0.31.0
conda install -c conda-forge csvtk

# <= v0.31.0
conda install -c bioconda csvtk

Method 4: Install via Homebrew

brew install csvtk

Method 5: For Go developers (latest stable/dev version)

go install github.com/shenwei356/csvtk/csvtk@latest

Method 6: For ArchLinux AUR users (may not be the latest)

yaourt -S csvtk

Command-line completion

Bash:

# generate completion shell
csvtk genautocomplete --shell bash

# configure if never did.
# install bash-completion if the "complete" command is not found.
echo "for bcfile in ~/.bash_completion.d/* ; do source \$bcfile; done" >> ~/.bash_completion
echo "source ~/.bash_completion" >> ~/.bashrc

Zsh:

# generate completion shell
csvtk genautocomplete --shell zsh --file ~/.zfunc/_csvtk

# configure if never did
echo 'fpath=( ~/.zfunc "${fpath[@]}" )' >> ~/.zshrc
echo "autoload -U compinit; compinit" >> ~/.zshrc

fish:

csvtk genautocomplete --shell fish --file ~/.config/fish/completions/csvtk.fish

Compared to csvkit

Comparison with csvkit: this table has not been updated for many years.

Features	csvtk	csvkit	Note
Read Gzip	Yes	Yes	read gzip files
Field ranges	Yes	Yes	e.g. -f 1-4,6
Unselect fields	Yes	--	e.g. -1 for excluding the first column
Fuzzy fields	Yes	--	e.g. ab* for columns with name prefix "ab"
Reorder fields	Yes	Yes	it means -f 1,2 is different from -f 2,1
Rename columns	Yes	--	rename with new name(s) or from existing names
Sort by multiple keys	Yes	Yes	operations like Bash sort
Sort by number	Yes	--	e.g. -k 1:n
Multiple sort	Yes	--	e.g. -k 2:r -k 1:nr
Pretty output	Yes	Yes	convert CSV to a readable aligned table
Unique data	Yes	--	unique data of selected fields
frequency	Yes	--	frequencies of selected fields
Sampling	Yes	--	sampling by proportion
Mutate fields	Yes	--	create new columns from selected fields
Replace	Yes	--	replace data of selected fields

Similar tools:

• csvkit - A suite of utilities for converting to and working with CSV, the king of tabular file formats. http://csvkit.rtfd.org/
• xsv - A fast CSV toolkit written in Rust.
• miller - Miller is like sed, awk, cut, join, and sort for name-indexed data such as CSV and tabular JSON http://johnkerl.org/miller
• tsv-utils - Command line utilities for tab-separated value files written in the D programming language.

Examples

More examples and tutorial.

Attention

1. By default, csvtk assumes input files have a header row. If not, enable flag -H.
2. By default, csvtk handles CSV files. Use flag -t for tab-delimited files.
3. Column names should be unique.
4. By default, lines starting with # are ignored. If the header row starts with #, set flag -C to another uncommon symbol, e.g. $.
5. Do not mix field (column) numbers and names when specifying columns to operate on.
6. The CSV parser requires all lines to have the same number of fields/columns. Even lines containing only spaces will cause an error. Use -I/--ignore-illegal-row to skip these lines if necessary. You can also use "csvtk fix" to fix files with different numbers of columns in rows.

7. If double-quotes exist in fields not enclosed with double-quotes, e.g.,

   x,a "b" c,1
   

   It would report an error:

   bare `"` in non-quoted-field.
   

   Please switch on the flag -l or use csvtk fix-quotes to fix it.

8. If some fields have a double-quote only at the beginning or end, e.g.,

   x,d "e","a" b c,1
   

   It would report an error:

   extraneous or missing " in quoted-field
   

   Please use csvtk fix-quotes to fix it, and use csvtk del-quotes to reset to the original format as needed.

9. Output paths must differ from input files, including symbolic and hard links.

10. Read and decompression errors in key-value, pattern, and custom sort-level files cause a nonzero exit status; partial contents are not used.
11. With -E/--ignore-empty-row or -I/--ignore-illegal-row, --quiet also avoids retaining skipped-row line numbers, reducing memory use on large inputs.

Examples

1. Pretty result

   $ csvtk pretty names.csv
   id   first_name   last_name   username
   --   ----------   ---------   --------
   11   Rob          Pike        rob
   2    Ken          Thompson    ken
   4    Robert       Griesemer   gri
   1    Robert       Thompson    abc
   NA   Robert       Abel        123
   
   $ csvtk pretty names.csv -S 3line
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
    id   first_name   last_name   username 
   ────────────────────────────────────────
    11   Rob          Pike        rob      
    2    Ken          Thompson    ken      
    4    Robert       Griesemer   gri      
    1    Robert       Thompson    abc      
    NA   Robert       Abel        123      
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
   
   $ csvtk pretty names.csv -S round -w 5 -m 1-
   ╭───────┬────────────┬───────────┬──────────╮
   │  id   │ first_name │ last_name │ username │
   ├───────┼────────────┼───────────┼──────────┤
   │  11   │    Rob     │   Pike    │   rob    │
   ├───────┼────────────┼───────────┼──────────┤
   │   2   │    Ken     │ Thompson  │   ken    │
   ├───────┼────────────┼───────────┼──────────┤
   │   4   │   Robert   │ Griesemer │   gri    │
   ├───────┼────────────┼───────────┼──────────┤
   │   1   │   Robert   │ Thompson  │   abc    │
   ├───────┼────────────┼───────────┼──────────┤
   │  NA   │   Robert   │   Abel    │   123    │
   ╰───────┴────────────┴───────────┴──────────╯
   

2. Summary of selected numeric fields, supporting "group-by"

   $ cat testdata/digitals2.csv \
       | csvtk summary -i -f f4:sum,f5:sum -g f1,f2 \
       | csvtk pretty
   f1    f2     f4:sum   f5:sum
   bar   xyz    7.00     106.00
   bar   xyz2   4.00     4.00
   foo   bar    6.00     3.00
   foo   bar2   4.50     5.00
   

3. Select fields/columns (cut)

   • By index: csvtk cut -f 1,2
   • By names: csvtk cut -f first_name,username
   • Unselect: csvtk cut -f -1,-2 or csvtk cut -f -first_name
   • Fuzzy fields: csvtk cut -F -f "*_name,username"
   • Field ranges: csvtk cut -f 2-4 for columns 2, 3, and 4 or csvtk cut -f -3--1 for discarding columns 1, 2, and 3
   • All fields: csvtk cut -f 1- or csvtk cut -F -f "*"

4. Search by selected fields (grep) (matched parts will be highlighted in red)

   • By exact matching: csvtk grep -f first_name -p Robert -p Rob
   • By regular expression: csvtk grep -f first_name -r -p Rob
   • By pattern list: csvtk grep -f first_name -P name_list.txt
   • Remove rows containing missing data (NA): csvtk grep -F -f "*" -r -p "^$" -v

5. Rename column names (rename and rename2)

   • Setting new names: csvtk rename -f A,B -n a,b or csvtk rename -f 1-3 -n a,b,c
   • Replacing original names using regular expressions: csvtk rename2 -f 1- -p "(.*)" -r 'prefix_$1' for adding a prefix to all column names.

6. Edit data with regular expressions (replace)

   • Remove Chinese characters: csvtk replace -F -f "*_name" -p "\p{Han}+" -r ""

7. Create a new column from selected fields using regular expressions (mutate)

   • By default, copy a column: csvtk mutate -f id
   • Extract a prefix from data as a group name (get "A" from "A.1" as a group name): csvtk mutate -f sample -n group -p "^(.+?)\." --after sample

8. Sort by multiple keys (sort)

   • By a single column: csvtk sort -k 1 or csvtk sort -k last_name
   • By multiple columns: csvtk sort -k 1,2 or csvtk sort -k 1 -k 2 or csvtk sort -k last_name,age
   • Sort by number: csvtk sort -k 1:n or csvtk sort -k 1:nr for reverse numeric order
   • Complex sort: csvtk sort -k region -k age:n -k id:nr
   • In natural order: csvtk sort -k chr:N

9. Join multiple files by keys (join)

   • All files have the same key column: csvtk join -f id file1.csv file2.csv
   • Files have different key columns: csvtk join -f "username;username;name" names.csv phone.csv adress.csv -k

10. Filter by numbers (filter)

    • Single field: csvtk filter -f "id>0"
    • Multiple fields: csvtk filter -f "1-3>0"
    • Using --any to print a record if any of the fields satisfy the condition: csvtk filter -f "1-3>0" --any
    • fuzzy fields: csvtk filter -F -f "A*!=0"

11. Filter rows by awk-like arithmetic/string expressions (filter2)

    • Using field index: csvtk filter2 -f '$3>0'
    • Using column names: csvtk filter2 -f '$id > 0'
    • Both arithmetic and string expressions: csvtk filter2 -f '$id > 3 || $username=="ken"'
    • More complicated: csvtk filter2 -H -t -f '$1 > 2 && $2 % 2 == 0'

12. Plotting

    • plot a histogram using data from the second column:

      csvtk -t plot hist testdata/grouped_data.tsv.gz -f 2 | display
      

    

    • plot a boxplot using data from the "GC Content" (third) column; group information is in the "Group" column.

      csvtk -t plot box testdata/grouped_data.tsv.gz -g "Group" \
          -f "GC Content" --width 3 --title "Box plot" | display
      

    

    • plot a horizontal boxplot using data from the "Length" (second) column; group information is in the "Group" column.

      csvtk -t plot box testdata/grouped_data.tsv.gz -g "Group" -f "Length"  \
          --height 3 --width 5 --horiz --title "Horiz box plot" | display
      

    

    • plot a line graph with X-Y data

      csvtk -t plot line testdata/xy.tsv -x X -y Y -g Group | display
      

    

    • plot a line graph with X-Y data (where X values are dates or other sortable values)

      cat testdata/date2value.csv \
          | csvtk gather -f 2- -k type -v count \
          | csvtk plot line --group-field type -x date -y count --data-field-x-nominal \
              -o testdata/figures/line_plot_date.png
      

    

    • plot a scatter plot with X-Y data

      csvtk -t plot line testdata/xy.tsv -x X -y Y -g Group --scatter | display
      

    

Acknowledgements

We are grateful to Zhiluo Deng and Li Peng for suggesting features and reporting bugs.

Thanks to Albert Vilella for feature suggestions, which have made csvtk feature-rich.

Contact

Create an issue to report bugs, propose new functions or ask for help.

Or leave a comment.

License

MIT License

Starchart