A data cleaning processing method based on dynamic rule configuration

Abstract

The application discloses a kind of based on dynamic rule configuration's data cleaning processing method, belong to data cleaning technical field, including based on sliding window real-time calculation data stream's mean, standard deviation, quantile, skewness and kurtosis;Obtain pre-defined cleaning rule skeleton, skeleton includes static cleaning logic and dynamic parameter placeholder and its binding mapping relationship with statistical characteristics;Normality test is carried out according to skewness and kurtosis, and when data obeys normal distribution, dynamic threshold is generated with mean and standard deviation, and when deviating from normal, dynamic threshold is generated with quantile and quartile range;Dynamic parameter value is filled into skeleton to generate instantiated cleaning rule and execute cleaning;According to the false alarm rate and the miss rate of cleaning result, construct loss function, adjust preset coefficient online and feedback to subsequent window;The application uses the above-mentioned one based on dynamic rule configuration's data cleaning processing method, realizes the adaptive switching of cleaning rule and coefficient self-optimization, effectively reduces false alarm rate and miss rate.

Description

Technical Field

[0001] This invention relates to the field of data cleaning technology, and in particular to a data cleaning processing method based on dynamic rule configuration. Background Technology

[0002] In the field of Industrial Internet of Things (IIoT), sensor data acquisition and processing are fundamental to equipment condition monitoring and fault early warning. Taking the vibration monitoring of wind farm generator gearboxes as an example, vibration sensors collect data at a frequency of thousands of times per second, and the data stream needs to be cleaned before it can be used in subsequent fault diagnosis algorithms.

[0003] Currently, the widely used cleaning method is outlier filtering based on fixed thresholds. For example, a vibration amplitude upper limit is set at 80 mm / s; any data point exceeding this threshold is marked as an anomaly and removed. The shortcomings of this method have been fully exposed in actual production: the vibration level of the same wind turbine varies significantly under different seasons and load conditions. In winter, at low temperatures, the overall foundation vibration amplitude may increase by 20% to 30%, and normal vibration under high load conditions may exceed the anomaly threshold under low load conditions. Fixed thresholds lead to a large amount of normal data being mistakenly deleted in winter, while some genuine anomalies are missed in summer due to relatively high thresholds.

[0004] To address this issue, some sites employ manual threshold adjustments based on time periods, with maintenance personnel setting different thresholds for different seasons based on experience. However, this approach relies on manual experience, resulting in delayed responses and an inability to handle short-term data drift caused by rapid load changes. Other solutions introduce simple adaptive thresholds, such as moving averages of historical data plus or minus a fixed factor of the standard deviation. However, these methods assume that the data follows or approximately follows a normal distribution. When the data distribution becomes skewed or heavy-tailed due to changes in equipment status, the threshold calculation under this single distribution assumption also fails.

[0005] The essence of the above problem lies in the fact that the statistical distribution of sensor data streams in industrial fields is not constant; its central tendency, dispersion, and even distribution pattern all drift over time. Existing cleaning rules, whether fixed thresholds or simple adaptive thresholds, cannot dynamically match this multi-dimensional change in data distribution at the rule level. Summary of the Invention

[0006] The purpose of this invention is to provide a data cleaning and processing method based on dynamic rule configuration to solve the problems in industrial IoT scenarios where the statistical distribution of sensor data streams drifts due to environmental changes or equipment operating condition fluctuations, leading to increased false deletion or false negative rates of fixed threshold cleaning rules, and the performance degradation of adaptive threshold calculation methods under a single distribution assumption after changes in data distribution.

[0007] To achieve the above objectives, this invention provides a data cleaning and processing method based on dynamic rule configuration, comprising the following steps: Step 1: Acquire the data stream in real time, and calculate the mean, standard deviation, first quartile, third quartile, skewness, and kurtosis of the data within the current window based on a sliding window; Step 2: Obtain the predefined cleaning rule skeleton. The cleaning rule skeleton includes static cleaning logic and dynamic parameter placeholders, as well as the binding mapping relationship between dynamic parameter placeholders and statistical features. Step 3: Perform a normality test on the current window data based on skewness and kurtosis; if the test result indicates that the data follows a normal distribution, then generate dynamic parameter values ​​using the mean and standard deviation as variables and the currently valid first preset coefficient as the multiplier; if the test result indicates that the data deviates from a normal distribution, then generate dynamic parameter values ​​using the first quartile and the third quartile as variables and the currently valid second preset coefficient as the multiplier. Step 4: Fill the dynamic parameter values ​​into the corresponding placeholders in the cleaning rule skeleton to generate instantiated cleaning rules; Step 5: Perform cleaning operations on the data in the current window using instantiated cleaning rules to obtain the cleaning results; Step 6: Calculate the false alarm rate and false negative rate based on the cleaning results, construct the loss function, and use the loss function to adjust the first preset coefficient or the second preset coefficient online. The adjusted coefficient is used as the currently effective first preset coefficient or the second preset coefficient in Step 3 of the subsequent window.

