데이터세트 조인을 위한 data.table 메서드 업데이트 각 조인 유형에 대한 아래 예를 참조하십시오. 두 가지 방법이 있습니다. 하나는 [.data.table 에서 두 번째 data.table을 부분 집합에 대한 첫 번째 인수로 전달할 때, 다른 방법은 빠른 data.table 방법으로 전달 merge
df1 = data.frame(CustomerId = c(1:6), Product = c(rep("Toaster", 3), rep("Radio", 3))) df2 = data.frame(CustomerId = c(2L, 4L, 7L), State = c(rep("Alabama", 2), rep("Ohio", 1))) # one value changed to show full outer join library(data.table) dt1 = as.data.table(df1) dt2 = as.data.table(df2) setkey(dt1, CustomerId) setkey(dt2, CustomerId) # right outer join keyed data.tables dt1[dt2] setkey(dt1, NULL) setkey(dt2, NULL) # right outer join unkeyed data.tables - use `on` argument dt1[dt2, on = "CustomerId"] # left outer join - swap dt1 with dt2 dt2[dt1, on = "CustomerId"] # inner join - use `nomatch` argument dt1[dt2, nomatch=NULL, on = "CustomerId"] # anti join - use `!` operator dt1[!dt2, on = "CustomerId"] # inner join - using merge method merge(dt1, dt2, by = "CustomerId") # full outer join merge(dt1, dt2, by = "CustomerId", all = TRUE) # see ?merge.data.table arguments for other cases
아래 벤치마크 테스트는 R, sqldf, dplyr 및 data.table을 기반으로 합니다.
벤치마크는 키가 없는/인덱싱되지 않은 데이터 세트를 테스트합니다. 벤치마크는 50M-1개의 행 데이터 세트에서 수행되며 조인 열에는 50M-2개의 공통 값이 있으므로 각 시나리오(내부, 왼쪽, 오른쪽, 전체)를 테스트할 수 있으며 조인을 수행하는 것은 여전히 간단하지 않습니다. 조인 알고리즘을 잘 강조하는 조인 유형입니다. 타이밍은 sqldf:0.4.11 , dplyr:0.7.8 , data.table:1.12.0 입니다.
# inner Unit: seconds expr min lq mean median uq max neval base 111.66266 111.66266 111.66266 111.66266 111.66266 111.66266 1 sqldf 624.88388 624.88388 624.88388 624.88388 624.88388 624.88388 1 dplyr 51.91233 51.91233 51.91233 51.91233 51.91233 51.91233 1 DT 10.40552 10.40552 10.40552 10.40552 10.40552 10.40552 1 # left Unit: seconds expr min lq mean median uq max base 142.782030 142.782030 142.782030 142.782030 142.782030 142.782030 sqldf 613.917109 613.917109 613.917109 613.917109 613.917109 613.917109 dplyr 49.711912 49.711912 49.711912 49.711912 49.711912 49.711912 DT 9.674348 9.674348 9.674348 9.674348 9.674348 9.674348 # right Unit: seconds expr min lq mean median uq max base 122.366301 122.366301 122.366301 122.366301 122.366301 122.366301 sqldf 611.119157 611.119157 611.119157 611.119157 611.119157 611.119157 dplyr 50.384841 50.384841 50.384841 50.384841 50.384841 50.384841 DT 9.899145 9.899145 9.899145 9.899145 9.899145 9.899145 # full Unit: seconds expr min lq mean median uq max neval base 141.79464 141.79464 141.79464 141.79464 141.79464 141.79464 1 dplyr 94.66436 94.66436 94.66436 94.66436 94.66436 94.66436 1 DT 21.62573 21.62573 21.62573 21.62573 21.62573 21.62573 1
data.table 사용하여 수행할 수 있는 다른 유형의 조인이 있습니다.
- 조인 시 업데이트 - 다른 테이블에서 기본 테이블로 값을 조회하려는 경우
- 조인 시 집계 - 조인 중인 키에 대해 집계하려는 경우 모든 조인 결과를 구체화할 필요가 없습니다.
- 겹치는 조인 - 범위별로 병합하려는 경우
- 롤링 조인 - 앞 또는 뒤로 롤링하여 앞/뒤 행의 값과 병합할 수 있도록 하려는 경우
- 비동등 조인 - 조인 조건이 같지 않은 경우
재현할 코드:
library(microbenchmark) library(sqldf) library(dplyr) library(data.table) sapply(c("sqldf","dplyr","data.table"), packageVersion, simplify=FALSE) n = 5e7 set.seed(108) df1 = data.frame(x=sample(n,n-1L), y1=rnorm(n-1L)) df2 = data.frame(x=sample(n,n-1L), y2=rnorm(n-1L)) dt1 = as.data.table(df1) dt2 = as.data.table(df2) mb = list() # inner join microbenchmark(times = 1L, base = merge(df1, df2, by = "x"), sqldf = sqldf("SELECT * FROM df1 INNER JOIN df2 ON df1.x = df2.x"), dplyr = inner_join(df1, df2, by = "x"), DT = dt1[dt2, nomatch=NULL, on = "x"]) -> mb$inner # left outer join microbenchmark(times = 1L, base = merge(df1, df2, by = "x", all.x = TRUE), sqldf = sqldf("SELECT * FROM df1 LEFT OUTER JOIN df2 ON df1.x = df2.x"), dplyr = left_join(df1, df2, by = c("x"="x")), DT = dt2[dt1, on = "x"]) -> mb$left # right outer join microbenchmark(times = 1L, base = merge(df1, df2, by = "x", all.y = TRUE), sqldf = sqldf("SELECT * FROM df2 LEFT OUTER JOIN df1 ON df2.x = df1.x"), dplyr = right_join(df1, df2, by = "x"), DT = dt1[dt2, on = "x"]) -> mb$right # full outer join microbenchmark(times = 1L, base = merge(df1, df2, by = "x", all = TRUE), dplyr = full_join(df1, df2, by = "x"), DT = merge(dt1, dt2, by = "x", all = TRUE)) -> mb$full lapply(mb, print) -> nul
0..*:0..1 카디널리티가 있는 왼쪽 조인 0..1:0..* 카디널리티가 있는 오른쪽 조인의 경우 조이너( 0..1 테이블)을 조인자( 0..* 테이블)에 직접 연결하여 완전히 새로운 데이터 테이블 생성을 방지합니다. 이를 위해서는 조인의 키 열을 조인에 일치시키고 할당에 따라 조인의 행을 인덱싱+순서화해야 합니다.
match() 에 대한 단일 호출을 사용하여 일치를 수행할 수 있습니다. 이 답변에서 다룰 경우입니다.
다음은 조이너에서 일치하지 않는 키의 경우를 테스트하기 위해 ID가 7 df2 추가한 것을 제외하고는 OP를 기반으로 한 예입니다. 이것은 효과적으로이다 df1 왼쪽 가입 df2 :
df1 <- data.frame(CustomerId=1:6,Product=c(rep('Toaster',3L),rep('Radio',3L))); df2 <- data.frame(CustomerId=c(2L,4L,6L,7L),State=c(rep('Alabama',2L),'Ohio','Texas')); df1[names(df2)[-1L]] <- df2[match(df1[,1L],df2[,1L]),-1L]; df1; ## CustomerId Product State ## 1 1 Toaster <NA> ## 2 2 Toaster Alabama ## 3 3 Toaster <NA> ## 4 4 Radio Alabama ## 5 5 Radio <NA> ## 6 6 Radio Ohio
위의 경우 키 열이 두 입력 테이블의 첫 번째 열이라는 가정을 하드 코딩했습니다. 나는 일반적으로 이것이 불합리한 가정이 아니라고 주장합니다. 왜냐하면 키 열이 있는 data.frame이 있는 경우 data.frame의 첫 번째 열로 설정되지 않았다면 이상할 것이기 때문입니다. 시작. 그리고 항상 열을 재정렬하여 그렇게 할 수 있습니다. 이 가정의 유리한 결과는 키 열의 이름을 하드 코딩할 필요가 없다는 것입니다. 비록 한 가정을 다른 가정으로 바꾸는 것일 뿐이라고 생각합니다. 간결함은 정수 인덱싱의 또 다른 장점이자 속도입니다. 아래 벤치마크에서 경쟁 구현과 일치하도록 문자열 이름 인덱싱을 사용하도록 구현을 변경할 것입니다.
하나의 큰 테이블에 대해 조인을 남기고 싶은 여러 테이블이 있는 경우 이것이 특히 적절한 솔루션이라고 생각합니다. 각 병합에 대해 전체 테이블을 반복적으로 다시 작성하는 것은 불필요하고 비효율적입니다.
반면에 어떤 이유로든 이 작업을 통해 Joinee가 변경되지 않은 상태로 유지되어야 하는 경우 이 솔루션은 Joinee를 직접 수정하므로 사용할 수 없습니다. 이 경우 단순히 복사본을 만들고 복사본에서 제자리 할당을 수행할 수 있습니다.
참고로, 여러 열 키에 대해 가능한 일치 솔루션을 간략하게 살펴보았습니다. 불행히도 내가 찾은 유일한 일치 솔루션은 다음과 같습니다.
- 비효율적인 연결 예를 들어
match(interaction(df1$a,df1$b),interaction(df2$a,df2$b)) paste() 와 같은 아이디어입니다. - 비효율적인 데카르트 접속사, 예:
outer(df1$a,df2$a,`==`) & outer(df1$b,df2$b,`==`) . - 기본 R
merge() 및 이에 상응하는 패키지 기반 병합 함수는 병합된 결과를 반환하기 위해 항상 새 테이블을 할당하므로 제자리 할당 기반 솔루션에 적합하지 않습니다.
예를 들어, 서로 다른 데이터 프레임에서 여러 열 일치 및 다른 열을 결과로 가져오기 , 두 열을 다른 두 열과 일치 , 여러 열 에서 일치, 원래 제자리 솔루션을 생각해 낸 이 질문의 속임수를 참조하십시오. Combine R 의 행 수가 다른 두 데이터 프레임 .
벤치마킹
나는 내부 할당 접근 방식이 이 질문에서 제공된 다른 솔루션과 어떻게 비교되는지 알아보기 위해 자체 벤치마킹을 수행하기로 결정했습니다.
테스트 코드:
library(microbenchmark); library(data.table); library(sqldf); library(plyr); library(dplyr); solSpecs <- list( merge=list(testFuncs=list( inner=function(df1,df2,key) merge(df1,df2,key), left =function(df1,df2,key) merge(df1,df2,key,all.x=T), right=function(df1,df2,key) merge(df1,df2,key,all.y=T), full =function(df1,df2,key) merge(df1,df2,key,all=T) )), data.table.unkeyed=list(argSpec='data.table.unkeyed',testFuncs=list( inner=function(dt1,dt2,key) dt1[dt2,on=key,nomatch=0L,allow.cartesian=T], left =function(dt1,dt2,key) dt2[dt1,on=key,allow.cartesian=T], right=function(dt1,dt2,key) dt1[dt2,on=key,allow.cartesian=T], full =function(dt1,dt2,key) merge(dt1,dt2,key,all=T,allow.cartesian=T) ## calls merge.data.table() )), data.table.keyed=list(argSpec='data.table.keyed',testFuncs=list( inner=function(dt1,dt2) dt1[dt2,nomatch=0L,allow.cartesian=T], left =function(dt1,dt2) dt2[dt1,allow.cartesian=T], right=function(dt1,dt2) dt1[dt2,allow.cartesian=T], full =function(dt1,dt2) merge(dt1,dt2,all=T,allow.cartesian=T) ## calls merge.data.table() )), sqldf.unindexed=list(testFuncs=list( ## note: must pass connection=NULL to avoid running against the live DB connection, which would result in collisions with the residual tables from the last query upload inner=function(df1,df2,key) sqldf(paste0('select * from df1 inner join df2 using(',paste(collapse=',',key),')'),connection=NULL), left =function(df1,df2,key) sqldf(paste0('select * from df1 left join df2 using(',paste(collapse=',',key),')'),connection=NULL), right=function(df1,df2,key) sqldf(paste0('select * from df2 left join df1 using(',paste(collapse=',',key),')'),connection=NULL) ## can't do right join proper, not yet supported; inverted left join is equivalent ##full =function(df1,df2,key) sqldf(paste0('select * from df1 full join df2 using(',paste(collapse=',',key),')'),connection=NULL) ## can't do full join proper, not yet supported; possible to hack it with a union of left joins, but too unreasonable to include in testing )), sqldf.indexed=list(testFuncs=list( ## important: requires an active DB connection with preindexed main.df1 and main.df2 ready to go; arguments are actually ignored inner=function(df1,df2,key) sqldf(paste0('select * from main.df1 inner join main.df2 using(',paste(collapse=',',key),')')), left =function(df1,df2,key) sqldf(paste0('select * from main.df1 left join main.df2 using(',paste(collapse=',',key),')')), right=function(df1,df2,key) sqldf(paste0('select * from main.df2 left join main.df1 using(',paste(collapse=',',key),')')) ## can't do right join proper, not yet supported; inverted left join is equivalent ##full =function(df1,df2,key) sqldf(paste0('select * from main.df1 full join main.df2 using(',paste(collapse=',',key),')')) ## can't do full join proper, not yet supported; possible to hack it with a union of left joins, but too unreasonable to include in testing )), plyr=list(testFuncs=list( inner=function(df1,df2,key) join(df1,df2,key,'inner'), left =function(df1,df2,key) join(df1,df2,key,'left'), right=function(df1,df2,key) join(df1,df2,key,'right'), full =function(df1,df2,key) join(df1,df2,key,'full') )), dplyr=list(testFuncs=list( inner=function(df1,df2,key) inner_join(df1,df2,key), left =function(df1,df2,key) left_join(df1,df2,key), right=function(df1,df2,key) right_join(df1,df2,key), full =function(df1,df2,key) full_join(df1,df2,key) )), in.place=list(testFuncs=list( left =function(df1,df2,key) { cns <- setdiff(names(df2),key); df1[cns] <- df2[match(df1[,key],df2[,key]),cns]; df1; }, right=function(df1,df2,key) { cns <- setdiff(names(df1),key); df2[cns] <- df1[match(df2[,key],df1[,key]),cns]; df2; } )) ); getSolTypes <- function() names(solSpecs); getJoinTypes <- function() unique(unlist(lapply(solSpecs,function(x) names(x$testFuncs)))); getArgSpec <- function(argSpecs,key=NULL) if (is.null(key)) argSpecs$default else argSpecs[[key]]; initSqldf <- function() { sqldf(); ## creates sqlite connection on first run, cleans up and closes existing connection otherwise if (exists('sqldfInitFlag',envir=globalenv(),inherits=F) && sqldfInitFlag) { ## false only on first run sqldf(); ## creates a new connection } else { assign('sqldfInitFlag',T,envir=globalenv()); ## set to true for the one and only time }; ## end if invisible(); }; ## end initSqldf() setUpBenchmarkCall <- function(argSpecs,joinType,solTypes=getSolTypes(),env=parent.frame()) { ## builds and returns a list of expressions suitable for passing to the list argument of microbenchmark(), and assigns variables to resolve symbol references in those expressions callExpressions <- list(); nms <- character(); for (solType in solTypes) { testFunc <- solSpecs[[solType]]$testFuncs[[joinType]]; if (is.null(testFunc)) next; ## this join type is not defined for this solution type testFuncName <- paste0('tf.',solType); assign(testFuncName,testFunc,envir=env); argSpecKey <- solSpecs[[solType]]$argSpec; argSpec <- getArgSpec(argSpecs,argSpecKey); argList <- setNames(nm=names(argSpec$args),vector('list',length(argSpec$args))); for (i in seq_along(argSpec$args)) { argName <- paste0('tfa.',argSpecKey,i); assign(argName,argSpec$args[[i]],envir=env); argList[[i]] <- if (i%in%argSpec$copySpec) call('copy',as.symbol(argName)) else as.symbol(argName); }; ## end for callExpressions[[length(callExpressions)+1L]] <- do.call(call,c(list(testFuncName),argList),quote=T); nms[length(nms)+1L] <- solType; }; ## end for names(callExpressions) <- nms; callExpressions; }; ## end setUpBenchmarkCall() harmonize <- function(res) { res <- as.data.frame(res); ## coerce to data.frame for (ci in which(sapply(res,is.factor))) res[[ci]] <- as.character(res[[ci]]); ## coerce factor columns to character for (ci in which(sapply(res,is.logical))) res[[ci]] <- as.integer(res[[ci]]); ## coerce logical columns to integer (works around sqldf quirk of munging logicals to integers) ##for (ci in which(sapply(res,inherits,'POSIXct'))) res[[ci]] <- as.double(res[[ci]]); ## coerce POSIXct columns to double (works around sqldf quirk of losing POSIXct class) ----- POSIXct doesn't work at all in sqldf.indexed res <- res[order(names(res))]; ## order columns res <- res[do.call(order,res),]; ## order rows res; }; ## end harmonize() checkIdentical <- function(argSpecs,solTypes=getSolTypes()) { for (joinType in getJoinTypes()) { callExpressions <- setUpBenchmarkCall(argSpecs,joinType,solTypes); if (length(callExpressions)<2L) next; ex <- harmonize(eval(callExpressions[[1L]])); for (i in seq(2L,len=length(callExpressions)-1L)) { y <- harmonize(eval(callExpressions[[i]])); if (!isTRUE(all.equal(ex,y,check.attributes=F))) { ex <<- ex; y <<- y; solType <- names(callExpressions)[i]; stop(paste0('non-identical: ',solType,' ',joinType,'.')); }; ## end if }; ## end for }; ## end for invisible(); }; ## end checkIdentical() testJoinType <- function(argSpecs,joinType,solTypes=getSolTypes(),metric=NULL,times=100L) { callExpressions <- setUpBenchmarkCall(argSpecs,joinType,solTypes); bm <- microbenchmark(list=callExpressions,times=times); if (is.null(metric)) return(bm); bm <- summary(bm); res <- setNames(nm=names(callExpressions),bm[[metric]]); attr(res,'unit') <- attr(bm,'unit'); res; }; ## end testJoinType() testAllJoinTypes <- function(argSpecs,solTypes=getSolTypes(),metric=NULL,times=100L) { joinTypes <- getJoinTypes(); resList <- setNames(nm=joinTypes,lapply(joinTypes,function(joinType) testJoinType(argSpecs,joinType,solTypes,metric,times))); if (is.null(metric)) return(resList); units <- unname(unlist(lapply(resList,attr,'unit'))); res <- do.call(data.frame,c(list(join=joinTypes),setNames(nm=solTypes,rep(list(rep(NA_real_,length(joinTypes))),length(solTypes))),list(unit=units,stringsAsFactors=F))); for (i in seq_along(resList)) res[i,match(names(resList[[i]]),names(res))] <- resList[[i]]; res; }; ## end testAllJoinTypes() testGrid <- function(makeArgSpecsFunc,sizes,overlaps,solTypes=getSolTypes(),joinTypes=getJoinTypes(),metric='median',times=100L) { res <- expand.grid(size=sizes,overlap=overlaps,joinType=joinTypes,stringsAsFactors=F); res[solTypes] <- NA_real_; res$unit <- NA_character_; for (ri in seq_len(nrow(res))) { size <- res$size[ri]; overlap <- res$overlap[ri]; joinType <- res$joinType[ri]; argSpecs <- makeArgSpecsFunc(size,overlap); checkIdentical(argSpecs,solTypes); cur <- testJoinType(argSpecs,joinType,solTypes,metric,times); res[ri,match(names(cur),names(res))] <- cur; res$unit[ri] <- attr(cur,'unit'); }; ## end for res; }; ## end testGrid()
다음은 이전에 시연한 OP를 기반으로 하는 예제의 벤치마크입니다.
## OP's example, supplemented with a non-matching row in df2 argSpecs <- list( default=list(copySpec=1:2,args=list( df1 <- data.frame(CustomerId=1:6,Product=c(rep('Toaster',3L),rep('Radio',3L))), df2 <- data.frame(CustomerId=c(2L,4L,6L,7L),State=c(rep('Alabama',2L),'Ohio','Texas')), 'CustomerId' )), data.table.unkeyed=list(copySpec=1:2,args=list( as.data.table(df1), as.data.table(df2), 'CustomerId' )), data.table.keyed=list(copySpec=1:2,args=list( setkey(as.data.table(df1),CustomerId), setkey(as.data.table(df2),CustomerId) )) ); ## prepare sqldf initSqldf(); sqldf('create index df1_key on df1(CustomerId);'); ## upload and create an sqlite index on df1 sqldf('create index df2_key on df2(CustomerId);'); ## upload and create an sqlite index on df2 checkIdentical(argSpecs); testAllJoinTypes(argSpecs,metric='median'); ## join merge data.table.unkeyed data.table.keyed sqldf.unindexed sqldf.indexed plyr dplyr in.place unit ## 1 inner 644.259 861.9345 923.516 9157.752 1580.390 959.2250 270.9190 NA microseconds ## 2 left 713.539 888.0205 910.045 8820.334 1529.714 968.4195 270.9185 224.3045 microseconds ## 3 right 1221.804 909.1900 923.944 8930.668 1533.135 1063.7860 269.8495 218.1035 microseconds ## 4 full 1302.203 3107.5380 3184.729 NA NA 1593.6475 270.7055 NA microseconds
여기서는 임의의 입력 데이터를 벤치마킹하여 두 입력 테이블 간에 서로 다른 척도와 다른 패턴의 키 겹침을 시도합니다. 이 벤치마크는 여전히 단일 열 정수 키의 경우로 제한됩니다. 또한 동일한 테이블의 왼쪽 및 오른쪽 조인 모두에서 제자리 솔루션이 작동하도록 하기 위해 모든 무작위 테스트 데이터는 0..1:0..1 카디널리티를 사용합니다. 이는 두 번째 data.frame의 key column을 생성할 때 첫 번째 data.frame의 key column을 교체하지 않고 샘플링하여 구현한다.
makeArgSpecs.singleIntegerKey.optionalOneToOne <- function(size,overlap) { com <- as.integer(size*overlap); argSpecs <- list( default=list(copySpec=1:2,args=list( df1 <- data.frame(id=sample(size),y1=rnorm(size),y2=rnorm(size)), df2 <- data.frame(id=sample(c(if (com>0L) sample(df1$id,com) else integer(),seq(size+1L,len=size-com))),y3=rnorm(size),y4=rnorm(size)), 'id' )), data.table.unkeyed=list(copySpec=1:2,args=list( as.data.table(df1), as.data.table(df2), 'id' )), data.table.keyed=list(copySpec=1:2,args=list( setkey(as.data.table(df1),id), setkey(as.data.table(df2),id) )) ); ## prepare sqldf initSqldf(); sqldf('create index df1_key on df1(id);'); ## upload and create an sqlite index on df1 sqldf('create index df2_key on df2(id);'); ## upload and create an sqlite index on df2 argSpecs; }; ## end makeArgSpecs.singleIntegerKey.optionalOneToOne() ## cross of various input sizes and key overlaps sizes <- c(1e1L,1e3L,1e6L); overlaps <- c(0.99,0.5,0.01); system.time({ res <- testGrid(makeArgSpecs.singleIntegerKey.optionalOneToOne,sizes,overlaps); }); ## user system elapsed ## 22024.65 12308.63 34493.19
위 결과의 로그-로그 플롯을 생성하는 코드를 작성했습니다. 각 겹침 백분율에 대해 별도의 플롯을 생성했습니다. 약간 어수선하지만 모든 솔루션 유형과 조인 유형이 동일한 플롯에 표현되는 것을 좋아합니다.
개별 pch 기호로 그려진 각 솔루션/조인 유형 조합에 대한 부드러운 곡선을 표시하기 위해 스플라인 보간법을 사용했습니다. 조인 유형은 내부에 점, 왼쪽 및 오른쪽에 대해 왼쪽 및 오른쪽 꺾쇠 괄호를 사용하고 전체에 다이아몬드를 사용하여 pch 기호로 캡처됩니다. 솔루션 유형은 범례에 표시된 대로 색상으로 캡처됩니다.
plotRes <- function(res,titleFunc,useFloor=F) { solTypes <- setdiff(names(res),c('size','overlap','joinType','unit')); ## derive from res normMult <- c(microseconds=1e-3,milliseconds=1); ## normalize to milliseconds joinTypes <- getJoinTypes(); cols <- c(merge='purple',data.table.unkeyed='blue',data.table.keyed='#00DDDD',sqldf.unindexed='brown',sqldf.indexed='orange',plyr='red',dplyr='#00BB00',in.place='magenta'); pchs <- list(inner=20L,left='<',right='>',full=23L); cexs <- c(inner=0.7,left=1,right=1,full=0.7); NP <- 60L; ord <- order(decreasing=T,colMeans(res[res$size==max(res$size),solTypes],na.rm=T)); ymajors <- data.frame(y=c(1,1e3),label=c('1ms','1s'),stringsAsFactors=F); for (overlap in unique(res$overlap)) { x1 <- res[res$overlap==overlap,]; x1[solTypes] <- x1[solTypes]*normMult[x1$unit]; x1$unit <- NULL; xlim <- c(1e1,max(x1$size)); xticks <- 10^seq(log10(xlim[1L]),log10(xlim[2L])); ylim <- c(1e-1,10^((if (useFloor) floor else ceiling)(log10(max(x1[solTypes],na.rm=T))))); ## use floor() to zoom in a little more, only sqldf.unindexed will break above, but xpd=NA will keep it visible yticks <- 10^seq(log10(ylim[1L]),log10(ylim[2L])); yticks.minor <- rep(yticks[-length(yticks)],each=9L)*1:9; plot(NA,xlim=xlim,ylim=ylim,xaxs='i',yaxs='i',axes=F,xlab='size (rows)',ylab='time (ms)',log='xy'); abline(v=xticks,col='lightgrey'); abline(h=yticks.minor,col='lightgrey',lty=3L); abline(h=yticks,col='lightgrey'); axis(1L,xticks,parse(text=sprintf('10^%d',as.integer(log10(xticks))))); axis(2L,yticks,parse(text=sprintf('10^%d',as.integer(log10(yticks)))),las=1L); axis(4L,ymajors$y,ymajors$label,las=1L,tick=F,cex.axis=0.7,hadj=0.5); for (joinType in rev(joinTypes)) { ## reverse to draw full first, since it's larger and would be more obtrusive if drawn last x2 <- x1[x1$joinType==joinType,]; for (solType in solTypes) { if (any(!is.na(x2[[solType]]))) { xy <- spline(x2$size,x2[[solType]],xout=10^(seq(log10(x2$size[1L]),log10(x2$size[nrow(x2)]),len=NP))); points(xy$x,xy$y,pch=pchs[[joinType]],col=cols[solType],cex=cexs[joinType],xpd=NA); }; ## end if }; ## end for }; ## end for ## custom legend ## due to logarithmic skew, must do all distance calcs in inches, and convert to user coords afterward ## the bottom-left corner of the legend will be defined in normalized figure coords, although we can convert to inches immediately leg.cex <- 0.7; leg.x.in <- grconvertX(0.275,'nfc','in'); leg.y.in <- grconvertY(0.6,'nfc','in'); leg.x.user <- grconvertX(leg.x.in,'in'); leg.y.user <- grconvertY(leg.y.in,'in'); leg.outpad.w.in <- 0.1; leg.outpad.h.in <- 0.1; leg.midpad.w.in <- 0.1; leg.midpad.h.in <- 0.1; leg.sol.w.in <- max(strwidth(solTypes,'in',leg.cex)); leg.sol.h.in <- max(strheight(solTypes,'in',leg.cex))*1.5; ## multiplication factor for greater line height leg.join.w.in <- max(strheight(joinTypes,'in',leg.cex))*1.5; ## ditto leg.join.h.in <- max(strwidth(joinTypes,'in',leg.cex)); leg.main.w.in <- leg.join.w.in*length(joinTypes); leg.main.h.in <- leg.sol.h.in*length(solTypes); leg.x2.user <- grconvertX(leg.x.in+leg.outpad.w.in*2+leg.main.w.in+leg.midpad.w.in+leg.sol.w.in,'in'); leg.y2.user <- grconvertY(leg.y.in+leg.outpad.h.in*2+leg.main.h.in+leg.midpad.h.in+leg.join.h.in,'in'); leg.cols.x.user <- grconvertX(leg.x.in+leg.outpad.w.in+leg.join.w.in*(0.5+seq(0L,length(joinTypes)-1L)),'in'); leg.lines.y.user <- grconvertY(leg.y.in+leg.outpad.h.in+leg.main.h.in-leg.sol.h.in*(0.5+seq(0L,length(solTypes)-1L)),'in'); leg.sol.x.user <- grconvertX(leg.x.in+leg.outpad.w.in+leg.main.w.in+leg.midpad.w.in,'in'); leg.join.y.user <- grconvertY(leg.y.in+leg.outpad.h.in+leg.main.h.in+leg.midpad.h.in,'in'); rect(leg.x.user,leg.y.user,leg.x2.user,leg.y2.user,col='white'); text(leg.sol.x.user,leg.lines.y.user,solTypes[ord],cex=leg.cex,pos=4L,offset=0); text(leg.cols.x.user,leg.join.y.user,joinTypes,cex=leg.cex,pos=4L,offset=0,srt=90); ## srt rotation applies *after* pos/offset positioning for (i in seq_along(joinTypes)) { joinType <- joinTypes[i]; points(rep(leg.cols.x.user[i],length(solTypes)),ifelse(colSums(!is.na(x1[x1$joinType==joinType,solTypes[ord]]))==0L,NA,leg.lines.y.user),pch=pchs[[joinType]],col=cols[solTypes[ord]]); }; ## end for title(titleFunc(overlap)); readline(sprintf('overlap %.02f',overlap)); }; ## end for }; ## end plotRes() titleFunc <- function(overlap) sprintf('R merge solutions: single-column integer key, 0..1:0..1 cardinality, %d%% overlap',as.integer(overlap*100)); plotRes(res,titleFunc,T);
다음은 키 열의 수와 유형, 카디널리티와 관련하여 더 강력한 두 번째 대규모 벤치마크입니다. 0..*:0..* ) 문자 1개, 정수 1개, 논리 1개 등 세 개의 키 열을 사용합니다. (일반적으로 부동 소수점 비교 복잡성으로 인해 이중 또는 복소수 값으로 키 열을 정의하는 것은 권장되지 않으며 기본적으로 아무도 원시 유형을 사용하지 않으며 키 열에는 훨씬 적기 때문에 키에 해당 유형을 포함하지 않았습니다. 또한 정보를 위해 처음에는 POSIXct 키 열을 포함하여 4개의 키 열을 사용하려고 했지만 POSIXct 유형은 부동 소수점 비교 이상으로 인해 어떤 이유로 sqldf.indexed , 그래서 제거했습니다.)
makeArgSpecs.assortedKey.optionalManyToMany <- function(size,overlap,uniquePct=75) { ## number of unique keys in df1 u1Size <- as.integer(size*uniquePct/100); ## (roughly) divide u1Size into bases, so we can use expand.grid() to produce the required number of unique key values with repetitions within individual key columns ## use ceiling() to ensure we cover u1Size; will truncate afterward u1SizePerKeyColumn <- as.integer(ceiling(u1Size^(1/3))); ## generate the unique key values for df1 keys1 <- expand.grid(stringsAsFactors=F, idCharacter=replicate(u1SizePerKeyColumn,paste(collapse='',sample(letters,sample(4:12,1L),T))), idInteger=sample(u1SizePerKeyColumn), idLogical=sample(c(F,T),u1SizePerKeyColumn,T) ##idPOSIXct=as.POSIXct('2016-01-01 00:00:00','UTC')+sample(u1SizePerKeyColumn) )[seq_len(u1Size),]; ## rbind some repetitions of the unique keys; this will prepare one side of the many-to-many relationship ## also scramble the order afterward keys1 <- rbind(keys1,keys1[sample(nrow(keys1),size-u1Size,T),])[sample(size),]; ## common and unilateral key counts com <- as.integer(size*overlap); uni <- size-com; ## generate some unilateral keys for df2 by synthesizing outside of the idInteger range of df1 keys2 <- data.frame(stringsAsFactors=F, idCharacter=replicate(uni,paste(collapse='',sample(letters,sample(4:12,1L),T))), idInteger=u1SizePerKeyColumn+sample(uni), idLogical=sample(c(F,T),uni,T) ##idPOSIXct=as.POSIXct('2016-01-01 00:00:00','UTC')+u1SizePerKeyColumn+sample(uni) ); ## rbind random keys from df1; this will complete the many-to-many relationship ## also scramble the order afterward keys2 <- rbind(keys2,keys1[sample(nrow(keys1),com,T),])[sample(size),]; ##keyNames <- c('idCharacter','idInteger','idLogical','idPOSIXct'); keyNames <- c('idCharacter','idInteger','idLogical'); ## note: was going to use raw and complex type for two of the non-key columns, but data.table doesn't seem to fully support them argSpecs <- list( default=list(copySpec=1:2,args=list( df1 <- cbind(stringsAsFactors=F,keys1,y1=sample(c(F,T),size,T),y2=sample(size),y3=rnorm(size),y4=replicate(size,paste(collapse='',sample(letters,sample(4:12,1L),T)))), df2 <- cbind(stringsAsFactors=F,keys2,y5=sample(c(F,T),size,T),y6=sample(size),y7=rnorm(size),y8=replicate(size,paste(collapse='',sample(letters,sample(4:12,1L),T)))), keyNames )), data.table.unkeyed=list(copySpec=1:2,args=list( as.data.table(df1), as.data.table(df2), keyNames )), data.table.keyed=list(copySpec=1:2,args=list( setkeyv(as.data.table(df1),keyNames), setkeyv(as.data.table(df2),keyNames) )) ); ## prepare sqldf initSqldf(); sqldf(paste0('create index df1_key on df1(',paste(collapse=',',keyNames),');')); ## upload and create an sqlite index on df1 sqldf(paste0('create index df2_key on df2(',paste(collapse=',',keyNames),');')); ## upload and create an sqlite index on df2 argSpecs; }; ## end makeArgSpecs.assortedKey.optionalManyToMany() sizes <- c(1e1L,1e3L,1e5L); ## 1e5L instead of 1e6L to respect more heavy-duty inputs overlaps <- c(0.99,0.5,0.01); solTypes <- setdiff(getSolTypes(),'in.place'); system.time({ res <- testGrid(makeArgSpecs.assortedKey.optionalManyToMany,sizes,overlaps,solTypes); }); ## user system elapsed ## 38895.50 784.19 39745.53
위에 제공된 동일한 플로팅 코드를 사용하여 결과 플롯:
titleFunc <- function(overlap) sprintf('R merge solutions: character/integer/logical key, 0..*:0..* cardinality, %d%% overlap',as.integer(overlap*100)); plotRes(res,titleFunc,F);
출처 : http:www.stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right