Provide a data table view usable via JavaScript

[sheikirfan]

No description provided.

[etpinard]

It is possible. A plotly.py table is simply a regular heatmap trace with judiciously placed text annotations. But, as you pointed out, we don't expose any figure factories in plotly.js.

That said, with the new plotly.js transform plugin functionality, we would pretty easy to convert the plotly.py logic in JS as a transform module.

[etpinard]

Anyone interested in doing this may want to look at #1020 first for inspiration.

[monfera]

Description

There's a need for tables in the context of crossfiltering. This ticket is repurposed to

• clarify and confirm needs for tables
• consider pros/cons of approaches (e.g. as a new plot/trace type in plotly.js, or an abstraction that uses existing facilities e.g. heatmap and transform)
• decide on direction and finalize initial requirements (Minimum Viable Cathedral)

Context

We're working on crossfiltering functionality. On an initial glance, it's just a way of letting widgets constrain dimensions and make the other widgets rerender. But there's a lot more to it - to be described elsewhere, except for the fact that showing tables is common on crossfiltered views.

• Mike Bostock's example with his crossfilter.js package
• some dashboards made with dc.js
• stated user needs

The need that a table work well with crossfiltering already poses some demands, and there are general requirements toward a table widget as well.

Table functionality needed for, or implied by crossfiltering

There are some direct, often obvious needs, such as:

• take tabular (multidimensional) data and render them with dimensions in one direction (usually, columns) and samples, or records, in another (usually, rows)
• support styling options, e.g. row pitch
• have a clear approach for overflow (e.g. scrollable table)
• rerender efficiently if the set of records changes

As part of a crossfiltered dashboard, it may be necessary to show aggregate items rather than atomic details. It applies for all plots though, eg. barcharts that show total by country. It is thus expected that crossfilter will provide data transformations such as filtering (obv.), grouping and aggregation. Similarly, top N / bottom N records are often required, but it's arguably the role of the upstream data transformations rather than the view to pick top/bottom N records from a large dataset. For example, crossfilter.js provides functionality for such ranking.

Crossfiltered tabular data, in principle, may contain a lot of records, for the purpose of crossfiltering is very often an affordance toward data exploration on large, multidimensional datasets. Thousands to hundreds of thousands of records are not uncommon even in the browser. On the other hand, screen size is always limited. Therefore,

Question 1: does a table view need to be immediately, or in the future, be able to display any and all records, or can a row count limit be established? It is possible to make essentially infinite scrollers, but there are some tradeoffs incl. development time, or e.g. the use of canvas means no text copying etc.. These tradeoffs can be worked around, providing alternative rendering approaches, but that increases complexity, so it's useful to know the OOM row count and OOM column count.

When filtering on other widgets, the table needs to be updated, and, previously shown records may stay on in a different position, or rows may appear/disappear, therefore:

Question 2: Is there a current, or eventual need for tweening according to the enter/update/exit lifecycle, e.g. fading in/out rows and smoothly transitioning rows that merely change location eg. in a top 10 list? (click and hold columns)

There are crossfilter-relating functions that are not critical for a MVP but could be useful, therefore:

Question 3: Should the user be able to select one record, a contiguous block of records, discontiguous records (and which of these) in order to filter the currently shown dataset on the dashboard? E.g. brush 10 rows, then show only these 10 points (and their aggregates) on other views.

Question 4: Do we need selection of dimension value by example? E.g. right-click on a specific cell that has 'Canada', and click 'Keep (or Exclude) records where country is 'Canada') - it's not just extra interaction, but plotly.js doesn't have context menus so far, and alternative affordances might look odd, so ideas welcome if such a feature is needed.

Question 5: Do we need a focus+context facility in the table, and if so, is it only for the table's own selections, or also for selections in other widgets? When selecting rows, there should clearly be a highlight or other styling; when another widget restricts records, is it perhaps needed that table rows are not eliminated, but simply faded or otherwise restyled?

Interlude - heatmap approach

From the above list it's already clear that, while heatmap, or pretty much all plot types can be configured to show text in a grid arrangement, e.g. via annotations, the existing tools lack various means of interaction, or substitute idiosyncratic ones e.g. panning instead of scrolling. It is for such reasons that the question of repurpose vs make emerges.

Table functionality not directly related to crossfiltering

While crossfiltering poses some mandatory and optional requirements (above), if we make a table, we should consider use cases and usage patterns that don't directly relate to crossfiltering.

Some functions are somewhat obvious, yet worth asking:

