5.3 Working with CSV

5.3.1 Bodies and Headers and Columns, Oh My!

The command-line tools that we’ve used to scrub plain text, such as tr and grep, cannot always be applied to CSV. The reason is that these command-line tools have no notion of headers, bodies, and columns. What if we wanted to filter lines using grep but always include the header in the output? Or what if we only wanted to uppercase the values of a specific column using tr and leave the other columns untouched? There are multi-step workarounds for this, but they are very cumbersome. We have something better. In order to leverage ordinary command-line tools for CSV, we’d like to introduce you to three command-line tools, aptly named: body (Janssens 2014a), header (Janssens 2014c), and cols (Janssens 2014b).

Let’s start with the first command-line tool, body. With body you can apply any command-line tool to the body of a CSV file, that is, everything excluding the header. For example:

$ echo -e "value\n7\n2\n5\n3" | body sort -n
value
2
3
5
7

It assumes that the header of the CSV file only spans one row. Here’s the source code for completeness:

#!/usr/bin/env bash
IFS= read -r header        
printf '%s\n' "$header"    
$@                         

It works like this:

• Take one line from standard in and store it as a variable named $header.
• Print out the header.
• Execute all the command-line arguments passed to body on the remaining data in standard in.Here’s another example. Imagine that we count the lines of the following CSV file:

$ seq 5 | header -a count
count
1
2
3
4
5

With wc -l, we can count the number of all lines:

$ seq 5 | header -a count | wc -l
6

If we only want to consider the lines in the body (so everything except the header), we simply add body:

$ seq 5 | header -a count | body wc -l
count
5

Note that the header is not used and is also printed again in the output.

The second command-line tool, header allows us, as the name implies, to manipulate the header of a CSV file. The complete source code is as follows:

#!/usr/bin/env bash
get_header () {
        for i in $(seq $NUMROWS); do
                IFS= read -r LINE
                OLDHEADER="${OLDHEADER}${LINE}\n"
        done
}
print_header () {
        echo -ne "$1"
}
print_body () {
        cat
}
OLDHEADER=
NUMROWS=1
while getopts "dn:ha:r:e:" OPTION
do
        case $OPTION in
                n)
                        NUMROWS=$OPTARG
                        ;;
                a)
                        print_header "$OPTARG\n"
                        print_body
                        exit 1
                        ;;
                d)
                        get_header
                        print_body
                        exit 1
                        ;;
                r)
                        get_header
                        print_header "$OPTARG\n"
                        print_body
                        exit 1
                        ;;
                e)
                        get_header
                        print_header "$(echo -ne $OLDHEADER | eval $OPTARG)\n"
                        print_body
                        exit 1
                        ;;
                h)
                        usage
                        exit 1
                        ;;
        esac
done
get_header
print_header $OLDHEADER

If no argument are provided, the header of the CSV file is printed:

$ < tips.csv | header
bill,tip,sex,smoker,day,time,size

This is the same as head -n 1. If the header spans more than one row, which is not recommended, you can specify -n 2. We can also add a header to a CSV file:

$ seq 5 | header -a count
count
1
2
3
4
5

This is equivalent to echo "count" | cat - <(seq 5). Deleting a header is done with the -d command-line argument:

$ < iris.csv | header -d | head
5.1,3.5,1.4,0.2,Iris-setosa
4.9,3.0,1.4,0.2,Iris-setosa
4.7,3.2,1.3,0.2,Iris-setosa
4.6,3.1,1.5,0.2,Iris-setosa
5.0,3.6,1.4,0.2,Iris-setosa
5.4,3.9,1.7,0.4,Iris-setosa
4.6,3.4,1.4,0.3,Iris-setosa
5.0,3.4,1.5,0.2,Iris-setosa
4.4,2.9,1.4,0.2,Iris-setosa
4.9,3.1,1.5,0.1,Iris-setosa

This is similar to tail -n +2, but it’s a bit easier to remember. Replacing a header, which is basically first deleting a header and then adding one if you look at the above source code, is accomplished with specifying -r. Here, we combine it with body:

$ seq 5 | header -a line | body wc -l | header -r count
count
5

And last but not least, we can apply a command to just the header, similar to what the body command-line tool does to the body:

$ seq 5 | header -a line | header -e "tr '[a-z]' '[A-Z]'"
LINE
1
2
3
4
5

The third command-line tool is called cols, which is similar to header and body in that it allows you to apply a certain command to only a subset of the columns. The code is as follows:

#!/usr/bin/env bash
ARG="$1"
shift
COLUMNS="$1"
shift
EXPR="$@"
DIRTMP=$(mktemp -d)
mkfifo $DIRTMP/other_columns
tee $DIRTMP/other_columns | csvcut $ARG $COLUMNS | ${EXPR} |
paste -d, - <(csvcut ${ARG~~} $COLUMNS $DIRTMP/other_columns)
rm -rf $DIRTMP

For example, if we wanted to uppercase the values in the day column in the tips data set (without affecting the other columns and the header), we would use cols in combination with body, as follows:

$ < tips.csv cols -c day body "tr '[a-z]' '[A-Z]'" | head -n 5 | csvlook -I
|------+-------+------+--------+--------+--------+-------|
|  day | bill  | tip  | sex    | smoker | time   | size  |
|------+-------+------+--------+--------+--------+-------|
|  SUN | 16.99 | 1.01 | Female | No     | Dinner | 2     |
|  SUN | 10.34 | 1.66 | Male   | No     | Dinner | 3     |
|  SUN | 21.01 | 3.5  | Male   | No     | Dinner | 3     |
|  SUN | 23.68 | 3.31 | Male   | No     | Dinner | 2     |
|------+-------+------+--------+--------+--------+-------|

Please note that passing multiple command-line tools and arguments as command to header -e, body, and cols can lead to tricky quoting citations. If you ever run in such problems, it is best to create a separate command-line tool for this and pass that as command.

In conclusion, while it is generally preferable to use command-line tools which are specifically made for CSV data, body, header, and cols also allow you to apply the classic command-line tools to CSV files if needed.

5.3.2 Performing SQL Queries on CSV

In case the command-line tools mentioned in this chapter do not provide enough flexibility, then there is another approach to scrub your data from the command line. The command-line tool csvsql (Groskopf 2014f) allows you to execute SQL queries directly on CSV files. As you may know, SQL is a very powerful language to define operations for scrubbing data; it is a very different way than using individual command-line tools.

If your data originally comes from a relational database, then, if possible, try to execute SQL queries on that database and subsequently extract the data as CSV. As discussed in Chapter 3, you can use the command-line tool sql2csv for this. When you first export data from the database to a CSV file, and then apply SQL, it is not only slower, but there is also a possibility that the column types are not correctly inferred from the CSV data.

In the scrubbing tasks below, we’ll include several solutions that involve csvsql. The basic command is this:

$ seq 5 | header -a value | csvsql --query "SELECT SUM(value) AS sum FROM stdin"
sum
15

If you pass standard input to csvsql, then the table is named stdin. The types of the column are automatically inferred from the data. As you will see later, in the combining CSV files section, you can also specify multiple CSV files. Please keep in mind that csvsql employs SQLite dialect. While SQL is generally more verbose than the other solutions, it is also much more flexible. If you already know how to tackle a scrubbing problem with SQL, then there’s no shame in using it from the command line!