9

How can I manipulate field-based data from the commandline? For example

  • How can I print only lines whose Nth field is foo?
  • How can I print only lines whose Nth field isn't foo?
  • How can I print only lines whose Nth field matches foo?
  • How can I change field N to foo?

Is there a standard approach or toolset that facilitates manipulating field-based data on *nix systems?

  • possible duplicate of How can I replace a string in a file(s)? – mikeserv Jun 17 '15 at 18:42
  • 4
    No, this is not a dupe. It deals with the specific case of data organized in fields and is not only about replacing. It is also about printing a subset of the file. – terdon Jun 17 '15 at 18:46
  • Separated into fields how? The answer suggests delimited by commas (csv), tab, space or similar. I think a word for specialized libraries like R or Python's pandas that convert csv files etc to data frames is also in order. Those are probably the best solutions overall. And yes, I know it said "from the command line". – Faheem Mitha Jun 18 '15 at 6:15
  • @FaheemMitha I just wanted to give a primer showcasing the abilities of tools like awk, perl and sed to deal with field-based data. I don't want to include more advance techniques in my answer since I'd rather keep it simple. I'd like to read it though so feel free to add your own answer using R or python. – terdon Jun 18 '15 at 11:37
9

There are two basic approaches one can use when dealing with fields: i) use a tool that understands fields; ii) use a regular expression. Of the two, the former is usually both more robust and simpler.

Many of the commonly available tools on *nix are either explicitly designed to deal with fields or have nifty tricks to facilitate it.

1. Use a tool that understands fields

1.1 awk

The classic tool here is awk. It will automatically split each input line into fields (the field separator is whitespace by default but can be changed using the -F flag) and the fields are then available to the awk script as $n where n is the field number. The 1st field is $1, the second $2 etc.

  • Print lines whose 3rd field is foo.

    awk '$3=="foo"' file
    

    Changing the delimiter to :

    awk -F":" '$3=="foo"' file
    

    The default action of awk is to print. Therefore the commands above will print all lines whose 3rd field is foo. When using -F, you can set arbitrary field separators, and even use regular expressions.

  • How can I print only lines whose 3rd field isn't foo?

    awk '$3!="foo"' file
    
  • How can I print only lines whose 3rd field matches foo?

    If you're just looking for fields that match a pattern (for example, foo matches foobar), use ~ instead of ==:

    awk '$3~/foo/' file
    
  • How can I print only lines whose 3rd field doesn't match foo?

    awk '$3!~/foo/' file
    
  • How can I change the 3rd field to foo?

    awk '$3="foo"' file
    

1.2 Perl

Another choice is perl one-liners. Like awk, Perl is a full-featured scripting language but can also be run as a commandline program taking a script as input. Its behavior is modified by commandline switches, the most relevant of which for this question are:

  • -e : the script that perl should run;
  • -n : read the input file line by line;
  • -p : print each input line after applying the script given by -e;
  • -l : remove trailing newlines from each input line and add a newline to each print call;
  • -a : awk-mode, split each input line into the array @F;
  • -F : the field separator for -a.

An important difference with awk is that perl's -a switch splits files into an array. In Perl, arrays start at 0, not 1. This means that the 2nd field is actually $F[1] and not $F[2]. With all this in mind, the perl equivalents of the above are:

  • Print lines whose 3rd field is foo.

    perl -ane 'print if $F[2] eq "foo"' file
    

    Changing the delimiter to :

    perl -F":" -ane 'print if $F[2] eq "foo"' file
    

    Unlike awk, perl can't use regular expressions as field delimiters. They need to be a specific character or string.

  • How can I print only lines whose 3rd field isn't foo?

    perl -ane 'print unless $F[2] eq "foo"' file
    
  • How can I print only lines whose 3rd field matches foo?

    perl -ane 'print if $F[2]=~/foo/' file
    
  • How can I print only lines whose 3rd field doesn't match foo?

    perl -lane 'print unless $F[2]=~/foo/' file
    
  • How can I change the 3rd field to foo?

    This one is a bit more cumbersome in Perl. The usual approach is to change the value in the @F array and then print the array. With simple space-separated files, this is easy:

    perl -lane '$F[2]="foo"; print "@F"' file
    

    With a different delimiter, you will need to join the array. Otherwise, it will be printed space-separated:

    perl -F: -lane '$F[2]="foo"; print join ":",@F' file
    

