Data cleaning and repairing method and device

Abstract

The invention provides a data cleaning and repairing method and device. The method comprises the following steps: acquiring to-be-cleaned data comprising a numerical field and a text field; performing abnormal value detection on the numerical field and the text field based on an isolated forest and an association rule mining algorithm to obtain a first detection result and a second detection result; when the first detection result and / or the second detection result are / is abnormal, performing anomaly detection on the text field based on a semantic detection model to obtain a third detection result; inputting the first detection result, the second detection result and the third detection result into a meta-learner for dynamic weighted voting to obtain a comprehensive detection result; when the comprehensive detection result is abnormal, intelligently repairing the to-be-cleaned data based on an AI model to obtain repaired data; and obtaining optimization data after the restoration data is optimized based on manual work, and performing incremental training on the AI model based on the optimization data. According to the invention, the quality and availability of the cleaned and repaired data are improved.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, specifically to a data cleaning and repair method and apparatus. Background Technology

[0002] With the rapid development of the Internet and the Internet of Things, data volume has become an important asset and fundamental support for enterprises. However, due to the many stages of data generation, the diversity of data types, the huge volume of data, and the difficulty in guaranteeing data quality, data quality issues have become an important factor restricting the utilization value of enterprise data.

[0003] Currently, resolving data quality issues relies on scripts or rule engines, requiring manual writing of regular expressions, thresholds, dictionaries, and other rules. This results in high maintenance costs and low coverage, while the results obtained using common big data algorithms are relatively singular and incomplete. When faced with multi-source heterogeneous data (such as logs, sensor data, and user forms), rules struggle to encompass all abnormal scenarios, leading to "missed data" or "accidental deletion." The remediation phase typically involves simply filling in averages or fixed values, failing to restore the true meaning of the data for the business context.

[0004] Therefore, there is an urgent need to provide a data cleaning and repair method and device to achieve intelligent, comprehensive and accurate detection of anomalies and improve repair results, thereby improving data quality. Summary of the Invention

[0005] In view of this, it is necessary to provide a data cleaning and repair method and apparatus to solve the technical problems existing in the technology, such as the reliance on scripts or rule engines, which cannot exhaust all abnormal scenarios, resulting in inaccurate anomaly detection results. At the same time, the repair stage simply fills in the mean or fixed value, which cannot restore the true meaning of the business, resulting in low quality of the repaired data.

[0006] To address the aforementioned technical problems, in a first aspect, the present invention provides a data cleaning and repair method, comprising: Obtain the data to be cleaned, which includes numeric fields and text fields; An outlier detection is performed on the numerical field based on an isolated forest algorithm to obtain a first detection result, and an outlier detection is performed on the text field based on an association rule mining algorithm to obtain a second detection result. When the first detection result and / or the second detection result is abnormal, the text field is subjected to anomaly detection based on the semantic detection model to obtain a third detection result; The first detection result, the second detection result, and the third detection result are input into the meta-learner for dynamic weighted voting to obtain a comprehensive detection result. When the comprehensive detection result is abnormal, the data to be cleaned is intelligently repaired based on the AI ​​model to obtain repaired data. The process involves obtaining optimized data based on manual adjustments to the repaired data, incrementally training the AI ​​model using the optimized data to obtain an optimized AI model, and then performing subsequent data repair based on the optimized AI model.

[0007] In one possible implementation, the outlier detection of the numerical field based on an isolated forest to obtain a first detection result includes: Input the numerical field into the isolated forest to obtain the anomaly score; Determine the upper quartile, lower quartile, and interquartile range corresponding to the numerical field, and determine the dynamic anomaly boundary based on the upper quartile, lower quartile, and interquartile range; When the abnormal score is not located at the dynamic abnormal boundary, the first detection result is determined to be abnormal.

[0008] In one possible implementation, the text field includes multiple text subfields; then, the outlier detection of the text field based on the association rule mining algorithm to obtain a second detection result includes: Determine whether the multiple text subfields satisfy the association rules determined by the association rule mining algorithm. If they do not satisfy the association rules, then determine that the second detection result is abnormal.

[0009] In one possible implementation, the semantic detection model is a BERT model and a CRF sequence labeling model.

