R sqldf/data.table/dplyrでCSVファイルを選択する方法は？

RにはSQLレベルの利便性を備えた多くのパッケージがあります。最も便利なパッケージは

• dplyr（現代的で基本的な機能よりも10〜100倍速い場合が多い）には、グループ別や個々の結合などのSQLに触発されたコマンドがあります。

• SparkR（Sparkサポートが必要な場合はここにはありませんが、優れたSQL便利なコマンドが付属しています）や、グループ別や個別の結合などのSQLに触発されたコマンド

• data-tableread.csvに置き換えるなど、基本機能よりも効率的な機能を提供しますfread。

• SQLDFは非常に信頼できず、非効率的であり、上記の失敗したRstudioのような優先順位エラーは致命的なエラーを引き起こす可能性があります。

これらのパッケージは、SQLを学習してデバッグする方法を教えてくれません。 SQLDFは確かに適切なSQLを学習するのに最も便利なツールではありません。 SQL Server Management Studio（Windows）は使いやすくなります（たとえば、Azure Basic SQL DB、月5€、または無料評価）。ここまたは他のデータベース - または直接設定

• コマンドソース pos​​tgres データベースを使用したsrc_postgres()R のデータベースコマンドの詳細ここ

以下に、正しいSQLを示し、コード例で発生したエラーを修正するデモを示します。また、SQLの便利なコマンドも示しています。 Rパッケージで何を見つけるべきかを知るために、まずSQLを正しく学ぶのが最善です。

デモ

---

コード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からインスピレーションを得ました。

• 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スタイルの結合（内部結合、左結合など）とグループ化基準。もっとここ。

  

sqldf方法。

1つの方法は、その方法に関する警告を表示します。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

と一緒に2つの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おそらく2番目のファイルと添付ファイルに文法エラーがある可能性がありますJOIN。

広く使っています。真珠言語モジュールテキスト::CSV_XSCSVファイルの大規模な一時作業に使用されます。このモジュールを使用して、4つの小さな基本モジュールを作成しました。真珠プログラムは、私がやりたいことすべてのためのコンポーネントとして使用できます。

1. フィルタ - 入力ファイルフィルタファイルフィールドフィルタリング
2. 拒否 - 入力ファイルフィルタファイルフィールドを拒否します。
3. ストリッパー - ストリッパー入力ファイルフィールド[field2 field3… ]
4. スワップ - 入力ファイル スワップ swapFile matchField フィールド

filterFile の各行には正規表現パターンがあります。これらのパターンのいずれかに一致する項目は、受諾または拒否に対して一致します。さまざまな「フィールド」は列ヘッダー名です。

したがって、あなたの例では、filterFileに "1"を入れて、次のようにしました。

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には同じ名前の2行があります。

1
2 

STDINから入力を受け取るか、特定のURLに出力を投稿するなどの操作を実行する4つのビルディングブロックルーチンのすべてにさまざまなバリエーションバージョンがあります。

専用ルーチンを使用してこれを直接実行したい場合は、非常に簡単です。 テキスト::CSV_XSCSVファイル行をハッシュで簡単に抽出し、必要な操作を実行できます。

まず、ファイルが大きい場合は、次を使用する必要があります。データベースファイルモジュールは、ハッシュがディスクにデータベースとして保存されることを指定します。そうしないと、メモリがいっぱいになって停滞する危険があります。

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」は、ハッシュに含まれるさまざまなパラメータまたはパラメータの配列に置き換えられます。