project 2#: cluster analysis

2020/10/18

https://canvas.oregonstate.edu/courses/1782574/assignments/8052052 1/ 5

Project #2: Cluster Analysis

For this project, we’re going to use cluster analysis to "tell a story" about our data. I’m asking you to divide the Oregonians in your sample

into groups or clusters based on two quantitative variables. Your "story" will be an explanation of your data that highlights some

interesting feature(s) or makes a point about the data.

Please note this project will likely take some trial and error. Please relax into it and have some fun with the process: think of it as an

exploration. Trial and error is the spice of life!

You will begin by opening up the OregonPUMS data set. Take a small subset of this data (I recommend n=400, so as not to upset

XLSTAT too much: some clustering algorithms grind to a halt with large data sets).

Process:

Step 1: Select your sample of n=400. Lucky for us, XLSTAT is quite good at taking a random sample. Check out:

Simple Random Sampling in XLSTAT There are many ways to take a random sample in Excel using the rand() function, but XLSTAT makes this step a bit easier.

To begin, open up your data set in Excel and click on the XLSTAT tab. Then click:

Preparing Data -> Data sampling

2020/10/18

https://canvas.oregonstate.edu/courses/1782574/assignments/8052052 2/ 5

In our case, suppose we would like to choose "Random without Replacement" so that there isn't a chance of selecting the same sample twice. We would like to sample 1 group of 100 samples, so we can let Number of Samples =1 and Sample size =100. Our data has variable labels and I have no need for the report header. Choosing shuffle is optional.

From there, choose OK, then Continue.

Your sample should then be on the next sheet. From here, the first two rows and the first column are unnecessary.

2020/10/18

https://canvas.oregonstate.edu/courses/1782574/assignments/8052052 3/ 5

After deleting the unnecessary rows/columns, you will be left with the following. And then you're ready to analyze your sample! Have fun!

2020/10/18

https://canvas.oregonstate.edu/courses/1782574/assignments/8052052 4/ 5

Sampling from Filtered Data:

In the event that you would need to sample from filtered data (if you had to filter by airport, for example), then XLSTAT does that too. Go ahead and use Sort&Filter so that only your airport of choice is shown. Then choose Preparing Data -> Data sampling and repeat all the steps from above. When you click OK, XLSTAT will ask if you only want to sample from the filtered data. Then you can choose option 1.

2020/10/18

https://canvas.oregonstate.edu/courses/1782574/assignments/8052052 5/ 5

From there, you should have a random sample from your filtered data.

Selecting Multiple Samples:

Suppose that you would like to take multiple samples (from DepDelay perhaps) all at once! This is something that XLSTAT does quite well.

As before, choose Preparing Data -> Data sampling. You may take your sample from the entire data set, as I have done above, or you can sample from a particular column.

Next, in the "Number of Samples" box, select the number of groups you would like to sample. For example, in project 5, you would need to sample 5 groups. Then next to "Sample Size," type the correct sample size. Initially, in Project 1, the correct sample size would be 4.

2020/10/18

https://canvas.oregonstate.edu/courses/1782574/assignments/8052052 6/ 5

If you also click on the "Display side by side" feature, your sample will come in the output shown below.

2020/10/18

https://canvas.oregonstate.edu/courses/1782574/assignments/8052052 7/ 5

Now, if you chose to sample from just a column of data and the entire data set (all 40+ columns of data), then on the plus side, your output is presented in this cute little table. On the downside, look at all those green triangles in the upper-left corner of each cell in my table! This is because the numeric output is in a text format. That is not ideal when working with numeric data. I don't know why XLSTAT thought this would be convenient. Not to worry though, if you highlight all the data in your sample, click the down arrow next to the warning symbol, and choose the "convert to number" option, all will be right in the world.

From there, you can use your data to find averages, sample standard deviations, run an ANOVA, etc. Have fun!

Step 2: Choose two QUANTITATIVE variables that you would like to work with. Copy and paste your two variables and their corresponding

sampled data (there should be 400 rows of data, two columns) into a new sheet. I prefer to do this so that I am not overwhelmed by

