Help with converting monthly values to yearly values

[ksingh19]

Hi,

I have a tsibble with the following variables. Making a reprex of it changes the data type of month to be character. But otherwise, it is of data type month.

df <- data.frame(
          month = c("2012 Jan","2012 Feb","2012 Mar",
                    "2012 Apr","2012 May","2012 Jun","2012 Jul","2012 Aug",
                    "2012 Sep","2012 Oct","2012 Nov","2012 Dec"),
         volume = c(1558933,1392161,1703372,1443328,
                    1680644,1591807,1388084,1496928,1640968,1692362,1855691,
                    1941984)
   )

# Data type of "month" in original data as mnth
df <- df%>%
mutate(month = yearmonth(month))%>%
as_tsibble(index = month)

1. I want to have volumes by year and I am not able to use group_ by in this tsibble for some reason
2. How can I add commas in the values of volume. I can also add commas after saving into .xlsx as shown below. But I am not sure how can I do that?

wb <- createWorkbook()
addWorksheet(wb, "example")

writeDataTable(wb, example", x = df, tableStyle = "None",
  tableName = "example", withFilter = FALSE)

saveWorkbook(wb, "example.xlsx", overwrite = TRUE)

Thanks for your help!

[robjhyndman]

library(dplyr)
library(tsibble)
library(lubridate)

df <- data.frame(
  month = yearmonth(seq(as.Date("2012-01-01"), by="1 month", length=36)),
  volume = sample(1:1e6, 36)
)

# Monthly data as a tsibble
monthly <- df%>%
  mutate(month = yearmonth(month))%>%
  as_tsibble(index = month)
monthly
#> # A tsibble: 36 x 2 [1M]
#>       month volume
#>       <mth>  <int>
#>  1 2012 Jan 579609
#>  2 2012 Feb 864608
#>  3 2012 Mar 815976
#>  4 2012 Apr 674126
#>  5 2012 May  60692
#>  6 2012 Jun 389770
#>  7 2012 Jul 132181
#>  8 2012 Aug 145169
#>  9 2012 Sep 844833
#> 10 2012 Oct  17056
#> # … with 26 more rows

# Yearly data as a tsibble
yearly <- monthly %>%
  mutate(year = year(month)) %>%
  index_by(year) %>%
  summarise(volume = sum(volume))
yearly
#> # A tsibble: 3 x 2 [1Y]
#>    year  volume
#>   <dbl>   <int>
#> 1  2012 5100433
#> 2  2013 7660037
#> 3  2014 6595198

# Add comma separators to volume
yearly %>%
  mutate(
    format_volume = scales::number(volume, big.mark=',')
  )
#> # A tsibble: 3 x 3 [1Y]
#>    year  volume format_volume
#>   <dbl>   <int> <chr>        
#> 1  2012 5100433 5,100,433    
#> 2  2013 7660037 7,660,037    
#> 3  2014 6595198 6,595,198

^{Created on 2020-09-22 by the reprex package (v0.3.0)}

[ksingh19]

@robjhyndman

# Add comma separators to volume
yearly %>%
  mutate(
    format_volume = scales::number(volume, big.mark=',')
  )

Thank you so much! This was really helpful!