Reproducing stats or verbose output from LINEST command in Excel or Google Sheet in Python

	#reproduces the "stats" or "verbose" output from LINEST in Excel or Google Sheets
	import numpy as np
	data = np.genfromtxt('data.csv', delimiter=',', names=True)
	x = data['x']
	y = data['y']
	n = len(y)
	dofreedom = n-2
	z, cov = np.polyfit(x,y,1,cov=True)
	p = np.poly1d(z)
	yp = p(x) #predicted y values based on fit
	slope = z[0]
	intercept = z[1]
	r2 = np.corrcoef(x,y)[0][1]**2
	regression_ss = np.sum( (yp-np.mean(y))**2)
	residual_ss = np.sum( (y-yp)**2 )
	slope_pm = np.sqrt(residual_ss / (dofreedom*np.sum((x-np.mean(x))**2)))
	intercept_pm = slope_pm*np.sqrt(np.sum(x**2)/n)
	s = np.sqrt(residual_ss/dofreedom)
	F = regression_ss/s**2
	print slope, intercept
	print slope_pm, intercept_pm
	print r2,s
	print F, dofreedom
	print regression_ss,residual_ss

view raw linest.py hosted with ❤ by GitHub

The python code above reproduces the output produced by the LINEST(y;x;true;true) command in Excel [LINEST(y,x,true,true) in Google Sheets] with a csv file as input.  In the csv file I have assumed that the x and y columns are labelled "x" and "y" respectively.  This page has a good explanation of the output (pdf).



This work is licensed under a Creative Commons Attribution 4.0