3
$\begingroup$

Background:

I've been struggling with an exponential regression problem for about 8 months now (on and off):

It goes without saying that I'm not a math guy. Not even close.


Problem:

I would like to learn how to generate an exponential regression equation for road condition data, just like it was done for me here:

In other words, I want to learn how to generate an exponential regression equation, so I can eventually update the coefficient in the existing model (on the full data set).


My soloution (mock data):

I've mocked up a sample dataset here:

+--------------+---------------+
|    X (AGE)   | Y (CONDITION) |
+--------------+---------------+
|       0      |       20      |
|       1      |       20      |
|       2      |       20      |
|       3      |       20      |
|       4      |       20      |
|       5      |       20      |
|       6      |       18      |
|       7      |       18      |
|       8      |       18      |
|       9      |       18      |
|       10     |       16      |
|       11     |       16      |
|       12     |       14      |
|       13     |       14      |
|       14     |       12      |
|       15     |       12      |
|       16     |       10      |
|       17     |        8      |
|       18     |        6      |
|       19     |        4      |
|       20     |        2      |
+--------------+---------------+

Steps in Excel:

C. Convert Y to be more linear using the natural logarithm function

D. Calculate a straight line that best fits the data, and then return an array that describes the line (using the LINEST function).

E. Generate a trend-line on D, and use the coefficient from that trend-line to create an exponential regression equation: =21-exp(0.14723*x)

enter image description here enter image description here


Question:

How successful was I? Was my approach mathematically correct?

$\endgroup$
  • $\begingroup$ Is this clear ? Tell if it is not. $\endgroup$ – Claude Leibovici Aug 27 '18 at 5:37
3
+50
$\begingroup$

You want to fit the model $$y=21-e^{a x}\tag 1$$ For sure, you can have an extimate writing $$21-y=e^{a x}\implies \log(21-y)=ax\implies z=a x\tag 2$$ and a preliminary linear regression gives $a=0.147233$ (just as you did).

In fact, you do not need to use regression since you can get $a$ directly from the normal equation $$a=\frac{\sum_{i=1}^n x_iz_i } { \sum_{i=1}^n x_i^2 }=\frac{\sum_{i=1}^n x_i \log(21-y_i)} { \sum_{i=1}^n x_i^2 }$$

But this is only the preliminary step since what is measured is $y$ and not $\log(21-y)$. So, you need to continue with a nonlinear regression using this estimate. This would lead to $a=0.149140$.

Let us compare the results for $y$ using both models $$\left( \begin{array}{cccc} x & y & (2) & (1) \\ 0 & 20 & 20.0000 & 20.0000 \\ 1 & 20 & 19.8414 & 19.8392 \\ 2 & 20 & 19.6576 & 19.6525 \\ 3 & 20 & 19.4447 & 19.4357 \\ 4 & 20 & 19.1979 & 19.1841 \\ 5 & 20 & 18.9121 & 18.8921 \\ 6 & 18 & 18.5809 & 18.5531 \\ 7 & 18 & 18.1972 & 18.1595 \\ 8 & 18 & 17.7526 & 17.7027 \\ 9 & 18 & 17.2374 & 17.1723 \\ 10 & 16 & 16.6406 & 16.5567 \\ 11 & 16 & 15.9491 & 15.8421 \\ 12 & 14 & 15.1479 & 15.0125 \\ 13 & 14 & 14.2196 & 14.0495 \\ 14 & 12 & 13.1441 & 12.9316 \\ 15 & 12 & 11.8980 & 11.6339 \\ 16 & 10 & 10.4542 & 10.1275 \\ 17 & 8 & 8.78136 & 8.37881 \\ 18 & 6 & 6.84319 & 6.34887 \\ 19 & 4 & 4.59758 & 3.99245 \\ 20 & 2 & 1.99576 & 1.25704 \end{array} \right)$$

Using model $(2)$ and back to the $y$'s, the sum of squares is $8.28$ while using model $(1)$ lead to a sum of squares equal to $6.66$ which is quite better.

Moreover, it is interesting to look at the statistics.