[0008] Preferably, the incremental calculation algorithm is used to calculate the mean, standard deviation, first quartile, third quartile, skewness and kurtosis in step one.

[0009] Preferably, the normality test in step three includes: based on skewness and kurtosis Calculate the test statistic ,in, The number of data points in the current sliding window. If the condition is met, the data is determined to deviate from a normal distribution; otherwise, the data is determined to follow a normal distribution. The significance level is 0.05 and the critical value of the chi-square distribution with 2 degrees of freedom is 0.05.

[0010] Preferably, the dynamic parameter values ​​generated using the mean and standard deviation as variables and the currently effective first preset coefficient as the multiplier include: the upper bound of the dynamic threshold. Dynamic threshold lower bound ,in The mean, Standard deviation This is the first preset coefficient.

[0011] Preferably, the dynamic parameter values ​​generated using the first and third quartiles as variables and the currently valid second preset coefficient as the multiplier include: abnormal lower bound. upper bound of anomalies ,in It is the first quartile. It is the third quartile. Interquartile range, This is the second preset coefficient.

[0012] Preferably, step six includes: periodically calculating the false alarm rate of the cleaning results. and underreporting rate Define the loss function ,in Weighting based on business preferences; adjusting the first preset coefficient using online optimization methods. Or the second preset coefficient ,coefficient The update formula is: ; in, For the first The first preset coefficient after the adjustment. For the first The first preset coefficient after the adjustment. For learning rate, For small perturbations, the updated coefficients are limited to a preset safe range.

[0013] Preferably, in step four, before filling the dynamic parameter value into the corresponding placeholder, the method further includes: verifying whether the dynamic parameter value is within a preset reasonable range; if the dynamic parameter value exceeds the preset reasonable range, then the valid parameter value from the previous moment or the preset default parameter is used instead.

[0014] Preferably, the preset reasonable range is determined based on the distribution range of cleaning parameters in historical normal data, or set according to the physical range of the sensor.

[0015] Therefore, the data cleaning and processing method based on dynamic rule configuration described above, as used in this invention, has the following beneficial effects: (1) The present invention calculates the mean, standard deviation, quantile, skewness and kurtosis of the data stream in real time through a sliding window, and dynamically generates the parameter values ​​in the cleaning rules accordingly, so that the cleaning threshold can be adjusted in real time with the changes in data distribution, avoiding the failure of fixed rules due to data drift, and reducing the maintenance cost of manually calibrating the threshold repeatedly.

[0016] (2) This invention introduces a normality test based on skewness and kurtosis. When the data follows a normal distribution, the threshold is generated by a calculation method based on the mean and standard deviation. When the data deviates from the normal distribution, the threshold is automatically generated by a calculation method based on the interquartile range and interquartile distance. This solves the problem of performance degradation of a single calculation method after the data distribution changes.

[0017] (3) This invention constructs a loss function by utilizing the false alarm rate and false alarm rate of the cleaning results and adjusts the preset coefficients online, so that the cleaning performance is continuously optimized during operation. The influence of the initial coefficient setting deviation on the final effect decreases over time, thus realizing the closed-loop self-optimization of the cleaning rules.

[0018] The technical solution of the present invention will be further described in detail below with reference to the accompanying drawings and embodiments. Attached Figure Description

[0019] Figure 1 This is a flowchart of a data cleaning and processing method based on dynamic rule configuration according to the present invention. Detailed Implementation

[0020] The following detailed description of embodiments of the invention provided in the accompanying drawings is not intended to limit the scope of the claimed invention, but merely illustrates selected embodiments of the invention. All other embodiments obtained by those skilled in the art based on the embodiments of the invention without inventive effort are within the scope of protection of the invention.

[0021] Example This invention provides a data cleaning and processing method based on dynamic rule configuration, applicable to online cleaning scenarios of sensor data streams in the Industrial Internet of Things, such as real-time cleaning of vibration data from wind farm generator gearboxes. This embodiment takes the vibration amplitude data collected by the vibration sensor of the gearbox in the wind farm as an example. The sensor sampling frequency is 1000 times per second. The data is transmitted to the edge computing node for online cleaning. The cleaned data is used by the downstream fault diagnosis algorithm.

