Crunch Expressions, i.e. CrunchExpr
and CrunchLogicalExpr
,
encapsulate derivations of Crunch variables, possibly composed of other functions
which are only evaluated when sent to the server when creating a variable using VarDef()
or using as.vector()
to get data. The crunch database functions can be found in the
Help Center,
and can be called directly via crunchdbFunc()
m but many have also been wrapped
in native R functions, and are described in the details section below.
crunchdbFunc(fun, x, ...)
The name of the crunch database function to call
An input, a crunch variable, expression or R object
Other arguments passed to the database function
Logical expressions
These logical operators ==
, !=
, &
, |
, !
,%in%
work the same way as their
base R counterparts
is.selected(x)
return CrunchLogicalExpr
whether a value is in a selected category
rowAny(x)
and rowAll(x)
work row-wise on MultipleResponse
Variables (and expressions),
though na.rm
is not implemented for all(x)
.
%ornm%
is similar to |
, but where "not selected" beats "missing" (so FALSE %ornm% NA
is FALSE
instead of NA
as it would be with FALSE | NA
)
Comparisons
Comparison operators <
, <=
, >
, >=
work the same way as their base R counterparts.
crunchBetween(x, lower, upper, inclusive)
to provide lower and upper bounds in a single
expression.
Missing data expressions
is.na(x)
, is.valid(x)
return CrunchLogicalExpr
whether a single variable (or expression
that creates one) is missing (or not missing).
rowAnyNA(x)
, rowAllNA(x)
return CrunchLogicalExpr
whether any/all values in an
array variable (or expression that creates one) are missing.
complete.cases(x)
returns an expression that is "selected" if all cases are non-missing,
"missing" if they are all missing, and "other" otherwise.
Selection expressions
selectCategories(x, selections, collapse = TRUE)
takes a categorical variable (or array)
and marks categories as selected. selections
should be a list of category names or
values. If collapse
is TRUE
, (the default), it collapses the categories to "selected",
"other" and "missing", but it is FALSE
, then the old categories are preserved.
asSelected(x)
returns an expression that condenses a categorical into 3 categories
("selected", "other" or "missing")
selectedDepth(x)
returns an expression that creates a numeric variable that counts the
number of selections across rows of an array variable (or expression that creates one)
arraySelections(x)
returns an expression that takes an array and creates an array with
each variable condensed to "selected", "other" or "missing" and an extra subvariable
"any" that indicates whether any is selected.
alterCategoriesExpr(x, categories = NULL, category_order = NULL, subvariables = NULL)
Change the category names, order, or subvariable names of categorical or Array variables
(can only modify existing ones, not add or remove categories or subvariables).
categories
is a Categories
object or a list of lists, each with a name
indicating
the new name, as well as an id
or old_name
to identify which category to modify.
category_order
is either a numeric vector indicating category ids or a character vector
indicating the names of the categories in the order they should be displayed
(note that all categories must be specified). subvariables
is a list of lists, each with
a name
to rename the subvariable and an alias
, old_nam
or id
to identify the
subvariable. When x
is an expression, all categories and subvariables must be identified
by id
.
Array expressions
makeFrame(x, numeric = NULL)
an expression that creates an array from existing
variables or expressions, see deriveArray()
for more details
arraySubsetExpr(x, subvars, subvar_id = c("alias", "name", "id"))
Take a subset of an
existing array variable, identifying the subvariables by alias, name, or id (if x
is
an expression, you must use id).
alterArrayExpr(
x,
add = NULL,
order = NULL,
order_id = c("alias", "name", "id"),
remove = NULL,
remove_id = c("alias", "name", "id"),
subreferences = NULL,
subreferences_id = c("alias", "name", "id")
)
Add, reorder, remove or rename subvariables on an an array variable x
. The add
argument is
a list of variables or expressions, optionally named with the id they should have. order
and remove
are vectors of aliases, names or ids (specify which with order_id
/remove_id
).
The subreferences
object is a list of lists that are named the alias, name, or id (again
specify which with subreferences_id
) with metadata information like name and alias in the
list.
Miscellaneous expressions
caseExpr(..., cases)
Create a categorical variable from
a set of logical expressions that when met are assigned to a category. See
makeCaseVariable()
for more details.
bin(x)
returns a column's values binned into equidistant bins.
nchar(x)
returns a numeric value indicating the length of a string (or missing reason)
in a TextVariable
(or expression that creates one)
unmissing(x)
for a NumericVariable
(or expression that creates one) return the values of
the data, ignoring the ones set to missing.
trim(x, min, max)
for a NumericVariable
(or expression that creates one) return values
that where all values less than min
have been replaced with min
and all values greater
than max
have been
crunchDifftime(e1, e2, resolution)
Gets the difference between two datetimes as a number
with specified resolution units (one of c("Y", "Q", "M", "W", "D", "h", "m", "s", "ms")
).
datetimeFromCols(year, month, day, hours, minutes, seconds)
create a Datetime
variable
from numeric variables or expressions (year
, month
, and day
are required, but hours
,
minutes
, and seconds
are optional)
rollup(x, resolution)
sets the resolution of a datetime variable or expression, see
resolution()