Spreading data over a date range from a column (R)

Question

I have a set of survey data, where each survey covers multiple days. Here is an example of what the data looks like in the current form:

| Survey | Dates        | Result |
|--------|--------------|--------|
| A      | 11/30 - 12/1 | 33%    |
| B      | 12/2 - 12/4  | 26%    |
| C      | 12/4 - 12/5  | 39%    |

This example can be made with the following:

frame <- data.frame(Survey = c('A','B','C'),
                Dates = c('11/30 - 12/1', '12/2 - 12/4', '12/4 - 12/5'),
                Result = c('33%', '26%', '39%'))

What I would like to do is make a column for each date, and if the date is within the range of the survey, to put the result in the cell. It would look something like this:

| Survey | 11/30 | 12/1 | 12/2 | 12/3 | 12/4 | 12/5 |
|--------|-------|------|------|------|------|------|
| A      | 33%   | 33%  |      |      |      |      |
| B      |       |      | 26%  | 26%  | 26%  |      |
| C      |       |      |      |      | 39%  | 39%  |

Any help would be appreciated.

Please take a look at these tips on how to produce a [minimum, complete, and verifiable example](http://stackoverflow.com/help/mcve), as well as this post on [creating a great example in R](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). — lmo, Jan 06 '17 at 20:21
You may also want to add `tidyr` (or `tidyverse`) in addition to those mentioned by CCurtis. I say this because it will likely be the next useful tool for reshaping data. — steveb, Jan 06 '17 at 21:29

Steven Beaupré · Accepted Answer · 2017-01-07T14:13:01.143

Here's an idea:

library(dplyr)
library(tidyr)

frame %>%
  separate_rows(Dates, sep = " - ") %>%
  mutate(Dates = as.Date(Dates, format = "%m/%d")) %>%
  group_by(Survey) %>%
  complete(Dates = seq(min(Dates), max(Dates), 1)) %>%
  fill(Result) %>%
  spread(Dates, Result)

Which gives:

#  Survey `2017-11-30` `2017-12-01` `2017-12-02` `2017-12-03` `2017-12-04` `2017-12-05`
#* <fctr>       <fctr>       <fctr>       <fctr>       <fctr>       <fctr>       <fctr>
#1      A          33%          33%           NA           NA           NA           NA
#2      B           NA           NA          26%          26%          26%           NA
#3      C           NA           NA           NA           NA          39%          39%

Running this, I get an error `could not find function "mutate_at"` — Darren McNally, Jan 06 '17 at 22:16

Abdou · Answer 2 · 2017-01-06T23:11:24.023

A tidyverse solution but it requires that you play with the Dates column a bit:

#install.packages('tidyverse')

library(tidyverse)


dframe <- data.frame(Survey = c('A','B','C'),
                     Dates = c('11/30 - 12/1', '12/2 - 12/4', '12/4 - 12/5'),
                     Result = c('33%', '26%', '39%'), stringsAsFactors = F)

dframe$Dates <- lapply(strsplit(dframe$Dates, split = " - "), function(x) {
  x <- strptime(x, "%m/%d")
  x <- seq(min(x), max(x), '1 day')
  paste0(strftime(x, "%m/%d"), collapse = " - ")
})


dframe %>%
  separate_rows(Dates, sep = " - ") %>%
  spread(Dates, Result)

Should get:

Survey 11/30 12/01 12/02 12/03 12/04 12/05
    A   33%   33%  <NA>  <NA>  <NA>  <NA>
    B  <NA>  <NA>   26%   26%   26%  <NA>
    C  <NA>  <NA>  <NA>  <NA>   39%   39%

I hope this helps.

Spreading data over a date range from a column (R)

2 Answers2