R: Convert monthly data into daily data for panel data

Question

I have the following data:

5 Products with a monthly rating from 2018-08 to 2018-12

Now with the help of R programming I would like to convert the monthly data into daily data and to have panel data.The monthly rating for each product will also be the rating for each day in the respective month.

So, that the new data will look like:

(with the first column being the product, the second column the date and the third column the rating)

A 2018-08-01 1
A 2018-08-02 1
A 2018-08-03 1
A 2018-08-04 1
... so on
A 2018-09-01 1
A 2018-09-02 1
...so on
A 2018-12-31 1
B 2018-08-01 3
B 2018-08-02 3
... so on
E 2018-12-31 3

Answer 1

library(tidyverse)
library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#> 
#>     date, intersect, setdiff, union

# example data
data <- tribble(
  ~Product, ~`Product Rating 2018-08`, ~`Product Rating 2018-10`,
  "A", 1, 1,
  "B", 3, 3,
)

data2 <-
  data %>%
  pivot_longer(-Product) %>%
  mutate(
    name = name %>% str_extract("[0-9-]+$") %>% paste0("-01") %>% as.Date()
  )

seq(as.Date("2018-08-01"), as.Date("2018-12-31"), by = "days") %>%
  tibble(date = .) %>%
  # left join on year and month
  expand_grid(data2) %>%
  filter(month(date) == month(name) & year(date) == year(name)) %>%
  select(Product, date, value)
#> # A tibble: 124 × 3
#>    Product date       value
#>    <chr>   <date>     <dbl>
#>  1 A       2018-08-01     1
#>  2 B       2018-08-01     3
#>  3 A       2018-08-02     1
#>  4 B       2018-08-02     3
#>  5 A       2018-08-03     1
#>  6 B       2018-08-03     3
#>  7 A       2018-08-04     1
#>  8 B       2018-08-04     3
#>  9 A       2018-08-05     1
#> 10 B       2018-08-05     3
#> # … with 114 more rows

^{Created on 2022-03-09 by the reprex package (v2.0.0)}