content/blog/2021-08-25-audit-sampling.org

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