My end user wants an excel workbook that includes the time series index in format YYYY Qx e.g. 1998 Q1 but whatever way I export my quarterly time series tsibble to excel the index column always ends up being a number - I think the number represents the number of days from 1 January 1970. I would like to find a way to do this.
I looked at Rob Hyndman's function on https://robjhyndman.com/hyndsight/ts2csv/ to write a tsibble to csv and this works fine (not sure if Rob still checks his page - but will not that I have asked here to avoid doubling up). Below is the write to csv, which works well and the index has "1998 Q1" style formatting.
library(fpp2)
readr::write_csv(tsibble::as_tsibble(visnights), "visnights.csv")
But I can't find a way of exporting that retains the time index format when exporting to excel. If I use something like the code below the first data element in the index column is a number - 10227 - instead of 1998 Q1
library(writexl)
write_xlsx(tsibble::as_tsibble(visnights), path = "visnights.xlsx", col_names = TRUE)
One more example - using pivot_longer = FALSE
library(tsibble)
library(writexl)
z <- ts(matrix(rnorm(300), 100, 3), start = c(1961, 1), frequency = 12)
exp <- as_tsibble(z, pivot_longer = FALSE)
write_xlsx(exp, path = "exp.xlsx", col_names = TRUE)
You could convert it to a tibble (instead of a tsibble) as this allows you to convert the index column to a character using mutate() from dplyr:
library(tsibble)
library(writexl)
z <- ts(matrix(rnorm(300), 100, 3), start = c(1961, 1), frequency = 12)
exp_z <- as_tsibble(z, pivot_longer = FALSE) %>%
as_tibble() %>%
dplyr::mutate(index = as.character(index))
write_xlsx(exp_z, path = "exp.xlsx", col_names = TRUE)
Note that, I've renamed your variable here as it's bad practice to create variables with the same name as functions.