0

I want to get multiple, specific columns from a large file on a Linux system that has two thousand columns. How can I do this?

The file, file1.gz, looks like:

0 0 0 0 0 0 0 0 0 0 ...
0 0 0 0 0 0 0 0 0 0 ...
0 0 0 0 0 0 0 0 0 0 ...
0 0 0 0 0 0 0 0 0 0 ...
0 0 0 0 0 0 0 0 0 0 ...
0 0 0 0 0 0 0 0 0 0 ...
0 0 0 0 0 0 0 0 0 0 ...
0 0 0 0 0 0 0 0 0 0 ...
0 0 0 0 0 0 0 0 0 0 ...

The columns I need to get in file2, looks like:

186
187
188
189
190
191
192
193
194
195
(about 1000 column)
10
  • 2
    Welcome to the site. Please edit your file to include a minimum testable example including example input and desired output. Don't forget to specify how your input files are formatted (CSV, tab-separated, ...). Also, please show what you tried already and where you faced difficulties, so that contributors can help you in a targeted way and avoid proposing answers that you already know won't work.
    – AdminBee
    Jul 28 '21 at 9:27
  • You say file.gz?, so it's a gzipped file?
    – pLumo
    Jul 28 '21 at 10:02
  • yes, gzipped file Jul 28 '21 at 10:05
  • 3
    @berndbausch please don't answer in comments: we can't vote on comments, so that circumvents the quality control system of the site (such as it is). Also, comments can be deleted without warning so any such answer will be lost.
    – terdon
    Jul 28 '21 at 10:43
  • 1
    @berndbausch yes, I get that. I was basically saying: don't post that kind of thing as a comment. Either flesh it out into a full answer, or don't comment because leaving comments with partial answers might i) make people think that is the answer, but we can't vote down wrong ones and ii) discourage others from posting a proper answer because "meh, somebody already said this in the comments".
    – terdon
    Jul 28 '21 at 15:12
4

In awk, you can refer to a specific column by number. For example, column 12 is $12 and column 1345 is $1345. In addition, the default column separator is whitespace, so in your example with a space-separated file, all you need is to decompress and pass through an awk script that prints the columns you are interested in.

zcat file.gz | awk '{print $1,$12,$195} > newFile

The complication here is that you have too many desired columns for it to be practical to type them out. Here, you need to read the columns first and then print:

awk '{
        if (NR==FNR){ wantedColumns[NR]=$1 }
        else{ 
            for(i=1;i<=length(wantedColumns)-1;i++){ 
                printf "%s ", $(wantedColumns[i])
            }
            print $(wantedColumns[length(wantedColumns)])
        }
     }' file2 <(zcat file1.gz)

For example:

$ zcat file1.gz
line1_field1 line1_field2 line1_field3 line1_field4 line1_field5 line1_field6 
line2_field1 line2_field2 line2_field3 line2_field4 line2_field5 line2_field6 
line3_field1 line3_field2 line3_field3 line3_field4 line3_field5 line3_field6 
line4_field1 line4_field2 line4_field3 line4_field4 line4_field5 line4_field6 
line5_field1 line5_field2 line5_field3 line5_field4 line5_field5 line5_field6 
line6_field1 line6_field2 line6_field3 line6_field4 line6_field5 line6_field6 
line7_field1 line7_field2 line7_field3 line7_field4 line7_field5 line7_field6 
line8_field1 line8_field2 line8_field3 line8_field4 line8_field5 line8_field6 
line9_field1 line9_field2 line9_field3 line9_field4 line9_field5 line9_field6 

$ cat file2
2
4
5

If I run the script above on these files, I get:

$ awk '{
>         if (NR==FNR){ wantedColumns[NR]=$1 }
>         else{ 
>             for(i=1;i<=length(wantedColumns)-1;i++){ 
>                 printf "%s ", $(wantedColumns[i])
>             }
>             print $(wantedColumns[length(wantedColumns)])
>         }
>      }' file2 <(zcat file1.gz)
line1_field2 line1_field4 line1_field5
line2_field2 line2_field4 line2_field5
line3_field2 line3_field4 line3_field5
line4_field2 line4_field4 line4_field5
line5_field2 line5_field4 line5_field5
line6_field2 line6_field4 line6_field5
line7_field2 line7_field4 line7_field5
line8_field2 line8_field4 line8_field5
line9_field2 line9_field4 line9_field5
line10_field2 line10_field4 line10_field5