[0010] In one possible implementation, the data to be cleaned includes continuous data and categorized data, and the AI ​​model includes the LightGBM multi-classification model; intelligent repair of the data to be cleaned based on the AI ​​model includes: K similar data points of the continuous data are identified, and the weight of each similar data point is determined based on the similarity. The K similar data points are then weighted and averaged to obtain the target data. The target data is used as the missing value of the data to be cleaned. The categorized data is input into the LightGBM multi-classification model to obtain classification prediction values, and these classification prediction values ​​are used as the missing values ​​of the data to be cleaned.

[0011] In one possible implementation, the AI ​​model further includes an anomaly correction model; intelligent repair of the data to be cleaned based on the AI ​​model includes: When the numerical field contains outliers, the outliers in the data to be cleaned are corrected based on the business logic or calculation relationship of the numerical field. When an outlier exists in the text field, the outlier is input into the anomaly correction model to obtain multiple candidate correction values. The context information of the text field is also obtained. Based on the context information, a target correction value is determined from the multiple candidate correction values. The outlier in the data to be cleaned is corrected based on the target correction value.

[0012] In one possible implementation, the incremental training of the AI ​​model based on the optimized data to obtain an optimized AI model includes: Obtain the frequency of occurrence of error types in the repaired data, and determine the training weights of the optimized data based on the frequency of occurrence; The AI ​​model is incrementally trained based on the training weights and the optimization data to obtain an optimized AI model.

[0013] In one possible implementation, prior to incrementally training the AI ​​model based on the optimized data, the method further includes: Identify the sensitive fields in the optimized data and add noise to the sensitive fields that meets local differential privacy requirements.

[0014] In one possible implementation, the data to be cleaned includes a first text field and a second text field; then, before the intelligent repair of the data to be cleaned based on the AI ​​model, the following is also included: When the first text field and the second text field are short text fields, the Jaro-Winkler distance between the first text field and the second text field is determined, and Soundex encoding is performed on the first text field and the second text field to obtain the first encoding and the second encoding. Based on the Jaro-Winkler distance, the first encoding and the second encoding, the comprehensive field similarity score of the first text field and the second text field is determined. When the comprehensive field similarity score is greater than the similarity score threshold, the text field with the timestamp updated in the first text field and the second text field is retained. When the first text field and the second text field are long text fields, the first text field and the second text field are input into the Sentence-BERT model to obtain the first text vector and the second text vector, and the cosine similarity between the first text vector and the second text vector is determined. When the cosine similarity is greater than the similarity threshold, the text field with the timestamp updated in the first text field and the second text field is retained.

[0015] Secondly, the present invention also provides a data cleaning and repair apparatus, comprising: A data to be cleaned acquisition unit is used to acquire data to be cleaned, which includes numeric fields and text fields. The preliminary detection unit is used to perform outlier detection on the numerical field based on the isolated forest to obtain a first detection result, and to perform outlier detection on the text field based on the association rule mining algorithm to obtain a second detection result; A secondary detection unit is used to perform anomaly detection on the text field based on a semantic detection model to obtain a third detection result when the first detection result and / or the second detection result are abnormal. The comprehensive detection unit is used to input the first detection result, the second detection result, and the third detection result into the meta-learner for dynamic weighted voting to obtain a comprehensive detection result; The repair unit is used to intelligently repair the data to be cleaned based on an AI model when the comprehensive detection result is abnormal, and obtain repaired data. The model optimization unit is used to acquire optimized data based on manual optimization of the repaired data, incrementally train the AI ​​model based on the optimized data to obtain an optimized AI model, and perform subsequent data repair based on the optimized AI model.

[0016] The beneficial effects of this invention are as follows: The data cleaning and repair method provided by this invention achieves automated data cleaning and repair through intelligent models such as isolated forests, meta-learners, and AI models. Compared with traditional methods that rely on scripts or rule engines, it can achieve full-scenario anomaly coverage. Furthermore, intelligent repair based on AI models, compared with single repair methods using fixed values, can further align with actual business scenarios and improve the data quality of the repaired data.

