2

I have a data set generated similar to the following table.

Label | X | Y | A | 1 | 1 | B | 2 | 2 | B | 3 | 2 | A | 4 | 3 | C | 5 | 4 | A | 4 | 3 | C | 2 | 1 | This list will expand to 1000 of items

This Data set will be generated using k-mean cluster algorithm. So I want to show my output in a graph, as clusters. These data will be generated using another program in C language. Once I get these data, I want to plot them in Excel as clusters as shown bellow in different colors for each cluster. Can you please tell me how can I do this

enter image description here

2 Answers 2

2

For each cluster, create a column next to your source data and use this formula to get the Y value if the row label equals the column label:

=IF($A2=D$1,$C2,NA())

Copy across and down. Construct a scatter chart with the three series A, B and C, all of them using the same X values.

enter image description here

1

I propose one tool named Funfun which supports cluster analysis in Excel.

It allows people to code JavaScript and Python (rather than classic VBA macros) in Excel. It also supports external libraries. With this tool, you can use any JavaScript library you like to do data analysis (e.g. cluster analysis) or to plot graphs.

To answer the question, the function takes input in the format of JSON array (defined in the settings) and save them to a JavaScript variable. And then creates a link between them and spreadsheets.

Here I uses CanvasJS and jQuery to plot this chart. Clusterfck.js is used for the cluster process. The core code:

var nb_clusters = 4;
// Calculate clusters.
var clusters = clusterfck.kmeans(data, nb_clusters); // data, nb_clusters
var chart = new CanvasJS.Chart("chartContainer", {
  title: {
    text: "Birth and death rate"
  },
  width:500,
  axisX: {
    title: "Birth rate",
    minimum: 0,
    maximum: 60
  },
  axisY: {
    title: "Death rate",
    titleFontSize: 16
  },
  data: [
    {
      type: "scatter",
      markerType: "circle", 
      dataPoints: process(clusters[0])
    },
    {
      type: "scatter",
      markerType: "square", 
      dataPoints: process(clusters[1])
    },
    {
      type: "scatter",
      markerType: "square", 
      dataPoints: process(clusters[2])
    },
    {
      type: "scatter",
      markerType: "cross", 
      dataPoints: process(clusters[3])
    }
  ]
});
chart.render();

Here’s the screenshot:

screenshot

As you can see, it uses k-means cluster with k=4. You can change the hyper parameters in the code.

The link of this function of online playground. The screenshot is as follows:

screenshot

The second pane is a preview of Excel. The third pane is the coding area. The last pane is the output of the code.

Hope it helps and have fun :)

P.S. I'm a developper of Funfun

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .