Showing posts with label Notes on Statistics. Show all posts
Showing posts with label Notes on Statistics. Show all posts

Thursday, April 10, 2025

R Library to load inorder perform OLS tests

The R library required to perform Durbin-Watson, Breusch-Godfrey, Breusch-Pagan, and Goldfeld-Quandt tests:

 

> library(lmtest)

 

 

Wednesday, March 26, 2025

Regression using R

Ordinary Least Squares (OLS) Regression Coefficients

MyMdl = lm(formula = GDP2018 ~ T, data = MM_DTA_01)

summary(MyMdl)

Will produce the following output:

Call:
lm(formula = GDP2018 ~ T, data = MM_DTA_01)

Residuals:
     Min       1Q   Median       3Q      Max
-2684399 -2025650  -592330  1430009  6473734

Coefficients:
            Estimate Std. Error t value Pr(>|t|)    
(Intercept) -2427989     513893  -4.725 1.03e-05 ***
T             218026      11303  19.290  < 2e-16 ***
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 2248000 on 76 degrees of freedom
Multiple R-squared:  0.8304,    Adjusted R-squared:  0.8282
F-statistic: 372.1 on 1 and 76 DF,  p-value: < 2.2e-16

 

Multiple Regression

MyMdl = lm(formula = GDP2018 ~ T + EX2018 + IM2018, data = MM_DTA_01)


 

Durbin-Watson Statistic for Serial Correlation (Autocorrelation)

DurbinWatsonTest(MyMdl) 

Will produce the following output:
    Durbin-Watson test

data:  MyMdl
DW = 0.035978, p-value < 2.2e-16
alternative hypothesis: true autocorrelation is greater than 0

 

Breusch-Godfrey Statistic for Serial Correlation (Autocorrelation)

bgtest(MyMdl) 

Will produce the following output:
    Breusch-Godfrey test for serial correlation of order up to 1
 

data:  MyMdl
LM test = 74.301, df = 1, p-value < 2.2e-16


Breusch-Pagan Statistic for Heteroscedasticity

bptest(MyMdl)

Will produce the following output:

  Studentized Breusch-Pagan test

data:  MyMdl

BP = 19.317, df = 1, p-value = 1.107e-05

 

Goldfeld-Quandt Statistic for Heteroscedasticity

gqtest(MyMdl)

Will produce the following output:

  Goldfeld-Quandt test

data:  MyMdl

BP = 29.613, df1 = 37, df2 = 37, p-value = 2.2e-16

alternative hypothesis: Variance increase from segment 1 to 2

 

Correlation Coefficient

head(DataNo2)

Will produce the following output:

# A tibble: 6 × 7
     YR     T       GDP GDPPI2018   GDP2018  EX2018  IM2018
  <dbl> <dbl>     <dbl>     <dbl>     <dbl>   <dbl>   <dbl>
1  2008     1  8050201.     0.784 10270878. 1647293 2216258
2  2009     2  8390421.     0.805 10419633. 1481405 2192637
3  2010     3  9399451.     0.840 11183861. 1769189 2668470
4  2011     4 10144661.     0.873 11615360. 1697601 2716794
5  2012     5 11060589.     0.891 12416466. 1901720 2867464
6  2013     6 12050592.     0.909 13254644. 1817413 3037079

 

cor_matrix = cor(DataNo2)

print(cor_matrix)

Will produce the following output:

                YR         T       GDP GDPPI2018   GDP2018    EX2018    IM2018
YR        1.0000000 1.0000000 0.9884230 0.9872057 0.9791493 0.9599731 0.9465083
T         1.0000000 1.0000000 0.9884230 0.9872057 0.9791493 0.9599731 0.9465083
GDP       0.9884230 0.9884230 1.0000000 0.9788880 0.9943552 0.9807268 0.9764256
GDPPI2018 0.9872057 0.9872057 0.9788880 1.0000000 0.9595876 0.9402991 0.9210839
GDP2018   0.9791493 0.9791493 0.9943552 0.9595876 1.0000000 0.9769194 0.9851542
EX2018    0.9599731 0.9599731 0.9807268 0.9402991 0.9769194 1.0000000 0.9859029
IM2018    0.9465083 0.9465083 0.9764256 0.9210839 0.9851542 0.9859029 1.0000000


