Johnny V
Reputation: 1238
Save configuration of pivottablejs in Jupyter notebook
I am using google Colab (Jupyter python notebook on a server) to run the pivot table.
This code:
pivot_ui(d1,outfile_path='pivottablejs.html')
HTML('pivottablejs.html')
Results in this blank pivot:
Which I can manipulate to get this desired chart:
But when I refresh the page it all goes back to the blank pivot. What I would like is to store the config of the desired chart so that I can get it back after a refresh.
I am aware that there are instructions on how to do this in JavaScript, but I cannot figure out how to do this in a Jupyter notebook. Any ideas?
Edit It seems I am not the first to have tried: https://github.com/nicolaskruchten/jupyter_pivottablejs/issues/34 and it is not possible with the current set up.
Upvotes: 6
Views: 2794
Answers (1)
Johnny V
Reputation: 1238
My hacky solution
I rewrote part of the pivottablejs package to add a "copy config" button. I put this code in an earlier cell in by Jupyter notebook, so the function would be accessible later:
Version for Google Colab:
!pip install pivottablejs
from pivottablejs import pivot_ui
from IPython.display import HTML
from IPython.display import IFrame
import json, io
TEMPLATE = u"""
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>PivotTable.js</title>
<!-- external libs from cdnjs -->
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/c3/0.4.11/c3.min.css">
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/d3/3.5.5/d3.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/c3/0.4.11/c3.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.11.4/jquery-ui.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery-csv/0.71/jquery.csv-0.71.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.19.0/pivot.min.css">
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.19.0/pivot.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.19.0/d3_renderers.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.19.0/c3_renderers.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.19.0/export_renderers.min.js"></script>
<style>
body {font-family: Verdana;}
.node {
border: solid 1px white;
font: 10px sans-serif;
line-height: 12px;
overflow: hidden;
position: absolute;
text-indent: 2px;
}
.c3-line, .c3-focused {stroke-width: 3px !important;}
.c3-bar {stroke: white !important; stroke-width: 1;}
.c3 text { font-size: 12px; color: grey;}
.tick line {stroke: white;}
.c3-axis path {stroke: grey;}
.c3-circle { opacity: 1 !important; }
.c3-xgrid-focus {visibility: hidden !important;}
</style>
</head>
<body>
<script type="text/javascript">
$(function(){
$("#output").pivotUI(
$.csv.toArrays($("#output").text())
, $.extend({
renderers: $.extend(
$.pivotUtilities.renderers,
$.pivotUtilities.c3_renderers,
$.pivotUtilities.d3_renderers,
$.pivotUtilities.export_renderers
),
hiddenAttributes: [""]
}
, {
onRefresh: function(config) {
var config_copy = JSON.parse(JSON.stringify(config));
//delete some values which are functions
delete config_copy["aggregators"];
delete config_copy["renderers"];
//delete some bulky default values
delete config_copy["rendererOptions"];
delete config_copy["localeStrings"];
$("#output2").text(JSON.stringify(config_copy, undefined, 2));
}
}
, %(kwargs)s
, %(json_kwargs)s)
).show();
});
</script>
<div id="output" style="display: none;">%(csv)s</div>
<textarea id="output2"
style="float: left; width: 0px; height: 0px; margin: 0px; opacity:0;" readonly>
</textarea>
<button onclick="copyTextFunction()">Copy settings</button>
<script>
function copyTextFunction() {
var copyText = document.getElementById("output2");
copyText.select();
document.execCommand("copy");
}
</script>
</body>
</html>
"""
def pivot_cht_html(df, outfile_path = "pivottablejs.html", url="",
width="100%", height="500",json_kwargs='', **kwargs):
with io.open(outfile_path, 'wt', encoding='utf8') as outfile:
csv = df.to_csv(encoding='utf8')
if hasattr(csv, 'decode'):
csv = csv.decode('utf8')
outfile.write(TEMPLATE %
dict(csv=csv, kwargs=json.dumps(kwargs),json_kwargs=json_kwargs))
IFrame(src=url or outfile_path, width=width, height=height)
return HTML(outfile_path)
Version for standard Jupyter notebook:
#this is my custom version of pivottablejs, it allows you to save your analysis
!pip install pivottablejs
from pivottablejs import pivot_ui
from IPython.display import HTML
from IPython.display import IFrame
import json, io
TEMPLATE = u"""
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>PivotTable.js</title>
<!-- external libs from cdnjs -->
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/c3/0.4.11/c3.min.css">
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/d3/3.5.5/d3.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/c3/0.4.11/c3.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.11.4/jquery-ui.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery-csv/0.71/jquery.csv-0.71.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.19.0/pivot.min.css">
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.19.0/pivot.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.19.0/d3_renderers.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.19.0/c3_renderers.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.19.0/export_renderers.min.js"></script>
<style>
body {font-family: Verdana;}
.node {
border: solid 1px white;
font: 10px sans-serif;
line-height: 12px;
overflow: hidden;
position: absolute;
text-indent: 2px;
}
.c3-line, .c3-focused {stroke-width: 3px !important;}
.c3-bar {stroke: white !important; stroke-width: 1;}
.c3 text { font-size: 12px; color: grey;}
.tick line {stroke: white;}
.c3-axis path {stroke: grey;}
.c3-circle { opacity: 1 !important; }
.c3-xgrid-focus {visibility: hidden !important;}
</style>
</head>
<body>
<script type="text/javascript">
$(function(){
$("#output").pivotUI(
$.csv.toArrays($("#output").text())
, $.extend({
renderers: $.extend(
$.pivotUtilities.renderers,
$.pivotUtilities.c3_renderers,
$.pivotUtilities.d3_renderers,
$.pivotUtilities.export_renderers
),
hiddenAttributes: [""]
}
, {
onRefresh: function(config) {
var config_copy = JSON.parse(JSON.stringify(config));
//delete some values which are functions
delete config_copy["aggregators"];
delete config_copy["renderers"];
//delete some bulky default values
delete config_copy["rendererOptions"];
delete config_copy["localeStrings"];
$("#output2").text(JSON.stringify(config_copy, undefined, 2));
}
}
, %(kwargs)s
, %(json_kwargs)s)
).show();
});
</script>
<div id="output" style="display: none;">%(csv)s</div>
<textarea id="output2"
style="float: left; width: 0px; height: 0px; margin: 0px; opacity:0;" readonly>
</textarea>
<button onclick="copyTextFunction()">Copy settings</button>
<script>
function copyTextFunction() {
var copyText = document.getElementById("output2");
copyText.select();
document.execCommand("copy");
}
</script>
</body>
</html>
"""
def pivot_cht_ui(df, name, url="",
width="100%", height="500",json_kwargs='', **kwargs):
print(name)
outfile_path = name + '.html'
with io.open(outfile_path, 'wt', encoding='utf8') as outfile:
csv = df.to_csv(encoding='utf8')
if hasattr(csv, 'decode'):
csv = csv.decode('utf8')
outfile.write(TEMPLATE %
dict(csv=csv, kwargs=json.dumps(kwargs),json_kwargs=json_kwargs))
return IFrame(src=url or outfile_path, width=width, height=height)
This enables you to make a chart as so (google colab version, slightly different for Jupyter notebook version):
pivot_cht_html(d1)
Which loads a blank chart which you can manually configure (below image is configured). Note the "copy settings" button:
If you press the button it copies a JSON of the config, which you can then put back into the charting function so that it runs your configured chart:
pivot_cht_html(d1,json_kwargs="""
{
"derivedAttributes": {},
"hiddenAttributes": [
""
],
"hiddenFromAggregators": [],
"hiddenFromDragDrop": [],
"menuLimit": 500,
"cols": [
"weeks"
],
"rows": [],
"vals": [
"resurrected_90d"
],
"rowOrder": "key_a_to_z",
"colOrder": "key_a_to_z",
"exclusions": {},
"inclusions": {},
"unusedAttrsVertical": 85,
"autoSortUnusedAttrs": false,
"sorters": {},
"inclusionsInfo": {},
"aggregatorName": "Average",
"rendererName": "Line Chart"
}
""")
This is far from ideal... Ideally this should be added to the pivottablejs package as a PR. If I get time I will try to do this!
Upvotes: 4
Related Questions
- Google Colabs eval_js in Jupyter Notebook?
- Saving Jupyter notebook session
- How to insert javascript code into Jupyter
- Jupyter Notebook Pivottablejs Multiple Instances
- How to replace jupyter-notebook magic with python script
- Globally configuring Javascript for notebooks on Jupyterhub
- Add Derived Attribute to pivottablejs in jupyter notebooks
- displaying data in Jupyter pivottable.js
- Reproduce example pivottable.js in jupyter notebook
- Save a pivottablejs figure to file