how to replace Index values in a dataframe by group IDs in r

Question

I have a data frame:

    df = read.table(text="index group   S1  S2  S3  S4
    1   A   2   3   4   6
    2   A   3   4   1   6
    3   A   2   4   1   5
    4   B   5   6   2   3
    5   B   6   4   9   10
    6   B   5   4   8   11
    7   B   11  12  8   10
    8   C   11  9   10  12
    9   C   10  8   11  12
    10  C   9   8   11  12
    11  D   8   9   10  12
    12  D   9   10  8   11", header=T, stringsAsFactors=F)

I would like to replace values of column S1-S4 with values of column 'group', if the value in column S1-S4 match the column "index". so the expected result is :

    index   S1  S2  S3  S4
    1   A   A   B   B
    2   A   B   A   B
    3   A   B   A   B
    4   B   B   A   A
    5   B   B   C   C
    6   B   B   C   D
    7   D   D   C   C
    8   D   C   C   D
    9   C   C   D   D
    10  C   C   D   D
    11  C   C   C   D
    12  C   C   C   D

I can get the result with a loop and match but I am not satisfied the solution. I appreciate any helps

Like this df[, 3:6] <- df$group[unlist(df[, 3:6])]? It's as simple as subsetting the group column by the (unlisted) indices in columns S1-S4. — talat
– talat, Commented Apr 5, 2017 at 14:13

jogo · Accepted Answer · 2017-04-05 14:21:46Z

2

The the values in the columns S1 ... S4 are indices of df$group:

df[, -(1:2)] <- lapply(df[-(1:2)], function(x) df$group[x])
# > df
#    index group S1 S2 S3 S4
# 1      1     A  A  A  B  B
# 2      2     A  A  B  A  B
# 3      3     A  A  B  A  B
# 4      4     B  B  B  A  A
# 5      5     B  B  B  C  C
# 6      6     B  B  B  C  D
# 7      7     B  D  D  C  C
# 8      8     C  D  C  C  D
# 9      9     C  C  C  D  D
# 10    10     C  C  C  D  D
# 11    11     D  C  C  C  D
# 12    12     D  C  C  C  D

or (if you want only the four last columns):

as.data.frame(lapply(df[-(1:2)], function(x) df$group[x]))
#> as.data.frame(lapply(df[-(1:2)], function(x) df$group[x]))
#    S1 S2 S3 S4
# 1   A  A  B  B
# 2   A  B  A  B
# 3   A  B  A  B
# 4   B  B  A  A
# 5   B  B  C  C
# 6   B  B  C  D
# 7   D  D  C  C
# 8   D  C  C  D
# 9   C  C  D  D
# 10  C  C  D  D
# 11  C  C  C  D
# 12  C  C  C  D

edited Apr 5, 2017 at 14:21

answered Apr 5, 2017 at 14:15

jogo

12.6k11 gold badges41 silver badges43 bronze badges

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

1 Comment

jogo Over a year ago

The solutions of Ronak and docendo are faster because they do not loop through the columns. The solution of Ronak even compares with df$index (for a situation where df$index is not identical with the row number)

Ronak Shah · Accepted Answer · 2017-04-05 14:15:49Z

2

We can match all the values of 4 columns with that of index and extract the corresponding group value.

df[3:6] <- df$group[match(unlist(df[3:6]), df$index)]

df
#   index group S1 S2 S3 S4
#1      1     A  A  A  B  B
#2      2     A  A  B  A  B
#3      3     A  A  B  A  B
#4      4     B  B  B  A  A
#5      5     B  B  B  C  C
#6      6     B  B  B  C  D
#7      7     B  D  D  C  C
#8      8     C  D  C  C  D
#9      9     C  C  C  D  D
#10    10     C  C  C  D  D
#11    11     D  C  C  C  D
#12    12     D  C  C  C  D

answered Apr 5, 2017 at 14:15

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Comments

akrun · Accepted Answer · 2017-04-05 17:07:11Z

0

We can also do this by converting to matrix

df[-(1:2)] <- setNames(df$group, df$index)[as.matrix(df[-(1:2)])]
df
#   index group S1 S2 S3 S4
#1      1     A  A  A  B  B
#2      2     A  A  B  A  B
#3      3     A  A  B  A  B
#4      4     B  B  B  A  A
#5      5     B  B  B  C  C
#6      6     B  B  B  C  D
#7      7     B  D  D  C  C
#8      8     C  D  C  C  D
#9      9     C  C  C  D  D
#10    10     C  C  C  D  D
#11    11     D  C  C  C  D
#12    12     D  C  C  C  D

answered Apr 5, 2017 at 17:07

akrun

891k38 gold badges590 silver badges700 bronze badges

Collectives™ on Stack Overflow

how to replace Index values in a dataframe by group IDs in r

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