Join Cells in R

xiaoni · February 28, 2020, 4:16pm

I have this code and I want to join them by ID

df <- tibble(id = c("a","a","c"),
             b = c("foo", "bar", "foo"),
              c = c("x", "y", "z"))
df

# A tibble: 3 x 3
     id b     c    
  <chr> <chr> <chr>
1     a foo   x    
2     a bar   y    
3     c foo   z

Expected Output:

     id      b         c    
  <chr>    <chr>     <chr>
1     a     foo bar   x y     
3     c    foo         z

I have tried this code below but it only returns me a character vector. I think this one does not work if value in group_by is character/string.

df %>% group_by(id) %>% 
  summarize(new = paste(b, collapse = " "),
            new2 = paste(c, collapse = " "))

StatSteph · February 28, 2020, 4:22pm

You were very close. I'm using the function str_c from the stringr package instead of paste.

library(tidyverse)

df <- tibble(id = c("a","a","c"),
             b = c("foo", "bar", "foo"),
             c = c("x", "y", "z"))
df
#> # A tibble: 3 x 3
#>   id    b     c    
#>   <chr> <chr> <chr>
#> 1 a     foo   x    
#> 2 a     bar   y    
#> 3 c     foo   z

df %>%
  group_by(id) %>%
  summarise(b_new=str_c(b, collapse=" "),
            c_new=str_c(c, collapse = " "))
#> # A tibble: 2 x 3
#>   id    b_new   c_new
#>   <chr> <chr>   <chr>
#> 1 a     foo bar x y  
#> 2 c     foo     z

^{Created on 2020-02-28 by the reprex package (v0.3.0)}

xiaoni · February 28, 2020, 4:39pm

I tried to run the code but it is still the same. Do you have any idea why?

StatSteph · February 28, 2020, 4:43pm

I'm not sure why your output is different from mine. I suggest using the package reprex. What other packages do you have loaded?

library(tidyverse)

df <- tibble(id = c("a","a","c"),
             b = c("foo", "bar", "foo"),
             c = c("x", "y", "z"))
df
#> # A tibble: 3 x 3
#>   id    b     c    
#>   <chr> <chr> <chr>
#> 1 a     foo   x    
#> 2 a     bar   y    
#> 3 c     foo   z

df %>%
  group_by(id) %>%
  summarise(b_new=str_c(b, collapse=" "),
            c_new=str_c(c, collapse = " "))
#> # A tibble: 2 x 3
#>   id    b_new   c_new
#>   <chr> <chr>   <chr>
#> 1 a     foo bar x y  
#> 2 c     foo     z

^{Created on 2020-02-28 by the reprex package (v0.3.0)}

Session info

