doing a lookup function between rows and columns in r

user_14622403 0
Viewed 231

I have a dataframe that has a column, called ID in dummy data below, that has values that match to 1 of a few subsequent column names. I want to do some sort of lookup function, where it sees the value of ID, finds the matching column, and then gives a value of that row.

ID = c("A", "A", "B", "B", "B", "C", "C")
A = c(1, 2, 3, 4, 5, 6, 7)
B = c(8, 9, 10, 11, 12, 13, 14)
C = c("set1", "set2", "set1", "set2", "set3", "set1", "set2")

df = data.frame(ID, A, B, C)

#pseudo code:
#df$newrow <- for df, if ID == Column Name, then Column Name & Row Value

output I want:

  ID A  B    C newrow
1  A 1  8 set1      1
2  A 2  9 set2      2
3  B 3 10 set1     10
4  B 4 11 set2     11
5  B 5 12 set3     12
6  C 6 13 set1   set1
7  C 7 14 set2   set2

I can conceptualize doing it with a bunch of if/then statements, but the real data has a lot more columns and I want to know if there's a better way / or a specific command, to do.

rowwise dplyr r
2 Answers

You can use the following solution:

library(dplyr)

df %>%
  rowwise() %>%
  mutate(new_col = as.character(get(ID)))

# A tibble: 7 x 5
# Rowwise: 
  ID        A     B C     new_col
  <chr> <dbl> <dbl> <chr> <chr>  
1 A         1     8 set1  1      
2 A         2     9 set2  2      
3 B         3    10 set1  10     
4 B         4    11 set2  11     
5 B         5    12 set3  12     
6 C         6    13 set1  set1   
7 C         7    14 set2  set2
user_143145200

A base R option to achieve your desired result:

df$newrow <- diag(as.matrix(df[seq(nrow(df)), df$ID]))
df
#>   ID A  B    C newrow
#> 1  A 1  8 set1      1
#> 2  A 2  9 set2      2
#> 3  B 3 10 set1     10
#> 4  B 4 11 set2     11
#> 5  B 5 12 set3     12
#> 6  C 6 13 set1   set1
#> 7  C 7 14 set2   set2
user_129938610
Related