Lesson 10.3: Cleaning data with regular expressions in sed and awk
Capture groups and back-references in sed (\1, \2, &), reformatting dates, fixing capitalisation with GNU sed's \u and \U, matching fields in awk with ~ and !~, normalising text with gsub, finding values that have the right shape but the wrong meaning, and combining it all into a cleaned file.
20 min
You will learn to
- Capture parts of a match and reuse them with \1, \2 and &
- Reorder fields such as dates with sed -E
- Change letter case in a substitution
- Test a field against a regex in awk with ~ and !~
- Replace every match in a field with awk's gsub
- Build a cleaned copy of a data file
Key terms
- Capture group
- Parentheses in the pattern remember what they matched. Groups are numbered from the left by their opening parenthesis.
- Back-reference \1 … \9
- In the replacement of sed's s command, \1 is the text of group 1, \2 of group 2, and so on.
- &
- In the replacement, the whole matched text. Write \& for a literal ampersand.
- \u \l \U \L \E
- GNU sed extensions in the replacement: upper/lower-case the next character, or everything until \E.
- awk ~ and !~
- field ~ /regex/ is true when the field matches; !~ when it doesn't. A bare /regex/ tests the whole line ($0).
- gsub and sub
- awk functions: gsub(re, repl, target) replaces every match, sub only the first. Both return the number of replacements.
← Repetition, groups and alternatives · All lessons