Python-based data cleaning, grouping and articulation method

Abstract

The invention relates to the technical field of data processing, and discloses a Python-based data cleaning, grouping and articulation method, which comprises the following steps: S1, acquiring a general folder needing data arrangement, and storing at least one format of original data files about production and operation monthly reports of a plurality of counties belonging to a certain city in a certain month in the general folder, one of the formats is set; s2, converting an original data file in a non-set format in the total folder into a data file in a set format, then taking the data file which is newly converted into the set format and the original data file which is originally in the set format as first data files, and respectively storing the first data files in the same newly established first folder, the first folder takes the city name of the S1 as the folder name, and the name of the superior folder of the first folder is the month in the S1; s3, reading the specified first data file, obtaining table data in the first data file according to a set condition combination, and preprocessing the table data to obtain a standard table about various production and operation monthly reports, the production and operation data related to the production and operation monthly reports of the same type of each power supply company are summarized and stored in a specification table of the same type, the specification tables are stored in a second folder in the form of data tables, and one specification table corresponds to one data table; and S4, acquiring and preprocessing external data, and performing articulation based on an external characteristic value of the external data and a data characteristic value of a standard table corresponding to the production and operation monthly report, so that the external data and table data in the standard table are associated in the same table. According to the method, the efficiency and accuracy of processing monthly reports produced by the power supply company and related data can be remarkably improved, human errors and labor intensity are reduced through the automatic data extraction, cleaning, integration and articulation processes, and meanwhile the data processing speed is increased.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a data cleaning grouping and cross-checking method based on Python. Background Art

[0002] In order to deeply analyze the economic vitality and characteristic status of regional power grid companies, improve the quality and efficiency of power grid production and operation, discover production and operation problems, evaluate the current status of regional economic development, judge future development trends, and provide decision support for optimizing resource allocation to promote balanced and healthy regional development, data analysis based on the production and operation reports of power supply companies is necessary. At present, power supply companies rely on monthly production reports and related data to conduct production and operation analysis. These data are a collection of internal operation data and external environmental data (such as regional characteristics, economic development status, etc.). However, the diversity of data sources and the inconsistency of data formats have brought challenges to data integration and analysis. Existing data processing methods are usually manual extraction, integration and association, but due to the existence of inconsistent data formats (such as data distributed in various files such as doc, pdf, wps and rar), data quality problems (data may exist in the form of pictures, and the table form is not uniform, there are repeated headers, etc.), large workload of data integration (multiple tables need to be integrated into a unified format) and complex data cross-checking (the corresponding relationship between data such as industrial electricity consumption and industrial gross domestic product in counties is complex, and manual cross-checking is cumbersome and time-consuming). This method is not only inefficient but also prone to errors. Summary of the invention

[0003] Based on this, the present invention provides a data cleaning grouping and cross-checking method based on Python. By studying the existence forms of various types of data in files and analyzing the correlation between internal and external data, a data cleaning grouping and cross-checking method is formed that integrates multiple functions such as file format conversion, data extraction, data cleaning, and internal and external data cross-checking. It can complete the cleaning and cross-checking of internal and external data and provide support for subsequent data analysis.

[0004] The present invention is achieved through the following technical solutions:

[0005] A data cleaning grouping and cross-checking method based on Python, comprising:

[0006] S1. Obtain a general folder that needs to be sorted, wherein the general folder stores original data files of production and operation monthly reports of multiple districts and counties belonging to a certain city in at least one format, and one of the formats is set as a set format;

[0007] S2, converting the original data files in the general folder that are not in the set format into data files in the set format, and then storing the newly converted data files in the set format and the original data files that are originally in the set format as first data files in the same newly created first folder, wherein the first folder uses the city name of S1 as the folder name, and the name of the parent folder of the first folder is the month in S1;

[0008] S3, reading the specified first data file, obtaining the table data in the first data file according to the set condition combination, preprocessing the table data to obtain the standard table for various types of production and operation monthly reports, and summarizing and storing the relevant production and operation data of the same type of production and operation monthly reports of each power supply company in the same type of standard table, and storing the standard table in the form of a data table in the second folder, wherein one standard table corresponds to one data table;

