0

I'm writing a script to analyse some data. I have several subset of files, and I would like to count the line from these files and write the result into a csv file. I'll try to do an example. I have these two subset of file:

sample1.ext  
sample1.ext2  
sample1.ext3

sample2.ext  
sample2.ext2  
sample2.ext3

I would like to count the lines contained in all the files in *.ext, *.ext2 and *.ext3 and write the results into a csv file that seems to this:

count(sample1.ext), count(sample1.ext2), count(sample1.ext3)  
count(sample2.ext), count(sample2.ext2), count(sample2.ext3)

After counting the first series of file in *.ext, I output the results to the first column of a csv file. How do I write the output of the second count series of *.ext2 into the second column of the same csv file? And the same for the third column?


Thank to everybody for the answers, I was trying to adapt them to my files, but unfortunately I cannot do it. The example that I posted was just an example, where I put numbers instead of weird extensions to be easiest to understand the problem. You all understood, but you focused too much on the numbers that do not exist in the reality. I will explain you again using the real files. These files are coming from a mapping of genomic data to a reference genome. I treat these data to clean them, so I have three steps where the number of lines changes. So the file are:

name.sort.bam  
name.mapped.bam  
name.rmdup.bam  
othername.sort.bam  
othername.mapped.bam  
othername.rmdup.bam   

The extension bam is a compressed file. To count the lines in this file, there is a special command line:

samtools view -c (file)

The only way I found was to iterate into each *sort.bam, *mapped.bam, *rmdup.bam and write a txt output for each, and paste them at the end in a csv file. Is there a way to avoid these three loop and do averything together? Sorry for the misunderstanding, you all got great ideas!

  • Yes, sorry it was a bad copy-paste! corrected now! – Giulia L. Feb 8 '16 at 10:41
  • are all files in one (flat) directory? – Jacob Vlijm Feb 8 '16 at 11:26
  • Hi @Giulia, did you notice the answers? – Jacob Vlijm Feb 9 '16 at 10:40
  • Yes all the files are in the same directory! – Giulia L. Feb 9 '16 at 13:11
  • Hi Giulia, did you try the answers? Could you give some feedback, does it work etc? – Jacob Vlijm Feb 9 '16 at 15:16
1

You can use this Perl script:

#! /usr/bin/perl
use strict;
use warnings;

my @names;
my @files;

@ARGV == 1 || die();

opendir(my $dir, $ARGV[0]) || die $!;

while(readdir($dir)) {
    if($_ =~ /(.*)\.(sort|mapped|rmdup)\.bam$/) {
        grep(/^$1$/, @names) == 0 && push(@names, $1);
    }
}

close($dir);

foreach my $name (sort(@names)) {
    my @fields;
    push(@fields, $name);
    foreach my $extension ("sort", "mapped", "rmdup") {
        if(! -f "$ARGV[0]/$name.$extension.bam") {
            push(@fields, 0);
            print STDERR "'$ARGV[0]/$name.$extension.bam' missing\n";
            next;
        }
        my $count = `<"$ARGV[0]/$name.$extension.bam" wc -l`;
        chomp($count);
        push(@fields, $count)
    }
    print(join(", ", @fields)."\n")
}

Save it somewhere in your system, make it executable and run it passing the directory as an argument:

path/to/script path/to/directory
% tree directory
directory
├── name.mapped.bam
├── name.rmdup.bam
├── name.sort.bam
├── othername.mapped.bam
├── othername.rmdup.bam
└── othername.sort.bam

