Forum Discussion
Showing table with python containing trendline formula from scatter plot
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
4 Replies
- lbendlinSuper User
The Python visual MUST plot something. If you want a table, plot a table. pandas.plotting.table — pandas 2.0.1 documentation (pydata.org)
- carlosxxssRegular Visitor
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
- YG0nNew Member
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- lbendlinSuper User
you add the desired columns to the values area of the Python visual. That will automatically include them in the dataframe.