CODEWITHSUNDEEP
rindexing
Troy Hansen
Troy Hansen

Reputation: 21

Replicating Excel =INDEX() function in R

To begin, I am still pretty new to understand how R works. I know how to easily solve this question in Excel, but would like to know an equivalent solution method in R.

In R, I have a data frame with the below structure:

      Type      Type1      Type2     Type3     Type4     Type5
1        3  -9.697640 21.0876111 21.201925 20.306722 18.431434
2        4  -9.697640 21.0876111 21.201925 20.306722 18.431434
3        5  -9.697640 21.0876111 21.201925 20.306722 18.431434
4        1  -9.697640 21.0876111 21.201925 20.306722 18.431434
5        2  -9.697640 21.0876111 21.201925 20.306722 18.431434

My goal is simply to add a final column that uses Type to determine which of the other 5 columns to pull the value from. So for row 1, the value under Type3 would be selected, while row 2 would select the value from Type4.

To do this in Excel I can simply use =INDEX(B1:F1,A1). I've done this before using a loop in R, but would was hoping to find a more efficient method.

Upvotes: 2

Views: 396

Answers (3)

Jilber Urbina
Jilber Urbina

Reputation: 61164

df$result <- lapply(1:nrow(df), function(i) {
  df[i,df$Type[i]+1]
})

df
  Type    Type1    Type2    Type3    Type4    Type5   result
1    3 -9.69764 21.08761 21.20192 20.30672 18.43143 21.20192
2    4 -9.69764 21.08761 21.20192 20.30672 18.43143 20.30672
3    5 -9.69764 21.08761 21.20192 20.30672 18.43143 18.43143
4    1 -9.69764 21.08761 21.20192 20.30672 18.43143 -9.69764
5    2 -9.69764 21.08761 21.20192 20.30672 18.43143 21.08761

Upvotes: 0

IceCreamToucan
IceCreamToucan

Reputation: 28695

A solution with no loops or applys

df$newcol <- df[cbind(seq(nrow(df)), 1 + df$Type)]
df
#   Type    Type1    Type2    Type3    Type4    Type5   newcol
# 1    3 -9.69764 21.08761 21.20192 20.30672 18.43143 21.20192
# 2    4 -9.69764 21.08761 21.20192 20.30672 18.43143 20.30672
# 3    5 -9.69764 21.08761 21.20192 20.30672 18.43143 18.43143
# 4    1 -9.69764 21.08761 21.20192 20.30672 18.43143 -9.69764
# 5    2 -9.69764 21.08761 21.20192 20.30672 18.43143 21.08761

The object inside the brackets tells R which row (left) and column (right) to choose for each row of newcol

cbind(seq(nrow(df)), 1 + df$Type)
#      [,1] [,2]
# [1,]    1    4
# [2,]    2    5
# [3,]    3    6
# [4,]    4    2
# [5,]    5    3

Upvotes: 1

Maurits Evers
Maurits Evers

Reputation: 50688

Here is one possibility using apply:

df$final_column <- apply(df, 1, function(x) x[x["Type"] + 1]);
df;
#  Type    Type1    Type2    Type3    Type4    Type5 final_column
#1    3 -9.69764 21.08761 21.20192 20.30672 18.43143     21.20192
#2    4 -9.69764 21.08761 21.20192 20.30672 18.43143     20.30672
#3    5 -9.69764 21.08761 21.20192 20.30672 18.43143     18.43143
#4    1 -9.69764 21.08761 21.20192 20.30672 18.43143     -9.69764
#5    2 -9.69764 21.08761 21.20192 20.30672 18.43143     21.08761

Sample data

df <- read.table(text =
    "      Type      Type1      Type2     Type3     Type4     Type5
1        3  -9.697640 21.0876111 21.201925 20.306722 18.431434
2        4  -9.697640 21.0876111 21.201925 20.306722 18.431434
3        5  -9.697640 21.0876111 21.201925 20.306722 18.431434
4        1  -9.697640 21.0876111 21.201925 20.306722 18.431434
5        2  -9.697640 21.0876111 21.201925 20.306722 18.431434", header = T)

Upvotes: 0

Related Questions