[R] How to round the timestamps in a mutate statement?

[asfimport]

I was trying to aggregate over time using different granularity. Usually I would use the lubridate::floor_date() , which is currently not supported for parquet datasets.

Is there any comprehensive list of supported list of currently supported {}lubridate (or dplyr{}) verbs? Maybe, it’s only my fault, but except the changelog I haven’t find any relevant information.

 

Later I found that the round_temporal() function is exposed to {}R{}. 
But I am struggling to find the right syntax inside a mutate statement to apply on a timestamp[us, tz=UTC] type column.

new_dataset |>
  mutate(time = arrow_round_temporal(time))
#>  Error: Invalid: Attempted to initialize KernelState from null FunctionOptions

 

Here are some other attempts:

library(arrow)

arrow_now <- Scalar$create(lubridate::now())
(arrow_now)
#> Scalar
#> 2022-04-25 11:44:33.805609
call_function("round_temporal", arrow_now)
#> Scalar
#> 2022-04-25 00:00:00.000000
call_function("round_temporal", arrow_now, unit = "day")
#> Error: Argument 2 is of class character but it must be one of "Array", "ChunkedArray", "RecordBatch", "Table", or "Scalar"
arrow_unit <- Scalar$create("day")
(arrow_unit)
#> Scalar
#> day
call_function("round_temporal", arrow_now, unit = arrow_unit)
#> Error: Invalid: Function 'round_temporal' accepts 1 arguments but attempted to look up kernel(s) with 2

 

Reporter: Zsolt Kegyes-Brassai / @kbzsl
Watchers: Rok Mihevc / @rok

Related issues:

• [R] Implement bindings for lubridate's floor_date, ceiling_date, and round_date (duplicates)
• [R] [Docs] Document the lubridate functions we support in {arrow} (relates to)
• [R] Implement bindings for lubridate's floor_date, ceiling_date, and round_date (is related to)

_{Note: This issue was originally created as ARROW-16316. Please see the migration documentation for further details.}

[asfimport]

Dragoș Moldovan-Grünfeld / @dragosmg:
Hi @kbzsl and thanks for opening this ticket. I'll try to reply to your questions:

• you are right, we don't yet have a comprehensive list of the {}lubridate{}, dplyr and other tidyverse verbs supported in arrow (other than the NEWS). It is definitely something that might be useful to have. I've just opened ARROW-16319 and I hope to cover that in time for release 9.0.0 (the release following the imminent one)
• support for lubridate functionality in arrow is under active development - see ARROW-15163 and ARROW-16155. However, we haven't been able to close  floor_date ticket yet, and it might not get solved in time for the 8.0.0 release. floor_date turned out to be quite complex and there are currently 2 pull request (PR 12657 and PR 12154) under review tackling different aspects of the functionality. Once these PRs get merged floor_date will be available in the development version of the package (hopefully soon).

[asfimport]

Dragoș Moldovan-Grünfeld / @dragosmg:
In terms of syntax, I think what you are looking for might be:

call_function("round_temporal", arrow_now, options = list(unit = "day"))

How did I figure it out? The error message mentioned that round_temporal accepts 1 argument and not 2 arguments. In the Arrow documentation, on this page round_temporal is listed as a unary function (which means exactly the same thing - it accepts a single argument), but it also states that it is hooked up to the RoundTemporalOptions options class. Looking at the call_function documentation tells me options can be passed as a list. Have a look at the ?call_function documentation and get back to us or feel free to suggests improvements if you think they are needed.

[asfimport]

Rok Mihevc / @rok:
Exactly as @dragosmg said.

Additionally:

call_function("round_temporal", arrow_now, options = list(unit = arrow_unit))

currently should work but behaves differently than lubridate. Namely rounding origin is set to 1970-01-01 instead of the one unit greater than unit rounded to. That means if you round to 6 hrs it will round to multiple of 6hrs since 1970-01-01 instead of to multiple of 6hrs since the beginning of the day the timestamp falls into.

If you're rounding to multiple of 1 unit that the two behaviours would overlap and you would get the same behaviour.

[asfimport]

Zsolt Kegyes-Brassai / @kbzsl:
Hi @dragosmg. Thank you for your suggestion.

It looks that the unit is always day even though hour was specified.

#> Scalar
#> 2022-04-25 14:31:14.788641
> call_function("round_temporal", arrow_now, options = list(unit = "hour"))
#> Scalar
#> 2022-04-26 00:00:00.000000

What I am doing wrong?

 

[asfimport]

Dragoș Moldovan-Grünfeld / @dragosmg:
I don't think you are doing anything wrong. I think "day" is the default. @rok  do you know what's going on here? It looks like CalendarUnit might not actually hooked-up to unit.

[asfimport]

Rok Mihevc / @rok:
I think only #12154 introduces passing temporal rounding options. So I'm not sure if anything consumes that options parameter.

[asfimport]

Rok Mihevc / @rok:
And "day" is the default so it makes sense it's used here/

[asfimport]

Dragoș Moldovan-Grünfeld / @dragosmg:
That's what I thought. Thanks for confirming @rok  

[asfimport]

Zsolt Kegyes-Brassai / @kbzsl:
Does it mean that the default unit (day) cannot be overwritten if required?

[asfimport]

Dragoș Moldovan-Grünfeld / @dragosmg:
It looks like, for the time being, that might be the case. 

[asfimport]

Rok Mihevc / @rok:
Yes, effectively options are not passed from R to the C++ kernel in the back so only the default options are applied (rounding to 1 day).
This will be resolved with the two PRs Dragos mentioned.

[asfimport]

Neal Richardson / @nealrichardson:
This is solved by ARROW-14821 right?

[asfimport]

Rok Mihevc / @rok:
@nealrichardson yes