Counting a categorical variable, three ways

The first summary of a categorical variable

A categorical variable has no mean. What it has is a frequency table: each category and how many records fall in it, usually with a relative frequency — the count divided by the total — beside it. For prov in the orders sheet that table is the whole description: four provinces, twelve orders, and the share each province holds.

Worked example — COUNTIF, filled down

List the categories once, in H2:H5: AB, BC, ON, QC. In I2 type

=COUNTIF($B$2:$B$13, H2)

and fill it down to I5. COUNTIF takes a range to look in and a criterion to match. The range is $B$2:$B$13 with both parts locked, because every copy must scan the same twelve provinces; the criterion is H2, relative, because each copy must ask about its own row's province. That pairing — absolute range, relative criterion — is the whole reason Module 3 came first. Fill down and read: AB 2, BC 3, ON 4, QC 3. =SUM(I2:I5) gives 12, and it must, or a category was missed or misspelled.

For relative frequency, in J2 type =I2/SUM($I$2:$I$5) and fill down: 0.1667 · 0.25 · 0.3333 · 0.25. The absolute range inside SUM is the share-of-total pattern from Lesson 3.3. The column sums to 1 — 1.0000 here, and up to rounding always; a relative-frequency column that sums to 0.9 or 1.2 is a wrong denominator. Sheets and Excel match COUNTIF text case-insensitively, so on and ON count together; Module 5 shows why that matters.

The same table in R

R does the whole thing in one call:

> table(orders$prov)

AB BC ON QC 
 2  3  4  3 

The categories come out sorted alphabetically, exactly as in H2:H5, with counts underneath. prop.table() turns any table into shares:

> round(prop.table(table(orders$prov)), 4)

    AB     BC     ON     QC 
0.1667 0.2500 0.3333 0.2500 

One default to know: table() drops NA. If a province were missing, table() would report the eleven known ones and say nothing; table(orders$prov, useNA = "ifany") adds an <NA> column with the count of missing values. A frequency table that silently omits the blanks is the categorical version of AVERAGE skipping a blank cell (Lesson 2.6).

Three counting functions

COUNT counts numbers only; on the prov column it returns 0, which is the fastest test for a column stored as text. COUNTA counts every non-blank cell, so =COUNTA(B2:B13) is 12. COUNTBLANK counts the empties. COUNTIF is the one that counts matching cells and builds the table.

Type the four calls, then build the table

Type both spreadsheet formulas and both R calls. The worksheet asks for the four counts; the code block rebuilds the table in plain Python with a dictionary, so the mechanism — walk the column, add one to the matching key — is visible.

CRISP-DM: a frequency table is data understanding → explore data.