01-02: Data Representation and Organization¶

Raw data arrives messy. Before any statistic can be trusted, the data has to be laid out in a shape every tool can read the same way. This note covers that shape — tidy data — plus coding, sorting, arrays, and the checks that catch problems before they reach a formula.

The Rectangular Data Layout¶

The universal layout for statistical data:

One row = one observation (one person, one order, one day, one specimen).
One column = one variable (one measured attribute).
One cell = one value (never "72 kg" or "5–7" or two things separated by a slash).

| student_id | gender | major   | study_hours | midterm | final |
|------------|--------|---------|-------------|---------|-------|
| S001       | F      | Biology | 12.5        | 78      | 84    |
| S002       | M      | Business| 6.0         | 65      | 61    |
| S003       | F      | Biology | 15.0        | 91      | 95    |

This is called a data matrix, a flat file, or tidy data. Excel calls it a list or table; R calls it a data.frame; Python calls it a DataFrame. Same idea, three names.

Rules that keep it usable:

Exactly one header row, at the very top, with short unique names.
No blank rows or blank columns inside the block.
No merged cells, no sub-headings, no totals row inside the data.
No units, footnotes, or symbols inside numeric cells (1,250 kg → 1250).
Missing values are genuinely empty or a documented code (NA), never 0, never -, never "n/a" in a numeric column.
Dates in a real date format, one convention throughout.

Warning

The single most common cause of a wrong answer in an intro statistics course is a "number" column that Excel is storing as text — usually because of a stray space, a currency symbol, or a comma. AVERAGE silently skips text, so you get an answer, just not the right one.

Wide vs. Long Format¶

The same data can be arranged two ways, and different procedures want different ones.

Wide — one row per subject, one column per occasion:

| student | quiz1 | quiz2 | quiz3 |
|---------|-------|-------|-------|
| S001    | 8     | 7     | 9     |
| S002    | 6     | 8     | 7     |

Long — one row per subject-occasion:

| student | quiz  | score |
|---------|-------|-------|
| S001    | quiz1 | 8     |
| S001    | quiz2 | 7     |
| S001    | quiz3 | 9     |
| S002    | quiz1 | 6     |

Use wide for	Use long for
Paired t-tests (Chapter 11)	Grouped boxplots and bar charts
Correlation between occasions (Chapter 13)	One-way ANOVA (Chapter 12)
Excel's `t-Test: Paired Two Sample` dialog	R's `aov(score ~ quiz)` formula syntax

Converting between them is a routine operation in every tool — reshape rather than retype.

Coding Categorical Data¶

Qualitative data is often stored as short codes for speed and consistency. Two rules:

Keep a codebook — a separate sheet listing every variable, its allowed values, and what each means.
Never let a code drift (F, f, Female, FEMALE are four different categories to a computer).

CODEBOOK
variable     type      allowed values                     meaning
-----------  --------  ---------------------------------  ----------------------------
student_id   nominal   S001 … S999                        unique key
gender       nominal   F, M, X                             F=female, M=male, X=other
major        nominal   Biology, Business, CS, Nursing      declared major
study_hours  ratio     0.0 – 40.0                          hours studied per week
midterm      ratio     0 – 100                             midterm score, blank = absent

Dummy (indicator) coding turns a category into 0/1 columns so it can enter a regression (Chapter 13): a variable with k categories becomes k − 1 dummy columns, with the omitted category as the reference.

Sorting, Filtering, and Ranking¶

Three operations that come up constantly:

Sorting — order rows by one or more columns. Essential before finding a median, quartiles, or a stem-and-leaf plot by hand. Always sort the whole row block, never a single column, or the rows come apart.
Filtering — keep only rows meeting a condition (major = "Biology"), to describe a subgroup.
Ranking — assign 1st, 2nd, 3rd; the basis for percentiles and nonparametric methods.

Data Quality Checks Before Any Analysis¶

Run these every time, in this order:

