0
$\begingroup$

I want to do a regression to predict "value" based on the other columns from below example table. The data was collected by single indicator and not across all data points, resulting in many NaN/blank values:

value     age     education     gender
32.3                            Male    
31.8                            Female  
32.8              High school       
33.8              Technical school      
26.4              College graduate      
16.3     18 - 24            
35.2     25 - 34            
35.5     35 - 44

I converted categorical data by using dummy variables which resulted in below column examples. I guess that the quality of my model will be affected because I have only a single 1 by row and the rest is all 0.

value   18 - 24   25 - 34   35 - 44   College   High school
32.8       0         0         0        0          1
26.4       0         0         0        1          0
16.5       1         0         0        0          0

So my question is, what is the best way to clean and convert the data for given source data structure?

Improve this question
$\endgroup$
2
  • $\begingroup$ It depends on the model you use for prediction. Boosting trees usually allow Nan values. Xgboost and lightGBM should be able to work with nan values efficiently without any preprocessing. For other methods there are lots of variants beginning with replace with zero. $\endgroup$
    – keiv.fly
    Commented Dec 9, 2018 at 2:06
  • $\begingroup$ Would you be allowed to use inferences such as if education = High School, then Age = 13-18 (or whatever the age for the region you are considering)? This might not be the best idea if you want to incorporate outliers, but since you are using ranges for the age column, I don't think this data is anyway accounting for outliers and so should not be a problem. $\endgroup$
    – rahs
    Commented Dec 9, 2018 at 2:32

1 Answer 1

Reset to default
1
$\begingroup$

If you have 3 completely different data sources where there are no common columns consider creating 3 separate models instead of trying to force them into one.

If you have overlapping columns as well handling of NaN values can be influenced by your model choice. (i.e. most tree based model can handle missing values) If you use linear regression you need to have numerical values and you can experiment with several options:

  • adding new variables indicating if a certain variable is missing or not (0/1)
  • instead of using 0 for the missing values experiment replacing them with either median/mean/random value from the original distribution of the variable (as a result your new data will have the same distribution of values as the original one)
  • create model for predicting variables containing missing values and use it to replace NaNs
  • use some dimensionality reduction if your matrix is too big and sparse
Improve this answer
$\endgroup$

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.