如何通過 R sqldf/data.table/dplyr 選擇 CSV 文件？

我知道執行緒如何在 R中內部加入兩個 csv 文件，其中包含我不想要的合併選項。我有兩個數據 CSV 文件。我正在考慮如何像使用 R 的 SQL 一樣查詢它們。兩個 CSV 文件的主鍵是data_id. 可以在（等）data.csv中找不到ID的地方log.csv``4

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_value從data.csv和log.csv

類似部分 PostgreSQL 選擇的虛擬碼

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 方法How to SELECT with two CSV files/… on PostgreSQL?

sqldf方法。

一種顯示方法警告的方法 -如果您加入，join則不能在兩個表上使用。程式碼 1WHERE data_id``data_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

使用兩個SELECTsJOIN

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。

R 有許多附帶 SQL 級別便利的包。最方便的包是

• dplyr（現代，通常是基本函式的 10-100 倍速度），帶有受 SQL 啟發的命令，例如 group-by 和不同的連接
• SparkR（如果你需要 Spark 支持，顯然不是在這裡，但它帶有很好的 SQL 方便命令），帶有 SQL 啟發的命令，例如 group-by 和不同的連接
• data-table提供比基本功能更高效的功能，例如 replace read.csvwith 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 樣式的連接（內連接、左連接等）和 group-by。更多在這裡。

引用自：https://unix.stackexchange.com/questions/363576