How to display a pandas dataframe as datatable?

Question

I want to display a table - which is a pandas dataframe - as a DataTable. In the simplified example below, I read two numbers provided by a user, that determine the row and column number of the table. The number of elements of this table is then displayed correctly, however, the table does not appear.

The problem is, I think, that I pass the table in the wrong way. When I try

return jsonify(number_elements=a * b,
                   my_table=df)

I get the error

anaconda2/lib/python2.7/json/encoder.py", line 184, in default raise TypeError(repr(o) + " is not JSON serializable")

TypeError: 0 1 2 3 0 51 35 10 84 1 30 60 79 24 is not JSON serializable

if I use

return jsonify(number_elements=a * b,
                   my_table=df.to_json())

then there is no error but the table is still not displayed.

How would I do this correctly?

My index.html file looks like this:

<!DOCTYPE html>
<html lang="en">
  <head>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <link href="https://netdna.bootstrapcdn.com/bootstrap/3.0.0/css/bootstrap.min.css"
          rel="stylesheet">
     <link href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css"
           rel="stylesheet">
  <script type=text/javascript>
    $(function() {
      $('a#calculate').bind('click', function() {
        $.getJSON('/_get_table', {
          a: $('input[name="a"]').val(),
          b: $('input[name="b"]').val()
        }, function(data) {
          $("#elements").text(data.number_elements);
          $("#a_nice_table").DataTable(data.my_table);
        });
        return false;
      });
    });
  </script>
  </head>
  <body>
    <div class="container">
      <div class="header">
        <h3 class="text-muted">Create a pretty table</h3>
      </div>

      <div>
        <p>Number of rows</p>
        <input type="text" size="5" name="a" value="2">
        <p>Number of columns</p>
        <input type="text" size="5" name="b" value="4">

        <p><a href="javascript:void();" id="calculate">get a pretty table</a></p>
         <p>Result</p>
        <p>Number of elements:</p>
          <span id="elements">Hallo</span><br>
          <span id="a_nice_table">Here should be a table</span>
      </div>
    </div>
  </body>
</html>

And my file app.py looks like this:

from flask import Flask, render_template, request, jsonify
import pandas as pd
import numpy as np

# Initialize the Flask application
app = Flask(__name__)


@app.route('/')
def index():
    return render_template('index.html')


@app.route('/_get_table')
def get_table():
    a = request.args.get('a', type=int)
    b = request.args.get('b', type=int)

    df = pd.DataFrame(np.random.randint(0, 100, size=(a, b)))

    return jsonify(number_elements=a * b,
                   my_table=df)


if __name__ == '__main__':
    app.run(debug=True)

Scratch'N'Purr · Accepted Answer · 2018-02-05T15:03:45.130

Here's my implementation. I did some optimizations such as moving your js files to the end of the HTML:

index.html

<!DOCTYPE html>
<html lang="en">
  <head>
    <link href="https://netdna.bootstrapcdn.com/bootstrap/3.0.0/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" rel="stylesheet">
  </head>
  <body>
    <div class="container">
      <div class="header">
        <h3 class="text-muted">Create a pretty table</h3>
      </div>

      <div>
        <p>Number of rows</p>
        <input type="text" size="5" name="a" value="2">
        <p>Number of columns</p>
        <input type="text" size="5" name="b" value="4">

        <p><a href="javascript:void();" id="calculate">get a pretty table</a></p>
         <p>Result</p>
        <p>Number of elements:</p>
          <span id="elements">Hallo</span><br>
          <table id="a_nice_table">Here should be a table</table>
      </div>
    </div>
    <script src="https://code.jquery.com/jquery-1.12.4.js" type="text/javascript"></script>
    <script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js" type="text/javascript"></script>
    <script type="text/javascript">
      $(document).ready(function() {
        var table = null;
        $('a#calculate').bind('click', function() {
          $.getJSON('/_get_table', {
            a: $('input[name="a"]').val(),
            b: $('input[name="b"]').val()
          }, function(data) {
            $("#elements").text(data.number_elements);
            if (table !== null) {
              table.destroy();
              table = null;
              $("#a_nice_table").empty();
            }
            table = $("#a_nice_table").DataTable({
              data: data.my_table,
              columns: data.columns
            });
          });
          return false;
        });
      });
    </script>
  </body>
</html>

app.py

from flask import Flask, render_template, request, jsonify
import pandas as pd
import numpy as np
import json

# Initialize the Flask application
app = Flask(__name__)


@app.route('/')
def index():
    return render_template('index.html')


@app.route('/_get_table')
def get_table():
    a = request.args.get('a', type=int)
    b = request.args.get('b', type=int)

    df = pd.DataFrame(np.random.randint(0, 100, size=(a, b)))

    return jsonify(number_elements=a * b,
                   my_table=json.loads(df.to_json(orient="split"))["data"],
                   columns=[{"title": str(col)} for col in json.loads(df.to_json(orient="split"))["columns"]])