For model $(2)$, we have $$\begin{array}{clclclclc} \text{} & \text{Estimate} & \text{Standard Error} & \text{Confidence Interval} \\ a & 0.147233 & 0.005034 & \{0.136698,0.157769\} \\ \end{array}$$ while for model $(1)$ $$\begin{array}{clclclclc} \text{} & \text{Estimate} & \text{Standard Error} & \text{Confidence Interval} \\ a & 0.149140 & 0.000873 & \{0.147312,0.150967\} \\ \end{array}$$ showing that, using the "true" model, the standard error is basically divided by a factor of almost $6$.

If you do not want to use nonlinear regression, you could use Excel to solve for $a$ the equation $$f(a)=\sum_{i=1}^n e^{ax_i}\left(21-e^{ax_i}-y_i \right)=0$$ strating from the preliminary guess. Even graphing the function could be sufficient.

For solving the equation, you could also use Newton method $$f'(a)=a\sum_{i=1}^n e^{ax_i}\left(21-2e^{ax_i}-y_i \right)$$ and use $$a_{n+1}=a_n-\frac{f(a_n)}{f'(a_n)}$$ using for $a_0$ the value obtained from the preliminary step.

For your problem, Newton iterates would be $$\left( \begin{array}{cc} n & a_n \\ 0 & 0.1472330000 \\ 1 & 0.1492437955 \\ 2 & 0.1491401458 \\ 3 & 0.1491398530 \end{array} \right)$$

Edit

If we consider the data set outside its specific context, we could have obtained a better fit using $$y=a-b\, e^{cx}\tag 3$$ which leads to a sum of squares equal to $4.97$ with the following parameters $$\begin{array}{clclclclc} \text{} & \text{Estimate} & \text{Standard Error} & \text{Confidence Interval} \\ a & 22.1098 & 0.5276 & \{20.9968,23.2229\} \\ b & 1.57255 & 0.3101 & \{0.91830,2.22680\} \\ c & 0.12823 & 0.0092 & \{0.10875,0.14771\} \\ \end{array}$$ leading to the following results $$\left( \begin{array}{ccc} x & y & (3) \\ 0 & 20 & 20.5373 \\ 1 & 20 & 20.3221 \\ 2 & 20 & 20.0775 \\ 3 & 20 & 19.7995 \\ 4 & 20 & 19.4834 \\ 5 & 20 & 19.1241 \\ 6 & 18 & 18.7156 \\ 7 & 18 & 18.2513 \\ 8 & 18 & 17.7234 \\ 9 & 18 & 17.1233 \\ 10 & 16 & 16.4410 \\ 11 & 16 & 15.6655 \\ 12 & 14 & 14.7838 \\ 13 & 14 & 13.7816 \\ 14 & 12 & 12.6422 \\ 15 & 12 & 11.3469 \\ 16 & 10 & 9.87440 \\ 17 & 8 & 8.20046 \\ 18 & 6 & 6.29750 \\ 19 & 4 & 4.13420 \\ 20 & 2 & 1.67494 \end{array} \right)$$

$\endgroup$
  • $\begingroup$ @Wilson. Basically you did most of it by yourself. For the remaing, code in Excel the last recurrence relation. That's all. It is very simple. Tell me what you do not understand. Cheers. $\endgroup$ – Claude Leibovici Aug 27 '18 at 6:19
  • $\begingroup$ Can you clarify what you mean by this?: But this is only the preliminary step since what is measured is y and not log(21−y). So, you need to continue with a nonlinear regression using this estimate. This would lead to a=0.149140. Where did 0.149140 come from? $\endgroup$ – Wilson Aug 27 '18 at 22:07
  • 1
    $\begingroup$ @Wilson. The two models are not the same; so the parameters are not identical (except if the model was perfect and no error at all in the $y$'s. The first step (using logarithms) allows a qucik and easy estimate of $a$. Now, using model $(1)$, you have to tune it for final solution. This is given by the nonlinear regression. $\endgroup$ – Claude Leibovici Aug 28 '18 at 2:10
  • $\begingroup$ Thank you for the follow-up/edit to your answer. I missed it before, but I see it now. $\endgroup$ – Wilson Sep 16 '18 at 20:06
  • $\begingroup$ @Wilson. You are welcome ! I would prefer Basic exponential regression $\endgroup$ – Claude Leibovici Sep 17 '18 at 2:36

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.