How can I count lines of differently named files, and write the outcome to a csv file?

Question

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!

Answer 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 ,.

Answer 2

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?

Answer 3

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