como calcular a média dos valores em uma coluna considerando as informações de outra coluna?

Question

arquivo 1:

HOGBRM443983         -2522.00  19800826
HOGBRM445985         -2389.00  19801101
HOUSAM1891409        -1153.00  19811228
HOUSAM2004289        -650.00   19860101
HOUSAM2005991        -843.00   19860109
HOCANM388722         -1546.00  19860116
HOUSAM2007297        -1882.00  19860125
HOUSAM2007389        -1074.00  19860128
HOITAM801038516      -691.00   19860128

As colunas 2 e 3 incluem valores e informações de data de nascimento (ano, mês, dia) de cada ID da coluna1, respectivamente. Quero verificar quantos IDs existem dentro de cada ano de nascimento e quais são os valores médios (da segunda coluna) de IDs em diferentes anos. Por exemplo, no arquivo 1 existem 2, 1 e 6 ids nos anos 1980, 1981 e 1986 respectivamente, portanto a saída deve ser:

output:

1980 2 -2455.5
1981 1 -1153.00
1986 6 -114.33

em que a primeira coluna mostra o ano de nascimento, a segunda coluna mostra vários IDs em cada ano e a terceira coluna é a média dos valores dos IDs em diferentes anos.

Considerando que os dados reais são realmente enormes, qualquer sugestão seria apreciada.

    

Answer 1

A resposta do Awk:

awk '{y=substr($3,1,4); c[y]++; s[y]+=$2} END {for (y in c) {print y, c[y], (s[y]/c[y])}}' file.txt

    

Answer 2

Com gnu datamash :

cut -c1-35 infile | datamash -W -g 3 count 3 mean 2

Observe que você precisa processar seus dados primeiro (usei cut como era a escolha óbvia com sua amostra de entrada, mas qualquer ferramenta serve) para remover o mês e o dia da data de nascimento:

HOGBRM443983         -2522.00  1980
HOGBRM445985         -2389.00  1980
HOUSAM1891409        -1153.00  1981
HOUSAM2004289        -650.00   1986
......

e só então canalizá-lo para datamash .
Isso também pressupõe que as 3ª colunas são classificadas por ano (se não estiver classificada use datamash -s -W -g ... )

    

Answer 3

Considere usar um banco de dados real.

Usando uma caixa de areia Postgres configurada em uma VM do Vagrant , fiz isso usando o os seguintes passos:

CREATE TABLE MyData (id text, val float, bday date);
INSERT INTO MyData VALUES
('HOGBRM443983',-2522.00,'1980-08-26'),
('HOGBRM445985',-2389.00,'1980-11-01'),
('HOUSAM1891409',-1153.00,'1981-12-28'),
('HOUSAM2004289',-650.00,'1986-01-01'),
('HOUSAM2005991',-843.00,'1986-01-09'),
('HOCANM388722',-1546.00,'1986-01-16'),
('HOUSAM2007297',-1882.00,'1986-01-25'),
('HOUSAM2007389',-1074.00,'1986-01-28'),
('HOITAM801038516',-691.00,'1986-01-28')
;
SELECT
  extract(year FROM bday) AS yr,
  count(id) AS count,
  avg(val) AS average
FROM mydata GROUP BY yr;

A saída é:

  yr  | count |      average      
------+-------+-------------------
 1981 |     1 |             -1153
 1980 |     2 |           -2455.5
 1986 |     6 | -1114.33333333333
(3 rows)

Você provavelmente poderia lidar com isso com processamento de texto, mas você menciona que os dados são ENORMES, e um banco de dados real é projetado para esse tipo de computação. (E a postagem do blog com a qual eu fiz o link tem todas as etapas para configurar um sandbox do Postgres.)

    

Answer 4

A Miller foi criada para resolver problemas como estes:

$ cat hogbrm.txt | \
  mlr --nidx --repifs put '$3=sub(string($3),"(....).*", "")' \
  then stats1 -a count,mean -f 2 -g 3
1980 2 -2455.500000
1981 1 -1153.000000
1986 6 -1114.333333

Contexto:

• Use --nidx , pois não há cabeçalho, apenas colunas indexadas posicionalmente
• Use --repifs , pois vários espaços separam colunas
• Use sub para descartar os últimos quatro dígitos da data (coluna 3)
• Use stats1 para calcular a contagem e a média da coluna 2 agrupadas pela coluna 3