Data cleaning method based on multiple interpolation

doi:10.21203/rs.3.rs-4866672/v1

Download PDF

Research Article

Data cleaning method based on multiple interpolation

https://doi.org/10.21203/rs.3.rs-4866672/v1

This work is licensed under a CC BY 4.0 License

Version 1

posted

You are reading this latest preprint version

To ensure the availability and data quality of integrated and fused data, develop data cleaning methods, and achieve real-time processing of missing data values, this research project studies various methods for filling missing data values. By understanding the principles of each method, a multi-method data missing value filling module is developed, and a multiple interpolation missing value method based on random forest method is proposed. Using 500 sample data from a 250MW gas turbine in 2013 for simulation and comparison tests, in order to test the calculation errors of the four filling methods under different missing rates, five rows of sample data were randomly emptied at missing rates of 25%, 50%, 75%, and 90%. The experimental results compared the mean absolute error (MAE), mean squared error (MSE), root mean squared error (RMSE), running time, and maximum deviation. Compared with traditional regression filling methods, the multiple interpolation method has an accuracy improvement of more than 90% in terms of MAE, MSE, RMSE, running time, and maximum deviation. Due to the complexity of the multiple interpolation algorithm, for 500 sample data, the running processing time is 20s longer. Subsequently, appropriate data cleaning methods can be selected based on actual background conditions.

CCS CONCEPTS

Computing methodologies

Machine learning

Machine learning algorithms

Data cleaning plays a pivotal role in data quality control and is an important means to ensure the quality of subsequent modeling data. Data cleaning is mainly studied in the fields of data quality control and data mining. The research on data cleaning first appeared in the United States. This research is a data cleaning of the entire American social insurance information [1]. With the development of economy and Internet plus technology, data cleaning has attracted the attention of many scholars and promoted the research on data cleaning related technologies. Many domestic and foreign scholars have carried out missing data cleaning, as shown in Table 1.

Table 1

Current Research on Data Cleaning
Author	Method	Advantages	Disadvantage
Bahram [2]	Multiple imputation using Gaussian Processes	Can capture complex dependencies and uncertainties in the data. Provides multiple plausible imputations, preserving uncertainty.	Computationally intensive, especially for large datasets. Requires careful tuning of hyperparameters.
Gan Q [3]	Imputation Method for Batch Process Monitoring Dataset	Tailored for batch process data, which can have unique characteristics such as sequential dependencies.	May not generalize well to other types of datasets. Effectiveness depends on the specific characteristics of batch process data.
Ming-Yue D [4]	A second-order calibration algorithm	Addresses higher-order interactions in calibration tasks, potentially improving accuracy.	Complexity may increase with higher-order terms, requiring more computational resources.
Hufsa K [5]	Deep convolutional neural network	Powerful for extracting hierarchical features from data. Can handle large datasets effectively.	Requires large amounts of labeled data for training. Prone to overfitting without proper regularization.
Hufsa K [6]	Shorter interval selection driven by Fuzzy C-Means clustering	Adaptive approach to interval selection, potentially improving model performance.	Sensitivity to the choice of clustering parameters. Interpretability of fuzzy clustering results can be challenging.
Mausor FH [7]	Iterative Fuzzy C Means, Fuzzy Silhouette, and Imputation	Integrates clustering and imputation, leveraging both for better handling of missing data.	Complexity increases with iterative processes. Performance heavily dependent on clustering quality.
Hu P [8]	Multilayer Perceptron and Momentum Gradient Descent	Effective for nonlinear relationships and large-scale datasets. Momentum helps accelerate convergence.	Susceptible to local minima. Requires tuning of momentum and learning rate parameters.
Zhang X [9]	A Matrix Completion Method	Explicitly models missing values in a matrix, leveraging relationships between variables.	Limited by assumptions of matrix structure. Performance may degrade with sparse or noisy data.
Vishal H B [10]	Nullify the Missing Values before Imputation	Simple approach, potentially preserving original data structure.	Ignores potentially valuable information in missing data patterns. May lead to biased imputations.
Júnior [12]	Efficient incremental missing data imputation method for evolving neo-fuzzy network	Tailored for evolving datasets. Efficiently updates imputations as new data arrives.	Specific to evolving datasets, may not generalize to static datasets.
Dehghani A [13]	Proposed the Multi-Pass Neighbor Sorting Algorithm (MPN Algorithm)	Novel approach for imputation, potentially offering unique insights into neighbor-based methods.	Performance and scalability depend on sorting efficiency. General applicability across datasets may vary.
Missing data includes two types: attribute missing and tuple missing. Attribute missing includes complete missing, partial missing, and random missing, while tuple missing refers to missing values in some attributes. The main idea of filling is to find the spatiotemporal data attributes between data and then fill the data according to time series data. For missing values, the most primitive method is deletion. However, due to the large amount of data, directly deleting missing values will delete many relevant and useful data, resulting insignificant data loss. Therefore, reasonable values are generally used to fill in missing values. Common interpolation methods include:

