Csvkit

How to use csvkit

Overview

Questions

  • How to open data with csvkit?

  • How to select certain rows and columns of the data? How to append them after filtering?

  • How to sort and describe basic characteristics of the data?

Objectives

  • Learn how to install csvkit and how to use csvlook

  • Learn csvgrep, csvcut and csvstack commands

  • Learn csvsort and csvstat commands

The use of csvkit

csvkit is a command-line tool written in Python to be used for simple data wrangling and analysis tasks. This tutorial presents the most important commands implemented in it. The following sections rely heavily on the official csvkit tutorial.

Installing csvkit

The csvkit tool can be installed with the following command (if you use Python 2.7 you might type sudo pip install csvkit instead).

$ sudo pip3 install csvkit

For illustration purposes an example dataset is also used in this tutorial. The data contain information on cars and their characteristics. To get the data you should type the following command. The dataset has a second row with information on data type that is removed for later analysis purposes with the head and tail commands - an alternative way to do this is by using sed 2,2d cars.csv > cars-tutorial.csv.

$ wget https://perso.telecom-paristech.fr/eagan/class/igr204/data/cars.csv
$ head -1 cars.csv > cars-tutorial.csv
$ tail -n+3 cars.csv >> cars-tutorial.csv

The most important csvkit commands

The example dataset is semi-colon and not comma separated. For all the commands presented below the input delimiter can be set with the -d argument: in this case as -d ";". Setting the input delimiter with -d changes the decimal separator in the ouput as well. To change it back to dot from comma, csvformat -D "." should be used after any command where it is relevant.

  • csvlook shows the data in a Markdown-compatible format. cat may also be used instead of csvlook to open a csv file, but the latter is more readable. This command can be combined with head in order to have a look at the first few lines of the data. As seen here and in later examples, csvkit commands can be piped together and with other commands. For the latter command the following output can be seen.

$ csvlook -d ";" cars-tutorial.csv | csvformat -D "."
$ head -5 cars-tutorial.csv | csvlook -d ";" | csvformat -D "."

  • csvcut shows the column names in the data if the -n argument is specified. It can also help to select certain columns of the data with the -c argument and the corresponding column numbers (or names). The code and output is the following:

$ csvcut -n -d ";" cars-tutorial.csv

The following two commands have identical outputs: car, miles per gallon consumption and origin columns, as shown below. For space constraints only the first few rows are printed out.

$ csvcut -c 1,2,9 -d ";" cars-tutorial.csv | head -5 | csvlook
$ csvcut -c Car,MPG,Origin -d ";" cars-tutorial.csv | head -5 | csvlook

  • csvstat calculates summary statistics for all columns. It recognizes the data type of the column and prints out descriptive information accordingly. csvstat may be piped together with csvcut to calculate descriptive statistics only for certain columns.

    The following command shows the summary statistics for the car, miles per gallon consumption and origin columns.

$ csvcut -c 1,2,9 -d ";" cars-tutorial.csv | csvstat | csvformat -D "."

  • csvsort sorts the rows for the column specified (either with a number or a name) after the argument -c. Reversed order can be set by using -r.

    Based on the previous example, the highest value in miles per gallon is 46.6. If you want to search for this very fuel efficient car, one way is to sort the data in a reversed order.

$ csvcut -c 1,2 -d ";" cars-tutorial.csv | csvsort -c 2 -r | head -5 | csvlook | csvformat -D "."

The output shows the four most fuel efficient cars. Mazda GLC, the most fuel efficient one has indeed a 46.6 miles per gallon consumption.

  • csvgrep selects rows that match specific patterns, so in other words it can be used for filtering. The pattern may either be a string or an integer. The -c argument specifies the column in which the pattern is searched for (either the column number or name can be used), while -m defines the pattern.

    Following the previous examples, the car with the highest miles per gallon consumption (which is 46.6) is searched for.

$ csvgrep -c MPG -m 46.6 -d ";" cars-tutorial.csv | csvlook | csvformat -D "."

The command yields an output showing only cars with a 46.6 miles per gallon consumption. There is only one such car: Mazda GLC.

It is also possible to filter and separate the file based on a string variable. In the following example three different csv files are created based on the origin variable. We know from the csvstat command that there are three possible categories for origin: US, Japan and Europe.

$ csvgrep -c Origin -m US -d ";" cars-tutorial.csv > cars-tutorial-us.csv
$ csvgrep -c Origin -m Japan -d ";" cars-tutorial.csv > cars-tutorial-japan.csv
$ csvgrep -c Origin -m Europe -d ";" cars-tutorial.csv > cars-tutorial-europe.csv

  • csvstack appends datasets with identical column names. There might be cases where it makes sense to specify the -g argument which adds a column identifying the source csv. In the following example it is not needed.

    The three csv files created in the previous example can be stacked. Since there were three countries of origin, this command should have the same length as the original data.

    $ csvstack cars-tutorial-us.csv cars-tutorial-europe.csv cars-tutorial-japan.csv
$ csvstack cars-tutorial-us.csv cars-tutorial-europe.csv cars-tutorial-japan.csv | wc -l
$ wc cars-tutorial.csv -l

    Both files have 407 rows as expected (406 plus the header).

Useful resources for learning csvkit:

PreviousJuliaNextRelease

Last updated