0

I'd like to combine multiple rows of a data.frame in R.

here is my data.frame:

    df<-data.frame(col1 = c("color","color","color","day","day","year"),
                 col2=c("red","red","red",1,1,18),
                 col3=c("red","blue","blue",10,12,17),
                 col4=c("red","blue","green",13,13,12))

df

      col1 col2 col3  col4
    1 color  red  red   red
    2 color  red blue  blue
    3 color  red blue green
    4   day    1   10    13
    5   day    1   12    13
    6  year   18   17    12

I used following code to merge the identical rows based on df$col1: 

    aggregate(df, by=list(df$col1),paste)

and the result is:

  Group.1                col1          col2            col3
1   color color, color, color red, red, red red, blue, blue
2     day            day, day          1, 1          10, 12
3    year                year            18              17
              col4
1 red, blue, green
2           13, 13
3               12

But how would I merge the elements of the identically-named rows to produce a data.frame like this:

   col1 col2      col3             col4
1 color  red red//blue red//blue//green
2   day    1    10//12               13
3  year   18        17               12

Thank you very much.

ahmad
  • 378
  • 1
  • 7
  • 1
    Images doesnt help anyone. Please have a look at https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example in order to understand ways to provide example data with question – MKR Jul 28 '18 at 16:43

1 Answers1

1

Data:

df <- data.frame(
        col1 = c("color","color","color","day","day","year"),
        col2 = c("red","red","red",1,1,18),
        col3 = c("red","blue","blue",10,12,17),
        col4 = c("red","blue","green",13,13,12),
        stringsAsFactors = FALSE)

Solution:

aggregate(df[,-1], by=df["col1"], function(x) paste(unique(x), collapse="//"))

Explanation:

  1. use the collapse argument to paste() during aggregate() to return one string per cell, and
  2. wrap x in unique() so as to avoid things like "1//1"
DanY
  • 5,920
  • 1
  • 13
  • 33