0

I have around 20 files in different folders for which I have created a separate file called pathtofiles.in which lists path information:

/home/users/gray_wolf/unix/File_1.tsv
/home/users/gray_wolf/unix/File_2.tsv
.
.
.

Each file contains around 11 columns and approximately 100,000 rows. Examples:

File_1:

Chromosome    begin    end    .....
chr1          1000     2000
chr1          2000     3000
chr1          4000     5000
chr1          5000     6000
chr1          10000    12000
chr1          12000    13000

File_2:

Chromosome    begin    end    .....
chr1          1000     2000
chr1          4000     5000
chr1          5000     6000
chr1          6000     7000
chr1          10000    12000
chr1          13000    14000

Final required file format:

Chromosome    begin    end     Column5                    column8 
chr1          1000     2000    File1,File2,File3...       File1,File2,File3...
chr1          2000     3000    File1,File2(0),File3       File1,File2(0),File3...
chr1          4000     5000    File1,File2,File3...       File1,File2,File3...
chr1          5000     6000    File1,File2,File3...       File1,File2,File3...
chr1          6000     7000    File1,File2,File3...       File1,File2,File3...
chr1          10000    12000   File1,File2,File3...       File1,File2,File3...
chr1          12000    13000   File1,File2,File3...       File1,File2,File3...
chr1          13000    14000   File1(0),File2,File3...    File1(0),File2,File3...

When I pass pathtofile.in to script.sh like this:

./script.sh < pathtofile.in

...the script should read the files given in pathtofiles.in and output a 5-column tab separated list. The first 3 columns of output should be the 3 columns of each file combined. The 4th output column should be the corresponding row from the 5th column of all the input files, (in the same order as pathtofiles.in), comma separated. The 5th output column should be the corresponding row from the 8th column of all the input files, comma separated.

In the event that a given row of input lacks a column 8, (or column 5), output a "(0)" in its place.

I tried to cut the columns separately and use paste/join functions, but since there are different numbers of rows in each file, the order gets wrong. How can I do this using awk or some other command that runs under bash?

Thanks in advance.

~M

  • are the column widths fixed? is removing the multiple spaces to single ones an option (e.g. tr -s [:space:])? – Fiximan Aug 11 '15 at 14:20
  • Drav Sloan, I have not written script for this purpose. I tried to cut the columns using the command; cut -d$'\t'-f5 for all the files and same for column 8 and join them using paste command and join command – user3668772 Aug 11 '15 at 15:01
  • fiximan, the columns are tab separated. – user3668772 Aug 11 '15 at 15:06
  • Youe say, "column 5 and column 8 from all the files" - Do you mean, "column 5 and column 8 from the files that contain the same first 3 fields"? ... eg. Only the first sample file contains chr1 2000 3000, but your sample output show more than 1 comma-separated value in that row's new columns ... your sample output appears to basically nott match your input samples. It seems you have generalized your sample data,, when it would be better to keep it simple so that the output actually matches the input). The gereralization in your description is enough. – Peter.O Aug 11 '15 at 15:56
  • Peter.O sorry if the description had not been clear. In this case the corresponding row is absent hence in file 2, hence no value is present so it is zero in that place. I had forgot to mention that. – user3668772 Aug 11 '15 at 16:13
1

for a simple solution: paste the three files together, then get the columns you want:

paste -d' ' file1 file2 file3  |\
awk 'BEGIN { FS = " +" } { NR ==1} { printf "%-10s%-7s%-7s  %-12s  %-12s\n" $1,$2,$3,$6,$7 } { NR >=2 } { printf "%-10s%-7s%-7s  %s,%s%s  %s%s%s\n" $1,$2,$3,$6,$7,$8,$9,$10,$11 } '

This will have to be adopted according to your files and likings for the output format. Explanations:

1) paste -d' ' -> merge the tree files in vertical direction, use space as -delimiter.

2) pipe it to awk (and continue command in new line |\ for readability here)

2.1) BEGIN { FS = " +" } - for all following use one or more (+) spaces as field delimiter

2.2) in first line { NR ==1} print fields 1,2,3,6,7 ($1,$2 ...) with following format (in double quotes)

%-10s a fixed 10 character long string (rest filled with spaces, alligned to the left).

twice the same with 7-character length, then two spaces, a 12-character long string, two spaces, 12-character string. Add a new line \n in the end.

(found in the { printf "%-10s%-7s%-7s %-12s %-12s\n" $1,$2,$3,$6,$7 } part)

2.2) the Data: from lines two and greater { NR >=2 } print columns $1,$2,$3,$6,$7,$8,$9,$10,$11 with the format %-10s%-7s%-7s %s,%s,%s %s,%s,%s\n

similar to the above, but now e.g. columns 6,7,8 are of arbitrary length and separated by a comma %s,%s,%s

  • Dear Fiximan, thanks for the suggestion. The 3rd file I had given is the required format for output not another input file. – user3668772 Aug 11 '15 at 15:13
  • A my bad, the principle remains the same, though - but since the lines are not always matching, the solution will not fit all your need. – Fiximan Aug 11 '15 at 20:22
0

You're going to want a program that has all your input files open at once. awk has a getline <file syntax, so that or perl would be good choices. Or any other high level language you know.

I'd go with perl for this. There's http://www.bioperl.org/ with perl modules specifically for processing genetic sequence data file formats.

I got part-way into it and realized it may be more complex than I thought. You have to do a multi-way diff type of algorithm to handle the general case of when one of the input files has a different column 2/3 value than the others. You can't just keep reading lines from it until you get to a match for the begin/end pair you're looking for, because there might not be one.

So I think your best bet is a priority queue or something to get sorted input. For each file, read until the line you're inserting in the queue goes behind one that was there before you started on the current file. (Or, until the line you just read is the new tail of the pqueue).


If your files easily all fit in memory at the same time, building the output with an associative array (indexed by column2:column3) would make it easier to code. Then you don't need a priority queue, or to figure out which file to advance next.


Here's the code I have so far. It just loops over input lines without handling the out-of-order case, or merging. I stopped once I realized this was bigger than was reasonable for a stackexchange answer, but this might give you a start.

#!/usr/bin/perl -w

my @f = @ARGV;  # list of files to process


sub getfields($) {
    my $file = $_[0];
    my $ln = <$file> or return ();  # sentinel for EOF
    my @fl = split ' ', $ln, 9;
    return ( $fl[0], $fl[1], $fl[2], $fl[4], $fl[7] );
}

# open each filename in @f, storing the file handles in @f.
foreach (@f) {
    open $_, '<', $_  or die "opening $_: $!";
}

my $newdata = 0;
do {
    $newdata = 0;
    foreach my $fd (@f) {
    my @fl = getfields($fd);
    next if ! (@fl);  # end of file on $fd.  TODO: take it out of @f?
    $newdata = 1;
    print join("|", @fl), "\n";  # debug
    }
} while ($newdata);  # done when all files are EOF

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.