Python Forum
Showing trendline formula in a table per product
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Showing trendline formula in a table per product
#1
Information 
Hi all,

I have a Python script running in Power BI desktop that shows a scatter plot with a exponential and linear trendline. Also it shows the formula of the trendlines and the R-squared value. My dataset contains sales by temperature per product. Each product is part of a segment. If i select a segement in a Power BI slicer i get the scatter plot and trendline formulas and R-squared values for that specific segment.

But i want to see the trendline formula and R-squared value per segment in a table. So i can instantly see all the segments and their corresponding values.
I have the following code:

# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script: 

# dataset = pandas.DataFrame(average sales per day, temperature_2m_max °C)
# dataset = dataset.drop_duplicates()

# Paste or type your script code here:
import matplotlib.pyplot as plt
import pandas as pd 
import numpy as np

# Extract the x and y values from the dataset
x = dataset['temperature_2m_max °C']
y = dataset['average sales per day']

# Fit the data to an exponential curve
p = np.polyfit(x, np.log(y), deg=1)
a = np.exp(p[1])
b = p[0]

# Calculate the predicted y values
y_pred_exp = a * np.exp(b * x)

# Fit the data to a linear trendline
slope, intercept = np.polyfit(x, y, 1)
y_pred_lin = slope * x + intercept

# Calculate the R-squared value for the exponential trendline
ss_tot = np.sum((y - np.mean(y))**2)
ss_res_exp = np.sum((y - y_pred_exp)**2)
r_squared_exp = 1 - (ss_res_exp / ss_tot)

# Calculate the R-squared value for the linear trendline
ss_res_lin = np.sum((y - y_pred_lin)**2)
r_squared_lin = 1 - (ss_res_lin / ss_tot)

# Plot the data and the trendlines
fig, ax = plt.subplots(figsize=(8, 5))
ax.scatter(x, y, color='black')
ax.plot(x, y_pred_exp, color='green', label='Exponential Trendline')
ax.plot(x, y_pred_lin, color='blue', label='Linear Trendline')
ax.text(0.01, 0.8, f'Exponential Trendline: y = {a:.2f} * exp({b:.3f}x), R-squared: {r_squared_exp:.4f}', transform=ax.transAxes)
ax.text(0.01, 0.7, f'Linear Trendline: y = {slope:.2f}x + {intercept:.2f}, R-squared: {r_squared_lin:.4f}', transform=ax.transAxes)
ax.legend()

plt.show()
I can't figure out what the code should be for displaying a table with the values i want to see. Can some help me with this?

Thanks a lot!
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to add product details in exe generated by pyinstaller arex786 1 8,501 Oct-10-2021, 11:00 AM
Last Post: Sran012
  Python “Formula” Package: How do I parse Excel formula with a range of cells? JaneTan 1 2,691 Jul-12-2021, 11:09 AM
Last Post: jefsummers
  Largest product in a grid (projecteuler problem11) tragical 1 2,295 Sep-14-2020, 01:03 PM
Last Post: Gribouillis
  Blending calculator from final product xerxes106 0 1,624 Dec-05-2019, 10:32 AM
Last Post: xerxes106
  Make dual vector dot-product more efficient technossomy 3 2,543 Nov-28-2019, 09:27 PM
Last Post: Gribouillis
  Store a product/item in a inventory program viktoria_linn 1 4,094 Jul-02-2019, 09:26 PM
Last Post: DeaD_EyE
  Product expression. jarrod0987 1 2,368 Dec-13-2018, 11:32 AM
Last Post: buran
  Product of maximum in first array and minimum in second Thethispointer 9 5,310 Jan-19-2018, 07:38 PM
Last Post: Thethispointer

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020