Python Forum

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!