1
$\begingroup$

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?

$\endgroup$
  • $\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 '17 at 13:22
2
$\begingroup$

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.

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

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.