cmc/cleberg.net

  1#+date:        [2021-08-25 Wed 00:00:00]
  2#+title:       Audit Sampling with Pandas
  3#+description: Using Python and Pandas to run random and stratified audit samples.
  4#+slug:        audit-sampling
  5#+filetags:    :audit:
  6
  7* Introduction
  8
  9For anyone who is familiar with internal auditing, external auditing, or
 10consulting, you will understand how tedious audit testing can become when you
 11are required to test large swaths of data. When we cannot establish an automated
 12means of testing an entire population, we generate samples to represent the
 13population of data. This helps ensure we can have a small enough data pool to
 14test and that our results still represent the population.
 15
 16However, sampling data within the world of audit still seems to confuse quite a
 17lot of people. While some audit-focused tools have introduced sampling
 18functionality (e.g. Wdesk), many audit departments and firms cannot use software
 19like this due to certain constraints, such as the team's budget or knowledge.
 20Here is where this article comes in: we're going to use [[https://www.python.org][Python]], a free and
 21open-source programming language, to generate random samples from a dataset in
 22order to suffice numerous audit situations.
 23
 24* Audit Requirements for Sampling
 25
 26Before we get into the details of how to sample with Python, I want to make sure
 27I discuss the different requirements that auditors may have of samples used
 28within their projects.
 29
 30** Randomness
 31
 32First, let's discuss randomness. When testing out new technology to help assist
 33with audit sampling, you need to understand exactly how your samples are being
 34generated. For example, if the underlying function is just picking every 57th
 35element from a list, that's not truly random; it's a systematic form of
 36sampling. Luckily, since Python is open-source, we have access to its codebase.
 37Through this blog post, I will be using the [[https://pandas.pydata.org][pandas]] module in order to generate
 38the random samples. More specifically, I will be using the
 39[[https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.sample.html][pandas.DataFrame.sample]] function provided by Pandas.
 40
 41Now that you know what you're using, you can always check out the code behind
 42=pandas.DataFrame.sample=. This function does a lot of work, but we really only
 43care about the following snippets of code:
 44
 45#+begin_src python
 46# Process random_state argument
 47rs = com.random_state(random_state)
 48
 49...
 50
 51locs = rs.choice(axis_length, size=n, replace=replace, p=weights)
 52result = self.take(locs, axis=axis)
 53if ignore_index:
 54result.index = ibase.default_index(len(result))
 55
 56return result
 57#+end_src
 58
 59The block of code above shows you that if you assign a =random_state= argument
 60when you run the function, that will be used as a seed number in the random
 61generation and will allow you to reproduce a sample, given that nothing else
 62changes. This is critical to the posterity of audit work. After all, how can you
 63say your audit process is adequately documented if the next person can't run the
 64code and get the same sample? The final piece here on randomness is to look at
 65the [[https://docs.python.org/3/library/random.html#random.choice][choice]] function used above. This is the crux of the generation and can also
 66be examined for more detailed analysis on its reliability. As far as auditing
 67goes, we will trust that these functions are mathematically random.
 68
 69** Sample Sizes
 70
 71As mentioned in the intro, sampling is only an effective method of auditing when
 72it truly represents the entire population. While some audit departments or firms
 73may consider certain judgmental sample sizes to be adequate, you may need to
 74rely on statistically-significant confidence levels of sample testing at certain
 75
 76points. I will demonstrate both here. For statistically-significant confidence
 77levels, most people will assume a 90% - 99% confidence level. In order to
 78actually calculate the correct sample size, it is best to use statistical tools
 79due to the tedious math work required. For example, for a population of 1000,
 80and a 90% confidence level that no more than 5% of the items are nonconforming,
 81you would sample 45 items.
 82
 83However, in my personal experience, many audit departments and firms do not use
 84statistical sampling. Most people use a predetermined, often proprietary, table
 85that will instruct auditors which sample sizes to choose. This allows for
 86uniform testing and reduces overall workload. See the table below for a common
 87implementation of sample sizes:
 88
 89| Control Frequency | Sample Size (High Risk) | Sample Size (Low Risk) |
 90|-------------------+-------------------------+------------------------|
 91| More Than Daily   |                      40 |                     25 |
 92| Daily             |                      40 |                     25 |
 93| Weekly            |                      12 |                      5 |
 94| Monthly           |                       5 |                      3 |
 95| Quarterly         |                       2 |                      2 |
 96| Semi-Annually     |                       1 |                      1 |
 97| Annually          |                       1 |                      1 |
 98| Ad-hoc            |                       1 |                      1 |
 99
100*** Sampling with Python & Pandas
101
102In this section, I am going to cover a few basic audit situations that require
103sampling. While some situations may require more effort, the syntax,
104organization, and intellect used remain largely the same. If you've never used
105Python before, note that lines starting with a '=#=' symbol are called comments,
106and they will be skipped by Python. I highly recommend taking a quick tutorial
107online to understand the basics of Python if any of the code below is confusing
108to you.
109
110** Simple Random Sample
111
112First, let's look at a simple, random sample. The code block below will import
113the =pandas= module, load a data file, sample the data, and export the sample to
114a file.
115
116#+begin_src python
117# Import the Pandas module
118import pandas
119
120# Specify where to find the input file & where to save the final sample
121file_input = r'Population Data.xlsx'
122file_output = r'Sample.xlsx'
123
124# Load the data with pandas
125# Remember to use the sheet_name parameter if your Excel file has multiple sheets
126df = pandas.read_excel(file_input)
127
128# Sample the data for 25 selections
129# Remember to always use the random_state parameter so the sample can be re-performed
130sample = df.sample(n=25, random_state=0)
131
132# Save the sample to Excel
133sample.to_excel(file_output)
134#+end_src
135
136** Simple Random Sample: Using Multiple Input Files
137
138Now that we've created a simple sample, let's create a sample from multiple
139files.
140
141#+begin_src python
142# Import the Pandas module
143import pandas
144
145# Specify where to find the input file & where to save the final sample
146file_input_01 = r'Population Data Q1.xlsx'
147file_input_02 = r'Population Data Q2.xlsx'
148file_input_03 = r'Population Data Q3.xlsx'
149file_output = r'Sample.xlsx'
150
151# Load the data with pandas
152# Remember to use the sheet_name parameter if your Excel file has multiple sheets
153df_01 = pandas.read_excel(file_input_01)
154df_02 = pandas.read_excel(file_input_02)
155df_03 = pandas.read_excel(file_input_03)
156
157# Sample the data for 5 selections from each quarter
158# Remember to always use the random_state parameter so the sample can be re-performed
159sample_01 = df_01.sample(n=5, random_state=0)
160sample_02 = df_02.sample(n=5, random_state=0)
161sample_03 = df_03.sample(n=5, random_state=0)
162
163# If required, combine the samples back together
164sample = pandas.concat([sample_01, sample_02, sample_03], ignore_index=True)
165
166# Save the sample to Excel
167sample.to_excel(file_output)
168#+end_src
169
170** Stratified Random Sample
171
172Well, what if you need to sample distinct parts of a single file? For example,
173let's write some code to separate our data by "Region" and sample those regions
174independently.
175
176#+begin_src python
177# Import the Pandas module
178import pandas
179
180# Specify where to find the input file & where to save the final sample
181file_input = r'Sales Data.xlsx'
182file_output = r'Sample.xlsx'
183
184# Load the data with pandas
185# Remember to use the sheet_name parameter if your Excel file has multiple sheets
186df = pandas.read_excel(file_input)
187
188# Stratify the data by "Region"
189df_east = df[df['Region'] == 'East']
190df_west = df[df['Region'] == 'West']
191
192# Sample the data for 5 selections from each quarter
193# Remember to always use the random_state parameter so the sample can be re-performed
194sample_east = df_east.sample(n=5, random_state=0)
195sample_west = df_west.sample(n=5, random_state=0)
196
197# If required, combine the samples back together
198sample = pandas.concat([sample_east, sample_west], ignore_index=True)
199
200# Save the sample to Excel
201sample.to_excel(file_output)
202#+end_src
203
204** Stratified Systematic Sample
205
206This next example is quite useful if you need audit coverage over a certain time
207period. This code will generate samples for each month in the data and combine
208them all together at the end. Obviously, this code can be modified to stratify
209by something other than months, if needed.
210
211#+begin_src python
212# Import the Pandas module
213import pandas
214
215# Specify where to find the input file & where to save the final sample
216file_input = r'Sales Data.xlsx'
217file_output = r'Sample.xlsx'
218
219# Load the data with pandas
220# Remember to use the sheet_name parameter if your Excel file has multiple sheets
221df = pandas.read_excel(file_input)
222
223# Convert the date column to datetime so the function below will work
224df['Date of Sale'] = pandas.to_datetime(df['Date of Sale'])
225
226# Define a function to create a sample for each month
227def monthly_stratified_sample(df: pandas.DataFrame, date_column: str, num_selections: int) -> pandas.DataFrame:
228    static_num_selections = num_selections final_sample = pandas.DataFrame()
229    for month in range(1, 13):
230        num_selections = static_num_selections
231        rows_list = []
232        for index, row in df.iterrows():
233            df_month = row[date_column].month
234            if month == df_month:
235                rows_list.append()
236        monthly_df = pd.DataFrame(data=rows_list)
237        if (len(monthly_df)) == 0:
238            continue
239        elif not (len(monthly_df) > sample_size):
240            num_selections = sample_size
241        elif len(monthly_df) >= sample_size:
242            num_selections = sample_size
243        sample = monthly_df.sample(n=num_selections, random_state=0)
244        final_sample = final_sample.append(sample)
245    return sample
246
247# Sample for 3 selections per month
248sample_size = 3
249sample = monthly_stratified_sample(df, 'Date of Sale', sample_size)
250sample.to_excel(file_output)
251#+end_src
252
253*** Documenting the Results
254
255Once you've generated a proper sample, there are a few things left to do in
256order to properly ensure your process is reproducible.
257
2581. Document the sample. Make sure the resulting file is readable and includes
259   the documentation listed in the next bullet.
2602. Include documentation around the data source, extraction techniques, any
261   modifications made to the data, and be sure to include a copy of the script
262   itself.
2633. Whenever possible, perform a completeness and accuracy test to ensure your
264   sample is coming from a complete and accurate population. To ensure
265   completeness, compare the record count from the data source to the record
266   count loaded into Python. To ensure accuracy, test a small sample against the
267   source data (e.g., test 5 sales against the database to see if the details
268   are accurate).