2. Use regular expressions

The idea here is to use a regular expression ("regex" for short) that defines the position of the target string in the line. For example, in a file whose fields are separated by :, we can find the 2nd field by matching everything up to the 1st : (the 1st field) and then looking for the second:

^[^:]*:[^:]*:

This regex means:

  • ^ : the beginning of the line;
  • [^] : a negated character class. [^:] means "anything but :";
  • * : 0 or more of the previous pattern;
  • : : a literal :;

Taken together, this means that the first [^:]* is the first field and the second is the second field. Obviously, this is not very practical if you're looking for the 14th field but it can be useful for simpler things. So, how do we implement this to manipulate our data? There are various tools that can do this; in these examples I will use sed but you could do very similar things with awk, perl or python.

  • How can I print only lines whose 2nd field is foo?

    sed -n '/^[^:]*:foo:/p' file
    

    The -n suppresses normal output and the /regex/p means "print any lines that the regex matched.

  • How can I print only lines whose 2nd field isn't foo?

    sed '/^[^:]*:foo:/d' file
    

    The logical inverse of the above. Here, the /regex/d means "delete any lines that the regex matches.

  • How can I print only lines whose 2nd field matches foo?

    sed -n '/^[^:]*:[^:]*foo/p' file
    
  • How can I print only lines whose 2nd field doesn't match foo?

    sed '/^[^:]*:[^:]*foo/d' file
    
  • How can I change the 2nd field to foo?

    sed 's/\([^:]*:\)[^:]*/\1foo/' file 
    

    Or, since sed substitution can directly address a patterns occurrence by its repetition with a simple numeric flag:

    sed 's/[^:]*/foo/2' file
    
  • (1) Is there any standard, garden-variety *nix tool that understands embedded delimiters in fields, e.g., Capt. Kirk, Mr. Spock, "Dr. McCoy, MD", Scotty?  (2) I’m troubled by the fact that (i) → 2 and (ii) → 1, but I didn’t want to change anything that major.  (3) If this is being nominated to be a canonical question, shouldn’t it link to some thorough and authoritative references on regular expressions (e.g., Wikipedia, Regular-Expressions.info, RegexPlanet, etc)? – G-Man Says 'Reinstate Monica' Jun 17 '15 at 23:30
  • @G-Man 1) not as far as I know 2) fair enough, fixed 3) I just wanted to write a simple primer on the two main approaches to dealing with fields. I don't want to go into too much detail and it certainly isn't aimed at being a reference on regular expressions. Thanks for eating the apples by the way :) – terdon Jun 18 '15 at 11:08
  • In case you care: s/:[^:]*/:foo/ also works., but the backref is worth including in a canonical post about regexp as well, probably. – mikeserv Jun 18 '15 at 16:45
  • @G-Man - sed can do the embedded delimiters, and really, any fully-BRE capable tool can as well. With that list you gave, doing: sed 's/[^,"]*\("[^"]*\)\{0,1\}[^,]*,//;s///2' <list will print Mr. Spock, Scotty (with a leading space) because the first substitution removes the first field, and the second removes Bones. – mikeserv Jun 18 '15 at 22:32
  • You say Unlike awk, perl can't use regular expressions as field delimiters. - this is only true if you use -a to auto-split. You can still use split() yourself if you want. e.g. perl -p -e 'my @F = split /regexp/ ; print if $F[2] =~ /foo/' – cas May 19 '16 at 11:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.