[0009] S4. Obtain and preprocess external data, and then cross-check the external characteristic values ​​of the external data with the data characteristic values ​​of the standard form corresponding to the production and operation monthly report, so that the external data and the table data in the standard form are associated in the same form.

[0010] As an optimization, the formats of the original data file include doc, docx, pdf, wps and rar.

[0011] As an optimization, the set format is docx.

[0012] As an optimization, the specific steps of S2 are:

[0013] S2.1. Select the data files in the total folder according to the arrangement order of the files;

[0014] S2.2, determine whether the format of the data file is rar, if not rar, jump to S2.3, otherwise, jump to S2.13;

[0015] S2.3, determine whether the data file is a folder, if so, enter the folder, select the data file in the folder according to the arrangement order of the files, and jump to S2.2, otherwise, jump to S2.4;

[0016] S2.4, determining whether the format of the data file is a set format, if so, storing the data file in the first folder, and then jumping to S2.5, otherwise, converting the format of the data file into the set format according to the set conditions, and then storing the data file in the first folder and jumping to S2.5;

[0017] S2.5, determine whether the data file is the last one in the folder where the data file is located, if so, jump to S2.6, otherwise, jump to S2.12;

[0018] S2.6, determine whether the folder where the data file is located is a general folder, if not, jump to S2.7, otherwise, exit;

[0019] S2.7, determine whether the folder where the data file is located is the last one in the parent folder of the folder, if so, jump to S2.8, otherwise, jump to S2.11;

[0020] S2.8, determine whether the parent folder is a master folder, if so, exit, otherwise jump to S2.9;

[0021] S2.9, taking the parent folder as the original folder, obtaining the parent folder of the original folder, and determining whether the original folder is the last one of the parent folders. If so, return to S2.8; otherwise, jump to S2.10;

[0022] S2.10, determine the next data file in the parent folder and jump to S2.2;

[0023] S2.11, select the next data file in the folder where the data file in S2.7 is located in the parent folder, and jump to S2.2;

[0024] S2.12, select the next data file of the data file and jump to S2.2;

[0025] S2.13, decompress the file in rar format into a new folder and delete the original rar file;

[0026] S2.14. Determine the format of the data files in the new folder according to the arrangement order of the files. If the format is not rar, jump to S2.3; otherwise, jump to S2.13.

[0027] As an optimization, in S2.4, the specific process of converting a data file in a non-set format into a data file in a set format is as follows:

[0028] S2.4.1. Obtain the file name and extension of the data file and convert the extension into lower case;

[0029] S2.4.2. Determine the format of the data file:

[0030] If the extension is .wps, convert the wps file to docx format using the WPS API;

[0031] If the file extension is .doc, use pywin32 to convert the doc file to docx format;

[0032] If the file extension is .pdf, then use pdf2docx to convert the pdf file to docx format.

[0033] As an optimization, the specific steps of S3 are:

[0034] S3.1. Design the structure of the standard form according to the type of monthly production and operation report;

[0035] S3.2, sequentially read the text content of the first data file in the first folder, the text content including a table name, a table number and a table, and the table contains data;

[0036] S3.3, extracting the corresponding table name from the text content according to the table number;

[0037] S3.4. Find the first data file corresponding to the districts and counties under the city in the corresponding month according to the condition combination of month + city name, and open it, wherein the first data file corresponding to all districts and counties under the city from January to December is opened;

[0038] S3.5. Find the table corresponding to the same table name in the first data file in S3.4 according to the table name of the standard table, then traverse the rows and columns of the table, and extract the data in the table to the corresponding rows and columns in the standard table according to the correspondence between the row names and the column names to form internal data;

[0039] S3.6. Clean and process the data in the standard form, and finally store the standard form in the form of a data table in the second folder.

[0040] As an optimization, the standard forms corresponding to the types of production and operation monthly reports include: industry electricity consumption classification table, electricity sales details table, line loss rate statistics table, 10kV heavy loss & negative loss line details, 10kV heavy loss & negative loss substation details, 10kV heavy load line details and 10kV heavy load substation details.

[0041] As an optimization, the specific process of obtaining and preprocessing external data in S4 is as follows:

[0042] S4.1. Obtain a table of different types of external data, and all types of external data include year, city name, district and county name, and corresponding data values;

[0043] S4.2. Delete the rows with the same year and district and county name in the same type of external data.

