Transpor um arquivo e substituir valores ausentes

Question

Eu tenho Leituras para Nomes, de Máquinas e, às vezes, essas leituras são replicadas.

Quando uma leitura não foi encontrada, é deixada em branco.

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

Primeiro, quero consolidar as réplicas usando sua média (por nome por local). Então, quero transpor esses dados e substituir os valores ausentes por um ponto ( . ).

O que eu quero na minha saída é

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

Por favor, note que o número de Nomes e Leituras é bastante variável, em alguns arquivos eu tenho 134 leituras, algumas outras têm 28 etc., mas as leituras sempre começam da col3.

Isso é o que eu tentei sem sucesso para uma execução de teste em apenas uma coluna

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

Se você realmente quiser fazer isso em sed / awk , é possível:

Como mencionado por Joe , usando o valor de dados SPACE como separador de campo & é um problema em awk .

É por isso que sugiro usar sed para reformatar os dados primeiro:

sed 's/ *$//' remove SPACE s no final da linha (todas, exceto a primeira das suas linhas de entrada, terminam em SPACE , portanto, isso padroniza a entrada e remove possíveis valores ausentes no final de cada linha) .

Em seguida, sed 's/ / . /g/' insere um . entre cada par de SPACE s adjacentes (preenchendo possíveis valores ausentes que não estão no final de uma linha).

Como isso irá inserir% adicionalSPACE s no caso de valores ausentes adjacentes, sed 's/ / /g' tem que ser usado para removê-los novamente.

Em seguida, awk pode usar a primeira linha (ou seja, cabeçalho) para aprender os nomes & número de leituras, adicione possíveis valores omissos no final de cada linha (todos os outros já foram atendidos por sed ), sum & contabilizar todas as leituras mantendo o nome correspondente & instrumento, e produzir os meios (se houver) na orientação / ordem desejada:

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 ""
    }
  }
}
'

Observação: na minha opinião, usar FS como separador em índices de matriz multidimensional é a melhor escolha na maioria dos casos, pois todos os campos têm a garantia de não contê-lo (no caso de você precisar iterar sobre o array & ; dividir as 'dimensões' dos índices). Embora isso não seja necessário aqui, eu criei um hábito.

editar : Joe apontou que a forma como os nomes / instrumentos eram mantidos em uma versão anterior desta resposta poderia usar algumas explicações adicionais. Isso inspirou a versão simplificada usada acima: Ao contrário de k in a que verifica a existência da chave k na matriz a sem criar essa entrada, a[k] irá atribuir em> um valor vazio para essa entrada (e retorná-lo).

Para mim, o código acima produz a saída que você solicitou:

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

Observação: a sintaxe <<< que uso é uma HERE-STRING e pode não funcionar em todos os shells (no entanto, bash oferece suporte a ela). Basta passar o caminho do arquivo de entrada para o sed e ele deve funcionar em todos os shells (até onde eu sei).

Nota: Isso só funcionará se todos os seus dados couberem na memória. Se esse não for o caso, deve haver uma solução com menos intensidade de memória para resumir os dados com base na classificação da entrada primeiro. Transpor a matriz pode ser mais complicado nesse caso.

editar:

Nota: Minha saída não contém SPACE no final de qualquer linha, ao contrário do resultado do seu exemplo, já que não consegui descobrir, quando você coloca um SPACE e quando não o faz. Se isso tiver algum significado, ajuste a pergunta e atualizarei a resposta de acordo. Caso contrário, considere remover os SPACE s da sua saída esperada.

    

Answer 2

Aqui estão os problemas imediatos:

1) Você não pode usar um espaço em branco como um separador de campo e como um valor ao mesmo tempo. Se os seus valores forem de comprimento fixo (uma coluna cada), você poderá usar isso para sua vantagem. Se você pudesse definir os valores ausentes para zero, isso tornaria isso mais fácil, mas, em coisas como essa, a falta geralmente significa falta - exclua esse item do processamento posterior.

Para começar nesta abordagem, você tem $ 0 que contém toda a linha de entrada. Você pode obter as leituras usando substr ($ 0, offset, 1) onde offset é 7, 9, 11 ou 13 (esqueço se a indexação começa em 0 ou 1. Se for 0, subtraia 1 de cada um dos deslocamentos) .

Se isso ajudar o resto da sua lógica, você pode substituir as leituras em branco ausentes por algo como M como espaço reservado. Caso contrário, múltiplos espaços em branco são os mesmos que um e quaisquer campos após um em branco serão efetivamente deixados em um número de campo mais baixo.

É ainda mais fácil se a falta for igual a zero. Você simplesmente substitui o espaço em branco com um zero, mas isso estragará todos os seus cálculos, se a falta não for igual a zero.

Você poderia usar o gsub para substituir todas as ocorrências de dois espaços em branco consecutivos seguidos por um terceiro espaço em branco ou o fim de linha com um "M" ou um "0".

No seu primeiro awk atual, você teria que testar se faltava antes de incrementar e somar.

2) Em seu segundo awk, NF também seria potencialmente pequeno demais se houvesse algum valor faltando em branco - descartando todo o resto.

Acho que entendi o que seu primeiro awk faz, mas não tenho ideia do que você está tentando realizar com o segundo.

3) Você pode ser forçado a usar um ponto para um valor faltante para apaziguar algum outro programa no qual você vai alimentar essa saída, mas, em geral, é uma má ideia porque se parece com um ponto decimal (que é é legal em seus dados) e pode ser interpretado por algum software como um valor zero ou, geralmente, tornar a análise mais difícil.