count, groupby with sed, or awk

Question

i want to perform two different sort and count on a file, based on each line's content. 1. i need to take the first column of a .tsv file i would like to group by each line that starts with three digits, and keep only the three first digits, and for everything else, just sort and count the whole occurrence of the sentence in the first column.

Sample data:

687/878 9
890987  4
01a 55
1b  8743917
890a    34
abcdee  987
dfeqfe  fkdjald
890897  34213
6878853 834
32fasd  53891
abcdee  8794371
abd 873

result:

687 2
890 3
01a 1
1b  1
32fasd  1
abd 1
dfeqfe  1
abcdee  2

I would also appreciate a solution that would

also take into account a sample input like

687/878 9
890987  4
01a     55
1b      8743917
890a    34
abcdee  987
dfeqfe  545
890897  34213
6878853 834
(632)fasd  53891
(88)abcdee  8794371
abd     873

so the first column may have values like (,), #, ', all kind of characters

so output will have two columns, the first with the values extracted, and the second with the new count, with the new values extracted from the source file.

Again preferred output format tsv.

so i need to extract all values that start with ^\d\d\d, and then for these three first digits, sort and count unique values,

but in a second pass, also do the same for each line, that does not start with 3 digits, but this time, keep the whole columns value and sort count by it.

what i have tried: | sort | uniq -c | sort -nr for the lines that do start with ^\d\d\d, and

the same for those that do not fulfill the above regex, but is there a more elegant way using either sed or awk?

Answer 1

$ cat tst.awk
BEGIN { FS=OFS="\t" }
{ cnt[/^[0-9]{3}/ ? substr($1,1,3) : $1]++ }
END {
    for (key in cnt) {
        print (key !~ /^[0-9]{3}/), cnt[key], key, cnt[key]
    }
}

$ awk -f tst.awk file | sort -k1,2n | cut -f3-
687     1
890     2
abcdee  1

Answer 2

You can try Perl

$ cat nefijaka.txt
687     878     9
890987  4
890a    34
abcdee  987
$ perl -lne  ' /^(\d{3})|(\S+)/; $x=$1?$1:$2; $kv{$x}++; END { print "$_\t$kv{$_}" for (sort keys %kv) } ' nefijaka.txt
687     1
890     2
abcdee  1
$

You can pipe it to sort and get the values sorted..

$ perl -lne  ' /^(\d{3})|(\S+)/; $x=$1?$1:$2; $kv{$x}++; END { print "$_\t$kv{$_}" for (sort keys %kv) } ' nefijaka.txt | sort -k2 -nr
890     2
abcdee  1
687     1

EDIT1:

$ cat nefijaka.txt2
687     878     9
890987  4
890a    34
abcdee  987
a word and then 23
$ perl -lne  ' /^(\d{3})|(.+?\t)/; $x=$1?$1:$2; $x=~s/\t//g; $kv{$x}++; END { print "$_\t$kv{$_}" for (sort keys %kv) } ' nefijaka.txt2
687     1
890     2
a word and then 1
abcdee  1
$