3

If I have an html table that contains values that are calculated based on filters within my file, can I get plotly to read and produce a plot based on those values?

I'm not sure that it matters to answering this question, but I use R primarily, and use the r code chunks calculate sums from a sharedData object names shared_ert that I created with the crosstalk package for R.

<table id="example" class="cell-border" style="width:100%">
<tr>
<th>Enrolled</th>
<th>Not Enrolled</th>
</tr>
<tr>
<td>
```{r, echo=FALSE, collapse=TRUE, warning=FALSE, message=FALSE}
summarywidget::summarywidget(shared_ert,statistic = 'sum',column = 'Enrolled')
```
</td>
<td>

```{r, echo=FALSE, collapse=TRUE, warning=FALSE, message=FALSE}
summarywidget::summarywidget(shared_ert,statistic = 'sum',column = 'Not Enrolled')

```

</td>
</tr>
</table>

Note that summary widget ends up producing a span tag within each td. The spans look like <span id ="waffle" class="summarywidget html-widget html-widge-static-bound">1293</span>

So the table ends up looking like:

<table id="example" class="cell-border" style="width:100%">
<tr>
<th>Enrolled</th>
<th>Not Enrolled</th>
</tr>
<tr>
<td>
<span id ="waffle" class="summarywidget html-widget html-widge-static-bound">1293</span>
<script type="application/json" data-for="waffle">
### a bunch of data appears here, from which the 1293 value is derived
</script>

</td>
<td>
<span id ="iron" class="summarywidget html-widget html-widge-static-bound">948</span>
<script type="application/json" data-for="iron">
### a bunch of data appears here, from which the 948 value is derived
</script>


</td>
</tr>
</table>

From my limited understanding of the world of javascript, I need my data to look something like

var data = [
{
x: ['giraffes', 'orangutans', 'monkeys'],
y: [20, 14, 23],
type: 'bar'
  }
];

So that I can get a plot produced with something like:

Plotly.newPlot('myDiv', data);

