R：为每一行选择不同列的值

Question

我有一个大的数据框（这里仅保留了前5行），由多个天线的无线电遥测读数组成。通常每隔几周就会有10,000多行这样的数据。

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")

S1、S2…S6 表示来自不同天线的信号值，N1、N2…N6 表示相应的噪声值。

我试图提取每行中最大和次大的信号值及其相应的噪声值。我可以获取信号值，以及只有信号列的“索引”。

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

我卡在了创建两个新列的过程中，这两列需要提取出具有第一和第二强信号的天线的噪声值。我原本希望使用每个天线的位置索引（1-6）来提取正确的噪声值，但是它并没有起作用。它确实提取了正确的值，但是重复了与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

最强和次强的天线在这里不会改变，但随着被追踪动物的移动，数据中会发生变化。

我感觉自己可能忽略了一些简单的东西，但我想不出来。非常感谢您能提供的任何帮助。

- tlyons253

Answer 1

# 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]])

- Jota

---

太棒了！非常感谢！ - tlyons253

Answer 2

也许你可以尝试一下：

dat1 <- dat[,grep("S", colnames(dat))]
Strongest <-  do.call(`pmax`, dat1)
 Strongest
 #[1] -12516 -11485 -10871 -10900 -11452


indx1 <-which(dat1==Strongest,arr.ind=T)
indx11 <- unique(indx1[,2])
SecondStrongest <- do.call(`pmax`, dat1[,-indx])
SecondStrongest
 #[1] -12661 -12168 -10982 -11112 -12164


indx2 <- which(SecondStrongest ==dat1,arr.ind=TRUE)

dat2 <- dat[,grep("N", colnames(dat))]
MatchingNoise <- dat2[indx1]
MatchingSecondNoise <- dat2[indx2]

- akrun