Check	What you are looking for
Row and column count	Does it match the source? Did a filter hide rows?
Duplicates	Repeated ID values — usually a merge gone wrong
Type check	Numeric columns actually numeric, dates actually dates
Range check	Age = 250, score = 105, negative income → impossible values
Category check	Distinct values of each nominal column; catches `F`/`f`/`Female` drift
Missing values	How many, in which columns, and are they random or systematic?
Extreme values	Min and max of every numeric column (formal outlier rules come in 04-02)

Excel¶

Build a proper table: select the block → Insert ▸ Table (Ctrl+T, "My table has headers"). You get structured references, automatic filter buttons, and a range that grows with the data.

' ── Quality checks ────────────────────────────────────────────────
=COUNTA(A2:A1000)                          ' rows with an ID
=COUNTA(A2:A1000)-COUNTA(UNIQUE(A2:A1000)) ' duplicate IDs (0 is good)
=COUNTBLANK(E2:E1000)                      ' missing midterm scores
=SUMPRODUCT(--ISTEXT(E2:E1000))            ' numbers stored as text (0 is good)
=MIN(E2:E1000)  &  " to "  &  MAX(E2:E1000)   ' range check
=UNIQUE(B2:B1000)                          ' distinct categories — spot F/f drift

' ── Cleaning ──────────────────────────────────────────────────────
=TRIM(CLEAN(B2))                           ' strip stray/hidden spaces
=UPPER(TRIM(B2))                           ' normalize category case
=VALUE(SUBSTITUTE(SUBSTITUTE(E2,",",""),"$",""))   ' text -> real number

' ── Coding ────────────────────────────────────────────────────────
=IF(B2="F", 1, 0)                          ' dummy variable for gender
=IFS(D2<5,"Low", D2<15,"Medium", TRUE,"High")      ' band a numeric variable

' ── Ranking & lookup ──────────────────────────────────────────────
=RANK.EQ(E2, $E$2:$E$1000, 0)              ' 1 = highest score
=SORT(A2:F1000, 5, -1)                     ' sort whole block by col 5, descending
=FILTER(A2:F1000, C2:C1000="Biology")      ' subgroup

' ── Reshape ───────────────────────────────────────────────────────
' Wide -> Long : Data ▸ Get & Transform ▸ From Table/Range ▸
'                select quiz columns ▸ Transform ▸ Unpivot Columns
' Long -> Wide : Insert ▸ PivotTable (rows = student, columns = quiz,
'                values = score)

Tip

Analysis ToolPak — turn it on once and it powers most of this course: File ▸ Options ▸ Add-ins ▸ Manage: Excel Add-ins ▸ Go ▸ tick "Analysis ToolPak". It then appears as Data ▸ Data Analysis.

R¶

library(dplyr)
library(tidyr)
library(readxl)

# ── Read ───────────────────────────────────────────────────────────
scores <- read.csv("students.csv", stringsAsFactors = FALSE)
scores <- read_excel("students.xlsx", sheet = "data")     # from Excel

# ── Inspect ────────────────────────────────────────────────────────
dim(scores)          # rows, columns
str(scores)          # structure: name, type, first values
head(scores, 5)
summary(scores)      # per-column summary — instant range check
colSums(is.na(scores))               # missing values per column
sum(duplicated(scores$student_id))   # duplicate keys (0 is good)
table(scores$gender, useNA = "ifany")  # category check — spots F/f drift

# ── Clean ──────────────────────────────────────────────────────────
scores <- scores %>%
  mutate(
    gender      = toupper(trimws(gender)),
    gender      = factor(gender, levels = c("F", "M", "X")),
    major       = factor(major),
    study_hours = as.numeric(study_hours)
  )

# ── Code ───────────────────────────────────────────────────────────
scores <- scores %>%
  mutate(
    female = ifelse(gender == "F", 1L, 0L),                     # dummy
    band   = cut(study_hours, breaks = c(-Inf, 5, 15, Inf),
                 labels = c("Low", "Medium", "High"))           # banding
  )

