How to Clean Data Using Bash Commands

Bash is a powerful command-line interface available on Unix/Linux systems that allows users to execute commands and scripts. Data cleaning is a crucial step in data science to ensure data quality and reliability. Let’s take a look at how to clean data using Bash.

Today we will be using the following Bash tools and commands:

cat: Concatenates and displays the content of files
head: Outputs the first part of files
tail: Outputs the last part of files
wc: Prints newline, word, and byte counts for each file
sort: Sorts lines of text files
uniq: Filters out repeated lines in a file
sed: Stream editor for filtering and transforming text
awk: Pattern scanning and processing language
grep: Searches for patterns within files
split: Splits a file into pieces
diff: Compares files line by line
mv: Moves or renames files

Setting Up the Environment

Before starting, set up your workspace. Beyond having access to a Bash sheel (or zsh on a macOS system), create directories and navigate the filesystem using the following commands:

mkdir data_cleaning
cd data_cleaning

Now we can isolate the exercises within this tutorial.

Inspecting Data

Use cat to display the contents of a file:

cat data.csv

To view the file structure, use head and tail:

head -n 10 data.csv
tail -n 10 data.csv

Get basic file statistics with wc:

wc -l data.csv

Cleaning Data

Remove duplicate lines using sort and uniq:

sort data.csv | uniq > cleaned_data.csv

Handle missing values with sed (note that the pattern ‘,,’ denotes an empty column in a CSV file):

sed '/,,/d' data.csv > cleaned_data.csv

Trim whitespace using awk:

awk '{$1=$1;print}' data.csv > cleaned_data.csv

Filtering Data

Filter rows based on a condition with grep:

grep "pattern" data.csv > filtered_data.csv

For advanced filtering, use awk:

awk -F, '$3 > 1000' data.csv > filtered_data.csv

Transforming Data

Replace text with sed:

sed 's/oldtext/newtext/g' data.csv > transformed_data.csv

Rearrange columns using awk:

awk -F, '{print $2, $1, $3}' data.csv > transformed_data.csv

Combining and Splitting Data

Concatenate files with cat:

cat file1.csv file2.csv > combined.csv

Split files into smaller parts with split:

split -l 1000 data.csv part_

Validating and Saving Cleaned Data

Check data consistency with diff:

diff original_data.csv cleaned_data.csv

Save the final cleaned data:

mv cleaned_data.csv final_data.csv

Automating the Process

Create a Bash script for repetitive tasks:

#!/bin/bash
sort data.csv | uniq | sed '/,,/d' | awk '{$1=$1;print}' > cleaned_data.csv

Set up a cron job for regular data cleaning tasks:

crontab -e

Add the following line to schedule the script to run daily at midnight:

0 0 * * * /path/to/your/script.sh

Final Thoughts

In this tutorial, we’ve covered how to use Bash commands to inspect, clean, filter, transform, combine, split, and validate data. Keep in mind that using Bash scripting to accomplish tasks naturally lends itself to automating these same tasks when appropriate. Always keep your eye open for opportunities to do just that.

Check the following resources for additional information on writing Bash scripts:

Happy bashing!