Transpose a file, and replace missing values

Question

I have Readings for Names, from Machines and sometimes these readings are replicated.

Where a reading was not found, it is left as blank.

Name Instrument Rep R1 R2 R3 
N1 I1 1 1 2 3 
N2 I1 1 1 3 4
N1 I1 2 2 3 4
N3 I1 2 3 4 5
N1 I2 1 1 2 3 
N2 I2 1 1 3 4
N2 I2 2 2 3 4
N3 I2 1 3 4 5
N1 I3 1 1   4  
N2 I3 1 2 5   
N3 I3 1   6 
N3 I3 2     1

First, I want to consolidate the replicates by using their mean (per name per location). Then, I want to transpose this data and replace the missing values with a dot (.) .

What I want in my output is

Reading Instrument N1 N2 N3
R1 I1 1.5 1 3
R2 I1 2.5 3 4
R3 I1 3.5 4 5
R1 I2 1 1.5 3  
R2 I2 2 3 4
R3 I2 3 4 5
R1 I3 1 2 .
R2 I3 . 5 6
R3 I3 4 . 1

Please note that the number of Names and Readings are quite variable, in some files I have 134 readings, some others have 28 etc., but the readings always start from col3.

This is what I tried unsuccessfully for a test run for only one column

awk '
    NR>1{
        arr[$1" "$2" "$3]   += $4
        count[$1" "$2" "$3] += 1
    }
    END{
        for (a in arr) {
            print a, arr[a] / count[a]
        }
    }
' file |  awk '
NR == 1 {
    n = NF
    for (i = 1; i <= NF; i++)
        row[i] = $i
    next
}
{
    if (NF > n)
        n = NF
    for (i = 1; i <= NF; i++)
        row[i] = row[i] " " $i
}
END {
    for (i = 1; i <= n; i++)
        print row[i]
}' 

Answer 1

If you really want to do this in plain sed/awk, it is indeed possible:

As mentioned by Joe, using SPACE as field separator & data value is a problem in awk.

That is why I suggest using sed to re-format the data first:

sed 's/ *$//' removes SPACEs in the end of the line (all but the first of your input lines end in SPACE, so this standardizes the input & removes potential missing values in the end of each line).

Next, sed 's/ / . /g/' inserts a . between each pair of adjacent SPACEs (filling in potential missing values that are not in the end of a line).

Since this will insert addional SPACEs in case of adjacient missing values, sed 's/ / /g' has to be used to remove those again.

Then, awk can use the first (i.e. header) line to learn the names & number of the readings, add potential missing values in the end of each line (all the others had already been taken care of by sed), sum & count all readings keeping track of the corresponding name & instrument, and output the means (if any) in the desired orientation/order:

sed -e 's/ *$//' -e 's/  / . /g' -e 's/  / /g' <<< 'Name Instrument Rep R1 R2 R3
N1 I1 1 1 2 3
N2 I1 1 1 3 4
N1 I1 2 2 3 4
N3 I1 2 3 4 5
N1 I2 1 1 2 3
N2 I2 1 1 3 4
N2 I2 2 2 3 4
N3 I2 1 3 4 5
N1 I3 1 1   4
N2 I3 1 2 5
N3 I3 1   6
N3 I3 2     1' | awk '

# get number of readings/fields
NR==1{for(i=4;i<=NF;++i)readings[i-4]=$i;fields=NF;next}

# add missing fields in the end
{for(i=NF+1;i<=fields;++i)$i="."}

# keep track of names & instruments
names[$1];instruments[$2]

# sum & count readings per name/instrument (ignoring missing ["."] values)
{for(i=4;i<=NF;++i)if($i!="."){sum[readings[i-4] FS $2 FS $1]+=$i;++count[readings[i-4] FS $2 FS $1]}}

# after reading all data:
END{

  # print header
  printf "Reading"FS"Instrument";for(name in names)printf FS name;print ""

  # sort output rows by instrument
  for(instrument in instruments){

    # keep order of readings
    for(i=0;i<length(readings);++i){

      # print first two columns
      printf readings[i] FS instrument

      # remaining columns (i.e. names):
      for(name in names){

        # if data available:
        if(count[readings[i] FS instrument FS name]){

          # print average
          printf FS sum[readings[i] FS instrument FS name]/count[readings[i] FS instrument FS name]

        # otherwise:
        }else{

          # print missing value ["."]
          printf FS "."
        }

      # proceed with next row
      }print ""
    }
  }
}
'

Note: In my opinion, using FS as separator in multi-dimensional array indices is the best choice in most cases, since all the fields are guaranteed not to contain it (in case you have to iterate over the array & split the 'dimensions' of the indices). Though this is not needed here, I made it a habit.

edit: Joe pointed out that the way names/instruments were kept track of in a previous version of this answer could use some extra explaining. That inspired the simplified version used above: Unlike k in a that checks for the existence of key k in array a without creating such an entry, a[k] will assign an empty value to that entry (& return it).

For me, the above code produces the output you asked for:

Reading Instrument N1 N2 N3
R1 I1 1.5 1 3
R2 I1 2.5 3 4
R3 I1 3.5 4 5
R1 I2 1 1.5 3
R2 I2 2 3 4
R3 I2 3 4 5
R1 I3 1 2 .
R2 I3 . 5 6
R3 I3 4 . 1

Note: The <<< syntax I use is a HERE-STRING and might not work in all shells (bash supports it, though). Just pass your input file path to the sed and it should work in all shells (as far as I know).

Note: This will only work if all your data fit in memory. If that's not the case, there should be a less memory-intense solution to summarize the data based on sorting the input first. Transposing the matrix might be more tricky in that case.

edit:

Note: My output does not contain any SPACE at the end of any line, unlike your example output since I was not able to figure out, when you put a SPACE and when you don't. If this has any meaning, please adjust the question and I'll update the answer accordingly. Otherwise, consider removing those SPACEs from your expected output.

Answer 2

Here are the immediate problems:

1) You can't use a blank as a field separator and as a value at the same time. If your values are fixed length (one column each), then you can use that to your advantage. If you could just set missing values to zero, that would make it easier, but in things like this, missing usually really means missing - exclude that item from further processing.

To start on this approach, you have $0 which contains the whole input line. You can get to the readings using substr($0, offset, 1) where offset is 7, 9,11, or 13 (I forget if indexing starts at 0 or 1. If it's 0, then subtract 1 from each of the offsets).

If it helps the rest of your logic, you can replace the blank missing readings with something like an M as a placeholder. Otherwise, multiple blanks are the same as one and any fields following a blank one will effectively be left shifted into a lower field number.

It's even easier if missing is the same as zero. You just replace the offending blank with a zero, but that will mess up all your calculations if missing is not the same as zero.

You could use gsub to replace all occurrences of two consecutive blanks followed by either a third blank or the end of line with a " M " or a " 0 ".

In your current 1st awk, you would have to test for missing before incrementing and summing.

2) In your second awk, NF would also potentially be too small if there are any blank missing values - throwing off everything else.

I think I understand what your first awk does, but I have no idea what you're trying to accomplish with the second one.

3) You may be forced to use a dot for a missing value to appease some other program you're going to feed this output into, but in general, it's a bad idea because it looks like a decimal point (which is legal in your data) and might be interpreted by some software as a zero value or generally make other parsing trickier to do.