How to deal with categorical variable - location- with more than 60 levels

Question

I am new to statistics and to categorical variables. I need to predict the cost based on several variables and it happened that all of my variables are categorical. I tried doing a linear regression between my response (actual cost $) and the following predictor variables:estimated cost($), county, month, and project type. I noticed then under the following linear model

lm = lm(actual cost ~ estimated cost)

R-squared is 97%. However, I am looking for a degree of accuracy where 50 % of the projects should be within 5% of the estimated:

sum(abs((lm$fitted.values - actual cost)/actual cost))/nrow(data) >=50% 

Under the simple model, although R squared is high, but only 30% of the projects have a 5% or less level of accuracy. I started adding more predicted variables and I noticed that adj R squared does not increase much (98%), but 48% of the project satisfy the required level of accuracy under the following model:

lm = lm(actual cost ~ estimated cost + county + month + work type)

county: 78 levels month:12 levels qork type: 6 levels

The summary(lm) shown that not all p-values of the levels are significant. I then did a stepwise, and the selected model contained the estimated cost, the 12 months and the 5 work types. However, on 38% accuracy was achieved.

I don't know if the linear regression is correct, or if you guys can provide me with the resources to read more about regression with categorical varaibles with large number of levels. Can I do stepwise with categorical variables? Why the R squared did not increase much? What other analysis can be performed.

I would greatly appreciate any help on this issue!!

Answer 1

You state that all of your variables are categorical, but I'm going to assume that actual cost and estimated cost are continuous.

It seems that your main goal is prediction and not estimation of parameters. I think that a better approach might be to use a mixed effects model.

library(lme4)
library(lmerTest) 
m1 <- lmer(actual_cost ~ estimated_cost + (1|county) + (1|month) + (1|work_type))
summary(m1)

In this model, county, month, and work type are used as crossed random effects. You can also try this:

m2 <- lmer(actual_cost ~ estimated_cost + (estimated_cost|county) + (1|month) + (1|work_type))

Now the relationship between estimated cost and actual cost may also be affected by the county (if there are larger discrepancies between the estimated and actual costs in some counties). Of course, you could extend this to the other random effects as well:

m3 <- lmer(actual_cost ~ estimated_cost + (estimated_cost|county) + (estimated_cost|month) + (estimated_cost|work_type))

Work type only has 6 levels and is perhaps not best used as a random effect, but as a fixed effect, because the impact of the 6 levels on the dependent variable may not follow a gaussian distribution, which is assumed when using it as a random effect (I think you can make different distributional assumptions but I don't know how). So perhaps this:

m4 <- lmer(actual_cost ~ estimated_cost + work_type + (estimated_cost|county) + (1|month))

It may of course also be the case that the impact of the estimated cost on the actual cost is not quite linear. It may be the case that the estimated costs are less accurate (or more accurate) when the estimated costs are higher or lower. You can try adding a quadratic term and see if that improves the model:

m5 <- lmer(actual_cost ~ estimated_cost + I(estimated_cost^2) + work_type + (estimated_cost|county) + (1|month))

I hope this gives you some ideas how to continue.