01-02: Exercises — Data Representation and Organization¶
Notes reference: 01-02: Data Representation and Organization
Q1: Fix the layout¶
A spreadsheet arrives looking like this:
SALES REPORT — Q3
(all figures in USD)
Region Jul Aug Sep TOTAL
North $12,400 $13,100 $11,900 $37,400
South $9,800 n/a $10,200 $20,000
East $15,000 $14,700 $16,100 $45,800
TOTAL $37,200 $27,800 $38,200 $103,200
List six things that break the tidy-data rules, and write the corrected layout.
Solution
1. Title and subtitle rows above the header → delete; put them in a separate sheet or cell note
2. Currency symbols and commas inside numeric cells → store 12400, format for display only
3. "n/a" in a numeric column → leave the cell EMPTY (or use a documented NA code)
4. A blank row inside the data block → remove it
5. A TOTAL row inside the data block → move totals outside, or compute with a formula elsewhere
6. A TOTAL column inside the data block → derived values belong in a separate summary area
Corrected (wide) layout
| region | jul | aug | sep |
|--------|-------|-------|-------|
| North | 12400 | 13100 | 11900 |
| South | 9800 | | 10200 |
| East | 15000 | 14700 | 16100 |
Corrected (long) layout — better for grouped charts and ANOVA:
| region | month | sales |
|--------|-------|-------|
| North | Jul | 12400 |
| North | Aug | 13100 |
| ... | ... | ... |
| South | Aug | |
Q2: Wide or long?¶
State which format each task needs.
- A paired t-test comparing Jul and Sep sales for each region
- A grouped bar chart of sales by month, coloured by region
- Correlation between Jul and Aug sales across regions
- One-way ANOVA testing whether the three months differ
Solution
1. WIDE — a paired test needs the two measurements side by side in one row
2. LONG — chart libraries map a "group" column to colour
3. WIDE — correlation needs two aligned columns
4. LONG — aov(sales ~ month) needs one value column and one factor column
Q3: Data-quality checklist¶
You receive a 5,000-row customer file. Write the checks you would run before computing anything, in the order you would run them.
Solution
# R
dim(customers) # 1. row/column count matches the source?
str(customers) # 2. are types what you expect?
sum(duplicated(customers$customer_id)) # 3. duplicate keys (want 0)
colSums(is.na(customers)) # 4. missing values, per column
summary(customers) # 5. range check — impossible min/max?
sapply(customers[sapply(customers, is.character)], function(x) unique(x))
# 6. category drift: "F" vs "f" vs "Female"
range(customers$signup_date, na.rm = TRUE) # 7. date sanity
# Python
df.shape # 1
df.info() # 2
df["customer_id"].duplicated().sum() # 3
df.isna().sum() # 4
df.describe() # 5
for c in df.select_dtypes("object"):
print(c, df[c].unique()[:20]) # 6
df["signup_date"].agg(["min", "max"]) # 7
' Excel
=COUNTA(A2:A5001) ' 1
=SUMPRODUCT(--ISTEXT(E2:E5001)) ' 2 numbers stored as text (want 0)
=COUNTA(A2:A5001)-COUNTA(UNIQUE(A2:A5001)) ' 3 duplicates (want 0)
=COUNTBLANK(E2:E5001) ' 4
=MIN(E2:E5001) & " to " & MAX(E2:E5001) ' 5
=UNIQUE(B2:B5001) ' 6
=TEXT(MIN(F2:F5001),"yyyy-mm-dd") ' 7
Q4: Clean a messy category column¶
The gender column contains: F, f, F, Female, M, male, M, "".
Normalize it to exactly three levels: F, M, X (other/unknown).
Solution
' Excel — normalize, then map
=UPPER(TRIM(B2)) ' " F" -> "F", "female" -> "FEMALE"
=IFS(LEFT(C2,1)="F","F", LEFT(C2,1)="M","M", TRUE,"X")
' Then: Data ▸ Data Validation ▸ List ▸ Source: F,M,X to stop future drift
# R
df$gender <- toupper(trimws(df$gender))
df$gender <- substr(df$gender, 1, 1)
df$gender <- ifelse(df$gender %in% c("F", "M"), df$gender, "X")
df$gender <- factor(df$gender, levels = c("F", "M", "X"))
table(df$gender, useNA = "ifany")
# Python
s = df["gender"].fillna("").str.strip().str.upper().str[:1]
df["gender"] = pd.Categorical(s.where(s.isin(["F", "M"]), "X"),
categories=["F", "M", "X"])
df["gender"].value_counts(dropna=False)
Q5: Band a numeric variable¶
Create an age_group column from age: Under 25, 25–39, 40–59, 60+.
Solution
=IFS(D2<25,"Under 25", D2<40,"25-39", D2<60,"40-59", TRUE,"60+")
' Or with a lookup table (more maintainable — edit the table, not the formula):
=LOOKUP(D2, {0,25,40,60}, {"Under 25","25-39","40-59","60+"})
df$age_group <- cut(df$age,
breaks = c(-Inf, 25, 40, 60, Inf),
labels = c("Under 25", "25-39", "40-59", "60+"),
right = FALSE) # right = FALSE -> [25, 40)
table(df$age_group)
df["age_group"] = pd.cut(df["age"],
bins=[-np.inf, 25, 40, 60, np.inf],
labels=["Under 25", "25-39", "40-59", "60+"],
right=False)
df["age_group"].value_counts().sort_index()
Watch the boundary.
right = FALSE(R) andright=False(pandas) make the intervals[25, 40)— a 40-year-old lands in40-59, not25-39. Excel'sIFSabove uses<, matching that convention.
Q6: Dummy code a categorical predictor¶
major has four levels: Biology, Business, CS, Nursing. Create dummy variables for a regression.
Solution
4 levels → 3 dummy columns. The omitted level (Biology) is the REFERENCE.
| major | major_Business | major_CS | major_Nursing |
|----------|----------------|----------|---------------|
| Biology | 0 | 0 | 0 | ← reference
| Business | 1 | 0 | 0 |
| CS | 0 | 1 | 0 |
| Nursing | 0 | 0 | 1 |
model.matrix(~ major, data = df)[, -1] # R drops the reference automatically
# Or just use factor(major) directly in lm() — R handles it for you
Never create a fourth dummy. Four dummies plus an intercept are perfectly collinear — the "dummy-variable trap".
Q7: Reshape¶
Convert this wide table to long, then back to wide.
| student | quiz1 | quiz2 | quiz3 |
|---------|-------|-------|-------|
| S001 | 8 | 7 | 9 |
| S002 | 6 | 8 | 7 |
Solution
library(tidyr)
long <- pivot_longer(wide,
cols = starts_with("quiz"),
names_to = "quiz",
values_to = "score")
long
# student quiz score
# S001 quiz1 8
# S001 quiz2 7
# S001 quiz3 9
# S002 quiz1 6
# ...
back <- pivot_wider(long, names_from = quiz, values_from = score)
long = wide.melt(id_vars="student",
value_vars=["quiz1", "quiz2", "quiz3"],
var_name="quiz", value_name="score")
back = long.pivot(index="student", columns="quiz", values="score").reset_index()
' Wide -> Long: Data ▸ Get & Transform ▸ From Table/Range
' select quiz1:quiz3 ▸ Transform ▸ Unpivot Columns ▸ Close & Load
' Long -> Wide: Insert ▸ PivotTable (Rows = student, Columns = quiz,
' Values = score with Sum or Average)
Q8: Sort, filter, rank without breaking the data¶
Using the students table, produce: (a) the whole table sorted by midterm descending, (b) only Biology majors scoring at least 70, (c) a rank column with 1 = highest midterm.
Solution
' (a) Select the WHOLE block A1:F101 ▸ Data ▸ Sort ▸ Sort by "midterm" ▸ Largest to Smallest
' Never sort a single column — it detaches rows from each other.
=SORT(A2:F101, 5, -1) ' 365 dynamic array
' (b)
=FILTER(A2:F101, (C2:C101="Biology")*(E2:E101>=70)) ' * acts as AND
' (c)
=RANK.EQ(E2, $E$2:$E$101, 0) ' 0 = descending
library(dplyr)
arrange(students, desc(midterm)) # (a)
filter(students, major == "Biology", midterm >= 70) # (b)
students$rank <- rank(-students$midterm, ties.method = "min") # (c)
students.sort_values("midterm", ascending=False) # (a)
students[(students["major"] == "Biology") & (students["midterm"] >= 70)] # (b)
students["rank"] = students["midterm"].rank(ascending=False, method="min") # (c)
⬅️ Previous: 01-01: Exercises — Statistics Basics and Data Types ➡️ Next: 02-01: Exercises — Frequency Distributions