variables that I am not using. Next, remove any rows with missing observations. This will save time later when you go to plot your clusters.

Step 3: Use different options in the software to create 5 different "data stories": if you're overwhelmed about what to pick, you can use

these options:

*Scatterplots will have to be created separately using the Results by Object output. Under the colors tab, use whatever colors you would

like, but be sure they are bold and distinct. For example, it would be a bad idea to use white or both red and red-orange.

Step 4: Write up your project! Which clustering method out of the five did you prefer? Why?

For your final report, compare and contrast each of the five clustering methods. You may choose to use your XLSTAT output or use

Tableau/other software to make a prettier graph. Tell your story using your preferred clustering method, and how the clustering supports

that story. Who are these groups? What does this clustering tell us about the people in Oregon?

To really impress, give a little flavor! Describe a set of particular individuals who exemplify each cluster.

Rubric for Project (40 points

Project 2: Clustering Rubric

15 points: at least 5 different graphs, all using the same basic variables (Step 1) but different clustering choices (Step 3). Data process

and data product both discussed, particularly for Method 5.

10 points: your narration of the progression of your thinking (data process story).

5 points: Instructor’s subjective take on the product story. Was it gripping, interesting, well done?

5 points: graph conventions, labels, etc.

5 points: conventions: correct punctuation, sentences, etc.

5 points for early submission! Early submissions due by October 21st at 11:59 PM.

https://help.xlstat.com/s/article/agglomerative-hierarchical-clustering-ahc-in-excel?language=en_US

htt p s://help.xlstat.com/s/article/k-means-clustering-in-excel-tutorial?language=en_US ( https://help.xlstat.com/s/article/k-means- clusterin g -in-excel-tutorial?language=en_US)

htt p s://help.xlstat.com/s/article/scatter-plot-with-confidence-ellipses-in-excel?language=en_US

( https://help.xlstat.com/s/article/scatter-plot-with-confidence-ellipses-in-excel?language=en_US)

standard gra de

Score

5 Different Clusterings 15.0 score

Full Marks

12.0 score

Omitted a clustering, or didn't describe well

9.0 score

Omitted two, or described poorly

0.0 得分 No Marks 15.0 分

Data Process Story 10.0 得分 Full Marks: clear and compelling

for instance, a few thoughts clearly

linking each graph- what you liked, what

you didn't, how the following graph

compares.

8.0 得分 Good

For instance, a clear explanation of

the general process which doesn't

quite encompass all 5 graphs

4.0 得分 Limited

Lowest possible rating if it's

clear you tried to address the

process you used/followed. 10.0 分

Interesting? 5.0 得分 Full Marks

Group conveys why we

should care about this

data story.

4.0 得分 Good

Clumsy or incomplete

explanation of

purpose/interest.

3.0 得分 Partial Credit

No mention of why/how this

data story matters, but topic is

inherently interesting.

0.0 得分 No Marks

Group self-sabotages

(e.g. "this isn't worth

caring about") 5.0 分

Writing Mechanics 5.0

得分

Full

Mark

s

4.0 得分 "Picky Problems"

for example, affect/effect,

its'it's, there/their/they're

3.0 得分 Ambiguity

grammatical errors

that interfere with

reading

1.0 得分 Egregious

Errors that make it impossible to

determine what the author meant

0.0

得分

No

Mark

s

5.0 分

standard gra de

Sco re

Graphing Mechanics 5.0

得分

Full

Mark

s

4.0 得

分"Pick

y

Problem

s"

for instance,

ignored

overplotting

3.0 得分 Serious problem

for example, unlabeled axes or non-human-

readable labels ("HINCP" in place of "Annual

Household Income in $")

1.0 score

Egregious

A graph exists,

but has multiple

serious problems

0.0 score

No

Marks

5.0 points

Early Submission!

5 points of extra credit

if your project is

submitted by 10/21/20

at 11:59 PM.

0.0 score

Full Marks

0.0 score

No Marks

0.0 points

Total score: 40.0 out of 40.0