How to loop an awk command on every column of a table and output to a single output file?

Question

I have a multi column file composed of single unit 1s, 2s and 3s. There are a lot of repeats of a unit in each column, and sometimes it switches from one to another. I want to count how many times this switch happens on every column. For example in column 1 the switch change from 1 to 2 to 3 to 1, so there are 3 switches and the output should be 3. In the second column there are 2s the entire column, so the changes is 0 and the output is 0.

My input file has 4000 columns so it is impossible to do it by hand. The file is space separated.

For example:

Input:

1 2 3 1 2 
1 2 2 1 3
1 2 3 1 2
2 2 2 1 2
2 2 2 1 2    ......
3 2 2 1 2 
3 2 2 1 1
1 2 2 1 1
1 2 2 1 2
1 2 2 1 1

Desired output:

3    ## column 1 switch times
0    ## column 2 switch times
3    .....
0    
5    

I was using:

awk '{print $1}' <inputfile> | uniq | wc -l
awk '{print $2}' <inputfile> | uniq | wc -l
awk '{print $3}' <inputfile> | uniq | wc -l
....

This execute one column at a time. It will give me the output "4" for the first column, later I will just calculate 4-1 =3 to get my desired output. But Is there a way I can write this awk command into a loop and execute it on each column and output to one file?

Thanks!

Answer 1

awk tells you how many fields are in a given row in the variable NF, so you can create two arrays to keep track of the information you need. One array will keep the value of the last row in the given column. The other will count the number of switches in a given column. You'll also keep a track of the maximum number of columns (and set the counts for new columns to zero so that they are printed appropriately in the output at the end if the number of switches is 0 for that column). You'll also make sure you don't count the transition from an empty string to a non-empty string — which happens when the column is encountered for the first time.

If, in fact, the file is uniformly the same number of columns, that will only affect the first row of data. If subsequent rows actually have more columns than the first line, then it adds them. If a column stops appearing for a bit, I've assumed it should resume where it left off (as if the missing columns were the same value as before). You can decide on different algorithms; that could count as two transitions (from number to blank and from blank to number too. If that's the case, you have to modify the counting code. Or, perhaps more sensibly, you could decide that irregular numbers of columns are simply not allowed, in which case you can bail out early if the number of columns in the current row is not the same as in the previous row (beware blank lines, or are they outlawed too?).

And you won't try writing the whole program on one line because it will be incomprehensible and it really isn't necessary.

awk '{   if (NF > maxNF)
         {
             for (i = maxNF + 1; i <= NF; i++)
                 count[i] = 0;
             maxNF = NF;
         }
         for (i = 1; i <= NF; i++)
         {
             if (col[i] != "" && $i != col[i])
                 count[i]++;
             col[i] = $i;
         }
     }
     END {
         for (i = 1; i <= maxNF; i++)
             print count[i];
     }' data-file-with-4000-columns

Given your sample data (with the dots removed), the output from the script is as requested:

3
0
3
0
5

This alternative data file with jagged rows:

1 2 3 1 2
1 2 2 1 3
1 2 3 1 2
2 2 2 1 2
2 2 2 1 2 1 1 1
3 2 2 1 2 2 1
3 2 2 1 1
1 2 2 1 1 2 2 1
1 2 2 1
1 2 2 1 1 3

produces the output:

3
0
3
0
3
2
1
0

Which is correct according to the rules I formulated — but if you decide you want different rules to cover the data, you can end up with different answers.

If you used printf("%d\n", count[i]); in the final loop, you'd not need to set the count values to zero in a loop. You pays your money and takes your pick.