[0017] Furthermore, the meta-learner dynamically weights the first, second, and third detection results through voting, addressing the limitation of a single anomaly detection result and further improving the reliability and accuracy of the comprehensive detection results. Simultaneously, anomaly detection is only performed on the text field based on the semantic detection model when the first and / or second detection results are anomaly-indicating. Lightweight isolation forest and association rule mining algorithms are used, with the semantic detection model employed only when necessary, thus avoiding unnecessary computational overhead while ensuring anomaly detection accuracy.

[0018] Furthermore, by incrementally training the AI ​​model with optimized data obtained from manually refined repair data, the optimized AI model can continuously learn from real-world business scenarios, ensuring rapid adaptation to new data anomalies and changes in business rules, thereby further improving the data quality of subsequently obtained repair data. In other words, this invention achieves a shift in data cleaning and repair from static rule execution to dynamic intelligent evolution, significantly improving detection accuracy, processing efficiency, business adaptability, and business fit. Attached Figure Description

[0019] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0020] Figure 1 A schematic flowchart of an embodiment of the data cleaning and repair method provided by the present invention; Figure 2 A schematic flowchart of an embodiment of outlier detection for numerical fields based on isolated forest provided by the present invention; Figure 3 This is a schematic diagram of an embodiment of incremental training of an AI model based on optimized data provided by the present invention. Figure 4 This is a schematic diagram of an embodiment of the data cleaning and repair device provided by the present invention. Detailed Implementation

[0021] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of the present invention, and not all of them. All other embodiments obtained by those skilled in the art based on the embodiments of the present invention without creative effort are within the scope of protection of the present invention.

[0022] It should be understood that the schematic drawings are not drawn to scale. The flowcharts used in this invention illustrate operations implemented according to some embodiments of the invention. It should be understood that the operations in the flowcharts may be implemented out of order, and steps without logical contextual relationships may be reversed or performed simultaneously. Furthermore, those skilled in the art, guided by the content of this invention, may add one or more other operations to the flowcharts, or remove one or more operations from the flowcharts. Some block diagrams shown in the drawings are functional entities and do not necessarily correspond to physically or logically independent entities. These functional entities may be implemented in software, in one or more hardware modules or integrated circuits, or in different network and / or processor devices and / or microcontroller devices.

[0023] In this document, the term "embodiment" means that a particular feature, structure, or characteristic described in connection with an embodiment may be included in at least one embodiment of the invention. The appearance of this phrase in various places throughout the specification does not necessarily refer to the same embodiment, nor is it a mutually exclusive, independent, or alternative embodiment. It will be explicitly and implicitly understood by those skilled in the art that the embodiments described herein can be combined with other embodiments.

[0024] This invention provides a data cleaning and repair method and apparatus, which are described below.

[0025] Figure 1 This is a schematic flowchart of an embodiment of the data cleaning and repair method provided by the present invention, as shown below. Figure 1 As shown, data cleaning and repair methods include: S101. Obtain the data to be cleaned, which includes numeric fields and text fields; S102. Perform outlier detection on the numerical field based on isolated forest to obtain the first detection result, and perform outlier detection on the text field based on association rule mining algorithm to obtain the second detection result.

[0026] In step S102, it is necessary to construct an isolated forest. The specific process is as follows: randomly select multiple sub-samples from the historical numerical fields, and for each sub-sample, recursively select one feature and one segmentation value, and propose a binary isolated tree. Multiple binary isolated trees form an isolated forest.

[0027] Specifically, the association rule mining algorithm is the Apriori algorithm.

[0028] S103. When the first detection result and / or the second detection result are abnormal, perform anomaly detection on the text field based on the semantic detection model to obtain the third detection result.

[0029] Similarly, the semantic detection model needs to be trained in step S103. In a specific embodiment of the present invention, the semantic detection model is a BERT + CRF sequence labeling model. Its anomaly detection process specifically involves: converting the text field into units that the semantic detection model can process, such as characters or words. Then, the segmented sequence is input into the BERT model. The BERT model utilizes its knowledge learned from massive amounts of text to generate a feature vector rich in contextual semantic information for each character / word in the sequence. For example, the BERT model knows that "iphone" and "iPhone" are very similar in context, but the format is incorrect. Further, the CRF sequence labeling model receives the feature vector generated by the BERT model, labels each character / word, and based on the label sequence output by the CRF, the model can identify data anomalies such as spelling errors, non-standard formatting, and missing units.

