TidyData: Horizontal Conditions

When extracting data there will be columns whose value will depend conditionally on the values of other columns. In tidychef we handle this scenario with what we call HorizontalConditions.

Source Data

The data source we’re using for these examples is shown below:

The full data source can be viewed here.

from tidychef import acquire, preview
from tidychef.selection import CsvSelectable

table: CsvSelectable = acquire.csv.http("https://raw.githubusercontent.com/mikeAdamss/tidychef/main/tests/fixtures/csv/bands-wide.csv")
preview(table)

Unnamed Table

	A	B	C	D	E	F	G	H	I	J	K
1
2			Houses	Cars	Boats				Houses	Cars	Boats
3	Beatles						Rolling Stones
4		John	1	5	9			Keith	2	6	10
5		Paul	2	6	10			Mick	3	7	11
6		George	2	7	11			Charlie	3	8	12
7		Ringo	4	8	12			Ronnie	5	9	13
8

Syntax

The basic syntax for constructing a horizontal conditional is as follows:

Column.horizontal_condition(<name>, <callable>)

The <callable> is a python function or lambda function that operates upon a dictionary.

The Horizontal Condition Dictionary

The dictionary in question consists of:

• keys: the names of the extracted columns

• values: the values extracted against the same observation the horoxontal condition is operating against.

---

Example:

Let’s imagine the you run a transform that will create the following line of tidy data.

Observation	Member	Assets	Band
5	John	Cars	Beatles

If you were to add a horizonal condition to the TidyData constructor, then for that specific observation the dictionary accessible to the horizontal condition would be:

{
    "Observation": "5",
    "Member": "John",
    "Assets": "Cars",
    "Band" : "Beatles
}

This will be shown in context in the examples below.

Condition: Singer In The Beatles

For our first example, we’ll create a horizontal condition columns to identify members of The Rolling Stones whose names end with “e”.

You’ve seen this example before, so mainly focus on the new Column.horizontal_condition() syntax.

from typing import Dict
from tidychef import acquire, preview, filters
from tidychef.direction import right, below
from tidychef.selection import CsvSelectable, filters
from tidychef.output import TidyData, Column

table: CsvSelectable = acquire.csv.http("https://raw.githubusercontent.com/mikeAdamss/tidychef/main/tests/fixtures/csv/bands-wide.csv")

def e_ending_stones(line: Dict[str, str]) -> str:
    """
    Returns "True" as string if member ends with "e"
    and band is "Rolling Stones"
    """
    if line["Member"].endswith("e") and line["Band"] == "Rolling Stones":
        return "True"
    return "False"

observations = table.filter(filters.is_numeric).label_as("Observation")
bands = (table.excel_ref("A3") | table.excel_ref("G3")).label_as("Band")
assets = table.excel_ref('2').is_not_blank().label_as("Asset")
members = (table.excel_ref("B") | table.excel_ref("H")).is_not_blank().label_as("Member")
preview(observations, bands, assets, members)

tidy_data = TidyData(
    observations,
    Column(bands.attach_closest(right)),
    Column(assets.attach_directly(below)),
    Column(members.attach_directly(right)),
    Column.horizontal_condition("Stones ending with e", e_ending_stones)
)
print(tidy_data)

Observation

Band

Asset

Member

Unnamed Table

	A	B	C	D	E	F	G	H	I	J	K
1
2			Houses	Cars	Boats				Houses	Cars	Boats
3	Beatles						Rolling Stones
4		John	1	5	9			Keith	2	6	10
5		Paul	2	6	10			Mick	3	7	11
6		George	2	7	11			Charlie	3	8	12
7		Ringo	4	8	12			Ronnie	5	9	13
8

Observation	Band	Asset	Member	Stones ending with e
1	Beatles	Houses	John	False
5	Beatles	Cars	John	False
9	Beatles	Boats	John	False
2	Rolling Stones	Houses	Keith	False
6	Rolling Stones	Cars	Keith	False
10	Rolling Stones	Boats	Keith	False
2	Beatles	Houses	Paul	False
6	Beatles	Cars	Paul	False
10	Beatles	Boats	Paul	False
3	Rolling Stones	Houses	Mick	False
7	Rolling Stones	Cars	Mick	False
11	Rolling Stones	Boats	Mick	False
2	Beatles	Houses	George	False
7	Beatles	Cars	George	False
11	Beatles	Boats	George	False
3	Rolling Stones	Houses	Charlie	True
8	Rolling Stones	Cars	Charlie	True
12	Rolling Stones	Boats	Charlie	True
4	Beatles	Houses	Ringo	False
8	Beatles	Cars	Ringo	False
12	Beatles	Boats	Ringo	False
5	Rolling Stones	Houses	Ronnie	True
9	Rolling Stones	Cars	Ronnie	True
13	Rolling Stones	Boats	Ronnie	True