Today I Learnt - Data Engineering Diaries

Conditional Colors in Plotly Tables

Problem

Generate a data table in Plotly that has the following features:

Alternating cell and line colors for odd/even rows
Unique cell color on first column
For third column onwards, color cells using two different colors based on two levels of upper-bound/lower-bound conditions

What I did

Step 1: Define Pandas DataFrame to transform into Plotly Table

Let's go back to the table that was created in the previous note:

:::python

>> df_unstack

                           value         Total
Buyer                      BU1    BU2
Vendor variable
A      Count               1.0    2.0    3.0
       Total Amount ($)    1.0   15.0   16.0
B      Count               NaN    2.0    2.0
       Total Amount ($)    NaN   60.0   60.0
C      Count               1.0    3.0    4.0
       Total Amount ($)  103.0  262.0  365.0
D      Count               1.0    1.0    2.0
       Total Amount ($)   30.0   23.0   53.0
E      Count               2.0    3.0    5.0
       Total Amount ($)  179.0  171.0  350.0

The DataFrame has the following hierarchial indexing properties: 2 levels of column indices and 2 levels of row indices.

For the column indices:

:::python

>> df_unstack.columns

MultiIndex([('value', 'BU1'),
        ('value', 'BU2')],
       names=[None, 'Buyer'])

For the row indices:

:::python

>> df_unstack.index

MultiIndex([('A',            'Count'),
            ('A', 'Total Amount ($)'),
            ('B',            'Count'),
            ('B', 'Total Amount ($)'),
            ('C',            'Count'),
            ('C', 'Total Amount ($)'),
            ('D',            'Count'),
            ('D', 'Total Amount ($)'),
            ('E',            'Count'),
            ('E', 'Total Amount ($)')],
           names=['Vendor', 'variable'])

Step 2: Create backend table for metric used in the conditions

For example, I would like to use a ratio of two variables as one of the metrics for the upper-bound/lower-bound conditions. As the ratio is not explicitly displayed on the data table, I create a backend table to store the ratios.

:::python

def pv_po_ratio_analysis(df_table, column_level1_agg):

    po_count_df = df_table[
                    df_table['Item'] == 'Count'
                    ].set_index(
                        [column_level1_agg, 'Item']
                        ).reset_index('Item'
                                    ).drop(columns='Item')

    total_pv_df = df_table[
                    df_table['Item'] == 'Total PV ($)'
                    ].set_index(
                        [column_level1_agg, 'Item']
                        ).reset_index('Item'
                                    ).drop(columns='Item')

    pv_po_ratio_df = total_pv_df.div(po_count_df, fill_value=0)

    return pv_po_ratio_df

Step 3: Define conditions for conditional formatting of cell colors

For this example, I would like to color cells using two different colors based on two levels of conditions:

PO Count >= T1
PV/PO ratio < T2 (pink) or PV/PO ratio > T3 (yellow)

Criteria no. 1 on PO Count needs to be fulfilled before traversing down to criteria 2 on PV/PO ratio. To obtain the boolean DataFrames that represent whether the value in each cell fulfils the less-than or more-than conditions for the metrics (in this case, PO Count and PV/PO ratio), we first define the DataFrames that represent these metrics.

:::python

# define DataFrame for the metrics PO Count, PV and PV/PO ratio

po_count_df = df_table[
                df_table['Item'] == 'Count'
                ].set_index(
                    [column_level1_agg, 'Item']
                    ).reset_index('Item'
                                  ).drop(columns='Item')

pv_df = df_table[
                df_table['Item'] == 'Total PV ($)'
                ].set_index(
                    [column_level1_agg, 'Item']
                    ).reset_index('Item'
                                  ).drop(columns='Item')

pv_po_ratio_df = pv_po_ratio_analysis(df_table, column_level1_agg)

After the metric DataFrames are initialized, we make use of pandas-defined binary operator functions to apply boolean operations to the value in each cell.

:::python

# set criterion using binary operators

is_po_count_gt_threshold = po_count_df.ge(30)

is_pv_po_ratio_lt_threshold1 = pv_po_ratio_df.lt(2000)

is_pv_po_ratio_gt_threshold2 = pv_po_ratio_df.gt(5000)

Step 4: Transform Pandas MultiIndex DataFrame into Plotly-compatible format

Since we would like to color the Count and Total Amount cells for each Buyer-Vendor combination based on the conditions, we use the Index.repeat() function in pandas to repeat each Index associated with the Vendor for the boolean DataFrames and reindex the DataFrames with the repeated Indexes.

:::python

# Repeat rows
is_po_count_gt_threshold_repeat = \
    is_po_count_gt_threshold.reindex(
        is_po_count_gt_threshold.index.repeat(2))

