0

I have dataframe like this:

Id  Date
1   12.3.2011
1   22.2.2012
1   15.8.2015
2   2.7.2017
2   2.10.2017
3   1.9.2014
3   31.1.2015
3   11.11.2016

How to get dataframes in R like this:

Id  Date_1      Date_2      Date_3
1   12.3.2011   22.2.2012   15.8.2015
2   2.7.2017    2.10.2017   
3   1.9.2014    31.1.2015   11.11.2016

Thanks!

www
  • 38,575
  • 12
  • 48
  • 84
Denmla
  • 135
  • 1
  • 8
  • 2
    This is usually called 'long-to-wide' reshape. Take a look at https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format – Victor Mayrink Oct 07 '17 at 11:31

1 Answers1

2

A solution based on dplyr and tidyr.

library(dplyr)
library(tidyr)

dt2 <- dt %>%
  group_by(Id) %>%
  mutate(Number = paste0("Date", 1:n())) %>%
  spread(Number, Date)
dt2
# A tibble: 3 x 4
# Groups:   Id [3]
     Id     Date1     Date2      Date3
* <int>     <chr>     <chr>      <chr>
1     1 12.3.2011 22.2.2012  15.8.2015
2     2  2.7.2017 2.10.2017       <NA>
3     3  1.9.2014 31.1.2015 11.11.2016

Or with the dcast function from the reshape2 package.

library(dplyr)
library(reshape2)

dt2 <- dt %>%
  group_by(Id) %>%
  mutate(Number = paste0("Date", 1:n())) %>%
  dcast(Id ~ Number, value.var = "Date")
dt2
  Id     Date1     Date2      Date3
1  1 12.3.2011 22.2.2012  15.8.2015
2  2  2.7.2017 2.10.2017       <NA>
3  3  1.9.2014 31.1.2015 11.11.2016

Or use the data.table package.

dt_temp <- as.data.table(dt)
dt_temp2 <- dt_temp[, Number := paste0("Date", as.character(1:.N)), by = Id]

dcast(dt_temp2, Id ~ Number, value.var = "Date")
   Id     Date1     Date2      Date3
1:  1 12.3.2011 22.2.2012  15.8.2015
2:  2  2.7.2017 2.10.2017         NA
3:  3  1.9.2014 31.1.2015 11.11.2016

DATA

dt <- read.table(text = "Id  Date
1   12.3.2011
1   22.2.2012
1   15.8.2015
2   2.7.2017
2   2.10.2017
3   1.9.2014
3   31.1.2015
3   11.11.2016",
                 header = TRUE, stringsAsFactors = FALSE)
www
  • 38,575
  • 12
  • 48
  • 84