## 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.

Show source

## Answers to Spreading data over a date range from a column (R) ( 2 )

1. 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 = " - ") %>%
``````

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.

2. 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) %>%
``````#  Survey `2017-11-30` `2017-12-01` `2017-12-02` `2017-12-03` `2017-12-04` `2017-12-05`