how to use 'which' inside of 'apply'?
Your original code works far faster when the input
is a matrix that when it is a data.frame. Selecting
a row from a data.frame is a very slow operation,
selecting a row from a matrix is quick. Modifying
a row or a single element in a data.frame is even
worse compared to do it on a matrix. I compared your
original code:
f0 <- function (df)
{
for (i in seq_len(nrow(df))) {
t = which(df[i, 2:24] > df[i, 25])
r = min(t)
df[i, 26] = (r - 1) * 16 + 1
}
df[, 26] # for now just return the computed column
}
to one that converts relevant parts of the data.frame
df to matrices or vectors before doing the loop over
rows:
f0.a <- function (df)
{
thold <- df[, "thold"]
tmp <- as.matrix(df[,2:24])
ans <- df[,26]
for (i in seq_len(nrow(df))) {
t = which(tmp[i,]>thold[i])
r = min(t)
ans[i] = (r-1)*16+1
}
# df[,26] <- ans
ans
}
On a 10,000 row data.frame f0 took 47.950 seconds and f0.a took
0.140 seconds. (f1, below, took 0.012 seconds.)
Bill Dunlap
Spotfire, TIBCO Software
wdunlap tibco.com
-----Original Message-----
From: William Dunlap
Sent: Monday, October 17, 2011 11:00 AM
To: Nathan Piekielek
Cc: r-help at r-project.org
Subject: RE: [R] how to use 'which' inside of 'apply'?
Try vectorizing it a bit by looping over the columns.
E.g.,
f1 <- function (df)
{
# loop (backwards) over all columns in df whose
# names start with "D" to find the earliest one
# that is bigger than column "thold". I tested with
# df being a data.frame but a matrix should work too.
i <- rep(NA_character_, nrow(df))
colNames <- grep(value = TRUE, "^D", colnames(df))
for (colName in rev(colNames)) {
i[df[, colName] > df[, "thold"]] <- colName
}
# convert column name "D<number>" to <number>.
doy <- as.numeric(sub("^D", "", i))
doy
}
> f1(a)
[1] 129 145 129 177 177 177
You could also try looping over rows with something like
findInterval. If there are far fewer columns than rows
then looping over columns is generally faster.
Your sample data.frame I called 'a' and in copy-and-pastable
form (from dput()) is
a <- structure(list(pt = c(39177L, 39178L, 39164L, 39143L, 39144L,
39146L), D1 = c(0L, 0L, 0L, 0L, 0L, 0L), D17 = c(0L, 0L, 0L,
0L, 0L, 0L), D33 = c(0L, 0L, 0L, 0L, 0L, 0L), D49 = c(0L, 0L,
0L, 0L, 0L, 0L), D65 = c(0L, 0L, 0L, 0L, 0L, 0L), D81 = c(0L,
0L, 0L, 0L, 0L, 0L), D97 = c(0L, 0L, 0L, 0L, 0L, 0L), D113 = c(0L,
0L, 0L, 0L, 0L, 0L), D129 = c(0.4336, 0.342, 0.483, 0.3088, 0.339,
0.4232), D145 = c(0.4754, 0.4543, 0.4943, 0.3753, 0.4152, 0.4442
), D161 = c(0.5340667, 0.5397666, 0.5740333, 0.4466, 0.5147,
0.5084), D177 = c(0.5927334, 0.6252333, 0.6537667, 0.5179, 0.6142,
0.5726), D193 = c(0.6514, 0.7107, 0.7335, 0.5892, 0.7137, 0.6368
), D209 = c(0.6966, 0.7123, 0.6255, 0.6468, 0.6914, 0.5896),
D225 = c(0.59, 0.5591, 0.6228, 0.4794, 0.6381, 0.4703), D241 = c(0.5583,
0.4617, 0.5255, 0.4411, 0.5704, 0.4936), D257 = c(0.5676,
0.4206, 0.5436, 0.4307, 0.5619, 0.5353), D273 = c(0.4682,
0.3867, 0.5541, 0.3632, 0.5347, 0.4067), D289 = c(0.35115,
0.2578, 0.46195, 0.34355, 0.4976, 0.39685), D305 = c(0.2341,
0.1289, 0.3698, 0.3239, 0.4605, 0.387), D321 = c(0.11705,
0, 0.1849, 0, 0, 0), D337 = c(0L, 0L, 0L, 0L, 0L, 0L), D353 = c(0L,
0L, 0L, 0L, 0L, 0L), thold = c(0.406825, 0.4206, 0.4592,
0.4778, 0.52635, 0.5119), doy = c(0L, 0L, 0L, 0L, 0L, 0L)), .Names = c("pt",
"D1", "D17", "D33", "D49", "D65", "D81", "D97", "D113", "D129",
"D145", "D161", "D177", "D193", "D209", "D225", "D241", "D257",
"D273", "D289", "D305", "D321", "D337", "D353", "thold", "doy"
), class = "data.frame", row.names = c("1", "2", "3", "4", "5",
"6"))
Bill Dunlap
Spotfire, TIBCO Software
wdunlap tibco.com
-----Original Message----- From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf Of R. Michael Weylandt Sent: Monday, October 17, 2011 10:32 AM To: Nathan Piekielek Cc: r-help at r-project.org Subject: Re: [R] how to use 'which' inside of 'apply'? I think something like this should do it at a huge speed up, though I'd advise you check it to make sure it does exactly what you want: there's also nothing to guarantee that something beats the threshold, so that might make the whole thing fall apart (though I don't think it will) # Sample data df = data.frame(x = sample(5, 15,T), y = sample(5, 15, T), z = sample(5, 15,T), w = (1:5)/2 + 0.5, th = (1:5)/2, doy = rep(0,15)) wd <- which(df[,1:4] > df[,5], arr.ind = TRUE) # identify all elements that beat the threshold value by their indices wd <- wd[!duplicated(wd[,1]),] # select only the first appearance of each "row" value in wd -- this keeps the earliest column beating the threshold wd <- wd[order(wd[,"row"]),] # sort them by row df$doy = (wd[,"col"]-1)*16 + 1 # The column transform you used. Hope this helps, Michael On Mon, Oct 17, 2011 at 1:03 PM, Nathan Piekielek <npiekielek at gmail.com> wrote:
Hello R-community,
I am trying to populate a column (doy) in a large dataset with the first
column number that exceeds the value in another column (thold) using the
'apply' function.
Sample data:
? ? pt D1 D17 D33 D49 D65 D81 D97 D113 ? D129 ? D145 ? ? ?D161 ? ? ?D177
D193 ? D209 ? D225 ? D241 ? D257
1 39177 ?0 ? 0 ? 0 ? 0 ? 0 ? 0 ? 0 ? ?0 0.4336 0.4754 0.5340667 0.5927334
0.6514 0.6966 0.5900 0.5583 0.5676
2 39178 ?0 ? 0 ? 0 ? 0 ? 0 ? 0 ? 0 ? ?0 0.3420 0.4543 0.5397666 0.6252333
0.7107 0.7123 0.5591 0.4617 0.4206
3 39164 ?0 ? 0 ? 0 ? 0 ? 0 ? 0 ? 0 ? ?0 0.4830 0.4943 0.5740333 0.6537667
0.7335 0.6255 0.6228 0.5255 0.5436
4 39143 ?0 ? 0 ? 0 ? 0 ? 0 ? 0 ? 0 ? ?0 0.3088 0.3753 0.4466000 0.5179000
0.5892 0.6468 0.4794 0.4411 0.4307
5 39144 ?0 ? 0 ? 0 ? 0 ? 0 ? 0 ? 0 ? ?0 0.3390 0.4152 0.5147000 0.6142000
0.7137 0.6914 0.6381 0.5704 0.5619
6 39146 ?0 ? 0 ? 0 ? 0 ? 0 ? 0 ? 0 ? ?0 0.4232 0.4442 0.5084000 0.5726000
0.6368 0.5896 0.4703 0.4936 0.5353
? ?D273 ? ?D289 ? D305 ? ?D321 D337 D353 ? ?thold doy
1 0.4682 0.35115 0.2341 0.11705 ? ?0 ? ?0 0.406825 ? 0
2 0.3867 0.25780 0.1289 0.00000 ? ?0 ? ?0 0.420600 ? 0
3 0.5541 0.46195 0.3698 0.18490 ? ?0 ? ?0 0.459200 ? 0
4 0.3632 0.34355 0.3239 0.00000 ? ?0 ? ?0 0.477800 ? 0
5 0.5347 0.49760 0.4605 0.00000 ? ?0 ? ?0 0.526350 ? 0
6 0.4067 0.39685 0.3870 0.00000 ? ?0 ? ?0 0.511900 ? 0
For the first record in above example I would expect doy = 129.
I can achieve this with the following loop, but it takes several days to run
and there must be a more efficient solution:
for (i in (1:152000)) {
t=which(data[i,2:24]>data[i,25])
r=min(t)
data[i,26]=(r-1)*16+1
}
How do I write this using 'apply' or another function that will be more
efficient?
I have tried the following:
data$doy=apply(which(data[,2:24]>data[,25]),1,min)
Which returns the following error message:
"Error in apply(which(new[, 2:24] > new[, 25]), 1, min) :
?dim(X) must have a positive length"
Any help would be much appreciated.
Nathan
______________________________________________ R-help at r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
______________________________________________ R-help at r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.