Liam Green-Hughes

pivot.js helps us get your data your way

Being able to work with data and drill down to the details you need is essential if you want to get the most out of the information in front of you. Many data analysis tasks usually require a fairly advanced understanding of a spreadsheet program, but Pivot.js is a very handy Javascript library that makes many analysis tasks possible in a web browser. It takes a CSV file and builds a table of results in a web page allowing the user to show and hide columns, generate summary values and filter data. We have been using this library in a recent project and have been delighted with the results.

csv on the server side

On the server side all Pivot.js expects us to provide is a CSV file. In our project this is dynamically generated from the contents of a Mongo database. As Mongo is a NoSQL is does not store data in flat tables like a traditional database. Instead it stores data in a document like structure, so we have to flatten out the data into rows before converting it to CSV. For example if you had a Mongo record for a book the same document could contain details for each edition of the book. If we wanted to convert this record to CSV we would need to repeat the book details for the record for each edition to flatten out the data.

An added bonus of generating these CSV endpoints is that a user can use tools other than Pivot.js if they wish. They could download the CSV into their favourite spreadsheet programme for example.

pivotjsscreenshot

flexible but easy for the end user

Once the raw CSV files are available it is time to wire these into Pivot.js. This involves telling Pivot.js about the columns in our CSV data and the summaries we want. Using display functions do such things as create links and format numbers nicely. We can also generate columns from the values of other columns and summary values thanks to pseudo columns. This was very flexible although we did find that we could not perform any calculations based on the differences between a current and last row as we found the rows were not being passed into the functions in order.

After getting the data on screen in a user friendly way, the next task was to think about the user interface to control filtering and which columns to show. We chose to tie this closely with the aims of the project so the controls we ended up with are not generic but designed to be specific to the tasks a user might want to achieve. Adding this sort of customised interface in Pivot.js was pretty straightforward thanks to the way it has been built. Pivot.js can be manipulated to quite a low level using Javascript so it offers a lot of flexibility in this area.

performance

A concern we had initially was that this solution might be a bit slow given the large amount of data in our project. The Pivot.js demo site shows a CSV file of five thousand rows being manipulated and coping well. In our project we found that despite the dynamic calculations taking place Pivot.js performed very well with page generation taking no more than a few seconds at the most.

We’re pretty impressed with Pivot.js. It is a powerful tool, yet it is possibile to use it to make a system more useful to an end user without making it complex to use. If you are developing a solution that uses it then it is best to to take some time to learn about the library and experiment with it before diving in so that you can get the most out of it. Now that we have tried one project with the library we are thinking about how it could be useful to future projects too.

blog comments powered by Disqus