Compare variable names with String to compute new variable

Question

I want to use variable names in R for comparison with String-Data in an id-Variable.

Table 1 shows how my sample table is currently looking and Table 2 shows what I want to achieve. In Table 2 corresponding values are copied into a new variable "newid" if the variable id matches the column name. The other values are also copied into new variables for easier calculating

id    a    b    c   newid

c     1    2    3   
a     4    2    1   
b     5    8    9   

id    a    b    c   newid   rest1   rest2

c     1    2    3   3       1       2
a     4    2    1   4       2       1
b     5    8    9   8       5       9

Thank you in advance

Roland · Accepted Answer · 2017-11-02 14:16:23Z

2

Using match and subsetting with an index matrix:

DF <- read.table(text = "id    a    b    c
c     1    2    3   
a     4    2    1   
b     5    8    9 ", header = TRUE)

DF$newid <- DF[, -1][cbind(seq_len(nrow(DF)), 
                           match(DF$id, colnames(DF)[-1]))]
#  id a b c newid
#1  c 1 2 3     3
#2  a 4 2 1     4
#3  b 5 8 9     8

answered Nov 2, 2017 at 14:16

Roland

134k12 gold badges203 silver badges305 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

P.Hubert Over a year ago

Is there a possibility to copy the other values (so the ones that don´t fit the id) behind the “new id” Variable so that I can calculate more easily?

Sotos · Accepted Answer · 2017-11-02 14:16:08Z

2

An approach via tidyverse,

library(tidyverse)

df %>% 
 gather(var, val, -id) %>% 
 filter(id == var) %>% 
 left_join(df, ., by = 'id') %>% 
 select(-var)

which gives,

  id a b c val
1  c 1 2 3   3
2  a 4 2 1   4
3  b 5 8 9   8

answered Nov 2, 2017 at 14:16

Sotos

51.6k6 gold badges36 silver badges69 bronze badges

Comments

BENY · Accepted Answer · 2017-11-02 14:23:29Z

0

By using melt from reshape

temp=with(melt(dt,'id'),melt(dt,'id')[id==variable,])
dt$New=temp$value[match(dt$id,temp$variable)]
dt
  id a b c New
1  c 1 2 3   3
2  a 4 2 1   4
3  b 5 8 9   8

answered Nov 2, 2017 at 14:23

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

Compare variable names with String to compute new variable

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related