devtools::session_info()
#> - Session info ----------------------------------------------------------
#>  setting  value                       
#>  version  R version 3.6.1 (2019-07-05)
#>  os       Windows 10 x64              
#>  system   x86_64, mingw32             
#>  ui       RTerm                       
#>  language (EN)                        
#>  collate  English_United States.1252  
#>  ctype    English_United States.1252  
#>  tz       America/New_York            
#>  date     2020-02-28                  
#> 
#> - Packages --------------------------------------------------------------
#>  package     * version date       lib source        
#>  assertthat    0.2.1   2019-03-21 [1] CRAN (R 3.6.1)
#>  backports     1.1.4   2019-04-10 [1] CRAN (R 3.6.0)
#>  broom         0.5.2   2019-04-07 [1] CRAN (R 3.6.1)
#>  callr         3.3.1   2019-07-18 [1] CRAN (R 3.6.1)
#>  cellranger    1.1.0   2016-07-27 [1] CRAN (R 3.6.1)
#>  cli           1.1.0   2019-03-19 [1] CRAN (R 3.6.1)
#>  colorspace    1.4-1   2019-03-18 [1] CRAN (R 3.6.1)
#>  crayon        1.3.4   2017-09-16 [1] CRAN (R 3.6.1)
#>  desc          1.2.0   2018-05-01 [1] CRAN (R 3.6.1)
#>  devtools      2.1.0   2019-07-06 [1] CRAN (R 3.6.1)
#>  digest        0.6.20  2019-07-04 [1] CRAN (R 3.6.1)
#>  dplyr       * 0.8.3   2019-07-04 [1] CRAN (R 3.6.1)
#>  evaluate      0.14    2019-05-28 [1] CRAN (R 3.6.1)
#>  fansi         0.4.0   2018-10-05 [1] CRAN (R 3.6.1)
#>  forcats     * 0.4.0   2019-02-17 [1] CRAN (R 3.6.1)
#>  fs            1.3.1   2019-05-06 [1] CRAN (R 3.6.1)
#>  generics      0.0.2   2018-11-29 [1] CRAN (R 3.6.1)
#>  ggplot2     * 3.2.1   2019-08-10 [1] CRAN (R 3.6.1)
#>  glue          1.3.1   2019-03-12 [1] CRAN (R 3.6.1)
#>  gtable        0.3.0   2019-03-25 [1] CRAN (R 3.6.1)
#>  haven         2.1.1   2019-07-04 [1] CRAN (R 3.6.1)
#>  highr         0.8     2019-03-20 [1] CRAN (R 3.6.1)
#>  hms           0.5.1   2019-08-23 [1] CRAN (R 3.6.1)
#>  htmltools     0.3.6   2017-04-28 [1] CRAN (R 3.6.1)
#>  httr          1.4.1   2019-08-05 [1] CRAN (R 3.6.1)
#>  jsonlite      1.6     2018-12-07 [1] CRAN (R 3.6.1)
#>  knitr         1.24    2019-08-08 [1] CRAN (R 3.6.1)
#>  lattice       0.20-38 2018-11-04 [2] CRAN (R 3.6.1)
#>  lazyeval      0.2.2   2019-03-15 [1] CRAN (R 3.6.1)
#>  lifecycle     0.1.0   2019-08-01 [1] CRAN (R 3.6.1)
#>  lubridate     1.7.4   2018-04-11 [1] CRAN (R 3.6.1)
#>  magrittr      1.5     2014-11-22 [1] CRAN (R 3.6.1)
#>  memoise       1.1.0   2017-04-21 [1] CRAN (R 3.6.1)
#>  modelr        0.1.5   2019-08-08 [1] CRAN (R 3.6.1)
#>  munsell       0.5.0   2018-06-12 [1] CRAN (R 3.6.1)
#>  nlme          3.1-140 2019-05-12 [2] CRAN (R 3.6.1)
#>  pillar        1.4.2   2019-06-29 [1] CRAN (R 3.6.1)
#>  pkgbuild      1.0.5   2019-08-26 [1] CRAN (R 3.6.1)
#>  pkgconfig     2.0.2   2018-08-16 [1] CRAN (R 3.6.1)
#>  pkgload       1.0.2   2018-10-29 [1] CRAN (R 3.6.1)
#>  prettyunits   1.0.2   2015-07-13 [1] CRAN (R 3.6.1)
#>  processx      3.4.1   2019-07-18 [1] CRAN (R 3.6.1)
#>  ps            1.3.0   2018-12-21 [1] CRAN (R 3.6.1)
#>  purrr       * 0.3.2   2019-03-15 [1] CRAN (R 3.6.1)
#>  R6            2.4.0   2019-02-14 [1] CRAN (R 3.6.1)
#>  Rcpp          1.0.2   2019-07-25 [1] CRAN (R 3.6.1)
#>  readr       * 1.3.1   2018-12-21 [1] CRAN (R 3.6.1)
#>  readxl        1.3.1   2019-03-13 [1] CRAN (R 3.6.1)
#>  remotes       2.1.0   2019-06-24 [1] CRAN (R 3.6.1)
#>  rlang         0.4.0   2019-06-25 [1] CRAN (R 3.6.1)
#>  rmarkdown     1.15    2019-08-21 [1] CRAN (R 3.6.1)
#>  rprojroot     1.3-2   2018-01-03 [1] CRAN (R 3.6.1)
#>  rvest         0.3.4   2019-05-15 [1] CRAN (R 3.6.1)
#>  scales        1.0.0   2018-08-09 [1] CRAN (R 3.6.1)
#>  sessioninfo   1.1.1   2018-11-05 [1] CRAN (R 3.6.1)
#>  stringi       1.4.3   2019-03-12 [1] CRAN (R 3.6.0)
#>  stringr     * 1.4.0   2019-02-10 [1] CRAN (R 3.6.1)
#>  testthat      2.2.1   2019-07-25 [1] CRAN (R 3.6.1)
#>  tibble      * 2.1.3   2019-06-06 [1] CRAN (R 3.6.1)
#>  tidyr       * 1.0.0   2019-09-11 [1] CRAN (R 3.6.1)
#>  tidyselect    0.2.5   2018-10-11 [1] CRAN (R 3.6.1)
#>  tidyverse   * 1.2.1   2017-11-14 [1] CRAN (R 3.6.1)
#>  usethis       1.5.1   2019-07-04 [1] CRAN (R 3.6.1)
#>  utf8          1.1.4   2018-05-24 [1] CRAN (R 3.6.1)
#>  vctrs         0.2.0   2019-07-05 [1] CRAN (R 3.6.1)
#>  withr         2.1.2   2018-03-15 [1] CRAN (R 3.6.1)
#>  xfun          0.9     2019-08-21 [1] CRAN (R 3.6.1)
#>  xml2          1.2.2   2019-08-09 [1] CRAN (R 3.6.1)
#>  yaml          2.2.0   2018-07-25 [1] CRAN (R 3.6.0)
#>  zeallot       0.1.0   2018-01-28 [1] CRAN (R 3.6.1)
#> 
#> [1] ../R/win-library/3.6
#> [2] ../R/R-3.6.1/library

