Show/Hide Toolbars

MPCluster for Maptitude

Navigation: Running MPCluster

Exporting Clusters to Microsoft Excel

Scroll Prev Top Next More

The Display Options box also includes an Export to Excel check box. Set this check box if you would like lists of all the clusters and component data locations to be written to a Microsoft Excel workbook. You must have Excel installed in order to use this option.

 

The output workbook starts with an optional Parameters worksheet which lists the cluster run's parameters. This is followed by a Summary worksheet which lists each cluster. This gives an identifying number; central location as a longitude,latitude coordinate; and the number of component data locations. It is also possible to select cluster-wide sums and/or averages of data fields and write these to the Summary worksheet.

 

MPCluster will also list the individual component data locations. These can be combined into one long worksheet, or listed in individual worksheets. These listings list the data location's name; parent cluster; longitude,latitude coordinate; parent dataset name; and any data fields that are present.

 

Finally, an Unallocated worksheet is produced. This lists all data locations (if any) that were not allocated to a cluster, and is formatted in the same manner as the other cluster worksheets but excludes the parent cluster column.

 

For both Excel output options (combined and individual worksheets), the data field output can be slow. For large datasets, it is generally recommended that you minimize the number of pushpin data fields in order to speed-up the export process.

 

Setting the Excel Options

 

Set the various Excel options by pressing the Excel Options button in the Display Options frame. This will display the Excel Output Settings dialog box:

 

excel_dialog

 

Listings of the cluster component data points can be written out to one combined worksheet, or individual worksheets for each cluster. Set the Write each cluster to its own worksheet check box if you wish to write the data points to individual worksheets for each cluster.

 

Set the Write all parameters to a worksheet to produce a Parameters worksheet that summarizes all of the cluster run's parameters.

 

The Summary worksheet lists one row per cluster. It is possible to also include data fields from the component data fields, that are summed or averaged on a per-cluster basis. Select worksheets for this by using the Summary Worksheet table. This lists all of the numeric data fields that are available in the dataset. Simply check the check box in the Sums column and/or Averages column to select a data field for summing and/or averaging. In the above (Excel Options) example, the Tornado (2007) data field will be averaged, and the Population (2007) data field will be summed.

 

The two red icons mark the data fields that have already been selected for use in the cluster computation. The asterisk icon indicates the data field that will be used to weight the cluster center positions; and the downward pointing arrow indicates the field that has been selected for the sum constraint. These functions are described further on the Applying Data Fields page. In the above example, the Population (2007) data field has been selected as both the sum constraint and the weight in the main MPCluster panel.

 

Sample Output Worksheets

 

Here is a sample summary worksheet list the individual cluster locations and sizes:

 

sample_excel_summary

 

Clusters created by MPCluster will be given cluster names that are numbered (as above). Pre-defined fixed clusters will use the name identifiers provided with the input cluster positions. These names are also used for the worksheets that contain each cluster's data points.

 

And here is an example of one of the clusters. The first five columns are produced by MPCluster from the available data. The remaining columns are fetched from the data fields which are found for these data locations.

 

sample_excel_cluster

 

Here is an example of the parameters worksheet:

 

excel_parameters