Test of using https://github.com/nicolaskruchten/pivottable. Pivot table demo src files are pulled (without permission) from nicolas.kruchten.com
#Use the HTML table demo - and its libraries - as the crib
!wget http://nicolas.kruchten.com/pivottable/dist/pivot.js -P pivotTable
!wget http://nicolas.kruchten.com/pivottable/dist/pivot.css -P pivotTable
!wget http://nicolas.kruchten.com/pivottable/examples/ext/jquery-1.8.3.min.js -P pivotTable
!wget http://nicolas.kruchten.com/pivottable/examples/ext/jquery-ui-1.9.2.custom.min.js -P pivotTable
html_base='''
<html>
<head>
<title>Pivot Demo</title>
<link rel="stylesheet" type="text/css" href="{path}/pivotTable/pivot.css">
<script type="text/javascript" src="{path}/pivotTable/jquery-1.8.3.min.js"></script>
<script type="text/javascript" src="{path}/pivotTable/jquery-ui-1.9.2.custom.min.js"></script>
<script type="text/javascript" src="{path}/pivotTable/pivot.js"></script>
</head>
<style>
* {{font-family: Verdana;}}
</style>
<body>
<script type="text/javascript">
$(function(){{
var input = $("#input")
$("#output").pivotUI(input);
}});
</script>
<div id="output" style="margin: 10px;"></div>
<br /><br /><br />
{table}
</body>
</html>
'''
def table_to_html(df,fn='tmp/tmp.html',path='..'):
f=open(fn,'w',encoding="utf-8")
html=html_base.format(path=path,
table=df.to_html(index=False).replace('<table','<table id="input" style="display:none"'))
f.write(html)
def pivotTable_fromhtml(fn,width=1000,height=600):
return IFrame(fn,width=width,height=height)
def pivotTable(df,width=1000,height=600,fn='tmp/tmp.html',path='..'):
table_to_html(df,fn=fn,path=path)
return pivotTable_fromhtml(fn,width=width,height=height)
import pandas as pd
df=pd.read_csv('data/IW_PUBLISHED FORMAT - JAN 2014.csv',thousands=',',encoding = "ISO-8859-2")
df[:5]
Capital or Revenue | Directorate | Transaction Number | Date | Service Area | Expenses Type | Amount | Supplier Name | |
---|---|---|---|---|---|---|---|---|
0 | Revenue | Community Wellbeing & Social Care | 5105650243 | 29.01.2014 | Drug Misuse - Adults | Charges from Independent Providers | 120.00 | REDACTED PERSONAL DATA |
1 | Revenue | Community Wellbeing & Social Care | 5105646636 | 15.01.2014 | Drug Misuse - Adults | Charges from Independent Providers | 120.00 | REDACTED PERSONAL DATA |
2 | Revenue | Community Wellbeing & Social Care | 5105648361 | 22.01.2014 | Leaseholds by LA | Accommodation Costs - Leaseholder Payments | 695.89 | REDACTED PERSONAL DATA |
3 | Revenue | Community Wellbeing & Social Care | 5105648361 | 22.01.2014 | Leaseholds by LA | Accommodation Costs - Leaseholder Payments | 695.89 | REDACTED PERSONAL DATA |
4 | Revenue | Community Wellbeing & Social Care | 5105648361 | 22.01.2014 | Leaseholds by LA | Accommodation Costs - Leaseholder Payments | 695.89 | REDACTED PERSONAL DATA |
table_to_html(df[['Capital or Revenue','Directorate','Amount']][:100],'tmp.html',path='.')
pivotTable_fromhtml('tmp.html')