Knowledge Cleansing on the Command Line for Newbie Knowledge Scientists

Picture by Writer

# Introduction

 
When you concentrate on knowledge cleansing, you most likely consider spinning up a Jupyter Pocket book. However this is one thing which may shock you: among the strongest knowledge cleansing can occur proper in your terminal, utilizing instruments which are already put in in your system.

On this article, you’ll discover ways to use fundamental command-line utilities to wash, rework, and discover knowledge recordsdata. No installations are required; simply your terminal and a few CSV recordsdata.

Earlier than we get began cleansing knowledge on the command line, let’s discuss why this issues:

• Command-line instruments are straightforward to make use of, quick, and environment friendly, particularly for giant recordsdata.
• These instruments are constructed into Linux/macOS and can be found on Home windows.
• They’re nice for getting a primary have a look at knowledge earlier than loading and analyzing it with Python.
• It’s straightforward to chain instructions collectively in scripts and use these scripts for automation.

Now, let’s begin coding!

Observe: You could find all of the instructions on this Bash script on GitHub. To get essentially the most out of this text, I encourage you to open your terminal and code alongside.

# Setting Up Pattern Knowledge

 
Let’s create a messy CSV file to work with. This simulates real-world knowledge points you’ll seemingly run into.

cat > messy_data.csv << 'EOF'
identify,age,wage,division,e-mail
John Lee,32,50000,Engineering,[email protected]
Jane Smith,28,55000,Advertising,[email protected]
   Bob Davis    ,35,60000,Engineering,[email protected]
Alice Williams,29,,Advertising,[email protected]
Charlie Brown,45,70000,Gross sales,[email protected]
Dave Wilson,31,52000,Engineering,
Emma Davis,,58000,Advertising,[email protected]
Frank Miller,38,65000,Gross sales,[email protected]
John Lee,32,50000,Engineering,[email protected]
Grace Lee,27,51000,Engineering,[email protected]
EOF

This dataset has a number of frequent points: main and trailing whitespace, lacking values, and duplicate rows. It’s nice for studying!

# 1. Exploring Your Knowledge with head, tail, and wc

 
Earlier than cleansing your knowledge, you want to perceive what you’re working with. Let’s begin with the fundamentals.

# See the primary 5 rows (together with header)
head -n 5 messy_data.csv

# See the final 3 rows
tail -n 3 messy_data.csv

# Depend complete rows (together with header)
wc -l messy_data.csv

Here’s what is occurring:

• head -n 5 exhibits the primary 5 traces, providing you with a fast preview.
• tail -n 3 exhibits the final 3 traces (helpful for checking if knowledge is full).
• wc -l counts traces — subtract 1 for the header to get your report rely.

Output:

identify,age,wage,division,e-mail
John Lee,32,50000,Engineering,[email protected]
Jane Smith,28,55000,Advertising,[email protected]
   Bob Davis    ,35,60000,Engineering,[email protected]
Alice Williams,29,,Advertising,[email protected]
Frank Miller,38,65000,Gross sales,[email protected]
John Lee,32,50000,Engineering,[email protected]
Grace Lee,27,51000,Engineering,[email protected]
11 messy_data.csv

# 2. Viewing Particular Columns with minimize

 
You don’t at all times must see all of the columns. Let’s extract solely the names and departments.

minimize -d',' -f1,4 messy_data.csv

Breaking it down:

• minimize is a instrument for extracting sections from every line.
• -d',' units the delimiter to a comma (for CSV recordsdata).
• -f1,4 selects fields (columns) 1 and 4.
• It’s also possible to use ranges: -f1-3 for columns 1 by 3.

Right here is the output:

identify,division
John Lee,Engineering
Jane Smith,Advertising
   Bob Davis    ,Engineering
Alice Williams,Advertising
Charlie Brown,Gross sales
Dave Wilson,Engineering
Emma Davis,Advertising
Frank Miller,Gross sales
John Lee,Engineering
Grace Lee,Engineering

# 3. Eradicating Duplicate Rows with kind and uniq

 
Discover that “John Lee” seems twice in our dataset. Let’s repair that.

# Save the header first
head -n 1 messy_data.csv > cleaned_data.csv

# Take away duplicates from the information (excluding header)
tail -n +2 messy_data.csv | kind | uniq >> cleaned_data.csv

Here’s what every command does: head -n 1 grabs simply the header row. tail -n +2 will get the whole lot ranging from line 2 (skipping the header). Then, kind kinds the traces. Please be aware that uniq solely works on sorted knowledge, and uniq removes adjoining duplicate traces. Lastly, >> appends to the file (versus > which overwrites).

# 4. Looking and Filtering with grep

 
Let’s now do some looking and filtering operations. Need to discover all engineers or filter out rows with lacking knowledge? grep is useful for all such duties.

# Discover all engineers
grep "Engineering" messy_data.csv

# Discover rows with empty fields (two consecutive commas)
grep ",," messy_data.csv

# Exclude rows with lacking knowledge
grep -v ",," messy_data.csv > no_missing.csv

Right here, grep "sample" searches for traces containing that sample. grep -v inverts the match (exhibits traces that DO NOT match). This can be a fast option to filter out incomplete data, supplied the lacking worth leads to a double comma (,,).

# 5. Trimming Whitespace with sed

 
See how the report of “Bob Davis” has additional areas? Let’s clear that up.