[0022] like Figure 1 As shown, it includes the following steps: Step 1: Acquire the data stream in real time and calculate the mean, standard deviation, first quartile, third quartile, skewness, and kurtosis of the data within the current window based on a sliding window.

[0023] In the edge computing node, a fixed-size sliding window is maintained in memory, with a window size of 1000 data points, corresponding to 1 second of sampling data. Each time a new data point is collected, the window slides forward one position, and the oldest data point is removed from the window. After each window slide, based on the current 1000 data points within the window, six statistical features are calculated: mean, standard deviation, first quartile, third quartile, skewness, and kurtosis. To meet the real-time requirement of 1000 samplings per second, the calculation of these statistical features uses an incremental calculation algorithm. Specifically, the mean and standard deviation use the Welford incremental algorithm, which only needs to update a few intermediate variables to obtain the new mean and variance when adding a new data point or removing an old data point, without needing to traverse all 1000 data points each time; the first quartile and third quartile use the P-value algorithm. 2 The algorithm performs incremental estimation, and skewness and kurtosis are calculated by incrementally updating the second, third, and fourth moments of the data. The combination of the above incremental algorithms ensures that the amount of statistical feature calculation remains essentially constant after each window slide, and does not increase with the growth of the window size. This guarantees that the requirements for real-time processing can still be met even with the limited computing resources of edge computing nodes.

[0024] Step 2: Obtain the predefined cleaning rule skeleton. The cleaning rule skeleton includes static cleaning logic and dynamic parameter placeholders, as well as the binding mapping relationship between dynamic parameter placeholders and statistical features.

[0025] During the initialization and startup phase, edge computing nodes load a predefined cleaning rule skeleton from the configuration center. The cleaning rule skeleton is stored in JSON format and contains three parts: the first part is the static cleaning logic, defined in this embodiment as "outlier removal," which involves marking and filtering out data points exceeding the threshold range; the second part is dynamic parameter placeholders, containing two placeholders in this embodiment, corresponding to the upper and lower thresholds respectively, and expressed in JSON as "...". "and" The first part serves as a placeholder identifier; the second part is the binding mapping relationship, specifying that the dynamic parameter values ​​corresponding to the upper and lower thresholds are generated in step three, i.e., establishing the mapping between placeholders and statistical features. The structure of this cleaning rule skeleton is fixed, but the parameter values ​​are in a state of being to be filled before the rule is instantiated. They can change dynamically according to the data features during the operation without modifying the skeleton structure itself.

[0026] Step 3: Perform a normality test on the current window data based on skewness and kurtosis. If the test result indicates that the data follows a normal distribution, generate dynamic parameter values ​​using the mean and standard deviation as variables and the currently valid first preset coefficient as the multiplier. If the test result indicates that the data deviates from a normal distribution, generate dynamic parameter values ​​using the first quartile and the third quartile as variables and the currently valid second preset coefficient as the multiplier. This step is performed after each statistical feature calculation is completed in Step 1.

[0027] Obtain the skewness from the calculation results of step one. and kurtosis According to skewness and kurtosis Calculate the Jarque-Bera test statistic The calculation formula is as follows: ; in, In this embodiment, the number of data points within the current sliding window is [number]. .

[0028] The calculated J value was compared with the critical value of the chi-square distribution with 2 degrees of freedom at a significance level of 0.05. For comparison, the critical value is approximately 5.991. If... If the current window data is determined to follow a normal distribution, then the first parameter generation method is adopted: using the mean and standard deviation as variables, and the currently valid first preset coefficient... As a multiplier, generate a dynamic threshold upper bound. Dynamic threshold lower bound ,in The mean of the data in the current window. This is the standard deviation. If... If the current window data deviates from a normal distribution, for example, during unit start-up or shutdown or under conditions of drastic load changes, vibration data often exhibits a skewed or heavy-tailed distribution. In this case, a second parameter generation method is used: using the first and third quartiles as variables and the currently valid second preset coefficients. As a multiplier, it generates an abnormal lower bound. upper bound of anomalies ,in It is the first quartile. It is the third quartile. The interquartile range. The first preset coefficient is used during the initial system run. The initial value is 3, and the second preset coefficient is... The initial value is 1.5; during system operation, and The current valid value is updated periodically by step six.

[0029] Step 4: Fill the dynamic parameter values ​​into the corresponding placeholders in the cleaning rule skeleton to generate instantiated cleaning rules.