# ── Sort / filter / rank ───────────────────────────────────────────
arrange(scores, desc(midterm))
filter(scores, major == "Biology", midterm >= 70)
scores$rank <- rank(-scores$midterm, ties.method = "min")

# ── Reshape ────────────────────────────────────────────────────────
long <- pivot_longer(scores, cols = starts_with("quiz"),
                     names_to = "quiz", values_to = "score")
wide <- pivot_wider(long, names_from = quiz, values_from = score)

Python¶

import pandas as pd

# ── Read ───────────────────────────────────────────────────────────
df = pd.read_csv("students.csv")
df = pd.read_excel("students.xlsx", sheet_name="data")

# ── Inspect ────────────────────────────────────────────────────────
df.shape                     # (rows, columns)
df.info()                    # dtypes + non-null counts
df.head()
df.describe()                # numeric summary — instant range check
df.isna().sum()              # missing values per column
df["student_id"].duplicated().sum()      # duplicate keys
df["gender"].value_counts(dropna=False)  # category check

# ── Clean ──────────────────────────────────────────────────────────
df["gender"] = df["gender"].str.strip().str.upper()
df["study_hours"] = pd.to_numeric(df["study_hours"], errors="coerce")
df["major"] = df["major"].astype("category")

# ── Code ───────────────────────────────────────────────────────────
df["female"] = (df["gender"] == "F").astype(int)                 # dummy
df["band"] = pd.cut(df["study_hours"], bins=[-float("inf"), 5, 15, float("inf")],
                    labels=["Low", "Medium", "High"])
dummies = pd.get_dummies(df["major"], prefix="major", drop_first=True)

# ── Sort / filter / rank ───────────────────────────────────────────
df.sort_values("midterm", ascending=False)
df[(df["major"] == "Biology") & (df["midterm"] >= 70)]
df["rank"] = df["midterm"].rank(ascending=False, method="min")

# ── Reshape ────────────────────────────────────────────────────────
long = df.melt(id_vars="student_id",
               value_vars=["quiz1", "quiz2", "quiz3"],
               var_name="quiz", value_name="score")
wide = long.pivot(index="student_id", columns="quiz", values="score")

Quick Reference¶

Task	Excel	R	Python
Read a file	Open / Get Data	`read.csv()`, `read_excel()`	`pd.read_csv()`, `pd.read_excel()`
Dimensions	Status bar / `ROWS`	`dim(df)`	`df.shape`
Structure	—	`str(df)`	`df.info()`
Quick summary	Data Analysis ▸ Descriptive Statistics	`summary(df)`	`df.describe()`
Missing count	`COUNTBLANK`	`colSums(is.na(df))`	`df.isna().sum()`
Duplicates	Data ▸ Remove Duplicates	`duplicated(x)`	`df.duplicated()`
Distinct values	`UNIQUE()`	`unique(x)` / `table(x)`	`s.unique()` / `value_counts()`
Sort	Data ▸ Sort / `SORT()`	`arrange()` / `order()`	`df.sort_values()`
Filter	AutoFilter / `FILTER()`	`filter()` / `df[cond, ]`	`df[cond]`
Rank	`RANK.EQ`	`rank()`	`s.rank()`
Band a variable	`IFS` / `VLOOKUP`	`cut()`	`pd.cut()`
Dummy code	`IF(x="F",1,0)`	`model.matrix()`	`pd.get_dummies()`
Wide → long	Power Query ▸ Unpivot	`pivot_longer()`	`df.melt()`
Long → wide	PivotTable	`pivot_wider()`	`df.pivot()`

Common Mistakes¶

Sorting one column without the others — silently scrambles every row.
Typing a totals row inside the data block, then including it in AVERAGE.
Storing 0 for "not answered", which then drags the mean toward zero. Leave it blank or use NA.
Two categories that differ only by a trailing space — they count as two groups forever.
Merged header cells; every tool but Excel reads them as blanks.

Exercises: 01-02: Exercises — Data Representation and Organization

⬅️ Previous: 01-01: Statistics Basics and Data Types ➡️ Next: 02-01: Frequency Distributions