>>> import ibis
>>> from ibis import _
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
... [
... ("2020-04-15 08:04:00", 1),
... ("2020-04-15 08:06:00", 2),
... ("2020-04-15 08:09:00", 3),
... ("2020-04-15 08:11:00", 4),
... ],
... columns=["ts", "val"],
... ).cast({"ts": "timestamp"})Temporal expressions
Dates, times, timestamps and intervals.
TimestampValue
TimestampValue(self, arg)
Attributes
| Name | Description |
|---|---|
| add | Add an interval to a timestamp. |
| radd | Add an interval to a timestamp. |
| sub | Subtract a timestamp or an interval from a timestamp. |
Methods
| Name | Description |
|---|---|
| bucket | Truncate the timestamp to buckets of a specified interval. |
| date | Return the date component of the expression. |
| delta | Compute the number of parts between two timestamps. |
| strftime | Format a timestamp according to format_str. |
| truncate | Truncate timestamp expression to units of unit. |
bucket
bucket(interval=None, *, years=None, quarters=None, months=None, weeks=None, days=None, hours=None, minutes=None, seconds=None, milliseconds=None, microseconds=None, nanoseconds=None, offset=None)
Truncate the timestamp to buckets of a specified interval.
This is similar to truncate, but supports truncating to arbitrary intervals rather than a single unit. Buckets are computed as fixed intervals starting from the UNIX epoch. This origin may be offset by specifying offset.
Parameters
| Name | Type | Description | Default |
|---|---|---|---|
interval |
Any | The bucket width as an interval. Alternatively may be specified via component keyword arguments. | None |
years |
int | None | Number of years | None |
quarters |
int | None | Number of quarters | None |
months |
int | None | Number of months | None |
weeks |
int | None | Number of weeks | None |
days |
int | None | Number of days | None |
hours |
int | None | Number of hours | None |
minutes |
int | None | Number of minutes | None |
seconds |
int | None | Number of seconds | None |
milliseconds |
int | None | Number of milliseconds | None |
microseconds |
int | None | Number of microseconds | None |
nanoseconds |
int | None | Number of nanoseconds | None |
offset |
Any | An interval to use to offset the start of the bucket. | None |
Returns
| Type | Description |
|---|---|
| TimestampValue | The start of the bucket as a timestamp. |
Examples
Bucket the data into 5 minute wide buckets:
>>> t.ts.bucket(minutes=5)┏━━━━━━━━━━━━━━━━━━━━━━━━━┓ ┃ TimestampBucket(ts, 5m) ┃ ┡━━━━━━━━━━━━━━━━━━━━━━━━━┩ │ timestamp │ ├─────────────────────────┤ │ 2020-04-15 08:00:00 │ │ 2020-04-15 08:05:00 │ │ 2020-04-15 08:05:00 │ │ 2020-04-15 08:10:00 │ └─────────────────────────┘
Bucket the data into 5 minute wide buckets, offset by 2 minutes:
>>> t.ts.bucket(minutes=5, offset=ibis.interval(minutes=2))┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓ ┃ TimestampBucket(ts, 5m, 2m) ┃ ┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩ │ timestamp │ ├─────────────────────────────┤ │ 2020-04-15 08:02:00 │ │ 2020-04-15 08:02:00 │ │ 2020-04-15 08:07:00 │ │ 2020-04-15 08:07:00 │ └─────────────────────────────┘
One common use of timestamp bucketing is computing statistics per bucket. Here we compute the mean of val across 5 minute intervals:
>>> mean_by_bucket = (
... t.group_by(t.ts.bucket(minutes=5).name("bucket"))
... .agg(mean=_.val.mean())
... .order_by("bucket")
... )
>>> mean_by_bucket┏━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┓ ┃ bucket ┃ mean ┃ ┡━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━┩ │ timestamp │ float64 │ ├─────────────────────┼─────────┤ │ 2020-04-15 08:00:00 │ 1.0 │ │ 2020-04-15 08:05:00 │ 2.5 │ │ 2020-04-15 08:10:00 │ 4.0 │ └─────────────────────┴─────────┘
date
date()
Return the date component of the expression.
Returns
| Type | Description |
|---|---|
| DateValue | The date component of self |
delta
delta(other, part)
Compute the number of parts between two timestamps.
The order of operands matches standard subtraction
The second argument is subtracted from the first.
Parameters
| Name | Type | Description | Default |
|---|---|---|---|
other |
datetime.datetime | Value[dt.Timestamp] |
A timestamp expression | required |
part |
Literal[‘year’, ‘quarter’, ‘month’, ‘week’, ‘day’, ‘hour’, ‘minute’, ‘second’, ‘millisecond’, ‘microsecond’, ‘nanosecond’] | Value[dt.String] |
The unit of time to compute the difference in | required |
Returns
| Type | Description |
|---|---|
IntegerValue |
The number of parts between self and other |
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> start = ibis.time("01:58:00")
>>> end = ibis.time("23:59:59")
>>> end.delta(start, "hour")22
>>> data = '''tpep_pickup_datetime,tpep_dropoff_datetime
... 2016-02-01T00:23:56,2016-02-01T00:42:28
... 2016-02-01T00:12:14,2016-02-01T00:21:41
... 2016-02-01T00:43:24,2016-02-01T00:46:14
... 2016-02-01T00:55:11,2016-02-01T01:24:34
... 2016-02-01T00:11:13,2016-02-01T00:16:59'''
>>> with open("/tmp/triptimes.csv", "w") as f:
... nbytes = f.write(data) # nbytes is unused
>>> taxi = ibis.read_csv("/tmp/triptimes.csv")
>>> ride_duration = taxi.tpep_dropoff_datetime.delta(
... taxi.tpep_pickup_datetime, "minute"
... ).name("ride_minutes")
>>> ride_duration┏━━━━━━━━━━━━━━┓ ┃ ride_minutes ┃ ┡━━━━━━━━━━━━━━┩ │ int64 │ ├──────────────┤ │ 19 │ │ 9 │ │ 3 │ │ 29 │ │ 5 │ └──────────────┘
strftime
strftime(format_str)
Format a timestamp according to format_str.
Format string may depend on the backend, but we try to conform to ANSI strftime.
Parameters
| Name | Type | Description | Default |
|---|---|---|---|
format_str |
str | strftime format string |
required |
Returns
| Type | Description |
|---|---|
StringValue |
Formatted version of arg |
truncate
truncate(unit)
Truncate timestamp expression to units of unit.
Parameters
| Name | Type | Description | Default |
|---|---|---|---|
unit |
Literal[‘Y’, ‘Q’, ‘M’, ‘W’, ‘D’, ‘h’, ‘m’, ‘s’, ‘ms’, ‘us’, ‘ns’] | Unit to truncate to | required |
Returns
| Type | Description |
|---|---|
| TimestampValue | Truncated timestamp expression |
DateValue
DateValue(self, arg)
Attributes
| Name | Description |
|---|---|
| add | Add an interval to a date. |
| radd | Add an interval to a date. |
| sub | Subtract a date or an interval from a date. |
Methods
| Name | Description |
|---|---|
| delta | Compute the number of parts between two dates. |
| strftime | Format a date according to format_str. |
| truncate | Truncate date expression to units of unit. |
delta
delta(other, part)
Compute the number of parts between two dates.
The order of operands matches standard subtraction
The second argument is subtracted from the first.
Parameters
| Name | Type | Description | Default |
|---|---|---|---|
other |
datetime.date | Value[dt.Date] |
A date expression | required |
part |
Literal[‘year’, ‘quarter’, ‘month’, ‘week’, ‘day’] | Value[dt.String] |
The unit of time to compute the difference in | required |
Returns
| Type | Description |
|---|---|
IntegerValue |
The number of parts between self and other |
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> start = ibis.date("1992-09-30")
>>> end = ibis.date("1992-10-01")
>>> end.delta(start, "day")1
>>> prez = ibis.examples.presidential.fetch()
>>> prez.mutate(
... years_in_office=prez.end.delta(prez.start, "year"),
... hours_in_office=prez.end.delta(prez.start, "hour"),
... ).drop("party")┏━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┓ ┃ name ┃ start ┃ end ┃ years_in_office ┃ hours_in_office ┃ ┡━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━┩ │ string │ date │ date │ int64 │ int64 │ ├────────────┼────────────┼────────────┼─────────────────┼─────────────────┤ │ Eisenhower │ 1953-01-20 │ 1961-01-20 │ 8 │ 70128 │ │ Kennedy │ 1961-01-20 │ 1963-11-22 │ 2 │ 24864 │ │ Johnson │ 1963-11-22 │ 1969-01-20 │ 6 │ 45264 │ │ Nixon │ 1969-01-20 │ 1974-08-09 │ 5 │ 48648 │ │ Ford │ 1974-08-09 │ 1977-01-20 │ 3 │ 21480 │ │ Carter │ 1977-01-20 │ 1981-01-20 │ 4 │ 35064 │ │ Reagan │ 1981-01-20 │ 1989-01-20 │ 8 │ 70128 │ │ Bush │ 1989-01-20 │ 1993-01-20 │ 4 │ 35064 │ │ Clinton │ 1993-01-20 │ 2001-01-20 │ 8 │ 70128 │ │ Bush │ 2001-01-20 │ 2009-01-20 │ 8 │ 70128 │ │ … │ … │ … │ … │ … │ └────────────┴────────────┴────────────┴─────────────────┴─────────────────┘
strftime
strftime(format_str)
Format a date according to format_str.
Format string may depend on the backend, but we try to conform to ANSI strftime.
Parameters
| Name | Type | Description | Default |
|---|---|---|---|
format_str |
str | strftime format string |
required |
Returns
| Type | Description |
|---|---|
StringValue |
Formatted version of arg |
truncate
truncate(unit)
Truncate date expression to units of unit.
Parameters
| Name | Type | Description | Default |
|---|---|---|---|
unit |
Literal[‘Y’, ‘Q’, ‘M’, ‘W’, ‘D’] | Unit to truncate arg to |
required |
Returns
| Type | Description |
|---|---|
| DateValue | Truncated date value expression |
TimeValue
TimeValue(self, arg)
Attributes
| Name | Description |
|---|---|
| add | Add an interval to a time expression. |
| radd | Add an interval to a time expression. |
| sub | Subtract a time or an interval from a time expression. |
Methods
| Name | Description |
|---|---|
| delta | Compute the number of parts between two times. |
| strftime | Format a time according to format_str. |
| truncate | Truncate the expression to a time expression in units of unit. |
delta
delta(other, part)
Compute the number of parts between two times.
The order of operands matches standard subtraction
The second argument is subtracted from the first.
Parameters
| Name | Type | Description | Default |
|---|---|---|---|
other |
datetime.time | Value[dt.Time] |
A time expression | required |
part |
Literal[‘hour’, ‘minute’, ‘second’, ‘millisecond’, ‘microsecond’, ‘nanosecond’] | Value[dt.String] |
The unit of time to compute the difference in | required |
Returns
| Type | Description |
|---|---|
IntegerValue |
The number of parts between self and other |
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> start = ibis.time("01:58:00")
>>> end = ibis.time("23:59:59")
>>> end.delta(start, "hour")22
>>> data = '''tpep_pickup_datetime,tpep_dropoff_datetime
... 2016-02-01T00:23:56,2016-02-01T00:42:28
... 2016-02-01T00:12:14,2016-02-01T00:21:41
... 2016-02-01T00:43:24,2016-02-01T00:46:14
... 2016-02-01T00:55:11,2016-02-01T01:24:34
... 2016-02-01T00:11:13,2016-02-01T00:16:59'''
>>> with open("/tmp/triptimes.csv", "w") as f:
... nbytes = f.write(data) # nbytes is unused
>>> taxi = ibis.read_csv("/tmp/triptimes.csv")
>>> ride_duration = (
... taxi.tpep_dropoff_datetime.time()
... .delta(taxi.tpep_pickup_datetime.time(), "minute")
... .name("ride_minutes")
... )
>>> ride_duration┏━━━━━━━━━━━━━━┓ ┃ ride_minutes ┃ ┡━━━━━━━━━━━━━━┩ │ int64 │ ├──────────────┤ │ 19 │ │ 9 │ │ 3 │ │ 29 │ │ 5 │ └──────────────┘
strftime
strftime(format_str)
Format a time according to format_str.
Format string may depend on the backend, but we try to conform to ANSI strftime.
Parameters
| Name | Type | Description | Default |
|---|---|---|---|
format_str |
str | strftime format string |
required |
Returns
| Type | Description |
|---|---|
StringValue |
Formatted version of arg |
truncate
truncate(unit)
Truncate the expression to a time expression in units of unit.
Commonly used for time series resampling.
Parameters
| Name | Type | Description | Default |
|---|---|---|---|
unit |
Literal[‘h’, ‘m’, ‘s’, ‘ms’, ‘us’, ‘ns’] | The unit to truncate to | required |
Returns
| Type | Description |
|---|---|
| TimeValue | self truncated to unit |
IntervalValue
IntervalValue(self, arg)
Attributes
| Name | Description |
|---|---|
| days | Extract the number of days from an interval. |
| hours | Extract the number of hours from an interval. |
| microseconds | Extract the number of microseconds from an interval. |
| milliseconds | Extract the number of milliseconds from an interval. |
| minutes | Extract the number of minutes from an interval. |
| months | Extract the number of months from an interval. |
| nanoseconds | Extract the number of nanoseconds from an interval. |
| quarters | Extract the number of quarters from an interval. |
| seconds | Extract the number of seconds from an interval. |
| weeks | Extract the number of weeks from an interval. |
| years | Extract the number of years from an interval. |
Methods
| Name | Description |
|---|---|
| negate | Negate an interval expression. |
| to_unit | Convert this interval to units of target_unit. |
negate
negate()
Negate an interval expression.
Returns
| Type | Description |
|---|---|
| IntervalValue | A negated interval value expression |
to_unit
to_unit(target_unit)
Convert this interval to units of target_unit.
DayOfWeek
DayOfWeek(self, expr)
A namespace of methods for extracting day of week information.
Methods
| Name | Description |
|---|---|
| full_name | Get the name of the day of the week. |
| index | Get the index of the day of the week. |
full_name
full_name()
Get the name of the day of the week.
Returns
| Type | Description |
|---|---|
StringValue |
The name of the day of the week |
index
index()
Get the index of the day of the week.
Ibis follows the
pandas convention for day numbering: Monday = 0 and Sunday = 6.
Returns
| Type | Description |
|---|---|
IntegerValue |
The index of the day of the week. |
now
ibis.now()
Return an expression that will compute the current timestamp.
Returns
| Type | Description |
|---|---|
TimestampScalar |
An expression representing the current timestamp. |
date
ibis.date(value_or_year, month=None, day=None, /)
Construct a date scalar or column.
Parameters
| Name | Type | Description | Default |
|---|---|---|---|
value_or_year |
Either a string value or datetime.date to coerce to a date, or an integral value representing the date year component. |
required | |
month |
The date month component; required if value_or_year is a year. |
None |
|
day |
The date day component; required if value_or_year is a year. |
None |
Returns
| Type | Description |
|---|---|
DateValue |
A date expression |
Examples
>>> import ibis
>>> ibis.options.interactive = TrueCreate a date scalar from a string
>>> ibis.date("2023-01-02")datetime.date(2023, 1, 2)
Create a date scalar from year, month, and day
>>> ibis.date(2023, 1, 2)datetime.date(2023, 1, 2)
Create a date column from year, month, and day
>>> t = ibis.memtable(dict(year=[2001, 2002], month=[1, 3], day=[2, 4]))
>>> ibis.date(t.year, t.month, t.day).name("my_date")┏━━━━━━━━━━━━┓ ┃ my_date ┃ ┡━━━━━━━━━━━━┩ │ date │ ├────────────┤ │ 2001-01-02 │ │ 2002-03-04 │ └────────────┘
time
ibis.time(value_or_hour, minute=None, second=None, /)
Return a time literal if value is coercible to a time.
Parameters
| Name | Type | Description | Default |
|---|---|---|---|
value_or_hour |
Either a string value or datetime.time to coerce to a time, or an integral value representing the time hour component. |
required | |
minute |
The time minute component; required if value_or_hour is an hour. |
None |
|
second |
The time second component; required if value_or_hour is an hour. |
None |
Returns
| Type | Description |
|---|---|
TimeValue |
A time expression |
Examples
>>> import ibis
>>> ibis.options.interactive = TrueCreate a time scalar from a string
>>> ibis.time("01:02:03")datetime.time(1, 2, 3)
Create a time scalar from hour, minute, and second
>>> ibis.time(1, 2, 3)datetime.time(1, 2, 3)
Create a time column from hour, minute, and second
>>> t = ibis.memtable({"h": [1, 4], "m": [2, 5], "s": [3, 6]})
>>> ibis.time(t.h, t.m, t.s).name("time")┏━━━━━━━━━━┓ ┃ time ┃ ┡━━━━━━━━━━┩ │ time │ ├──────────┤ │ 01:02:03 │ │ 04:05:06 │ └──────────┘
timestamp
ibis.timestamp(value_or_year, month=None, day=None, hour=None, minute=None, second=None, /, timezone=None)
Construct a timestamp scalar or column.
Parameters
| Name | Type | Description | Default |
|---|---|---|---|
value_or_year |
Either a string value or datetime.datetime to coerce to a timestamp, or an integral value representing the timestamp year component. |
required | |
month |
The timestamp month component; required if value_or_year is a year. |
None |
|
day |
The timestamp day component; required if value_or_year is a year. |
None |
|
hour |
The timestamp hour component; required if value_or_year is a year. |
None |
|
minute |
The timestamp minute component; required if value_or_year is a year. |
None |
|
second |
The timestamp second component; required if value_or_year is a year. |
None |
|
timezone |
The timezone name, or none for a timezone-naive timestamp. | None |
Returns
| Type | Description |
|---|---|
TimestampValue |
A timestamp expression |
Examples
>>> import ibis
>>> ibis.options.interactive = TrueCreate a timestamp scalar from a string
>>> ibis.timestamp("2023-01-02T03:04:05")Timestamp('2023-01-02 03:04:05')
Create a timestamp scalar from components
>>> ibis.timestamp(2023, 1, 2, 3, 4, 5)Timestamp('2023-01-02 03:04:05')
Create a timestamp column from components
>>> t = ibis.memtable({"y": [2001, 2002], "m": [1, 4], "d": [2, 5], "h": [3, 6]})
>>> ibis.timestamp(t.y, t.m, t.d, t.h, 0, 0).name("timestamp")┏━━━━━━━━━━━━━━━━━━━━━┓ ┃ timestamp ┃ ┡━━━━━━━━━━━━━━━━━━━━━┩ │ timestamp │ ├─────────────────────┤ │ 2001-01-02 03:00:00 │ │ 2002-04-05 06:00:00 │ └─────────────────────┘
interval
ibis.interval(value=None, unit='s', *, years=None, quarters=None, months=None, weeks=None, days=None, hours=None, minutes=None, seconds=None, milliseconds=None, microseconds=None, nanoseconds=None)
Return an interval literal expression.
Parameters
| Name | Type | Description | Default |
|---|---|---|---|
value |
int | datetime.timedelta | None | Interval value. | None |
unit |
str | Unit of value |
's' |
years |
int | None | Number of years | None |
quarters |
int | None | Number of quarters | None |
months |
int | None | Number of months | None |
weeks |
int | None | Number of weeks | None |
days |
int | None | Number of days | None |
hours |
int | None | Number of hours | None |
minutes |
int | None | Number of minutes | None |
seconds |
int | None | Number of seconds | None |
milliseconds |
int | None | Number of milliseconds | None |
microseconds |
int | None | Number of microseconds | None |
nanoseconds |
int | None | Number of nanoseconds | None |
Returns
| Type | Description |
|---|---|
IntervalScalar |
An interval expression |