[0030] S104. Input the first detection result, the second detection result, and the third detection result into the meta-learner for dynamic weighted voting to obtain the comprehensive detection result.

[0031] Specifically, the first, second, and third detection results all include whether an anomaly exists and the confidence level of this result. The meta-learner cross-compares the results, which solves the limitations of a single model. For example, the isolated forest model only looks at the numerical distribution and ignores the business logic. The meta-learner improves the accuracy of the detection results by introducing association rule features to access the business context.

[0032] S105. When the comprehensive test result is abnormal, the data to be cleaned is intelligently repaired based on the AI ​​model to obtain repaired data.

[0033] Specifically, intelligent repair includes two dimensions of repair: filling in missing values ​​in the data to be cleaned and correcting outliers in the data to be cleaned.

[0034] In a specific embodiment of the present invention, the model structure of the AI ​​model is a deep residual structure, which can achieve the same effect as a general neural network with fewer learning iterations.

[0035] S106. Obtain optimized data after manual optimization of the repair data, perform incremental training on the AI ​​model based on the optimized data to obtain the optimized AI model, and perform subsequent data repair based on the optimized AI model.

[0036] It should be noted that the optimization cycle of the AI ​​model can be set. Within an optimization cycle, the optimized AI model from the previous optimization cycle is used to repair the data.

[0037] It should be understood that the data cleaning and repair methods in the embodiments of the present invention can be implemented in any device based on data cleaning and repair methods, such as a data processing device based on data cleaning and repair. Specifically, the data cleaning and repair methods are stored in the aforementioned device as a pre-programmed program. When the device is started, the program is invoked, and the data cleaning and repair methods are implemented.

[0038] Compared with existing technologies, the data cleaning and repair method provided in this invention achieves automated data cleaning and repair through intelligent models such as isolated forests, meta-learners, and AI models. Compared with traditional methods that rely on scripts or rule engines, it can achieve full-scenario anomaly coverage. At the same time, intelligent repair based on AI models, compared with a single repair method with fixed values, can further align with actual business scenarios and improve the data quality of the repaired data.

[0039] Furthermore, the meta-learner dynamically weights the first, second, and third detection results through voting, addressing the limitation of a single anomaly detection result and further improving the reliability and accuracy of the comprehensive detection results. Simultaneously, anomaly detection is only performed on the text field based on the semantic detection model when the first and / or second detection results are anomaly-indicating. Lightweight isolation forest and association rule mining algorithms are used, with the semantic detection model employed only when necessary, thus avoiding unnecessary computational overhead while ensuring anomaly detection accuracy.

[0040] Furthermore, by incrementally training the AI ​​model with optimized data obtained from manually refined repair data, the optimized AI model can continuously learn from real-world business scenarios, ensuring rapid adaptation to new data anomalies and changes in business rules, thereby further improving the data quality of subsequently obtained repair data. In other words, this embodiment of the invention achieves a shift in data cleaning and repair from static rule execution to dynamic intelligent evolution, significantly improving detection accuracy, processing efficiency, business adaptability, and business fit.

[0041] To improve the accuracy of the first detection result, in some embodiments of the present invention, such as... Figure 2 As shown, step S102, which involves outlier detection on the numerical field based on an isolated forest to obtain a first detection result, includes: S201. Input the numerical field into the isolated forest to obtain the anomaly score.

[0042] Specifically, the numerical field is input into the isolated forest, which then traverses each binary tree and calculates the path length required to isolate a tree in each tree. The average path length across all trees is then calculated, and the outlier score is determined based on this average.

[0043] The formula for calculating the outlier score is as follows:

[0044] In the formula, These are abnormal scores; This represents the average path length. This is the normalization factor.

[0045] S202. Determine the upper quartile, lower quartile, and interquartile range corresponding to the numerical field, and determine the dynamic anomaly boundary based on the upper quartile, lower quartile, and interquartile range.

[0046] Specifically, the interval of the dynamic anomaly boundary is represented as: [Q1-k×IQR, Q3+k×IQR], where Q1 is the lower quartile, Q3 is the upper quartile, IQR is the interquartile range, and k is the adaptive coefficient.

[0047] S203. When the abnormal score is not located in the dynamic abnormal range, the first detection result is determined to be abnormal.

