Ingredients

Note

This help should be accurate and comprehensive. If you see anything missing or that needs to be fixed, see How to Contribute or let us know in the Juice Slack #documentation channel.

Ingredients are reusable SQLAlchemy snippets used for creating recipes. Once defined, Ingredients can be used across your Juicebox application in as many recipes as you like.

Ingredients can easily be created using yaml.

zip:
    kind: Dimension
    field: zip
    singular: Zipcode
    plural: Zipcodes
total_const_cost:
    kind: Metric
    field: const_cost
    singular: Total Construction Cost
    format: dollar
avg_const_cost:
    kind: Metric
    field:
        value: const_cost
        aggregation: avg
    singular: Avg. Construction Cost
    format: dollar
avg_issue_time:
    kind: DivideMetric
    numerator_field:
        value: date_issued-date_entered
    denominator_field:
        value: permit
        aggregation: count
    singular: 'Avg Issue Time (Days)'
    format: comma

The above yaml configuration is equivalent to the ingredient shelves written in Python code below.

dimension_shelf = {
    'zip': Dimension(MyTable.zip, singular='Zipcode', plural='Zipcodes')
}

metric_shelf = {
    'total_const_cost': Metric(func.sum(MyTable.const_cost),
    singular='Total Construction Cost',
    format='$,.0f')
    'avg_const_cost': Metric(func.avg(MyTable.const_cost),
    singular='Avg Construction Cost',
    format='$,.0f')
    'avg_issue_time': DivideMetric(func.sum(MyTable.date_issued
            - MyTable.date_entered),
    func.count(MyTable.permit),
    singular='Avg Issue Time (Days)',
    format=',.0f')
}

Fields

Consider field: zip.

This represents a database column, zip, in whatever table this shelf is using. Another way of representing the same thing is:

field:
    value: zip

We use this object expression when we need to add modifiers to the field. Two such modifiers are aggregations and conditions.

Aggregations

field:
    value: zip
    aggregation: sum

The above is equivalent to func.sum(MyTable.zip), which should look familiar if you are used to configuring ingredient shelves in Python.

There are many built-in aggregations ( None means the field isn’t aggregated).

sum:func.sum
min:func.min
max:func.max
avg:func.avg
count:func.count
count_distinct:lambda fld: func.count(distinct(fld))
month:lambda fld: func.date_trunc('month', fld)
week:lambda fld: func.date_trunc('week', fld)
year:lambda fld: func.date_trunc('year', fld)
quarter:lambda fld: func.date_trunc('quarter', fld)
None:lambda fld: fld

Conditions

Conditions compare a field against an expression.

For example:

condition:
    field: last_name
    in: ['Jones', 'Punjabi']

This specifies the ingredient should match cases where the database column last_name is one of either ‘Jones’ or ‘Punjabi’.

field:
    value: zip
    condition:
        field: last_name
        in: ['Jones', 'Punjabi']

The above yields the following SQL:

case when mytable.last_name in ('Jones', 'Punjabi') then mytable.zip end

In SQLAlchemy, it is expressed with a case function and a blizzard of ‘(‘ and ‘)’.

Field Math

A field value can contain columns that are added and subtracted from each other.

field:
    value: sales - tax

… is the same as MyTable.sales - MyTable.tax

Combining & Nesting

Conditions, aggregations and fields can be combined.

field:
    value: age
    aggregation: avg
    condition:
        field: last_name
        in: ['Jones', 'Punjabi']

…results in the SQL

avg(case when mytable.last_name in ('Jones', 'Punjabi') then mytable.age end)

Metrics

Metrics are automatically aggregated by sum. You can supply a different aggregation explicitly.

sales:
kind: Metric
field: sales_dollars

…will give you this

metric_shelf = {
'sales': Metric(func.sum(MyTable.sales_dollars))
}

If you want to specify a different aggregation, do this:

avg_sales:
kind: Metric
field:
    value: sales_dollars
    aggregation: avg

… which will give you

metric_shelf = {
'avg_sales': Metric(func.avg(MyTable.sales_dollars))
}

Dimensions

Dimensions can be either Dimension or IdValueDimension.

zip:
    kind: Dimension
    field: zip
    singular: Zipcode
    plural: Zipcodes

… is the same as

dimension_shelf = {
'zip': Dimension(MyTable.zip, singular='Zipcode', plural='Zipcodes')

Kinds

This is the class of Ingredient we are defining, each requiring parameters which are listed below.

Note

If the parameter is an aggregated field, it will by default be aggregated by summing.

Dimension:
    field: 'field'
LookupDimension:
    field: 'field'
IdValueDimension:
    field: 'field'
    id_field: 'field'
Metric:
    field: 'aggregated_field'
DivideMetric:
    numerator_field: 'aggregated_field',
    denominator_field: 'aggregated_field'
WtdAvgMetric:
    field: 'field',
    weight: 'field'
SumIfMetric:
    field: 'field',
    condition: 'condition'
AvgIfMetric:
    field: 'field',
    condition: 'condition'
CountIfMetric:
    field: 'field',
    condition: 'condition'

Named Formats

Many formats are predefined and can be referred to by name.

These are the named formats that are currently supported:

comma:‘,.0f’
dollar:‘$,.0f’
percent:‘.0%’
comma1:‘,.1f’
dollar1:‘,.1f’
percent1:‘.1%’
comma2:‘,.2f’
dollar2:‘$,.2f’
percent2:‘.2%’

In other words, this…

sales:
kind: Metric
field: sales_dollars
format: dollar

… is the same as this

sales:
kind: Metric
field: sales_dollars
format: '$,.0f'

Icons

Ingredients may have FontAwesome icons specified that fit the data concept well and add more personality to the data story. These icons will appear in filtering pills.

sales:
kind: Metric
field: sales_dollars
format: dollar
icon: money

Note

Ingredients get receive icons based on the specified format.