Showing table with python containing trendline formula from scatter plot

[carlosxxss]

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 segment 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 for the scatter plot:

# 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. The table should look something like this:

• Column A: name of segment
• Column B: linear trendline formula
• Column C: linear R-squared value
• Column D : exponential trendline formula
• Column E: exponential R-squared value

Can someone help me with this?
@python @pythonia 

[YG0n]

In your Python script, how do you refer to actual columns/data already loaded in your pbix?

I have loaded some data from SQL and I cant find a way to use them as df

[lbendlin]

you add the desired columns to the values area of the Python visual. That will automatically include them in the dataframe.

[lbendlin]

The Python visual MUST plot something. If you want a table, plot a table. pandas.plotting.table — pandas 2.0.1 documentation (pydata.org)

[carlosxxss]

Hi Ibendlin. Thanks this points me in the right direction but not exactly what i wanted. I created the table below. But i want to move the exponential en linear trendline to the columns and on the rows i want the different segments.

What i have now:

What i want:

Segment	Exponential formula	Linear formula	Exponential R-squared	Linear R-squared
Segment 1
Segment 2

I have a sample dataset in this onedrive link: https://1drv.ms/u/s!AotJBlCCCXxigpNv7xxhBpxVSxeUYQ?e=xY5PJh 

I can't figure out this next step