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?

  • $\begingroup$ Is there a structure in the categorical categories that you can use to bin them? - essentially something like a grouped one hot encoding. Example for urls: top-domain. $\endgroup$
    – El Burro
    May 23, 2017 at 13:22

2 Answers 2


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.

  • $\begingroup$ Indeed, features that have different values for each instance (e.g. URLs) have no predictive value in itself. $\endgroup$
    – K3---rnc
    May 24, 2017 at 11:46

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

I am positive there might be many features in you dataset that can be grouped in one column, 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 no 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.) Same goes for variance. If a feature has more than 95% of it's values which are same (for ex if I have fuel feature which has 98 values as petrol and 2 values as diesel, I would drop that feature as it wont contribute much). Search VarianceThreshold on sklearns site for documentation.

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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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