Currently I have a data frame that looks like this:

   Months Total  Date
1  2      6      05/01/2021
2  5      10     18/06/2021

I want to transform the data so that the month are added to the "Date" and the "Total" is divided by the "Months" giving a row for each month like the following:

   Total  Date
1  3      05/01/2021
2  3      05/02/2021
3  2      18/06/2021
4  2      18/07/2021
5  2      18/08/2021
6  2      18/09/2021
7  2      18/10/2021

CodePudding user response：

Here is one way -

• Change Date to date class so that is easier to perform arithmetic operation on it.
• uncount to repeat each row Months times
• For each row, divide the Total value by number of times that row is repeated.
• Add 1 month for every row of the date.

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

df %>%
  mutate(Date = dmy(Date), 
         row = row_number()) %>%
  uncount(Months) %>%
  group_by(row) %>%
  mutate(Total = Total/n(), 
         Date = Date %m % months(row_number() - 1)) %>%
  ungroup %>%
  select(-row)

#   Total Date      
#  <dbl> <date>    
#1     3 2021-01-05
#2     3 2021-02-05
#3     2 2021-06-18
#4     2 2021-07-18
#5     2 2021-08-18
#6     2 2021-09-18
#7     2 2021-10-18