Reduce data frame where only one column varies in R

Question

I have data like:

    V1    V2    V3    V4    V5
1   A     5.7    f    n     j47
2   A     5.7    f    n     j44
3   A     5.7    f    n     e54
4   B     5.1    m    y     j47
5   B     5.1    m    y     r10

I want my output to like like:

1:    A    5.7    f    n    j47, j44, e54
2;    B    5.1    m    y    j47, r10

ie only v5 vaires but I want to collapse my data so that all values for V5 for each identifier (given in V1) can be seen.

How can I do this?

`df %>% group_by_at(vars(V1:V4)) %>% summarise_all(toString)` — alistaire, Dec 07 '17 at 00:31
Possible duplicate of [Collapse / concatenate / aggregate a column to a single comma separated string within each group](https://stackoverflow.com/questions/15933958/collapse-concatenate-aggregate-a-column-to-a-single-comma-separated-string-w) — Ronak Shah, Dec 07 '17 at 01:14

markdly · Answer 1 · 2017-12-07T00:57:42.493

2

Using tidyr which is also part of the tidyverse along with dplyr: (Edit Note this adds a list column rather than collapsing V5 into a single string for each group)

library(tidyr)

nest(df, V5)    
#>   V1  V2 V3 V4          data
#> 1  A 5.7  f  n j47, j44, e54
#> 2  B 5.1  m  y      j47, r10

Data used

df <- (read.table(text = "V1    V2    V3    V4    V5
1   A     5.7    f    n     j47
2   A     5.7    f    n     j44
3   A     5.7    f    n     e54
4   B     5.1    m    y     j47
5   B     5.1    m    y     r10", header = TRUE, stringsAsFactors = FALSE))

edited Dec 07 '17 at 00:57

answered Dec 07 '17 at 00:36

markdly

4,394
2
19
27

This is pretty neat but it removes the letters (which are icd10 codes, hence critical) – Sam Lipworth Dec 07 '17 at 09:39
@SamLipworth, I'm not quite sure what you mean by the letters being removed. Expanding my answer above to `library(tidyverse); nest(df, V5, .key = "V5") %>% mutate(V5 = map_chr(V5, ~ paste0(.$V5, collapse = ", ")))` should give you an answer equivalent to the other answers provided (although not as elegantly!). I hope this helps – markdly Dec 07 '17 at 10:38

Maurits Evers · Accepted Answer · 2017-12-07T00:46:08.967

2

Another solution using base R's aggregate (no additional libraries necessary):

aggregate(data = df, V5 ~ V1 + V2 + V3 + V4, paste, collapse = ", ")
#  V1  V2 V3 V4            V5
#1  A 5.7  f  n j47, j44, e54
#2  B 5.1  m  y      j47, r10

Data

df <- read.table(text = 
    "V1    V2    V3    V4    V5
     1   A     5.7    f    n     j47
     2   A     5.7    f    n     j44
     3   A     5.7    f    n     e54
     4   B     5.1    m    y     j47
     5   B     5.1    m    y     r10", header = T)

edited Dec 07 '17 at 00:46

answered Dec 07 '17 at 00:36

Maurits Evers

49,617
4
47
68

Unless you add a `collapse` parameter, it's adding a list column. That could be good, but it's not obvious. – alistaire Dec 07 '17 at 00:40
@alistaire Thanks for point that out. I've updated my solution to output a plain `dataframe`. – Maurits Evers Dec 07 '17 at 00:45

Reduce data frame where only one column varies in R

2 Answers2

Data