[0030] Before entering the dynamic parameter values, a verification step can be added: the dynamic parameter values ​​generated in step three are compared with a preset reasonable range to determine whether the dynamic parameter values ​​are distorted due to instantaneous sensor failure or abnormal data spikes. The preset reasonable range can be set according to the physical range of the sensor. In this embodiment, the measurement range of the vibration sensor is 0 to 100 mm / s, so the preset reasonable ranges for both the upper and lower thresholds are set to [0, 100]. The preset reasonable range can also be derived statistically from the distribution range of cleaning parameters in historical normal data. If the dynamic parameter value falls within the preset reasonable range, the verification passes, and the value of the upper limit of the dynamic threshold replaces the "" in the cleaning rule skeleton. "Placeholder, replace with the value of the lower bound of the dynamic threshold" Placeholder; if the dynamic parameter value exceeds the preset reasonable range, the verification fails, the generated dynamic parameter value is discarded, and the valid parameter value from the previous moment or the system's preset default parameter value is used instead. After verification, the placeholder in the cleaning rule skeleton is replaced with a specific value, resulting in an instantiated cleaning rule that can be directly executed in the current window.

[0031] Step 5: Use the instantiated cleaning rules to perform cleaning operations on the data in the current window and obtain the cleaning results.

[0032] The specific process of the cleaning operation is as follows: Iterate through the 1000 data points within the current sliding window. For each data point, compare its vibration amplitude with the upper and lower thresholds defined in the instantiated cleaning rules. If the vibration amplitude of a data point is greater than the upper threshold or less than the lower threshold, the data point is marked as abnormal and removed, not entering the downstream fault diagnosis algorithm. If the vibration amplitude of a data point is between the upper and lower thresholds, the data point is marked as normal, retained, and transmitted to the downstream fault diagnosis system. The cleaning result, i.e., the record of whether each data point is marked as normal or abnormal, is stored in a local cache for use in step six.

[0033] Step 6: Calculate the false alarm rate and false negative rate based on the cleaning results, construct the loss function, and use the loss function to adjust the first preset coefficient or the second preset coefficient online. The adjusted coefficient is used as the currently effective first preset coefficient or the second preset coefficient in Step 3 of the subsequent window.

[0034] Step six is ​​executed periodically. The execution cycle can be set according to actual needs; in this embodiment, it is set to be executed once per hour. Each time it is executed, the cleaning results of all windows from the past hour are retrieved from the local cache. By comparing historical maintenance records and manually labeled fault tags, the accuracy of the cleaning determination for each data point is determined. False alarm rate. The calculation method is: the number of samples incorrectly marked as abnormal by the cleaning rules divided by the total number of actual normal samples; false negative rate. The calculation method is: the number of abnormal samples missed by the cleaning rules divided by the total number of actual abnormal samples.

[0035] Define loss function ,in The business preference weight ranges from 0 to 1. In this embodiment... A value of 0.5 indicates that false alarms and false negatives have equally significant impacts on the system.

[0036] Taking the currently effective first preset coefficient k as an example, calculate the loss function value under small perturbations: For small perturbations, take the perturbation step size. Calculate separately and Substitute into the update formula: ; in For the first The first preset coefficient after the adjustment. For the first The first preset coefficient after the adjustment. In this embodiment, the learning rate is... Take 0.05.

[0037] Updated The values ​​are limited to a preset safety range [1.5, 5] to prevent the coefficients from deviating from a reasonable range due to accidental fluctuations. If the data in the current operating phase mainly follows a normal distribution, the first preset coefficient is updated. If the data in the current operating phase mainly deviates from a normal distribution, then update the second preset coefficient. , The update method and Similarly, the safe range for α can be set to, for example, [0.5, 3]. The updated coefficient value is used as the currently valid preset coefficient, overriding the previously used coefficient value, and will be used in step three of the subsequent window.

[0038] Through the iterative execution of steps one through six, this embodiment achieves adaptive adjustment of cleaning rule parameters as vibration data distribution changes. This includes two aspects: First, during each window sliding, the data distribution pattern is automatically determined based on skewness and kurtosis, automatically switching between calculation methods based on mean and standard deviation and those based on quartiles and interquartile ranges, ensuring the cleaning rules match changes in data distribution. Second, by periodically collecting cleaning results, calculating false alarm and false negative rates, constructing a loss function, and optimizing coefficients online, the first and second preset coefficients are continuously adjusted to reduce losses during operation. The impact of initial coefficient setting deviations on the final cleaning effect decreases over time, forming a closed-loop self-optimization. In the scenario of vibration monitoring of wind farm generator gearboxes, backtesting using three months of historical data shows that, compared to traditional cleaning methods with a fixed 3σ threshold, this embodiment reduces the false alarm rate from 32% to 8.5% and the false negative rate from 11% to 4.2%, significantly improving the quality of cleaning data and providing a more reliable data foundation for downstream fault diagnosis.