cor_matrix = round(cor(DataNo2), 2)
print(cor_matrix)
Will produce the following rounded to 2 decimal digits correlation matrix output: 
          YR    T  GDP GDPPI2018 GDP2018 EX2018 IM2018
YR 1.00 1.00 0.99 0.99 0.98 0.96 0.95
T 1.00 1.00 0.99 0.99 0.98 0.96 0.95
GDP 0.99 0.99 1.00 0.98 0.99 0.98 0.98
GDPPI2018 0.99 0.99 0.98 1.00 0.96 0.94 0.92
GDP2018 0.98 0.98 0.99 0.96 1.00 0.98 0.99
EX2018 0.96 0.96 0.98 0.94 0.98 1.00 0.99
IM2018 0.95 0.95 0.98 0.92 0.99 0.99 1.00 

Graphing with R

plot(MyMdl)

Will produce the following graphs:


 



 

 

 

 

 

 

 


Thursday, March 16, 2023

Correlation Coefficient

The correlation coefficient measures the linear relationship between two variables.  It is often represented by p (rho) symbol for the population and r for the sample.  The value ranges from -1 to +1.  The sign of the value indicates the direction of the linear relationship.  A positive (+) sign indicates a positive relationship which can be viewed as an upward sloping line, where an increase in one variable meant an increase in the other variable.  A (-) sign indicates a negative relationship which can be viewed as a downward sloping line, where an increase in one variable meant a decrease in the other variable and vice versa.

The formula for the correlation coefficient is:


 

Generally, a correlation coefficient of 0.90 and above, regardless of the sign, is considered high correlation.  A correlation coefficient of 0.50 and below is considered low correlation.  A correlation coefficient between 0.5 and 0.9 is considered medium correlation.

Some things to note about correlation coefficient value:

a)  A correlation coefficient of 0.50 meant that the one variable explains only about 25% of the variation in the other variable;

b)  It takes a correlation coefficient of 0.70 for one variable to explain about 50% of the variation in the other variable;

c)  For a correlation coefficient to be convincing in determining the linear relationship between the two variables, a medium correlation needs to tested for statistical significance using t statistic;

d)  It is often safe to say, that low correlations means "no correlations".  By precision, no correlation will have a correlation value of 0.


 

t-Statistic of the Correlation Coefficient

The t-Statistic of the Correlation Coefficient is given by the formula:



        where:     r = value of the correlation coefficient

                        n = is the number of samples

 

The t-Statistic is used to test the following hypothesis:

a)  Ho (Null Hypothesis) that r is equal to 0 and there is no significant correlation between the two variables under study;

b)  Ha (Alternative Hypothesis) the r is not equal to zero and that there is a significant correlation between the two variables under study.


As a rule of the thumb for samples larger than 60, a t-Statistic value of 2 or more (regardless of the sign) means that it is statistically significant and therefore the Ha (alternative hypothesis) is accepted and the Ho (null hypothesis) is rejected.  Inversely, a t-Statistic value of less than 2 (regardless of the sign) means that it is NOT statistically significant and therefore the Ho (null hypothesis) is accepted and the Ha (alternative hypothesis) is rejected.  

For boundary values, the reference t-Statistic is found in most college level textbooks.  However, the reference t-Statistic can also be viewed using tinv() function found in most computer spreadsheets.  For the LibreCalc spreadsheet, the tinv() function is given by formula =tinv(risk level,degrees of freedom).  To illustrate, a sample of 200 with a confidence level of 95%, we place  =tinv(0.05,198).  This gives a t-Statistic reference value of (1.972017).  The degrees of freedom is 200-2=198.  The risk level is 1 minus the confidence level of 95% (0.95) equals (0.05).

As a note, the t-Statistic of the Correlation Coefficient is equal to the t-Statistic of the Simple Regression Equation.  As such, the t-Statistic can be derived from the Simple Regression Model using the F-Statistic as F=t^2 or t-Statistic is the square root of the F-Statistic.


Friday, May 15, 2020

Simple Regression using SQL

A.  Calculating for the Slope

Given the following data in the MariaDB database table named "simreg":

XY
11234
21768
32234
42555
52800

Where X is the independent variable and Y is the dependent variable, the slope can be calculated using the following SQL command:

SELECT (sum((x - (select avg(x) from simreg)) * (y - (select avg(y) from simreg)))) / sum(power(x - (select avg(x) from simreg),2)) AS 'Slope' FROM simreg;

This query produces the following output:

Slope
391.9

