8. Building the First Data Table
The first data table in the dashboard presents metrics such as spend (the cost associated with a given advertising product), website sessions, bookings (transactions), and revenue. These metrics are aggregated depending upon the dates selected in the date selected and typically present data for the current year for the date range selected, data for the previous corresponding period, data for last year, as well as percent and absolute differences between these periods.
Changing the Dates Presented in the Data Table based upon the Date Selection
The main functionality I wanted for the first data table is making it interactive, whereby the data presented changes according in the date selected. To start with, the data table element needs to be included in the
layouts.py file as in the (simplified) code below:
The different parameters for the data table include:
id: identifier so that the data table can be referenced by the callbacks.
columns: the columns presented in the table;
deletablegives the ability of the user to delete the column while using the app.
editable=Truegives the user the ability to change the data in the table while using the app.
n_fixed_columns=2freezes the first two columns (the checkboxes and the placement type in our case), so when a user scrolls across the complete data table, the user can still view the check boxes and the placement type.
style_tableallows CSS styles to be applied to the table.
row_selectable='multi'allows the user to select multiple rows via the checkboxes. This will enable updating of the graphs below based upon the selection.
selected_rows=0contains the index of the rows that are selected initially (and presented in the graphs below).
style_cellallows CSS styles to be applied to the table cells.
I will add additional parameters in a subsequent step so we can have conditional style formatting. Number formatting such as dollar signs, commas, and percent signs are added in pandas and defined as a series of formatters.
I should note that I do not specify the
data parameter for the data table in the
layouts.py file. Rather, the
data parameter for the data table will come from the callback:
Calculating Changes in the Metrics, as well as the calculating cost-per-session, conversion rate, and cost-per-acquisition.
Since we need to calculate changes in the metrics, as well as CPA, CPS, and conversion rate depending upon the dates selected “on-the-fly,” I push these calculations into a separate file,
functions.py. I can then re-use these functions for the different Dashboard pages. I also define formatting functions in this file:
The functions for both the first data table and the second data table are similar so I only present one here:
The flow between the data table element, the callback and the function can be portrayed as:
A method to select either a condensed data table or the complete data table.
One of the features that I wanted for the data table was the ability to show a “condensed” version of the table as well as the complete data table. Therefore, I included a radio button in the
layouts.py file to select which version of the table to present:
The callback for this functionality takes input from the radio button and outputs the columns to render in the data table:
This callback is a little bit more complicated since I am adding columns for conditional formatting (which I will go into below). Essentially, just as the callback below is changing the data presented in the data table based upon the dates selected using the callback statement,
Output('datatable-paid-search', 'data', this callback is changing the columns presented in the data table based upon the radio button selection using the callback statement,
Conditionally Color-Code Different Data Table cells
One of the features which the stakeholders wanted for the data table was the ability to have certain numbers or cells in the data table to be highlighted based upon a metric’s value; red for negative numbers for instance. However, conditional formatting of data table cells has three main issues.
- There is lack of formatting functionality in Dash Data Tables at this time.
- If a number is formatted prior to inclusion in a Dash Data Table (in pandas for instance), then data table functionality such as sorting and filtering does not work properly.
- There is a bug in the Dash data table code in which conditional formatting does not work properly.
I ended up formatting the numbers in the data table in pandas despite the above limitations. I discovered that conditional formatting in Dash does not work properly for formatted numbers (numbers with commas, dollar signs, percent signs, etc.). Indeed, I found out that there is a bug with the method described in the Conditional Formatting — Highlighting Cells section of the Dash Data Table User Guide:
The cell for New York City temperature shows up as green even though the value is less than 3.9.* I’ve tested this in other scenarios and it seems like the conditional formatting for numbers only uses the integer part of the condition (“3” but not “3.9”). The filter for Temperature used for conditional formatting somehow truncates the significant digits and only considers the integer part of a number. I posted to the Dash community forum about this bug, and it has since been fixed in a recent version of Dash.
*This has since been corrected in the Dash Documentation.
Conditional Formatting of Cells using Doppelganger Columns
Due to the above limitations with conditional formatting of cells, I came up with an alternative method in which I add “doppelganger” columns to both the pandas data frame and Dash data table. These doppelganger columns had either the value of the original column, or the value of the original column multiplied by 100 (to overcome the bug when the decimal portion of a value is not considered by conditional filtering). Then, the doppelganger columns can be added to the data table but are hidden from view with the following statements:
Then, the conditional cell formatting can be implemented using the following syntax:
Essentially, the filter is applied on the “doppelganger” column,
Revenue_YoY_percent_conditional (filtering cells in which the value is less than 0). However, the formatting is applied on the corresponding “real” column,
Revenue YoY (%). One can imagine other usages for this method of conditional formatting; for instance, highlighting outlier values.
The complete statement for the data table is below (with conditional formatting for odd and even rows, as well highlighting cells that are above a certain threshold using the doppelganger method):
I describe the method to update the graphs using the selected rows in the data table below.