# Transformation of data with zero and R squared

I have a conceptual concern about data tranformation and R^2. Often we transform data to respect the assumption of the linear model. Therefore, we can use multiple type of transformation such as log and square root. When the data contain zero and we find that the log transformation is the most suitable then we need to add a value to be able to transform it in log since log of zero is - Inf.

So we try to add a value that won't affect the result of our relationship. My problem is here that if I had value to my data set, the bigger it is , the higher is the R squared.

Here an example

x<-c(10,20,0,30,40,10,0,1,8,56)
y<-c(5.6,7.3,0,6.5,8.9,0,0.1,2,4.5,10.6)

modelz<-lm(y~x)
summary(modelz)


The square root transformation always give a higher R squared. Somebody know why ?( might be a stupid question sorry for that)

 modelzsq<-lm(sqrt(y)~sqrt(x))
summary(modelzsq)

modelzlog<-lm(log10(y+0.1)~log10(x+0.1))
summary(modelzlog)

modelzlog2<-lm(log10(y+0.05)~log10(x+0.05))
summary(modelzlog2)

modelzlog3<-lm(log10(y+0.2)~log10(x+0.2))
summary(modelzlog3)


So if you notice, the modelzlog3 has a higher R squared than the two other log transform models. Therefore, I think the reason for that is that smaller value when transform in log10 are more negative .

For example log(0.1) give -1 and log(0.01) is -2 . So The bigger is the vaue the closest is the value to the other one (all positive value), this explain why the R squared is higher with bigger value I think...

My question is: I am doing model selection base on AIC but I can't compare different data transform model(see AIC equation...) so do I select model only base on the respect of the assumptions and the biological sense of the transformation ? Like "y" would be rodent density and a value of 0.1 would be the density value when we catch only one rodent so the minimal density that can be obersved.

Thanks a lot for your advice !

Cheers

• Why not use the natural logarithm? And then try with log(x+1)... but really one should always avoid such transformations since they are completely arbitrary. There are many questions on this site that has discussion on this topic. I have a given an answer with a reference if you look though my profile. Feb 9, 2016 at 20:48
• A common situation in pharmacological trials, it seems, is that doses of a drug should be tried with approximately logarithmically spaced positive doses, plus no dose at all. It's often hard to avoid a composite transformation in analyses, as dose is a predictor or covariate. May 21, 2021 at 13:14

It is not very meaningful to compare $$R^2$$ between models with differently transformed data - for a start the transformations also imply different error distributions (e.g. additive errors for the linear model, multiplicative errors for a log-transformed model) and that you care about certain errors more than about others.

So, really, you should first decide what you care about. E.g. $$R^2$$ on the square-root transformed scale. Then you fit the model and look at how it does in terms of a metric that you truly care about e.g. like this:

x<-c(10,20,0,30,40,10,0,1,8,56)
y<-c(5.6,7.3,0,6.5,8.9,0,0.1,2,4.5,10.6)

modelz<-lm(y~x)
summary(modelz)

rsquared = function(object, ys, newdata, transform=sqrt, backtransform=identity){
ssres = sum( (transform(ys)-transform(backtransform(predict(object=object, newdata=newdata))))^2 )
sstot = sum( (transform(ys)-mean(transform(ys)))^2 )
return( 1 - ssres/sstot)
}

rsquared(modelz, y, data.frame(x=x), transform=identity, backtransform=identity) # The result you had: 0.7876
rsquared(modelz, y, data.frame(x=x), transform=sqrt, backtransform=identity) # 0.569

modelzsq<-lm(sqrt(y)~sqrt(x))
summary(modelzsq)

squared = function(x) x^2
rsquared(modelzsq, y, data.frame(x=x), transform=sqrt, backtransform=squared) # This matches the result you had: 0.6859596

modelzlog<-lm(log10(y+0.1)~log10(x+0.1))
summary(modelzlog)

logp0_1 = function(x) log10(x+0.1)
logp0_1_inv = function(x) 10^x - 0.1
rsquared(modelzlog, y, data.frame(x=x), transform=logp0_1, backtransform=logp0_1_inv) # This matches the result you had: 0.5795
rsquared(modelzlog, y, data.frame(x=x), transform=sqrt, backtransform=logp0_1_inv) # 0.6605

modelzlog2<-lm(log10(y+0.05)~log10(x+0.05))
summary(modelzlog2)

logp0_05_inv = function(x) 10^x - 0.05
rsquared(modelzlog2, y, data.frame(x=x), transform=sqrt, backtransform=logp0_05_inv) # 0.639