xiaoni · February 28, 2020, 5:11pm

I tried a new session and yes it is working. Still figuring out how to use reprex. Can you send me the command so I can also run. Thank you

xiaoni · February 28, 2020, 5:23pm

@StatSteph I have figured out why. Yes, you are correct. I have conflicting packages. Thank you so much for enlightening me.

xiaoni · February 28, 2020, 5:52pm

These are all my packages.

sessioninfo::package_info()
 package          * version    date       lib source        
 ahp              * 0.2.12     2018-03-06 [1] CRAN (R 3.6.0)
 arules           * 1.6-4      2019-08-29 [1] CRAN (R 3.6.0)
 arulesViz        * 1.3-3      2019-05-20 [1] CRAN (R 3.6.0)
 assertthat         0.2.1      2019-03-21 [1] CRAN (R 3.6.0)
 backports          1.1.5      2019-10-02 [1] CRAN (R 3.6.0)
 bitops             1.0-6      2013-08-17 [1] CRAN (R 3.6.0)
 brew               1.0-6      2011-04-13 [1] CRAN (R 3.6.0)
 caret            * 6.0-84     2019-04-27 [1] CRAN (R 3.6.0)
 caTools            1.17.1.2   2019-03-06 [1] CRAN (R 3.6.0)
 chron              2.3-54     2019-08-26 [1] CRAN (R 3.6.0)
 class            * 7.3-15     2019-01-01 [1] CRAN (R 3.6.0)
 cli                1.1.0      2019-03-19 [1] CRAN (R 3.6.0)
 cluster            2.1.0      2019-06-19 [1] CRAN (R 3.6.1)
 codetools          0.2-16     2018-12-24 [1] CRAN (R 3.6.1)
 colorspace         1.4-1      2019-03-18 [1] CRAN (R 3.6.0)
 crayon             1.3.4      2017-09-16 [1] CRAN (R 3.6.0)
 crosstalk          1.0.0      2016-12-21 [1] CRAN (R 3.6.0)
 curl               4.2        2019-09-24 [1] CRAN (R 3.6.0)
 data.table       * 1.12.2     2019-04-07 [1] CRAN (R 3.6.0)
 data.tree          0.7.11     2019-11-09 [1] CRAN (R 3.6.0)
 dendextend         1.12.0     2019-05-11 [1] CRAN (R 3.6.0)
 DiagrammeR       * 1.0.1      2019-04-22 [1] CRAN (R 3.6.0)
 digest             0.6.21     2019-09-20 [1] CRAN (R 3.6.0)
 downloader         0.4        2015-07-09 [1] CRAN (R 3.6.0)
 dplyr            * 0.8.3      2019-07-04 [1] CRAN (R 3.6.0)
 DT               * 0.9        2019-09-17 [1] CRAN (R 3.6.0)
 e1071            * 1.7-2      2019-06-05 [1] CRAN (R 3.6.0)
 evaluate           0.14       2019-05-28 [1] CRAN (R 3.6.0)
 fansi              0.4.0      2018-10-05 [1] CRAN (R 3.6.0)
 fastmap            1.0.1      2019-10-08 [1] CRAN (R 3.6.0)
 foreach            1.4.7      2019-07-27 [1] CRAN (R 3.6.0)
 formattable      * 0.2.0.1    2016-08-05 [1] CRAN (R 3.6.0)
 fs                 1.3.1      2019-05-06 [1] CRAN (R 3.6.0)
 gclus              1.3.2      2019-01-07 [1] CRAN (R 3.6.0)
 gdata              2.18.0     2017-06-06 [1] CRAN (R 3.6.0)
 gender             0.5.3      2019-11-09 [1] CRAN (R 3.6.0)
 generics           0.0.2      2018-11-29 [1] CRAN (R 3.6.0)
 ggplot2          * 3.2.1      2019-08-10 [1] CRAN (R 3.6.0)
 glue               1.3.1      2019-03-12 [1] CRAN (R 3.6.0)
 gower              0.2.1      2019-05-14 [1] CRAN (R 3.6.0)
 gplots             3.0.1.1    2019-01-27 [1] CRAN (R 3.6.0)
 gridExtra          2.3        2017-09-09 [1] CRAN (R 3.6.0)
 gtable             0.3.0      2019-03-25 [1] CRAN (R 3.6.0)
 gtools             3.8.1      2018-06-26 [1] CRAN (R 3.6.0)
 hms                0.5.2      2019-10-30 [1] CRAN (R 3.6.0)
 htmltools          0.4.0      2019-10-04 [1] CRAN (R 3.6.0)
 htmlwidgets        1.5.1      2019-10-08 [1] CRAN (R 3.6.0)
 httpuv             1.5.2      2019-09-11 [1] CRAN (R 3.6.0)
 httr               1.4.1      2019-08-05 [1] CRAN (R 3.6.0)
 hunspell         * 3.0        2018-12-15 [1] CRAN (R 3.6.0)
 igraph             1.2.4.1    2019-04-22 [1] CRAN (R 3.6.0)
 influenceR         0.1.0      2015-09-03 [1] CRAN (R 3.6.0)
 ipred              0.9-9      2019-04-28 [1] CRAN (R 3.6.0)
 iterators          1.0.12     2019-07-26 [1] CRAN (R 3.6.0)
 jsonlite           1.6        2018-12-07 [1] CRAN (R 3.6.0)
 KernSmooth         2.23-16    2019-10-15 [1] CRAN (R 3.6.0)
 knitr              1.25       2019-09-18 [1] CRAN (R 3.6.0)
 later              1.0.0      2019-10-04 [1] CRAN (R 3.6.0)
 lattice          * 0.20-38    2018-11-04 [1] CRAN (R 3.6.1)
 lava               1.6.6      2019-08-01 [1] CRAN (R 3.6.0)
 lazyeval           0.2.2      2019-03-15 [1] CRAN (R 3.6.0)
 lexicon            1.2.1      2019-03-21 [1] CRAN (R 3.6.0)
 lifecycle          0.1.0      2019-08-01 [1] CRAN (R 3.6.0)
 lmtest             0.9-37     2019-04-30 [1] CRAN (R 3.6.0)
 lubridate          1.7.4      2018-04-11 [1] CRAN (R 3.6.0)
 magrittr         * 1.5        2014-11-22 [1] CRAN (R 3.6.0)
 MASS               7.3-51.4   2019-03-31 [1] CRAN (R 3.6.1)
 Matrix           * 1.2-17     2019-03-22 [1] CRAN (R 3.6.1)
 mime               0.7        2019-06-11 [1] CRAN (R 3.6.0)
 ModelMetrics       1.2.2      2018-11-03 [1] CRAN (R 3.6.0)
 modeltools         0.2-22     2018-07-16 [1] CRAN (R 3.6.0)
 munsell            0.5.0      2018-06-12 [1] CRAN (R 3.6.0)
 nlme               3.1-141    2019-08-01 [1] CRAN (R 3.6.0)
 NLP              * 0.2-0      2018-10-18 [1] CRAN (R 3.6.0)
 nnet               7.3-12     2016-02-02 [1] CRAN (R 3.6.1)
 openNLP            0.2-7      2019-10-26 [1] CRAN (R 3.6.0)
 openNLPdata        1.5.3-4    2017-11-12 [1] CRAN (R 3.6.0)
 pillar             1.4.2      2019-06-29 [1] CRAN (R 3.6.0)
 pkgconfig          2.0.3      2019-09-22 [1] CRAN (R 3.6.0)
 plotly             4.9.0      2019-04-10 [1] CRAN (R 3.6.0)
 plotrix            3.7-6      2019-06-21 [1] CRAN (R 3.6.0)
 plyr             * 1.8.4      2016-06-08 [1] CRAN (R 3.6.0)
 prodlim            2018.04.18 2018-04-18 [1] CRAN (R 3.6.0)
 promises           1.1.0      2019-10-04 [1] CRAN (R 3.6.0)
 purrr            * 0.3.3      2019-10-18 [1] CRAN (R 3.6.1)
 qdap             * 2.3.2      2019-01-02 [1] CRAN (R 3.6.0)
 qdapDictionaries * 1.0.7      2018-03-05 [1] CRAN (R 3.6.0)
 qdapRegex        * 0.7.2      2017-04-09 [1] CRAN (R 3.6.0)
 qdapTools        * 1.3.3      2017-06-20 [1] CRAN (R 3.6.0)
 R6                 2.4.0      2019-02-14 [1] CRAN (R 3.6.0)
 RColorBrewer     * 1.1-2      2014-12-07 [1] CRAN (R 3.6.0)
 Rcpp               1.0.2      2019-07-25 [1] CRAN (R 3.6.0)
 RCurl              1.95-4.12  2019-03-04 [1] CRAN (R 3.6.0)
 RDRPOSTagger     * 1.1        2019-10-19 [1] local         
 readr              1.3.1      2018-12-21 [1] CRAN (R 3.6.0)
 recipes            0.1.7      2019-09-15 [1] CRAN (R 3.6.0)
 registry           0.5-1      2019-03-05 [1] CRAN (R 3.6.0)
 reports            0.1.4      2014-12-21 [1] CRAN (R 3.6.0)
 reprex           * 0.3.0      2019-05-16 [1] CRAN (R 3.6.0)
 reshape2           1.4.3      2017-12-11 [1] CRAN (R 3.6.0)
 rgexf              0.15.3     2015-03-24 [1] CRAN (R 3.6.0)
 rhandsontable    * 0.3.7      2018-11-20 [1] CRAN (R 3.6.0)
 rJava            * 0.9-11     2019-03-29 [1] CRAN (R 3.6.0)
 rlang              0.4.4      2020-01-28 [1] CRAN (R 3.6.0)
 rmarkdown          1.16       2019-10-01 [1] CRAN (R 3.6.0)
 Rook               1.1-1      2014-10-20 [1] CRAN (R 3.6.0)
 rpart              4.1-15     2019-04-12 [1] CRAN (R 3.6.1)
 rsconnect          0.8.16     2019-12-13 [1] CRAN (R 3.6.1)
 rstudioapi         0.10       2019-03-19 [1] CRAN (R 3.6.0)
 scales             1.0.0      2018-08-09 [1] CRAN (R 3.6.0)
 scatterplot3d      0.3-41     2018-03-14 [1] CRAN (R 3.6.0)
 sentimentr       * 2.7.1      2019-03-22 [1] CRAN (R 3.6.0)
 seriation          1.2-8      2019-08-27 [1] CRAN (R 3.6.0)
 sessioninfo        1.1.1      2018-11-05 [1] CRAN (R 3.6.0)
 shiny            * 1.4.0      2019-10-10 [1] CRAN (R 3.6.0)
 shinyjs          * 1.0        2018-01-08 [1] CRAN (R 3.6.0)
 shinyWidgets     * 0.5.0      2019-11-18 [1] CRAN (R 3.6.0)
 slam               0.1-45     2019-02-26 [1] CRAN (R 3.6.0)
 SnowballC        * 0.6.0      2019-01-15 [1] CRAN (R 3.6.0)
 stringi            1.4.3      2019-03-12 [1] CRAN (R 3.6.0)
 stringr          * 1.4.0      2019-02-10 [1] CRAN (R 3.6.0)
 survival           2.44-1.1   2019-04-01 [1] CRAN (R 3.6.1)
 syuzhet            1.0.4      2017-12-14 [1] CRAN (R 3.6.0)
 textclean          0.9.3      2018-07-23 [1] CRAN (R 3.6.0)
 textreg          * 0.1.5      2018-10-04 [1] CRAN (R 3.6.0)
 tibble             2.1.3      2019-06-06 [1] CRAN (R 3.6.0)
 tidyr              1.0.0      2019-09-11 [1] CRAN (R 3.6.0)
 tidyselect         0.2.5      2018-10-11 [1] CRAN (R 3.6.0)
 timeDate           3043.102   2018-02-21 [1] CRAN (R 3.6.0)
 tm               * 0.7-6      2018-12-21 [1] CRAN (R 3.6.0)
 tokenizers       * 0.2.1      2018-03-29 [1] CRAN (R 3.6.0)
 topicmodels      * 0.2-8      2018-12-21 [1] CRAN (R 3.6.0)
 TSP                1.1-7      2019-05-22 [1] CRAN (R 3.6.0)
 utf8               1.1.4      2018-05-24 [1] CRAN (R 3.6.0)
 V8               * 2.3        2019-07-02 [1] CRAN (R 3.6.0)
 vcd                1.4-4      2017-12-06 [1] CRAN (R 3.6.0)
 vctrs              0.2.0      2019-07-05 [1] CRAN (R 3.6.0)
 venneuler          1.1-0      2011-08-10 [1] CRAN (R 3.6.0)
 viridis            0.5.1      2018-03-29 [1] CRAN (R 3.6.0)
 viridisLite        0.3.0      2018-02-01 [1] CRAN (R 3.6.0)
 visNetwork         2.0.8      2019-08-28 [1] CRAN (R 3.6.0)
 withr              2.1.2      2018-03-15 [1] CRAN (R 3.6.0)
 wordcloud        * 2.6        2018-08-24 [1] CRAN (R 3.6.0)
 xfun               0.10       2019-10-01 [1] CRAN (R 3.6.0)
 xlsx               0.6.1      2018-06-11 [1] CRAN (R 3.6.0)
 xlsxjars           0.6.1      2014-08-22 [1] CRAN (R 3.6.0)
 XML                3.98-1.20  2019-06-06 [1] CRAN (R 3.6.0)
 xml2               1.2.2      2019-08-09 [1] CRAN (R 3.6.0)
 xtable             1.8-4      2019-04-21 [1] CRAN (R 3.6.0)
 yaml             * 2.2.0      2018-07-25 [1] CRAN (R 3.6.0)
 zeallot            0.1.0      2018-01-28 [1] CRAN (R 3.6.0)
 zoo                1.8-6      2019-05-28 [1] CRAN (R 3.6.0)

