Mesclar vários arquivos CSV com coluna de chave parcialmente correspondente

2

Eu tenho 100% de arquivoscsv, cada um contendo 2 colunas. O primeiro é taxonomy e o segundo counts . Cada arquivo tem cerca de 10.000 linhas. Os dados taxonomy em cada arquivo são compartilhados apenas parcialmente com um total de cerca de 50000 valores exclusivos. Eu preciso mesclar estes em uma tabela onde faltando taxa em um arquivo são atribuídos o valor 0 . O resultado deve ser uma tabela ( csv ou tsv ) com 50000 linhas e 101 colunas. Um exemplo simplificado seria: Arquivo 1 ( R1.csv ):

A,1
B,20
C,30

Arquivo 2 ( R2.csv ):

C,1
D,13
E,15
F,19

Arquivo 3 ( R3.csv ):

A,1
B,4
E,2
G,6
H,8

Resultado esperado:

Taxa,R1,R2,R3
A,1,0,1
B,20,0,4
C,30,1,0
D,0,13,0
E,0,15,2
F,0,19,0
G,0,0,6
H,0,0,8

Alguma ideia de como fazer isso com o script bash?

    
por Mina Bizic 11.04.2018 / 15:51

3 respostas

1

isso vai ser doloroso

 join -t, -j1 -a1 -e0 -o auto r1.csv r2.csv > r12a.csv
 join -t, -j1 -a2 -e0 -o auto r1.csv r2.csv > r12b.csv
 sort -u r12?.csv > r12.csv
 join -t, -j1 -a1 -e0 -o auto r12.csv r3.csv > r123a.csv
 join -t, -j1 -a2 -e0 -o auto r12.csv r3.csv > r123b.csv
 sort -u r123{a,b}.csv
  • a primeira junção imprime o valor não-pareado no arquivo x ( -ax ) com um valor predeterminado ( -e0 ), -o auto indica união para imprimir 0
  • sort -u sort e manter registro único.

Eu não tenho certeza se o código awk seria mais legível.

    
por 11.04.2018 / 16:53
1

Eu usei awk , que manipula vários arquivos de uma só vez:

sed 's/,R[1-9]\+\.csv:/,/g' <(awk -v HEADER="$(printf ",%s:" R{1..3}.csv)" -F, '
    { seen[$1]=seen[$1]","FILENAME":"$2; }
    END { print HEADER; for (x in seen) print x seen[x]}' R{1..3}.csv \
|awk -F, 'NR==1{split($0,arr,/,/);next} {SEP=""; fld=1;
    for (x in arr){printf ($0 ~ arr[x])?SEP""$(fld++):",0";SEP=","};print ""}')

A saída:

A,1,0,1
B,20,0,4
C,30,1,0
D,0,13,0
E,0,15,2
F,0,19,0
G,0,0,6
H,0,0,8

Quebra de código:

awk -F, '{ seen[$1]=seen[$1]","FILENAME":"$2; }
    END{ print HEADER; for (x in seen) print x seen[x] }' R{1..3}.csv

A parte principal do código que une toda a segunda coluna em todos os arquivos em uma impressão com o valor pertence a qual arquivo tem a mesma primeira coluna. O seen aqui é um nome de matriz com a chave como primeira coluna e valor com ,FILENAME:$2 no modo de anexação.

Em seen[$1]=seen[$1]","FILENAME":"$2; significa imprimir uma vírgula , seguido pelo arquivo de processamento atual FILENAME com awk , dois pontos : seguido pelo segundo valor da coluna $2 quando tiver o mesmo primeiro coluna seen[$1]=... e anexar no mesmo índice de chaves =seen[$1]... e salvar no mesmo valor da chave.

A instrução END , awk executando este bloco no final quando todos os registros / linha foram lidos, e usamos um loop for para iterar o array visto e imprima a chave primeiro e o valor da chave na próxima.

resultará:

A,R1.csv:1,R3.csv:1
B,R1.csv:20,R3.csv:4
C,R1.csv:30,R2.csv:1
D,R2.csv:13
E,R2.csv:15,R3.csv:2
F,R2.csv:19
G,R3.csv:6
H,R3.csv:8

OK, agora sabemos que os valores de saída são de quais arquivos e quais arquivos não possuem esses dados. Para isso, preenchendo os dados dos arquivos não existentes com 0 , usei a substituição do comando shell para gerar uma linha de cabeçalho congaando todos os arquivos name e passou para awk como HEADER -v aplicável:

awk -v HEADER="$(printf ",%s:" R{1..3}.csv)" ...

mais tarde, usaremos essa linha HEADER e preencheremos os dados dos arquivos perdidos com 0 . Agora nossa entrada é como este formato:

$ awk -v HEADER="$(printf ",%s:" R{1..3}.csv)" -F, '
    { seen[$1]=seen[$1]","FILENAME":"$2; }
    END { print HEADER; for (x in seen) print x seen[x]}' R{1..3}.csv 
,R1.csv:,R2.csv:,R3.csv:
A,R1.csv:1,R3.csv:1
B,R1.csv:20,R3.csv:4
C,R1.csv:30,R2.csv:1
D,R2.csv:13
E,R2.csv:15,R3.csv:2
F,R2.csv:19
G,R3.csv:6
H,R3.csv:8

Em seguida, usei outro script awk abaixo para preencher os dados dos arquivos que não saem com 0 , que copiei da minha outra resposta à pergunta " Formatar e preencher dados perdidos com base na coluna ".

... |awk -F, 'NR==1{split($0,arr,/,/);next} {SEP=""; fld=1;
    for (x in arr){printf ($0 ~ arr[x])?SEP""$(fld++):",0";SEP=","};print ""}'

No final, o sed 's/,R[1-9]\+\.csv:/,/g' é usado para substituir o nome do arquivo existente no resultado por vírgula única , .

    
por 11.04.2018 / 21:46
0

Existem muitas maneiras de lidar com CSVs na linha de comando , ou a resposta de Archemar. No entanto, por causa de seus requisitos, aconselho usar python. Eu testei este script em python 3.5 e deve resolver o problema, ou pelo menos dar-lhe um bom começo:

import os,re,argparse
import csv

parser = argparse.ArgumentParser(description='join csvs with rows of the \
        form \w+,[1-9], inserting a zero for a row label if it does not \
        exist.')
parser.add_argument('infiles', type=str, help='infile names', nargs='+')
args = parser.parse_args()

d = {}
file_idx = 0
for infile in args.infiles:
    with open(infile, 'r') as f:
        for line in f:
            parsed_line = re.match('(\w+),([0-9]+)', line)
            if not parsed_line:
                print("line {} not parsed in file {}".format(line, infile))
                continue
            if parsed_line.group(1) in d:
                d[parsed_line.group(1)].append(parsed_line.group(2))
            else:
                l = [0]*(file_idx)
                l.append(parsed_line.group(2))
                d[parsed_line.group(1)]=l
        for k in d:
            if (len(d[k]) == file_idx):
                d[k].append(0)
            if not(len(d[k]) == file_idx+1):
                print("problem with file {}, dict {}, key {}".format(f,d,k))
    file_idx = file_idx + 1

## output time
with open('results.csv','w') as csvfile:
    cwriter = csv.writer(csvfile)
    header = [os.path.splitext(x)[0] for x in args.infiles]
    header.insert(0,'Taxa')
    cwriter.writerow(header)
    for k in sorted(d.keys()):
        d[k].insert(0,k)
        cwriter.writerow(d[k])
    
por 11.04.2018 / 19:55