The slope value of 391.9 is correct.  We can convert the query into an SQL function as follows:

DELIMITER |
CREATE FUNCTION c_slope() RETURNS DOUBLE
BEGIN
    DECLARE myres DOUBLE;
    SELECT (sum((x - (select avg(x) from simreg)) * (y - (select avg(y) from simreg)))) / sum(power(x - (select avg(x) from simreg),2)) INTO myres FROM simreg;
    RETURN myres;
END |
DELIMITER ;

Such will create the function c_slope() and can be queried using the command:

SELECT c_slope() AS 'Slope';


B.  Calculating for the Intercept

The Intercept can be calculated by issuing the following SQL command:

SELECT avg(y) - (sum((x - (select avg(x) from simreg)) * (y - (select avg(y) from simreg)))) / (sum((x - (select avg(x) from simreg))* (x - (select avg(x) from simreg)))) * avg(x) AS 'Intercept' FROM simreg;

This query produces the following output:

Intercept
942.5

The Intercept value of 942.5 is correct.  Such query can be converted into an SQL function as follows:

DELIMITER |
CREATE FUNCTION c_intercept() RETURNS DOUBLE
BEGIN
     DECLARE myres DOUBLE;
     SELECT avg(y) - (sum((x - (select avg(x) from simreg)) * (y - (select avg(y) from simreg)))) / (sum((x - (select avg(x) from simreg))* (x - (select avg(x) from simreg)))) * avg(x) INTO myres FROM simreg;
     RETURN myres;
END |
DELIMITER ;

Likewise, such will create the function c_intercept() and can be queried using the command:

SELECT c_intercept() AS 'Intercept';


C.  Forecasting

Given the Slope (b) and Intercept (a), the equation of the straight line is "Y = a + b X".  We can use this equation to forecast the value of Y by supplying the value of X.  To execute this in SQL, the command is:

SELECT c_intercept()+c_slope()*x AS 'Y Forecast' FROM simreg;

This query produces the following output:

Y Forecast
1334.4
1726.3
2118.2
2510.1
2902

If we want to have the forecast side by side with the original Y, we can just add the Y into the query as:

SELECT c_intercept()+c_slope()*x AS 'Y Forecast', y AS 'Y Original' FROM simreg;

This will produce the following output:

Y ForecastY Original
1334.41234
1726.31768
2118.22234
2510.12555
29022800

Likewise, if we want to get the forecast error, we can just modify the query a bit to deduct the 'Y Original' with the 'Y Forecast' as follows:

SELECT c_intercept()+c_slope()*x AS 'Y Forecast', y AS 'Y Original', (c_intercept()+c_slope()*x) - y AS 'Y Error' FROM simreg;

This will produce the following output:

Y ForecastY OriginalY Error
1334.41234100.40000000000009
1726.31768-41.700000000000045
2118.22234-115.80000000000018
2510.12555-44.90000000000009
29022800102


D.  Calculating the Mean Absolute Difference

We can calculate the Mean Absolute Difference (MAD) as a means to compare the "Simple Regression Method" with other forecasting methods.  The SQL command to get the MAD is:

SELECT avg(abs((c_intercept()+c_slope()*x) - y)) AS 'MAD' FROM simreg;

Which produces the output:

MAD
80.96000000000008


E.  Calculating the Root Mean Square Error

As an alternative the MAD, we can also get the Root Mean Square Error (RMSE) of the method.  The SQL command is:

SELECT SQRT(AVG(POWER((c_intercept()+c_slope()*x - y),2))) AS 'RMSE' FROM simreg;

Which produces the output:

RMSE
86.77407446927921


F.  Coefficient of Determination

The Coefficient of Determination, also commonly referred to as R-Squared, is the percentage of the variation in the dependent variable (Y) that can be explained by the independent variable (X).  The higher percentage and closer the value to 1, the better.  To be precise, R-Squared is the Regression Sum of Squares (RSS) over the Total Sum of Squares (TSS).

To calculate R-Squared, the following SQL functions needs to be created to facilitate the query.

a) Mean of X function

DELIMITER |
CREATE FUNCTION c_avex() RETURNS DOUBLE
BEGIN
DECLARE myres DOUBLE;
SELECT avg(x) INTO myres FROM simreg;
return myres;
END |
DELIMITER ;

b) Mean of Y function

