Corrigindo um arquivo .csv onde algumas linhas possuem colunas ausentes

Question

Atualmente, estou trabalhando com dados de muitos arquivos .csv mesclados. Infelizmente, essas mesclagens são falhas às vezes.

Isso é melhor explicado por este exemplo:

"var1", "var2", "var3", "var4", "var5"
"2001", "yellow", "123", "abc", "bcdefg"
"2002", "yellow", "123", "abw", "asdfkl"
"2001", "green", "abe"
"2002", "green", "abp"
"2001", "blue", "324", "abx", "badsf"
"2002", "blue", "231", "abl", "cpq"

Portanto, na linha 3 e 4, os valores de var3 e var5 estão ausentes. Os erros de quais variáveis (colunas) estão faltando são sempre os mesmos.

Eu quero que meu csv tenha esta aparência:

"var1", "var2", "var3", "var4", "var5"
"2001", "yellow", "123", "abc", "bcdefg"
"2002", "yellow", "123", "abw", "asdfkl"
"2001", "green", , "abe" ,
"2002", "green", , "abp" ,
"2001", "blue", "324", "abx", "badsf"
"2002", "blue", "231", "abl", "cpq"

Portanto, agora as linhas 3 e 4 têm valores ausentes para var3 e var5. Os erros nem sempre acontecem para (neste exemplo) "verde", mas também podem ser para outro grupo.

Minha ideia seria que as linhas fossem verificadas quanto a colunas e, se não houvesse a mesma quantidade de colunas como no cabeçalho ("var1", "var2", etc.), as novas colunas vazias serão adicionadas.

Eu terei que fazer isso para muitos arquivos diferentes, mas uma vez que eu tenha um idead sobre como fazer isso, eu acho que posso para um loop de script bash.

[edit]: I want to clarify, the dataset is quite big. With at least 19 variables (columns). (Another file where i need to check has over 60 variables)

Agora estou pensando em uma solução com o awk. Algo parecido com isto:

awk '{if (NF < 19) {$ 7 = $ 7 "," # NA "," # NA}} "file1 > arquivo2

Aqui deve inserir duas colunas após a sétima coluna se não houver 19 colunas (o que deveria ter). Vai tentar isso mais tarde ...

    

Answer 1

A coisa mais simples que vem à mente é dividir as linhas em vírgulas e inserir vírgulas extras onde há apenas duas delas. A limitação óbvia é que, se você tiver vírgulas nos valores reais, isso será quebrado.

$ cat test.csv | sed -r 's/^([^,]*),([^,]*),([^,]*)$/,, ,, /g'
"var1", "var2", "var3", "var4", "var5"
"2001", "yellow", "123", "abc", "bcdefg"
"2002", "yellow", "123", "abw", "asdfkl"
"2001", "green", , "abe", 
"2002", "green", , "abp", 
"2001", "blue", "324", "abx", "badsf"
"2002", "blue", "231", "abl", "cpq"

Para algo mais geral, eu provavelmente escreveria um script Python (ele tem recursos de CSV em). Por exemplo, isso lê CSVs de stdin e saídas para stdout:

#!/usr/bin/env python
import sys
import csv

missing = [3, 5]  # 1-indexed positions of missing values
missing.sort()  # enforce the increasing order
reader = csv.reader(sys.stdin, delimiter=',', skipinitialspace=True)
writer = csv.writer(sys.stdout)
header = next(reader)  # get first row (header)
writer.writerow(header)  # write it back
for row in reader:
    if len(row) < len(header):
        # row shorter than header -> insert empty strings
        # inserting changes indices so 'missing' must be sorted
        for idx in missing:
            row.insert(idx - 1, '')
    writer.writerow(row)

O benefício de usar um analisador CSV real é que ele manipula corretamente vírgulas ou aspas em valores e outros casos de borda. O formato de saída também será um CSV correto, mas um pouco diferente do que você tinha:

$ cat test.csv | python test.py 
var1,var2,var3,var4,var5
2001,yellow,123,abc,bcdefg
2002,yellow,123,abw,asdfkl
2001,green,,abe,
2002,green,,abp,
2001,blue,324,abx,badsf
2002,blue,231,abl,cpq

Como você pode ver, não há citações ou espaços supérfluos após as vírgulas. Se você realmente precisar deles, eu posso olhar para configurar o dialeto CSV para o escritor.