0 directories, 6 files
% perl script.pl directory
name, 0, 0, 0
othername, 0, 0, 0
% for f in directory/*.sort.bam; do printf 'line\n' >>"$f"; done
% perl script.pl directory                                      
name, 1, 0, 0
othername, 1, 0, 0

What the script does is:

  • Iterates through all the files in path/to/directory; if a filename matches .*\.(sort|mapped|rmdup)\.bam$, appends the string before .sort.bam, .mapped.bam or .rmdup.bam to a list @names if not already in the list;
  • For each name in the sorted @names list as $name, appends $name to a list @fields; for each extension in sort, mapped and rmdup as $extension checks if $name.$extension.bam exists in path/to/directory; if the file doesn't exist, appends 0 to @fields, prints an error message and moves on to the next $extension / $name; if the file exists, appends the output of <"$name.$extension.bam" wc -l to @fields; once all of the possible values for $extension have been iterated, prints a line containing the elements of @fields joined on ,.
  • Thank you kos, seems really nice. As I'm new in scripting, can you explain me what does this line do: @ARGV == 1 || die() ? – Giulia L. Feb 12 '16 at 10:24
  • @GiuliaL. ARGV is an array containing the list of arguments passed to the script (e.g., when running the script with path/to/script path/to/directory as in the example, ARGV contains /path/to/directory); when in scalar context (e.g. in case of a numerical comparison such as in @ARGV == 1) it returns the number of elements in the array. – kos Feb 12 '16 at 10:41
  • Since we want the script to proceed further only in case a single argument was passed to the script, @ARGV is compared against 1; if @ARGV == 1 evaluates to 0 (false), then die() is executed; die() simply exits the script with an error message. The error message could be made more verbose e.g. by replacing die() with die("Too few / too many arguments"). – kos Feb 12 '16 at 10:41
  • 1
    @GiuliaL. You should append it to fields as the first thing inside the first loop (i.e. push(@fields, $name) after my @fields): see the updated script. Remember also that you may accept either mine, Jacob Vlijm's or waltinator's answer if they worked for you (it's a way to indicate that an answer worked for you, that the question has been answered and at the same time to thank the user; if more than one question worked for you just pick the one that you prefer, for a reason or another). – kos Feb 12 '16 at 11:37
  • 1
    @GiuliaL. additionally (be sure, we're not begging for anything, but believe you are unfamiliar with the voting system) , once you earned > 15 reputation: if you like an answer, you can upvote it. I am pretty sure the existing votes are because we valued each other's answers :). – Jacob Vlijm Feb 12 '16 at 11:41
1

Assuming you want output like 42, 19, 10207, 3 on each line (no filenames), wcand some Bashing will solve your problem.

outfile="Result.csv" 
for samplenum in $( seq 1 100 ) ; do
    line=""
    for file in sample${samplenum}.* ; do
        numlines=$( wc -l <$file )
        line="$line $numlines,"
    done
    # remove the final comma
    line=${line%,}
    # not quoting $line below will suppress the initial blank 
    echo $line >> $outfile 
done

Read man bash, man wc, man seq and man bash again

Responding to comment:

Did you read the man pages?

$( seq 1 100) is replaced with the results of the seq 1 100 command, which simply outputs the integers from 1 to 100 (which reading man seq would have told you). Replace it with something that supplies the numbers of the samples you have.

Put the code in a file (e.g. test.sh) and run it with bash -x test.sh to see details. Replace the seq 1 100 with seq 1 2 for the test, to avoid an avalanche of output.

samplenum holds the number of the sample, which, for this example, runs from 1 to 100.

sample, in sample${samplenum}.* is just a string. It is concatenated with the value of samplenum and the string .* to produce the filename pattern, e.g. sample1.* the first time through the for samplenum ... loop, sample2.* the second time, etc.

Did you read, and understand, man bash, man wc, man seq and man bash again?

  • Thank you, but can you explain me what does it do? what are the variable samplenum and sample and the (seq 1 100) what does it means? – Giulia L. Feb 8 '16 at 10:47
1

A python option

Interesting question. A nice occasion to apply python's groupby()

Since your files are in a single, "flat" directory:

#!/usr/bin/env python3
from itertools import groupby
import os
import sys

dr = sys.argv[1]
# list the files in the directory, split into "sortable" elements
flist = [[item, item.split(".", 1)] for item in os.listdir(dr)]
# sort the file list by first section (until the first found dot)
flist.sort(key=lambda x: x[1][0])
# create sub groups of the files, grouped by first section of name
for key, line in groupby(flist, lambda x: x[1][0]):
    line = list(line)
    # sort the files by second section of name for correct order in the csv lines
    line.sort(key=lambda x: x[1][1])
    # count the lines of the files, arrange the csv file
    print((", ").join([str(len(open(dr+"/"+f[0]).readlines())) for f in line]))

How it works

If a directory contains nine files:

sample1.ext                  2 lines
sample1.ext2                 3 lines
sample1.ext3                 3 lines

sample2.ext                  1 lines
sample2.ext2                 1 lines
sample2.ext3                 4 lines

sample3.ext                  6 lines
sample3.ext2                 1 lines
sample3.ext3                 4 lines
  • The script lists the files, splits each of the names into two sections, e.g.:

    sample2
    

    and

    ext2
    

    since the order of both the lines and the file length inside the lines depends on accurate sorting of these two sections.

  • The script then sorts the files by the first section of the name, since the length of each of the files (with a similar first name section) should be grouped per first section into one line; sample1, sample2, sample3 and so on.
  • Subsequently, sub groups (per csv line) are created, properly sorted by the second name section, to make the (line-) numbers appear in the correct order in the line

The result:

python3 '/home/jacob/Bureaublad/create_csv.py' '/home/jacob/Bureaublad/samples' 
2, 3, 3
1, 1, 4
6, 1, 4

How to use

  • Copy the script into an empty file, save it as create_csv.py
  • Run it with the directory with your files as argument

    python3 /path/to/create_csv.py /path/to/directory_with_files
    

Important note

The method, used to count the lines, is appropriate as long as the files are not huge. If the files are, another method to count the lines would result in a better performance.


EDIT

As a result of the latest info, added to your question, an edited version of the script. Coincidentally, not much has to be changed: the script already split the file names by the first found dot, by the command:

item.split(".", 1)

Since the last section of the name is .bam, which is equal on all files, it is meaningless for the sorting order.

Then we only need to replace the "old" way to count the file's lines:

str(len(open(dr+"/"+f[0]).readlines()))

by (the python implementation & integration into the script of-) the command you provided:

str(subprocess.check_output(["samtools", "view", "-c", dr+"/"+f[0]]).decode("utf-8").strip())

The edited script

#!/usr/bin/env python3
from itertools import groupby
import os
import sys
import subprocess

dr = sys.argv[1]
# list the files in the directory, split into "sortable" elements
flist = [[item, item.split(".", 1)] for item in os.listdir(dr)]
# sort the file list by first section (until the first found dot)
flist.sort(key=lambda x: x[1][0])
# create sub groups of the files, grouped by first section of name
for key, line in groupby(flist, lambda x: x[1][0]):
    line = list(line)
    # sort the files by second section of name for correct order in the csv lines
    line.sort(key=lambda x: x[1][1])
    # count the lines of the files, arrange the csv file
    print((", ").join([
        str(subprocess.check_output(["samtools", "view", "-c", dr+"/"+f[0]]).decode("utf-8").strip())
        for f in line]))

Note

Note that the order of the numbers inside the line is determined by the sorted order of the second part of the name, e.g.

mapped.bam, rmdup.bam, sort.bam  
  • It works really nicely, is fast and is perfectly answering to what I was looking for. Thanks for your explanation and your time spent to do this nice work. Can I just ask you what the "subprocess" do in the new version? – Giulia L. Feb 12 '16 at 11:05
  • @GiuliaL. The command samtools view -c (file) is not a "native" python command. Therefore we need to "connect" python to it, to read its output. To do so, we use the subprocess module, see also (e.g.): sharats.me/the-ever-useful-and-neat-subprocess-module.html – Jacob Vlijm Feb 12 '16 at 11:11

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.