2

I have the following situation.

    df <- rbind(
  data.frame(thisDate = rep(seq(as.Date("2018-1-1"), as.Date("2018-1-2"), by="day")) ),
  data.frame(thisDate = rep(seq(as.Date("2018-2-1"), as.Date("2018-2-2"), by="day")) ))
df <- cbind(df,lastMonth = as.Date(format(as.Date(df$thisDate - months(1)),"%Y-%m-01")))
df <- cbind(df, prod1Quantity= seq(1:4) )

I have quantities for different days of a month for an unknown number of products. I want to have 1 column for every product with the total monthly quantity of that product for all of the previous month. So the output would be like this .. ie grouped by lastMonth, Prod1Quantity . I just don't get how to group by, mutate and summarise dynamically if that indeed is the right approach.

I came across data.table generate multiple columns and summarize them . I think it appears to do what I need - but I just don't get how it is working!

Desired Output


   thisDate  lastMonth prod1Quantity prod1prevMonth
1 2018-01-01 2017-12-01             1             NA
2 2018-01-02 2017-12-01             2             NA
3 2018-02-01 2018-01-01             3              3
4 2018-02-02 2018-01-01             4              3
Improve this question

2 Answers 2

Reset to default
0

Another approach could be

library(dplyr)
library(lubridate)

temp_df <- df %>%
  mutate(thisDate_forJoin = as.Date(format(thisDate,"%Y-%m-01"))) 

final_df <- temp_df %>%
  mutate(thisDate_forJoin = thisDate_forJoin %m-% months(1)) %>%
  left_join(temp_df %>%
              group_by(thisDate_forJoin) %>%
              summarise_if(is.numeric, sum), 
            by="thisDate_forJoin") %>%
  select(-thisDate_forJoin)

Output is:

    thisDate prod1Quantity.x prod2Quantity.x prod1Quantity.y prod2Quantity.y
1 2018-01-01               1              10              NA              NA
2 2018-01-02               2              11              NA              NA
3 2018-02-01               3              12               3              21
4 2018-02-02               4              13               3              21

Sample data:

df <- structure(list(thisDate = structure(c(17532, 17533, 17563, 17564
), class = "Date"), prod1Quantity = 1:4, prod2Quantity = 10:13), class = "data.frame", row.names = c(NA, 
-4L))
#    thisDate prod1Quantity prod2Quantity
#1 2018-01-01             1            10
#2 2018-01-02             2            11
#3 2018-02-01             3            12
#4 2018-02-02             4            13
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

A solution can be reached by calculating the month-wise production quantity and then joining on month of lastMonth and thisDate.

lubridate::month function has been used evaluate month from date. 

library(dplyr)
library(lubridate)
df %>% group_by(month = as.integer(month(thisDate))) %>%
  summarise(prodQuantMonth = sum(prod1Quantity)) %>%
  right_join(., mutate(df, prevMonth = month(lastMonth)), by=c("month" = "prevMonth")) %>%
  select(thisDate, lastMonth, prod1Quantity, prodQuantLastMonth = prodQuantMonth)

# # A tibble: 4 x 4
#   thisDate   lastMonth  prod1Quantity prodQuantLastMonth
#   <date>     <date>             <int>              <int>
# 1 2018-01-01 2017-12-01             1                 NA
# 2 2018-01-02 2017-12-01             2                 NA
# 3 2018-02-01 2018-01-01             3                  3
# 4 2018-02-02 2018-01-01             4                  3
Improve this answer

1 Comment

Thank you! I will now try to extend it to a dynamic number of products as in prod1Quantity, prod2Quantity and so on. Thank you once again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.