[0048] The embodiments of the present invention use an adaptive box plot method to determine dynamic anomaly boundaries, which greatly improves the accuracy of the first detection result compared with the traditional fixed threshold setting method that cannot adapt to data distribution.

[0049] In practical applications, association rule mining algorithms are used to discover the relationships between different text subfields in a text field. For example, "Province = Guangdong" contradicts "Postal Code = 100000" (Beijing postal code). However, the postal code field itself is not problematic; without anomaly detection using an association rule mining algorithm, this anomaly cannot be detected. Therefore, in some embodiments of the present invention, step S102, which uses an association rule mining algorithm to perform outlier detection on the text field and obtain a second detection result, includes: Determine whether multiple text subfields satisfy the association rules determined by the association rule mining algorithm. If they do not satisfy the rules, then the second detection result is determined to be abnormal.

[0050] Specifically, association rule mining algorithms can generate valuable association rules from frequent itemsets. For example, rules can be generated from the frequent itemset {province = Guangdong, postal code = starting with 5}. Rule 1: Province = Guangdong - Postal Code = Starting with 5 (Confidence Level = 98%) Rule 2: Postal code = starting with 5 - province = Guangdong (confidence level = 60%).

[0051] If the text field shows province = Guangdong, but the postal code = 100000, it satisfies the premise of rule 1 (province is Guangdong), but the conclusion (postal code starts with 5) is not true. Because rule 1 has a high confidence level, this record will be marked as an anomaly.

[0052] In a specific embodiment of the present invention, when performing intelligent repair on the data to be cleaned to fill in missing values, it is necessary to fill in the missing values ​​according to whether the data to be cleaned is continuous data or categorized data. Continuous data may include price, age, etc., while categorized data may include brand, category, etc.

[0053] Specifically, step S105, which involves intelligent repair of the data to be cleaned based on an AI model, includes: Identify K similar data points for continuous data, determine the weight of each similar data point based on similarity, and perform a weighted average of the K similar data points based on the weights to obtain the target data. The target data is then used as the missing values ​​in the data to be cleaned. The categorized data is input into the LightGBM multi-classification model to obtain the classification prediction values, and these prediction values ​​are used as the missing values ​​of the data to be cleaned.

[0054] Specifically, when there are missing values ​​in the categorized data, the existing other fields are input into the LightGBM multi-classification model to obtain multiple classification prediction values, and the classification prediction value with the highest probability is taken as the missing value of the data to be cleaned.

[0055] The similarity of the K similar data can be Euclidean distance or cosine similarity.

[0056] This invention uses K-NN regression weighted average to impute missing values ​​in continuous data, which avoids distribution distortion caused by mean imputation, reduces imputation error by 60%, and greatly improves the data quality of the repaired data.

[0057] In a specific embodiment of the present invention, when intelligent repair of the data to be cleaned is performed to correct outliers in the data to be cleaned, the specific steps are as follows: When numeric fields contain outliers, the outliers in the data to be cleaned are corrected based on the business logic or calculation relationships of the numeric fields.

[0058] For example, if the "sales amount" is abnormal, but the "sales volume" and "unit price" are normal, the sales amount can be automatically corrected to sales volume × unit price.

[0059] When outliers exist in a text field, the outliers are input into the anomaly correction model to obtain multiple candidate correction values. The context information of the text field is also obtained. Based on the context information, the target correction value among the multiple candidate correction values ​​is determined. The outliers in the data to be cleaned are corrected based on the target correction value.

[0060] For example, if the erroneous text field is "background city", inputting it into the anomaly correction model will yield {"Beijing": 0.85, "Beihai": 0.10, ...,}, where the numbers are the probabilities calculated by the model. In this case, when the context information is "province", the target correction value can be determined to be "Beijing".

[0061] In summary, the embodiments of the present invention can fill in continuous missing values ​​and categorical missing values, and correct numerical anomalies and textual anomalies, realizing a complete intelligent data cleaning closed loop from identification to repair, which significantly improves data quality and usability.

[0062] To improve the fit between the optimized AI model obtained through training and business scenarios, in some embodiments of the present invention, such as... Figure 3 As shown, step S106, which involves incrementally training the AI ​​model based on optimized data to obtain an optimized AI model, includes: S301. Obtain the frequency of error types in the repair data and determine the training weights of the optimization data based on the frequency of occurrence. S302. Incrementally train the AI ​​model based on training weights and optimized data to obtain an optimized AI model.

