Shell Programming and Scripting

Hi,

Do anybody experience using awk or perl command to identify gradient of a straight line and The coefficient of determination/R-square value of a chart (R2) ?

Below is my input file :

Code:

t1 2 3 5 8
t2 0 2 0 2
t3 1 1 1 1
t4 50 70 80 90
.
.
.

Desired Output :

Code:

t1 2 3 5 8 0.6986 0.999
t2 0 2 0 2 0.1279 0.2237
t3 1 1 1 1 1 #N/A
t4 50 70 80 90 3.8813 0.9426
.
.
.

From the input file, column 1 is item that I wanna to calculate their corresponding gradient and The coefficient of determination/R-square value.
Column 2, 3, 4, 5 is the value at 0, 3, 6, 10 seconds.
Column 6 and 7 in the desired output file is gradient and The coefficient of determination/R-square value of item in column 1.

As I know that microsoft excel able to calculate the gradient of a straight line and The coefficient of determination/R-square value of a scatter lot (R2)
when we display the equation of chart and display R-square value of a chart.

Because I have a long list of item wanna to calculate the gradient and The coefficient of determination/R-square value of a chart (R2).
Thus I just curious whether anybody experience to calculate it through awk/perl command.

Thanks for any advice.

I'm assuming that you want the gradient of the best fit line. Is that correct?

Hi blakeoft,

Yup. You're right. I just wanna find the gradient of the best fit line. It might not necessary get exactly the same value as what I manual generated from microsoft excel.
As long as it able to generate the gradient of best fit line and R-square value are fine enough.

I have around 10k item wanna to calculate the gradient of best fit line and R-square value of each item.
I unable to manual do it one by one.
Thus hope that got other alternative way able to count it automatic.

Really thanks and appreciate your advice.

This will get you the slope of the least squares line.

Code:

>awk '{sumy=$2+$3+$4+$5} {sumxy=$3*3+$4*6+$5*10} {print $1,$2,$3,$4,$5,(4.0*sumxy-19*sumy)/(4.0*145-361)}' file.txt
t1 2 3 5 8 0.611872
t2 0 2 0 2 0.127854
t3 1 1 1 1 0
t4 50 70 80 90 3.88128

I'm not sure why our first slopes differs so much, but the slope of the third line is definitely zero. This awk line only works if you have four data points for each line. Otherwise, you'll need to make some modifications. Also, I don't know what the coefficient of determination is so I need to look it up. You might be able to do it yourself if you follow this example.

Hi blakeoft,

Many thanks. I will try it out now.
Really thanks and appreciate your help.

I will find it out for the R square

Sorry if this looks awful. It seems to work though.

Code:

>awk '{sumy=$2+$3+$4+$5} {sumxy=$3*3+$4*6+$5*10} {sumy2=$2*$2+$3*$3+$4*$4+$5*$5}
{denomy=(4.0*sumy2-sumy*sumy)} {slope=(4.0*sumxy-19*sumy)/(4.0*145-361)}
{if(denomy!=0) print $0,slope,(4.0*sumxy-19*sumy)*(4.0*sumxy-19*sumy)/((4.0*145-361)*denomy) ;
else print $0,slope,"#N/A"}' file.txt

Output:

Code:

t1 2 3 5 8 0.611872 0.976082
t2 0 2 0 2 0.127854 0.223744
t3 1 1 1 1 0 #N/A
t4 50 70 80 90 3.88128 0.942596

You might want to consider looking into R (I prefer to use R Studio as an interface). It's pretty easy to use and does stuff like this. Just something to think about if you have more work along the same lines.

Hi blakeoft,

Really many thanks for your assist.
The awk code looks awesome to me.

I'm really appreciate your help.
I will "digest" the meaning of your awk code.
Thanks again and a lot.

It really useful for me