Como selecionar arquivos CSV por R sqldf / data.table / dplyr?

Question

Eu conheço o tópico Como posso me juntar a ele? dois arquivos csv em R que tem uma opção de mesclagem, que eu não quero. Eu tenho dois arquivos CSV de dados. Estou pensando em como consultar como eles como SQL com R. Dois arquivos CSV em que a chave primária é data_id . data.csv onde OK para ter IDs não encontrados em log.csv (etc 4 )

data_id, event_value
1, 777
1, 666
2, 111
4, 123 
3, 324
1, 245

log.csv em que não há duplicatas na coluna ID , mas duplicatas podem estar em name

data_id, name
1, leo
2, leopold
3, lorem

Pseudocódigo por sintaxe parcial do PostgreSQL

1. Deixe data_id=1
2. Mostrar name e event_value de data.csv e log.csv , respectivamente

Pseudocódigo como seleção parcial do PostgreSQL

SELECT name, event_value 
    FROM data, log
    WHERE data_id=1;

Saída esperada

leo, 777
leo, 666 
leo, 245

abordagem R

file1 <- read.table("file1.csv", col.names=c("data_id", "event_value"))
file2 <- read.table("file2.csv", col.names=c("data_id", "name"))

# TODO here something like the SQL query 
# http://stackoverflow.com/a/1307824/54964

Possíveis abordagens em que acho que sqldf pode ser suficiente aqui

1. sqldf
2. data.table
3. dplyr

PostgreSQL Schema pseudocode para mostrar o que estou tentando fazer com arquivos CSV

CREATE TABLE data (
        data_id SERIAL PRIMARY KEY NOT NULL,
        event_value INTEGER NOT NULL
);
CREATE TABLE log (
        data_id SERIAL PRIMARY KEY NOT NULL,
        name INTEGER NOT NULL
);

R: 3.3.3
SO: Debian 8.7
Relacionados: abordagem PostgreSQL no segmento relevante Como SELECT com dois arquivos CSV /… no PostgreSQL?

    

Answer 1

abordagem sqldf .

Uma abordagem que mostra uma advertência com a abordagem join - você não pode usar WHERE data_id em ambas as tabelas se ingressar em data_id . Código 1

file1 <- read.table("data.csv", col.names=c("data_id", "event_value"))
file2 <- read.table("log.csv", col.names=c("data_id", "name"))