DELIMITER |
CREATE FUNCTION c_avey() RETURNS DOUBLE
BEGIN
DECLARE myres DOUBLE;
SELECT avg(y) INTO myres FROM simreg;
return myres;
END |
DELIMITER ;

c)  Regression Sum of Squares (RSS) function

DELIMITER |
CREATE FUNCTION c_rss() RETURNS DOUBLE
BEGIN
DECLARE myres DOUBLE;
SELECT sum(power(((c_intercept()+c_slope()*x) - c_avey()),2)) INTO myres FROM simreg;
return myres;
END |
DELIMITER ;

d)  Total Sum of Squares (TSS) function

DELIMITER |
CREATE FUNCTION c_tss() RETURNS DOUBLE
BEGIN
DECLARE myres DOUBLE;
SELECT  sum(power(y-c_avey(),2)) INTO myres FROM simreg;
return myres;
END |
DELIMITER ;


e)  R-Squared function

DELIMITER |
CREATE FUNCTION c_r_squared() RETURNS DOUBLE
BEGIN
DECLARE myres DOUBLE;
SELECT c_rss()/c_tss() INTO myres; 
return myres;
END |
DELIMITER ;

After creating all these SQL functions, R-Squared can be calculated by issuing the following command:

SELECT c_r_squared() AS 'R-Squared';

This produces the following output:

R-Squared
0.9760733246803284


G.  Correlation Coefficient

The Correlation Coefficient is designated in Statistics by the symbol R and this is for a good reason.  It means that if we have already the Coefficient of Determination, which is designated by R-Squared, we can readily derive R by just squaring the value of the R-Squared.  Thus, we can create the following function in SQL to get R:

DELIMITER |
CREATE FUNCTION c_r() RETURNS DOUBLE
BEGIN
DECLARE myres DOUBLE;
SELECT sqrt( c_r_squared() ) INTO myres; 
return myres;
END |
DELIMITER ;


After creating the c_r() function, we can get the Correlation Coefficient by issuing the command:

SELECT c_r() AS 'Correlation';

This produces the output:

Correlation
0.9879642448615398


H.  Calculating the F Statistic

The F Statistic is the difference between the regression mean square (RMS) and the error mean square (EMS).  Since the RMS is equal to the regression sum of square (RSS), we just need to create a function to derive the EMS as follows:

DELIMITER |
CREATE FUNCTION c_ems() RETURNS DOUBLE
BEGIN
DECLARE myres DOUBLE;
SELECT sum(power(y-(c_intercept()+c_slope()*x),2)) / (count(x)-2) INTO myres FROM simreg; 
return myres;
END |
DELIMITER ;

After this, the function that will create the F Statistic can be created as:


DELIMITER |
CREATE FUNCTION c_f_stat() RETURNS DOUBLE
BEGIN
DECLARE myres DOUBLE;
SELECT c_rss()/c_ems() INTO myres; 
return myres;
END |
DELIMITER ;

Then, the following command to calculate the F Statistic can be invoke:

SELECT c_f_stat() AS 'F Statistic';

This produces the following output;

F Statistic
122.38319782621953


I.  The Student's t Statistic

In a simple regression equation, the relation between t and F statistic is that the square of the t Statistic is equal to the F Statistic.  Therefore, if we got the F, the t can be derived by just getting the square root of F.  This is undertaken by the following function:

DELIMITER |
CREATE FUNCTION c_t_stat() RETURNS DOUBLE
BEGIN
DECLARE myres DOUBLE;
SELECT sqrt(c_rss()/c_ems()) INTO myres; 
return myres;
END |
DELIMITER ;

The Student's t Statistic can be calculated by issuing the following command:

SELECT c_t_stat() AS 't Statistic';

This produces the following output:

t Statistic
11.062693967846148


J.  Putting it all together

To put it all together, we can create the procedure:

DELIMITER |
CREATE PROCEDURE c_simple_regression()
BEGIN
SELECT c_intercept() as 'Intercept',c_slope() as 'Slope',c_t_stat() as 't Stat',c_r_squared() as 'R Squared',c_r() as 'Correlation', c_f_stat() as 'F Stat';
END |
DELIMITER ;

and the simple regression statistics can be calculated by issuing the command:

CALL c_simple_regression();

which produces the output:

InterceptSlopet StatR SquaredCorrelationF Stat
942.5391.911.0626930.9760730.987964122.383197


Note:

