CODEWITHSUNDEEP
r
Jklein
Jklein

Reputation: 101

Create new vector using another as an index to select data from a data frame

I have two data frames and am trying to create a vector composed of selections from the columns of a data frame using another vector as an index.

Here are two data frames as an illustrative sample:

PIN <- c("case1", "case2", "case3", "case4", "case5")
TS <- c("TS1", "TS4", "TS5", "TS1", "TS2")
index <- data.frame(PIN, TS)

PIN <- c("case1", "case2", "case3", "case4", "case5")
TS1 <- c(1, 2, 3, 4, 5)
TS2 <- c(6, 7, 8, 9, 10)
TS3 <- c(11, 12, 13, 14, 15)
TS4 <- c(16, 17, 18, 19, 20)
TS5 <- c(21, 22, 23, 24, 25)
data <- data.frame(PIN, TS1, TS2, TS3, TS4, TS5)

And the vector I'd like to create as an output is:

c(1, 17, 23, 4, 10)

Thanks!

Upvotes: 1

Views: 450

Answers (3)

akrun
akrun

Reputation: 887173

We can use vectorized option to extract the elements using row/column indexing. Set the row names of the 'data' with the first column while subsetting it to remove the first column (data[-1]) and use the 'index' dataset to extract the elements with row/column names

`row.names<-`(data[-1], data[,1])[as.matrix(index)]
#[1]  1 17 23  4 10

Or another option is tidyverse

library(tidyverse)
data %>%
    gather(key="TS", value, -PIN) %>% 
    right_join(., index) %>% 
   .$value
#[1]  1 17 23  4 10

Upvotes: 0

acylam
acylam

Reputation: 18681

Not as elegant, but works: 

diag(as.matrix(data[as.character(index$TS)]))
# [1]  1 17 23  4 10

Upvotes: 0

d.b
d.b

Reputation: 32548

sapply(1:NROW(index), function(i) data[i, as.character(index$TS[i])])
#[1]  1 17 23  4 10

OR

as.numeric(data[cbind(1:NROW(index), match(as.character(index$TS), names(data)))])
#[1]  1 17 23  4 10

Upvotes: 3

Related Questions