1

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'    
4
  • 1
    Please edit your question to explain why what you posted would be the expected output given the input you posted. – Ed Morton May 27 '20 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 May 27 '20 at 14:59
  • Why is the fourth line (A, N, G, L) not part of the output? – nohillside May 27 '20 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 May 27 '20 at 15:23
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 May 27 '20 at 16:10
  • 1
    @AdminBee : Ok ..make sense ...edited the answer. thanks... – Stalin Vignesh Kumar May 27 '20 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.