[0044] As an optimization, in S4, the specific process of cross-checking the external characteristic value of the external data with the characteristic value of the table data in the data table is:

[0045] S4.3. Select corresponding external data and data tables according to the correlation required;

[0046] S4.4. Perform fuzzy matching and cross-reference based on the year, city name, district and county name of the external data and the year, city name, district and county name in the data table, and store the data values ​​in the external data and the table data in the data table belonging to the same year, city name, district and county name in the same table.

[0047] As an optimization, the types of external data include district and county basic characteristic data, industrial structure data, and economic structure data.

[0048] Compared with the prior art, the present invention has the following advantages and beneficial effects:

[0049] The present invention can significantly improve the efficiency and accuracy of power supply companies' production of monthly reports and related data processing. The automated data extraction, cleaning, integration and cross-checking processes reduce human errors and labor intensity, while increasing the speed of data processing. Utilizing Python's powerful data processing capabilities, data from different sources can be quickly integrated and cleaned to improve data consistency and integrity. Ultimately, the data that has been grouped and cross-checked can provide power supply companies with more reliable and accurate data analysis results, supporting better decision-making. In this way, power supply companies can more effectively manage and analyze key operational data, thereby optimizing resource allocation, improving service quality, and ultimately improving customer satisfaction and corporate competitiveness. The present invention is based on data extraction, cleaning, cross-checking and other codes written in Python, and can also be extended to other fields. BRIEF DESCRIPTION OF THE DRAWINGS

[0050] The drawings described herein are used to provide a further understanding of the embodiments of the present invention, constitute a part of this application, and do not constitute a limitation of the embodiments of the present invention. In the drawings:

[0051] Figure 1 is a flow chart of the present invention;

[0052] Figure 2 This is an example of the different formats of the actual data files collected;

[0053] Figure 3 Screenshot of the code for batch conversion of formats;

[0054] Figure 4 is a schematic diagram of a first data file after data file conversion;

[0055] Figure 5 This is a schematic diagram of the detailed list of electricity sales collected;

[0056] Figure 6 A schematic diagram of the standard form corresponding to the electricity sales detailed table;

[0057] Figure 7 Take a screenshot of some of the data extraction and cleaning codes;

[0058] Figure 8 This is a schematic diagram of the summary results of the detailed data of electricity sales of each power supply company;

[0059] Fig. 9 Schematic diagram of 7 data tables formed by extracting 7 types of data from the production and operation monthly report;

[0060] Fig.10 This is an example diagram of district and county characteristic data;

[0061] Fig.11 This is a schematic diagram of the cross-check results of district and county electricity consumption and industrial gross output value. DETAILED DESCRIPTION

[0062] In order to make the objectives, technical solutions and advantages of the present invention more clearly understood, the present invention is further described in detail below in conjunction with embodiments and drawings. The exemplary embodiments of the present invention and their description are only used to explain the present invention and are not intended to limit the present invention.

[0063] The present invention aims to solve the problems encountered in manual document data extraction, data integration and internal and external data association, such as inconsistent data formats, poor data quality, low integration efficiency and high cross-checking complexity, especially for the automated cleaning, grouping, integration and cross-checking of monthly production report data of power supply companies, so as to improve the accuracy and efficiency of data processing.

[0064] This embodiment 1 provides a data cleaning grouping and cross-checking method based on Python, such as Figure 1 As shown, including:

[0065] S1. Obtain a general folder that needs to be sorted, wherein the general folder stores original data files of at least one format of monthly production and operation reports of multiple districts and counties belonging to a certain city in a certain month, and one of the formats is set as a set format; in this embodiment, the formats of the original data files include doc, docx, pdf, wps and rar, and the set format is docx. For example, the data files in the general folder are the original data files of the monthly production and operation reports of the districts and counties under Chengdu (such as Xinjin, Xindu, Wenjiang, Pujiang, etc.) in March 2020.

[0066] There is a lack of uniformity in the formats of data files in the folder. The file formats include doc, pdf, wps, rar and other types. Figure 2 As shown. Because it is a monthly report of a local company (the original data file of the monthly production and operation report), there is no direct Excel file from the previous project file. Therefore, the file format of the original data file of the monthly production and operation report here does not have Excel.

