Data Analyzer: HTML Table to Chart

• Download source - DataAnalyzer_v2
• Download source - DataAnalyzer_v1
• Demo - http://jsbin.com/iqojut/1/

Introduction

"Data Analyzer", as the name says, analyses the data available in the form of HTML table and provides you all possible options of grouping the available values to display it in the form of chart (bar chart / pie chart).

Background

Whether it was a stone age period or the period of super computers, the well known saying - "picture speaks a thousand words" has proved correct in all kinds of information conveyed to others.

Based on my experience in the field of development and designing, I have seen that majority of the web sites / web applications are generating HTML tabular contents in the form of reports. These reports could be generated using any of the tool/technology available in the market. Of those, there will be very few sites / applications that provide the option of displaying charts as well as reports, simultaneously. Even if they provide it, users of the sites / applications will have very limited scope in playing with the report contents thereby changing the charts, dynamically.

So, here, I am presenting you a complete JavaScript based solution, where you just need to add few lines to integrate your HTML table contents with a chart of your choice.

Sounds Good? Let's Start...

Imagine you have HTML table contents as shown in figure-1 below:

It is good to provide users with a detailed report about certain functionalities, but what about the search / filter facility? You might need to write some extra logic in the back-end / front-end tool for the same.

For the sake of understanding, I have added an action button (Analyze) in this table as shown in figure-2 below:

There is no such need to add any button, you can invoke the script directly on page load. Now, when you click on this action button, some magic occurs and you will see some new drop-down boxes added along with the total record count as well as multiple chart options, as shown in figure-3 below:

Here, you can see that all columns available in the table are automatically added in the filter criteria, along with an added option of 'Global Search' which can perform search on all the text contents of the table. Using this filter utility, user can perform search globally or specifically for any column. For example, I would like to search for something called 'Alex'. Figure-4 below shows that the table contents are automatically filtered showing only the records having content - 'Alex'. It has also updated the total record count.

In Figure-3, you might have noticed that at the bottom of action button (Analyze), some extra drop-down boxes are added. These are:

1. Chart Type: Indicates what type of chart you would like to see. Possible values are:
   • Bar Chart
   • Pie Chart
   • Random (Any of the above chart types could be selected randomly)
2. Possible Charts: Based on the analysis carried out, this drop-down box provides list of all possible charts from the data available. As per the logic, these charts are always based on the HTML table header columns. Also, this gets populated dynamically.
3. Along with (Operation): [Optional] This is an add-on feature for version 2.0. It says what mathematical operation you would like to perform during the chart generation. Possible values as of now are:
   • Sum
   • Average
4. Of (Numeric column): [Optional] This is again an add-on feature for version 2.0. It says on which numeric column you want to perform the operation specified above.

All these values combine together to provide you the result as Chart.

