How to match multiple corresponding values between columns in R

Question

I have a data frame that is structured as follows

ID  Value1    Value2
1   a;d;g;f   12;14;15;9
2   b;c;e     5;18;20
3   h;i;j     6;7;25

So I have an ID and two values, for value 1 one there are multiple options that correspond to value 2. And I want to end up with the following data frame i.e. every option of value 1 with its corresponding value 2

ID  Value1   Value2
1   a        12
1   d        14
1   g        15
1   f        9
2   b        5
2   c        18
2   e        2
3   h        6
3   i        7
3   j        25

How can I script R to do this?

akrun · Accepted Answer · 2015-07-15 09:14:48Z

4

You can try cSplit

library(splitstackshape)
cSplit(df1, sep=";", c('Value1', 'Value2'), 'long', type.convert=TRUE)
#    ID Value1 Value2
#1:  1      a     12
#2:  1      d     14
#3:  1      g     15
#4:  1      f      9
#5:  2      b      5
#6:  2      c     18
#7:  2      e     20
#8:  3      h      6
#9:  3      i      7
#10: 3      j     25

data

df1 <- structure(list(ID = 1:3, Value1 = c("a;d;g;f", "b;c;e", "h;i;j"
), Value2 = c("12;14;15;9", "5;18;20", "6;7;25")), .Names = c("ID", 
"Value1", "Value2"), class = "data.frame", row.names = c(NA, -3L))

answered Jul 15, 2015 at 9:14

akrun

891k38 gold badges590 silver badges700 bronze badges

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

Comments

Arun · Accepted Answer · 2015-07-15 09:50:31Z

4

Another option is to use tstrsplit from the devel version of data.table

library(data.table)
setDT(df)[, lapply(.SD, function(x) unlist(tstrsplit(x, ";", fixed=TRUE))), by = ID]
#     ID Value1 Value2
#  1:  1      a     12
#  2:  1      d     14
#  3:  1      g     15
#  4:  1      f      9
#  5:  2      b      5
#  6:  2      c     18
#  7:  2      e     20
#  8:  3      h      6
#  9:  3      i      7
# 10:  3      j     25

edited Jul 15, 2015 at 9:50

Arun

119k28 gold badges290 silver badges396 bronze badges

answered Jul 15, 2015 at 9:17

David Arenburg

92.4k18 gold badges145 silver badges202 bronze badges

Comments

Cath · Accepted Answer · 2015-07-15 10:06:43Z

3

Or with base r (df1 being borrowed from @akrun):

vals <- lapply(colnames(df1)[-1], function(col){strsplit(df1[, col], ";")})
names(vals) <- colnames(df1)[-1]
v_id <- mapply(rep, df1$ID, lapply(s_v1, length))

data.frame(ID=unlist(v_id), lapply(vals, unlist), stringsAsFactors=F)

#    ID Value1 Value2
#1   1      a     12
#2   1      d     14
#3   1      g     15
#4   1      f      9
#5   2      b      5
#6   2      c     18
#7   2      e     20
#8   3      h      6
#9   3      i      7
#10  3      j     25

edited Jul 15, 2015 at 10:06

answered Jul 15, 2015 at 9:37

Cath

24.1k5 gold badges56 silver badges87 bronze badges

Collectives™ on Stack Overflow

How to match multiple corresponding values between columns in R

3 Answers 3

data

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

data

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related