[0067] S2, converting the original data files in the general folder that are not in the set format into data files in the set format, and then storing the newly converted data files in the set format and the original data files that are originally in the set format as first data files in the same newly created first folder, wherein the first folder uses the city name of S1 as the folder name, and the name of the parent folder of the first folder is the month in S1;

[0068] Since the data is for one year, there are 12 folders in total, corresponding to 12 months. The folders named after the months corresponding to different districts and counties of the city are in the same file path, that is, the folder "January" contains multiple folders named after different cities. For example, the path of the Chengdu folder in January is "D / Data / January / Chengdu", and the path of the Dazhou folder in January is "D / Data / January / Dazhou".

[0069] In order to facilitate the subsequent batch extraction of data, all files in the general folder need to be converted to a unified file format. File format conversion code is written based on third-party Python libraries such as rarfile, pdf2docx, and win32com to realize batch conversion of production and operation monthly report related files into docx documents.

[0070] The specific steps of S2 are:

[0071] S2.1. Select the data files in the total folder according to the arrangement order of the files;

[0072] S2.2, determine whether the format of the data file is rar, if not rar, jump to S2.3, otherwise, jump to S2.13;

[0073] S2.3, determine whether the data file is a folder, if so, enter the folder, select the data file in the folder according to the arrangement order of the files, and jump to S2.2, otherwise, jump to S2.4;

[0074] S2.4, determining whether the format of the data file is a set format, if so, storing the data file in the first folder, and then jumping to S2.5, otherwise, converting the format of the data file into the set format according to the set conditions, and then storing the data file in the first folder and jumping to S2.5;

[0075] The specific process of converting a non-formatted data file into a formatted data file is as follows:

[0076] S2.4.1. Obtain the file name and extension of the data file and convert the extension into lower case;

[0077] S2.4.2. Determine the format of the data file:

[0078] If the extension is .wps, convert the wps file to docx format using the WPS API;

[0079] If the file extension is .doc, use pywin32 to convert the doc file to docx format;

[0080] If the file extension is .pdf, then use pdf2docx to convert the pdf file to docx format.

[0081] S2.5, determine whether the data file is the last one in the folder where the data file is located, if so, jump to S2.6, otherwise, jump to S2.12;

[0082] S2.6, determine whether the folder where the data file is located is a general folder, if not, jump to S2.7, otherwise, exit;

[0083] S2.7, determine whether the folder where the data file is located is the last one in the parent folder of the folder, if so, jump to S2.8, otherwise, jump to S2.11;

[0084] S2.8, determine whether the parent folder is a master folder, if so, exit, otherwise jump to S2.9;

[0085] S2.9, taking the parent folder as the original folder, obtaining the parent folder of the original folder, and determining whether the original folder is the last one of the parent folders. If so, return to S2.8; otherwise, jump to S2.10;

[0086] S2.10, determine the next data file in the parent folder and jump to S2.2;

[0087] S2.11, select the next data file in the folder where the data file in S2.7 is located in the parent folder, and jump to S2.2;

[0088] S2.12, select the next data file of the data file and jump to S2.2;

[0089] S2.13, decompress the file in rar format into a new folder and delete the original rar file;

[0090] S2.14. Determine the format of the data files in the new folder according to the arrangement order of the files. If the format is not rar, jump to S2.3; otherwise, jump to S2.13.

[0091] Figure 3 To implement the code for batch conversion of file formats, the specific conversion steps are as follows:

[0092] 1. Import the required libraries:

[0093] Import libraries such as os, rarfile, comtypes.client, win32com.client, and pdf2docx. Each library has specific functions and methods for implementing different steps of file format conversion.

[0094] Preliminary preparation for implementing the specific steps of S2.

[0095] 2. Define the convert_to_docx function:

[0096] Create a function named convert_to_docx to convert a single file into docx format. The function accepts a file path as a parameter, such as "D:\Data\May Monthly Report\Guang'an\xx County Power Supply Branch Production and Operation Statistics Analysis Monthly Report.pdf" is a specific file path.

[0097] The convert_to_docx function mainly implements the format conversion in S2.4.

[0098] 3. Get the file name and extension in the file path:

[0099] Use the os.path.splitext function to get the file name and extension, and convert the extension to lowercase. For example, the extension of "xx County Power Supply Branch Production and Operation Statistics Analysis Monthly Report.pdf" is ".pdf".