is_pv_po_ratio_lt_threshold1_repeat = \
    is_pv_po_ratio_lt_threshold1.reindex(
        is_pv_po_ratio_lt_threshold1.index.repeat(2))

is_pv_po_ratio_gt_threshold2_repeat = \
    is_pv_po_ratio_gt_threshold2.reindex(
        is_pv_po_ratio_gt_threshold2.index.repeat(2))

To transform the resulting boolean DataFrames into Plotly-compatible format, we need to understand how Plotly interprets and outputs table values.

According to the documentation for Tables in Python with Plotly, the data are arranged in a grid of rows and columns and the grid is represented as a vector of column vectors (which is known as a column-major order). Due to limitations in displaying MultiIndex DataFrames in Table format with Plotly, the original MultiIndex DataFrame has to be manipulated in a way that allows Plotly to read the header and cell values in the DataFrame.

When cell values for a Table object is defined in the following manner:

:::python

fig = go.Figure(data=[go.Table(
    header=dict(
        values=['A Scores', 'B Scores']),
    cells=dict(
        values=[[100, 90, 80, 90], [95, 85, 75, 95]]))
    ])

fig.show()

the Table object would be displayed column-wise in the following manner:

|  A Scores   |  B Scores   |
|:-----------:|:-----------:|
|     100     |     95      |
|     90      |     85      |
|     80      |     75      |
|     90      |     95      |

Column-major order also applies for header values, as illustrated by the example below:

:::python

fig = go.Figure(data=[go.Table(
    header=dict(
        values=[['Scores', 'A'], ['Scores','B']]),
    cells=dict(
        values=[[100, 90, 80, 90], [95, 85, 75, 95]]))
    ])

fig.show()

The Table object generated from the code snippet above would be displayed in the following manner:

|   Scores    |   Scores    |
|:-----------:|:-----------:|
|      A      |      B      |
|:-----------:|:-----------:|
|     100     |     95      |
|     90      |     85      |
|     80      |     75      |
|     90      |     95      |

To represent the row levels in the MultiIndex, the row levels in the DataFrame are defined as a list of lists having the same length as the number of row levels in the DataFrame.

Understanding how the header and cell values are defined in column-major order in Tables with Plotly is important, as the same column-major order is also used in specifying the styling for header and individual cells.

In the case of the boolean DataFrames generated from the earlier steps, list of values in DataFrames are typically read in row-major order i.e. df.loc[row_idx,column_idx] or df.iloc[row_num,column_num]. Hence, we would have to transform the DataFrames so that the list of values from the DataFrames follow the column-major order that Plotly uses to read table values i.e. values=[df.column1, df.column2]. Since column names for Buyer may vary, we transpose the DataFrames from row-major order to column-major order such that Plotly reads the values in column-major order.

:::python

# Transpose tables so that list of values follow
# how Plotly outputs table values
is_po_count_gt_threshold_repeat_T = \
    is_po_count_gt_threshold_repeat.transpose().values.tolist()

is_pv_po_ratio_lt_threshold1_repeat_T = \
    is_pv_po_ratio_lt_threshold1_repeat.transpose().values.tolist()

is_pv_po_ratio_gt_threshold2_repeat_T = \
    is_pv_po_ratio_gt_threshold2_repeat.transpose().values.tolist()

Step 5: Define conditional coloring of table cells

Finally, we define the conditional coloring of table cells, with alternating colors for odd and even rows if the table cell does not fulfil the conditional coloring conditions. For readability, the 'Total' column has been excluded from the conditional coloring and is instead colored with alternating colors for odd and even rows.

:::python

def cond_to_color_cells(po_count_bool_array,
                    pv_po_ratio_high_bool_array,
                    pv_po_ratio_low_bool_array,
                    i, j):

    rowEvenColor = 'lavender'
    rowOddColor = 'white'
    rowPVPOratioUpperBoundColor = 'hotpink'
    rowPVPOratioLowerBoundColor = 'yellow'

    color = None

    if i % 2:
        color = rowOddColor
    else:
        color = rowEvenColor
    if po_count_bool_array[j][i] != 0  and j != (len(po_count_bool_array)-1):
        if pv_po_ratio_high_bool_array[j][i] != 0:
            color = rowPVPOratioUpperBoundColor
        elif pv_po_ratio_low_bool_array[j][i] != 0:
            color = rowPVPOratioLowerBoundColor

    return color

Today I Learnt - Data Engineering Diaries

Other articles

Dataframe manipulation sequence - GroupBy Agg, Melt, Unstack

Problem

MultiIndex.to_frame()

Problem

What I did

MultiIndex.set_levels() in pandas

Problem

Other articles

Problem

Problem

What I did

Problem

links

social