0

So I have a TSV file that looks something like this:

Hello world how are you
1 2 3 4 5
6 7 8 9 0

(I've separated everything within each row with a single whitespace in the hand-written toy example above.)

My objective is to get the columns that contain "how" and "are" as the first row values. The output, therefore, would look like this:

how are
3 4
8 9

The problem is that I do not know which column these patterns (i.e., "how" and "are") will occur. So, for example, the TSV file could actually be arranged like this:

Hello how world are you
1 3 2 4 5
6 8 7 9 0

I know how I can deal with this in, say, something like python (just transpose the file, and then select the rows I want) but I'd like to do it in the shell (for reasons). The problem is, I don't know how to. I know that transposition is also possible using command line utilities (e.g., see here) but I'd like to avoid that bulky code if I can. My initial, simple-minded solution was to just grep "how" and "are" but that obviously returned the entire file, and that's where I've been stuck since.

Any help or pointers would be appreciated!

Edit: I must mention, I do not have privileges to install any new tools on this machine. I am also quite uncertain what it actually comes with. If it helps, this is Scientific Linux 7.3 (Nitrogen).

  • You want to do this in the shell, but you've tagged this with [awk] and [grep]? Which one do you mean? With just the shell, or with standard utilities, or... what? – ilkkachu Sep 16 at 18:14
  • @ilkkachu well, standard utilities would be fine by me. Basically with just whatever is available (see my edit). I know this is a bit nebulous, and I apologize for that. – Dunois Sep 16 at 18:16
  • it's just that with text processing, awk is seriously useful, but it's not the same as just the shell. Even Busybox has awk, but one might still have some weird context where it's actually only the shell that's available. So it's necessary to know what's available. – ilkkachu Sep 16 at 19:16
3

Use csvtool¹:

csvtool -t ' ' -u ' ' namedcol how,are file

-t Input separator char.
-u Output separator char.

 namedcol <names>
    Assuming the first row of the CSV file is a list of column headings,
    this returned the column(s) with the named headings.

¹ sudo apt install csvtool

| improve this answer | |
  • Thank you for your answer. Problem is, this machine doesn't come with csvtool, and I can't install anything on it either. I didn't realize I should have mentioned this in the OP. I apologize for wasting your time! – Dunois Sep 16 at 18:13
  • 1
    No problem, I'll leave the answer though if anyone else needs it. – schrodigerscatcuriosity Sep 16 at 18:14
  • Thank you for being understanding. And I've learned about csvtool from your answer (and a tool like this is something I'd use elsewhere in the future), so it was not in vain! – Dunois Sep 16 at 18:17
3

Via awk you can check the first row for matching keywords, note down the column number, then print the respective values:

#first line -> Select columns based on keyword
NR==1 {
  for (i = 1; i <= NF; i++) {
    if ( $i == "how" ) {col_how=i}
    if ( $i == "are" ) {col_are=i}
  }
}
#print selected columns including header line
NR>=1 {
  print $col_how, $col_are
}

Save as e.g. script.awk and execute

awk -f script.awk datafile

EDIT:

Also your idea of transposing and greping can be realized easily:

datamash transpose <datafile | grep 'how\|are' | datamash transpose

use -t ' ' to define spaces as separators. Nevertheless on very long files this might run into RAM limits. However might not be installed by standard on most machines.

| improve this answer | |
2
$ cat tst.awk
BEGIN {
    cols = (cols == "" ? "how are" : cols)
    nf = split(cols,tgts)
    FS = OFS = "\t"
}
NR==1 {
    for (i=1; i<=NF; i++) {
        f[$i] = i
    }
}
{
    for (i=1; i<=nf; i++) {
        printf "%s%s", $(f[tgts[i]]), (i<nf ? OFS : ORS)
    }
}

.

$ awk -f tst.awk file
how     are
3       4
8       9

.

$ awk -v cols='are world you Hello' -f tst.awk file
are     world   you     Hello
4       2       5       1
9       7       0       6
| improve this answer | |

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.