[0100] Prepare to determine the format of the data file.

[0101] 4. Perform the corresponding conversion operation according to the file extension:

[0102] 1) If the file extension is .wps, use the WPS API to convert the wps file to docx format (call the CreateObject function in the comtypes.client library to create a WPS application object; use

[0103] wps_app.Documents.Open opens the wps file;

[0104] Use the doc.SaveAs function to save the wps file as docx format; use the doc.Close function to close the wps file; use the wps_app.Quit function to close the WPS application. ).

[0105] 2) If the file extension is .doc, use pywin32 to convert the doc file to docx format (call the Dispatch function in the win32com.client library to create a Word application object; use word.Documents.Open to open the doc file; use doc.SaveAs function to save the doc file as docx format; use doc.Close function to close the doc file; use word.Quit function to close the Word application).

[0106] 3) If the file extension is .pdf, use pdf2docx to convert the pdf file to docx format (use pdf2docx.convert function to convert the pdf file to a byte stream in docx format; use the open function to open a new docx file and open it in binary write mode; write the docx byte stream to the new file; close the new file and the pdf file).

[0107] The above process is the specific conversion steps of the convert_to_docx function.

[0108] 5. Define the convert_files_to_docx function:

[0109] Create a function called convert_files_to_docx to batch convert files in a folder. The function accepts a folder path as a parameter.

[0110] The convert_to_docx function mainly implements the specific traversal conversion process of S2.1-S2.14.

[0111] 6. Traverse the files in the folder:

[0112] 1) Use the os.walk function to traverse the folder path and obtain the absolute path of each file. This function can obtain the absolute path of a file, so as to know whether there is a parent folder in the folder where the file is located and whether the parent folder is a master folder.

[0113] 2) Check if the file is a RAR file. If so, decompress the RAR file to the current folder and delete the original RAR file.

[0114] The remove function can be used to delete the original rar file.

[0115] 3) If it is not a RAR file, call the convert_to_docx function to convert the file to docx format and delete the original file.

[0116] 7. Call the convert_files_to_docx function:

[0117] In the example usage, the convert_files_to_docx function is called and the folder path is passed in as a parameter. For example, if "D:\data" is the folder path (the "data" folder is the main folder), it means that all rar, wps, pdf and doc files in the folder are converted into docx format files.

[0118] Realize the whole process of S2.1-S2.14.

[0119] Through the above process, the process of converting compressed files including rar format in the same folder into docx format files can be realized in batches, instead of simply converting individual files.

[0120] Figure 4 This is a document of the format conversion of files from various districts and counties of a power supply company, that is, all types of files are converted into docx files to facilitate subsequent data extraction.

[0121] S3, reading the specified first data file, obtaining the table data in the first data file according to the set condition combination, preprocessing the table data to obtain the standard table for various types of production and operation monthly reports, and summarizing and storing the relevant production and operation data of the same type of production and operation monthly reports of each power supply company in the same type of standard table, and storing the standard table in the form of a data table in the second folder, wherein one standard table corresponds to one data table;

[0122] In a standard form, the relevant production and operation data of all power supply companies in the region for one year are summarized in the standard form, so that the monthly production and operation data of all power supply companies in the region can be clearly known.

[0123] The specific steps of S3 are:

[0124] S3.1. Design the structure of the standard form according to the type of monthly production and operation report;

[0125] S3.2, sequentially read the text content of the first data file in the first folder, the text content including a table name, a table number and a table, and the table contains data;

[0126] S3.3, extracting the corresponding table name from the text content according to the table number;

[0127] S3.4. Find the first data file corresponding to the districts and counties under the city in the corresponding month according to the condition combination of month + city name, and open it, wherein the first data file corresponding to all districts and counties under the city from January to December is opened;

[0128] In this embodiment, the city

[0129] S3.5. Find the table corresponding to the same table name in the first data file in S3.4 according to the table name of the standard table, then traverse the rows and columns of the table, and extract the data in the table to the corresponding rows and columns in the standard table according to the correspondence between the row names and the column names to form internal data;

[0130] S3.6. Clean and process the data in the standard form, and finally store the standard form in the form of a data table in the second folder.