Question 6: Is it a reasonable minimal set of generic table functions?

• striping rows
• units, pre-and postfixes (e.g. SI), fixed decimals
• font styling (for headers, row block and maybe columns)
• layout styling, eg. left/middle/right alignment
• padding
• border (optionally)

As it is the first plotly.js view that has a unbounded screenspace dimensions:

Question 7: What types of scrolling and sizing needs to be supplied?

• scrolling up/down
• being able to switch between scrolling, and truncation (not show records that aren't fully visible)
• autosizing: show as many rows as possible in the specified layout.height, respecting character height and padding constraints, but if there's fewer rows than capacity, stretch rows (thicker rows) to fill space
• lots of columns and scrolling sideways?

Question 8: Constraint management:

• how to determine column widths if layout.width is very high or isn't a constraint?
• what to do if layout.width + margins is such that cell contents do not fit?
  • truncation, e.g. ellipsis
  • wrapping lines
  • shrinking characters
  • minimize wrapping/shrinking by prioritizing the resizing those columns where impact is lower?
• expectations for responsivity?
  • speed expectations for rerendering on browser window resizing?

Question 9: Some more general data display questions:

• have we got a need for sorting by column (e.g. click and hold various columns here)
• should it be possible to render records as columns, rather than rows, ie. flipping rows/columns?
• is cell merging needed, e.g. to convey a hierarchical relationship?
• if so, is there a need for expanding/collapsing row blocks, and are hierarchical block aggregations needed? (we're in report writing territory)

Question 10: Printing and PNG saving: as table is the first unbounded thing, what special requirements are present for printing? E.g. a common need is to print the table on paper, maximizing paper size, ensuring it fits on one page width, height or width and height. There's the question of repeating the header row on each sheet, there was a Chrome bug that made it impossible (for me) the last time I looked in 2014. Spreadsheet programs support multipage printing too, but then export is needed?

Table functionality above and beyond a table view

When considering tables, esp. in the context of a dataviz library, we can't escape the thought of putting into cells stuff other than numbers and strings, so:

Question 11: What cell contents should we support?

• coloring of the cell background
• actual chartlets in the cells
• if so, then should this new table thingie be also the basis for trellising / faceting, e.g. to construct a SPLOM out of existing scatterplots? (note: our crossfilter is being prepared to support faceting and small multiples, so it's partly a crossfilter relating question)
• since we have a table, usually with text: should it be editable in some cases, and maybe a 1x1 cell is also useful as a text entry field similar to the table filter field here? also, just entry, or dropdown? undo/redo?

General usability, ergonomics and accessibility

Question 12:

• should the cells, rows, row blocks, arbitrary rectangular cell blocks be selectable and copyable as text? - this rules out canvas
• similarly, should screenreading work? - this rules out canvas
• should texts be present for all rows, whether visible or not (eg. for reader or export), or can we use some infinite revolver or canvas concept?

Implementation options

Rendering medium

There are many ways, without being exhaustive:

1. <canvas>: fast but can't copy text, not accessible, and complete layouting is in JS
2. <table>: semantically correct, but poses limitations and enforces structure, and it's impossible, I think, to make a revolving or infinite scroller with it - also, since it fell out of favour for most tasks eg. layouting, it gets little attention as it's really just intended for a small niche, data tables; bugs can have a long life
3. <div> with absolute positioning and transform: a revolver can be built out of it, but it's more CSS and widths etc. need to be set via CSS + JS
4. <div> with CSS display: table - no comment (yet?)
5. flex box: pretty neat and now well supported; can do the job but it's still a fundamentally one-dimensional tool; also, positions are integers so row positions can't be tweened
6. CSS grid layout: maybe best for the job and proper 2D but got only recent support esp. from Microsoft
7. SVG text: unlike HTML it can't do text truncation and wrapping, and in general, has fewer means for text manipulation

Current suggestion for the rendering medium:

Option 3: <div> with transform - it's copyable and screen readers can use it, and it's also able to eventually do fancy things eg. drag columns via direct manipulation (like parcoords axes here) or tween rows; it's also closest to the SVG layout paradigm in that things are positioned with transform (note: while MS doesn't properly support CSS transforms of SVG elements, HTML is actually okay). HTML DOM is also GPU accelerated right now, while SVG isn't, and it can be really handy for an infinite revolver scroller whenever we need it - tradeoff is, screencopy or printing may be more cumbersome than SVG and <table>, respectively - and another consideration is, it'd be the first plot type whose main medium is HTML rather than SVG

Question 13: Is it okay if we go ahead with this rendering option?

Sparse or dense

There are some web spreadsheets that support trillions of rows, of course it's hypothetical in that this much data doesn't fit in the current address space, let alone through a cable modem. Yet a table implementation can rely on a sparse representation to allow very large tables. I see limited or no need for it from a plotly PoV so my suggestion is, let's not worry about it for now.

Full render vs growing DOM vs redrawing (or infinity scroller) vs pagination

Full render is the simplest but the DOM is losing speed due to the mere presence of elements, so it's good for up to around 100 rows and a few columns. Can be a lot more, if user opens a separate tab for export / browse but it won't be interactive. Full render is sufficient for crossfiltering unless there's a need for a lot of rows

Question 14: Is it okay if we go ahead with a full rendering now, while we retain the future option for an infinity scroller by using the <div> + transform approach above?

[monfera]

Writing the above, I felt less and less certain that repurposing an existing plot type such as heatmap is a sustainable option. BUT we can approach it from what we have, we can use heatmap or just scatter annotations with deactivated interactions for minuscule, fixed-layout tables and we can still revisit the table question later.

[monfera]

In a current example, we're doing a minimal thing with Option 3 (<div> + transform) and just font styling, so not a lot of work went into table rendering. It's useful for a crossfilter approach to be capable of rendering arbitrary SVG / HTML / D3 visuals anyway:

[etpinard]

For reference, @cldougl 's python figure factory: https://plot.ly/python/table/

[monfera]

Indeed, I thought it was linked on the top - generates (in)credible table views!

[jackparmer]

This should be a first-class table trace type. Traces as transforms never worked, and the lack of tables in plotly.js and the workspace has been a longtime (3 years?) sales thorn, ameliorated slightly by the plotly.py heatmap table. People are always gonna want nice tables with no fuss when making charts.

Question 1: does a table view need to be immediately, or in the future, be able to display any and all records, or can a row count limit be established?

Row limit sounds fine to me. SVG tables are only going to be able to show so many rows anyway. Big tables with 1000s of rows loaded are going to bog down dashboards, the workspace, Dash apps, etc.

Question 2: Is there a current, or eventual need for tweening

I'd say no unless it's easy to implement.

Question 3: Should the user be able to select one record, a contiguous block of records, discontiguous records (and which of these) in order to filter the currently shown dataset on the dashboard?

For consistency and simplicity, it seems like we could recycle rectangular selection into tables.

Question 4: Do we need selection of dimension value by example? E.g. right-click on a specific cell that has 'Canada', and click 'Keep (or Exclude) records where country is 'Canada')

There are a lot of fascinating possibilities for declarative context menus (get stats for a selection, open a selection in a new graph, etc), but we should leave that out of this project and treat it holistically with other trace types, if we even ever get to it.

Question 5: ... is it perhaps needed that table rows are not eliminated, but simply faded or otherwise restyled?

Maybe hide versus restyle could be an option? Hide should be the default. See filtering on this table:

http://react-grid-crossfilter.getforge.io/

Question 6: Is it a reasonable minimal set of generic table functions?

This list loooks good. @cldougl 's Plotly.py tables are an excellent starting point since they've been battle-tested for a few years among plotly users and customers. I think @charleyferrari has run into some additional customer requests.

Question 7: What types of scrolling and sizing needs to be supplied?

At least vertical scrolling. The crossfilter customer was happy with this table prototype, which has vertical scrolling:

http://react-grid-crossfilter.getforge.io/

Question 8: Constraint management

I'd copy this table as best you can, since the sponsoring customer already gave it a 👍

http://react-grid-crossfilter.getforge.io/

Question 9: Some more general data display questions

No, I'd keep this first table implementation as simple as possible, using @cldougl 's figure factory as the main feature spec.

Question 10: Printing and PNG saving: as table is the first unbounded thing

Going to need vector export. I wouldn't worry about @media print css.

Question 11: What cell contents should we support?

In addition to what the Plotly.py table supports, sparklines would be awesome. Sparklines could be its own infinite rabbit hole, so I'd just make them as simple as possible to start, then let customers guide their future development.

Question 12

Yes to copy-able text and screenreaders. No opinion on 3rd, except it sounds overly complicated for what we need now.

Question 13

HTML implementation sounds fine to me.

Question 14: Is it okay if we go ahead with a full rendering now

Definitely.

[monfera]

@etpinard @alexcjohnson @rreusser any overriding guidance on the Rendering option? (1..7) which one is OK / not OK? I spent a few hours looking into the alternatives but all options have important pros/cons

[charleyferrari]

@jackparmer Clients have brought up jQuery datatables quite a few times. It also has an R implementation that has become pretty standard for tables in Shiny. I think that would be a good place to look for specs.

[jackparmer]

@charleyferrari yeah, I get that one all the time too 👍

This fellow even thinks we make it 😹

https://groups.google.com/d/msg/jupyter/s7OWDL7JAVY/2uSC5mDPAwAJ

[monfera]

In functionality, the jQuery DataTable is leagues beyond the initial response above, it has a lot of options and affordances, so there should be a decision if we shoot for the basic thing (my initial read of Jack's response) or a DataTable-alike, even its separately downloadable list of options is sizable: https://datatables.net/download/packages - even the main DataTable source file is 500 megs, and the full enchilada (source with options, examples) is 2.5MB zipped! Besides dev time, there's the code size impact.

[jackparmer]

@monfera yeah, DataTables is way on the other end of the complexity spectrum, sorry for not clarifying.

@charleyferrari I was mainly remembering what you did here as an example for a Plotly user - augmenting the Plotly.py table with a background row color:

https://plot.ly/~charleyferrari/2060/

[charleyferrari]

@jackparmer Ah yeah, conditional formatting. I get that a lot too. Right now the figure factory doesn't seem to match the data to the formatting: it assumes you want the table pretty-formatted (unless it's an undocumented feature @cldougl added). To hack the above I just manually restyled after creating a table object.

In my experience customers want it to behave like Excel, which has tons of options (coloring based on ranges or outliers, numeric scales like the one above, etc.) Going for that level of functionality would be a lot. But, if tables were a top level trace type, the data could constantly be associated with it and we could color it like a heatmap (and users can create custom color scales for excel-like conditional formatting, with a setting to toggle between pretty formatting and conditional formatting)

[cldougl]

yeah @charleyferrari for coloring based on data (ranges or outliers) I point people to the annotated heatmap rather than the table

A big issue that I've heard a lot is editing column widths (independently) easily.

[etpinard]

@etpinard @alexcjohnson @rreusser any overriding guidance on the Rendering option? (1..7) which one is OK / not OK? I spent a few hours looking into the alternatives but all options have important pros/cons

• <canvas>: fast but can't copy text, not accessible, and complete layouting is in JS

Ok.

• <table>: semantically correct, but poses limitations and enforces structure, and it's impossible, I think, to make a revolving or infinite scroller with it - also, since it fell out of favour for most tasks eg. layouting, it gets little attention as it's really just intended for a small niche, data tables; bugs can have a long life

Not ok. Wouldn't export well.

• <div> with absolute positioning and transform: a revolver can be built out of it, but it's more CSS and widths etc. need to be set via CSS + JS

Not ok. Wouldn't export well.

• <div> with CSS display: table - no comment (yet?)

Not ok. Wouldn't export well.

• flex box: pretty neat and now well supported; can do the job but it's still a fundamentally one-dimensional tool; also, positions are integers so row positions can't be tweened

Not ok. We need to support IE9 given a typedarray polyfill

• CSS grid layout: maybe best for the job and proper 2D but got only recent support esp. from Microsoft

Nop.

• SVG text: unlike HTML it can't do text truncation and wrapping, and in general, has fewer means for text manipulation

Ok. Text manipulation is harder, but it wouldn't be wasted as axis labels are in dire need of some text wrapping.

---

So, canvas or SVG.

[alexcjohnson]

👍 for SVG with an eventual plan to automatically wrap text. So many places this would be useful, I'd like to see it built into to svgTextUtils.convertToTspans so we can reuse it in axis labels, annotations, legends, colorbar labels, etc etc.

It sounds a bit complicated to implement, but with selective rendering and aggressive caching of dimensions I'd imagine we could get reasonable performance...

[etpinard]

done in #2052

[DashboardBuilder]

It's entirely feasible to achieve that using Plotly as a library, PHP to connect to the database (MySQL in this case), and a database. Essentially, you can create a table in Plotly by treating it as a heatmap trace with strategically positioned text annotations. However, it's worth noting that figure factories aren't directly available in plotly.js.

However, with the advent of the plotly.js transform plugin functionality, you could easily port the logic from plotly.py to JavaScript as a transform module. This would allow you to replicate the functionality seamlessly in a web environment.
For more details with example https://dashboardbuilder.net/pivot-table