Representing data in a single row per group in R

Question

I need to represent grouped data with various columns in one row. eg:

Dataset

ID Action_Number 
1    A            
1    B
1    C
2    D
2    E

Output required is:-

1  A  B  C 
2  D  E

I have tried various group by techniques and transpose but unable to get exact output. Please help.

Answer 1

The simplest option would be to split the 'Action_Number' by 'ID' into a list of vectors output

split(df1$Action_Number, df1$ID)

However, if we need a data.frame/data.table, then dcast can be used

library(data.table)#1.9.7+
dcast(setDT(df1), ID~rowid(ID), value.var = 'Action_Number')

Note that rowid is available in the devel version of data.table. So, if we have a version of data.table that is < 1.9.7, then create a sequence variable and then do the dcast

dcast(setDT(df1)[, rn := 1:.N, by = ID], ID ~ rn, value.var = 'Action_Number')

Answer 2

Using tidyverse (dplyr and tidyr)

tab <- readr::read_delim("ID Action_Number
1 A
1 B
1 C
2 D
2 E", delim = " ")

tab %>% 
  dplyr::group_by(ID) %>%
  dplyr::mutate(rn = paste0("V", 1:n())) %>%
  tidyr::spread(rn, Action_Number)
#> Source: local data frame [2 x 4]
#> Groups: ID [2]
#> 
#>      ID    V1    V2    V3
#> * <int> <chr> <chr> <chr>
#> 1     1     A     B     C
#> 2     2     D     E  <NA>

Answer 3

If you also want to keep them as a list instead, you can do:

df = data.frame(ID=c(1, 1, 1, 2, 2), Action_Number = c('A', 'B', 'C', 'D', 'E'), stringsAsFactors = F)

unique_IDs <- unique(df$ID)
l = list()
for(i in 1:length(unique_IDs)){
    l[[i]] <- df[df$ID == i,"Action_Number"]
}

Which here l gives you:

[[1]]
[1] "A" "B" "C"

[[2]]
[1] "D" "E"