[0131] The above process is the process of data extraction, cleaning and integration.

[0132] After converting all kinds of files into docx documents, by analyzing the structure of the data tables in each docx document, we summarized the structural forms of several types of standard tables of data to be extracted (specifically, the standard tables corresponding to the types of production and operation monthly reports include: industry electricity classification table, electricity sales details table, line loss rate statistics table, 10kV heavy loss & negative loss line details, 10kV heavy loss & negative loss area details, 10kV heavy load line details and 10kV heavy load area details), such as Figure 5 For the detailed list of electricity sales in the monthly production and operation report, its structure can be summarized as follows: Figure 6 As shown. Write data extraction and cleaning code based on Python (code snippet as shown Figure 7 ), realize batch extraction of tables from docx documents, and finally integrate the same type of data together to realize data extraction and grouping. The specific procedure of implementing S3.1-S3.6 is as follows:

[0133] 1. Import the required libraries and modules:

[0134] Import various Python libraries and modules that you need to use in your scripts, such as docx for processing Word documents, pandas for data processing, os for file path operations, re for matching specific strings in documents, and sqlalchemy for interacting with databases.

[0135] 2. Define the file path and database connection:

[0136] The Word document path to be processed (PATH), year (year), data storage path, and connection information to the MySQL database (engine) are specified.

[0137] 3. Define auxiliary functions:

[0138] Some auxiliary functions for processing city names and table contents are defined in the code. For example, the re_city_name function is used to find the corresponding district and county names based on the city name.

[0139] The above is the preliminary preparation for implementing the specific steps of S3.

[0140] 4. Define the table name and table processing function:

[0141] Define the table name list (table_names) to be processed and the corresponding processing functions. The code of each function is compiled according to different table structures to process different table data. The seven processing functions table_one, table_two, table_three, table_four, table_five, table_six, and table_seven are used to process the industry electricity classification table, electricity sales detail table, line loss rate statistics table, 10kV heavy loss & negative loss line details, 10kV heavy loss & negative loss area details, 10kV heavy load line details, and 10kV heavy load area details in the production and operation monthly report, corresponding to S3.1.

[0142] 5. Main process of table data extraction:

[0143] It is mainly divided into the following steps:

[0144] a. Read Word document: Use the Document object to read text content from the specified Word document, corresponding to S3.2.

[0145] b. Get table information: Extract the table name in the document from the text content. This is identified by searching for "Table X" in the text, corresponding to S3.3.

[0146] c. Iterate the month and city: traverse the combination of month and city and enter the next loop.

[0147] d. Process the document for each month and city: find the corresponding document based on the combination of month and city, and then open the document, corresponding to S3.4.

[0148] e. Extract table data: In the open document, find the corresponding table according to the index of the table name (the position in the table name list), then traverse the rows and columns of the table to extract the data in the table, corresponding to S3.5.

[0149] f. Data cleaning and processing: Clean and process the extracted data, including adjusting the order of columns, deleting unnecessary rows, fixing data problems in the table, etc., corresponding to S3.6 for cleaning and processing the data in the standard table.

[0150] g. Data storage: The cleaned data is stored in a MySQL database, each table corresponds to a data table, and the to_sql method is used to implement it. At the end of S3.6, the standard table is stored in the second folder in the form of a data table.

[0151] like Figure 8 This is the summary result of the detailed data of electricity sales of each power supply company. Fig. 9 The results of the extraction and summary of 7 types of data are summarized, so that the data existing in various files are summarized into the same table to form an internal data set.

[0152] S4. Obtain and preprocess external data, and then cross-check the external characteristic values ​​of the external data with the data characteristic values ​​of the standard form corresponding to the production and operation monthly report, so that the external data and the table data in the standard form are associated in the same form.

[0153] The specific process of obtaining external data and preprocessing is as follows:

[0154] S4.1. Obtain a table of different types of external data, and all types of external data include year, city name, district and county name, and corresponding data values;

[0155] S4.2. Delete the rows with the same year and district and county name in the same type of external data.

[0156] The above process belongs to external data cleaning, such as deleting duplicate data of external district and county industrial gross product data. According to the meaning of the data itself, if both the year and district and county fields are repeated, they are judged as duplicates and need to be deleted, using the drop_duplicates function in pandas.

