How do I replicate Excel's "Power Trendline" in Python?

Question

How can I recreate the "Excel Power Trendline" in Python and acquire the coefficients?

In Excel, this data...

x = [5.5, 6.0, 6.5, 7, 9]
y = [64.0575, 69.656, 75.781, 82.7023, 111.156866]

...creates a trendline that produces the regression formula:

y = 9.2347 * (x ^ 1.1294)

I would like to do this in Python so I can utilize the coefficients from the formula later on in my software.

Thanks!

Answer 1

I realize this question is 2 years, 3 months old currently but the current answers are not complete. Here is a full answer.

You have to know the structure of a power formula first.

y = some_number*x^(-another_number)

Example:

from scipy.optimize import curve_fit
import matplotlib.pyplot as plt

x = [5.5, 6.0, 6.5, 7, 9]
y = [64.0575, 69.656, 75.781, 82.7023, 111.156866]
popt, pcov = curve_fit(lambda fx,a,b: a*fx**-b,  x,  y)
power_y = popt[0]*x**-popt[1]

plt.scatter(x, y, label='actual data')
plt.plot(x, power_y, label='power-fit')
plt.legend()
plt.show()

Produces the following chart:

A better example to illustrate a power curve might be:

x = [5.5, 6.0, 6.5, 7, 9]
y = [100, 80, 40, 10, 5]
popt, pcov = curve_fit(lambda fx,a,b: a*fx**-b,  x,  y)
power_y = popt[0]*x**-popt[1]

plt.scatter(x, y, label='actual data')
plt.plot(x, power_y, label='power-fit')
plt.legend()
plt.show()

If you want to show a smooth line instead of a jagged one, import numpy and do this:

# make the line smooth instead of jagged
import numpy as np

x = [5.5, 6.0, 6.5, 7, 9]
y = [100, 80, 40, 10, 5]
popt, pcov = curve_fit(lambda fx,a,b: a*fx**-b,  x,  y)
x_linspace = np.linspace(min(x), max(x), 100)
power_y = popt[0]*x_linspace**-popt[1]

plt.scatter(x, y, label='actual data')
plt.plot(x_linspace, power_y, label='smooth-power-fit')
plt.legend()
plt.show()

The key to all of this is to find the right coefficients using scipy.optimize.curve_fit and knowing the structure of a power function mathematically.

Answer 2

Without knowing exactly what 'Power Trendline' is, I would assume that you could solve your problem with the following:

x = [5.5, 6.0, 6.5, 7, 9]
y = []

def formula(arg):
    return (9.2347 * (arg ** 1.1294))

for number in x:
    y.append(formula(x))

Answer 3

Create a function to define the equation you'd like to fit to, then use curvefit(funx, x, y) from scipy to obtain the fit values based on your x and y arguments.

def func(x, C, m): return C*x**m

popt, pcov = scipy.optimize.curve_fit(func,  x,  y)

popt will return the C and m values in func, pcov I believe is the bounds but I'm not too sure.

Hope this helps a bit.

Answer 4

To replicate exactly excel power trendline in python you must do as following:

import numpy  as np
def power_fit(x, y):
  
  coefs = np.polyfit(np.log(x),np.log(y),1)
  a = np.exp(coefs[1])
  b = coefs[0]

  return a*x**b

Although the other methods presented here being excellent, none of them faithfully replicate the excel method