1) mean interpolation, including mean interpolation, mode interpolation, minimum value interpolation, etc.

2) regression interpolation. Ming-Yue D et al. [4] proposed a new second-order correction algorithm for missing values. The algorithm combines the idea of treating multiple linear regression as a set of separate univariate linear regression and iterative least squares principles. This algorithm can directly handle missing values in three-dimensional data sets and can directly process these data.

3) maximum likelihood estimation (LE), which infers unknown parameters from data distribution to maximize the probability of sample occurrence.

4) other methods, Dehghani A et al. [13] proposed a new missing value interpolation technique called KWGI, which utilizes the K-means algorithm based on gray correlation and a sample weighting mechanism. This method applies the K-means algorithm of grey correlation to all samples in the incomplete dataset, clusters similar samples together, and then generates appropriate weights based on grey correlation. The estimation of missing values is based on weighted average to reduce the influence of outliers and fuzzy samples. In the clustering and interpolation steps, a penalty mechanism is considered to reduce the similarity of fuzzy samples with high missing values, thereby improving the accuracy of clustering and interpolation. M K F et al. [14] proposes is a hybrid missing data imputation method called KI, which combines k-nearest neighbor and iterative imputation algorithms. The k-nearest neighbor algorithm is used to discover the optimal neighbor set for each missing record, automatically estimating the appropriate value of k to improve similarity. Then, an iterative interpolation method is used to interpolate the missing values of incomplete records based on the global correlation structure. Tlamelo E et al. [15] proposed and evaluated two methods: k-nearest neighbor and iterative imputation method based on random forest algorithm (missForest). The experiments used the Iris and New Power Plant datasets and were evaluated at missing rates of 5–20%, validating that missForest and k-nearest neighbor methods can successfully handle missing values.

In summary, the commonly used methods of mean and median interpolation for filling missing values are simple to implement but can lead to biases in data distribution. Regression interpolation methods can reflect the correlation between missing attributes and other attributes, but they must be prevented from overfitting. The concept of the KNN method is simple; it uses the relationship between data to estimate missing values. However, it is difficult to define similar standards and requires considering many subjective factors to measure similarity. Therefore, a multiple imputation method based on the random forest regression model (MI-RandomForest) is proposed, which calculates the predicted values of missing values according to the random forest regression model and then imputes them, repeatedly trains models, iteratively evaluates the model's performance, and achieves high accuracy.

The MICE (Multivariate Imputation by Chained Equations) method is a technique for handling missing values based on repeated simulations which can generate a complete dataset from an incomplete dataset containing missing values. Multiple imputation mainly involves using several imputed values to form a complete dataset, thereby completing the imputation of missing data. It requires constructing several imputed values based on the estimated prior distribution under certain conditions to determine their actual posterior distribution. Multiple imputation has significant practical applications because the constructed imputed estimates reflect the uncertainty of missing values to a certain extent, and multiple imputation estimates the joint distribution of parameters, reducing the influence of the prior distribution. The multiple imputation method is divided into three main steps:

Imputation: The missing observation rows in incomplete datasets are estimated and filled m times, with the filling values extracted from a certain distribution, Simulating random sampling does not include the uncertainty of model parameters. This step will generate m complete datasets;

Analysis: Conduct analysis on each of the m complete datasets separately;

Pooling: The process of combining results from multiple imputed datasets to produce a final statistical inference that takes into account the uncertainty introduced by the imputation. This method treats missing values as random samples, and the resulting statistical inference may be affected by the uncertainty of these missing values.

In each simulated dataset, missing data were imputed using the Random Forest regression method. Initially, missing data in the original dataset were imputed using the Random Forest method. Subsequently, statistical models were applied to each complete dataset for analysis, and the eligibility of a dataset was tested. These individual analysis results were consolidated into a single set of results. The quality of the imputation model was evaluated to assess the overall method's eligibility. The complete dataset was then output. The main idea is: first, make an initial guess for the missing values, such as filling them with the mean or median or 0. Then, sort the variables according to their missing rate, from small to large. Use random forest regression to fill the missing values of the variable with the smallest missing rate, and then iterate until the latest filling result does not change (changes very little) compared to the previous filling result. The steps of the random forest regression imputation method are shown as below:

ALGORITHM 1

Impute missing values with random forest.

Require: X an n × p matrix, stopping criterion γ

l. Make initial guess for missing values

2. k <- vector of sorted indices of columns in X w.r.t. increasing amount of missing values

3. while not γ do

4. X_old^imp <- store previously imputed matrix

5. for s in k do

6. Fit a random forest: y_obs^(s) ~ x_obs^(s)

7. Predict y_miss^(s), using x_miss^(s)

8. X_new^imp <- update imputed matrix, using predicted y_miss^(s)

9. end for

10. update γ.

11. end while

12. return the imputed matrix X^imp

3.1 Environment and dataset

The experimental environment used in this paper includes a Dell PC Core i7 processor, 32GB memory, and Windows 10 operating system, with Python as the programming language. A simulation comparison test was conducted on 500 sample data from a 250W gas turbine in 2013. The dataset contained 500 pieces of data and 39 variables. To test the calculation errors of the four aforementioned filling methods under different missing rates, the middle five rows of the sample data were randomly deleted at missing rates of 25%, 50%, 75%, and 90% respectively. The invalid data density of the dataset is displayed as shown in the Fig. 1.

3.2 Results and Comparative Analysis

To study the accuracy of interpolation methods, this section introduces four different evaluation metrics to measure the difference between various interpolated data and the original data, namely mean absolute error (MAE), mean squared error (MSE), root mean squared error (RMSE), and coefficient of determination R2, with their definitions as follows:

$$\:MAE=\:\frac{1}{n}\sum\:_{i=1}^{n}\left|\widehat{{y}_{i}}-{y}_{i}\right|$$

$$\:MSE=\:\frac{1}{n}\sum\:_{i=1}^{n}{\left(\widehat{{y}_{i}}-{y}_{i}\right)}^{2}$$

$$\:RMSE=\sqrt{\frac{1}{n}\sum\:_{i=1}^{n}{\left(\widehat{{y}_{i}}-{y}_{i}\right)}^{2}}\:$$

$$\:{R}^{2}=1-\frac{\sum\:_{i=1}^{n}{\left(\widehat{{y}_{i}}-{y}_{i}\right)}^{2}}{\sum\:_{i=1}^{n}{\left(\stackrel{-}{{y}_{i}}-{y}_{i}\right)}^{2}}$$

Where n is the sample size, y is the original data value of the sample, and $\:\widehat{{y}_{i}}$ is the imputed value of the sample. Among the three indicators, MAE, MSE, and RMSE are used to measure the deviation between the predicted and actual values, with smaller values generally indicating better performance. Unlike residuals. these metrics do not cancel each other out based on their sign. $\:{R}^{2}$ measures the proportion of the dependent variable's variation that can be explained by the independent variable, with a value range of 0–1. When it is close to 1, it indicates that the dependent variable is completely determined by the independent variable, thus a higher value indicates better model fitting performance.

Given the operating conditions of gas turbines and the reasons for missing values, the four traditional missing value imputation methods such as Mean value filling, Median filling, Regression imputation and KNN method filling mentioned above are compared with the MI-RandomForest method proposed in this paper. Table 2 shows the six evaluation metrics, MAE, MSE, RMSE, $\:{R}^{2}$, Time(s) and Maximum deviation results of the five imputation methods. From Table 2, it can be found that the trends of the three indicators, MAE, MSE, and RMSE, are similar. The MI-RandomForest method produces the smallest values for the three indicators and the largest corresponding values, close to 1, while the regression imputation method produces the largest values for the three indicators and the smallest corresponding values. Among the several imputation methods the median imputation has the shortest running time, while the multiple imputation method has a longer running time due to multiple imputations. If the data volume is large, the running time should also be used as a condition for measuring the algorithm. The maximum deviation of multiple imputation is the smallest, the maximum deviation of KNN method is relatively small, and the maximum error of regression method is the largest.

This study visualizes the experimental results using MSE and $\:{R}^{2}$ results as examples, as shown in Figs. 2 and Figs. 3, respectively.

Table 2

Current Research on Data Cleaning
Method	MAE	MSE	RMSE	$\:{R}^{2}$	Time(s)	Maximum deviation
Mean value filling	0.019	0.047	0.216	0.993	0.045	0.299
Median filling	0.016	0.036	0.189	0.993	0.031	0.288
Regression imputation [4]	0.030	0.198	0.445	0.923	2.749	0.401
KNN method filling [14]	0.0040	0.00568	0.0754	0.9988	0.047	0.127
Multiple imputation [15]	0.0022	0.00141	0.0375	0.9997	22.849	0.019