[0063] Specifically, step S302 involves the following: If the error of misspelling "background city" as "Beijing Municipality" has been manually corrected 1000 times in the past month, while other errors have only occurred a few dozen times, then the system will determine that "city name misspelling" is a high-frequency, common problem. In the next round of model training, all training samples belonging to the "city name misspelling" category will receive a higher weight. In the loss function used for training, the prediction error of each sample will be multiplied by the weight of that sample, and then summed.

[0064] In other words, the model will pay more attention to those samples with high weights (i.e. common errors) and try to reduce prediction errors.

[0065] It should be understood that incremental training is implemented using LoRA technology.

[0066] By assigning higher training weights to samples that appear more frequently, this invention can maximize the speed of business value delivery under the constraint of limited resources by optimizing the priority of learning objectives.

[0067] Because the optimized data may contain sensitive fields such as user privacy or trade secrets, directly using this data could lead to privacy or confidentiality leaks. To address this technical problem, in some embodiments of the present invention, before incrementally training the AI ​​model based on the optimized data, the following steps are also included: Identify sensitive fields in the data to optimize, and add noise to these sensitive fields that meets local differential privacy requirements.

[0068] By adding noise to sensitive fields, the embodiments of the present invention enable the model to learn the overall distribution after adding noise, rather than the sensitive fields. Sensitive information is hidden, but statistical features are preserved. Under the premise of achieving model optimization training, sensitive data is protected from leakage.

[0069] To further ensure that data is not leaked, in some embodiments of the present invention, each business unit can train based on local sensitive fields, and only send the updated parameters of the trained model or the discovered abnormal pattern rules to the central server for aggregation. That is, the optimized data does not leave the domain based on the federated learning model, ensuring that it is not leaked.

[0070] Furthermore, as described above, AI models and other models are used. These models require training, and training requires historical data. For fields without historical data, this embodiment of the invention proposes to generate training samples using minority class oversampling (SMOTE) or by calling a general pre-trained model to achieve small sample cold start.

[0071] Furthermore, the embodiments of the present invention include multiple execution steps. To improve the repair efficiency, Apache Flink can be used to implement incremental detection, making the latency of data cleaning and repair less than 100 ms.

[0072] In practical application scenarios, there are redundant or duplicate data in the data to be cleaned. To avoid excessive computational volume caused by this data and affect the repair efficiency, in some embodiments of the present invention, when the data to be cleaned includes a first text field and a second text field, before step S105, it further includes: When the first text field and the second text field are short text fields, determine the Jaro-Winkler distance between the first text field and the second text field, and perform Soundex encoding on the first text field and the second text field to obtain the first encoding and the second encoding. Based on the Jaro-Winkler distance, the first encoding and the second encoding, determine the field comprehensive similarity score between the first text field and the second text field. When the field comprehensive similarity score is greater than the similarity score threshold, retain the text field with the updated timestamp in the first text field and the second text field.

[0073] Among them, the Jaro-Winkler distance is an enhanced version of the Jaro distance, giving extra rewards to the same characters at the beginning of the string, which is very suitable for the detection of fields such as names and place names. Soundex is a phonetic algorithm that converts a string into a code consisting of the first letter and three digits, representing its pronunciation. For example, the Soundex encodings of "Robert" and "Rupert" are both R163. This step is to capture the redundancy of homophonic different characters. For example, if the similarity between "张三" and "章三" is >0.9, they are merged, and only the field with the latest timestamp is retained.

[0074] When the first text field and the second text field are long text fields, input the first text field and the second text field into the Sentence-BERT model to obtain the first text vector and the second text vector, and determine the cosine similarity between the first text vector and the second text vector. When the cosine similarity is greater than the similarity threshold, retain the text field with the updated timestamp in the first text field and the second text field.

[0075] Among them, the Sentence-BERT model converts each text record (such as a product description) into a high-dimensional vector (such as 384-dimensional or 768-dimensional). This vector is called a "sentence vector" or "embedding", which captures the deep semantic information of the text. Cosine similarity focuses on the direction of the vector rather than its magnitude. For text vectors, it is usually between [0, 1]. The closer the value is to 1, the more similar the semantics are.

