Colorize background cell color mapped by column value

Question

I am trying to map colors to cells in a table. However, I want this to be called from values in a separate column. Specifically, for the exmaple below, I'm plotting time values from Place. However, I have a separate column named Code that is a reference for these time values. I'm hoping to map a separate color for each unique value in Code to differentiate between time values.

At the moment, I'm manually inserting a separate color for appropriate time values. I'm hoping to generate a more flexible function that uses a colormap and can handle varying values in Code.

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import six

df = pd.DataFrame({
    'Place' : ['Johnathon Santiago-Guillermo','Alan','Cory','Jim','Johnathon Santiago-Guillermo','Alan','Cory','Jim'],                                
    'Number' : ['1','3','5','6','2','4','6','7'],          
    'Code' : ['1','2','3','4','1','2','3','4'],                      
    'Time' : ['1904-01-01 08:00:00','1904-01-01 09:00:00','1904-01-02 01:00:00','1904-01-02 02:00:00','1904-01-01 08:10:00','1904-01-01 09:10:00','1904-01-02 01:10:00','1904-01-02 02:10:00'],                           
    })

df['Time'] = pd.to_datetime(df['Time'])
df = df.sort_values('Time')
df['Time'] = pd.DatetimeIndex(df['Time']).time

df1 = df.pivot_table(index = 'Number', columns = 'Place', values = 'Time', 
        aggfunc = 'first').fillna('')

df1 = df1.reindex(columns = df['Place'].unique())

fig, ax = plt.subplots(figsize = (20, 20))

def Sheet(ax1):
    ax1.axis('off')
    ax1.axis('tight')
    Times = ax1.table(cellText = df1.values, colLabels = df1.columns, cellLoc='center',
             bbox = [0,0,1,1])

    Times.auto_set_font_size(False)
    Times.set_fontsize(5)

ax1 = plt.subplot2grid((2,3), (0,0), colspan = 3)   

Sheet(ax1)

plt.show()

Answer 1

I played with your function a bit and pass a cmap='tab10'. But first, you probably want to make sure that the color code is of the same shape as the time:

new_df = df.pivot_table(index='Number', 
                        columns='Place', 
                        aggfunc='first', 
                        fill_value='')

# data
df1 = new_df['Time'].reindex(columns = df['Place'].unique())

# codes
df2 = (new_df['Code'].reindex(columns = df['Place'].unique())
           .replace('',0)
           .astype(int)
      )

Now the change in the function:

def render_mpl_table(data, col_width = 1, row_height = 0.3, font_size = 6,
                     header_color='black', row_colors= 'black', edge_color='black',
                     bbox=[0, 0, 1, 1], header_columns=0,
                     cmap=None,
                     ax=None, **kwargs):

    ### blah
    ### ...
    ### here comes the change
    ### remove everything after
    # Unique values in code

    # define the cmap
    # maybe checking if the cmap is continuous or discreet
    if cmap is None: cmap='tab10'
    cm = plt.cm.get_cmap('tab10')

    # map the codes to the colors
    colours = np.array(cm.colors)[df2.values]

    # Set face colour of cell based on value in Code
    for c, cell in mpl_table._cells.items():
        if  (c[0]==0                           # header
            or c[1] == -1                      # index
            or df2.values[c[0]-1, c[1]]==0):   # 0 - code or empty cell

            continue

        cell.set_facecolor(colours[c[0]-1, c[1]])

    mpl_table.auto_set_font_size(False)
    mpl_table.set_fontsize(font_size)   

Output