R sqldf/data.table/dplyr을 통해 CSV 파일을 선택하는 방법은 무엇입니까?

R sqldf/data.table/dplyr을 통해 CSV 파일을 선택하는 방법은 무엇입니까?

난 단서를 알아R에서 두 개의 csv 파일을 내부적으로 연결하는 방법병합 옵션이 있지만 원하지 않습니다. 두 개의 CSV 데이터 파일이 있습니다. SQL, R과 같은 쿼리를 어떻게 처리할지 고민 중입니다. 기본 키가 2개의 CSV 파일입니다 data_id. 찾을 수 없는 ID는 (etc. ) data.csv에서 찾을 수 있습니다.log.csv4

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

CSV 파일로 수행하려는 작업을 보여주는 PostgreSQL 스키마 유사 코드

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
OS: Debian 8.7
관련: 관련 스레드의 PostgreSQL 메소드PostgreSQL에서 두 개의 CSV 파일/...을 사용하여 SELECT를 수행하는 방법은 무엇입니까?

답변1

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€ 또는 무료 등급).여기또는 다른 데이터베이스 - 또는 직접 설정

  • 명령 소스 postgres 데이터베이스를 사용하여 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 스타일 조인(내부 조인, 왼쪽 조인 등) 및 그룹화 기준. 더여기.

    여기에 이미지 설명을 입력하세요.

답변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_XSCSV 파일에 대한 대규모 임시 작업에 사용됩니다. 이 모듈을 사용하여 네 개의 작은 기본 모듈을 만들었습니다.진주프로그램은 내가 하고 싶은 모든 일을 위한 구성 요소로 사용될 수 있습니다.

  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에는 동일한 이름의 두 줄이 있습니다.

1
2 

STDIN에서 입력을 받거나 특정 URL에 출력을 게시하는 등의 작업을 수행하는 네 가지 빌딩 블록 루틴 모두에 대한 다양한 변형 버전이 있습니다.

전용 루틴을 사용하여 이 작업을 직접 수행하려는 경우 매우 쉽습니다. 텍스트::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"은 해시에 포함된 다양한 매개변수 또는 매개변수 배열로 대체됩니다.

관련 정보