[0157] The specific process of cross-checking the external characteristic value of the external data with the characteristic value of the table data in the data table is as follows:

[0158] S4.3. Select corresponding external data and data tables according to the correlation required;

[0159] S4.4. Perform fuzzy matching and cross-reference based on the year, city name, district and county name of the external data and the year, city name, district and county name in the data table, and store the data values ​​in the external data and the table data in the data table belonging to the same year, city name, district and county name in the same table.

[0160] Taking the cross-checking of district and county electricity consumption data with external industrial gross domestic product as an example, the fields involved are year, city, and district and county. Therefore, for internal electricity consumption data and external industrial gross domestic product data, these three fields need to be processed into a standard format, such as the year is uniformly processed into the "2022" style, the city is uniformly processed into "xx city", and the district and county are uniformly processed into "xx county / district" (the above processing is implemented through the python string fuzzy matching code) to avoid the situation where internal and external data cannot be associated (such as the internal data is Pidu District, and the external data is Pidu).

[0161] We can use the merge function in pandas to cross-check the internal and external data. We use the three fields of year, city, and district to match the internal and external data together if the three fields are the same. The final cross-check result is as follows: Fig.11 .

[0162] This step belongs to the process of data cross-checking and association.

[0163] The data crawled externally mainly include basic characteristic data of districts and counties, industrial structure data, economic structure data, etc. Based on the attributes and dimensions of the external data set and combined with the characteristics of the internal data set, the Pandas library of Python is used to clean the crawled data and integrate the required data fields into a complete data table format, such as Fig.10 For the characteristic data of the districts and counties (i.e. external data). Based on the names, geographical locations and industrial types of each district and county, a data cross-checking code is written to cross-check the industrial electricity consumption of the district and county (internal data, in this invention, the data in the electricity sales detailed table) with the gross industrial output value, the main industrial types and the characteristic industrial park data, which can be used to mine the correlation between the industrial electricity consumption data of the district and county and the gross industrial output value. Based on the names, geographical locations and industry characteristics of each district and county, a data cross-checking code is written to cross-check the industrial electricity consumption of the district and county with the industrial structure type data, which can be used to mine the differences in electricity consumption of the main industries and the impact of the industrial parks of the main industries on the local economy.

[0164] The specific implementation methods described above further illustrate the objectives, technical solutions and beneficial effects of the present invention in detail. It should be understood that the above description is only a specific implementation method of the present invention and is not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A data cleaning grouping and cross-checking method based on Python, characterized in that: include: S1. Obtain a general folder that needs to be sorted, wherein the general folder stores original data files of production and operation monthly reports of multiple districts and counties belonging to a certain city in at least one format, and one of the formats is set as a set format; S2, converting the original data files in the general folder that are not in the set format into data files in the set format, and then storing the newly converted data files in the set format and the original data files that are originally in the set format as first data files in the same newly created first folder, wherein the first folder uses the city name of S1 as the folder name, and the name of the parent folder of the first folder is the month in S1; S3, reading the specified first data file, obtaining the table data in the first data file according to the set condition combination, preprocessing the table data to obtain the standard table for various types of production and operation monthly reports, and summarizing and storing the relevant production and operation data of the same type of production and operation monthly reports of each power supply company in the same type of standard table, and storing the standard table in the form of a data table in the second folder, wherein one standard table corresponds to one data table; S4. Obtain and preprocess external data, and then cross-check the external characteristic values ​​of the external data with the data characteristic values ​​of the standard form corresponding to the production and operation monthly report, so that the external data and the table data in the standard form are associated in the same form.

2. According to a Python-based data cleaning grouping and cross-checking method according to claim 1, it is characterized in that: The formats of the original data files include doc, docx, pdf, wps and rar.

3. According to a Python-based data cleaning grouping and cross-checking method according to claim 1, it is characterized in that: The setting format is docx.

4. According to a Python-based data cleaning grouping and cross-checking method according to claim 1, it is characterized in that: The specific steps of S2 are: S2.

