A: Select the value of a different column for each row

advertisements

I have a large data frame (cut down to first 5 rows here) comprised of radio-telemetry readings from multiple antennas. Normally there are 10,000+ rows of data like this every couple of weeks.

structure(list(freq.id = c(13, 13, 13, 13, 13), DT = structure(c(1393835337,
1393921137, 1393879437, 1393881387, 1393920987), class = c("POSIXct",
"POSIXt"), tzone = "America/Bogota"), S1 = c(-13624L, -12866L,
-13291L, -13415L, -13002L), N1 = c(-13969L, -13824L, -13868L,
-13881L, -13911L), S2 = c(-14114L, -14026L, -13957L, -13969L,
-14052L), N2 = c(-14211L, -14238L, -14168L, -14148L, -14211L),
S3 = c(-13245L, -13113L, -12801L, -12860L, -13133L), N3 = c(-13816L,
-13832L, -13878L, -14001L, -13706L), S4 = c(-13479L, -12702L,
-12388L, -12501L, -12692L), N4 = c(-13872L, -13820L, -13992L,
-13905L, -13798L), S5 = c(-12516L, -11485L, -10871L, -10900L,
-11452L), N5 = c(-13884L, -13995L, -13804L, -13840L, -13929L
), S6 = c(-12661L, -12168L, -10982L, -11112L, -12164L), N6 = c(-13911L,
-13914L, -13078L, -13778L, -13911L), PW = c(20L, 20L, 20L,
20L, 21L), PI = c(1078L, 1078L, 1080L, 2156L, 1078L), aru.unk = c(2072L,
2058L, 2014L, 2052L, 2047L), msrfreq = c(164421600L, 164421700L,
164421400L, 164421300L, 164421800L), TOWERID = structure(c(1L,
1L, 1L, 1L, 1L), .Label = c("TOWER4", "TOWER5", "TOWER6",
"TOWER7"), class = "factor"), prog.freq = structure(c(9L,
9L, 9L, 9L, 9L), .Label = c("162.7920", "162.9774", "163.0780",
"163.6804", "163.8600", "164.0309", "164.2930", "164.3950",
"164.4220", "164.4350", "164.5040", "164.5430", "164.5620",
"164.7026", "164.7840", "164.8230", "164.8430", "164.9338",
"165.5000"), class = "factor")), .Names = c("freq.id", "DT",
"S1", "N1", "S2", "N2", "S3", "N3", "S4", "N4", "S5", "N5", "S6",
"N6", "PW", "PI", "aru.unk", "msrfreq", "TOWERID", "prog.freq"
), row.names = 40615:40619, class = "data.frame")

Columns S1,S2...S6 are signal values from different antennas and N1,N2...N6 are corresponding noise values

I am trying to pull out the largest and second largest signal values for each row and their corresponding noise values. I can get the the signal values, as well as it's "index" of just the columns of signal.

maxn <- function(n) function(x) order(x, decreasing = TRUE)[n]

mydata$strongest<-apply(mydata[,c(3,5,7,9,11,13)],1,function(x) x[maxn(1)(x)])
#columns 3,5,6,11,13 are the subset of columns containing signal values

mydata$secondstrongest<-apply(mydata[,c(3,5,7,9,11,13)],1,function(x) x[maxn(2)(x)])

mydata$strongestantenna<-apply(mydata[,c(3,5,7,9,11,13)],1,maxn(1))
# returns 5 because in the first 5 rows, the strongest signal is the 5th antenna (S5)

mydata$secondstrongestantenna<-apply(mydata[,c(3,5,7,9,11,13)],1,maxn(2))
#returns a 6

I'm stuck trying to create 2 new columns that extract the noise values for the antennas that have the 1st and 2nd strongest signals. I was hoping to use the place index (1-6) for each antenna to pull out the correct noise values like this, but it isn't working. It pulls the correct value, but repeats it the same number of times as the value of mydata$strongantenna

mydata$strongantennanoise<-mydata[c(4,6,8,10,12,14)][mydata$strongestantenna],
#Columns 4,6,8,10,and 12  are the noise values

The strongest and second strongest antennas don't change here, but do in the data, as the animal being tracked moves around.

I feel like I'm overlooking something simple, but I can't figure it out. I appreciate whatever help you can give.


# Get names of the strongest and second strongest antennas by row:
strongest <- apply(mydata[,c(3,5,7,9,11,13)],1, function(x) names(x[maxn(1)(x)]))
secondstrongest <- apply(mydata[,c(3,5,7,9,11,13)],1, function(x) names(x[maxn(2)(x)]))

# Get column index for associated noise columns
biggest.noise.col <- sapply(seq_along(mydata[,1]),
                     function(x) which(colnames(mydata) == strongest[x]) +1)
second.biggest.noise.col <- sapply(seq_along(mydata[,1]),
                     function(x) which(colnames(mydata) == secondstrongest[x]) +1)

# Use the indices to extract relevant noise values:
mydata$strongestantennanoise <- sapply(seq_along(mydata[,1]),
                     function(x) mydata[x, biggest.noise.col[x]])
mydata$secondstrongestantennanoise <- sapply(seq_along(mydata[,1]),
                     function(x) mydata[x, second.biggest.noise.col[x]])