0

I want to count values/word occurrence in second and third column of a tab delim txt file, where first column value is same

Input file:

GeneA Pathogenic snp
GeneA Pathogenic snp
GeneA Benign indel
GeneA Likely_pathogenic snp
GeneA Pathogenic    indel
GeneB Benign    snp
GeneB Pathogenic    snp
GeneB Benign    indel
GeneC  Benign   snp
GeneC  Likely_pathogenic    snp

Expected Output:(tabular format)

| Pathogenic | Benign | Likely_Pathogenic | snp | indel |
 ------------|--------|-------------------|-----|-------|
|GeneA|     3|       1|                  1|    3|      2|
|GeneB|     1|       2|                  0|    2|      1|
|GeneC|     0|       1|                  1|    2|      0|
7
  • 1
    Please fix your formatting (remove these unnecessary html tags and format as code). Then please show us what you tried and how it did not work.
    – pLumo
    Oct 1, 2021 at 7:31
  • So, you want html output or what do you mean by "tabular format"?
    – pLumo
    Oct 1, 2021 at 8:50
  • I can do that with Ruby. Let me know if that works. Because there are no programming language / shell mentioned.
    – 15 Volts
    Oct 1, 2021 at 8:53
  • @pLumo: not in html format, table with second/third col value should be used as header and count of these values should be mentioned with unique gene name in col first.
    – LiNi
    Oct 1, 2021 at 9:11
  • @S.Goswami Basically I am trying to do it with perl, but if ruby works that would also be fine. thank you
    – LiNi
    Oct 1, 2021 at 9:13

2 Answers 2

1

An awk script like the what's shown below would collect the data from the given file and count how many times each "type" (columns two and on) occurs for each "name" (the first column). It outputs a simple CSV-like format that could be imported into other programs assuming none of the input data contains embedded commas.

{
        genes[$1] = 1
        for (i = 2; i <= NF; ++i) {
                types[$i] = 1
                counts[$1,$i]++
        }
}

END {
        OFS = ","

        $0 = "name"
        for (t in types)
                $(NF+1) = header[++n] = t
        print

        for (g in genes) {
                $0 = g
                for (i = 1; i <= n; ++i)
                        $(NF+1) = counts[g,header[i]]+0
                print
        }
}

The genes and types arrays are associative arrays that contain the names and the types as keys. The counts array counts how many times a specific name and type has occurred as a pair in the input.

The END block creates and outputs the header and then iterates over all gene names and outputs the collected counts for each type.

Testing this:

$ awk -f script file
name,indel,Benign,Likely_pathogenic,snp,Pathogenic
GeneA,2,1,1,3,3
GeneB,1,2,0,2,1
GeneC,0,1,1,2,0
$ awk -f script file | column -t -s,
name   indel  Benign  Likely_pathogenic  snp  Pathogenic
GeneA  2      1       1                  3    3
GeneB  1      2       0                  2    1
GeneC  0      1       1                  2    0
$ awk -f script file | csvlook
| name  | indel | Benign | Likely_pathogenic | snp | Pathogenic |
| ----- | ----- | ------ | ----------------- | --- | ---------- |
| GeneA |     2 |      1 |              True |   3 |          3 |
| GeneB |     1 |      2 |             False |   2 |          1 |
| GeneC |     0 |      1 |              True |   2 |          0 |

(If you don't want csvlook to infer the type from the data, us it with its -I option.)

0
0

As told in the Comment box, Ruby is permitted, I've a standalone solution, it doesn't require piping different programs:

Program

data = <<~'EOF'
    GeneA Pathogenic snp
    GeneA Pathogenic snp
    GeneA Benign indel
    GeneA Likely_pathogenic snp
    GeneA Pathogenic    indel
    GeneB Benign    snp
    GeneB Pathogenic    snp
    GeneB Benign    indel
    GeneC  Benign   snp
    GeneC  Likely_pathogenic    snp
EOF

hash, header1, header2 = {}, [], []

data.each_line { |x|
    fields = x.split("\s")
    field = hash[fields[0].to_sym] ||= [{}, {}]

    field[0].merge!(fields[1] => 1) { |key, val1, val2| val1 + val2 }
    field[1].merge!(fields[2] => 1) { |key, val1, val2| val1 + val2 }
    header1 << fields[1]
    header2 << fields[2]
}

headers = header1.tap(&:uniq!).concat(header2.tap(&:uniq!))
header = '| ' + headers.join(' | ') + ' | '

puts header, ?- * header.length

hash.each { |k, v|
    v0, v1 = v[0], v[1]

    print "| #{k} |"
    headers.each_with_index { |x, i|
        out = "#{(v0[x] || v1[x]).to_i} |"
        print i == 0 ? out.rjust(5) : out.rjust(x.length + 3)
    }
    puts
}

Execute:

$ ruby thisFile.rb

Output:

| Pathogenic | Benign | Likely_pathogenic | snp | indel | 
----------------------------------------------------------
| GeneA |  3 |      1 |                 1 |   3 |     2 |
| GeneB |  1 |      2 |                 0 |   2 |     1 |
| GeneC |  0 |      1 |                 1 |   2 |     0 |

You might yell at those rjust(5), rjust(x.length + 3), because they are hardcoded. Actually those are for the ' | ', which are hardcoded as well. If you add one more space, you add one to the numbers. It won't break if data changes.

Instead of having data <<~EOF..., you can read a file containing the data with data = (ARGV[0] && File.readable?(ARGV[0])) ? IO.read(ARGV[0]) : '' - here data is read from the filename passed as argument. Should work with every data, but the alignment is done on basis of the header. A small modification of this code can align data based on the biggest field. But for now I think this will get your job done.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .