This is a continuation of the question that I originally posted at http://r.789695.n4.nabble.com/subset-between-data-table-list-and-single-data-table-object-tp4673202.html . Matthew suggested that I post my question here, so I'm doing it now.
This is my input below:
library(data.table) library(pracma) # for the interp1 function tempbigdata1 <- data.table(c(14.80, 14.81, 14.82), c(7900, 7920, 7930), c("02437100", "02437100", "02437100")) tempbigdata2 <- data.table(c(9.98, 9.99, 10.00), c(816, 819, 821), c("02446500", "02446500", "02446500")) tempbigdata3 <- data.table(c(75.65, 75.66, 75.67), c(23600, 23700, 23800), c("02467000", "02467000", "02467000")) tempsbigdata <- rbind(tempbigdata1, tempbigdata2, tempbigdata3) setnames(tempsbigdata,c("y", "x", "site_no")) setkey(tempsbigdata, site_no) tempsbigdata yx site_no 1: 14.80 7900 02437100 2: 14.81 7920 02437100 3: 14.82 7930 02437100 4: 9.98 816 02446500 5: 9.99 819 02446500 6: 10.00 821 02446500 7: 75.65 23600 02467000 8: 75.66 23700 02467000 9: 75.67 23800 02467000 aimsmall <- data.table(c("02437100", "02446500", "02467000"), c(3882.65, 819.82, 23742.37), c(1830.0, 382.0, 10400.0)) setnames(aimsmall,c("site_no", "mean", "p50")) setkey(aimsmall, site_no) aimsmall site_no mean p50 1: 02437100 3882.65 1830 2: 02446500 819.82 382 3: 02467000 23742.37 10400
I use this code to generate the interpolated tempsbigdata$y using aimsmall$mean values using site_no :
meanpre <- tempsbigdata[,if(aimsmall$mean > min(tempsbigdata$x){ interp1(tempsbigdata$x, tempsbigdata$y, xi = aimsmall$mean, method ="linear")},by=site_no]
This is the result of meanpre function, but it is not true.
meanpre site_no V1 1: 02437100 12.07599 2: 02437100 9.99410 3: 02437100 19.56813 4: 02446500 12.07599 5: 02446500 9.99410 6: 02446500 19.56813 7: 02467000 12.07599 8: 02467000 9.99410 9: 02467000 19.56813
This is what I would like to receive:
meanpre site_no V1 1: 02446500 9.99 2: 02467000 75.66
Any suggestions? Thanks.
UPDATE 1:
Hugh, I used an approximate function in the past, and this is not accurate for my data; however, the interp1 function in pracma is accurate. The mean and p50 columns in aimsmall and the x values in tempsbigdata are the discharge values. y in tempsbigdata represent the height of the image. I use the interp1 function to determine the appropriate meter height or y value for bit values or mean (and p50 ).
Frank, thank you for the tip and suggested code. This is the result for your suggested code:
tempsbigdata[aimsmall][,if(mean[1] > min(x)){interp1(tempsbigdata$x,tempsbigdata$y, xi = aimsmall$mean, method ="linear")},by=site_no] site_no V1 1: 02446500 12.07599 2: 02446500 9.99410 3: 02446500 75.66424 4: 02467000 12.07599 5: 02467000 9.99410 6: 02467000 75.66424
When I run the following code, I get the result below:
interp1(tempsbigdata$x, tempsbigdata$y, xi = aimsmall$mean, method ="linear") [1] 12.07599 9.99410 75.66424
Is there any way to get this in return? Thanks.
site_no V1 1: 02446500 9.99 2: 02467000 75.66
UPDATE 2
Frank, thanks, and I added the code to make it easier to have data in R. Pracma is a package of numerical methods of the R method that were ported from GNU Octave [like MATLAB (R)] to R. The interp1 function is a one-dimensional interpolation function.
Frank, that was great (your last comment about the R code for "do stuff"):
tempsbigdata[aimsmall][,if(mean[1] > min(x)){interp1(x, y, xi = mean[1], method ="linear")},by=site_no] site_no V1 1: 02446500 9.99410 2: 02467000 75.66424