General table into a quartely table

Question

Using R:

Initially, the table looks like : PS (Date column is in the format : (dd/mm/yyyy))

      Date        Q           Season    
01/01/2017     1023           Winter
01/02/2017     2233           Winter
01/03/2017     1312           Winter
01/04/2017     1194           Spring
01/05/2017     907            Spring
01/06/2017     1939           Spring
01/07/2017     3264           Summer
01/08/2017     2232           Summer
01/09/2017     3574           Summer
01/10/2017     1533           Fall
01/11/2017     907            Fall
01/12/2017     1778           Fall

Similarly, for 2018 and 2019 (can change for different products so the table below needs to be dynamic in terms of the number of rows)

I want the table to be like:

Date         Winter                   Spring                 Summer                      Fall

2017  Sum of Q (01/17-03/17)   Sum of Q (04/17-06/17)  Sum of Q (07/17-09/17)    Sum of Q (10/17-12/17)
2018  Sum of Q (01/18-03/18)   Sum of Q (04/18-06/18)  Sum of Q (07/18-09/18)    Sum of Q (10/18-12/18)
2019  Sum of Q (01/19-03/19)   Sum of Q (04/19-06/19)  Sum of Q (07/19-09/19)    Sum of Q (10/19-12/19)

I/P Data:

structure(list(Time = structure(1:30, .Label = c("2017-02-01", "2017-03-01", "2017-04-01", "2017-05-01", "2017-06-01", "2017-07-01", "2017-08-01", "2017-09-01", "2017-10-01", "2017-11-01", "2017-12-01", "2018-01-01", "2018-02-01", "2018-03-01", "2018-04-01", "2018-05-01", "2018-06-01", "2018-07-01", "2018-08-01", "2018-09-01", "2018-10-01", "2018-11-01", "2018-12-01", "2019-01-01", "2019-02-01", "2019-03-01", "2019-04-01", "2019-05-01", "2019-06-01", "2019-07-01"), class = "factor"), Qty = c(1638.2520529, 0, 0, 1644.9257156, 1629.2808217, 2227.0057667, 586.0942485, 593.1155226, 1664.6943283, 2193.6993566, 2696.5906737, 3354.9333327, 2749.2299918, 3835.7584686, 2791.6314399, 2145.3504203, 3311.5319066, 4446.0188521, 2740.9135081, 2786.6458353, 3839.8798065, 3216.524473, 566.3303976, 0, 0, 0, 0, 559.2043639, 0, 528.4764685 ), Quarters = c("Q1", "Q1", "Q2", "Q2", "Q2", "Q3", "Q3", "Q3", "Q4", "Q4", "Q4", "Q1", "Q1", "Q1", "Q2", "Q2", "Q2", "Q3", "Q3", "Q3", "Q4", "Q4", "Q4", "Q1", "Q1", "Q1", "Q2", "Q2", "Q2", "Q3"), Season = structure(c(1L, 2L, 2L, 2L, 3L, 3L, 3L, 4L, 4L, 4L, 1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 4L, 4L, 4L, 1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L), .Label = c("Winter", "Spring", "Summer", "Fall"), class = "factor")), row.names = c(NA, -30L), class = "data.frame")

Ronak Shah Ronak Shah · Accepted Answer · 2020-07-08T04:47:03

Change Date to date class, extract year from it and cast data into wide format.

library(dplyr)

df %>%
  mutate(Time = as.Date(Time),
         year = format(Time, '%Y')) %>%
  select(-Time, -Quarters) %>%
  tidyr::pivot_wider(names_from = Season, values_from = Qty, values_fn = sum)
  #OR in old tidyr
  #tidyr::pivot_wider(names_from = Season, values_from = Qty, 
  #                   values_fn = list(Qty = sum))

# A tibble: 3 x 5
#  year  Winter Spring Summer  Fall
#  <chr>  <dbl>  <dbl>  <dbl> <dbl>
#1 2017   4335.  1645.  4442. 4452.
#2 2018   6670.  8773. 10498. 9843.
#3 2019      0    559.   528.   NA

An alternative, instead of using values_fn we can sum it first and then cast to wide.

df %>%
  type.convert(as.is = TRUE) %>% 
  mutate(Time = as.Date(Time),
         year = format(Time, '%Y')) %>%
  select(-Time, -Quarters) %>%
  group_by(year, Season) %>%
  summarise(Qty = sum(Qty)) %>%
  tidyr::spread(Season, Qty)
  #OR
  #tidyr::pivot_wider(names_from = Season, values_from = Qty)

General table into a quartely table

1 Answers