R transform data wid while keeping date format - reshape or dcast

Question

Trying to do something so simple and transpose this data wide so for each ID I have the VisitNum {1,2,3} as the column header and the CollectionDate as the value.

At my starting point Collection date was a character variable and VisitNum was numeric. I've tried with them as imported and with different formats after this code:

visit2$CollectionDate <- as.Date(visit2$CollectionDate, "%m-%d-%Y")
visit2$VisitNum <- as.character(visit2$VisitNum)

I've tried with dcast and got the below results. It's the dataframe I want but the date's aren't formatted anymore.

visit_long_a<- dcast(visit2, ParticipantID ~ VisitNum, value.var = "CollectionDate")
Aggregation function missing: defaulting to length

and with reshape and got even more bizarre results. I don't understand why the column is being concatenated.

visit_long_a <- reshape(visit2, direction = "wide", idvar = "ParticipantID", timevar = "VisitNum")

Hi Megan! Please edit your question to include data as reproducible code - the use of images for sharing data is strongly discouraged (see [Please do not upload images of code/data/errors.](//meta.stackoverflow.com/q/285551)). This will help you get better, faster support. Good luck! — jpsmith, Mar 23 '23 at 13:00
`tidyr::pivot_wider(visit2, names_from = VisitNum, values_from = CollectionDate)` — jpsmith, Mar 23 '23 at 13:28

score 0 · Answer 1 · answered Mar 23 '23 at 13:01

Is this the output you are looking for?

library(tidyverse)

df <- tribble(
  ~ParticipantID, ~VisitNum, ~CollectionDa,
  11001,         1,  "11-22-2022",
  11002,         1,  "03-16-2023",
  11003,         1,  "11-29-2022",
  11008,         1,  "11-18-2022",
  11008,         2,  "02-21-2023",
  11010,         1,  "12-09-2022",
  11010,         2,  "03-10-2023"
)

pivot_wider(
  df,
  names_from = VisitNum,
  names_prefix = "VisitNum_",
  values_from = CollectionDa
)
#> # A tibble: 5 × 3
#>   ParticipantID VisitNum_1 VisitNum_2
#>           <dbl> <chr>      <chr>     
#> 1         11001 11-22-2022 <NA>      
#> 2         11002 03-16-2023 <NA>      
#> 3         11003 11-29-2022 <NA>      
#> 4         11008 11-18-2022 02-21-2023
#> 5         11010 12-09-2022 03-10-2023

^{Created on 2023-03-23 with reprex v2.0.2}

R transform data wid while keeping date format - reshape or dcast

1 Answers1