&, |, !, %in%, and the proportion idiom

◈ 9 cards

subset(orders, prov %in% c("ON", "QC") & amount > 300) keeps 4 rows. prov == c("ON", "QC") is NOT "either" — it recycles the pair down the column. sum(orders$amount > 500) counts the TRUEs, 5; mean(orders$amount > 500) is the proportion, 0.4167.

Two conditions, one filter

Module 6 filtered with two AutoFilter criteria at once. In R the conditions are joined with & (both) or | (either), and ! negates. "Province is ON or QC, and amount over 300":

> subset(orders, prov %in% c("ON", "QC") & amount > 300)
  order_id prov channel amount units order_date
1     1001   ON  Online    420     3 2025-01-06
3     1003   ON   Store    760     5 2025-01-15
6     1006   ON  Online    510     4 2025-02-10
9     1009   ON   Store    305     2 2025-03-04

Four rows — all Ontario, because every Quebec order is under 300. %in% means "is one of": prov %in% c("ON", "QC") is TRUE for 7 of the 12 rows.

Worked example — why `==` is the wrong "or"

The tempting spelling is prov == c("ON", "QC"). It runs without error and it is wrong:

> orders$prov == c("ON", "QC")
 [1]  TRUE  TRUE  TRUE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE FALSE FALSE
> sum(orders$prov == c("ON", "QC"))
[1] 4
> sum(orders$prov %in% c("ON", "QC"))
[1] 7

== compares element by element, and Module 9's recycling rule stretches the two-element vector to twelve: row 1 against "ON", row 2 against "QC", row 3 against "ON" … Row 6 is ON but is compared with "QC", so it is FALSE. Four matches instead of seven, no warning (12 is a multiple of 2). The other tempting spelling, prov == "ON" | "QC", is an error — "QC" alone is not a condition:

> orders$prov == "ON" | "QC"
Error in orders$prov == "ON" | "QC" : 
  operations are possible only for numeric, logical or complex types

Either write it out, prov == "ON" | prov == "QC", or use %in%.

`&&` is not `&`

The doubled forms && and || are for single TRUE/FALSE values inside an if. On a column they fail:

> orders$amount > 500 && orders$prov == "ON"
Error in orders$amount > 500 && orders$prov == "ON" : 
  'length = 12' in coercion to 'logical(1)'

Columns take the single & and |.

Counting and the proportion idiom

A condition is a vector of TRUE and FALSE, and arithmetic treats TRUE as 1. So sum() of a condition is a count and mean() of it is a proportion:

> sum(orders$amount > 500)
[1] 5
> mean(orders$amount > 500)
[1] 0.4166667

41.7 % of orders are over 500 — COUNTIF(D2:D13, ">500") / COUNT(D2:D13) in one expression. sum(!(orders$prov == "ON")) is 8: the non-Ontario orders.

Type the filter and the proportion, then reproduce it in Python

CRISP-DM: a compound filter is data preparation → select data; the proportion is data understanding → explore data.