[0039] Therefore, the present invention adopts the above-mentioned data cleaning and processing method based on dynamic rule configuration, which realizes adaptive switching of cleaning rules and self-optimization of coefficients, effectively reducing false alarm rate and false negative rate.

[0040] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and not to limit them. Although the present invention has been described in detail with reference to preferred embodiments, those skilled in the art should understand that modifications or equivalent substitutions can still be made to the technical solutions of the present invention, and these modifications or equivalent substitutions cannot cause the modified technical solutions to deviate from the spirit and scope of the technical solutions of the present invention.

Claims

1. A data cleaning and processing method based on dynamic rule configuration, characterized in that, Includes the following steps: Step 1: Acquire the data stream in real time, and calculate the mean, standard deviation, first quartile, third quartile, skewness, and kurtosis of the data within the current window based on a sliding window; Step 2: Obtain the predefined cleaning rule skeleton. The cleaning rule skeleton includes static cleaning logic and dynamic parameter placeholders, as well as the binding mapping relationship between dynamic parameter placeholders and statistical features. Step 3: Perform a normality test on the current window data based on skewness and kurtosis; If the test result indicates that the data follows a normal distribution, then the mean and standard deviation are used as variables, and the currently valid first preset coefficient is used as the multiplier to generate dynamic parameter values; if the test result indicates that the data deviates from a normal distribution, then the first quartile and the third quartile are used as variables, and the currently valid second preset coefficient is used as the multiplier to generate dynamic parameter values. Step 4: Fill the dynamic parameter values ​​into the corresponding placeholders in the cleaning rule skeleton to generate instantiated cleaning rules; Step 5: Perform cleaning operations on the data in the current window using instantiated cleaning rules to obtain the cleaning results; Step 6: Calculate the false alarm rate and false negative rate based on the cleaning results, construct the loss function, and use the loss function to adjust the first preset coefficient or the second preset coefficient online. The adjusted coefficient is used as the currently effective first preset coefficient or the second preset coefficient in Step 3 of the subsequent window.

2. The data cleaning and processing method based on dynamic rule configuration according to claim 1, characterized in that: In step one, the mean, standard deviation, first quartile, third quartile, skewness, and kurtosis are calculated using an incremental calculation algorithm.

3. The data cleaning and processing method based on dynamic rule configuration according to claim 1, characterized in that: Step three, the normality test, includes: based on skewness and kurtosis Calculate the test statistic ,in, The number of data points in the current sliding window. If the condition is met, the data is determined to deviate from a normal distribution; otherwise, the data is determined to follow a normal distribution. The significance level is 0.05 and the critical value of the chi-square distribution with 2 degrees of freedom is 0.

05.

4. The data cleaning and processing method based on dynamic rule configuration according to claim 3, characterized in that: Using the mean and standard deviation as variables and the currently effective first preset coefficient as the multiplier, the generated dynamic parameter values ​​include: the upper bound of the dynamic threshold. Dynamic threshold lower bound ,in The mean, Standard deviation This is the first preset coefficient.

5. The data cleaning and processing method based on dynamic rule configuration according to claim 4, characterized in that: Using the first and third quartiles as variables and the currently valid second preset coefficient as the multiplier, the generated dynamic parameter values ​​include: abnormal lower bounds. upper bound of anomalies ,in It is the first quartile. It is the third quartile. Interquartile range, This is the second preset coefficient.

6. The data cleaning and processing method based on dynamic rule configuration according to claim 5, characterized in that, Step six includes: periodically calculating the false alarm rate of the cleaning results. and underreporting rate Define the loss function ,in Weighting based on business preferences; adjusting the first preset coefficient using online optimization methods. Or the second preset coefficient ,coefficient The update formula is: ； in, For the first The first preset coefficient after the adjustment. For the first The first preset coefficient after the adjustment. For learning rate, For small perturbations, the updated coefficients are limited to a preset safe range.

7. The data cleaning and processing method based on dynamic rule configuration according to claim 1, characterized in that, In step four, before filling the dynamic parameter value into the corresponding placeholder, it also includes: verifying whether the dynamic parameter value is within a preset reasonable range; if the dynamic parameter value exceeds the preset reasonable range, then the valid parameter value from the previous moment or the preset default parameter is used instead.

8. The data cleaning and processing method based on dynamic rule configuration according to claim 7, characterized in that: The preset reasonable range is determined based on the distribution range of cleaning parameters in historical normal data, or set according to the physical range of the sensor.

Images