audit-labs/tutorials
Learn how to perform data analysis, scripting, automation, and more!
clone: git clone https://gitbay.org/audit-labs/tutorials.git
main: notebooks/sampling/sampling.ipynb · raw
1{
2 "cells": [
3 {
4 "cell_type": "markdown",
5 "id": "a1b2c3d4-0001-0001-0001-000000000001",
6 "metadata": {},
7 "source": [
8 "# Audit Sampling\n",
9 "\n",
10 "Auditors rarely test every transaction or record in a population. Testing everything is time-consuming and often unnecessary — a well-chosen sample can give you the same level of confidence at a fraction of the effort.\n",
11 "\n",
12 "This notebook covers:\n",
13 "1. Why auditors sample, and key concepts\n",
14 "2. How to calculate an appropriate sample size\n",
15 "3. Three sampling methods: random, systematic, and stratified\n",
16 "4. How to export your sample with metadata for documentation\n",
17 "\n",
18 "We'll use a dataset of 100 vendor transactions as our population throughout."
19 ]
20 },
21 {
22 "cell_type": "markdown",
23 "id": "a1b2c3d4-0001-0001-0001-000000000002",
24 "metadata": {},
25 "source": [
26 "## 1. Why Auditors Sample\n",
27 "\n",
28 "**Population** — the full set of records you're auditing (e.g., all 5,000 invoices processed this year).\n",
29 "\n",
30 "**Sample** — a subset of that population you actually test.\n",
31 "\n",
32 "**Confidence level** — how certain you want to be that your sample reflects the population. Audits typically use 90% or 95%.\n",
33 "\n",
34 "**Tolerable error rate** — the maximum rate of errors you'd accept before concluding a control has failed. Common values are 5% or 10%.\n",
35 "\n",
36 "The core tradeoff: a higher confidence level or lower tolerable error rate means you need a larger sample. The relationship isn't linear — going from 90% to 95% confidence increases your sample size more than you might expect.\n",
37 "\n",
38 "**When to sample vs. test everything:**\n",
39 "- Sample when the population is large and testing everything isn't practical\n",
40 "- Test everything (100%) when the population is small (e.g., only 10 journal entries), when the control only fires occasionally, or when the risk is very high"
41 ]
42 },
43 {
44 "cell_type": "markdown",
45 "id": "a1b2c3d4-0001-0001-0001-000000000003",
46 "metadata": {},
47 "source": [
48 "## 2. Load the Population"
49 ]
50 },
51 {
52 "cell_type": "code",
53 "execution_count": 13,
54 "id": "7ab8b26e",
55 "metadata": {},
56 "outputs": [
57 {
58 "name": "stdout",
59 "output_type": "stream",
60 "text": [
61 "Defaulting to user installation because normal site-packages is not writeable\n",
62 "Requirement already satisfied: pandas in /Users/cmc/Library/Python/3.9/lib/python/site-packages (2.3.3)\n",
63 "Collecting openpyxl\n",
64 " Downloading openpyxl-3.1.5-py2.py3-none-any.whl.metadata (2.5 kB)\n",
65 "Requirement already satisfied: numpy>=1.22.4 in /Users/cmc/Library/Python/3.9/lib/python/site-packages (from pandas) (2.0.2)\n",
66 "Requirement already satisfied: python-dateutil>=2.8.2 in /Users/cmc/Library/Python/3.9/lib/python/site-packages (from pandas) (2.9.0.post0)\n",
67 "Requirement already satisfied: pytz>=2020.1 in /Users/cmc/Library/Python/3.9/lib/python/site-packages (from pandas) (2025.2)\n",
68 "Requirement already satisfied: tzdata>=2022.7 in /Users/cmc/Library/Python/3.9/lib/python/site-packages (from pandas) (2025.3)\n",
69 "Collecting et-xmlfile (from openpyxl)\n",
70 " Downloading et_xmlfile-2.0.0-py3-none-any.whl.metadata (2.7 kB)\n",
71 "Requirement already satisfied: six>=1.5 in /Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/site-packages (from python-dateutil>=2.8.2->pandas) (1.15.0)\n",
72 "Downloading openpyxl-3.1.5-py2.py3-none-any.whl (250 kB)\n",
73 "Downloading et_xmlfile-2.0.0-py3-none-any.whl (18 kB)\n",
74 "Installing collected packages: et-xmlfile, openpyxl\n",
75 "\u001b[2K \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m2/2\u001b[0m [openpyxl]\n",
76 "\u001b[1A\u001b[2KSuccessfully installed et-xmlfile-2.0.0 openpyxl-3.1.5\n",
77 "Note: you may need to restart the kernel to use updated packages.\n"
78 ]
79 }
80 ],
81 "source": [
82 "%pip install pandas openpyxl"
83 ]
84 },
85 {
86 "cell_type": "code",
87 "execution_count": 4,
88 "id": "a1b2c3d4-0001-0001-0001-000000000004",
89 "metadata": {},
90 "outputs": [
91 {
92 "name": "stdout",
93 "output_type": "stream",
94 "text": [
95 "Population size: 100 records\n"
96 ]
97 },
98 {
99 "data": {
100 "text/html": [
101 "<div>\n",
102 "<style scoped>\n",
103 " .dataframe tbody tr th:only-of-type {\n",
104 " vertical-align: middle;\n",
105 " }\n",
106 "\n",
107 " .dataframe tbody tr th {\n",
108 " vertical-align: top;\n",
109 " }\n",
110 "\n",
111 " .dataframe thead th {\n",
112 " text-align: right;\n",
113 " }\n",
114 "</style>\n",
115 "<table border=\"1\" class=\"dataframe\">\n",
116 " <thead>\n",
117 " <tr style=\"text-align: right;\">\n",
118 " <th></th>\n",
119 " <th>Transaction_ID</th>\n",
120 " <th>Date</th>\n",
121 " <th>Vendor</th>\n",
122 " <th>Amount</th>\n",
123 " <th>Department</th>\n",
124 " <th>Approved_By</th>\n",
125 " <th>Payment_Method</th>\n",
126 " </tr>\n",
127 " </thead>\n",
128 " <tbody>\n",
129 " <tr>\n",
130 " <th>0</th>\n",
131 " <td>T0001</td>\n",
132 " <td>2024-01-03</td>\n",
133 " <td>Staples</td>\n",
134 " <td>124.50</td>\n",
135 " <td>Marketing</td>\n",
136 " <td>J. Rivera</td>\n",
137 " <td>Credit Card</td>\n",
138 " </tr>\n",
139 " <tr>\n",
140 " <th>1</th>\n",
141 " <td>T0002</td>\n",
142 " <td>2024-01-05</td>\n",
143 " <td>AWS</td>\n",
144 " <td>3200.00</td>\n",
145 " <td>Engineering</td>\n",
146 " <td>S. Patel</td>\n",
147 " <td>ACH</td>\n",
148 " </tr>\n",
149 " <tr>\n",
150 " <th>2</th>\n",
151 " <td>T0003</td>\n",
152 " <td>2024-01-07</td>\n",
153 " <td>Office Depot</td>\n",
154 " <td>87.25</td>\n",
155 " <td>HR</td>\n",
156 " <td>M. Chen</td>\n",
157 " <td>Credit Card</td>\n",
158 " </tr>\n",
159 " <tr>\n",
160 " <th>3</th>\n",
161 " <td>T0004</td>\n",
162 " <td>2024-01-08</td>\n",
163 " <td>Delta Airlines</td>\n",
164 " <td>1450.00</td>\n",
165 " <td>Sales</td>\n",
166 " <td>J. Rivera</td>\n",
167 " <td>Credit Card</td>\n",
168 " </tr>\n",
169 " <tr>\n",
170 " <th>4</th>\n",
171 " <td>T0005</td>\n",
172 " <td>2024-01-10</td>\n",
173 " <td>Adobe</td>\n",
174 " <td>599.99</td>\n",
175 " <td>Marketing</td>\n",
176 " <td>M. Chen</td>\n",
177 " <td>ACH</td>\n",
178 " </tr>\n",
179 " </tbody>\n",
180 "</table>\n",
181 "</div>"
182 ],
183 "text/plain": [
184 " Transaction_ID Date Vendor Amount Department \\\n",
185 "0 T0001 2024-01-03 Staples 124.50 Marketing \n",
186 "1 T0002 2024-01-05 AWS 3200.00 Engineering \n",
187 "2 T0003 2024-01-07 Office Depot 87.25 HR \n",
188 "3 T0004 2024-01-08 Delta Airlines 1450.00 Sales \n",
189 "4 T0005 2024-01-10 Adobe 599.99 Marketing \n",
190 "\n",
191 " Approved_By Payment_Method \n",
192 "0 J. Rivera Credit Card \n",
193 "1 S. Patel ACH \n",
194 "2 M. Chen Credit Card \n",
195 "3 J. Rivera Credit Card \n",
196 "4 M. Chen ACH "
197 ]
198 },
199 "execution_count": 4,
200 "metadata": {},
201 "output_type": "execute_result"
202 }
203 ],
204 "source": [
205 "import pandas as pd\n",
206 "import math\n",
207 "\n",
208 "# Load the transaction population\n",
209 "df = pd.read_csv('transactions.csv')\n",
210 "\n",
211 "print(f\"Population size: {len(df)} records\")\n",
212 "df.head()"
213 ]
214 },
215 {
216 "cell_type": "code",
217 "execution_count": 5,
218 "id": "a1b2c3d4-0001-0001-0001-000000000005",
219 "metadata": {},
220 "outputs": [
221 {
222 "name": "stdout",
223 "output_type": "stream",
224 "text": [
225 "Transactions by Department:\n",
226 "Department\n",
227 "Sales 23\n",
228 "Engineering 20\n",
229 "Operations 16\n",
230 "Marketing 15\n",
231 "HR 14\n",
232 "IT 12\n",
233 "Name: count, dtype: int64\n",
234 "\n",
235 "Amount summary:\n",
236 "count 100.00\n",
237 "mean 1287.50\n",
238 "std 1856.76\n",
239 "min 22.60\n",
240 "25% 91.64\n",
241 "50% 420.00\n",
242 "75% 1670.00\n",
243 "max 6500.00\n",
244 "Name: Amount, dtype: float64\n"
245 ]
246 }
247 ],
248 "source": [
249 "# Get a quick overview of the population before sampling\n",
250 "print(\"Transactions by Department:\")\n",
251 "print(df['Department'].value_counts())\n",
252 "print()\n",
253 "print(\"Amount summary:\")\n",
254 "print(df['Amount'].describe().round(2))"
255 ]
256 },
257 {
258 "cell_type": "markdown",
259 "id": "a1b2c3d4-0001-0001-0001-000000000006",
260 "metadata": {},
261 "source": [
262 "## 3. Calculate Sample Size\n",
263 "\n",
264 "A common formula for attribute sampling (testing whether something is present or absent, like an approval) is:\n",
265 "\n",
266 "$$n = \\frac{Z^2 \\times p \\times (1 - p)}{E^2}$$\n",
267 "\n",
268 "Where:\n",
269 "- **Z** = Z-score for your confidence level (1.645 for 90%, 1.96 for 95%)\n",
270 "- **p** = expected error rate in the population (use 0.5 if unknown — this gives the most conservative/largest sample)\n",
271 "- **E** = tolerable error rate (e.g., 0.05 for 5%)\n",
272 "\n",
273 "This formula assumes an infinite population. For smaller populations, apply the **finite population correction (FPC)**:\n",
274 "\n",
275 "$$n_{adjusted} = \\frac{n}{1 + \\frac{n - 1}{N}}$$\n",
276 "\n",
277 "Where **N** is the population size."
278 ]
279 },
280 {
281 "cell_type": "code",
282 "execution_count": 6,
283 "id": "a1b2c3d4-0001-0001-0001-000000000007",
284 "metadata": {},
285 "outputs": [
286 {
287 "name": "stdout",
288 "output_type": "stream",
289 "text": [
290 "Population size: 100\n",
291 "Confidence level: 95%\n",
292 "Tolerable error rate: 5%\n",
293 "Base sample size: 385\n",
294 "Adjusted sample size: 80 (after finite population correction)\n"
295 ]
296 }
297 ],
298 "source": [
299 "# --- Configure your sampling parameters here ---\n",
300 "CONFIDENCE_LEVEL = 0.95 # 90% = 0.90, 95% = 0.95\n",
301 "TOLERABLE_ERROR = 0.05 # 5% = 0.05, 10% = 0.10\n",
302 "EXPECTED_ERROR = 0.5 # Use 0.5 (most conservative) if unknown\n",
303 "# ------------------------------------------------\n",
304 "\n",
305 "POPULATION_SIZE = len(df)\n",
306 "\n",
307 "# Z-scores for common confidence levels\n",
308 "z_scores = {0.90: 1.645, 0.95: 1.96, 0.99: 2.576}\n",
309 "z = z_scores.get(CONFIDENCE_LEVEL)\n",
310 "\n",
311 "if z is None:\n",
312 " raise ValueError(\"Confidence level must be 0.90, 0.95, or 0.99\")\n",
313 "\n",
314 "# Base sample size (infinite population)\n",
315 "n_base = (z**2 * EXPECTED_ERROR * (1 - EXPECTED_ERROR)) / (TOLERABLE_ERROR**2)\n",
316 "\n",
317 "# Finite population correction\n",
318 "n_adjusted = n_base / (1 + (n_base - 1) / POPULATION_SIZE)\n",
319 "SAMPLE_SIZE = math.ceil(n_adjusted)\n",
320 "\n",
321 "print(f\"Population size: {POPULATION_SIZE}\")\n",
322 "print(f\"Confidence level: {int(CONFIDENCE_LEVEL * 100)}%\")\n",
323 "print(f\"Tolerable error rate: {int(TOLERABLE_ERROR * 100)}%\")\n",
324 "print(f\"Base sample size: {math.ceil(n_base)}\")\n",
325 "print(f\"Adjusted sample size: {SAMPLE_SIZE} (after finite population correction)\")"
326 ]
327 },
328 {
329 "cell_type": "markdown",
330 "id": "a1b2c3d4-0001-0001-0001-000000000008",
331 "metadata": {},
332 "source": [
333 "Notice that the adjusted sample size is smaller than the base. When your population is small relative to the uncorrected sample size, the FPC has a meaningful impact. For very large populations, the correction is negligible.\n",
334 "\n",
335 "We'll use this `SAMPLE_SIZE` across all three sampling methods below."
336 ]
337 },
338 {
339 "cell_type": "markdown",
340 "id": "a1b2c3d4-0001-0001-0001-000000000009",
341 "metadata": {},
342 "source": [
343 "## 4. Method 1 — Random Sampling\n",
344 "\n",
345 "Every item in the population has an equal chance of being selected. This is the simplest method and appropriate for most general audit tests where the population is relatively homogeneous.\n",
346 "\n",
347 "We set a `random_state` so the sample is reproducible — running the notebook again will produce the same selection, which is important for documentation and review."
348 ]
349 },
350 {
351 "cell_type": "code",
352 "execution_count": 7,
353 "id": "a1b2c3d4-0001-0001-0001-000000000010",
354 "metadata": {},
355 "outputs": [
356 {
357 "name": "stdout",
358 "output_type": "stream",
359 "text": [
360 "Random sample: 80 records\n"
361 ]
362 },
363 {
364 "data": {
365 "text/html": [
366 "<div>\n",
367 "<style scoped>\n",
368 " .dataframe tbody tr th:only-of-type {\n",
369 " vertical-align: middle;\n",
370 " }\n",
371 "\n",
372 " .dataframe tbody tr th {\n",
373 " vertical-align: top;\n",
374 " }\n",
375 "\n",
376 " .dataframe thead th {\n",
377 " text-align: right;\n",
378 " }\n",
379 "</style>\n",
380 "<table border=\"1\" class=\"dataframe\">\n",
381 " <thead>\n",
382 " <tr style=\"text-align: right;\">\n",
383 " <th></th>\n",
384 " <th>Transaction_ID</th>\n",
385 " <th>Date</th>\n",
386 " <th>Vendor</th>\n",
387 " <th>Amount</th>\n",
388 " <th>Department</th>\n",
389 " <th>Approved_By</th>\n",
390 " <th>Payment_Method</th>\n",
391 " </tr>\n",
392 " </thead>\n",
393 " <tbody>\n",
394 " <tr>\n",
395 " <th>0</th>\n",
396 " <td>T0001</td>\n",
397 " <td>2024-01-03</td>\n",
398 " <td>Staples</td>\n",
399 " <td>124.50</td>\n",
400 " <td>Marketing</td>\n",
401 " <td>J. Rivera</td>\n",
402 " <td>Credit Card</td>\n",
403 " </tr>\n",
404 " <tr>\n",
405 " <th>1</th>\n",
406 " <td>T0004</td>\n",
407 " <td>2024-01-08</td>\n",
408 " <td>Delta Airlines</td>\n",
409 " <td>1450.00</td>\n",
410 " <td>Sales</td>\n",
411 " <td>J. Rivera</td>\n",
412 " <td>Credit Card</td>\n",
413 " </tr>\n",
414 " <tr>\n",
415 " <th>2</th>\n",
416 " <td>T0005</td>\n",
417 " <td>2024-01-10</td>\n",
418 " <td>Adobe</td>\n",
419 " <td>599.99</td>\n",
420 " <td>Marketing</td>\n",
421 " <td>M. Chen</td>\n",
422 " <td>ACH</td>\n",
423 " </tr>\n",
424 " <tr>\n",
425 " <th>3</th>\n",
426 " <td>T0006</td>\n",
427 " <td>2024-01-11</td>\n",
428 " <td>Zoom</td>\n",
429 " <td>149.00</td>\n",
430 " <td>IT</td>\n",
431 " <td>S. Patel</td>\n",
432 " <td>ACH</td>\n",
433 " </tr>\n",
434 " <tr>\n",
435 " <th>4</th>\n",
436 " <td>T0007</td>\n",
437 " <td>2024-01-14</td>\n",
438 " <td>FedEx</td>\n",
439 " <td>62.10</td>\n",
440 " <td>Operations</td>\n",
441 " <td>L. Gomez</td>\n",
442 " <td>Credit Card</td>\n",
443 " </tr>\n",
444 " <tr>\n",
445 " <th>...</th>\n",
446 " <td>...</td>\n",
447 " <td>...</td>\n",
448 " <td>...</td>\n",
449 " <td>...</td>\n",
450 " <td>...</td>\n",
451 " <td>...</td>\n",
452 " <td>...</td>\n",
453 " </tr>\n",
454 " <tr>\n",
455 " <th>75</th>\n",
456 " <td>T0096</td>\n",
457 " <td>2024-05-20</td>\n",
458 " <td>Slack</td>\n",
459 " <td>320.00</td>\n",
460 " <td>IT</td>\n",
461 " <td>S. Patel</td>\n",
462 " <td>ACH</td>\n",
463 " </tr>\n",
464 " <tr>\n",
465 " <th>76</th>\n",
466 " <td>T0097</td>\n",
467 " <td>2024-05-21</td>\n",
468 " <td>FedEx</td>\n",
469 " <td>41.70</td>\n",
470 " <td>Operations</td>\n",
471 " <td>L. Gomez</td>\n",
472 " <td>Credit Card</td>\n",
473 " </tr>\n",
474 " <tr>\n",
475 " <th>77</th>\n",
476 " <td>T0098</td>\n",
477 " <td>2024-05-22</td>\n",
478 " <td>Delta Airlines</td>\n",
479 " <td>1730.00</td>\n",
480 " <td>Sales</td>\n",
481 " <td>J. Rivera</td>\n",
482 " <td>Credit Card</td>\n",
483 " </tr>\n",
484 " <tr>\n",
485 " <th>78</th>\n",
486 " <td>T0099</td>\n",
487 " <td>2024-05-23</td>\n",
488 " <td>Office Depot</td>\n",
489 " <td>119.50</td>\n",
490 " <td>HR</td>\n",
491 " <td>M. Chen</td>\n",
492 " <td>Credit Card</td>\n",
493 " </tr>\n",
494 " <tr>\n",
495 " <th>79</th>\n",
496 " <td>T0100</td>\n",
497 " <td>2024-05-24</td>\n",
498 " <td>Salesforce</td>\n",
499 " <td>6500.00</td>\n",
500 " <td>Sales</td>\n",
501 " <td>J. Rivera</td>\n",
502 " <td>ACH</td>\n",
503 " </tr>\n",
504 " </tbody>\n",
505 "</table>\n",
506 "<p>80 rows × 7 columns</p>\n",
507 "</div>"
508 ],
509 "text/plain": [
510 " Transaction_ID Date Vendor Amount Department \\\n",
511 "0 T0001 2024-01-03 Staples 124.50 Marketing \n",
512 "1 T0004 2024-01-08 Delta Airlines 1450.00 Sales \n",
513 "2 T0005 2024-01-10 Adobe 599.99 Marketing \n",
514 "3 T0006 2024-01-11 Zoom 149.00 IT \n",
515 "4 T0007 2024-01-14 FedEx 62.10 Operations \n",
516 ".. ... ... ... ... ... \n",
517 "75 T0096 2024-05-20 Slack 320.00 IT \n",
518 "76 T0097 2024-05-21 FedEx 41.70 Operations \n",
519 "77 T0098 2024-05-22 Delta Airlines 1730.00 Sales \n",
520 "78 T0099 2024-05-23 Office Depot 119.50 HR \n",
521 "79 T0100 2024-05-24 Salesforce 6500.00 Sales \n",
522 "\n",
523 " Approved_By Payment_Method \n",
524 "0 J. Rivera Credit Card \n",
525 "1 J. Rivera Credit Card \n",
526 "2 M. Chen ACH \n",
527 "3 S. Patel ACH \n",
528 "4 L. Gomez Credit Card \n",
529 ".. ... ... \n",
530 "75 S. Patel ACH \n",
531 "76 L. Gomez Credit Card \n",
532 "77 J. Rivera Credit Card \n",
533 "78 M. Chen Credit Card \n",
534 "79 J. Rivera ACH \n",
535 "\n",
536 "[80 rows x 7 columns]"
537 ]
538 },
539 "execution_count": 7,
540 "metadata": {},
541 "output_type": "execute_result"
542 }
543 ],
544 "source": [
545 "RANDOM_SEED = 42 # Change this to get a different random sample; document what seed you used\n",
546 "\n",
547 "random_sample = df.sample(n=SAMPLE_SIZE, random_state=RANDOM_SEED).copy()\n",
548 "random_sample = random_sample.sort_values('Transaction_ID').reset_index(drop=True)\n",
549 "\n",
550 "print(f\"Random sample: {len(random_sample)} records\")\n",
551 "random_sample"
552 ]
553 },
554 {
555 "cell_type": "markdown",
556 "id": "a1b2c3d4-0001-0001-0001-000000000011",
557 "metadata": {},
558 "source": [
559 "## 5. Method 2 — Systematic Sampling\n",
560 "\n",
561 "Select every *k*th item from the population, starting from a random point. The interval *k* is calculated as `population size ÷ sample size`.\n",
562 "\n",
563 "Systematic sampling is useful when records are already ordered (e.g., by date or transaction ID) and you want even coverage across the full period. It's also easy to explain to a reviewer — \"we took every 4th transaction starting from record 2.\"\n",
564 "\n",
565 "**Caution:** Avoid systematic sampling if the data has a pattern that aligns with your interval (e.g., if transactions are grouped in batches of 4, every 4th record could always land on the same type)."
566 ]
567 },
568 {
569 "cell_type": "code",
570 "execution_count": 8,
571 "id": "a1b2c3d4-0001-0001-0001-000000000012",
572 "metadata": {},
573 "outputs": [
574 {
575 "name": "stdout",
576 "output_type": "stream",
577 "text": [
578 "Interval: every 1 records\n",
579 "Starting index: 0\n",
580 "Systematic sample: 80 records\n"
581 ]
582 },
583 {
584 "data": {
585 "text/html": [
586 "<div>\n",
587 "<style scoped>\n",
588 " .dataframe tbody tr th:only-of-type {\n",
589 " vertical-align: middle;\n",
590 " }\n",
591 "\n",
592 " .dataframe tbody tr th {\n",
593 " vertical-align: top;\n",
594 " }\n",
595 "\n",
596 " .dataframe thead th {\n",
597 " text-align: right;\n",
598 " }\n",
599 "</style>\n",
600 "<table border=\"1\" class=\"dataframe\">\n",
601 " <thead>\n",
602 " <tr style=\"text-align: right;\">\n",
603 " <th></th>\n",
604 " <th>Transaction_ID</th>\n",
605 " <th>Date</th>\n",
606 " <th>Vendor</th>\n",
607 " <th>Amount</th>\n",
608 " <th>Department</th>\n",
609 " <th>Approved_By</th>\n",
610 " <th>Payment_Method</th>\n",
611 " </tr>\n",
612 " </thead>\n",
613 " <tbody>\n",
614 " <tr>\n",
615 " <th>0</th>\n",
616 " <td>T0001</td>\n",
617 " <td>2024-01-03</td>\n",
618 " <td>Staples</td>\n",
619 " <td>124.50</td>\n",
620 " <td>Marketing</td>\n",
621 " <td>J. Rivera</td>\n",
622 " <td>Credit Card</td>\n",
623 " </tr>\n",
624 " <tr>\n",
625 " <th>1</th>\n",
626 " <td>T0002</td>\n",
627 " <td>2024-01-05</td>\n",
628 " <td>AWS</td>\n",
629 " <td>3200.00</td>\n",
630 " <td>Engineering</td>\n",
631 " <td>S. Patel</td>\n",
632 " <td>ACH</td>\n",
633 " </tr>\n",
634 " <tr>\n",
635 " <th>2</th>\n",
636 " <td>T0003</td>\n",
637 " <td>2024-01-07</td>\n",
638 " <td>Office Depot</td>\n",
639 " <td>87.25</td>\n",
640 " <td>HR</td>\n",
641 " <td>M. Chen</td>\n",
642 " <td>Credit Card</td>\n",
643 " </tr>\n",
644 " <tr>\n",
645 " <th>3</th>\n",
646 " <td>T0004</td>\n",
647 " <td>2024-01-08</td>\n",
648 " <td>Delta Airlines</td>\n",
649 " <td>1450.00</td>\n",
650 " <td>Sales</td>\n",
651 " <td>J. Rivera</td>\n",
652 " <td>Credit Card</td>\n",
653 " </tr>\n",
654 " <tr>\n",
655 " <th>4</th>\n",
656 " <td>T0005</td>\n",
657 " <td>2024-01-10</td>\n",
658 " <td>Adobe</td>\n",
659 " <td>599.99</td>\n",
660 " <td>Marketing</td>\n",
661 " <td>M. Chen</td>\n",
662 " <td>ACH</td>\n",
663 " </tr>\n",
664 " <tr>\n",
665 " <th>...</th>\n",
666 " <td>...</td>\n",
667 " <td>...</td>\n",
668 " <td>...</td>\n",
669 " <td>...</td>\n",
670 " <td>...</td>\n",
671 " <td>...</td>\n",
672 " <td>...</td>\n",
673 " </tr>\n",
674 " <tr>\n",
675 " <th>75</th>\n",
676 " <td>T0076</td>\n",
677 " <td>2024-04-22</td>\n",
678 " <td>UPS</td>\n",
679 " <td>47.30</td>\n",
680 " <td>Operations</td>\n",
681 " <td>L. Gomez</td>\n",
682 " <td>Credit Card</td>\n",
683 " </tr>\n",
684 " <tr>\n",
685 " <th>76</th>\n",
686 " <td>T0077</td>\n",
687 " <td>2024-04-23</td>\n",
688 " <td>Staples</td>\n",
689 " <td>73.20</td>\n",
690 " <td>Marketing</td>\n",
691 " <td>M. Chen</td>\n",
692 " <td>Credit Card</td>\n",
693 " </tr>\n",
694 " <tr>\n",
695 " <th>77</th>\n",
696 " <td>T0078</td>\n",
697 " <td>2024-04-24</td>\n",
698 " <td>GitHub</td>\n",
699 " <td>420.00</td>\n",
700 " <td>Engineering</td>\n",
701 " <td>S. Patel</td>\n",
702 " <td>ACH</td>\n",
703 " </tr>\n",
704 " <tr>\n",
705 " <th>78</th>\n",
706 " <td>T0079</td>\n",
707 " <td>2024-04-25</td>\n",
708 " <td>Adobe</td>\n",
709 " <td>599.99</td>\n",
710 " <td>Engineering</td>\n",
711 " <td>S. Patel</td>\n",
712 " <td>ACH</td>\n",
713 " </tr>\n",
714 " <tr>\n",
715 " <th>79</th>\n",
716 " <td>T0080</td>\n",
717 " <td>2024-04-26</td>\n",
718 " <td>Delta Airlines</td>\n",
719 " <td>1560.00</td>\n",
720 " <td>Sales</td>\n",
721 " <td>J. Rivera</td>\n",
722 " <td>Credit Card</td>\n",
723 " </tr>\n",
724 " </tbody>\n",
725 "</table>\n",
726 "<p>80 rows × 7 columns</p>\n",
727 "</div>"
728 ],
729 "text/plain": [
730 " Transaction_ID Date Vendor Amount Department \\\n",
731 "0 T0001 2024-01-03 Staples 124.50 Marketing \n",
732 "1 T0002 2024-01-05 AWS 3200.00 Engineering \n",
733 "2 T0003 2024-01-07 Office Depot 87.25 HR \n",
734 "3 T0004 2024-01-08 Delta Airlines 1450.00 Sales \n",
735 "4 T0005 2024-01-10 Adobe 599.99 Marketing \n",
736 ".. ... ... ... ... ... \n",
737 "75 T0076 2024-04-22 UPS 47.30 Operations \n",
738 "76 T0077 2024-04-23 Staples 73.20 Marketing \n",
739 "77 T0078 2024-04-24 GitHub 420.00 Engineering \n",
740 "78 T0079 2024-04-25 Adobe 599.99 Engineering \n",
741 "79 T0080 2024-04-26 Delta Airlines 1560.00 Sales \n",
742 "\n",
743 " Approved_By Payment_Method \n",
744 "0 J. Rivera Credit Card \n",
745 "1 S. Patel ACH \n",
746 "2 M. Chen Credit Card \n",
747 "3 J. Rivera Credit Card \n",
748 "4 M. Chen ACH \n",
749 ".. ... ... \n",
750 "75 L. Gomez Credit Card \n",
751 "76 M. Chen Credit Card \n",
752 "77 S. Patel ACH \n",
753 "78 S. Patel ACH \n",
754 "79 J. Rivera Credit Card \n",
755 "\n",
756 "[80 rows x 7 columns]"
757 ]
758 },
759 "execution_count": 8,
760 "metadata": {},
761 "output_type": "execute_result"
762 }
763 ],
764 "source": [
765 "import random\n",
766 "\n",
767 "random.seed(RANDOM_SEED)\n",
768 "\n",
769 "interval = math.floor(POPULATION_SIZE / SAMPLE_SIZE)\n",
770 "start = random.randint(0, interval - 1) # Random start within the first interval\n",
771 "\n",
772 "systematic_indices = list(range(start, POPULATION_SIZE, interval))[:SAMPLE_SIZE]\n",
773 "systematic_sample = df.iloc[systematic_indices].copy().reset_index(drop=True)\n",
774 "\n",
775 "print(f\"Interval: every {interval} records\")\n",
776 "print(f\"Starting index: {start}\")\n",
777 "print(f\"Systematic sample: {len(systematic_sample)} records\")\n",
778 "systematic_sample"
779 ]
780 },
781 {
782 "cell_type": "markdown",
783 "id": "a1b2c3d4-0001-0001-0001-000000000013",
784 "metadata": {},
785 "source": [
786 "## 6. Method 3 — Stratified Sampling\n",
787 "\n",
788 "Divide the population into subgroups (strata) and sample from each group proportionally. Use this when the population has distinct segments that vary significantly — for example, departments with very different transaction volumes or risk levels.\n",
789 "\n",
790 "Stratified sampling ensures that smaller but important subgroups aren't missed entirely, which can happen with random sampling.\n",
791 "\n",
792 "Here we'll stratify by **Department**."
793 ]
794 },
795 {
796 "cell_type": "code",
797 "execution_count": 9,
798 "id": "a1b2c3d4-0001-0001-0001-000000000014",
799 "metadata": {},
800 "outputs": [
801 {
802 "name": "stdout",
803 "output_type": "stream",
804 "text": [
805 "Stratum breakdown:\n",
806 " Sales 23 records (23%) → 18 samples\n",
807 " Engineering 20 records (20%) → 16 samples\n",
808 " Operations 16 records (16%) → 12 samples\n",
809 " Marketing 15 records (15%) → 12 samples\n",
810 " HR 14 records (14%) → 11 samples\n",
811 " IT 12 records (12%) → 9 samples\n"
812 ]
813 }
814 ],
815 "source": [
816 "STRATIFY_COLUMN = 'Department'\n",
817 "\n",
818 "# Calculate each stratum's proportion of the population\n",
819 "strata_counts = df[STRATIFY_COLUMN].value_counts()\n",
820 "strata_proportions = strata_counts / POPULATION_SIZE\n",
821 "\n",
822 "print(\"Stratum breakdown:\")\n",
823 "for stratum, count in strata_counts.items():\n",
824 " proportion = strata_proportions[stratum]\n",
825 " allocated = math.floor(SAMPLE_SIZE * proportion)\n",
826 " print(f\" {stratum:<15} {count:>3} records ({proportion:.0%}) → {allocated} samples\")"
827 ]
828 },
829 {
830 "cell_type": "code",
831 "execution_count": 10,
832 "id": "a1b2c3d4-0001-0001-0001-000000000015",
833 "metadata": {},
834 "outputs": [
835 {
836 "name": "stdout",
837 "output_type": "stream",
838 "text": [
839 "Stratified sample: 80 records\n",
840 "\n",
841 "Sample breakdown by department:\n",
842 "Department\n",
843 "Sales 18\n",
844 "Engineering 16\n",
845 "Operations 13\n",
846 "Marketing 12\n",
847 "HR 11\n",
848 "IT 10\n",
849 "Name: count, dtype: int64\n"
850 ]
851 }
852 ],
853 "source": [
854 "strata_samples = []\n",
855 "\n",
856 "for stratum, proportion in strata_proportions.items():\n",
857 " n = math.floor(SAMPLE_SIZE * proportion)\n",
858 " if n == 0:\n",
859 " continue\n",
860 " stratum_df = df[df[STRATIFY_COLUMN] == stratum]\n",
861 " if n > len(stratum_df):\n",
862 " raise ValueError(f\"Stratum '{stratum}' has fewer records ({len(stratum_df)}) than required samples ({n})\")\n",
863 " strata_samples.append(stratum_df.sample(n=n, random_state=RANDOM_SEED))\n",
864 "\n",
865 "stratified_sample = pd.concat(strata_samples)\n",
866 "\n",
867 "# Fill any rounding gap by randomly selecting from the remaining records\n",
868 "shortfall = SAMPLE_SIZE - len(stratified_sample)\n",
869 "if shortfall > 0:\n",
870 " already_selected = stratified_sample.index\n",
871 " remaining = df.drop(index=already_selected)\n",
872 " extras = remaining.sample(n=shortfall, random_state=RANDOM_SEED)\n",
873 " stratified_sample = pd.concat([stratified_sample, extras])\n",
874 "\n",
875 "stratified_sample = stratified_sample.sort_values('Transaction_ID').reset_index(drop=True)\n",
876 "\n",
877 "print(f\"Stratified sample: {len(stratified_sample)} records\")\n",
878 "print()\n",
879 "print(\"Sample breakdown by department:\")\n",
880 "print(stratified_sample[STRATIFY_COLUMN].value_counts())"
881 ]
882 },
883 {
884 "cell_type": "markdown",
885 "id": "a1b2c3d4-0001-0001-0001-000000000016",
886 "metadata": {},
887 "source": [
888 "## 7. Comparing the Methods\n",
889 "\n",
890 "It's worth looking at how the three samples differ before deciding which to use — or which to document."
891 ]
892 },
893 {
894 "cell_type": "code",
895 "execution_count": 11,
896 "id": "a1b2c3d4-0001-0001-0001-000000000017",
897 "metadata": {},
898 "outputs": [
899 {
900 "data": {
901 "text/html": [
902 "<div>\n",
903 "<style scoped>\n",
904 " .dataframe tbody tr th:only-of-type {\n",
905 " vertical-align: middle;\n",
906 " }\n",
907 "\n",
908 " .dataframe tbody tr th {\n",
909 " vertical-align: top;\n",
910 " }\n",
911 "\n",
912 " .dataframe thead th {\n",
913 " text-align: right;\n",
914 " }\n",
915 "</style>\n",
916 "<table border=\"1\" class=\"dataframe\">\n",
917 " <thead>\n",
918 " <tr style=\"text-align: right;\">\n",
919 " <th></th>\n",
920 " <th>Method</th>\n",
921 " <th>Sample Size</th>\n",
922 " <th>Avg Amount</th>\n",
923 " <th>Unique Departments</th>\n",
924 " </tr>\n",
925 " </thead>\n",
926 " <tbody>\n",
927 " <tr>\n",
928 " <th>0</th>\n",
929 " <td>Random</td>\n",
930 " <td>80</td>\n",
931 " <td>1258.68</td>\n",
932 " <td>6</td>\n",
933 " </tr>\n",
934 " <tr>\n",
935 " <th>1</th>\n",
936 " <td>Systematic</td>\n",
937 " <td>80</td>\n",
938 " <td>1244.90</td>\n",
939 " <td>6</td>\n",
940 " </tr>\n",
941 " <tr>\n",
942 " <th>2</th>\n",
943 " <td>Stratified</td>\n",
944 " <td>80</td>\n",
945 " <td>1287.08</td>\n",
946 " <td>6</td>\n",
947 " </tr>\n",
948 " </tbody>\n",
949 "</table>\n",
950 "</div>"
951 ],
952 "text/plain": [
953 " Method Sample Size Avg Amount Unique Departments\n",
954 "0 Random 80 1258.68 6\n",
955 "1 Systematic 80 1244.90 6\n",
956 "2 Stratified 80 1287.08 6"
957 ]
958 },
959 "execution_count": 11,
960 "metadata": {},
961 "output_type": "execute_result"
962 }
963 ],
964 "source": [
965 "comparison = pd.DataFrame({\n",
966 " 'Method': ['Random', 'Systematic', 'Stratified'],\n",
967 " 'Sample Size': [len(random_sample), len(systematic_sample), len(stratified_sample)],\n",
968 " 'Avg Amount': [\n",
969 " random_sample['Amount'].mean(),\n",
970 " systematic_sample['Amount'].mean(),\n",
971 " stratified_sample['Amount'].mean()\n",
972 " ],\n",
973 " 'Unique Departments': [\n",
974 " random_sample['Department'].nunique(),\n",
975 " systematic_sample['Department'].nunique(),\n",
976 " stratified_sample['Department'].nunique()\n",
977 " ]\n",
978 "})\n",
979 "\n",
980 "comparison['Avg Amount'] = comparison['Avg Amount'].round(2)\n",
981 "comparison"
982 ]
983 },
984 {
985 "cell_type": "markdown",
986 "id": "a1b2c3d4-0001-0001-0001-000000000018",
987 "metadata": {},
988 "source": [
989 "**Which method to use:**\n",
990 "\n",
991 "- **Random** — default choice; easy to implement and defend. Use when the population is homogeneous.\n",
992 "- **Systematic** — good for ordered populations (e.g., daily transactions); provides even coverage over time.\n",
993 "- **Stratified** — use when subgroups differ significantly in size or risk, and you want guaranteed representation from each group.\n",
994 "\n",
995 "All three methods are defensible to auditors and regulators as long as you document how the sample was selected."
996 ]
997 },
998 {
999 "cell_type": "markdown",
1000 "id": "a1b2c3d4-0001-0001-0001-000000000019",
1001 "metadata": {},
1002 "source": [
1003 "## 8. Export the Sample\n",
1004 "\n",
1005 "Documentation is as important as the sample itself. When you hand off your sample to a reviewer or include it in a workpaper, they need to know:\n",
1006 "- What population was tested\n",
1007 "- What method was used\n",
1008 "- What parameters were applied\n",
1009 "- When the sample was selected\n",
1010 "\n",
1011 "We'll export the sample to Excel with a metadata sheet capturing all of this."
1012 ]
1013 },
1014 {
1015 "cell_type": "code",
1016 "execution_count": 14,
1017 "id": "a1b2c3d4-0001-0001-0001-000000000020",
1018 "metadata": {},
1019 "outputs": [
1020 {
1021 "name": "stdout",
1022 "output_type": "stream",
1023 "text": [
1024 "Exported: Sampling_Workpaper_Stratified.xlsx\n",
1025 " Sheet 'Metadata' — sampling parameters\n",
1026 " Sheet 'Sample' — 80 selected records\n"
1027 ]
1028 }
1029 ],
1030 "source": [
1031 "from datetime import datetime\n",
1032 "\n",
1033 "# Choose which sample to export\n",
1034 "EXPORT_METHOD = 'Stratified' # Change to 'Random' or 'Systematic' as needed\n",
1035 "\n",
1036 "samples_map = {\n",
1037 " 'Random': random_sample,\n",
1038 " 'Systematic': systematic_sample,\n",
1039 " 'Stratified': stratified_sample\n",
1040 "}\n",
1041 "\n",
1042 "export_sample = samples_map[EXPORT_METHOD]\n",
1043 "\n",
1044 "# Build a metadata summary\n",
1045 "metadata = pd.DataFrame([\n",
1046 " {'Parameter': 'Population File', 'Value': 'transactions.csv'},\n",
1047 " {'Parameter': 'Population Size', 'Value': POPULATION_SIZE},\n",
1048 " {'Parameter': 'Sampling Method', 'Value': EXPORT_METHOD},\n",
1049 " {'Parameter': 'Confidence Level', 'Value': f\"{int(CONFIDENCE_LEVEL * 100)}%\"},\n",
1050 " {'Parameter': 'Tolerable Error Rate', 'Value': f\"{int(TOLERABLE_ERROR * 100)}%\"},\n",
1051 " {'Parameter': 'Sample Size', 'Value': len(export_sample)},\n",
1052 " {'Parameter': 'Random Seed', 'Value': RANDOM_SEED},\n",
1053 " {'Parameter': 'Date Generated', 'Value': datetime.today().strftime('%Y-%m-%d')},\n",
1054 "])\n",
1055 "\n",
1056 "output_file = f'Sampling_Workpaper_{EXPORT_METHOD}.xlsx'\n",
1057 "\n",
1058 "with pd.ExcelWriter(output_file, engine='openpyxl') as writer:\n",
1059 " metadata.to_excel(writer, sheet_name='Metadata', index=False)\n",
1060 " export_sample.to_excel(writer, sheet_name='Sample', index=False)\n",
1061 "\n",
1062 "print(f\"Exported: {output_file}\")\n",
1063 "print(f\" Sheet 'Metadata' — sampling parameters\")\n",
1064 "print(f\" Sheet 'Sample' — {len(export_sample)} selected records\")"
1065 ]
1066 }
1067 ],
1068 "metadata": {
1069 "kernelspec": {
1070 "display_name": "Python 3",
1071 "language": "python",
1072 "name": "python3"
1073 },
1074 "language_info": {
1075 "codemirror_mode": {
1076 "name": "ipython",
1077 "version": 3
1078 },
1079 "file_extension": ".py",
1080 "mimetype": "text/x-python",
1081 "name": "python",
1082 "nbconvert_exporter": "python",
1083 "pygments_lexer": "ipython3",
1084 "version": "3.9.6"
1085 }
1086 },
1087 "nbformat": 4,
1088 "nbformat_minor": 5
1089}