Example 2-15 Merging Ordered and Unordered ore.frame Objects
This example shows the result of merging two ordered ore.frame
objects and two unordered ore.frame
objects.
# Prepare the data. library(kernlab) data(spam) s <- spam # Create a column that has integer values. s$TS <- 1001:(1000 + nrow(s)) # Create a column that has integer values with each number repeated twice. s$USERID <- rep(351:400, each=2, len=nrow(s)) # Ensure that the database tables do not exist. ore.drop(table='SPAM_PK') ore.drop(table='SPAM_NOPK') # Create database tables. ore.create(s[,c(59:60,1:28)], table="SPAM_PK") ore.create(s[,c(59:60,1:28)], table="SPAM_NOPK") # Using a SQL statement, alter the SPAM_PK table to add a composite primary key. ore.exec("alter table SPAM_PK add constraint SPAM_PK primary key (\"USERID\",\"TS\")") # Synchronize the table to get the change to it. ore.sync(table = "SPAM_PK") # Create objects for merging data from unordered ore.frame objects. x <- SPAM_NOPK[,1:4] y <- SPAM_NOPK[,c(1,2,4,5)] m1 <- merge(x, y, by="USERID") # The merged result m1 produces a warning because it is not an ordered frame. head(m1,3) # Create objects for merging data from ordered ore.frame objects. x <- SPAM_PK[,1:4] y <- SPAM_PK[,c(1,2,4,5)] # The merged result m1 does not produce a warning now because it is an # ordered frame. m1 <- merge(x, y, by="USERID") head(m1,3)Listing for Example 2-15
R> # Prepare the data. R> library(kernlab) R> data(spam) R> s <- spam R> # Create a column that has integer values. R> s$TS <- 1001:(1000 + nrow(s)) R> # Create a column that has integer values with each number repeated twice. R> s$USERID <- rep(351:400, each=2, len=nrow(s)) R> # Ensure that the database tables do not exist. R> ore.drop(table='SPAM_PK') R> ore.drop(table='SPAM_NOPK') R> # Create database tables. R> ore.create(s[,c(59:60,1:28)], table="SPAM_PK") R> ore.create(s[,c(59:60,1:28)], table="SPAM_NOPK") R> # Uing a SQL statement, alter the SPAM_PK table to add a composite primary key. R> ore.exec("alter table SPAM_PK add constraint SPAM_PK primary key + (\"USERID\",\"TS\")") R> # Synchronize the table to get the change to it. R> ore.sync(table = "SPAM_PK") R> # Create objects for merging data from unordered ore.frame objects. R> x <- SPAM_NOPK[,1:4] R> y <- SPAM_NOPK[,c(1,2,4,5)] R> m1 <- merge(x, y, by="USERID") R> # The merged result m1 produces a warning because it is not an ordered frame. R> head(m1,3) USERID TS.x make address.x TS.y address.y all 1 351 5601 0.00 0 1001 0.64 0.64 2 351 5502 0.00 0 1001 0.64 0.64 3 351 5501 0.78 0 1001 0.64 0.64 Warning messages: 1: ORE object has no unique key - using random order 2: ORE object has no unique key - using random order R> # Create objects for merging data from ordered ore.frame objects. R> x <- SPAM_PK[,1:4] R> y <- SPAM_PK[,c(1,2,4,5)] R> # The merged result m1 does not produce a warning now because it is an R> # ordered frame. R> m1 <- merge(x, y, by="USERID") R> head(m1,3) USERID TS.x make address.x TS.y address.y all 1001|1001 351 1001 0 0.64 1001 0.64 0.64 1001|1002 351 1001 0 0.64 1002 0.28 0.50 1001|1101 351 1001 0 0.64 1101 0.00 0.00