Xavier Tibau Alberdi Xavier Tibau Alberdi - 2 months ago 6
R Question

Aggregating rows and adding new variables

I have a problem and I really don't know how to face it.

I've a DataFrame with thousends of rows, with two colums of my interest, ID and Date. Some IDs are reapited, but not dates. I want to have only one row for each ID, and then store the diferent dates into a columns, like: UniqueID, date1, date2, and so on.

Does anyone know what is the best way to do that? I use R, is there any optimal way to do this in R?

Thanks a lot!

Xavier

Answer

Do you mean something like this?

require(dplyr)
require(tidyr)
dates <- c("02/26/92", "02/27/92", "01/14/92", "02/28/92", "02/01/92")
dat <- data.frame(id = c(1,1,2,3,2), date = as.Date(dates, "%m/%d/%y"))

dat looks as follows:

  id       date
1  1 1992-02-26
2  1 1992-02-27
3  2 1992-01-14
4  3 1992-02-28
5  2 1992-02-01

With the following trick

dat %>% 
  select(id, date) %>% #here you select the columns that you want to use
  group_by(id) %>% 
  mutate(seq = paste0("date", row_number(id))) %>% 
  spread(seq, date)

become this:

Source: local data frame [3 x 3]
Groups: id [3]

     id      date1      date2
* <dbl>     <date>     <date>
1     1 1992-02-26 1992-02-27
2     2 1992-01-14 1992-02-01
3     3 1992-02-28       <NA>
Comments