0

I am quite new to python and I am looking for a way to have a visual representation of my field values. Like a grid heatmap.

I have an excel file (that I import with pd.read_excel) that looks like this:

# X Y Score

1 1 1 44

2 2 1 37

3 3 1 0

4 3 2 100

And so on.

Excel file

I know there is a way to use 2d numpy array into a grid but have not found how to do it with my excel file.

I want something that will look like this at the end

Output

Can someone help me please?

Thank you in advance

Nicolas Peille
  • 149
  • 1
  • 2
  • 10

2 Answers2

0

Answered here. It uses the Excel's conditional formatting feature using Xlsx Writer. See here as well.

James Sarlo
  • 1
  • 1
  • 2
0

Maybe you're looking for something like this (you can change the colormap if you like):

import pandas as pd
import matplotlib.pyplot as plt

df = pd.read_excel('test.xlsx')
nrows = 2
ncols = 2

# Main Plot
score = np.array(df["Score"]).reshape(nrows,ncols)
plt.matshow(score,cmap=plt.cm.jet)

# Grid Labels
row_labels = range(1,nrows+1)
col_labels = range(1,ncols+1)
plt.xticks(range(ncols), col_labels)
plt.yticks(range(nrows), row_labels)

# Write Score
for i in range(ncols):
    for j in range(nrows):
        print(score[i,j])
        plt.text(i, j, score[i,j], va='center', ha='center',color='white',size='20',weight='bold')

enter image description here

imricardoramos
  • 846
  • 1
  • 7
  • 12
  • Thank you for the answer :) Is there a way to use a gradual color for the score, for example, low score red, then up to medium turns orange and to high green? Thank you in advance – Nicolas Peille Jan 17 '19 at 15:11
  • You can change the colormap in the line `plt.matshow(score,cmap=plt.cm.jet)` You can checkout more about colomaps here: https://matplotlib.org/tutorials/colors/colormaps.html – imricardoramos Jan 18 '19 at 00:43