Forum Discussion

carlosxxss's avatar
carlosxxss
Regular Visitor
3 years ago

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

    • carlosxxss's avatar
      carlosxxss
      Regular 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:

      SegmentExponential formulaLinear formulaExponential R-squaredLinear 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

  • YG0n's avatar
    YG0n
    New 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

     

    • lbendlin's avatar
      lbendlin
      Super User

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