Brian Stamper February 2016

R data.frame get value from variable which is selected by another variable, vectorized

I have data that comes to me with many similar variables, with an additional variable which indicates which one of those similar variables I really want. Using a loop I can look up the correct value, but the data is large, the loop is slow, and it seems like this should be vectorizable. I just haven't figured out how.

EDIT: The selected variable will be used as a new variable in the same data frame, so order matters. There are many other variables not shown in the example given below.

Example data set:

set.seed(0)
df <- data.frame(yr1 = sample(1000:1100, 8),
                 yr2 = sample(2000:2100, 8),
                 yr3 = sample(3000:3100, 8),
                 yr4 = sample(4000:4100, 8),
                 var = paste0("yr", sample(1:4, 8, replace = TRUE)))
# df
# 
#    yr1  yr2  yr3  yr4 var
# 1 1090 2066 3050 4012 yr3
# 2 1026 2062 3071 4026 yr2
# 3 1036 2006 3098 4038 yr1
# 4 1056 2020 3037 4001 yr4
# 5 1088 2017 3075 4037 yr3
# 6 1019 2065 3089 4083 yr4
# 7 1085 2036 3020 4032 yr1
# 8 1096 2072 3061 4045 yr3

This loop method does the trick, but is slow and awkward:

ycode <- character(nrow(df))
for(i in 1:nrow(df)) {
 ycode[i] <- df[i, df$var[i]]
}
df$ycode <- ycode

# df
#    yr1  yr2  yr3  yr4 var ycode
# 1 1090 2066 3050 4012 yr3  3050
# 2 1026 2062 3071 4026 yr2  2062
# 3 1036 2006 3098 4038 yr1  1036
# 4 1056 2020 3037 4001 yr4  4001
# 5 1088 2017 3075 4037 yr3  3075
# 6 1019 2065 3089 4083 yr4  4083
# 7 1085 2036 3020 4032 yr1  1085
# 8 1096 2072 3061 4045 yr3  3061 

It seems like I should be able to vectorize this, like so:

df$ycode <- df[, df$var]

But I find the result surprising:

#    yr1  yr2  yr3  yr4 var ycode.yr3 ycode.yr2 ycode.yr1 ycode.yr4 ycode.yr3.1 ycode.yr4.1 ycode.yr1.1 ycode.yr3.2
# 1 1090 2066 3050 4012 yr3      3050      2066      1090      4012        3050        4012        1090        

Answers


akrun February 2016

We can use the row/column indexing. It should be fast compared to the loop.

 df[-ncol(df)][cbind(1:nrow(df),match(df$var,head(names(df),-1)))]
 #[1] 3050 2062 1036 4001 3075 4083 1085 3061

Just for some diversity, a data.table solution would be (should be slow compared to the indexing above). Convert the 'data.frame' to 'data.table' (setDT(df)), grouped by the sequence of rows, we get the value of 'var' after converting to character class.

library(data.table)
setDT(df)[, ycode := get(as.character(var)) , 1:nrow(df)]
df
#    yr1  yr2  yr3  yr4 var ycode
#1: 1090 2066 3050 4012 yr3  3050
#2: 1026 2062 3071 4026 yr2  2062
#3: 1036 2006 3098 4038 yr1  1036
#4: 1056 2020 3037 4001 yr4  4001
#5: 1088 2017 3075 4037 yr3  3075
#6: 1019 2065 3089 4083 yr4  4083
#7: 1085 2036 3020 4032 yr1  1085
#8: 1096 2072 3061 4045 yr3  3061


CPhil February 2016

I like the syntax of dplyr and tidyr:

df$ID = 1:nrow(df)
library(dplyr)
library(tidyr)

df %>% 
    gather(year, value, yr1:yr4) %>% 
    filter(var == year) %>% 
    select(-year) %>%
    spread(year, value) %>%
    arrange(ID)


micstr February 2016

I noticed this answer from @josliber see (http://stackoverflow.com/a/30279903/4606130) when trying to work on a data.table solution and it seems fast:

df[cbind(seq(df$var), df$var)]

[1] "3050" "2062" "1036" "4001" "3075" "4083" "1085" "3061"


Ilari Scheinin February 2016

One more vectorized option is to use a nested ifelse(). It has the benefit of being, at least in my opinion, relatively readable compared to other solutions. But the obvious downside of not scaling when the number of variables grows.

ifelse(df$var == "yr1", df$yr1,
  ifelse(df$var == "yr2", df$yr2,
  ifelse(df$var == "yr3", df$yr3,
  ifelse(df$var == "yr4", df$yr4, NA))))

[1] 3050 2062 1036 4001 3075 4083 1085 3061

Post Status

Asked in February 2016
Viewed 1,312 times
Voted 11
Answered 4 times

Search




Leave an answer