1

Today I found a an interesting question about shell scripting in other site. The question was build a matrix based on the values of another matrix

https://askubuntu.com/questions/884372/count-number-in-two-columns-and-generate-matrix

The original matrix is like this:

joe it 9
wolf it 10
wolf pr 9
mark pm 6
jack pr 20
anton pm 5
joe pm 20
mark sa 35

the output matrix must be like this

0 anton jack joe mark wolf
it 0 0 9 0 10
pm 5 0 20 6 0
pr 0 20 0 0 9
sa 0 0 0 35 0

As you see the second matrix is a summary if the first one

I tried to resolve the problem as a follows:

I saved the first matrix in file called test01. I used a whitespace as separator

Extract the distinct elements from the first column, convert it in a row and save it to a file named new

cut -d ' ' -f1-1 test01 |sort |uniq |awk  '{ printf( "%s ", $1 ); } END { printf( "\n" ); }' > new

Extract the distinct elements the second column and saved in a file named new2

cut -d ' ' -f2-2 test01|sort|uniq > new2

Add a 0 as first element of new and save it as new1

while read line; do echo "0 $line"; done < new > new1

As there are 5 different elements in the file in the first column, 5 zeros were added to each row of the new2 file in order to make the cross comparisons

while read line; do echo "$line 0 0 0 0 0"; done < new2 > new3

And the contents of new3 were added to the end of file new1

while read line; do echo $line |awk '{print $1,$2}'; done < new1

Now the content of new1 is:

0 anton jack joe mark wolf
it 0 0 0 0 0
pm 0 0 0 0 0
pr 0 0 0 0 0
sa 0 0 0 0 0

that's when I got stuck.

I don't know how to browse the matrix in new1 and compare each element with the rows of test01 in order to replace 0's when it's necessary. The final result must be:

0 anton jack joe mark wolf
it 0 0 9 0 10
pm 5 0 20 6 0
pr 0 20 0 0 9
sa 0 0 0 35 0

Maybe there is a more efficient method than the one I have used up until now to get the result without so many intermediate files

I apologize for the length of post

1

A multi-pass method without AWK

This starts by reading the file to extract the row- and column-labels. Then it prints a 0 followed by each column label for the first row.

The loop takes care of the non-label rows. First it prints the row label, then it searches for all entries in the file that match that (row, column) pair. Entries in the third column of this result are summed with dc, in case multiple lines are returned.

One glaring issue with this approach is that the file is read once per entry in the result matrix. So, counting the initial two passes to get the row- and column-labels, your example would be read 22 times!

Call as ./contingency-table input-file:

#!/bin/sh
# file: contingency-table

columns=$(cut -d' ' -f 1 "$1" | sort | uniq)
rows=$(cut -d' ' -f 2 "$1" | sort | uniq)

printf '0'
printf ' %s' ${columns}
printf '\n'

for row in ${rows}; do
  printf "${row} "
  for col in ${columns}; do
    (grep "${col} ${row}" "${1}" \
     | cut -d' ' -f 3            \
     | tr '\n' '+'
     printf '\n')                \
    | sed -e 's/^/0 /'           \
          -e 's/$/pq/'           \
    | dc                         \
    | tr '\n' ' '
  done
  printf '\n'
done

A more efficient method using AWK

#!/usr/bin/awk -f

function max(val1, val2) {
    return ((val1 > val2) ? val1 : val2)
}

BEGIN {
    name_length = 0
    department_length = 0
    # This line influences sorting in GNU awk
    PROCINFO["sorted_in"] = "@ind_str_asc"
}

(!($1 in names)) {
    names[$1]
    name_length = max(length($1), name_length)
}

(!($2 in departments)) {
    departments[$2]
    department_length = max(length($2), department_length)
}

{
    hours[$2, $1] += $3
}

END {
    printf "%" department_length "s", 0
    for (name in names) {
        printf " %" name_length "s", name
    }
    printf "\n"
    for (department in departments) {
        printf "%" department_length "s", department
        for (name in names) {
            printf " %" name_length "d", hours[department, name]
        }
        printf "\n"
    }
}

The begin block sets up some variables and configures GNU awk to sort array traversals. The next two blocks add names and departments as needed, while scanning the input. The third block calculates each running total.

If you don't want the "human-readable" formatting, comment out the …_length = max(… lines.

The END block is where all of the output and formatting happens, by traversing the arrays created earlier. This allows for a single pass over the input file, instead of one per entry in the output table.

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.