library("sqldf")
df3 <- sqldf("SELECT event_value, name
   FROM file1
   LEFT JOIN file2 USING(data_id)")

df3

Saída errada porque data_id = 1 também deve estar ativo

Loading required package: gsubfn
Loading required package: proto
Loading required package: RSQLite
Loading required package: tcltk
Warning message:
Quoted identifiers should have class SQL, use DBI::SQL() if the caller performs the quoting. 
  event_value    name
1 event_value    name
2         777     leo
3         666     leo
4         111 leopold
5         123    <NA>
6         324   lorem
7         245     leo

Código 2

Código

df3 <- sqldf("SELECT event_value, name
   FROM file1
   LEFT JOIN file2 USING(data_id)
   WHERE data_id = 1")

Saída em branco porque join já foi aplicado

[1] event_value name       
<0 rows> (or 0-length row.names)

Código 3

Do WHERE anterior

df3 <- sqldf("SELECT event_value, name
   FROM file1
   WHERE data_id = 1
   LEFT JOIN file2 USING(data_id)")

Erro de saída porque duas tabelas são de tamanho diferente, então WHERE deve ser aplicado em ambas as tabelas

Error in rsqlite_send_query(conn@ptr, statement) : 
  near "LEFT": syntax error
Calls: sqldf ... initialize -> initialize -> rsqlite_send_query -> .Call
In addition: Warning message:
Quoted identifiers should have class SQL, use DBI::SQL() if the caller performs the quoting. 
Execution halted

Código 4

Usando dois SELECT s com JOIN

df3 <- sqldf("SELECT event_value, name
   FROM file1
   WHERE data_id = 1
   LEFT JOIN 
       (SELECT data_id, name
       FROM file2
       WHERE data_id = 1)
   USING(data_id)")

Erro de saída

Error in rsqlite_send_query(conn@ptr, statement) : 
  near "LEFT": syntax error
Calls: sqldf ... initialize -> initialize -> rsqlite_send_query -> .Call
In addition: Warning message:
Quoted identifiers should have class SQL, use DBI::SQL() if the caller performs the quoting. 
Execution halted

Talvez, um erro de sintaxe com o segundo SELECT e seu anexo com JOIN .

    

Answer 2

O R tem vários pacotes que vêm com o nível de conveniência do SQL. Os pacotes mais convenientes são

• dplyr (moderno, geralmente 10-100 vezes a velocidade em relação às funções base) com comandos inspirados em SQL, como junções agrupadas e diferentes

• SparkR (se você precisar de suporte ao Spark, aparentemente não aqui, mas isso vem com comandos convenientes do SQL) com comandos inspirados no SQL, como junções group-by e diferentes

• data-table fornece funções mais eficientes que as funções básicas, como substituir read.csv por fread .

• O SQLDF é muito pouco confiável e ineficiente, sim, você receberá um erro fatal com o erro de precedência, como acima, na falha do Rstudio.

onde os pacotes não ensinam a aprender e depurar SQL: para aprender o SQL apropriado, o SQLDF não é necessariamente a ferramenta mais conveniente para isso. O SQL Server Management Studio, Windows, pode ser mais fácil de usar, por exemplo, com o SQL DB Básico do Azure, 5EUR / mês ou camada gratuita aqui ou algum outro DB - ou configure o próprio banco de dados

• banco de dados postgres de origem com o comando src_postgres() , mais sobre comandos do banco de dados em R aqui

O abaixo fornece demonstrações, mostrando o SQL adequado e corrigindo os erros que ocorrem nos casos do seu Código. Eu também mostro os comandos de conveniência do SQL. É bom aprender primeiro o SQL corretamente para que você saiba o que procurar nos pacotes R.

DEMOS

O código 4 está falhando por causa da precedência errada. A cláusula where e o USING devem ser depois da associação.

> file1 <- read.csv("test1.csv", header=TRUE, sep=",")
> file2 <- read.csv("test2.csv", header=TRUE, sep=",")

> sqldf("SELECT event_value, name
+       FROM file1
+       LEFT JOIN 
+       (SELECT data_id, name
+       FROM file2
+       WHERE data_id = 1)
+       USING(data_id)
+       WHERE data_id = 1")
  event_value name
1         777  leo
2         666  leo
3         245  leo

Outras formas contêm

Aproximação LEFT-JOIN adequada

> df3 <- sqldf("SELECT event_value, name
+              FROM file1 a
+              LEFT JOIN file2 b ON a.data_id=b.data_id")
> 
> df3
  event_value    name
1         777     leo
2         666     leo
3         111 leopold
4         123    <NA>
5         324   lorem
6         245     leo
> df3 <- sqldf("SELECT a.event_value, b.name
+    FROM file1 a
+              LEFT JOIN file2 b ON a.data_id=b.data_id
+              WHERE a.data_id = 1")
> df3
  event_value name
1         777  leo
2         666  leo
3         245  leo

Mesclando as tabelas com a condição

> df4 <- sqldf("SELECT a.event_value, b.name
+    FROM file1 a, file2 b
+              WHERE a.data_id = 1
+              AND a.data_id=b.data_id")
> 
> df4
  event_value name
1         777  leo
2         666  leo
3         245  leo

Abordagem de subconsultas SQL

> df5 <- sqldf("SELECT a.event_value, b.name
+    FROM 
+              (SELECT data_id, event_value FROM file1) a,
+              (SELECT data_id, name FROM file2) b
+              WHERE a.data_id = 1
+              AND a.data_id=b.data_id")
> df5
  a.event_value b.name
1           777    leo
2           666    leo
3           245    leo

O QUE SÃO FORMAS SQL-CONVENIENT PARA USAR R?

dplyr

• Associações no estilo SQL

  

• função de conveniência build_sql para executar comandos no estilo SQL

  

• A função caso-quando é inspirada em SQL-CASE-WHEN

• A função Coalesce é inserida por SQL-COALESCE

• translate_sql ajuda a converter funções R para SQL (mais casos aqui )

  # Many functions have slightly different names
  translate_sql(x == 1 && (y < 2 || z > 3))
  #> <SQL> "x" = 1.0 AND ("y" < 2.0 OR "z" > 3.0)
  translate_sql(x ^ 2 < 10)
  #> <SQL> POWER("x", 2.0) < 10.0
  translate_sql(x %% 2 == 10)
  #> <SQL> "x" % 2.0 = 10.0
  

• SQLLite e dplyr: instale o pacote sqlite e tente o conjunto de dados NYC com dplyr, mais aqui

Pacote SparkR

• vem com junções no estilo SQL (junções internas, junções esquerdas, etc) e agrupadas por. Mais aqui .

  

Answer 3

Eu faço uso extensivo do módulo de linguagem Perl Text :: CSV_XS para manipulação ad hoc e pesada de arquivos CSV. Usando este módulo, construí quatro programas básicos Perl para usar como blocos de construção para o que eu quiser fazer.

1. Filtro - Filtro inputFile filterArquivoFile
2. Rejeitar - Rejeitar campo inputFile filterFile
3. Stripper - Stripper inputFile field [campo2 campo3…]
4. Troca - troca de entradaFile swapFile matchField outfield

O filterFile tem um padrão regEx em cada linha. Qualquer coisa que corresponda a um desses padrões é correspondida para fins de aceitação ou rejeição. Os "campos" sortidos são nomes de cabeçalhos de colunas.

Então, no seu exemplo, eu coloquei "1" no filterFile e vá:

perl Filter.pm data.csv filter.txt data_id >One.csv
perl Stripper.pm One.csv data_id event_value >Two.csv
perl Swap.pm Two.csv log.csv data_id name >Three.csv

Se também quiséssemos os eventos de Leopold, o filter.txt teria duas linhas com conteúdo homônimo:

1
2 

Eu tenho diversas versões mutantes de todas as quatro rotinas de blocos de construção que fazem coisas como receber entrada de STDIN ou postar saída para uma URL específica.

Se você quisesse fazer isso diretamente com uma rotina de propósito especial, isso seria bastante fácil. O Text :: CSV_XS atrai facilmente as linhas do arquivo CSV para os hashes e, em seguida, você pode fazer o que quiser com eles.

Primeiro, se seus arquivos são enormes, você deve usar o módulo DB_File para especificar que seu hash deve ser armazenado no disco como um banco de dados. Caso contrário, você pode encher a memória e parar.

use DB_File;

my %theHash;
unlink '/tmp/translation.db';
sleep 2;
tie ( %theHash, 'DB_File', '/tmp/translation.db' )
    or die "Can't open /tmp/translation.db\n";

Em seguida, crie objetos CSV

map{ $_ = Text::CSV_XS->new( { allow_whitespace => 1,
        eol =>"52",
        always_quote => 1, binary => 1 })}
   ( $data_csv, $log_csv, $output_csv );

Observe que estou usando caracteres EOL do DOS.

Em seguida, insira as linhas do cabeçalho de entrada para configurar os nomes das colunas

@cols = @{$data_csv->getline( $data_fh )};
$data_csv->column_names( @cols );
@cols = @{$log_csv->getline( $log_fh )};
$log_csv->column_names( @cols );

onde você abriu os arquivos no arquivo, lida com $ data_fh e $ log_fh.

Decida quais serão suas colunas de saída e escreva uma linha de cabeçalho de coluna

@output_cols = ( 'name', 'event_value' );
$output_csv->combine( @output_cols );
$latest_row = $output_csv->string();
print $output_fh, $latest_row;

Em seguida, crie um data_id para nomear o hash.

while ( $log_csv_row = $log_csv->getline_hr( $log_fh ) ){
    $theHash{ $log_csv_row->{data_id} } = $log_csv_row->{name};
}

Depois, como no seu exemplo, percorra o data.csv para obter todos os '1's'.

$outputHash{name} = $theHash{1};

while ( $data_csv_row = $data_csv->getline_hr( $data_fh ) ){

    next unless $data_csv_row->{data_id} == 1;

    $outputHash{data_id} = $data_csv_row->{data_id};
    $output_csv->combine( map { $outputHash{$_} } @output_cols );
    $latest_row = $output_csv->string();
    print $output_fh "$latest_row";
}

Este código de exemplo é a base para todas as rotinas de utilitários listadas acima, onde o '1' codificado permanentemente é substituído por argumentos variados ou matrizes de argumentos que são colocados em hashes.