Fill gaps not working when importing xlsx

HI All

I try it again with a different example. In this case the I took the example of the prison population in Australia.
I removed most of the rows to reduce the size of the file and
I deleted some values from the column Count In order to create some NA¨s.

It is not working, why? :frowning:

thanks in advance

Sharing my one drive folder with two files:




  ts_NA_Values <- read_xlsx("prison_missing.xlsx") %>% 
  mutate(Quarter = yearquarter(Date)) %>%
  as_tsibble(key = c(State, Gender, Legal, Indigenous),index = Quarter)

ts_NA_Values %>% 

ts_NA_Values %>% fill_gaps(Count=0)


its not clear how your code relates to the screenshot of a view.
if your screenshot of a view was made by immediately viewing ts_NA_Values after running only the code provided then I think the answer is simply that you did not assign the result of fill_gaps to a named object; so you are viewing ts_NA_values as it was without fill_gaps having operated on it.

in R <- is used as an assignment operator, as can be seen when you first create ts_NA_Values from the read_xlsx function; the principle is the same

1 Like

Thanks for your answer
It is not run after viewing...
the picture is after running this
ts_NA_Values %>% fill_gaps(Count=0)

In the original post i attached (one drive) the actual quarto file and the underlying xlsx file you can run all the code exactly the same way I did.

Also you can see it here

I'm behind a firewall so its not possible for me to access your xlsx.
can you provide the data in the preferred forum way ?
i.e. the result of


formatted as code

(until then; my assumption is that perhaps you have NA values in the starting data set that are not generated by the fill_gap step)

1 Like

HI many thanks
here it is
this is a print screen of the orignal file

here is the forum format

structure(list(Date = structure(c(1109635200, 1109635200, 1109635200, 
1109635200, 1109635200, 1109635200, 1109635200, 1109635200, 1109635200, 
1109635200, 1109635200, 1109635200, 1109635200), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), State = c("ACT", "ACT", "ACT", "ACT", "ACT", "ACT", 
"ACT", "ACT", "NSW", "NSW", "NSW", "NSW", "NSW"), Gender = c("Female", 
"Female", "Female", "Female", "Male", "Male", "Male", "Male", 
"Female", "Female", "Female", "Female", "Male"), Legal = c("Remanded", 
"Remanded", "Sentenced", "Sentenced", "Remanded", "Remanded", 
"Sentenced", "Sentenced", "Remanded", "Remanded", "Sentenced", 
"Sentenced", "Remanded"), Indigenous = c("ATSI", "Non-ATSI", 
"ATSI", "Non-ATSI", "ATSI", "Non-ATSI", "ATSI", "Non-ATSI", "ATSI", 
"Non-ATSI", "ATSI", "Non-ATSI", "ATSI"), Count = c(20, 30, 8, 
9, 7, 58, NA, NA, NA, NA, 145, 323, 355), Quarter = structure(c(12784, 
12784, 12784, 12784, 12784, 12784, 12784, 12784, 12784, 12784, 
12784, 12784, 12784), fiscal_start = 1, class = c("yearquarter", 
"vctrs_vctr"))), class = c("tbl_ts", "tbl_df", "tbl", "data.frame"
), row.names = c(NA, -13L), key = structure(list(State = c("ACT", 
"ACT", "ACT", "ACT", "ACT", "ACT", "ACT", "ACT", "NSW", "NSW", 
"NSW", "NSW", "NSW"), Gender = c("Female", "Female", "Female", 
"Female", "Male", "Male", "Male", "Male", "Female", "Female", 
"Female", "Female", "Male"), Legal = c("Remanded", "Remanded", 
"Sentenced", "Sentenced", "Remanded", "Remanded", "Sentenced", 
"Sentenced", "Remanded", "Remanded", "Sentenced", "Sentenced", 
"Remanded"), Indigenous = c("ATSI", "Non-ATSI", "ATSI", "Non-ATSI", 
"ATSI", "Non-ATSI", "ATSI", "Non-ATSI", "ATSI", "Non-ATSI", "ATSI", 
"Non-ATSI", "ATSI"), .rows = structure(list(1L, 2L, 3L, 4L, 5L, 
    6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L), ptype = integer(0), class = c("vctrs_list_of", 
"vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -13L), .drop = TRUE), index = structure("Quarter", ordered = TRUE), index2 = "Quarter", interval = structure(list(
    year = 0, quarter = 0, month = 0, week = 0, day = 0, hour = 0, 
    minute = 0, second = 0, millisecond = 0, microsecond = 0, 
    nanosecond = 0, unit = 0), .regular = TRUE, class = c("interval", 
"vctrs_rcrd", "vctrs_vctr")))
Filling_gaps <- ts_NA_Values %>% fill_gaps(Count = 0)
Filling_gaps %>% print()

as I predicted; rows 7-10 of your data before applying fill_gaps have NA values in the Count field.
you could do

Filling_gaps <- ts_NA_Values %>% mutate(Count=if_else(,0,Count)) %>% fill_gaps(Count = 0)
Filling_gaps %>% print()
1 Like

I´m lost it is not the purpose of fill_gaps to fill values that are missing in the first place?
Thanks again...

its intended use is to 'Turn implicit missing values into explicit missing values'
your dataset starts with explicit missing values.

1 Like

Sorry i would be more careful reading the functions nex time. thanks!
if we take my spreadesheet what would be an example of implicit missing values?
or if you can point me in the right direction i can investigate myself

thanks for all your answers , they are great

your example is small; there is only a single date '2005-03-01' ; only one quarter 2005Q1.
supposing there were examples of 2005Q3 in there two; and your tsibble is quarterly then the absent records 2005Q2 would be implicit NA's and I would think fill_gaps would generate them.

I'm not super familiar; I rarely work with timeseries

1 Like

thanks i think i get it!

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.