If can be used with all wrangles and connectors for read, write or run. If can be used to set a condition to determine if a wrangle or connector will execute or not.
If uses python syntax and will evaluate to True or False based on whether python considers the result to be 'truthy'.
Recipe variables ${variable} are parameterized and may be used within the statement, the dataframe object (df) is also available to write conditions on.
wrangles:
- convert.case:
input: a column
case: upper
if: ${variable}
# Set the case to upper only if the value of ${variable} is true
wrangles:
- convert.case:
input: a column
case: upper
if: len(df) > 0
# Set the case to upper only if the dataframe is not empty
For Wrangles, and Write, the additional variables 'columns', 'column_count', 'df', and 'row_count' are available.
| Variable | Function |
|---|---|
${column_count} |
Gives the number of columns. |
${columns} |
Gives a list of all the columns. |
${df} |
Gives the dataframe. |
${row_count} |
Gives the number of visible rows processed in each batch. |
write:
- file:
name: file1.csv
if: row_count > 0
# Write a file only if there's at least one row
- file:
name: file2.csv
if: '"specific_column" in columns'
# Write a file if a certain column exists. Note the quotes needed
# to prevent "spec... being interpreted as a YAML string.
An action whose if condition is false is skipped before validating or resolving its operation-specific columns and parameters. A wrangle whose where condition matches no rows is also skipped. This permits conditional steps to reference columns that are unavailable when that step does not run.
Environment variables are always strings. When using variable sourced from an environment variable, False may become 'False', which Python evaluates as a non-zero length string and therefore as True. Consider using an alternative such as:
if: str(${variable}).upper() == 'TRUE'
Where can be used to filter the rows used by a read, wrangle or write. Unlike if, which either executes or not, where will execute with a subset of the data.
Where uses SQL syntax (specifically the sqlite dialect) and should be written as the where clause of a sql select query.
# Write only the rows where the column
# named category contains the value 'example'
write:
- file:
name: file.csv
where: category = 'example'
Column names that contain spaces can be referenced using square brackets ([example column]), double quotes ("example column") or backticks (`example column`).
Recipe variables that are included in a where clause are vulnerable to sql injection. Values can be parameterized with where_params, to be correctly treated as a value. Parameters can be included in the where condition with ? as a position based variable or :name as a named variable.
# Write only the rows where the column named
# category matches the value passed in as
# the variable ${category_variable}
write:
- file:
name: file.csv
where: category = ?
where_params:
- ${category_variable}
# set value based on position
- file:
name: file.csv
where: category = :namedparameter
where_params:
namedparameter: ${category_variable}
# set a named value