if __name__ == '__main__':
    app.run(debug=True)

What I modified:

Added the js file to render the DataTable.
Moved the js files down to the bottom of the HTML.
Added a check in the js to destroy and clear columns when refreshing the data with new data.
Used the to_json method with orient of split to generate the json data for DataTables.
Also had to add a columns json string for DataTables to consume, which is dynamically set after using to_json

Here's how to use panda's to_html for generating the table:

index.html

<!DOCTYPE html>
<html lang="en">
  <head>
    <link href="https://netdna.bootstrapcdn.com/bootstrap/3.0.0/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" rel="stylesheet">
  </head>
  <body>
    <div class="container">
      <div class="header">
        <h3 class="text-muted">Create a pretty table</h3>
      </div>

      <div>
        <p>Number of rows</p>
        <input type="text" size="5" name="a" value="2">
        <p>Number of columns</p>
        <input type="text" size="5" name="b" value="4">

        <p><a href="javascript:void();" id="calculate">get a pretty table</a></p>
         <p>Result</p>
        <p>Number of elements:</p>
          <span id="elements">Hallo</span><br>
          <div id="mytablediv">Here should be a table</div>
      </div>
    </div>
    <script src="https://code.jquery.com/jquery-1.12.4.js" type="text/javascript"></script>
    <script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js" type="text/javascript"></script>
    <script type="text/javascript">
      $(document).ready(function() {
        var table = null;
        $('a#calculate').bind('click', function() {
          $.getJSON('/_get_table', {
            a: $('input[name="a"]').val(),
            b: $('input[name="b"]').val()
          }, function(data) {
            $("#elements").text(data.number_elements);
            if (table !== null) {
              table.destroy();
              table = null;
              $("#a_nice_table").empty();
            }
            $("#mytablediv").html(data.my_table);
            table = $("#a_nice_table").DataTable();
          });
          return false;
        });
      });
    </script>
  </body>
</html>

app.py

from flask import Flask, render_template, request, jsonify
import pandas as pd
import numpy as np

# Initialize the Flask application
app = Flask(__name__)


@app.route('/')
def index():
    return render_template('index2.html')


@app.route('/_get_table')
def get_table():
    a = request.args.get('a', type=int)
    b = request.args.get('b', type=int)

    df = pd.DataFrame(np.random.randint(0, 100, size=(a, b)))

    return jsonify(number_elements=a * b,
                   my_table=df.to_html(classes='table table-striped" id = "a_nice_table',
                                       index=False, border=0))


if __name__ == '__main__':
    app.run(debug=True)

Differences from former implementation:

In the HTML, I had to add a parent div in order to hold the generated HTML table. In this case, I called it mytablediv.
In the HTML on the JS side, I have to basically modify the HTML content of the mytablediv after I generate my data. This HTML content comes from the to_html output.
In the HTML on the JS side, I didn't have to pass anymore data into the DataTable function because that would be handled with HTML code.
In app.py, I had to use a hackey method for pandas to generate an HTML ID tag. The ID tag lets JS know what element to modify. I used the solution from here.
In app.py, because I'm now generating HTML, I have to also explicitly specify other table style options like border=0 and index=False to mimic the former implementation.

That works perfectly fine, thanks (upvoted for now; I will try to understand what's going on and then accept)! Is it the best way or would there be a simpler solution (with these ninja templates)? — Cleb, Feb 05 '18 at 14:11
@Cleb The other approach would be creating the HTML as @Arnaud pointed out but I don't see it as any different than using `to_json`, except for changing the javascript in a few places. Also, another thing I changed was your HTML element from `span` to table. And if you plan to style the table with bootstrap goodies, then you can do something like: `Here should be a table
` — Scratch'N'Purr, Feb 05 '18 at 14:24
Ok, thanks. My knowledge on all this is rather limited I have to admit. So I do not really know how and where to create the html (as the table can have different dimensions). If it is not much work for you, it would be great if you could add this in, otherwise I can also open another question at some point. Thanks for the suggestion regarding the table layout; will try that! — Cleb, Feb 05 '18 at 14:30

score 1 · Answer 2 · answered Feb 05 '18 at 12:45

1

Shouldn't you generate an html table first ? Taking advantage of the pandas.DataFrame.to_html() function ? Indeed, the documentation of DataTables show an example using an html table.

answered Feb 05 '18 at 12:45

arnaud

3,293
1
10
27

Where would I generate this html table then? `my_table=df.to_html()` does not work. – Cleb Feb 05 '18 at 13:17
1

Yes, didn't pay attention to this before. Now will :). Thanks for the feedback. – arnaud Feb 05 '18 at 17:04

How to display a pandas dataframe as datatable?

2 Answers2

Linked