Imputation of missing values and dealing with categorical values

Question

I have a dataset (10 million rows, 55 columns) with many missing values. I need to predict those values somehow using other non-missing values, i.e. replace them with something that is not NaN. Mean and median are not the solution here.

I tried to research other methods for that but none of them works since I have many categorical variables. I also tried to use one hot encoding to convert categorical variables to integers but I am not sure if that is a solution in my case since from only 1 categorical column I would get 600 new columns. If I do the same with other categorical columns, I would get many millions of new columns. One of the categorical columns is URL string and it is different for every row, so I have 10 million different URLs for example.

The other categorical column is a description and it is also different for every row. I could probably remove the URL column, but I can't remove description, title, location and others for example. I tried PCA, but it also doesn't work with categorical data. I have missing data for both categorical and integers/floats values. Would get_dummies method be a good approach to deal with this? For missing values imputation I tried KNN and maximum likelihood but I am getting errors due to categorical variables. Missing data is completely randomly missing.

Do you have any suggestions how to approach this problem and also which packages should I use?

Answer 1

I think like @El Burro suggested, you I believe you should focus on feature transformation mainly. Use different techniques for different features. For straightforward features, such as occupation or gender for example, use one-hot encoding, while for others you can use some kind of hierarchical mapping-clustering (e.g. map values to groups defined by you, for example if those urls linked to products, make 30 different groups with similar types of products and map the urls to these groups. Then you can use again one-hot encoding for these mapped features).

For the textual features you mentioned, I'm pretty confident you could drop some of them. If you really want to exploit some kind of information generated from text though, for starters you could either do some tf-idf feature extraction, so as to generate features for each text-snippet or consider topic modeling (take a look at gensim for python if interested) and represent each text as a mixture of topics.

Answer 2

I had the same problem in one of the datasets I was using, and the answer is to focus more on feature transformation. If you simply include all the features of your dataset for encoding, you would probably end up with more numbers of columns than your rows!

I am optimistic there might be many features in your dataset that can be grouped in one column, and some features can be dropped because of multicollinearity (but I would suggest you double-check using other feature selection techniques, too).

1. Mainly try to reduce the number of features (feature transformation) without dropping any of them.
2. Try to impute nan values and then find multicollinearity for numerical features.
3. If a feature contains more than 95% nan values, it would be wise to drop it as imputation can worsen the performance.
4. The same goes for variance if a feature has more than 95% of its values, which are the same (for example, if I have a fuel feature with 98 values as petrol and 2 values as diesel, I would drop that feature as it won't contribute much). Check VarianceThreshold documentation on sklearn package.

There are many more techniques, and I am sure doing all of the above will reduce your number of features and, hence, your dimensionality.