xiaoni · February 29, 2020, 2:31pm

@StatSteph if ever I want to exclude foo in b_new. i.e. my expected result is below, is there a way to do it?

#>   id    b_new   c_new
#>   <chr> <chr>   <chr>
#> 1 a     bar       x y  
#> 2 c               z

StatSteph · March 2, 2020, 4:07pm

You might make any instance of "foo" as blank before concatenating.

library(tidyverse)

df <- tibble(id = c("a","a","c"),
             b = c("foo", "bar", "foo"),
             c = c("x", "y", "z"))
df
#> # A tibble: 3 x 3
#>   id    b     c    
#>   <chr> <chr> <chr>
#> 1 a     foo   x    
#> 2 a     bar   y    
#> 3 c     foo   z

df %>%
  mutate(b=if_else(b=="foo", "", b)) %>%
  group_by(id) %>%
  summarise(b_new=str_trim(str_c(b, collapse=" ")),
            c_new=str_c(c, collapse = " "))
#> # A tibble: 2 x 3
#>   id    b_new c_new
#>   <chr> <chr> <chr>
#> 1 a     bar   x y  
#> 2 c     ""    z

^{Created on 2020-03-02 by the reprex package (v0.3.0)}

xiaoni · March 3, 2020, 1:50am

Oh. Thank you so much.

system · March 10, 2020, 1:50am

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.