Split row with yearly value into rows with monthly values

Question

I have a table with a yearly aggregate value. I want to split them up into the monthly values by dividing "Total" number by 12.

library(readr)
myData = read_delim("Date,b,c,d,Total\n2018,NA,NA,NA,12\n2018,0.5,0.5,NA,24\n2018,0.3,NA,0.5,36\n", delim=",")
myData 
# A tibble: 3 x 5
   Date     b     c     d Total
  <int> <dbl> <dbl> <dbl> <int>
1  2018  NA    NA    NA      12
2  2018   0.5   0.5  NA      24
3  2018   0.3  NA     0.5    36

desired output (for the first row, I'm expecting 36 rows in total):

   Date       b     c     d     Total
 1 2018-01-01 NA    NA    NA        1
 2 2018-02-01 NA    NA    NA        1
 3 2018-03-01 NA    NA    NA        1
 4 2018-04-01 NA    NA    NA        1
 5 2018-05-01 NA    NA    NA        1
 6 2018-06-01 NA    NA    NA        1
 7 2018-07-01 NA    NA    NA        1
 8 2018-08-01 NA    NA    NA        1
 9 2018-09-01 NA    NA    NA        1
10 2018-10-01 NA    NA    NA        1
11 2018-11-01 NA    NA    NA        1
12 2018-12-01 NA    NA    NA        1

I already checked the accepted answer here: Break summed row into individual rows in R but unfortunatley this is not working for me.

`myData <- myData[rep(1:nrow(myData), each = 12),]; myData$Total <- myData$Total/12` — Jaap, Jun 01 '18 at 11:43
Also: include the packages you are using. `read_delim` is not from base R, but from `readr`. — Jaap, Jun 01 '18 at 11:47

jasbner · Accepted Answer · 2018-06-01T12:52:15.377

If you actually need monthly dates, you can use complete to do that.

I created a unique ID for each year based on the Total (you may have to play with that depending on how your data is actually organized). I then made your date column into a date based on the first of the year. Then I used complete to fill out the rest of the months of the year. fill was used to complete the rows, and mutate to divide the total by 12.

library(dplyr)
library(tidyr)
library(readr)
library(lubridate)

myData = read_delim("Date,b,c,d,Total\n2018,NA,NA,NA,12\n2018,0.5,0.5,NA,24\n2018,0.3,NA,0.5,36\n", delim=",")
myData 
#  # A tibble: 3 x 5
#     Date      b      c      d Total
#    <int>  <dbl>  <dbl>  <dbl> <int>
#  1  2018 NA     NA     NA        12
#  2  2018  0.500  0.500 NA        24
#  3  2018  0.300 NA      0.500    36

myData %>%
  mutate(group_id = group_indices(., Total)) %>% 
  mutate(Date = dmy(paste("01/01/",Date))) %>% 
  group_by(group_id) %>% 
  complete(Date = seq.Date(Date[1],by = "month",length.out = 12)) %>% 
  fill(b,c,d,Total) %>% 
  mutate(Total = Total/12) %>%
  ungroup() %>% 
  select(-group_id)

#  # A tibble: 36 x 5
#     Date           b     c     d Total
#     <date>     <dbl> <dbl> <dbl> <dbl>
#   1 2018-01-01    NA    NA    NA  1.00
#   2 2018-02-01    NA    NA    NA  1.00
#   3 2018-03-01    NA    NA    NA  1.00
#   4 2018-04-01    NA    NA    NA  1.00
#   5 2018-05-01    NA    NA    NA  1.00
#   6 2018-06-01    NA    NA    NA  1.00
#   7 2018-07-01    NA    NA    NA  1.00
#   8 2018-08-01    NA    NA    NA  1.00
#   9 2018-09-01    NA    NA    NA  1.00
#  10 2018-10-01    NA    NA    NA  1.00
#  # ... with 26 more rows

Split row with yearly value into rows with monthly values

1 Answers1