2

I'm trying to get the total (sum of) counts of each uniq string in every column, with output in corresponding column order.

I need this in a powerful awk command, as the varying full input is usually thousands of rows and columns.

I've tried to do this myself and haven't had any luck. I think I'm close-ish, here's where I got to with the code, though it obviously doesn't work:

awk -F ',' '{ for(N=1; N<=NF; N++) {{count[$N]++} END {for (word in count) print word, count[word]}}}'

My thinking for the above code was that I could get the desired output for a single specified column, for now lets say column 2, if i ran:

awk '{count[$2]++} END {for (word in count) print word, count[word]}'

However I need that type of output for every column. So I attempted to loop through the columns to achieve this, but it failed miserably :(

Here's some example data:

Example input:

M,M,M,M
N,N,N,N    
A,M,G,L
P,P,P,P
A,N,G,L    
P,N,P,L
A,A,A,A
C,C,C,C
A,M,G,C
L,L,L,L
G,G,G,G

Corresponding desired output:

M 1,M 2,M 1,M 1
N 1,N 2,N 1,N 1
A 3,A 1,A 1,A 1
P 1,P 1,P 1,P 1
L 1,L 1,L 1,L 3
G 1,G 1,G 3,G 1
C 1,C 1,C 1,C 1

To explain the output, the first column in example input has 3 A's, and all other letter only have 1 each, so the output for that column is:
M 1
N 1
A 3
P 1
L 1
G 1
C 1

I wrote this code and it would work, but ideally I would like to achieve it within the awk command:

for i in $(seq $NumberOfColumns);do
ColumnOutput=$(awk -F ',' -v x=$i '{count[$x]++} END {for (word in count) print word, count[word]}' file)
TotalOutput=$(paste <(echo "$TotalOutput") <(echo "$ColumnOutput") -d ,)
done    
echo "$TotalOutput" | sed 's/^,//g'    
5
  • 1
    Please edit your question to explain why what you posted would be the expected output given the input you posted.
    – Ed Morton
    Commented May 27, 2020 at 13:42
  • What defines the position of the line breaks in the output, what defines the order? Could they also be sorted alphabetically?
    – nohillside
    Commented May 27, 2020 at 14:59
  • Why is the fourth line (A, N, G, L) not part of the output?
    – nohillside
    Commented May 27, 2020 at 15:18
  • I think by trying to keep my question short I left out too much of the details which has led to some confusion. I've edited the question now with every detail I can give. Hopefully this helps
    – Giles
    Commented May 27, 2020 at 15:23
  • It seems the sort order of the output is somewhat arbitrary? Commented Jan 14 at 2:43

2 Answers 2

2

I think this will help :

$ awk -F"," '              
    NR==FNR { for (i=1;i<=NF;i++) {a[i,$i]+=1;b[$i]=$i} next }
    { for (i=1;i<=NF;i++)if(b[$1]) printf "%s %s,",$1,a[i,$1];else next; print ""; delete b[$1] }
' file file
M 1,M 3,M 1,M 1,
N 1,N 3,N 1,N 1,
A 4,A 1,A 1,A 1,
P 2,P 1,P 2,P 1,
C 1,C 1,C 1,C 2,
L 1,L 1,L 1,L 3,
G 1,G 1,G 4,G 1,
2
  • It shouldn't be necessary to "manually" combine indices of a "pseudo"-multidimensional array into one string, like a[i$i]. If you use a[i,$i], awk will perform such a combination by using a non-printable character that is very unlikely to ever occur in the text (see the SUBSEP internal variable), which should be safer to use.
    – AdminBee
    Commented May 27, 2020 at 16:10
  • 1
    @AdminBee : Ok ..make sense ...edited the answer. thanks... Commented May 27, 2020 at 16:27
0

Using Raku (formerly known as Perl_6)

~$ raku -e 'my @a = [Z] lines.map: *.split(","); my @b; \ 
            for ^@a -> $row  {                          \
                for ^$row.elems -> $col {    my %h;     \
                    %h{$_}++ for @a[$row]>>.[$col];     \
                    @b.push: %h.sort }};                \ 
            for ^@b>>.elems.max -> $j {                 \
                put @b.map({ $_.[$j] // (" " => "0") }).join: "," };'  file

OR (more simply):

~$ raku -e 'my @a = [Z] lines.map: *.split(","); my @b; \
            for ^@a -> $row {                           \
                my %h is BagHash = @a[$row];            \ 
                @b.push: %h.sort };                     \ 
            for ^@b>>.elems.max -> $j {                 \
                put @b.map({ $_.[$j] // (" " => "0") }).join: "," };'  file

Here are answers coded in Raku, a member of the Perl-family of programming languages. Raku features high-level support for Unicode, built-in. The code above takes advantage of Perl-family features such as @-sigiled arrays and %-sigiled hashes (i.e. dictionaries/key-value pairs).

  • First, data is read in line-by-line using lines, which have each been split on commas. To interchange rows and columns the [Z] operator is used, and the data is stored in @a array.

  • Next the @a array is iterated through, first by $row and then by $col, so that for each $row the cell "key" ($_) is stored in the %h hash (first answer) or BagHash (second answer). When each row has been analyzed, the key/value data is stored in @b array.

  • Finally the max number of key/values (via elems) is determined, and data output, taking care to insert 0 zeros where a key might be undefined for a particular column (the OP could use "Nil" here, instead of " "):

Sample Input (note unequal # of keys in columns):

M,M,M,M
N,N,N,N
A,M,G,L
P,P,P,P
A,N,G,L
P,N,P,L
A,A,A,A
C,C,C,C
A,M,G,X
L,L,L,L
G,G,G,G

Sample Output (key/value pairs are \t tab-separated):

A  4,A  1,A  1,A  1
C  1,C  1,C  1,C  1
G  1,G  1,G  4,G  1
L  1,L  1,L  1,L  4
M  1,M  3,M  1,M  1
N  1,N  3,N  1,N  1
P  2,P  1,P  2,P  1
   0,   0,   0,X  1

https://docs.raku.org/type/BagHash
https://docs.raku.org
https://raku.org

You must log in to answer this question.

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