modelzlog3<-lm(log10(y+0.2)~log10(x+0.2))
summary(modelzlog3)

logp0_2_inv = function(x) 10^x - 0.2
rsquared(modelzlog3, y, data.frame(x=x), transform=sqrt, backtransform=logp0_2_inv) # 0.6736


Note that it is not surprising that the square root transformed model does best in terms of $$R^2$$ on the square-root scale, because it is actually trying to minimize mean-squared error on the square-root scale (the numerator of $$R^2$$).

Additionally, note that looking at lots and lots of variations of your model will lead to overfitting - especially with this small a dataset (assuming your real dataset is that small) - and performance that looks too good by evaluating model performance on the same data you fitted your model on. One possible way to reduce the impact on that is to split your data into training data (e.g. you fit the data on 80% of your data) and validation data (you see how good $$R^2$$ or some other metric like MSE is on the remaining 20% of the data). Of course, 20% of the data is not a lot to evaluate your model on, so you do this again with another non-overlapping 20% and again. After you've done that 5 times, you've just performed 5-fold cross-validation (CV). Looking at how you did across those 5 CV-splits can be a sensible way of deciding on modeling choices. There's other more sophisticated approaches, in particular nested cross-validation and bootstrapping are frequently recommended.

The other thing to note is that there's no rule that says that your $$y$$s and $$x$$s must undergo the same transformation. It may sometimes (often?) be the case that it makes sense, but that should in part be based on your expertise.

I think it's always important to understand the context for the data you're modeling. In your case, your outcome is rodent density. That means 0 has a real meaning, no rodents in a location and more generally, data cannot be less than 0 - it's important to identify the source of bounds on data. This applies to both outcomes and predictors, as sometimes, 0 reflects a limitation of your measurement instrument which can attenuate coefficients.

Now what you want is to be able to identify the relation between density and x. One challenge is that your sample size here is very small. If there was a series of transformations for understand the relation between density and x that made theoretical sense, I would simply go with that. However, that you're asking this question suggests no such theory. Without theory and with a small sample size, one should not have too high expectations of data. I begin by plotting the data with two fits:

• a penalized spline: from the mgcv package to capture a flexible non-linear relation
• a linear fit: for small datasets, this may just be good enough

I plotted your data with:

library(ggplot2)
ggplot(mapping = aes(x = x, y = y)) + geom_point(shape = 1) +
geom_smooth(method = "gam", formula = y ~ s(x, bs = "ts", k = 2), se = F) +
geom_smooth(method = "lm", color = "red")
# geom_smooth() using formula 'y ~ x'
# Warning message:
# In smooth.construct.tp.smooth.spec(object, data, knots) :
#   basis dimension, k, increased to minimum possible


In the particular example you provided, a linear fit to the data appears sufficient as the linear regression fit is not substantively different from a penalized non-linear fit. And I would settle for simple linear regression fit to these data.

With theoretical guidance, I might be willing to make a few more assumptions about the data and opt for a more complex model. Without theory, there is only so much one can expect to learn from small datasets, all other factors held constant. On the other hand, big datasets with good measurements (or quantified measurement error) allow you generate and compare different theories of the data.

With more data, I would again begin with the plot and go from there. There are quite a number of options for modeling data that it's difficult to answer such a broad question conceptually. Optimizing $$R^2$$ is not necessarily reasonable. Moreover, $$R^2$$ cannot be used to compare differently transformed versions of an outcome.

Additionally, for an outcome with zeroes, is there a different process governing locations/instances with no rodents than the process governing places with one or more rodents? If so, one may start to consider hurdle and/or zero-inflated models.

Additionally, one should prefer a distributional assumption that respects the lower-bound on the data. Assuming the mean function is reasonably captured, ignoring the lower-bound could affect standard errors. You could also stick with Gaussian and apply robust standard errors.

• There is two philosophies and transforming data is one of them ! May 27, 2017 at 22:46
• Yes, there are two philosophies expressing different modeling techniques. However, more often than not, it is much better to use a generalized linear model. May 27, 2017 at 23:12
• Not right , and not always pertinent ! Anyways that is not the topic of the question and there is plaintyyy of book and questions on this topic! May 28, 2017 at 0:55
• It seems like GLMs don't make so much sense here. These are clearly not Poisson or negative binomial counts, or binomial data. May 21, 2021 at 8:00
• @NickCox Björn 3/4 years later, I agree with your comments that my answer is too simple. I'll edit if I can make time. May 21, 2021 at 12:27