A data cleaning method and device based on probability density clustering
Through the method based on probability density clustering, the feature vector is constructed and numerical error data is detected using GradientBoostingClassifier, which solves the problems of inaccurate detection and excessive manual participation in the prior art, and realizes high-precision error data detection and repair.
Patent Information
- Application Number
- CN202111075791.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2021-09-14
- Publication Date
- 2024-11-12
- Estimated Expiration
- 2041-09-14
AI Technical Summary
The existing numerical error data detection methods are not accurate enough and have a lot of manual participation, resulting in the error detection of correct data as wrong data or the error data is not detected, affecting the accuracy and promotion of data cleaning.
Using a method based on probability density clustering, we calculate the probability density of numerical metadata, build a feature vector, and use GradientBoostingClassifier to build a binary classifier to realize error detection and repair of numerical metadata, and reduce the degree of manual participation.
The accuracy, recall and F1 value of wrong data detection are improved, which significantly reduces the workload and complexity of manual participation and improves detection accuracy.
Smart Images

Figure CN113934714B_ABST
Abstract
Description
Technical Field
[0001] The present invention belongs to the technical field of data cleaning, and relates to a data cleaning method and device based on probability density clustering. Background Art
[0002] It is very important to clean the numerical data output by sensors such as thermometers, CO2 concentration detectors, PM value detectors, atmospheric pressure detectors, gyroscopes, infrared distance detectors, etc. Through data cleaning, dirty data caused by the sensor itself or other reasons can be detected and repaired, so that when the cleaned data is used for data mining and decision-making, the errors in the original data will not lead to wrong results. In the two major steps of data cleaning, namely error detection and repair, error data detection is the first step. It is necessary to accurately detect the error data before the next step of error data repair can be carried out.
[0003] At present, the detection methods for batch-type numerical error data are not accurate enough or require more manual work and are more complicated, resulting in a high proportion of correct data being detected as error data or error data not being detected. In addition, it is difficult to operate in actual use, which affects the promotion. Therefore, in view of the problems existing in the current detection of numerical error data, it is necessary to invent an accurate error data detection method with less manual work. Summary of the invention
[0004] The purpose of the present invention is to further improve the precision, recall rate and F1 value in the existing error data detection method and reduce the degree of manual participation, and to propose a data cleaning method based on probability density clustering.
[0005] The technical solutions adopted by the present invention include the following:
[0006] A data cleaning method based on probability density clustering is implemented by the following steps:
[0007] Step (1), extracting numerical metadata by column from a specified database;
[0008] Step (2) is to find the probability density of the extracted numerical metadata by column; specifically:
[0009] 2-1 For the numerical metadata in column j, all its elements are grouped into a set U j , and form a set D with all its different element values j .
[0010] 2-2 Assuming that the numerical metadata satisfies the normal distribution, the probability density of all numerical metadata is calculated according to formula (1);
[0011]
[0012] where x∈D j , μ is D j The average value of all elements in D j The variance of .
[0013] Step (3), preprocess the numerical metadata according to the probability density and construct a training set.
[0014] 3-1 Calculate the weighted average probability density according to formula (2); Calculate the ratio p(x) of the weighted average probability density and the probability density of each numerical metadata according to formula (3) k );
[0015]
[0016]
[0017] where x i ∈D j Denotes the set D j The i-th element in f(x i ) is x i The probability density value, d(x i ) represents x i in U j The number of times it appears in D j The number of elements in D j The weighted average of the probability densities of all elements in , x k ∈U j Represents the set U j The kth element in f(x k ) is x k In D j The corresponding probability density of the same value in ;
[0018] 3-2 Based on experience, set the threshold set T = {t1, t2, ..., t m}, where all thresholds are arranged from small to large, and the adjacent thresholds are not equal, m is the number of custom thresholds; use the threshold to j Each element x in k Construct the feature vector F k =[f1,f2...f m ];
[0019]
[0020] Where 1≤l≤m;
[0021] 3-3 will Uj The feature vectors of all elements in are divided into y categories according to hierarchical clustering, where y is a value set based on experience;
[0022] 3-4 Randomly select one element from each cluster for labeling, marking it as correct data or incorrect data; the labeled y elements constitute the training data set.
[0023] Step (4): construct a binary data detection classifier and train it using the feature vectors and annotation results of the training data set.
[0024] Preferably, the classifier adopts GradientBoostingClassifier.
[0025] Step (5): Utilize the trained classifier to detect errors in numerical metadata.
[0026] Step (6), repairing the erroneous data detected in step (5).
[0027] Another object of the present invention is to provide a data cleaning device based on probability density clustering.
[0028] The data cleaning device based on probability density clustering includes a data extraction module, a trained data detection classifier module, and a data repair module.
[0029] Another object of the present invention is to provide a data cleaning device, comprising: a memory, a processor, and a data cleaning program based on probability density clustering stored in the memory and executable on the processor, wherein the data cleaning program based on probability density clustering implements the above method when executed by the processor.
[0030] Another object of the present invention is to provide a storage medium, on which is stored a data cleaning program based on probability density clustering, and the data cleaning program based on probability density clustering implements the above method when executed by the processor.
[0031] The present invention has the following beneficial effects:
[0032] 1. The present invention obtains the characteristic vector of the data through the probability density of the data, and then trains the model and predicts the data based on the characteristic vector. In the process of obtaining the characteristic vector from the probability density, a large number of thresholds are set and the accuracy is high. Therefore, the characteristic vector thus obtained is combined with the annotation for model training, which can effectively detect erroneous data with high accuracy.
[0033] 2. The workload of manual participation in the present invention is relatively small, and the complexity of the work involved is relatively low, and only y data need to be labeled. BRIEF DESCRIPTION OF THE DRAWINGS
[0034] Figure 1 This is a diagram of the architecture of the method of the present invention. DETAILED DESCRIPTION
[0035] The specific embodiments of the present invention are further described in detail below in conjunction with the accompanying drawings.
[0036] A data cleaning method based on probability density clustering, such as Figure 1 This is accomplished using the following steps:
[0037] Step (1), extracting numerical metadata by column from a specified database;
[0038] Step (2) is to find the probability density of the extracted numerical metadata by column; specifically:
[0039] 2-1 For the numerical metadata in column j, all its elements are grouped into a set U j , and form a set D with all its different element values j .
[0040] 2-2 Assuming that the numerical metadata satisfies the normal distribution, the probability density of all numerical metadata is calculated according to formula (1);
[0041]
[0042] where x∈D j , μ is D j The average value of all elements in D j The variance of .
[0043] Step (3), preprocess the numerical metadata according to the probability density and construct a training set.
[0044] 3-1 Calculate the weighted average probability density according to formula (2); Calculate the ratio p(x) of the weighted average probability density and the probability density of each numerical metadata according to formula (3) k );
[0045]
[0046]
[0047] where x i ∈D j Denotes the set D j The i-th element in f(x i ) is x i The probability density value, d(x i ) represents x i in U j The number of times it appears in D jThe number of elements in D j The weighted average of the probability densities of all elements in , x k ∈U j Represents the set U j The kth element in f(x k ) is x k In D j The corresponding probability density of the same value in ;
[0048] 3-2 Set the threshold set T = {0.85, 0.86, 0.87......1.19, 1.20}, a total of 36 thresholds. Set the strategy library S = {s1, s2,......s 36}, a total of 36 strategy lists. If p(x k )≥0.85, then record x in the strategy list s1 in the strategy library S. k The position subscript, if p(x k )≥0.86, then record x in the strategy list s2 in the strategy library S. k 's position subscript... and so on.
[0049] 3-3 According to each element x k Generate its feature vector F if there is / is not its position subscript in the strategy library S k =[f1,f2...f 36 ], that is, if the strategy list s in the strategy library S l (1≤l≤36) has x k The subscript of x k The eigenvector F k f l The value is 1 if yes, otherwise it is 0.
[0050] 3-4 will U j The feature vectors of all elements in are divided into 20 categories according to hierarchical clustering. Hierarchical clustering is an existing conventional technology, so it is not explained in detail.
[0051] 3-5 Randomly select 1 element from each cluster for labeling, labeling it as correct data or incorrect data (correct data is labeled as 0, incorrect data is labeled as 1). The labeled 20 data are the training data set.
[0052] Step (4), construct a binary data detection classifier, and use the feature vector and annotation results of the training set data for training. The classifier uses GradientBoostingClassifier.
[0053] Step (5): Utilize the trained classifier to detect errors in numerical metadata.
[0054] Step (6), repairing the erroneous data detected in step (5). The data repair here is an existing conventional technology, so it will not be explained in detail.
[0055] Experimental Results
[0056] For batch data prediction, the following three data sets are used to compare the effects of the model of the present invention and other models:
[0057] 1) Distance sensor dataset: The Distance (infrared distance) sensor provides 4 hours and 14 minutes of sensor data, with a total of 4017 records.
[0058] 2) CO2 sensor dataset. The CO2 sensor provides 4 days of sensor data, with a total of 4236 records.
[0059] 3) math_test dataset. The math_test dataset is a public dataset containing math test scores, with a total of 490 records.
[0060] The accuracy, recall and F1 value are used in the experiment to evaluate the performance of the algorithm:
[0061] (1) Confusion Matrix
[0062] The confusion matrix for a binary classification problem consists of four numbers:
[0063] False negative (FN): The number of examples predicted by the algorithm as negative (N) but actually positive (P), that is, the algorithm made a wrong prediction (False);
[0064] True negative (TN): The algorithm predicts a negative example (N), which is actually the number of negative examples (N), that is, the algorithm predicts correctly (True);
[0065] True Positives (TP): The number of examples predicted by the algorithm as positive (P) but actually negative (N), i.e., the algorithm made a wrong prediction (False);
[0066] False Positive (FP): The algorithm predicts a positive example (P), which is actually the number of positive examples (P), that is, the algorithm predicts correctly (True).
[0067] The confusion matrix is defined as follows:
[0068] Prediction value 0 Prediction value 1 True value 0 TN FP True value 1 FN TP
[0069] (2) Definition of precision:
[0070] Accuracy:
[0071] Therefore, the precision rate is the "ratio of the number of correct predictions among the data predicted as positive examples". In this experiment, it is the number of correct predictions among the data predicted as wrong.
[0072] (3) Definition of recall:
[0073] Recall:
[0074] Therefore, recall is the ratio of the number of correct predictions among the data that are actually positive examples. In this experiment, it is the ratio of the number of actually wrong data to the number of predicted wrong data.
[0075] (4) Harmonic mean
[0076] Precision and recall are mutually exclusive, that is, when the precision is high, the recall is low. In some scenarios, both precision and recall must be taken into account, so there is the F1 value.
[0077] The F1 value is used to comprehensively evaluate the precision and recall rate. When both the precision and recall rate are high, the F1 value will also be high.
[0078] The formula for F1 is:
[0079]
[0080] Experimental results show that for the prediction of erroneous data of batch numerical data, under the same manual participation of annotating 20 elements, the erroneous data detection method proposed by the present invention is 19%-59% better than other advanced methods.
Claims
1. A data cleaning method based on probability density clustering is implemented by the following steps: Step (1), extracting numerical metadata from the database by column; Step (2), calculating the probability density of the extracted numerical metadata by column; Specifically: 2-1 For the numerical metadata in column j, all its elements are grouped into a set U j , all different element values form a set D j ; 2-2 Assuming that the numerical metadata satisfies the normal distribution, the probability density of all numerical metadata is calculated according to formula (1); where x∈D j , μ is D j The average value of all elements in D j The variance of Step (3), preprocessing the numerical metadata according to the probability density to construct a training set; 3-1 Calculate the weighted average probability density according to formula (2); Calculate the ratio p(x) of the weighted average probability density and the probability density of each numerical metadata according to formula (3) k ); where x i ∈D j Denotes the set D j The i-th element in f(x i ) is x i The probability density value, d(x i ) represents x i in U j The number of times it appears in D j The number of elements in D j The weighted average of the probability densities of all elements in , x k ∈U j Represents the set U j The kth element in f(x k ) is x k In D j The corresponding probability density of the same value in ; 3-2 Based on experience, set the threshold set T = {t1, t2, ..., t m }, where all thresholds are arranged from small to large, and m is the number of custom thresholds; j Each element x in k Construct the feature vector F k =[f1,f2...f m ]; Where 1≤l≤m; 3-3 will U j The feature vectors of all elements in are divided into y categories according to the hierarchical clustering; 3-4 Randomly select one element from each cluster for labeling, labeling it as correct data or incorrect data; the labeled y elements constitute the training data set; Step (4), constructing a binary data detection classifier, and training it using the feature vectors and annotation results of the training data set; Step (5), using the trained classifier to detect errors in numerical metadata; Step (6), repairing the erroneous data detected in step (5).
2. The method according to claim 1, characterized in that The binary data detection classifier uses GradientBoostingClassifier.
3. A data cleaning device based on probability density clustering for implementing any of the methods described in claims 1-2, characterized in that The data cleaning device based on probability density clustering includes a data extraction module, a trained data detection classifier module, and a data repair module.
4. A data cleaning device, characterized in that include: A memory, a processor, and a data cleaning program based on probability density clustering stored in the memory and running on the processor, wherein the data cleaning program based on probability density clustering implements the method described in any one of claims 1-2 when executed by the processor.
5. A storage medium, characterized in that The storage medium stores a data cleaning program based on probability density clustering, and when the data cleaning program based on probability density clustering is executed by the processor, the method described in any one of claims 1-2 is implemented.