Explanation

  • if (NR==FNR){ wantedColumns[NR]=$1 } : NR is the input line number, and FNR is the line number of the current file. The two will be equal only while reading the first file. So, if NR is equal to FNR, if we are reading the first file, then save the first field of this file in the array wantedColumns whose indices are the line numbers and values are the fields.
  • else { ... }: if we are not reading the first file, if we are now at the second file.
  • for(i=1;i<=length(wantedColumns)-1;i++){ : iterate over the array of wanted columns, from the first index (which will be 1 since we are using the value of NR in the loop above) until the penultimate, and print each of them followed by a space. Important caveat: the column order of the original file will not necessarily be preserved. The columns will be printed in the order in which they were found in file2. If that is not the same as the order in the original file, if file2 has for example 1 3 2 instead of 1 2 3, then that is the order they will be printed in.
  • print $(wantedColumns[length(wantedColumns)]): print the last field followed by a newline.
  • <(zcat file1.gz) : This is a feature of bash (and some other shells) called ["process substitution"][1] which allows you to treat the output of a command as though it were a file. Here, we are decompressing the file with zcat and passing that as the second input "file" to awk.

Note that this approach will add an extra trailing space at the end of each line. If this is a problem, you can avoid it by piping through sed at the end:

awk '...' | sed 's/ $//'

Alternatively, you can use cut. Just change file2 into a comma-separated list of fields and pass that to cut:

$ zcat file1.gz | cut -d' ' -f $(tr '\n' ',' < file2 | sed 's/,$//') 
line1_field2 line1_field4 line1_field5
line2_field2 line2_field4 line2_field5
line3_field2 line3_field4 line3_field5
line4_field2 line4_field4 line4_field5
line5_field2 line5_field4 line5_field5
line6_field2 line6_field4 line6_field5
line7_field2 line7_field4 line7_field5
line8_field2 line8_field4 line8_field5
line9_field2 line9_field4 line9_field5
line10_field2 line10_field4 line10_field5

Explanation

  • zcat file1.gz |: decompress file1.gz and pipe its contents to the next command.
  • cut -d' ' : this tells cut to use a space instead of the default tab (\t) as the field separator.
  • -f $(tr '\n' ',' < file2 | sed 's/,$//'): -f tells cut which fields to print. It can take a comma-separated list of fields, so we use tr '\n' ',' to convert all newlines to commas and pass the result as the list of fields. [1]: https://www.gnu.org/software/bash/manual/html_node/Process-Substitution.html
4
  • I'm not clear why that works: specifically, why column order is maintained. "column in .." does not impose an order on the sequence of keys. GNU/awk does seem to report small numeric keys in order, but I suspect that breaks down for larger keys, and the order is entirely random in other awks. BEGIN { PROCINFO["sorted_in"] = "@ind_num_asc"; } might fix it. Jul 28 '21 at 10:42
  • @Paul_Pedant yes, I think it just happens to work in this case because it's a tiny array. Thanks, I keep forgetting this awk foible of not keeping the order. I'll fix in a minute.
    – terdon
    Jul 28 '21 at 10:55
  • @Paul_Pedant thanks, I switched to iterating using indices which should ensure we at least use whatever order was in file2.
    – terdon
    Jul 28 '21 at 11:58
  • you can also use paste -sd, instead of tr+sed
    – Sundeep
    Jul 28 '21 at 15:08
2

Untested since you didn't provide sample input/output we could test with but should be about right:

zcat file1.gz | awk '
NR==FNR { out2inFldNr[++numOutFlds] = $1; next }
{
    for (outFldNr=1; outFldNr<=numOutFlds; outFldNr++) {
        inFldNr = out2inFldNr[outFldNr]
        printf "%s%s", $inFldNr, (outFldNr<numOutFlds ? OFS : ORS)
    }
}
' file2 -
0

Using perl:

#!/usr/bin/perl
use strict;
my @file1;

# read in first file, assuming one column number per line
# subtract 1 because perl arrays start from 0 and append to
# an array called @file1
while(<>) {
  push @file1, $_-1;
  last if eof; # exit loop after end of the first file
};

# process second file, splitting it into an array called @line
# and then printing only the elements listed in the @file1 array
# (this is known as an "array slice", and perl is very flexible
#  about how it can be specified. see `man perldata` for details)
while(<>) {
  my @line = split;
  print join("\t", @line[@file1]),"\n";
};

With the input files, f1.txt and f2.txt.gz (see below), it produces the following output:

$ ./extract.pl f1.txt <(zcat f2.txt.gz)
a       c       e       g
a       c       e       g
a       c       e       g
a       c       e       g
a       c       e       g

Iinput files:

$ cat f1.txt
1
3
5
7


$ zcat f2.txt.gz
a b c d e f g h i j k l m n o p q r s t u v w x y z
a b c d e f g h i j k l m n o p q r s t u v w x y z
a b c d e f g h i j k l m n o p q r s t u v w x y z
a b c d e f g h i j k l m n o p q r s t u v w x y z
a b c d e f g h i j k l m n o p q r s t u v w x y z

It can also be written as a one-liner:

$ perl -lne 'push @file1, $_-1; last if eof;
             END {
               while(<>) {
                 my @line=split;
                 print join("\t", @line[@file1]);
               };
             }' f1.txt <(zcat f2.txt.gz)

The output is identical for both versions.


BTW, both versions of script above require two (or more) filename arguments (either actual filenames, or via process substitution). If, instead, you want to read the second file from stdin, you'd have to write it like:

#!/usr/bin/perl
use strict;
my @file1;

my $f1 = shift;
open(my $fh,"<",$f1) || die "couldn't open $f1: $!\n";
while(<$fh>) {
  push @file1, $_-1;
};
close($f1);

while(<>) {
  my @line = split;
  print join("\t", @line[@file1]), "\n";
};

That would allow you to run it like this:

$ zcat f2.txt.gz | ./extract.pl f1.txt

or you can still run it the same way as the first version:

$ ./extract.pl f1.txt <(zcat f2.txt.gz)

In other words, with this version, the first file must be given by a filename but the second can either be a file or stdin.

Yet another variation is to allow both files to come from stdin.

#!/usr/bin/perl

use strict;
my @file1;

while(<>) {
  my @line = split;
  if (@line == 1) {
    push @file1, $_-1;
  } else {
    print join("\t", @line[@file1]), "\n";
  }
};

This version checks to see how many fields are on each input line. If there's only one, we're still reading the first file so add it to the @file1 array. Otherwise print out the array slice.

It would be run something like this:

$ (cat f1.txt ; zcat f2.txt.gz) | ./extract.pl

or as a one-liner, using perl's -a auto-split into array @F option (which works similarly to how awk automatically splits its input into $1, $2, $3 etc):

$ (cat f1.txt ; zcat f2.txt.gz) |
  perl -lane 'if (@F==1) {push @file1,$_-1} else {print join("\t",@F[@file1])}'
0

You can do it as follows. Start by generating a fields composition in the form of ranges looking at file2, after you've numerically sorted n uniquified it.

Then a perl regex (advanced level) will turn the input like 24, 25, 26, 33 => 24-26,33 and we then feed it to cut options.

$ cols=$(< file2 sort -nu | perl -00pe '$_ = s/(\d+)(?{$1})\K(?:\n(\d+)(?(?{++$^R!=$2})(*F)))+/-$2/gr =~ s/\n(?!\z)/,/gr')

$ gunzip -c file1.gz | cut -d' ' -f"$cols"

Should the column numbers be contiguous then you can simply get the first n last column numbers, as in:

$ cols=$(< file2 sort -nu | sed '$q;1!d' | paste -sd- -)

And apply like previously to cut.

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.