Power Bi with Python -Heatmap

[kilala]

Hi all. For my learning purpose, I need to create Heatmap chart using Python in Power BI. Supoosedly, this heatmap measures the relationship for sales by state. however, when I tried to run it, it shows state by state. How do I correct this?

(p/s: I'm still beginner in both python and power bi)

this is my python script:

# Paste or type your script code here:
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
fig, ax = plt.subplots(figsize=(10,6))
sns.heatmap(dataset.corr(), center=0, cmap= 'coolwarm')
ax.set_title('Sales By State')
plt.show()

If anyone can help, I would totally appreciate it!

Thanks!

[PhilipTreacy]

Hi @kilala 

Yes you can do that but you need to supply the sales data.  Have you got such a dataset?

Regards

Phil

[kilala]

Hi,

Yes I have daily data sales for 3 years. So, basically I have Order_Year, Sales and State. 

[PhilipTreacy]

Hi @kilala 

So how do you want to view that data?  A heatmap with daily data for 3 years and 50(?) states will look incredibly busy.

Are you ok to create this heatmap or do you need assistance?

regards

Phil

[kilala]

Hi @PhilipTreacy ,

Basically, there are around 11 states only, and 3 years sales data.

I want to display data sum of sales data for every year by state. the colour density should be based on the sales amount.

The higher the amount, the darker the colour.

Can we achieve this using heatmap? Hope that you can assist me!

What I have planned to do is

[PhilipTreacy]

@kilala 

Can you please supply the data, can't do anything without it.

Any reason this has to be done with Python?  There are Heatmap visuals available natively in PBI.

Phil

[kilala]

Hello @PhilipTreacy , I have tried to upload the dataset here but it said that excel and .pbix is not supported. So, here I give some sample. Please let me know if there is any other way for me to share the data with you. Thanks!

[PhilipTreacy]

Hi @kilala 

You can upload the file to OneDrive or Dropbox and share a link to that.

Does this have to be done in Python?  Why not use one of the in-built Heatmap visuals?

Regards

Phil

[kilala]

Hi, @PhilipTreacy , here is the link:

https://drive.google.com/file/d/14O7wd9cwh5IxrORW6Gj5ZfHFJOsZXOWL/view?usp=sharing

Actually, it is for my learning purpose. I want to know more on what type of visualisation we can do using python in Power BI. I have tried to look up online but there is no further reference on this. Really appriciate your help! 

[PhilipTreacy]

Hi @kilala 

There's no data in your code to plot.  The code references a dataset ( dataset.corr() ) but hasn't defined that data.

If you use this code which has a dataset generated using np.random.rand()

import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
fig, ax = plt.subplots(figsize=(10,6))
dataset = np.random.rand(10, 12)
sns.heatmap(dataset, center=0, cmap= 'coolwarm')
ax.set_title('Sales By State')
plt.show()

You should end up with this heatmap

Regards

Phil

[kilala]

Hi @PhilipTreacy , thanks for helping!

I have tried it and it works. But, if possible, i dont want the dataset to be random. i'm thinking to put sales as y-axis, and state as x-axis.  some sort like this:

do you have any idea to do so?