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
| R   2017-01-06 21:01 2 Answers

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

  1. 2017-01-06 22:01

    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.

  2. 2017-01-06 22:01

    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%
    

Leave a reply to - Spreading data over a date range from a column (R)

◀ Go back