[0076] It should be noted that since repeated or redundant text refers to almost identical semantics, the similarity threshold needs to be set to a high value, such as 0.95.

[0077] In summary, the data cleaning and repair method proposed in this invention improves the accuracy of anomaly detection, increasing the F1-score from 78% to 94% and the recall rate for misspelling identification to >92%. Simultaneously, it reduces missing value imputation error by 60%, reduces manual rule writing by 90%, and shortens the cleaning cycle from days to hours. Furthermore, it enables transfer learning to adapt the model to new data sources within one hour.

[0078] On the other hand, embodiments of the present invention also provide a data cleaning and repair apparatus, such as... Figure 4 As shown, the data cleaning and repair device 400 includes: The data to be cleaned acquisition unit 401 is used to acquire data to be cleaned, which includes numeric fields and text fields. The preliminary detection unit 402 is used to perform outlier detection on numerical fields based on isolated forest to obtain a first detection result, and to perform outlier detection on text fields based on association rule mining algorithm to obtain a second detection result; The secondary detection unit 403 is used to perform anomaly detection on the text field based on the semantic detection model to obtain a third detection result when the first detection result and / or the second detection result are abnormal. The comprehensive detection unit 404 is used to input the first detection result, the second detection result and the third detection result into the meta-learner for dynamic weighted voting to obtain the comprehensive detection result; Repair unit 405 is used to intelligently repair the data to be cleaned based on an AI model when the comprehensive detection result is abnormal, and obtain repaired data. The model optimization unit 406 is used to acquire optimized data based on manual optimization of the repair data, incrementally train the AI ​​model based on the optimized data to obtain the optimized AI model, and perform subsequent data repair based on the optimized AI model.

[0079] The data cleaning and repair apparatus 400 provided in the above embodiments can realize the technical solutions described in the above data cleaning and repair method embodiments. The specific implementation principles of each module or unit can be found in the corresponding content in the above data cleaning and repair method embodiments, and will not be repeated here.

[0080] Those skilled in the art will understand that all or part of the processes of the methods described in the above embodiments can be implemented by a computer program instructing related hardware (such as a processor, controller, etc.), and the computer program can be stored in a computer-readable storage medium. The computer-readable storage medium may be a disk, optical disk, read-only memory, or random access memory, etc.

[0081] The above provides a detailed description of the data cleaning and repair method and apparatus provided by the present invention. Specific examples have been used to illustrate the principles and implementation methods of the present invention. The description of the above embodiments is only for the purpose of helping to understand the method and core ideas of the present invention. At the same time, for those skilled in the art, there will be changes in the specific implementation methods and application scope based on the ideas of the present invention. Therefore, the content of this specification should not be construed as a limitation of the present invention.

Claims

1. A data cleaning and repair method, characterized in that, include: Obtain the data to be cleaned, which includes numeric fields and text fields; An outlier detection is performed on the numerical field based on an isolated forest algorithm to obtain a first detection result, and an outlier detection is performed on the text field based on an association rule mining algorithm to obtain a second detection result. When the first detection result and / or the second detection result is abnormal, the text field is subjected to anomaly detection based on the semantic detection model to obtain a third detection result; The first detection result, the second detection result, and the third detection result are input into the meta-learner for dynamic weighted voting to obtain a comprehensive detection result. When the comprehensive detection result is abnormal, the data to be cleaned is intelligently repaired based on the AI ​​model to obtain repaired data. The process involves obtaining optimized data based on manual adjustments to the repaired data, incrementally training the AI ​​model using the optimized data to obtain an optimized AI model, and then performing subsequent data repair based on the optimized AI model.

2. The data cleaning and repair method according to claim 1, characterized in that, The outlier detection of the numerical field based on isolated forest to obtain a first detection result includes: Input the numerical field into the isolated forest to obtain the anomaly score; Determine the upper quartile, lower quartile, and interquartile range corresponding to the numerical field, and determine the dynamic anomaly boundary based on the upper quartile, lower quartile, and interquartile range; When the abnormal score is not located at the dynamic abnormal boundary, the first detection result is determined to be abnormal.