The above-mentioned procedure has been tested in MariaDB 10.3 database.  My guess is, it should also work with MySQL, PostgreSQL and other SQL databases with little modification and with some differences in precision mathematics.



Wednesday, May 29, 2019

Simple Regression using SQL

A.  Calculating for the Slope

Given the following data in the MariaDB database table named "simreg":

XY
11234
21768
32234
42555
52800

Where X is the independent variable and Y is the dependent variable, the slope can be calculated using the following SQL command:

SELECT (sum((x - (select avg(x) from simreg)) * (y - (select avg(y) from simreg)))) / sum(power(x - (select avg(x) from simreg),2)) AS 'Slope' FROM simreg;

This query produces the following output:

Slope
391.9

The slope value of 391.9 is correct.  We can convert the query into an SQL function as follows:

DELIMITER |
CREATE FUNCTION c_slope() RETURNS DOUBLE
BEGIN
    DECLARE myres DOUBLE;
    SELECT (sum((x - (select avg(x) from simreg)) * (y - (select avg(y) from simreg)))) / sum(power(x - (select avg(x) from simreg),2)) INTO myres FROM simreg;
    RETURN myres;
END |
DELIMITER ;

Such will create the function c_slope() and can be queried using the command:

SELECT c_slope() AS 'Slope';


B.  Calculating for the Intercept

The Intercept can be calculated by issuing the following SQL command:

SELECT avg(y) - (sum((x - (select avg(x) from simreg)) * (y - (select avg(y) from simreg)))) / (sum((x - (select avg(x) from simreg))* (x - (select avg(x) from simreg)))) * avg(x) AS 'Intercept' FROM simreg;

This query produces the following output:

Intercept
942.5

The Intercept value of 942.5 is correct.  Such query can be converted into an SQL function as follows:

DELIMITER |
CREATE FUNCTION c_intercept() RETURNS DOUBLE
BEGIN
     DECLARE myres DOUBLE;
     SELECT avg(y) - (sum((x - (select avg(x) from simreg)) * (y - (select avg(y) from simreg)))) / (sum((x - (select avg(x) from simreg))* (x - (select avg(x) from simreg)))) * avg(x) INTO myres FROM simreg;
     RETURN myres;
END |
DELIMITER ;

Likewise, such will create the function c_intercept() and can be queried using the command:

SELECT c_intercept() AS 'Intercept';


C.  Forecasting

Given the Slope (b) and Intercept (a), the equation of the straight line is "Y = a + b X".  We can use this equation to forecast the value of Y by supplying the value of X.  To execute this in SQL, the command is:

SELECT c_intercept()+c_slope()*x AS 'Y Forecast' FROM simreg;

This query produces the following output:

Y Forecast
1334.4
1726.3
2118.2
2510.1
2902

If we want to have the forecast side by side with the original Y, we can just add the Y into the query as:

SELECT c_intercept()+c_slope()*x AS 'Y Forecast', y AS 'Y Original' FROM simreg;

This will produce the following output:

Y ForecastY Original
1334.41234
1726.31768
2118.22234
2510.12555
29022800

Likewise, if we want to get the forecast error, we can just modify the query a bit to deduct the 'Y Original' with the 'Y Forecast' as follows:

SELECT c_intercept()+c_slope()*x AS 'Y Forecast', y AS 'Y Original', (c_intercept()+c_slope()*x) - y AS 'Y Error' FROM simreg;

This will produce the following output:

Y ForecastY OriginalY Error
1334.41234100.40000000000009
1726.31768-41.700000000000045
2118.22234-115.80000000000018
2510.12555-44.90000000000009
29022800102


D.  Calculating the Mean Absolute Difference

We can calculate the Mean Absolute Difference (MAD) as a means to compare the "Simple Regression Method" with other forecasting methods.  The SQL command to get the MAD is:

SELECT avg(abs((c_intercept()+c_slope()*x) - y)) AS 'MAD' FROM simreg;

Which produces the output:

MAD
80.96000000000008


E.  Calculating the Root Mean Square Error

As an alternative the MAD, we can also get the Root Mean Square Error (RMSE) of the method.  The SQL command is:

SELECT SQRT(AVG(POWER((c_intercept()+c_slope()*x - y),2))) AS 'RMSE' FROM simreg;

Which produces the output:

RMSE
86.77407446927921


F.  Coefficient of Determination