Data Analyzer Version 2.0 onwards, I have removed the JSChart library. And hence, the selection of chart library has been removed. By default, the output will be rendered using the Open flash chart (OFC) v1.0 (http://teethgrinder.co.uk/open-flash-chart/).

On selecting any of the options from the Possible charts drop-down box, you will see that the chart is displayed as shown in figure-5 below:

Up to this point, most of you might be thinking that this is wasteful effort in redeveloping things already available for free. Now the step ahead.

Select the operation and any of the numeric column from the list (as shown in figure-3 above). When this information combines with that of the selected chart, the outcome will be as shown in figure-6 below:

While figure-5 was showing the total count of records when grouped by Vendor Name, figure-6 is showing the aggregation of service charges incurred by the Vendors. Isn't that exciting ??

You saw the filter utility in figure-4 above, right? So, once you filter your data, how can you draw another chart based on the filter records? Here is the solution. In this demo, after performing the filter operation, again click on the Analyze action button. Aaha !!! your charts are ready to go. The Possible charts drop-down box will be refreshed with new contents, based on the changes in the table content, as shown in figure-7 below:

And related chart is shown in figure-8 below:

Let's perform some operation on this filtered data. Figure-9 below shows the average service charges the citizen- Alex paid for the services offered to her.

Add more complexity ? Let us perform multiple filter condition. The output would be something as shown in figure-10 below:

Let us see how the "Random" Chart type looks like? and what will happen when you select "Show All Charts" option from the possible charts drop-down box? The output will be random display of all the charts based on the operations selected, as shown in figure-11 below:

How about error handling? What if there are no records found? Well, everything is handled in the code. If there are no records available, or if all available records are distinct, due to which, it's not possible to generate the chart, you will see the message accordingly. Have a look at figure-12 below:

Well, that was all about the features of Data Analyzer. Hope you found it interesting. If so, read a bit more to learn how to integrate it with your code.

Moving Forward: The Integration Steps

To start with, the following steps are required to be carried out in order to integrate Data Analyzer with your current HTML code.

1. Include the following CSS and JS files in your HTML page HEAD section:
   HTML

   <link rel="stylesheet" type="text/css" href="dataanalyzer.css" />
   <script language="JavaScript" src="DataAnalyzer.js"></script> 

2. Include following scripts for Open Flash Chart. Add / replace it with your chart script (but you might need to modify the code of Data Analyzer accordingly).
   HTML

   <script language="JavaScript" src="swfobject.js"></script>

3. Create two DIV place-holders for displaying chart and error messages:
   HTML

   <div id="AnalysisStatus"></div>
   <div id="chartContainer"></div>

4. Create object for DataAnalyzer and pass the required parameters:
   HTML

   <script language="JavaScript">
     var analyzer = new DataAnalyzer('analyzer', 'InvoiceTable', 1, true);
     function doAnalyze() {
       analyzer.start();
     }
     // invoke function doAnalyze() on-load or on user event (button or link click).
   </script> 

And that's it. We are ready to roll the ball !!!

Understanding the API

Well, if you have found this interesting uptil this point, it would be nice have a proper understanding of the APIs used in Data Analyzer.

As you might have noticed, Data Analyzer object is created just like other JavaScript objects. The syntax is:

var analyzer = new DataAnalyzer
(thisObject, dataSourceId, vHeaderStartsAt, vAddFilter, vBypassCols, vAnalyzeCols);   

Meaning of the Parameters

• thisObject: [Mandatory] Self referencing object name. It will be the name of the variable of Data Analyzer object.
• dataSourceId: [Mandatory] Id of the HTML table to be referred and analysed.
• vHeaderStartsAt: [Optional] Row index of the header row of the HTML table. Default is 1.
• vAddFilter: [Optional] Flag to determine whether you want to include the filter option or not. Default is false.
• vBypassCols: [Optional] Array of either column indexes or 'named' columns (i.e., name of the header column AS-IS), or mixture of both. Columns listed in this array will neither be considered for the filter criteria nor it will be considered for the 'Possible charts' option. This parameter value has low priority if value is supplied for the parameter vAnalyzeCols.
• vAnalyzeCols: [Optional] Array of either column indexes or 'named' columns (i.e., name of the header column AS-IS), or mixture of both. Columns listed in this array will only be considered for the filter criteria. But for the 'Possible charts' option, they may or may not get included based on the analysis. This parameter value has more priority compare to the previous vBypassCols parameter value. If supplied, value for vBypassCols will be ignored. Reason for adding this as a last parameter is that the parameters required for analysing are more compared to that needs to be bypassed.

Next in the API list is the set of public methods supported by Data Analyzer. These are:

(A) Start Method

This is the very first (and only) method that developer will need to handle/invoke. Remaining methods are only required to be invoked manually if developer has written some custom code on top of it. The syntax is:

analyzer.start(vFilterOnly, vAutoAnalyzeOnFilter); 

Meaning of the Parameters

• vFilterOnly: [Optional] Default value is false. If set to true, and vAddFilter parameter in the constructor above is also set to true, it will disable the charting option. Only HTML table content filter option will be available. if vAddFilter parameter is false, then any value of this parameter is irrelevant.
• vAutoAnalyzeOnFilter: [Optional] Default value is false. If set to true, the HTML table content will be automatically analysed and related 'Possible Charts' drop-down box will get populated. When set to false, developer needs to invoke analyzer.start() method based on any user event (button click etc). if vFilterOnly parameter is true, then any value of this parameter is irrelevant.

The table below shows possible outcomes for various combinations of using these parameters:

(B) Filter Table Method

This method generally automatically binds with the search text-box that gets created based on the vAddFilter parameter defined above. There won't be any explicit need to invoke this method. The syntax is:

analyzer.filterTable(phrase, tableId, searchIn, headerStartsAt);

Meaning of the parameters

• phrase: [Optional] Filter phrase to search the HTML table contents. If left blank, all records will be displayed AS-IS.
• tableId: [Mandatory] Id of the source table whose contents will be searched for.
• searchIn: [Optional] Column index of the table to be looked for the phrase for filter. If left blank, supplied phrase will be searched in all the columns.
• headerStartsAt: [Optional] Numeric field to identify from which point the table content starts. Default is 1.

(C) Draw Method

This method generally automatically binds with the 'Possible Charts' drop-down box. There won't be any explicit need to invoke this method. Developers in need to integrate their custom chart library might need to look at the code of this method to modify it as required. The syntax is:

analyzer.draw(reportIndex);

Meaning of the parameters

• reportIndex: [Mandatory] Index of the chart to be drawn on screen. It could be individual chart index or all possible charts.

Customization

If you like this concept of displaying tabular reports and generating charts from it, I am sure you would be thinking of customizing it based on your application / web-site. Below are some points where you need to make changes that suit your requirements:

• Modify dataanalyzer.css as required.
• Include your custom chart library JavaScript file
• In the DataAnalyzer.js file, the following changes are possible:
  • Modify private method - addAutoFilter() to change the text, alignment, format, etc.
  • Modify private method - report() to change the chart configurations - Chart type, text, alignment, format, etc.
  • Modify public method - draw() to implement custom chart and related formats
  • Modify public method - filterTable() to customize the style for alternate row color, text, etc.

Browser Compatibility

This code is compatible with all major browsers - Internet Explorer, Chrome, Firefox, Safari, etc.

Behind the Scene: Algorithm

This section is for people who love algorithms and would like to go deep into the actual logic implementation of the solution / tool. Below are the steps I followed for designing this Data Analyzer.

1. analyzer.start() method internally calls the private method - init()
2. init() method will validate:
   • the parameters passed
   • whether the HTML table contains enough rows to render chart
   • Specific columns to analyze or bypass
   Based on the validation, it will either return success or error code.
3. Next, analyzer.start() method will call the private method - analyze().
4. Actual data analysis is handled in this method. It is again grouped into four sub-routines:
   • Analysing the table headers
     • This is done using the private method - analyzeHeaders(). It will parse the header row (based on the index supplied in the constructor parameter). This parsing will prepare a METADATA for each and every column.
     • Metadata attributes are - columnRow, columnIndex, columnLabel, columnType (initially unknown or null), and columnData (intially blank)
     • There won't be any metadata for the header columns with no text contents (these columns might be used for formatting and spacing purposes).
     • If vAnalyzeCols parameter is passed, metadata will only contain the header columns that match the values available in this array.
     • If vBypassCols parameter is passed, header columns matching with the values available in this array won't be included in metadata.
     • At the end, it will check for the flag - vAddFilter If true, then it will invoke another private method - addAutoFilter().
       • addAutoFilter() method iterates through the metadata and will create the dynamic HTML code that will be placed above the HTML table. This is how the automatic search/filter functionality is added in.
       • When particular text is searched, all those rows that do not contain the search text will be made hidden from the table. On clearing the search text, these hidden rows will again gets visible.
   • Analysing the table contents
     • This is done using the private method - analyzeData(). It iterates through every visible row and every individual column in it.
     • Table contents are only analysed when any of the flag - vAddFilter or vFilterOnly is set to false. When both are true, there is no need to process and generate charts.
     • Empty columns will by bypassed from the analysis
     • Metadata attribute - columnData will be updated with the column information which identifies the total occurrences of the cell text within whole column. This information is used for generating the 'Group by Chart' for that column.
     • Again, a uniqueness check will be carried out to see if any of the column contains unique records. Rule is - When total number of cell text identified in above step equals the total number of rows in the HTML table, then that column is a unique column. And hence it will be bypassed from the 'Group by Chart'.
   • Preparing datasets for chart
     • This is done using the private method - prepareDataSets(). Datasets are nothing but the format in which every chart takes input.
     • The content of dataset will be - chart title, X-axis label, Y-axis label, category and count
     • This method will iterate through the metadata records in order to prepare the dataset.
     • Due to filter condition, it is possible that one or more columns will have the uniform values. These values will be used to identify the extended grouping of data.
     • This method will dynamically prepare the chart title based on set of all uniform values identified.
   • Populating the chart drop-down box with all possible chart options or showing the error message
     • This is done using the private method - report().
     • Based on the datasets prepared, this method will populate the 'Possible Charts' drop-down box with list of all available charts.
     • In case there are no charts possible, reason being all records contains unique values, this method will display the error message stating all records are distinct.
     • User can select appropriate chart from the drop-down to see it in action.
5. Next and final step is to render the chart based on user selection. The onchange event of the drop-down will call the analyzer.draw() method, with the selected chart option passed as parameter. If the "Operation" and "Numeric Column" are selected, then this method will invoke the private internal method - updateDataSet(operation, numColumn, reportIndex). This method will perform the required calculations and update the dataset that will be used for rendering the chart.

History

• 10^th January, 2013: Initial post. Version 1.0
  • You can view the details of version 1.0 from the revisions link at left side of this article.
• 15^th January, 2013: Version 2.0 released
  • Changes are:
    • Added support for selecting the mathematical operations to generate chart
    • Removed JSChart library
    • By default, the charts will be displayed using Open Flash Chart library
• 14^th February, 2013: Added demo site link

Note

Please rate this article if you liked this. Also, let me know your feedback for the same.

If you have some suggestions about the features to be included in this utility, please do let me know so that everyone can use it.