Extract the exact number of elements from the same identifier

Question

I have a file that looks like this:

Id       Chr     Start   End    
Prom_1   chr1    3978952 3978953  
Prom_1   chr1    3979165 3979166  
Prom_1   chr1    3979192 3979193  
Prom_2   chr1    4379047 4379048  
Prom_2   chr1    4379091 4379092  
Prom_2   chr1    4379345 4379346  
Prom_2   chr1    4379621 4379622  
Prom_3   chr1    5184469 5184470  
Prom_3   chr1    5184495 5184496  

and I would like to count how many times the same identifier appear. Something like:

Prom_1  3  
Prom_2  4  
Prom_3  2  

Any idea is really appreciate.

Answer 1

With GNU datamash

$ datamash -W --header-in groupby 1 count 2 < file
Prom_1  3
Prom_2  4
Prom_3  2

Answer 2

You can count the identifiers with uniq:

tail -n +2 input | cut -d' ' -f1 | sort | uniq -c

Note that uniq expects sorted input. We use tail to skip the header and cut to 'cut-out' the first column.

Example output:

  3 Prom_1
  4 Prom_2
  2 Prom_3

In case an id with a smaller number should be printed before an id with a larger number (e.g. Prom_3 before Prom_10) you can replace sort with sort -V (version sort):

tail -n +2 input2 | cut -d' ' -f1 | sort -V | uniq -c

Example output (input2 contains an extra line for id Prom_10):

  3 Prom_1
  4 Prom_2
  2 Prom_3
  1 Prom_10

Answer 3

You can use awk

awk 'NR>1 {a[$1]++} END { for (x in a) { print x,a[x] } } ' file

The NR>1 will ignore the header
a[$1]++ is a hash iterator

Answer 4

Simple way with cat, cut , sort and uniq is :

sed -n '1,$p' input | cut -d' ' -f1 | sort | uniq -c

Answer 5

Something similar to user3589054:

since there are a lot of Id, when you sort you will not have the same order of Id because for example the Id starting with Prom_1 Prom_10 etc.. will be as first. So what i did was this and it worked fine:

awk 'NR>1 {a[$1]++} END { for (x in a) { print x,a[x] } } ' file | awk -F "_" '{print $1"\t"$2"\t"}' | cut -f 2 | sort -n | awk -F " " '{print $1"\t"$2}' | sed 's/^/Prom_/' > file.output.txt

where:
awk 'NR>1 {a[$1]++} END { for (x in a) { print x,a[x] } } ' file |
you will have the output with the Id and number but not sorted

awk -F "_" '{print $1"\t"$2"\t"}' | you will split the file in Prom , number, its Id number and its quantity

cut -f 2 | sort -n |
sort according the number Id and its correspondent amount

awk -F " " '{print $1"\t"$2}' |
here you tab the two columns

sed 's/^/Prom_/'
and finally you attach the Prom_ in front of its number Id