CODEWITHSUNDEEP
pythonawk
user2464553
user2464553

Reputation: 85

clustering or grouping help in awk

I have a file:

1 Chr1       100820415  
1 Chr1       100821817  
1 Chr1       100821818  
1 Chr1       100823536  
1 Chr1       100824427  
2 Chr1       100824427  
2 Chr1       100824427  
1 Chr1       100824428

I am trying to add column 1 values if all Column 2 is the same and column 3 values are the same. It is sort of like 'clustering'.

So the output should be:

1 Chr1       100820415  
1 Chr1       100821817  
1 Chr1       100821818  
1 Chr1       100823536  
5 Chr1       100824427
1 Chr1       100824428

I am new to awk and trying to understand the language however I am not able to say write a script that if $2 is same then add $1 and if $2 is same then add $3 values (if $3 values are same).

Here's what I have tried so far:

awk 'BEGIN{ x+=$1 } END {print x} if NF == $2' file_name

Solution can be either in awk or python.

Upvotes: 3

Views: 288

Answers (4)

Chris Seymour
Chris Seymour

Reputation: 85795

One way with awk:

$ awk '{a[$2 OFS $3]+=$1}END{for(k in a)print a[k],k}' file
1 Chr1 100821817
1 Chr1 100821818
1 Chr1 100820415
5 Chr1 100824427
1 Chr1 100824428
1 Chr1 100823536

One way with python:

$ cat cluster.py
#!/usr/bin/env python
import fileinput

cluster = {}

for line in fileinput.input():
    field = line.strip().split()
    try:
        cluster[' '.join(field[1:])] += int(field[0])
    except KeyError:
        cluster[' '.join(field[1:])] = int(field[0])

for key, value in cluster.items():
    print value, key

Make the script executable chmod +x cluster.py and run like:

$ ./cluster.py file
1 Chr1 100823536
1 Chr1 100821817
1 Chr1 100820415
5 Chr1 100824427
1 Chr1 100824428
1 Chr1 100821818

Both methods use the same technique here by taking advantage of a hash tables. With awk we use an associative array and python a dictionary. Simply put both are arrays where the keys are not numerical but are strings (the second and third column value). A simple example:

blue 1
blue 2
red  5
blue 1
red  2

If we say awk '{a[$1]+=$2}' file then we get the following:

Line    Array       Value  Explanation
1       a["blue"]   1      # Entry in 'a' is created with key $1 and value $2
2       a["blue"]   3      # Add $2 on line 2 to a["blue"] so the new value is 3
3       a["blue"]   3      # The key $1 is red so a["blue"] does not change
        a["red"]    5      # Entry in 'a' is created with new key "red"
4       a["blue"]   4      # Key "blue", Value 1, 1 + 3 = 4
        a["red"]    5      # Key "blue", so a["red"] doesn't change
5       a["blue"]   4      # Key "red", so a["blue"] doesn't change
        a["red"]    7      # Key "red", Value 2, 5 + 2 = 7

Upvotes: 3

user278064
user278064

Reputation: 10170

Exactly what you want:

import re
from collections import defaultdict

d = defaultdict(int)

with open('file.txt') as f:
    for line in f:
        qty, chr, _id = re.split('\s+', line.strip())
        d[(_id, chr)] += int(qty)

for (_id, chr), qty in d.iteritems():
    print '{} {}       {}'.format(qty, chr, _id)

Upvotes: 1

jwd
jwd

Reputation: 11114

Here's a Python version.

It reads input from stdin.

Note: it assumes the second column is always Chr1, and keeps output sorted by the last column's value - it will not preserve the input's ordering.

#!/usr/bin/env python2.7
import sys

# Maps a 'value' to its count
counter = {}

for line in sys.stdin:
    num, tag, value = line.split()
    num = int(num)
    counter[value] = counter.setdefault(value, 0) + num

for value in sorted(counter.keys()):
    print counter[value], 'Chr1', value

Upvotes: 0

twalberg
twalberg

Reputation: 62389

Something like this should work:

awk '{t1[$2$3] = $2; t2[$2$3] = $3; sums[$2$3] += $1}END{for (s in sums) print sums[s], t1[s], t2[s]}' input.txt

Upvotes: 1

Related Questions