Merge values in different cells of the same row into one cell using R

Question

Here is part of my dataframe. I want to add a column and merge the values in different cells into the newly added column separated by comma.

> df
                   X7     X8       X9     X10          X11     X12    X13    X14
1:                CA6   CORT     DFFA    ENO1         MTOR   PEX14    PGD PIK3CD
2:          ARHGEF10L   RCC2                                                    
3:             ADORA3   RHOC   ATP5F1  CAPZA1         CD53  CHI3L2  KCNA2  KCNA3
4:             NOTCH2 ADAM30                                                    
5:               BCL9   FMO5   PRKAB2  RNU1-4        CHD1L  RNU1-3 RNU1-2 RNU1-1
6:               ENSA   MCL1 ADAMTSL4 GOLPH3L ADAMTSL4-AS1 MIR4257

Here is the expected output.

> df
                   X7     X8       X9     X10          X11     X12    X13    X14            X15
1:                CA6   CORT     DFFA    ENO1         MTOR   PEX14    PGD PIK3CD CA6,CORT,DFFA,ENO1,MTOR,PEX14,PGD,PIK3CD 
2:          ARHGEF10L   RCC2                                                     ARHGEF10L,RCC2
3:             ADORA3   RHOC   ATP5F1  CAPZA1         CD53  CHI3L2  KCNA2  KCNA3 ADORA3,RHOC,ATP5F1,CAPZA1,CD53,CHI3L2,KCNA2,KCNA3
4:             NOTCH2 ADAM30                                                     NOTCH2,ADAM30
5:               BCL9   FMO5   PRKAB2  RNU1-4        CHD1L  RNU1-3 RNU1-2 RNU1-1 ...
6:               ENSA   MCL1 ADAMTSL4 GOLPH3L ADAMTSL4-AS1 MIR4257               ENSA,MCL1,ADAMTSL4,GOLPH3L,ADAMTSL4-AS1,MIR4257

data

structure(list(`X7` = c("CA6", "ARHGEF10L", "ADORA3", 
"NOTCH2", "BCL9", "ENSA"), X8 = c("CORT", "RCC2", "RHOC", "ADAM30", 
"FMO5", "MCL1"), X9 = c("DFFA", "", "ATP5F1", "", "PRKAB2", "ADAMTSL4"
), X10 = c("ENO1", "", "CAPZA1", "", "RNU1-4", "GOLPH3L"), X11 = c("MTOR", 
"", "CD53", "", "CHD1L", "ADAMTSL4-AS1"), X12 = c("PEX14", "", 
"CHI3L2", "", "RNU1-3", "MIR4257"), X13 = c("PGD", "", "KCNA2", 
"", "RNU1-2", ""), X14 = c("PIK3CD", "", "KCNA3", "", "RNU1-1", 
"")), row.names = c(NA, -6L), class = c("data.table", "data.frame"
), .internal.selfref = <pointer: 0x00000229e9f61ef0>)

https://stackoverflow.com/questions/22854112/how-to-skip-a-paste-argument-when-its-value-is-na-in-r — user2974951, Nov 10 '21 at 08:48

Park · Accepted Answer · 2021-11-10T08:53:06.477

Use tidyr::unite will helps.

library(dplyr)
library(tidyr)

df %>%
  rowwise %>%
  unite("x15", X7:X14, remove = FALSE, sep = ",", na.rm = TRUE)

  x15                                                X7        X8     X9       X10     X11          X12     X13    X14   
  <chr>                                              <chr>     <chr>  <chr>    <chr>   <chr>        <chr>   <chr>  <chr> 
1 CA6,CORT,DFFA,ENO1,MTOR,PEX14,PGD,PIK3CD           CA6       CORT   DFFA     ENO1    MTOR         PEX14   PGD    PIK3CD
2 ARHGEF10L,RCC2                                     ARHGEF10L RCC2   NA       NA      NA           NA      NA     NA    
3 ADORA3,RHOC,ATP5F1,CAPZA1,CD53,CHI3L2,KCNA2,KCNA3  ADORA3    RHOC   ATP5F1   CAPZA1  CD53         CHI3L2  KCNA2  KCNA3 
4 NOTCH2,ADAM30                                      NOTCH2    ADAM30 NA       NA      NA           NA      NA     NA    
5 BCL9,FMO5,PRKAB2,RNU1-4,CHD1L,RNU1-3,RNU1-2,RNU1-1 BCL9      FMO5   PRKAB2   RNU1-4  CHD1L        RNU1-3  RNU1-2 RNU1-1
6 ENSA,MCL1,ADAMTSL4,GOLPH3L,ADAMTSL4-AS1,MIR4257    ENSA      MCL1   ADAMTSL4 GOLPH3L ADAMTSL4-AS1 MIR4257 NA     NA

add

You can use everyting() instead of X7:X14

df %>%
  rowwise %>%
  unite("x15", everything(), remove = FALSE, sep = ",", na.rm = TRUE)

Mata · Answer 2 · 2021-11-10T09:17:25.003

1

I cannot get your data code to work, but you can use rowwise and paste:

library('dplyr')
df2 <- df %>% rowwise %>% mutate(new_col = paste(X7, X8, X9, X10, X11, X12, X13, X14, sep = ","))

edited Nov 10 '21 at 09:17

answered Nov 10 '21 at 08:48

Mata

538
3
17

Merge values in different cells of the same row into one cell using R

2 Answers2

add