0

I have a dataframe with yearly data from 2014 to 2018. I wish to expand this dataframe into monthly values and basically divide each variable's value by 12 as each month's value. Please note: There is no month column in my dataframe as of now. So, if there are 5 products, I have 5*5 rows and 5 columns :"year", "Product_ID", "Var1", "Var2" and "Var3" as the columns.

Eventually, I want 5*12 rows and 6 column with "month" inserted as well.

I have tried this code but it isnt working:

df_new$date <- NA
df_new <- complete(df,Product_ID, date = full_seq(2014,1))

Any suggestions?

user10579790
  • 333
  • 1
  • 10

1 Answers1

1

One option is to use uncount to repeat rows 12 times, create a new column month to take 1:12 value for each year and then divide Var columns by 12.

library(dplyr)
library(tidyr)

df %>%
  uncount(12) %>%
  group_by(year) %>%
  mutate(month = 1:12) %>%
  mutate_at(vars(Var1, Var2), ~./12)

# Groups:   year [3]
#    year Product_ID  Var1  Var2 month
#   <int> <chr>      <dbl> <dbl> <int>
# 1  2013 A          0.833     5     1
# 2  2013 A          0.833     5     2
# 3  2013 A          0.833     5     3
# 4  2013 A          0.833     5     4
# 5  2013 A          0.833     5     5
# 6  2013 A          0.833     5     6
# 7  2013 A          0.833     5     7
# 8  2013 A          0.833     5     8
# 9  2013 A          0.833     5     9
#10  2013 A          0.833     5    10
# … with 26 more rows

Or another option with complete and fill

df %>%
  mutate(month = 1) %>%
  complete(year, month = 1:12) %>%
  fill(Product_ID, Var1, Var2) %>%
  mutate_at(vars(Var1, Var2), ~./12)

data

df <- data.frame(year = 2013:2015, Product_ID = c("A", "B", "C"), 
      Var1 = c(10, 20, 30), Var2 = c(60, 80, 120), stringsAsFactors = FALSE)
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • Hey, I need a clarification. I understand "~." means divide each variable on the left handside with 12. But I am unable to understand the meaning of this symbol together. If I use only ´mutate_at(vars(Var1, Var2), /12)´ it is giving an error. I have read this post: https://stackoverflow.com/questions/13446256/meaning-of-tilde-dot-argument and ?formula, etc, but this combination is still unclear to me. Can you explain its significance wrt this case? – user10579790 May 29 '19 at 07:09
  • @user10579790 those are `purrr` formula style functions , you can replace it with anonymous functions. Eg `mtcars %>% mutate_at(1, function(x) x + 10)` and `mtcars %>% mutate_at(1, ~. + 10)` are the same. They are just different coding styles. The link which you shared has a different reference and is not applicable here. – Ronak Shah May 29 '19 at 07:31