Author: Wu Qiong, Lu Jing
In government investment auditing, the review of the bidding process for construction projects is a key focus. The traditional auditing method involves manually comparing unstructured bidding documents to identify clues of collusion and bid-rigging, which not only consumes a lot of manpower and time but also results in subjective comparisons that lack data support.The Audit Bureau of Xiangyang City appliedOCR text recognition technology to convert unstructured bidding documents into structured text data, and usedPython for text analysis and comparison, accurately locating suspicious clues and efficiently solving auditing challenges..
1. Text Data Conversion
Based on the project characteristics, the target text was determined. After preliminary review, it was found that the comprehensive quotes in a certain government investment audit bidding document did not show regular differences, indicating potential collusion or bid-rigging issues. To pinpoint suspicious clues, auditors selected the project implementation plan from the bidding documents as the target text, narrowing the analysis scope to check for similarities in the bidding documents.First, the documents were scanned, image preprocessing and necessary corrections were performed, and then theOCR text recognition system was used to extract text data, converting images into text files in bulk. Finally, data cleaning was conducted to verify the logicality and accuracy of the text data, organizing it into standardized electronic documents.
2. Text Data Segmentation
The review of the bidding process for a certain government investment audit project required analyzing12 bidding documents containing project implementation plans, necessitating66 pairwise comparisons, with each implementation plan exceeding10,000 words. The large volume of text data made manual comparisons labor-intensive, difficult, subjective, and inefficient. To address this issue, this audit considered using Python for batch automated text comparison.
First, the text data was segmented and counted. Using thedocx module, text data from the documents was obtained. Thejieba module was used to conveniently segment the text, breaking the document content into several phrases and terms. It is generally believed that single-character words (such as function words “的”, “了”) have no comparison significance, so we only counted the occurrences of all phrases longer than1 character. The main code is shown in the figure below:
3. Text Data Analysis
To examine whether there are similarities in the bidding documents from different bidding units, the similarity ratio between the text data must be calculated.The similarity ratio is calculated as follows:
Assuming the documents being compared areA andB, if a certain phrase has a length of2, appearing3 times in documentA and5 times in documentB, then the number of repeated characters caused by this phrase between documentsA andB is the phrase length multiplied by the minimum occurrence count in both documents, i.e., the repeated character count is (2×3=6 characters. By traversing all phrases that appeared in documentsA andB, the total repeated character count can be obtained by summing all the repeated character counts. Using the total repeated character count as the numerator and the minimum total character count of documentsA andB as the denominator, the final similarity percentage can be derived.
By using theos module to obtain12 document filenames, batch reading and looping comparisons of all documents can be achieved. The main implementation code is shown in the figure below:
4. Application Effects
In this government investment audit project, auditors usedPython data analysis methods to calculate the similarity ratios of the project implementation plans in the bidding documents. The results showed that16 groups had similarity ratios exceeding50%, with the highest reaching65.82%. After manual verification of these16 groups of documents, it was found that they all had issues of similarity in the bidding documents, and the algorithm’s problem localization accuracy reached100%.
This attempt to applyPython data analysis methods in the audit project is an exploration and practice of conducting research-based audits, enriching the auditing toolset, reducing labor costs, improving audit quality and efficiency, and providing reference significance for similar future work.
UsingPython to Reveal Similarity Issues in Bidding Texts
To determine whether there are collusion or manipulation behaviors among bidders in different sections of the same project, auditors need to conduct similarity reviews of the bidding technical documents. In the past, auditors often manually compared unstructured bidding documents, consuming a lot of time and manpower. In a certain government investment project audit, auditors usedPython text data analysis methods to compare bidding technical documents, pinpointing problem clues and achieving good results.
This project is a municipal road engineering project, implemented in five separate sections, with each section having similar implementation content. The participating units for each section were determined through public bidding, includingEPC general contracting, supervision, third-party testing, and full-process cost consulting. Auditors needed to analyze the text data of the bidding documents from the same type of participating units across the five sections to check for consistent or partially similar content.
The basic idea for establishing a data analysis of bidding technical documents is: to usePython‘sdocx andos modules to organize the sorted bidding technical documents into a standard format, and then use thejieba module to segment the document content. For example, the text “Equipped with fire extinguishing equipment to prevent fire hazards” can be segmented into “Equipped with”, “fire extinguishing”, “equipment”, “to prevent”, “fire hazards”, etc. The segmented words and their frequencies are written into a data dictionary to generate a duplicate comparison database.
In this project, auditors established a data analysis database for the EPC general contracting bidding technical documents using this method. First, they called thedocx module to read the locally stored bidding technical documents, then looped through them and called thejieba module for segmentation. It is generally believed that single-character words (such as function words “的”, “了”) have no comparison significance, so auditors only counted the frequencies of all words longer than1 character. The segmented words and their frequencies were then written into a data dictionary (named “cuts”, with keys as segmented words and values as their frequencies), forming a data analysis database.
Next, code was written to calculate the duplication rate, implemented as follows: when comparingA andB documents, auditors first usedfor loops to traverse the keys (segmented words) in the data dictionarycuts of documentB. If a segmented word in documentB appears in documentA, the repeated segmented word is written into a new data dictionary (named “jg”, with the same key-value type ascuts). Then, usingfor loops to traverse the key-value pairs in the data dictionaryjg, the total repeated character count is calculated by multiplying the length of the segmented words by their repetition frequency. Similarly, usingfor loops, the total character counts of documentsA andB are obtained, and the duplication rate is derived by dividing the total repeated character count by the minimum total character count of documentsA andB. Finally, nestedfor loops are used to calculate the duplication rate for all bidding technical documents, outputting the data analysis results.
By analyzing the text data of74 bidding technical documents, auditors found that the project management organization plan of one bidding unit had a duplication rate of73.04%, and other bidding units also had issues with partial content similarity, confirming the algorithm’s effectiveness in accurately identifying issues.
Date:2023-05-04 14:35 ReprintedSource: Xiangyang City Audit BureauAuthor: Lu Jing
This public account indicates that the marked parts or content are reprinted from the internet, and the copyright belongs to the original author, for learning and reference purposes only, and is prohibited for commercial use. If any media, company, enterprise, or individual’s intellectual property rights are inadvertently infringed, please contact for deletion, and this public platform will not bear any responsibility.