Search This Blog

Tuesday, November 13, 2012

Linear regression with Excel

Excel is a convenient tool, though often not so useful for science.

If you want to obtain a proper linear regression and r^2 values, don't use trend line from the GUI. Instead,
=index(linest(y's, x's, false, true),1) for slope
=index(linest(y's, x's, false, true),2) for y-intersect
=index(linest(y's, x's, false, true),3) for r^2

Thursday, November 8, 2012

Join multiple files

Join is a convenient text processor provided by Linux or other Unix-like systems. Join is for two files with a common field. Thus, joining two files are pretty straight forward:
join FILE1 FILE2
However, joining three files become non-trivial especially when you join on a field other than the first field. For example, in order to join as to the second field of files, you may try as follows:
join -j 2 FILE1 FILE2 | join -j 2 - FILE3
 The result would be an error because the output from the first join changes the position of the common field.  So, your second join should be:
join -j 2 FILE1 FILE2 | join -1 1 -2 2 - FILE3