1. Select the data files in the total folder according to the arrangement order of the files; S2.2, determine whether the format of the data file is rar, if not rar, jump to S2.3, otherwise, jump to S2.13; S2.3, determine whether the data file is a folder, if so, enter the folder, select the data file in the folder according to the order of the files, and jump to S2.2, otherwise, jump to S2.4; S2.4, determining whether the format of the data file is a set format, if so, storing the data file in the first folder, and then jumping to S2.5, otherwise, converting the format of the data file into the set format according to the set conditions, and then storing the data file in the first folder and jumping to S2.5; S2.5, determine whether the data file is the last one in the folder where the data file is located, if so, jump to S2.6, otherwise, jump to S2.12; S2.6, determine whether the folder where the data file is located is a general folder, if not, jump to S2.7, otherwise, exit; S2.7, determine whether the folder where the data file is located is the last one in the parent folder of the folder, if so, jump to S2.8, otherwise, jump to S2.11; S2.8, determine whether the parent folder is a master folder, if so, exit, otherwise jump to S2.9; S2.9, taking the parent folder as the original folder, obtaining the parent folder of the original folder, and determining whether the original folder is the last one of the parent folders. If so, return to S2.8; Otherwise, jump to S2.10; S2.10, determine the next data file in the parent folder and jump to S2.2; S2.11, select the next data file in the folder where the data file in S2.7 is located in the parent folder, and jump to S2.2; S2.12, select the next data file of the data file and jump to S2.2; S2.13, decompress the file in rar format into a new folder and delete the original rar file; S2.

14. Determine the format of the data files in the new folder according to the arrangement order of the files. If the format is not rar, jump to S2.3; otherwise, jump to S2.

13.

5. According to a Python-based data cleaning grouping and cross-checking method according to claim 4, it is characterized in that: In S2.4, the specific process of converting a data file in a non-set format into a data file in a set format is as follows: S2.4.

1. Obtain the file name and extension of the data file and convert the extension into lower case; S2.4.

2. Determine the format of the data file: If the extension is .wps, convert the wps file to docx format using the WPS API; If the file extension is .doc, use pywin32 to convert the doc file to docx format; If the file extension is .pdf, then use pdf2docx to convert the pdf file to docx format.

6. The Python-based data cleaning grouping and cross-checking method according to claim 1 is characterized in that: The specific steps of S3 are: S3.

1. Design the structure of the standard form according to the type of monthly production and operation report; S3.2, sequentially read the text content of the first data file in the first folder, the text content including a table name, a table number and a table, and the table contains data; S3.3, extracting the corresponding table name from the text content according to the table number; S3.

4. Find the first data file corresponding to the districts and counties under the city in the corresponding month according to the condition combination of month + city name, and open it, wherein the first data file corresponding to all districts and counties under the city from January to December is opened; S3.

5. Find the table corresponding to the same table name in the first data file in S3.4 according to the table name of the standard table, then traverse the rows and columns of the table, and extract the data in the table to the corresponding rows and columns in the standard table according to the correspondence between the row names and the column names to form internal data; S3.

6. Clean and process the data in the standard form, and finally store the standard form in the form of a data table in the second folder.

7. The Python-based data cleaning grouping and cross-checking method according to claim 1 is characterized in that: The standard forms corresponding to the types of production and operation monthly reports include: industry electricity consumption classification table, electricity sales details table, line loss rate statistics table, 10kV heavy loss & negative loss line details, 10kV heavy loss & negative loss substation details, 10kV heavy load line details and 10kV heavy load substation details.

8. The Python-based data cleaning grouping and cross-checking method according to claim 1 is characterized in that: In S4, the specific process of obtaining external data and preprocessing is as follows: S4.

1. Obtain a table of different types of external data, and all types of external data include year, city name, district and county name, and corresponding data values; S4.

2. Delete the rows with the same year and district and county name in the same type of external data.

9. A Python-based data cleaning grouping and cross-checking method according to claim 8, characterized in that: In S4, the specific process of cross-checking the external characteristic value of the external data with the characteristic value of the table data in the data table is: S4.

3. Select corresponding external data and data tables according to the correlation required; S4.

4. Perform fuzzy matching and cross-reference based on the year, city name, district and county name of the external data and the year, city name, district and county name in the data table, and store the data values ​​in the external data and the table data in the data table belonging to the same year, city name, district and county name in the same table.

10. A Python-based data cleaning grouping and cross-checking method according to claim 9, characterized in that: The types of external data include district and county basic characteristic data, industrial structure data, and economic structure data.

Images