Comma separated values (CSV) files are a data storage staple in data science. Their simplicity and compatibility with various tools and programming languages make them an ideal format for data exchange. However, as datasets grow, merging multiple CSV files becomes a common task, whether for combining datasets from different sources, aggregating results from experiments, or appending new data over time.

CSV files are plain text files that contain data separated by commas, or even other delimiters including semicolons, tabs, or other characters. Each line corresponds to a data record, while each record consists of fields separated by a specified delimiter. The first line often contains headers that describe the columns of data.

Bash, a powerful command-line shell, offers several straightforward methods for merging CSV files effectively. This article will guide you through the process of merging CSV files using Bash, focusing on practical commands and techniques suitable for both beginners and practitioners in data science. Specifically, we will look at cat, awk, and csvkit, as each of these tools has unique capabilities that can assist in different merging scenarios.

Merging with the cat Command

The cat command is one of the simplest ways to combine files in Bash. Short for “concatenate”, the basic usage of cat is as follows:

cat file1.csv file2.csv > merged.csv

This command concatenates file1.csv and file2.csv, saving the output to merged.csv. However, if the CSV files contain headers, this method will repeat the headers in the merged file.

To merge CSV files while excluding duplicate headers, you can use the following command:

(head -n 1 file1.csv && tail -n +2 -q file1.csv file2.csv) > merged.csv

This command uses head to extract the header from the first file and tail to skip the header in subsequent files.

Merging with the awk Command

awk is a powerful text processing tool that allows for more complex merging and data manipulation. Here is a simple example of merging two CSV files using awk:

awk 'NR==1 { print; next } FNR > 1' file1.csv file2.csv > merged.csv

This command prints the header from the first file and appends the content of both files, skipping the headers in the second file.

If you want to merge files while ensuring that duplicate rows are removed, you can use:

awk '!seen[$0]++' file1.csv file2.csv > merged.csv

This command uses an associative array to track and skip duplicate rows.

Advanced CSV Merging with csvkit

csvkit is a suite of command-line tools specifically designed for working with CSV files. You can install csvkit using pip:

pip install csvkit

Note that the package is written in Python, but we will be using its command line functionalities.

To merge CSV files with csvkit, use the csvstack command:

csvstack file1.csv file2.csv > merged.csv

This command automatically handles headers and combines the files, with no further arguments or considerations needed from the user.

You can find much more about the vast functionalities of csvkit here.

Wrapping Up

In this article, we covered several methods for merging CSV files using Bash. We started with the basic cat command, advanced to awk for more complex scenarios, and explored csvkit for specialized CSV handling.

Happy merging!