CODEWITHSUNDEEP
rdataframe
M Krane
M Krane

Reputation: 169

R reduce/combine columns in a data.frame

I'm working with a dataframe and would like to merge two columns into one, if two of the cells have the same value.

  X1 X2 X3 X4 X5 X6 X7 X8  X9 X10 X11 X12 X13 X14 X15 X16 X17 X18 X19 X20 X21 X22 X23 X24
1 20 30 40 54 64 74 88 98 108 122 132 142 168 178 188 202 212 222 236 246 256 270 280 290
5  2  4  6  2  4  6  2  4   6   2   4   6   2   4   6   2   4   6   2   4   6   2   4   6
6  6  6  6 13 13 13 20 20  20  27  27  27   6   6   6  13  13  13  20  20  20  27  27  27

Thats the dataset I have. I would like to merge two columns if the elements in row 5,6 are the same by combining what ever is in row 1, e.g. the above dataset would become

   X1       X2       X3       X4       X5       X6       X7       X8       X9        X10       X11       X12 
1 (20,168) (30,178) (40,188) (54,202) (64,212) (74,222) (88,236) (98,246) (108,256) (122,270) (132,280) (142,290)
5  2        4        6        2        4        6        2        4        6         2         4         6
6  6        6        6        13       13       13       20       20       20        27        27        27

Thanks in advance EDIT: changed rows to column

Upvotes: 2

Views: 284

Answers (1)

akrun
akrun

Reputation: 886938

We could create a grouping variable ('gr') by pasteing the 2nd and 3rd rows. Then, we split the sequence of column with 'gr' to get a list output. Subset the first row of 'df1' based on the column index, paste the elements (toString is a convenient wrapper for paste(., collapse=', '), and to add some extra formatting with parentheses we can either use paste or sprintf), unsplit and assign the output to the first row of 'df1'

gr <-  paste(df1[2,], df1[3,])

lst <- split(seq_along(df1), gr)
df1[1,] <- unsplit(lapply(lst, function(x)
                 sprintf('(%s)', toString(df1[1,x]))) , gr)
df1
#        X1        X2        X3        X4        X5        X6        X7
#1 (20, 168) (30, 178) (40, 188) (54, 202) (64, 212) (74, 222) (88, 236)
#5         2         4         6         2         4         6         2
#6         6         6         6        13        13        13        20
#         X8         X9        X10        X11        X12       X13       X14
#1 (98, 246) (108, 256) (122, 270) (132, 280) (142, 290) (20, 168) (30, 178)
#5         4          6          2          4          6         2         4
#6        20         20         27         27         27         6         6
#        X15       X16       X17       X18       X19       X20        X21
#1 (40, 188) (54, 202) (64, 212) (74, 222) (88, 236) (98, 246) (108, 256)
#5         6         2         4         6         2         4          6
#6         6        13        13        13        20        20         20
#         X22        X23        X24
#1 (122, 270) (132, 280) (142, 290)
#5          2          4          6
#6         27         27         27

Upvotes: 3

Related Questions