Reshape table and complete voids with NA (or -999) using bash

user_3200041 0
Viewed 119

I'm trying to create a table based on the ASCII bellow. What I need is to arrange the numbers from the 2nd column in a matrix. The first and third columns of the ASCII give columns and rows in the new matrix. The new matrix needs to be fully populated, so it is necessary to complete missing positions on the new table with NA (or -999).

This is what I have

$ cat infile.txt

1  68    2
1  182   3
1  797   4
2  4     1
2  70    2
2  339   3
2  1396  4
3  12    1
3  355   3
3  1854  4
4  7     1
4  85    2
4  333   3
5  9     1
5  68    2
5  182   3
5  922   4
6  10    1
6  70    2

and what I would like to have:

NA   4     12    7    9    10
68   70    NA    85   68   70
182  339   355   333  182  NA
797  1396  1854  NA   922  NA

I can only use standard UNIX commands (e.g. awk, sed, grep, etc).

So What I have so far...

I can mimic a 2d array in bash

irows=(`awk '{print $1 }'  infile.txt`) # rows positions 
jcols=(`awk '{print $3 }'  infile.txt`) # columns positions
values=(`awk '{print $2 }' infile.txt`) # values

declare -A matrix                                         # the new matrix
nrows=(`sort -k3 -n in.txt | tail -1 | awk '{print $3}'`) # numbers of rows
ncols=(`sort -k1 -n in.txt | tail -1 | awk '{print $1}'`) # numbers of columns
nelem=(`echo "${#values[@]}"`)                            # number of elements I want to pass to the new matrix

# Creating a matrix (i,j) with -999
for ((i=0;i<=$((nrows-1));i++)) do
    for ((j=0;j<=$((ncols-1));j++)) do
        matrix[$i,$j]=-999
    done
done

and even print on the screen

for ((i=0;i<=$((nrows-1));i++)) do
   for ((j=0;j<=$((ncols-1));j++)) do
      printf " %i" ${matrix[$i,$j]}
   done
   echo 
done

But when I tried to assign the elements, something gets wrong

for ((i=0;i<=$((nelem-1));i++)) do
   matrix[${irows[$i]},${jcols[$i]}]=${values[$i]}
done

Thanks in advance for any help with this, really.

reshape awk bash
3 Answers

A solution in plain bash by simulating a 2D array with an associative array could be something like that (Notice that row and column counts are not hard coded and the code works with any permutation of input lines provided that each line has the format specified in the question):

$ cat printmat

#!/bin/bash

declare -A mat
nrow=0
ncol=0
while read -r col elem row; do
    mat[$row,$col]=$elem
    if ((row > nrow)); then nrow=$row; fi
    if ((col > ncol)); then ncol=$col; fi
done

for ((row = 1; row <= nrow; ++row)); do
    for ((col = 1; col <= ncol; ++col)); do
        elem=${mat[$row,$col]}
        if [[ -z $elem ]]; then elem=NA; fi
        if ((col == ncol)); then elem+=$'\n'; else elem+=$'\t'; fi
        printf "%s" "$elem"
    done
done

$ ./printmat < infile.txt
prints out

NA      4       12      7       9       10
68      70      NA      85      68      70
182     339     355     333     182     NA
797     1396    1854    NA      922     NA
user_138090010

here is one way to get you started. Note that this is not intended to be "the" answer but to encourage you to try to learn the toolkit.

$ join -a1 -e NA -o2.2 <(printf "%s\n" {1..4}"_"{1..6})           \
                       <(awk '{print $3"_"$1,$2}' file | sort -n) | 
  pr -6at

NA          4           12          7           9           10
68          70          NA          85          68          70
182         339         355         333         182         NA
797         1396        1854        NA          922         NA

works, however, row and column counts are hard coded, which is not the proper way to do it.

Preferred solution will be filling up an awk 2D array with the data and print it in matrix form at the end.

user_14358690

Any time you find yourself writing a loop in shell just to manipulate text you have the wrong approcah. See why-is-using-a-shell-loop-to-process-text-considered-bad-practice for many of the reasons why.

Using any awk in any shell on every UNIX box:

$ cat tst.awk
{
    vals[$3,$1] = $2
    numRows = ($3 > numRows ? $3 : numRows)
    numCols = $1
}
END {
    OFS = "\t"
    for (rowNr=1; rowNr<=numRows; rowNr++) {
        for (colNr=1; colNr<=numCols; colNr++) {
            val = ((rowNr,colNr) in vals ? vals[rowNr,colNr] : "NA")
            printf "%s%s", val, (colNr < numCols ? OFS : ORS)
        }
    }
}

.

$ awk -f tst.awk infile.txt
NA      4       12      7       9       10
68      70      NA      85      68      70
182     339     355     333     182     NA
797     1396    1854    NA      922     NA
user_17450010
Related