(directly from https://plotly.com/javascript/bar-charts/)

If I understand the problem correctly, I need to read the html table example and create a var that holds the table. After much searching around SO and the web in general, my guess is that the solution here: HTML Table to JSON pulls the table into the correct format. I'm trying

```{js}
function tableToJson(table) {
var data = [];

// first row needs to be headers
var headers = [];
for (var i=0; i<table.rows[0].cells.length; i++) {
    headers[i] = table.rows[0].cells[i].innerHTML.toLowerCase().replace(/ /gi,'');
}

// go through cells
for (var i=1; i<table.rows.length; i++) {

    var tableRow = table.rows[i];
    var rowData = {};

    for (var j=0; j<tableRow.cells.length; j++) {

        rowData[ headers[j] ] = tableRow.cells[j].innerHTML;

    }

    data.push(rowData);
}       

return data;
}

var tabdata = $document.getElementById('#example').tableToJSON();            

```

I think from here, I need plotly to read the data from the table in it's current state, so I produce the plot using a button and onclick, as follows:

<button type="button" onclick="Plotly.newPlot('myDiv',tabdata);">Make Plot</button>

Upon clicking, the plotly plot appears, but doesn't have a data point anywhere.

I might be way off track in my methodology, so I defer to the original question: can I get plotly to read and produce a plot based on a dynamic html table?

Any help establishing a means to this end would be very much appreciated.

Pake
  • 968
  • 9
  • 24

1 Answers1

2

You need generate your json with keys x & y .So , here x value will be your header i.e : th tags and y values will be tdvalues . Now , if you have only one row in your table you can simply create JSON Object and then push value inside this using key i.e : data["x"] , data["y"]..etc .

Demo Code :

function tableToJSON(table) {
  var data = {}; //create obj
  data["x"] = [] //for x & y
  data["y"] = []
  data["type"] = "bar"
  for (var i = 0; i < table.rows[0].cells.length; i++) {
    data["x"].push(table.rows[0].cells[i].innerHTML.toLowerCase().trim()); //push x values
  }
  for (var i = 1; i < table.rows.length; i++) {
    var tableRow = table.rows[i];
    for (var j = 0; j < tableRow.cells.length; j++) {
      data["y"].push(parseInt(tableRow.cells[j].querySelector(".summarywidget").textContent.trim()));
      //push y values
      console.log(tableRow.cells[j].querySelector(".summarywidget").textContent.trim())
    }
  }

  return data;
}

function draw() {
  var tabdata = tableToJSON(document.getElementById('example'));
  tester = document.getElementById('tester');
  Plotly.newPlot(tester, [tabdata])
}
<script src="https://cdn.plot.ly/plotly-2.1.0.min.js"></script>
<table id="example" class="cell-border" style="width:100%">
  <tr>
    <th>Enrolled</th>
    <th>Not Enrolled</th>
  </tr>
  <tr>
    <td>
      <span id="waffle" class="summarywidget html-widget html-widge-static-bound">1293</span>
      <script type="application/json" data-for="waffle">
        ###
        a bunch of data appears here, from which the 1293 value is derived
      </script>

    </td>
    <td>
      <span id="iron" class="summarywidget html-widget html-widge-static-bound">948</span>
      <script type="application/json" data-for="iron">
        ###
        a bunch of data appears here, from which the 948 value is derived
      </script>


    </td>
  </tr>
</table>

<button type="button" onclick="draw()">Make Plot</button>
<div id="tester" style="width:600px;height:250px;"></div>

Now , if you have mutliple rows in your table you need to generate JSON Array of that values .For that you need to keep main_array and then push values inside this main_array on each iterations.

Demo Code :

function tableToJSON(table) {
  var main_array = [] //for main array
  var for_x = [] //for x values
  for (var i = 0; i < table.rows[0].cells.length; i++) {
    for_x.push(table.rows[0].cells[i].innerHTML.toLowerCase().trim()); //push value 
  }
  for (var i = 1; i < table.rows.length; i++) {
    var tableRow = table.rows[i];
    var data = {}; //create obj..
    data["y"] = [] //for y values
    for (var j = 0; j < tableRow.cells.length; j++) {
      data["y"].push(parseInt(tableRow.cells[j].innerHTML.trim())); //push y values
    }
    //save other values..
    data["x"] = for_x
    data["type"] = "bar"
    data["name"] = "Rows" + i
    main_array.push(data) //push values in main array
  }
  //console..[{},{}..]
  return main_array;

}

function draw() {
  var tabdata = tableToJSON(document.getElementById('example'));
  tester = document.getElementById('tester');
  //pass it here
  Plotly.newPlot(tester, tabdata, {
    barmode: 'stack'
  })
}
<script src="https://cdn.plot.ly/plotly-2.1.0.min.js"></script>
<table id="example" class="cell-border" style="width:100%">
  <tr>
    <th>Enrolled</th>
    <th>Not Enrolled</th>
  </tr>
  <tr>
    <td>
      123
    </td>
    <td>
      125
    </td>
  </tr>
  <tr>
    <td>
      121
    </td>
    <td>
      127
    </td>
  </tr>
</table>

<button type="button" onclick="draw()">Make Plot</button>
<div id="tester" style="width:600px;height:250px;"></div>
Swati
  • 28,069
  • 4
  • 21
  • 41
  • This does a great job with reading an plotting the html table! In my code, each has a within it, and I *think* that is keeping the plot from displaying properly. I found this: https://stackoverflow.com/questions/12051898/how-to-get-span-value, but don't know how to apply it and strip out the spans. Could you please edit the function to strip the tags so that the tableToJSON() function will read just the values? – Pake Jun 24 '21 at 14:48
  • I edited the question with a better look at how the `` contents looks when I inspect it. There's also a ` – Pake Jun 24 '21 at 15:09
  • 1
    Hi, see first example i have updated that with your html . – Swati Jun 24 '21 at 15:29
  • Is it possible to do this in R? https://stackoverflow.com/questions/68453274/can-plotly-use-a-datatable-as-source-data – Masoud Jul 25 '21 at 04:23