As can be seen from Fig. 2 and Fig. 3, by using multiple imputation methods, the missing values are filled using the random forest regression algorithm, and then the random forest model is used for fitting. resulting in the smallest mean squared error and the best fitting effect of the model. Therefore, it is recommended to use multiple imputation methods under complex operating conditions of gas turbines for better results.

Developing data cleaning methods to fill missing and omitted values, correct errors in real-time to ensure the availability and quality of integrated and fused data. This research focuses on various missing data imputation methods, understanding the principles of each method to develop a multi-method data missing value imputation module. The experimental results compared the mean absolute error (MAE), mean squared error (MSE), root mean squared error (RMSE), running time, and maximum deviation. Compared with traditional regression filling methods, the multiple interpolation method has an accuracy improvement of more than 90% in terms of MAE, MSE, RMSE, running time, and maximum deviation. Due to the complexity of the multiple interpolation algorithm, for 500 sample data, the running processing time is 20s longer. Subsequently, appropriate data cleaning methods can be selected based on actual background conditions.

Future work prospects: Due to the need to train multiple models for multiple imputations, the running time is relatively long. In the future, this part of the work can be optimized to improve training speed and reduce running time.

Author Contribution

Yiyang Liu conceived the mathematical models, implemented the methods, designed the numerical experiments, interpreted the results, and wrote the paper. Xiaomo Jiang, Peng Liu and Shilong Li supported this study and reviewed the final manuscript. All authors gave final approval for publication.

Larry E, Richards (1989) Book Review: Statistical Analysis with Missing Data [J]. J Mark Res 26(3):374–375
Bahram J, Daniel H, Pedro SL et al (2023) Gaussian processes for missing value imputation [J]. Knowl Based Syst, 273
Gan Q, Gong L, Hu D et al (2023) Sensors 23:21A Hybrid Missing Data Imputation Method for Batch Process Monitoring Dataset [J]
Ming-Yue D, Hai-Long W, Tong W et al (2024) PARAFACM: A second-order calibration algorithm for handling data with missing values [J]. Chemometrics and Intelligent Laboratory Systems, p 244105030
Hufsa K, Xizhao W, Han L (2022) Handling missing data through deep convolutional neural network [J]. Inf Sci 595:278–293
Hufsa K, Xizhao W, Han L (2021) Missing value imputation through shorter interval selection driven by Fuzzy C-Means clustering [J]. Comput Electr Eng, 93
Mausor FH, Jaafar J, Taib SM et al (2021) Iterative Fuzzy C Means, Fuzzy Silhouette, and Imputation for Missing Values in a Dataset [C]. IEEE International Conference on Computing (ICOCO), 2021, 382–385. IEEE
Hu P, Zhiwei Y, Qiyi H et al (2022) Sensors 22(15):5645–5645Discrete Missing Data Imputation Using Multilayer Perceptron and Momentum Gradient Descent [J]
Zhang X, Sun X, Xia L et al (2024) A Matrix Completion Method for Imputing Missing Values of Process Data [J]. Processes, 12 (4)
Vishal HB, Manminder S (2022) NMVI: A data-splitting based imputation technique for distinct types of missing data [J]. Chemometrics and Intelligent Laboratory Systems, p 104518
Guo C, Yang W, Liu C et al (2024) Iterative missing value imputation based on feature importance [J]. Knowl Inf Syst. (5):1–28
Júnior SDAG, Silva DMA (2021) A simple and efficient incremental missing data imputation method for evolving neo-fuzzy network [J]. Evol Syst 13(2):1–20
Dehghani A, Bagherifard K, Nejatian S et al (2023) A novel missing value imputation relying on K-means clustering and kernel-based weighting using grey relation (KWGI) [J]. J Intell Fuzzy Syst 44(4):5675–5697
M K F, M M I, Taher AA et al (2021) Advanced methods for missing values imputation based on similarity learning. [J]. PeerJ Comput Sci 7:619
Tlamelo E, Thabiso M, Dimane M et al (2021) A survey on missing data in machine learning [J]. J Big Data 8(1):140

No competing interests reported.

Download PDF

Version 1

posted

You are reading this latest preprint version

Data cleaning method based on multiple interpolation

Status:

Version 1

Abstract

Figures

1 Introduction

2 methodology

3 Experimental Result

3.1 Environment and dataset

3.2 Results and Comparative Analysis

4 conclusion

Declarations

Author Contribution

References

Additional Declarations

Status:

Version 1