CODEWITHSUNDEEP
rdataframe
Chuan
Chuan

Reputation: 677

Merge and expand a dataframe by group

Suppose I have a dataframe df like this:

> df <- data.frame(ID = c("A","B","C"),
                 value = c(1,2,3))
> df
  ID value
1  A     1
2  B     2
3  C     3

I would like to expand df by adding a column Date of a series of date value. The date column should be repeated based on ID. So the result would look like:


> df_new
  ID value  Date
1  A     1  2017-4-1
2  A     1  2017-4-2
3  A     1  2017-4-3
1  B     2  2017-4-1
2  B     2  2017-4-2
3  B     2  2017-4-3
1  C     3  2017-4-1
2  C     3  2017-4-2
3  C     3  2017-4-3

I find this post is similar to my issue, but the solution does not apply to mine. Below is something I try using tidyr: 


date <- c(seq(as.Date('2017-4-1'),as.Date('2017-4-3'), by = "days"))

df_new <- df %>% group_by(ID) %>% 
                 mutate(Date = date)

Error: Column `Date` must be length 1 (the group size), not 3

Any ideas? Thanks in advance.

Upvotes: 0

Views: 402

Answers (1)

Gregor Thomas
Gregor Thomas

Reputation: 145765

df %>% left_join(expand.grid(ID = unique(df$ID), date = date))
# Joining, by = "ID"
#   ID value       date
# 1  A     1 2017-04-01
# 2  A     1 2017-04-02
# 3  A     1 2017-04-03
# 4  B     2 2017-04-01
# 5  B     2 2017-04-02
# 6  B     2 2017-04-03
# 7  C     3 2017-04-01
# 8  C     3 2017-04-02
# 9  C     3 2017-04-03

expand.grid is the classic base function to generate all combinations. You can replace it with tidyr::crossing for the same result.

Upvotes: 2

Related Questions