Trying to pivot a table in R-CodePudding

I am trying to pivot a table in R for example:

Code to create the starting table:

df <- data.frame (ID  = c("A","A","A","B","B","C"),
                  Dates = c("01/01/2021", "10/02/2021", "30/03/2021","04/04/2021","06/05/2021","20/06/2021"))

Assume there is a max of three dates for the above example.

CodePudding user response：

You are missing a column with the identifier "Date1", "Date2", "Date3". You can create it with mutate(), then use pivot_wider() from the tidyverse library.

dt <- data.frame (ID  = c("A","A","A","B","B","C"),
                  Dates = c("01/01/2021", "10/02/2021", "30/03/2021","04/04/2021","06/05/2021","20/06/2021"))

library(tidyverse)
dt %>% group_by(ID) %>% 
  mutate(col = paste0("Date",row_number())) %>%
  pivot_wider(id_cols = ID, names_from = col, values_from = Dates)

CodePudding user response：

This is my approach :

my_df <- data.frame (ID  = c("A","A","A","B","B","C"),
                 Dates = c("01/01/2021", "10/02/2021", "30/03/2021","04/04/2021","06/05/2021","20/06/2021"),
                 stringsAsFactors = FALSE)

my_df <- my_df %>% group_by(ID) %>% mutate(value = paste("Date", seq_along(ID), sep = ""))
my_df <- dcast(my_df, ID ~ value, value.var = "Dates")

CodePudding user response：

Here's an approach similar to what you're requesting.

library("maditr")
df <- dcast(df, Dates ~ ID,fun.aggregate = length)

CodePudding user response：

Another solution, using data.table

df <- data.frame(
  ID  = c("A","A","A","B","B","C"),
  Dates = c("01/01/2021", "10/02/2021", "30/03/2021","04/04/2021","06/05/2021","20/06/2021")
)

library(data.table)

setDT(df)

df <- df[, .(dates = lapply(.SD, function(x) paste(x, collapse = ", "))), by = ID, .SDcols = c("Dates")]
df[, c("Date1", "Date2", "Date3") := tstrsplit(dates, ", ")]
df[, dates := NULL]

df
#    ID      Date1      Date2      Date3
# 1:  A 01/01/2021 10/02/2021 30/03/2021
# 2:  B 04/04/2021 06/05/2021       <NA>
# 3:  C 20/06/2021       <NA>       <NA>

CodePudding user response：

A base R option using reshape

reshape(
  transform(
    df,
    q = ave(seq_along(ID), ID, FUN = seq_along)
  ),
  direction = "wide",
  idvar = "ID",
  timevar = "q"
)

gives

  ID    Dates.1    Dates.2    Dates.3
1  A 01/01/2021 10/02/2021 30/03/2021
4  B 04/04/2021 06/05/2021       <NA>
6  C 20/06/2021       <NA>       <NA>