How to display a pandas dataframe as datatable?
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, jsonifyimport pandas as pdimport numpy as npimport json# Initialize the Flask applicationapp = 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 ofsplit
to generate the json data for DataTables. - Also had to add a
columns
json string for DataTables to consume, which is dynamically set after usingto_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, jsonifyimport pandas as pdimport numpy as np# Initialize the Flask applicationapp = 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 theto_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 likeborder=0
andindex=False
to mimic the former implementation.
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.