sed 's/^[ t]*//; s/[ t]*$//' messy_data.csv > trimmed_data.csv

Now let’s perceive the command: sed is a stream editor for textual content transformation. s/sample/alternative/ is the substitution syntax. ^[ t]* matches areas/tabs in the beginning of a line. [ t]*$ matches areas/tabs on the finish of a line. The semicolon separates two operations (trim the road begin, then trim the road finish).

# 6. Changing Values with sed

 
Generally you want to standardize values or repair typos. Let’s attempt to change all occurrences of “Engineering” with “Tech”.

# Exchange all "Engineering" with "Tech"
sed 's/Engineering/Tech/g' messy_data.csv

Subsequent, let’s fill empty e-mail fields (denoted by a comma on the finish of the road) with a default e-mail worth.

# Exchange empty e-mail fields with "[email protected]"
sed 's/,$/,[email protected]/' messy_data.csv

Run the above instructions and observe the output. I’ve excluded the output right here to keep away from being repetitive.

Breaking it down:

• The g flag means “world” — change all occurrences on every line.
• ,$ matches a comma on the finish of a line (indicating an empty final area).
• You possibly can chain a number of replacements with ; between them.

# 7. Counting and Summarizing with awk

 
awk is tremendous helpful for field-based operations. Let’s do some fundamental evaluation.

# Depend data by division
tail -n +2 messy_data.csv | minimize -d',' -f4 | kind | uniq -c

# Calculate common age (excluding header and empty values)
tail -n +2 messy_data.csv | awk -F',' '{if($2) sum+=$2; if($2) rely++} END {print "Common age:", sum/rely}'

On this awk command, -F',' units the sector separator to a comma, and $2 refers back to the second area (age). The situation if($2) ensures solely non-empty values are processed, whereas sum += $2 accumulates the whole. Lastly, the END block executes in any case traces are learn to calculate and print the common age.

Output:

      5 Engineering
      3 Advertising
      2 Gross sales
Common age: 33

# 8. Combining Instructions with Pipes

 
You get extra helpful processing while you chain these command-line instruments collectively.

# Get distinctive departments, sorted alphabetically
tail -n +2 messy_data.csv | minimize -d',' -f4 | kind | uniq

# Discover engineers with wage > 55000
tail -n +2 messy_data.csv | grep "Engineering" | awk -F',' '$3 > 55000' | minimize -d',' -f1,3

# Depend workers per division with counts
tail -n +2 messy_data.csv | minimize -d',' -f4 | kind | uniq -c | kind -rn

Right here, every | passes the output of 1 command as enter to the subsequent. This allows you to construct complicated knowledge transformations step-by-step. The final step kinds by rely in reverse numerical order (-rn).

This outputs:

Engineering
Advertising
Gross sales
   Bob Davis    ,60000
      5 Engineering
      3 Advertising
      2 Gross sales

# 9. Changing Knowledge Codecs

 
Generally you want to work with completely different delimiters. Right here, we attempt to use a tab because the separator as a substitute of a comma.

# Convert CSV to TSV (tab-separated)
sed 's/,/t/g' messy_data.csv > knowledge.tsv

# Add a brand new column with a hard and fast worth
awk -F',' 'BEGIN{OFS=","} {print $0, "2024"}' messy_data.csv > data_with_year.csv

On this awk command, BEGIN{OFS=","} units the output area separator to a comma. $0 represents the whole enter line, and print $0, "2024" appends “2024” as a brand new column to every line of output.

# 10. A Full Cleansing Pipeline

 
Let’s put all of it collectively into one helpful command that cleans our messy knowledge:

# Save header
head -n 1 messy_data.csv > final_clean.csv

# Clear the information: take away duplicates, trim whitespace, exclude lacking values
tail -n +2 messy_data.csv | 
  sed 's/^[ t]*//; s/[ t]*$//' | 
  grep -v ",," | 
  kind | 
  uniq >> final_clean.csv

echo "Cleansing full! Test final_clean.csv"

This pipeline first saves the header to protect the column names, then skips it whereas processing the information rows. It trims main and trailing whitespace from every line, removes any rows containing empty fields (particularly double commas), kinds the information, and eliminates duplicate entries. Lastly, it appends the cleaned knowledge to the output file.

# Conclusion

 
Command-line knowledge cleansing is a robust but underrated talent for knowledge scientists. These instruments are quick and dependable. Whereas you’ll nonetheless use Python for complicated evaluation, mastering these fundamentals will make you extra environment friendly and offer you choices when Python is not splendid.

The most effective half is that these expertise switch to knowledge engineering, DevOps, and system administration roles. Studying to govern knowledge on the command line makes you a extra versatile developer.

Begin practising with your individual datasets, and you may be shocked how typically you attain for these instruments as a substitute of spinning up Python notebooks. Completely happy knowledge cleansing!
 
 

Bala Priya C is a developer and technical author from India. She likes working on the intersection of math, programming, knowledge science, and content material creation. Her areas of curiosity and experience embody DevOps, knowledge science, and pure language processing. She enjoys studying, writing, coding, and low! At the moment, she’s engaged on studying and sharing her data with the developer group by authoring tutorials, how-to guides, opinion items, and extra. Bala additionally creates participating useful resource overviews and coding tutorials.