Correlation matrix with a dataset with many missing value

Question

I am a data-science rookie and I would like to use Python/ R to create a correlation matrix (something like this: http://www.marketcalls.in/python/quick-start-guide-compute-correlation-matrix-using-nsepy-pandas-python.html) and build a machine learning model. However, I have some questions and would really appreciate some guidance.

Question 1: Although the data file is pretty big and have more than 350,000 entries, some columns missed many values (i.e., 60%/ 70% of the values are missing). I am wondering should I abandon those columns/ delete those rows/ any other great recommendations? And what is a good threshold, is it okay to proceed with columns that 20%? 30%? 40% values are missing.

Thank you very much. Greatly appreciated your help!!

Answer 1

It is impossible to say whether or not you should trim your data, without doing some Exploratory Data Analysis first. Those missing values may be important to your understanding of the dataset, so you have to look at your data comparing variable to variable to determine if the missing values hold information.
For example, if your data is about vehicles, there could be a variable representing number of doors. If your missing data is structural an observation of a motorcycle may have a missing value for that variable. In which case, the observation for that variable could be discarded as meaningless. Or, you could impute the missing value to something meaningful like the number 0. It depends on what insight you are ultimately trying extract from you data. Handling missing values is quite a complex subject.

I would suggest that you "Tidy the data" first. From the Wickham paper

Tidy datasets are easy to manipulate, model and visualize, and have a specific structure: each variable is a column, each observation is a row, and each type of observational unit is a table.

Initially this can help you do some Exploratory Analysis to determine what is important and what is not. Once you have made those distinction you can find and implement strategies to handle the missing values appropriately.

Answer 2

There are a variety of ways in which missing data can be meaningful or not. Structural missing data are values which the underlying reality would tell you that no valid entry could be made, e.g. number of pregnancies in a male participant. These should be left as missing. Other NA entries may have predictable relationship with other columns of your data. Omitting these will potentially distort the estimates of correlations and other measures of joint relationships. This is partly a statistical task and partly analysis of the reality underpinning the data.

There is a rich statistical literature about how to properly "impute" values for missing entries in a manner that both allows preservation of the original set of inter-relationships in the data and also supports proper statiscal inference. Methods to impute data that are improper (at the extremes of wrongness) include a) replacements of the mean of the non-missing data or b) replacements simply with random data.

If you have knowledge of R, then the mice package and the rms package both have well described and validated approaches to imputation.