0
$\begingroup$

I have two matrices with numeric data and a lot of missing values. The missing values are not completely coherent in both matrices. My aim is to fill the missing values so I can perform further analysis on it. I can calculate the correlation using the cor() function and use the highly correlated matrices for filling in the gaps. But how can I after finding the correlation, fill the missing values? Say, I have 5 matrices and I want to use 4 matrices to fill the gaps in the first matrix.

So to add more detail, I have monthly rainfall data from 30 stations. The data is for more than 100 years but not of the same length for each station. Some stations have data for 120 years and some for 60 or 80 years. What I basically want to do is here:

  1. Using the 5 neighboring stations (the ones within an area of 80 km), fill in the missing data.
  2. For this purpose I intend to use correlation and select the station with the highest correlation for imputing missing values.
  3. Now, I can find the correlation between two matrices but is there a way to now replace the missing data in the first matrix with the rainfall data present in the second matrix? Like if I have matrix A which has a No data at row 30, column 6 and I have a matrix B which is highly correlated with matrix A and has a valid data entry at the same column and row id, how can I replace the data in that cell in A with B?

I know doing it manually would be an option but as I said there are more than 30 stations and each station has almost a matrix of size (120 x 13), so using part of some could would make it easier.

Improve this question
$\endgroup$
2
  • $\begingroup$ I edited the original question with more details, hope this makes it easier to understand $\endgroup$
    – Aitzaz Malik
    Sep 9, 2019 at 14:59
  • $\begingroup$ If you want to solve the problem programmatically, you must specify which software/programming language you want to use. You can certainly achieve what you want in R. $\endgroup$
    – Niels Holst
    Mar 23, 2022 at 15:54

1 Answer 1

Reset to default
0
$\begingroup$

I am not 100% sure about your question.

There are many ways to impute the missing data depending on the property of your dataset. Some of the common ways are: using the mean value of the feature column to impute the corresponding missing value; using hidden Markov model to do the imputation, using KNN, etc.

Back to your question, if you really want to use correlation to impute, I don't have a solid solution, but suppose you have a N*M matrix X, where M is the number of rows and M is the number of columns. In reality, M could represent the number of features while N represents the number of samples. In this case, suppose you have missing data in column i, and based on the feature correlation matrix (or correlation of M), you know the Pearson correlation between feature i and j is: r = 0.9. Then you may start from generating a new Xi using normal distribution with desirable mean and variance (the one you want). Compute the new Xi by using Xi = r'*Xj + sqrt(1 - r'^2)*r'*Xi. Where r' is a coefficient between -1 and 1.

In general, the larger r' is, the stronger correlation the new generated Xi and Xj would be. But you need to empirically check the correlation between your new Xi and Xj to make it approximate 0.9, which is the correlation you want.

But again, I won't choose to impute the data by using a correlation matrix. I prefer KNN or HMM.

Improve this answer
$\endgroup$
1
  • $\begingroup$ I edited the original question with more details, hope this makes it easier to understand $\endgroup$
    – Aitzaz Malik
    Sep 9, 2019 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.