feat: total re-write from Emacs org-mode to Zola markdown

1 files changed, 277 insertions, 0 deletions

diff --git a/content/blog/2021-08-25-audit-sampling.md b/content/blog/2021-08-25-audit-sampling.md
new file mode 100644
index 0000000..f31f276
--- /dev/null
+++ b/content/blog/2021-08-25-audit-sampling.md
@@ -0,0 +1,277 @@
++++
+date = 2021-08-25
+title = "Audit Sampling with Python"
+description = "Learn how to sample populations with Python."
++++
+
+## Introduction
+
+For anyone who is familiar with internal auditing, external auditing, or
+consulting, you will understand how tedious audit testing can become
+when you are required to test large swaths of data. When we cannot
+establish an automated means of testing an entire population, we
+generate samples to represent the population of data. This helps ensure
+we can have a small enough data pool to test and that our results still
+represent the population.
+
+However, sampling data within the world of audit still seems to confuse
+quite a lot of people. While some audit-focused tools have introduced
+sampling functionality (e.g. Wdesk), many audit departments and firms
+cannot use software like this due to certain constraints, such as the
+team's budget or knowledge. Here is where this article comes in: we're
+going to use [Python](https://www.python.org), a free and open-source
+programming language, to generate random samples from a dataset in order
+to suffice numerous audit situations.
+
+## Audit Requirements for Sampling
+
+Before we get into the details of how to sample with Python, I want to
+make sure I discuss the different requirements that auditors may have of
+samples used within their projects.
+
+### Randomness
+
+First, let's discuss randomness. When testing out new technology to
+help assist with audit sampling, you need to understand exactly how your
+samples are being generated. For example, if the underlying function is
+just picking every 57th element from a list, that's not truly random;
+it's a systematic form of sampling. Luckily, since Python is
+open-source, we have access to its codebase. Through this blog post, I
+will be using the [pandas](https://pandas.pydata.org) module in order to
+generate the random samples. More specifically, I will be using the
+[pandas.DataFrame.sample](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.sample.html)
+function provided by Pandas.
+
+Now that you know what you're using, you can always check out the code
+behind `pandas.DataFrame.sample`. This function does a lot of
+work, but we really only care about the following snippets of code:
+
+```python
+# Process random_state argument
+rs = com.random_state(random_state)
+
+...
+
+locs = rs.choice(axis_length, size=n, replace=replace, p=weights)
+result = self.take(locs, axis=axis)
+if ignore_index:
+result.index = ibase.default_index(len(result))
+
+return result
+```
+
+The block of code above shows you that if you assign a
+`random_state` argument when you run the function, that will
+be used as a seed number in the random generation and will allow you to
+reproduce a sample, given that nothing else changes. This is critical to
+the posterity of audit work. After all, how can you say your audit
+process is adequately documented if the next person can't run the code
+and get the same sample? The final piece here on randomness is to look
+at the
+[choice](https://docs.%20python.org/3/library/random.html#random.choice)
+function used above. This is the crux of the generation and can also be
+examined for more detailed analysis on its reliability. As far as
+auditing goes, we will trust that these functions are mathematically
+random.
+
+### Sample Sizes
+
+As mentioned in the intro, sampling is only an effective method of
+auditing when it truly represents the entire population. While some
+audit departments or firms may consider certain judgmental sample sizes
+to be adequate, you may need to rely on statistically-significant
+confidence levels of sample testing at certain points. I will
+demonstrate both here. For statistically-significant confidence levels,
+most people will assume a 90% - 99% confidence level. In order to
+actually calculate the correct sample size, it is best to use
+statistical tools due to the tedious math work required. For example,
+for a population of 1000, and a 90% confidence level that no more than
+5% of the items are nonconforming, you would sample 45 items.
+
+However, in my personal experience, many audit departments and firms do
+not use statistical sampling. Most people use a predetermined, often
+proprietary, table that will instruct auditors which sample sizes to
+choose. This allows for uniform testing and reduces overall workload.
+See the table below for a common implementation of sample sizes:
+
+  Control Frequency   Sample Size - High Risk   Sample Size - Low Risk
+  ------------------- ------------------------- ------------------------
+  More Than Daily     40                        25
+  Daily               40                        25
+  Weekly              12                        5
+  Monthly             5                         3
+  Quarterly           2                         2
+  Semi-Annually       1                         1
+  Annually            1                         1
+  Ad-hoc              1                         1
+
+## Sampling with Python & Pandas
+
+In this section, I am going to cover a few basic audit situations that
+require sampling. While some situations may require more effort, the
+syntax, organization, and intellect used remain largely the same. If
+you've never used Python before, note that lines starting with a
+'`#`' symbol are called comments, and they will be skipped
+by Python. I highly recommend taking a quick tutorial online to
+understand the basics of Python if any of the code below is confusing to
+you.
+
+### Simple Random Sample
+
+First, let's look at a simple, random sample. The code block below will
+import the `pandas` module, load a data file, sample the
+data, and export the sample to a file.
+
+```python
+# Import the Pandas module
+import pandas
+
+# Specify where to find the input file & where to save the final sample
+file_input = r'Population Data.xlsx'
+file_output = r'Sample.xlsx'
+
+# Load the data with pandas
+# Remember to use the sheet_name parameter if your Excel file has multiple sheets
+df = pandas.read_excel(file_input)
+
+# Sample the data for 25 selections
+# Remember to always use the random_state parameter so the sample can be re-performed
+sample = df.sample(n=25, random_state=0)
+
+# Save the sample to Excel
+sample.to_excel(file_output)
+```
+
+### Simple Random Sample: Using Multiple Input Files
+
+Now that we've created a simple sample, let's create a sample from
+multiple files.
+
+```python
+# Import the Pandas module
+import pandas
+
+# Specify where to find the input file & where to save the final sample
+file_input_01 = r'Population Data Q1.xlsx'
+file_input_02 = r'Population Data Q2.xlsx'
+file_input_03 = r'Population Data Q3.xlsx'
+file_output = r'Sample.xlsx'
+
+# Load the data with pandas
+# Remember to use the sheet_name parameter if your Excel file has multiple sheets
+df_01 = pandas.read_excel(file_input_01)
+df_02 = pandas.read_excel(file_input_02)
+df_03 = pandas.read_excel(file_input_03)
+
+# Sample the data for 5 selections from each quarter
+# Remember to always use the random_state parameter so the sample can be re-performed
+sample_01 = df_01.sample(n=5, random_state=0)
+sample_02 = df_02.sample(n=5, random_state=0)
+sample_03 = df_03.sample(n=5, random_state=0)
+
+# If required, combine the samples back together
+sample = pandas.concat([sample_01, sample_02, sample_03], ignore_index=True)
+
+# Save the sample to Excel
+sample.to_excel(file_output)
+```
+
+### Stratified Random Sample
+
+Well, what if you need to sample distinct parts of a single file? For
+example, let's write some code to separate our data by "Region" and
+sample those regions independently.
+
+```python
+# Import the Pandas module
+import pandas
+
+# Specify where to find the input file & where to save the final sample
+file_input = r'Sales Data.xlsx'
+file_output = r'Sample.xlsx'
+
+# Load the data with pandas
+# Remember to use the sheet_name parameter if your Excel file has multiple sheets
+df = pandas.read_excel(file_input)
+
+# Stratify the data by "Region"
+df_east = df[df['Region'] == 'East']
+df_west = df[df['Region'] == 'West']
+
+# Sample the data for 5 selections from each quarter
+# Remember to always use the random_state parameter so the sample can be re-performed
+sample_east = df_east.sample(n=5, random_state=0)
+sample_west = df_west.sample(n=5, random_state=0)
+
+# If required, combine the samples back together
+sample = pandas.concat([sample_east, sample_west], ignore_index=True)
+
+# Save the sample to Excel
+sample.to_excel(file_output)
+```
+
+### Stratified Systematic Sample
+
+This next example is quite useful if you need audit coverage over a
+certain time period. This code will generate samples for each month in
+the data and combine them all together at the end. Obviously, this code
+can be modified to stratify by something other than months, if needed.
+
+```python
+# Import the Pandas module
+import pandas
+
+# Specify where to find the input file & where to save the final sample
+file_input = r'Sales Data.xlsx'
+file_output = r'Sample.xlsx'
+
+# Load the data with pandas
+# Remember to use the sheet_name parameter if your Excel file has multiple sheets
+df = pandas.read_excel(file_input)
+
+# Convert the date column to datetime so the function below will work
+df['Date of Sale'] = pandas.to_datetime(df['Date of Sale'])
+
+# Define a function to create a sample for each month
+def monthly_stratified_sample(df: pandas.DataFrame, date_column: str, num_selections: int) -> pandas.DataFrame:
+    static_num_selections = num_selections final_sample = pandas.DataFrame()
+    for month in range(1, 13):
+        num_selections = static_num_selections
+        rows_list = []
+        for index, row in df.iterrows():
+            df_month = row[date_column].month
+            if month == df_month:
+                rows_list.append()
+        monthly_df = pd.DataFrame(data=rows_list)
+        if (len(monthly_df)) == 0:
+            continue
+        elif not (len(monthly_df) > sample_size):
+            num_selections = sample_size
+        elif len(monthly_df) >= sample_size:
+            num_selections = sample_size
+        sample = monthly_df.sample(n=num_selections, random_state=0)
+        final_sample = final_sample.append(sample)
+    return sample
+
+# Sample for 3 selections per month
+sample_size = 3
+sample = monthly_stratified_sample(df, 'Date of Sale', sample_size)
+sample.to_excel(file_output)
+```
+
+## Documenting the Results
+
+Once you've generated a proper sample, there are a few things left to
+do in order to properly ensure your process is reproducible.
+
+1.  Document the sample. Make sure the resulting file is readable and
+    includes the documentation listed in the next bullet.
+2.  Include documentation around the data source, extraction techniques,
+    any modifications made to the data, and be sure to include a copy of
+    the script itself.
+3.  Whenever possible, perform a completeness and accuracy test to
+    ensure your sample is coming from a complete and accurate
+    population. To ensure completeness, compare the record count from
+    the data source to the record count loaded into Python. To ensure
+    accuracy, test a small sample against the source data (e.g., test 5
+    sales against the database to see if the details are accurate).