CODEWITHSUNDEEP
r
user1987607
user1987607

Reputation: 2157

Dataframe merge rows

I have the following dataframe in R

my_df <- data.frame(V1 = c(1,2,3,1), V2 = c("A","B","C","A"), V3 = c("S1", "S1", "S1", "S2"), V4 = c("x","x","x","x"), V5 = c("y","y","y","y"), V6 =c("A", "B", "C", "D"))

> my_df
  V1 V2 V3 V4 V5 V6
1  1  A S1  x  y  A
2  2  B S1  x  y  B
3  3  C S1  x  y  C
4  1  A S2  x  y  D

Now I want to check if the combination of values in V1 and V2, occurs multiple times in the df. In my example my_df lines 1 and 4 have the same values '1 A' and '1 A'. If this happens, I want the following output:

> my_df_new
  V1 V2    V3 V4 V5  V6_S1   V6_S2
1  1  A S1;S2  x  y      A       D
2  2  B    S1  x  y      B     
3  3  C    S1  x  y      C

So basically two things have changed:

The rest of the columns and values should stay the same.

How can I achieve this?

Upvotes: 1

Views: 73

Answers (2)

Sotos
Sotos

Reputation: 51582

There should be a more concise way to doing this that doesn't force staff, but this is what I came up with,

library(data.table)
library(splitstackshape)

cSplit(setDT(my_df)[, .(V3 = toString(V3), 
                        V4 = V4[1], 
                        V5 = V5[1], 
                        V6 = toString(V6)), .(V1, V2)], 'V6')

#   V1 V2     V3 V4 V5 V6_1 V6_2
#1:  1  A S1, S2  x  y    A    D
#2:  2  B     S1  x  y    B <NA>
#3:  3  C     S1  x  y    C <NA>

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 388797

Here is one way using dplyr, group_by V1 and V2, collapse V3, create a new column (V7) to spread repeated values. 

library(dplyr)

my_df %>%
  group_by(V1, V2) %>%
  mutate(V3 = toString(V3), 
         V7 = paste0("V6_S", row_number())) %>%
  tidyr::spread(V7, V6)

#     V1 V2    V3     V4    V5  V6_S1 V6_S2
#  <dbl> <fct> <chr> <fct> <fct> <fct> <fct>
#1     1 A     S1, S2 x     y     A     D    
#2     2 B     S1     x     y     B     NA   
#3     3 C     S1     x     y     C     NA

Upvotes: 2

Related Questions