Mass data exception monitoring and multi-dimensional cleaning method
By employing massive data anomaly monitoring and multi-dimensional cleaning methods, and utilizing the 3sigma criterion and data repair algorithms, the problem of low efficiency in traditional data cleaning methods has been solved, achieving intelligent data processing and automated repair, thereby improving the accuracy and adaptability of the data.
Patent Information
- Application Number
- CN202411058119.4
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-02
- Publication Date
- 2024-11-15
AI Technical Summary
Traditional data cleaning and verification methods rely on manual processing, which is inefficient and prone to errors. They cannot meet the needs of cleaning and verifying massive amounts of data and lack intelligent processing capabilities.
We employ massive data anomaly monitoring and multi-dimensional cleaning methods. We use the 3sigma criterion to identify abrupt and continuous bad data, repair the data using the Laida method and Lagrange interpolation algorithm, standardize the data using the Min-max method and logarithmic transformation method, and use an SQL database for data cleaning and verification.
It achieves intelligent data identification and classification, automated data cleaning and repair, improves the efficiency and accuracy of data processing, enhances the credibility and usability of data, and adapts to the needs of different data types and application scenarios.
Smart Images

Figure BDA0004977007090000021 
Figure BDA0004977007090000031 
Figure BDA0004977007090000061
Abstract
Description
Technical Field
[0001] The present invention belongs to the technical field of data processing and data management, and relates to a method for monitoring anomalies of massive data and cleaning them in multiple dimensions. Background Art
[0002] In the era of big data, enterprises and organizations are faced with massive and diverse data. These data often have various quality problems, such as errors, omissions, duplications, etc., which seriously affect the credibility and effectiveness of the data. Traditional data cleaning and verification methods often rely on manual processing, which is inefficient and prone to errors. Therefore, it is imperative to develop an intelligent data cleaning and verification system.
[0003] The current data cleaning and verification methods have the following problems:
[0004] 1. Reliance on manual processing: Traditional methods mainly rely on manual identification and processing of data quality issues, which is inefficient and prone to errors.
[0005] 2. Unable to cope with massive data: As the scale of data continues to grow, traditional methods are unable to effectively cope with the cleaning and verification needs of massive data, resulting in low processing efficiency.
[0006] 3. Lack of intelligent processing: Traditional methods lack intelligent data processing capabilities and are unable to use technologies such as machine learning and natural language processing to improve the efficiency and accuracy of data cleaning and verification. Summary of the invention
[0007] In order to solve the above problems, the technical solution adopted by the present invention is: a method for monitoring and cleaning a large amount of data anomalies in multiple dimensions, characterized in that it includes the following steps:
[0008] Obtain massive sample data;
[0009] Use the 3sigma criterion to judge mutational bad data, then identify continuous bad data by comparing the rate of change of data at each moment to the previous moment, and add markers to the data to distinguish missing values and outliers;
[0010] Supplement the marked missing data and abnormal data, convert massive sample data into numerical values in the same range, and realize massive data anomaly monitoring and multi-dimensional cleaning.
[0011] Furthermore, the process of supplementing the marked missing data and abnormal data to convert the massive sample data into values in the same interval is as follows:
[0012] First, the Laida method is used to eliminate abnormal data, and then the Lagrange interpolation algorithm is used to supplement the missing data. After the repair processing, the Min-max method and logarithmic transformation method in the normalization method are used for standardization, and the standardized sample data are converted into values in the same interval.
[0013] Furthermore: the method adopts a SQL database.
[0014] Further: the process of obtaining massive sample data is as follows:
[0015] S11: Obtain the database type of the massive sample data to be processed;
[0016] S12: Create a connection object according to the type of database used, and enter the database host address, port, user name, and password information;
[0017] S13: Create a cursor object: used to execute SQL queries and retrieve results;
[0018] S14: Use the execute() method of the cursor object to execute the SQL query statement;
[0019] S15: Use the fetchall(), fetchone() or fetchmany() method of the cursor object to obtain the query result;
[0020] S16: Close the cursor and connection to release resources.
[0021] Further: the process of using the 3sigma criterion to judge the sudden bad data and then identifying the continuous bad data by comparing the change rate of the data at each moment to the previous moment is as follows:
[0022] S21: First, pre-process the massive sample data to form an m×n-dimensional load data set:
[0023]
[0024] S22: Identify missing values in the load data and mark them as bad data;
[0025] S23: Calculate the mean of each row of the load data set and standard deviation
[0026] S24: Calculate the upper and lower limits of normal load data according to the 3sigma criterion: The upper limit of the load data at the first moment on the load curve is The lower limit of load data is
[0027] S25: determine mutation type bad data;
[0028] S26: Calculate the change rate of the load data sample at each moment relative to the previous moment
[0029]
[0030] S27: Determine the continuous bad data, if B i,j =0 or B i,j-1 =B i,j or B i,j =B i,j+1 , then the load data A ij This is continuous bad data.
[0031] Further: it also includes returning and outputting the abnormal mass data after multi-dimensional cleaning to the database of mass data, and the process is as follows:
[0032] Create new tables or use existing tables to store data;
[0033] Traverse the processed data and splice it into SQL insert or update statements.
[0034] Execute SQL statements to batch write the abnormal mass data after multi-dimensional cleaning into the database of acquired mass data.
[0035] Further: the process of judging mutation type bad data is as follows: if If the load data A ij is normal load data; otherwise, load data A ij It is mutation type bad data.
[0036] A massive data anomaly monitoring and multi-dimensional cleaning device, comprising:
[0037] Data input module: used to obtain massive sample data;
[0038] Missing value and outlier judgment module: used to judge mutation-type bad data using the 3sigma criterion, and then identify continuous bad data by comparing the change rate of data at each moment to the previous moment, and add markers to the data to distinguish missing values and outliers;
[0039] Missing value and outlier supplement module: used to supplement the marked missing data and outlier data, convert massive sample data into values in the same interval, and realize anomaly monitoring and multi-dimensional cleaning of massive data;
[0040] Data output module: used to return the abnormal massive data after multi-dimensional cleaning to the database of acquired massive data.
[0041] A computer device comprises: a processor and a memory, wherein the memory stores a program module, and the program module runs on the processor to implement any one of the methods described above.
[0042] A readable storage medium stores a program module, wherein the program module is executed in a processor to implement any of the methods described above.
[0043] The present invention provides a method for monitoring and cleaning a large amount of data anomalies in multiple dimensions, which has the following features:
[0044] Intelligent data identification and classification: Utilize the bad data identification method based on the combined statistical model (identifying mutation-type and continuous bad data) to intelligently identify and classify data, and quickly and accurately discover errors, omissions, duplications, and other problems in the data.
[0045] Automated data cleaning and repair: It can automatically identify and clean errors and inconsistencies in data, and provide intelligent data repair functions to automatically fill in missing values, remove duplicate data, etc., thereby improving data accuracy and completeness.
[0046] Scalability and flexibility: It has good scalability and flexibility, can adapt to the needs of different data types and application scenarios, supports customized data cleaning and verification rules, and meets the personalized needs of users.
[0047] It has the following advantages and benefits:
[0048] Improve efficiency and accuracy: This method can greatly improve the efficiency and accuracy of data processing and reduce the cost and risk of manual processing.
[0049] Enhance data credibility: Through automated data cleaning and verification functions, the accuracy, completeness and consistency of data can be improved, thereby enhancing the credibility and availability of data.
[0050] Adapt to diverse needs: It has good scalability and flexibility, and can adapt to different data types, such as continuous data, discrete data, etc.; and the needs of different application scenarios, such as heating, power supply, water conservancy, etc., to provide users with customized data processing services. BRIEF DESCRIPTION OF THE DRAWINGS
[0051] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.
[0052] Figure 1is a flow chart of the method;
[0053] Figure 2 Flow chart of missing value and outlier judgment in the present invention;
[0054] Figure 3 Flow chart of missing value and outlier supplementation in the present invention;
[0055] Figure 4 This is a structural diagram of a device for monitoring and cleaning massive data anomalies and multiple dimensions;
[0056] Figure 5 It is the operation interface of the data input module in the present invention. DETAILED DESCRIPTION
[0057] It should be noted that, in the absence of conflict, the embodiments of the present invention and the features in the embodiments may be combined with each other. The present invention will be described in detail below with reference to the accompanying drawings and in combination with the embodiments.
[0058] In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. The following description of at least one exemplary embodiment is actually only illustrative and is by no means intended to limit the present invention and its application or use. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.
[0059] Figure 1 is a flow chart of the method;
[0060] A method for monitoring and cleaning massive data anomalies in multiple dimensions, comprising the following steps:
[0061] S1: Obtain massive sample data;
[0062] S2: Use the 3sigma criterion to judge the bad data of mutation type, and then identify the bad data of continuous type by comparing the change rate of the data at each moment to the previous moment, and add markers to the data to distinguish missing values and outliers;
[0063] S3: Supplement the marked missing data and abnormal data, convert the massive sample data into values in the same interval, realize the abnormal monitoring and multi-dimensional cleaning of massive data, and meet the data specification requirements for subsequent use;
[0064] S4: The abnormal massive data after multi-dimensional cleaning is returned to the massive data database.
[0065] Steps S1 / S2 / S3 / S4 are executed sequentially;
[0066] This method uses SQL database
[0067] Furthermore, the process of obtaining massive sample data is as follows:
[0068] S11: Obtain the database type of the massive sample data to be processed;
[0069] S12: Create a connection object according to the type of database used, and enter the database host address, port, user name, and password information;
[0070] S13: Create a cursor object: used to execute SQL queries and retrieve results;
[0071] S14: Use the execute() method of the cursor object to execute the SQL query statement;
[0072] S15: Use the fetchall(), fetchone() or fetchmany() method of the cursor object to obtain the query result;
[0073] S16: Close the cursor and connection to release resources.
[0074] Figure 2 Flow chart of missing value and outlier judgment in the present invention;
[0075] Further: the process of using the 3sigma criterion to judge the sudden bad data and then identifying the continuous bad data by comparing the change rate of the data at each moment to the previous moment is as follows:
[0076] S21: First, pre-process the massive sample data to form an m×n-dimensional load data set:
[0077]
[0078] S22: Identify missing values in the load data and mark them as bad data; missing values are displayed as null in the database, while normal values are numerical. Data is read according to time, and the missing value is a missing value if there is no time.
[0079] S23: Calculate the mean of each row of the load data set and standard deviation
[0080] S24: Calculate the upper and lower limits of normal load data according to the 3sigma criterion: The upper limit of the load data at the first moment on the load curve is The lower limit of load data is
[0081] S25: Determine mutation type bad data; the process of determining mutation type bad data is as follows: If the load data A ij is normal load data; otherwise, load data A ij For mutation type bad data:
[0082] S26: Calculate the change rate of the load data sample at each moment relative to the previous moment
[0083] S27: Determine the continuous bad data, if B i,j =0 or B i,j-1 =B i,j or B i,j =B i,j+1 , then the load data A ij This is continuous bad data.
[0084] Furthermore, the process of preprocessing the massive sample data to form an m×n-dimensional load data set is as follows:
[0085] S211: Install necessary libraries: First, you need to install the `pandas` and `numpy` libraries for data processing and matrix operations. In addition, you also need to install drivers related to database connection, `pymysql` for MySQL.
[0086] S212: Import libraries: Import required libraries in Python scripts.
[0087] S213: Connect to database and read data: Establish a connection with the database and use SQL query to extract data from the database.
[0088] S214: Convert DataFrame to NumPy matrix: Convert the read `pandas` DataFrame to a NumPy array to form a matrix.
[0089] S215: Handling specific data formats: If you need to adjust the shape of a matrix (for example, reshape the data to a specific dimension m*n), you can use NumPy's reshape function.
[0090] Pandas is an open source Python library for data processing and analysis. It provides efficient and flexible data structures and operation tools that can easily process structured data. Numpy is an open source Python library for scientific computing. It provides powerful tools that support multidimensional arrays and matrix operations, and also contains a large number of mathematical function libraries.
[0091] DataFrame is one of the most important data structures in pandas. It is a two-dimensional, tabular data structure, similar to a table in a spreadsheet or database. Each column of DataFrame can be a different data type (numeric, string, Boolean, etc.), which makes it very flexible and powerful.
[0092] reshape is a function in NumPy that is used to change the shape of an array. It can reconfigure an array into a new shape without changing its data. In simple terms, it allows you to change the number of rows and columns of an array as long as the total number of elements in the array remains the same.
[0093] Figure 3 Flow chart of missing value and outlier supplementation in the present invention;
[0094] Furthermore, the process of supplementing the marked missing data and abnormal data to convert the massive sample data into values in the same interval is as follows:
[0095] First, the Raida method is used to remove abnormal data.
[0096] Then the Lagrange interpolation algorithm is used to supplement the missing data. After repairing it, the Min-max method and logarithmic transformation method in the normalization method are used for standardization, and the standardized sample data are converted into values in the same interval.
[0097] Furthermore, the Laida method is first used to remove abnormal data, and then the Lagrange interpolation algorithm is used to supplement the missing data. After repairing it, the Min-max method and logarithmic transformation method in the normalization method are used for standardization. The process of converting the standardized sample data into values in the same interval is as follows:
[0098] S31: First, the continuous bad data is removed by the Laida method to remove abnormal data. The Laida method (3δ criterion): Assume that the sample data set X obeys the normal distribution = (x1, x2, x3...x n ), the average value of which is Sample data x i The deviation from the mean is Calculate the standard deviation of the sample from (1):
[0099]
[0100] If the sample data x i The deviation satisfies: |v i |>3σ(i=1,2,...,n), then x iIf the sample data is abnormal, the sample data is eliminated. Otherwise, the sample data is retained and the 3σ criterion is repeatedly used until no abnormal data can be eliminated. After the sample data is inspected and eliminated, data gaps will be generated. The missing data can be interpolated to ensure the integrity and continuity of the sample data.
[0101] S32: The data processed by S31 are then supplemented with missing data through the Lagrange interpolation algorithm. The basic concept of Lagrange interpolation is: suppose the function y=f(x) is defined on the interval [a,b], and it is known that the function is at a≤x0<x1<…x n The function value at ≤b is y0. Find the n-degree polynomial P. n (x i ), such that:
[0102] P n (x i )=y i (i=1,2,…,n), #(2)
[0103] Function P n (x) is the interpolation function of y = f(x), x0, x1, …, x n is the interpolation node, [a,b] is called the interpolation interval, P n (x i )=y i is called the interpolation condition, where:
[0104]
[0105] It is called the nth-order Lagrange interpolation polynomial and is denoted as:
[0106]
[0107] Interpolation remainder: Use interpolation function L n (x) replaces the original function f(x), and its accuracy depends on the interpolation remainder, also known as the truncation error;
[0108] Let node a≤x0<x1<…<x n ≤b, and f satisfies the condition f∈C n [a,b], the n+1 derivative of the function f(x) (n+1) In [a, b], we examine the interpolation remainder R(X), which is the difference between the actual function f(x) and the interpolation polynomial L n The error between (x):
[0109] R n (x) = f(x) - L n (x), #(5)
[0110]
[0111] Right now:
[0112]
[0113] Among them, ξ∈[a,b] represents the value on the x-axis corresponding to the n+1-order derivative of f(x). Usually, ξ cannot be determined, but it is estimated |f (n+1) (x)|≤M n+1 , Will As the upper limit of error estimate, M n+1 It represents the maximum value of the n+1 derivative of f(x) in the interval;
[0114] When f(x) is any polynomial of degree not greater than n, f (n+1) (x)≡0, we know that R n (x)≡0, that is, the interpolation polynomial is exact for polynomials of degree not greater than n.
[0115] S33: The data processed by S32 is standardized by the Min-max method and logarithmic transformation method in the normalization method to convert the sample data into values in the same interval, effectively avoiding the problem of unequal treatment of input variables due to different dimensions, so as to achieve the purpose of simplifying the calculation. The normalization method mainly includes the Min-max method and the logarithmic transformation method:
[0116] Min-max method, its calculation formula is as follows:
[0117]
[0118] When the sample data value range is determined, the output value needs to be denormalized. The calculation formula is as follows:
[0119] x=x nor (x max -x min )+x min ,#(9)
[0120] Logarithmic transformation method, the calculation formula is as follows:
[0121] x nor =log 10 x,#(10)
[0122] The transformation is completed using a logarithmic function, which is suitable for the standardized processing of dynamic sample data.
[0123] In the above formula, x is the sample data, x max 、x min are the maximum and minimum values of the sample data, respectively, norThe sample data is standardized.
[0124] Furthermore, the process of returning the abnormal mass data after multi-dimensional cleaning to the database of mass data is as follows:
[0125] Create new tables or use existing tables to store data;
[0126] Traverse the processed data and splice it into SQL insert or update statements.
[0127] Execute SQL statements to batch write the abnormal mass data after multi-dimensional cleaning into the database of acquired mass data.
[0128] Figure 4 This is a structural diagram of a device for monitoring and cleaning massive data anomalies and multiple dimensions;
[0129] A massive data anomaly monitoring and multi-dimensional cleaning device, comprising:
[0130] Data input module: used to obtain massive sample data; establish a connection with the specified database by using the SQL database connection operation interface, then write and execute SQL query statements to read the required raw data from the database, and load the result set into the program for subsequent processing; Figure 5 It is the operation interface of the data input module in the present invention.
[0131] Missing value and outlier judgment module: used to judge mutation-type bad data using the 3sigma criterion, and then identify continuous bad data by comparing the change rate of data at each moment to the previous moment, and add markers to the data to distinguish missing values and outliers;
[0132] Missing value and outlier supplement module: used to supplement the marked missing data and outlier data, convert massive sample data into values in the same interval, and realize anomaly monitoring and multi-dimensional cleaning of massive data;
[0133] Data output module: used to return the abnormal mass data after multi-dimensional cleaning to the database of acquired mass data. Use the database connection operation interface to establish a connection with the target database, create a new table or use an existing table according to the needs, traverse the cleaned and standardized data, splice it into SQL statements and write it into the database in batches to complete the final storage of the data.
[0134] A computer device comprises: a processor and a memory, wherein the memory stores a program module, and wherein the program module runs on the processor to implement any of the methods described above.
[0135] A readable storage medium stores a program module, wherein the program module is executed in a processor to implement any of the methods described above.
[0136] In the heating industry, the first thing to obtain is the data in the heat exchange station (primary water supply temperature, primary return water temperature, primary network pressure, secondary water supply temperature, secondary return water temperature, water tank water level, circulating pump current / frequency, electric regulating valve opening, etc.)
[0137] Use the 3sigma criterion to judge mutational bad data, then identify continuous bad data by comparing the rate of change of data at each moment to the previous moment, and add markers to the data to distinguish missing values and outliers;
[0138] Supplement the marked missing data and abnormal data, convert massive sample data into numerical values in the same range, and realize massive data anomaly monitoring and multi-dimensional cleaning.
[0139] The processed heating data can help heating industry staff to control the specific conditions of the heat exchange station, and through detailed analysis of the heat users' heating behavior, help enterprises optimize the allocation of heating resources, improve the operating efficiency of the system, reduce energy waste, and ensure the stability and reliability of heating services.
[0140] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.
Claims
1. A method for monitoring anomalies and cleaning multi-dimensional massive data, characterized by: The following steps are involved: Obtain massive sample data; Use the 3sigma criterion to judge mutational bad data, then identify continuous bad data by comparing the rate of change of data at each moment to the previous moment, and add markers to the data to distinguish missing values and outliers; Supplement the marked missing data and abnormal data, convert massive sample data into numerical values in the same range, and realize massive data anomaly monitoring and multi-dimensional cleaning.
2. The method for abnormal monitoring and multi-dimensional cleaning of massive data according to claim 1, characterized in that: The process of supplementing the marked missing data and abnormal data to convert the massive sample data into values in the same interval is as follows: First, the Laida method is used to eliminate abnormal data, and then the Lagrange interpolation algorithm is used to supplement the missing data. After the repair processing, the Min-max method and logarithmic transformation method in the normalization method are used for standardization, and the standardized sample data are converted into values in the same interval.
3. The method for abnormal monitoring and multi-dimensional cleaning of massive data according to claim 1, characterized in that: This method uses a SQL database.
4. The method for abnormal monitoring and multi-dimensional cleaning of massive data according to claim 1, characterized in that: The process of obtaining massive sample data is as follows: S11: Obtain the database type of the massive sample data to be processed; S12: Create a connection object according to the type of database used, and enter the database host address, port, user name, and password information; S13: Create a cursor object: used to execute SQL queries and retrieve results; S14: Use the execute() method of the cursor object to execute the SQL query statement; S15: Use the fetchall(), fetchone() or fetchmany() method of the cursor object to obtain the query result; S16: Close the cursor and connection to release resources.
5. According to claim 1, the name of a massive data anomaly monitoring and multi-dimensional cleaning system design is also characterized by: The process of using the 3sigma criterion to judge the sudden bad data and then identifying the continuous bad data by comparing the change rate of the data at each moment to the previous moment is as follows: S21: First, pre-process the massive sample data to form an m×n-dimensional load data set: S22: Identify missing values in the load data and mark them as bad data; S23: Calculate the mean of each row of the load data set and standard deviation S24: Calculate the upper and lower limits of normal load data according to the 3sigma criterion: The upper limit of the load data at the first moment on the load curve is The lower limit of load data is S25: determine mutation type bad data; S26: Calculate the change rate of the load data sample at each moment relative to the previous moment S27: Determine the continuous bad data, if B i,j =0 or B i,j-1 =B i,j or B i,j =B i,j+1 , then the load data A ij This is continuous bad data.
6. According to claim 1, the name of a massive data anomaly monitoring and multi-dimensional cleaning system design is also characterized by: It also includes returning and outputting the abnormal mass data after multi-dimensional cleaning to the database of mass data. The process is as follows: Create new tables or use existing tables to store data; Traverse the processed data and splice it into SQL insert or update statements. Execute SQL statements to batch write the abnormal mass data after multi-dimensional cleaning into the database of acquired mass data.
7. According to claim 5, the name of a massive data anomaly monitoring and multi-dimensional cleaning system design is also characterized by: The process of judging mutation type bad data is as follows: If the load data A ij is normal load data; otherwise, load data A ij It is mutation type bad data.
8. A device for monitoring and cleaning a large amount of data anomalies in multiple dimensions, characterized by: include Data input module: used to obtain massive sample data; Missing value and outlier judgment module: used to judge mutation-type bad data using the 3sigma criterion, and then identify continuous bad data by comparing the change rate of data at each moment to the previous moment, and add markers to the data to distinguish missing values and outliers; Missing value and outlier supplement module: used to supplement the marked missing data and outlier data, convert massive sample data into values in the same interval, and realize anomaly monitoring and multi-dimensional cleaning of massive data; Data output module: used to return the abnormal massive data after multi-dimensional cleaning to the database of acquired massive data.
9. A computer device comprising: A processor and a memory, wherein the memory stores a program module, wherein the program module runs on the processor to implement the method according to any one of claims 1 to 7.
10. A readable storage medium storing a program module, characterized in that: The program module is executed in a processor to implement the method according to any one of claims 1 to 7.