The Coefficient of Determination, also commonly referred to as R-Squared, is the percentage of the variation in the dependent variable (Y) that can be explained by the independent variable (X).  The higher percentage and closer the value to 1, the better.  To be precise, R-Squared is the Regression Sum of Squares (RSS) over the Total Sum of Squares (TSS).

To calculate R-Squared, the following SQL functions needs to be created to facilitate the query.

a) Mean of X function

DELIMITER |
CREATE FUNCTION c_avex() RETURNS DOUBLE
BEGIN
DECLARE myres DOUBLE;
SELECT avg(x) INTO myres FROM simreg;
return myres;
END |
DELIMITER ;

b) Mean of Y function

DELIMITER |
CREATE FUNCTION c_avey() RETURNS DOUBLE
BEGIN
DECLARE myres DOUBLE;
SELECT avg(y) INTO myres FROM simreg;
return myres;
END |
DELIMITER ;

c)  Regression Sum of Squares (RSS) function

DELIMITER |
CREATE FUNCTION c_rss() RETURNS DOUBLE
BEGIN
DECLARE myres DOUBLE;
SELECT sum(power(((c_intercept()+c_slope()*x) - c_avey()),2)) INTO myres FROM simreg;
return myres;
END |
DELIMITER ;

d)  Total Sum of Squares (TSS) function

DELIMITER |
CREATE FUNCTION c_tss() RETURNS DOUBLE
BEGIN
DECLARE myres DOUBLE;
SELECT  sum(power(y-c_avey(),2)) INTO myres FROM simreg;
return myres;
END |
DELIMITER ;


e)  R-Squared function

DELIMITER |
CREATE FUNCTION c_r_squared() RETURNS DOUBLE
BEGIN
DECLARE myres DOUBLE;
SELECT c_rss()/c_tss() INTO myres; 
return myres;
END |
DELIMITER ;

After creating all these SQL functions, R-Squared can be calculated by issuing the following command:

SELECT c_r_squared() AS 'R-Squared';

This produces the following output:

R-Squared
0.9760733246803284


G.  Correlation Coefficient

The Correlation Coefficient is designated in Statistics by the symbol R and this is for a good reason.  It means that if we have already the Coefficient of Determination, which is designated by R-Squared, we can readily derive R by just squaring the value of the R-Squared.  Thus, we can create the following function in SQL to get R:

DELIMITER |
CREATE FUNCTION c_r() RETURNS DOUBLE
BEGIN
DECLARE myres DOUBLE;
SELECT sqrt( c_r_squared() ) INTO myres; 
return myres;
END |
DELIMITER ;


After creating the c_r() function, we can get the Correlation Coefficient by issuing the command:

SELECT c_r() AS 'Correlation';

This produces the output:

Correlation
0.9879642448615398


H.  Calculating the F Statistic

The F Statistic is the difference between the regression mean square (RMS) and the error mean square (EMS).  Since the RMS is equal to the regression sum of square (RSS), we just need to create a function to derive the EMS as follows:

DELIMITER |
CREATE FUNCTION c_ems() RETURNS DOUBLE
BEGIN
DECLARE myres DOUBLE;
SELECT sum(power(y-(c_intercept()+c_slope()*x),2)) / (count(x)-2) INTO myres FROM simreg; 
return myres;
END |
DELIMITER ;

After this, the function that will create the F Statistic can be created as:


DELIMITER |
CREATE FUNCTION c_f_stat() RETURNS DOUBLE
BEGIN
DECLARE myres DOUBLE;
SELECT c_rss()/c_ems() INTO myres; 
return myres;
END |
DELIMITER ;

Then, the following command to calculate the F Statistic can be invoke:

SELECT c_f_stat() AS 'F Statistic';

This produces the following output;

F Statistic
122.38319782621953


I.  The Student's t Statistic

In a simple regression equation, the relation between t and F statistic is that the square of the t Statistic is equal to the F Statistic.  Therefore, if we got the F, the t can be derived by just getting the square root of F.  This is undertaken by the following function:

DELIMITER |
CREATE FUNCTION c_t_stat() RETURNS DOUBLE
BEGIN
DECLARE myres DOUBLE;
SELECT sqrt(c_rss()/c_ems()) INTO myres; 
return myres;
END |
DELIMITER ;

The Student's t Statistic can be calculated by issuing the following command:

SELECT c_t_stat() AS 't Statistic';

This produces the following output:

t Statistic
11.062693967846148


J.  Putting it all together

