awk based script to print the "mode(statistics term)" for each column in a data file

Shell Programming and Scripting
1

Hi All,
Thanks all for the continued support so far.
Today, I need to find the most occurring string/number(also called mode in statistics terminology) for each column in a data file (.csv type).
For one column of data(1.txt) like below

Sample
1
2
2
3
4
1
1
1
2

I can find the mode using the command

sort 1.txt |uniq -c |sort |tail -1 |awk '{print $2}'

. The output is 1. ( As '1' appeared more number of times than other numbers)
But if I have multiple columns in a csv file, I am unable to do this task for each column printing the mode side by side. So I need an awk based loop script to fulfill my requirement.
Please let me know if my problem statement is not clear.

Thanks
Sid

2 
cat test_st
1 2 3
2 4 5
2 3 5
3 4 9
4 7 5
1 5 6
1 4 5
1 4 2
2 5 5

script is

for i in {1..3}
do
echo -e "i == $i\n"
cat test_st | awk -v L=$i '{ print $L }' | sort | uniq -c | sort -nrk1 | head -1 | awk '{ print $2 }'
done

output is

i == 1

1
i == 2

4
i == 3

5
3

Hi Pamu,

Thanks for your quick reply.
This script is perfectly fine for all files which have equal number of columns.
My challenge is to find 'mode' for files(.csv type) where
1) I don't know how many columns are there unless I open each file and check(very tedious task)
2) Printing the output side by side like mode_of_col1,mode_of_col2 etc.
For the first one i can count the number of columns in each file and feed into variable and loop through.
For the second one I can use the command

tr '\n' ' '

to transpose the output.

But all these change are making my code very lengthy and also I need to process many files (some times in 1000's).
Thats why I am looking for automatic looping script to print 'mode' for each column as well as looping to random number of columns.
Can you modify your script if you can ?

Thanks
Sid

4

Try...

$ cat 1.txt
Sample
1
2
2
3
4
1
1
1
2

$ awk '{a[$1]++}END{for(i in a)if(a>x)x=a;for(i in a)if(a==x)print i}' 1.txt
1

$

Note that if the mode is not unique, more than one value is returned...

$ echo 2 >> 1.txt

$ awk '{a[$1]++}END{for(i in a)if(a>x)x=a;for(i in a)if(a==x)print i}' 1.txt
1
2

$
5

Hi ks_reddy,

you can do two things here..

1) for getting number of columns you can get the number columns from the file and use this as variable as you have suggested.

2) For getting the output in one line better you redirect the output to one file and then you can perform any operation on it.

3) For many number of files just do this one while loop and redirect the output the file which name is appended by the original file.

and length of code doesn't matter too much if we are getting our work done.. :slight_smile:

6

Try...

$ cat 1.txt
5,3,2,6
4,4,3,2
4,1,2,3
4,1,2,3
3,5,2,3

$ awk ' BEGIN { FS=OFS="," }
      { for (n = 1; n <= NF; n++) { a[n, $n]++ } }
  END { for (n = 1; n <= NF; n++) {
          x = 0
          for (i in a) {
            split(i, s, SUBSEP)
            if (s[1] == n && a > x) {
              x = a
              y = s[2]
            }
          }
          printf y (n==NF ? ORS : OFS)
        }
      } ' 1.txt > 2.txt

$ cat 2.txt
4,1,2,3

$

Note that if the mode is not unique, only one value is returned.

7

Hi Ygor,

Thank you very much. You provided me the exact solution that I am looking for .
This script is taking only 1/10000 part of time that the previous script (which works on individual columns ).

Thanks
Sidda