3. The data cleaning and repair method according to claim 1, characterized in that, The text field includes multiple text subfields; therefore, the outlier detection performed on the text field using the association rule mining algorithm to obtain a second detection result includes: Determine whether the multiple text subfields satisfy the association rules determined by the association rule mining algorithm. If they do not satisfy the association rules, then determine that the second detection result is abnormal.

4. The data cleaning and repair method according to claim 1, characterized in that, The semantic detection model is the BERT model and the CRF sequence labeling model.

5. The data cleaning and repair method according to claim 1, characterized in that, The data to be cleaned includes continuous data and categorized data, and the AI ​​model includes the LightGBM multi-classification model; the intelligent repair of the data to be cleaned based on the AI ​​model includes: K similar data points of the continuous data are identified, and the weight of each similar data point is determined based on the similarity. The K similar data points are then weighted and averaged to obtain the target data. The target data is used as the missing value of the data to be cleaned. The categorized data is input into the LightGBM multi-classification model to obtain classification prediction values, and these classification prediction values ​​are used as the missing values ​​of the data to be cleaned.

6. The data cleaning and repair method according to claim 1, characterized in that, The AI ​​model also includes an anomaly correction model; the intelligent repair of the data to be cleaned based on the AI ​​model includes: When there are outliers in the numerical field, the outliers in the data to be cleaned are corrected based on the business logic or calculation relationship of the numerical field. When an outlier exists in the text field, the outlier is input into the anomaly correction model to obtain multiple candidate correction values. The context information of the text field is also obtained. Based on the context information, the target correction value among the multiple candidate correction values ​​is determined. The outlier in the data to be cleaned is corrected based on the target correction value.

7. The data cleaning and repair method according to claim 1, characterized in that, The incremental training of the AI ​​model based on the optimized data to obtain an optimized AI model includes: Obtain the frequency of occurrence of error types in the repaired data, and determine the training weights of the optimized data based on the frequency of occurrence; The AI ​​model is incrementally trained based on the training weights and the optimization data to obtain an optimized AI model.

8. The data cleaning and repair method according to any one of claims 1-7, characterized in that, Before incrementally training the AI ​​model based on the optimized data, the method further includes: Identify the sensitive fields in the optimized data and add noise to the sensitive fields that meets local differential privacy requirements.

9. The data cleaning and repair method according to claim 1, characterized in that, The data to be cleaned includes a first text field and a second text field; therefore, before the intelligent repair of the data to be cleaned based on the AI ​​model, the following is also included: When the first text field and the second text field are short text fields, the Jaro-Winkler distance between the first text field and the second text field is determined, and Soundex encoding is performed on the first text field and the second text field to obtain the first encoding and the second encoding. Based on the Jaro-Winkler distance, the first encoding and the second encoding, the comprehensive field similarity score of the first text field and the second text field is determined. When the comprehensive field similarity score is greater than the similarity score threshold, the text field with the timestamp updated in the first text field and the second text field is retained. When the first text field and the second text field are long text fields, the first text field and the second text field are input into the Sentence-BERT model to obtain the first text vector and the second text vector, and the cosine similarity between the first text vector and the second text vector is determined. When the cosine similarity is greater than the similarity threshold, the text field with the timestamp updated in the first text field and the second text field is retained.

10. A data cleaning and repair device, characterized in that, include: A data to be cleaned acquisition unit is used to acquire data to be cleaned, which includes numeric fields and text fields. The preliminary detection unit is used to perform outlier detection on the numerical field based on the isolated forest to obtain a first detection result, and to perform outlier detection on the text field based on the association rule mining algorithm to obtain a second detection result; A secondary detection unit is used to perform anomaly detection on the text field based on a semantic detection model to obtain a third detection result when the first detection result and / or the second detection result are abnormal. The comprehensive detection unit is used to input the first detection result, the second detection result, and the third detection result into the meta-learner for dynamic weighted voting to obtain a comprehensive detection result; The repair unit is used to intelligently repair the data to be cleaned based on an AI model when the comprehensive detection result is abnormal, and obtain repaired data. The model optimization unit is used to acquire optimized data based on manual optimization of the repaired data, incrementally train the AI ​​model based on the optimized data to obtain an optimized AI model, and perform subsequent data repair based on the optimized AI model.

Images