To put it all together, we can create the procedure:

DELIMITER |
CREATE PROCEDURE c_simple_regression()
BEGIN
SELECT c_intercept() as 'Intercept',c_slope() as 'Slope',c_t_stat() as 't Stat',c_r_squared() as 'R Squared',c_r() as 'Correlation', c_f_stat() as 'F Stat';
END |
DELIMITER ;

and the simple regression statistics can be calculated by issuing the command:

CALL c_simple_regression();

which produces the output:

InterceptSlopet StatR SquaredCorrelationF Stat
942.5391.911.0626930.9760730.987964122.383197


Note:

The above-mentioned procedure has been tested in MariaDB 10.3 database.  My guess is, it should also work with MySQL, PostgreSQL and other SQL databases with little modification and with some differences in precision mathematics.



Tuesday, November 28, 2017

Simple Sample Size Formula

I have often been asked how to determine sample size in Statistics, while I always referred to Cochran [1963:1] for a complete explanation and Yamane [1967:2] for a simpler version of the formula, I discovered (an eureka moment!) that the existing published formula for sample size can still be further simplified.  Here is my simple sample size formula (Rasul Formula):

          Sample Size (n) = 1 / e^2

          where e is the margin of error (sampling error)

This simplified formula assumes an infinite population (or universe) and slightly greater than 95% confidence level.  The input requirement is only the margin of error expressed as a percentage.  If we assume further a 5% (.05) margin of error, the formula will give us a magic value of 400 (Rasul Sample), which can be interpreted as the sample size given infinite population at slightly more than 95% confidence level with 5% margin of error.

If you do not want to compute and was asked as to what sample size of proportion, the answer is 400 samples.

This simple formula also yields a simpler way of calculating the margin of error (e) given an actual sample number as follows (Rasul Margin of Error Formula):

          Margin of Error (e) = (1/n)^(1/2)

          or

          Margin of Error (e) is the square root of 1/n.

Let us say, you were able to get only 300 samples and you want to know what is your margin of error (e), applying the derived formula above will give you:

          e = (1/300)^(0.5)

          e = (0.003333)^(0.5)

          e = 0.057773 (or 6% rounded off to the nearest integer percent)

This tells us that even if we plan for a 5% margin of error, in real life situation, actual field condition (like peace and order situation) may not allow us to get the required sample size.  This particular formula can easily aid us in calculating the margin of error given the actual sample gathered.



Derivation

For those interested on how the formula was derived, here it is:

a)  Cochran's sample formula for proportions given infinite population (universe) is stated as,

          Sample Size (n) = ( Z^2 * p *q ) / e^2

          where:

                   Z is the abscissa of area of the normal curve
                   p is estimated proportion of an attribute that is present in the population
                   q is (1 - p )
                   e is the margin of error

b)  Remember that the Z score is the number of standard deviations a given proportion is away from the mean.  Given that, the desired confidence level and Z score relationship are as follows:

          Confidence Level          Z score

                     90%                         1.65
                     95%                         1.96
                     99%                         2.58


c)  Assuming the more widely acceptable confidence level of 95% and borrowing from the idea of Taro Yamane of approximating the Z score of 95% confidence by rounding it off to 2 instead of using 1.96, and

d)  Assuming further a more conservative proportion (p) of 50% (This is often the assumed value if we do not know the given proportion, which is most likely).  Therefore, p * (1 - p ) = 0.5 * (1-0.5) yields the value of 0.25,

e)  The formula can now can be stated as:

          n = [ 2^2 * (0.25) ]/e^2

          n = [ 4 * 0.25 ]/e^2


     Thereby producing the more simpler formula of:

          n = 1/ e^2

       








--------------------
References

[1]  Cochran, W.G. 1963. Sampling Techniques, 2nd Edition, New York.  John Wiley and Sons, Inc.

[2]  Yamane, Taro. 1967.  Statistics: An Introductory Analysis, 2nd Edition,  New York.  Harper and Row.


Thursday, August 12, 2010

Formula for the Intercept and Slope

Given a two (2) variable equation of a straight line, the formula for the slope is:

Slope (b) = S xy / S x^2

where:
S = summation
x = X minus the Mean of X
y = Y minus the Mean of Y


The formula for the intercept at its point of origin is:

Intercept (a) = M Y - b (M X)

where:
M = arithmetic mean
M Y = Mean of Y
b = Slope
M X = Mean of X