☰

如何通过 R sqldf/data.table/dplyr 选择 CSV 文件？

tag-icon • tag-icon csv r sql
如何通过 R sqldf/data.table/dplyr 选择 CSV 文件？

我知道线索如何在 R 中内部连接两个 csv 文件它有一个合并选项，但我不想要。我有两个 CSV 数据文件。我正在考虑如何像 SQL 和 R 一样进行查询。两个 CSV 文件，其中主键是data_id。 data.csv可以在log.csv（等4）中找到未找到的 ID

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

log.csv其中列中没有重复项，ID但可以重复项name

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

部分 PostgreSQL 语法的伪代码

  1. 让data_id=1
  2. 分别显示来自name和的 和event_valuedata.csvlog.csv

类似 PostgreSQL 部分 select 的伪代码

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

预期产出

leo, 777
leo, 666 
leo, 245

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

我认为sqldf在这里就足够的可能方法

  1. sqldf
  2. data.table
  3. dplyr

PostgreSQL Schema 伪代码显示我尝试对 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
操作系统：Debian 8.7
相关：相关线程中的 PostgreSQL 方法如何在 PostgreSQL 上使用两个 CSV 文件/…进行 SELECT？

答案1

R 有许多带有 SQL 级别便利性的包。最方便的套餐是

  • dplyr（现代，通常速度是基本函数的 10-100 倍），具有 SQL 启发的命令，例如 group-by 和不同的连接

  • SparkR（如果您需要 Spark 支持，这里显然不是，但它附带了很好的 SQL 方便命令）以及受 SQL 启发的命令，例如 group-by 和不同的联接

  • data-table提供比基本功能更有效的功能，例如替换read.csv为fread。

  • SQLDF 非常不可靠且效率低下，是的，您会遇到致命错误，并出现优先级错误，就像上面失败的 Rstudio 一样。

这些软件包不会教您学习和调试 SQL：为了学习正确的 SQL，SQLDF 当然不是最方便的工具。 SQL Server Management Studio（Windows）可能更容易使用，例如与 Azure Basic SQL DB 一起使用，每月 5 欧元或免费套餐这里或其他一些数据库——或者自己设置数据库

  • 使用命令源 postgres 数据库src_postgres()，有关 R 中数据库命令的更多信息这里

下面为您提供了演示，展示了正确的 SQL 并修复了代码案例中出现的错误。我还展示了 SQL 便利命令。最好先正确学习 SQL，这样您就知道在 R 包中寻找什么。

演示

代码 4 由于优先级错误而失败。 where 子句和 USING 必须位于连接之后。

> 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

其他方式包含

正确的 LEFT-JOIN 方法

> 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

使用 where 条件合并表

> 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

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

使用 R 的 SQL 便捷方式有哪些？

dplyr

  • SQL 风格的连接

    在此输入图像描述

  • build_sql 执行 SQL 风格命令的便捷函数

    在此输入图像描述

  • Case-when 函数的灵感来自于 SQL-CASE-WHEN

  • Coalesce 函数受 SQL-COALESCE 启发

  • translate_sql 帮助将 R 函数转换为 SQL（更多情况这里）

    # 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 和 dplyr：安装 sqlite 包并使用 dplyr 尝试 NYC 数据集，更多这里

SparkR包

  • 带有 SQL 风格的连接（内连接、左连接等）和分组依据。更多的这里。

    在此输入图像描述

答案2

sqldf方法。

一种方法显示了方法的警告-如果您通过 加入，则join不能在两个表上使用。代码1WHERE data_iddata_id

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

输出错误，因为data_id = 1也应该处于活动状态

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

代码2

代码

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

输出空白，因为join已经应用

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

代码3

WHERE早点做

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

输出错误，因为两个表的大小不同，因此WHERE应该应用于两个表

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

代码4

使用两个SELECTs 与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)")

输出错误

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

SELECT也许，第二个及其附件存在语法错误JOIN。

答案3

我广泛使用珀尔语言模块文本::CSV_XS用于 CSV 文件的重型、临时操作。使用这个模块我构建了四个小的基本模块珀尔程序可以用作我想做的任何事情的构建块。

  1. Filter - 过滤输入文件过滤文件字段
  2. 拒绝 - 拒绝输入文件过滤器文件字段
  3. Stripper - Stripper 输入文件字段 [field2 field3…]
  4. Swap - 交换输入文件 swapFile matchField 外场

filterFile 的每一行都有一个正则表达式模式。任何与这些模式之一匹配的内容都会被匹配以达到接受或拒绝的目的。各种“字段”是列标题名称。

所以在你的例子中我只是把“1”放在filterFile中然后去：

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

如果我们还想要 Leopold 的事件，filter.txt 将有两行同名内容：

1
2

我有所有四个构建块例程的各种突变版本，它们执行诸如从 STDIN 获取输入或将输出发布到特定 URL 之类的操作。

如果您想直接使用专用例程来完成此操作，那将相当容易。 文本::CSV_XS轻松将 CSV 文件行提取到哈希中，然后您可以对它们执行您喜欢的操作。

首先，如果您的文件很大，您应该使用数据库文件模块指定您的哈希值应作为数据库存储在磁盘上。否则你可能会填满内存并陷入停顿。

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";

然后创建 CSV 对象

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

请注意，我使用的是 DOS EOL 字符。

然后拉入输入标题行以设置列名称

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

您在文件句柄 $data_fh 和 $log_fh 上打开文件的位置。

决定输出列是什么并写出列标题行

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

然后组成一个 data_id 来命名哈希。

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

然后，如您的示例所示，循环遍历 data.csv 以获取所有“1”。

$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";
}

此示例代码是上面列出的所有实用程序例程的基础，其中硬编码的“1”被替换为放入散列中的各种参数或参数数组。

相关内容