CODEWITHSUNDEEP
python
Hotpepper
Hotpepper

Reputation: 266

How do I recreate an Excel formula which calls TREND() in Python?

I was looking to recreate Excel's trend function in Python and found the C# answer (How do I recreate an Excel formula which calls TREND() in C#?). Using this, I recreated in Python. Thought I'd post in case it was useful to anyone else.

Upvotes: 3

Views: 1793

Answers (2)

antonshc
antonshc

Reputation: 51

Given function in Excel cell: =TREND(C1:C10;B1:B10;3.5), where C1:C10 = (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) and B1:B10 = (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

Using numpy.

import numpy as np

def trend(x, y, given_x):
    # Linear regression
    coefficients = np.polyfit(x, y, 1)

    # Predicted y-value for a given x-value
    predicted_y_for_given_x = np.polyval(coefficients, given_x)

    return predicted_y_for_given_x


x = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
y = [1, 4, 4, 5, 3, 1, 0, 4, 1, 2]
given_x = 3.5

test_trend = trend(x, y, given_x)
print(test_trend)  # 2.8515151515151516

Upvotes: 3

Hotpepper
Hotpepper

Reputation: 266

Python implementation:

def LeastSquaresValueAtX(points, knownX, xBar, x):
    '''
        Gets the value at a given x using the line of best fit
        (least square method) to determine the equation
    '''
    slope = SlopeOfPoints(points, knownX, xBar)
    yIntercept = YInterceptOfPoints(points, xBar, slope)
    return (slope * x) + yIntercept

def SlopeOfPoints(points, knownX, xBar):
    '''
        Gets the slope for a set of points using the formula:
        m=sum(x-avg(x)(y-avg(y))/sum(x-avg(x))**2
    '''
    yBar=dividend=divisor=0.0
    for i in points:
        yBar=yBar+i
    yBar=yBar/5
    for j in points:
        kx = knownX.pop()
        dividend+=((kx-xBar)*(j-yBar))
        divisor+=((kx-xBar)**2)
    return dividend / divisor      

def YInterceptOfPoints(points, xBar, slope):
    '''
        Gets the y-intercept for a set of points using the formula:
        b-avg(y)-m(avg(x))
    '''
    yBar =0.0
    for i in points:
        yBar=yBar+i
    yBar=yBar/5
    return yBar - (slope * xBar)

def test(knownX, t):
    if t==1: return LeastSquaresValueAtX([4,13,10,22,20],[2011,2010,2009,2008,2007],2009,2012)
    if t==2: return LeastSquaresValueAtX([7,20,26,29,23],knownX, 2009,2012)
    if t==3: return LeastSquaresValueAtX([6,5,4,3,7], knownX, 2009,2012)


print test([2011,2010,2009,2008,2007], 1)##26.1
print test([2011,2010,2009,2008,2007], 2)##33.3
print test([2011,2010,2009,2008,2007], 3)##5.0

Upvotes: 3

Related Questions