Show the Data: Cluster Charts

In the last post, we explored the idea of adding bump(s) charts to our rotation of how we communicate our data. It's one way to show all of the data in a particular set. Another one I've been using quite a bit is a cluster chart. Full disclosure here, these are my own take on displaying data---a bastardized heat map, and certainly not based on heavy-duty math like real hierarchical cluster charts. So, really, I'm not sure what to call these...but in my current role, we're finding them to be very useful and I'm just rolling with cluster charts as my category.

This spreadsheet will eat your soul.

I get a lot of spreadsheets sent to me that look like the one on the right. I hate these with a fiery passion for a variety of reasons:

• Too much "ink" in the data-to-ink ratio. With all of those little boxes, I don't know where to look.
• And the colors. I feel like a circus came to town. But beyond that, the red and green are not particularly friendly to those with color vision issues...and I do work with some who are color blind. Are we really asking them to try and make decisions on student learning  based on this?
• Not to mention that all of the data is colored in. What's the point?
• And we have both numbers and colors. I'm not saying that you can't have both...or that they don't serve different purposes...but it's distracting. I'm constantly trying to make sense of the number patterns for each color.

I also think these data aren't useful because of the way they are organized. Alphabetical order is great for gradebooks, but not so much for trying to make sense of the data. Plus, we don't have any context---what if we're missing some signal in the noise? Suppose all of our low-performing students are boys...or in a minority group?

But let's say you are interested in showing both the progress students have made over time, as well as the characteristics of the students involved. We can reorganize the data by ranking the percentages on the second assessment (this is the "cluster" part). Then we can color code some additional information, such as gender or participation in a particular program. I also change the properties of the conditional formatting so that the fill and text are the same color, making the values seem invisible. Finally, I add thick white borders around all of the cells and resize the rows and columns. Here is a small part of the final product:

These are all the students who scored in the top level of our fictional grade 5 winter math assessment. Three of them improved a little, from light blue to a darker blue...others improved from lower on the scale (orange). But when we look at gender and program, another story emerges. Most of the students in the top category are female, not on free or reduced lunch, not in special education, and do not receive additional interventions through a Title I program.




See the difference when we look at students who have scored at the bottom for both fall and winter? Our population is mostly male and nearly everyone participates in one or more federal programs.

Maybe this representation doesn't necessarily hold any surprises, especially as we factor in free/reduced lunch. Children living in poverty typically do not perform as well as their peers. But one of the things I take away from this way to visualize that story is that we may need different interventions to support these students. Consider Student 39 on the right. He is receiving free or reduced lunch, special education and Title I services...and he's still ranked fourth from the bottom out of nearly 70 students. It doesn't mean that the school (and the student) aren't working as hard as they can. I do think it might mean that there are additional factors at work here that aren't (and can't be) addressed through the school. Perhaps the family is homeless or transient. Maybe the parents are going through a divorce...or the student has some medical issues. These are community-based issues and require different interventions to help close the gap for the student. I won't get up on my left coast soapbox about this right now. I'll just say that we have to work together on behalf of the whole child.

One of the pieces of information that is not represented in the visuals above is the number one item on teacher wishlists when it comes to reporting scores: progress. Sure, we have a bunch of students performing at the lowest level in the picture above, but that doesn't mean that they didn't make some growth.

This time around, I left the gender and program pieces coded the same, but I calculated the percent change between fall and spring and represented those in the leftmost column.

Look at Student 39 now. He's 12th from the top. Woo-hoo!

When we consider progress, we start to get a more equitable pattern---everyone is growing, and more often than not, it's our lowest performers who are making the biggest gains, even if they're still in the lowest part of the score breakdown.

By clustering similarly performing students together, either by scores or by progress, we get a much more useful pattern than we do with a spreadsheet that looks like a clown exploded on it. And, more importantly, we can show the data. In a very compact space, I can display everyone's scores and whatever demographic or program information is most relevant. And, I can fit the whole grade level on a single page.

I have no doubt that as we move forward, smarter people than me (I?) will continue to find new charts that help share everything we know about a group. Summary stats and charts will never go away---and they have their own purpose to serve. But sometimes we want the full version, not the Cliff's Notes. When we do, bump and cluster charts will be there.

Bonus Round
Want to see just how challenging colored squares can be? Play this online game. The rules are easy: just click on the one square that is different.

Getting It All Laid Out

One of the MOOCs I'm currently enrolled in is Alberto Cairo's Introduction to Infographics and Data Visualization. So far, it's familiar territory, which is nice. Some things are new to me, but I am not overwhelmed because everything is new to me. It's a six-week course. We're starting the third week and have our first offline assignment.

Imagine that you report to me (your managing editor in a news publication). You wish to make a proposal for a visualization based on these numbers. How would you convince me that your idea is relevant? You will need to show me detailed sketches (made by hand or through a design program) to do that.

By "these numbers," he is referring to these data about the changing number of tenured faculty at U.S. universities. I've downloaded the data, but I'll need to do some more reading and digging before I'm ready to do something with them. I have to figure out the "So what?" before I draw up a presentation of the data.

In the meantime, I thought I'd share a bit about the process I use when building a data display...something I worked on this week.Over the next month, I'll be sharing some data with groups of small districts. The data is not so much a report---they already have some of it in various forms---but something to explore in common. These groups of districts will be trying to identify some common ground as a starting point for some work together.

I started by finding all of the data I could about these districts: fiscal, staff, and student-level. Then I pared down the data sets by thinking about what the audience would want to see. Next, I got out my pencil and some scrap paper. It was time to draw some scenarios. I think that even if my digital tools weren't limited to Excel, I would still begin with an analog model. I like to list the types of things I want to show and then figure out how to arrange them. Mind you, this is only a starting point. I often find myself in the middle of developing something, only to find out that it didn't make sense after all.

Finally, I get knee deep in Excel. For this project, I ended up with three different displays: one that showed overall trends in student performance, another to dig deeper into performance in various subject areas, and a demographics overview for each district.

Here is the first one of the series. I am still struggling with it in terms of whether or not to go with clustered columns instead. Such a display does make it easier to compare data over the years, but since I am including both regional and state level data for three different years, clustered columns get a little busy. The reason why I am leaning toward the version shown is that it is easier to compare patterns between the region and state.

There are things I don't like about this display, so if you have some ideas about fixing it, I'm all ears. For example, I'm not convinced about using different colors for the bars in the top row, but since I'm not going with the clustered columns, I think the color helps make comparisons across the years. I really don't like the labels along the bottom set of charts. I suppose I could shorten them and then create some sort of legend that gives the real version. There are two choices a user can make in the interactive version of this chart. They can pick a subject area (reading, math, writing, science) and grade level (3 - 8)---so not all of the labels are as cumbersome as they are for reading.

The second display shows trends for graduating cohorts---although these students may be many years from walking across the stage. The purpose of this display is to look at performance for the same set of students. For example, how did a group of fifth graders score when they were in fourth and third grades? The current grade levels of students are in parentheses.

It's a little busy. Mind you, you need a big monitor to view the spreadsheet all at once. I've tried to be as consistent as I can with the color scheme, headers, etc. I do think that the small size of the graphs is a bit misleading---some of those gaps are as much as 20 points. Users can hover the cursor over points to see the numbers associated with them.

Finally, here is a sample of the district overview.

It's the only one of the three that doesn't have comparisons, but in this case, it should be okay. It will serve as a reference when teachers from each district talk about their schools. I have some stacked bar charts here to help conserve space, but I've tried to keep the style more or less the same. I know that the more I fuss over the details, the less they will be in the way of others making sense of what is presented.

For me, these sorts of designs are a slow process. The last graphic took me most of a day to derive. Sometimes, graphs don't turn out in the way you think they will. Or they take more space. As hard as I try to be consistent with colors, labeling, and fonts---there is always something I miss. Sometimes, I build something only to find out that I don't need it. And, no doubt, the day after I share these, I will see two or three other ideas I wish I would have incorporated. But I'm feeling pretty good at this point in the build.

Now, about those tenure data...guess I've procrastinated long enough...

Building a Better Heat Map

In the last post, I shared my version of a heat map created in Excel. Now it's time to get into the details of the build.

After you've organized your data in Excel, start a new sheet for the heat map. When I did a "back of the napkin" sketch for this visualization, I knew I would need 37 columns: one for the name of the district, and 36 others to fit three years of data points for four areas (dance, music, theatre, visual) for three grade bands. I labeled my rows and columns.

Next, I created a formula to fill in the data points. Because the values for each cell were a proportion of the grade band, they were all a variation of this one for cell C5, the first one for the District A: =2010!C2/SUM(2010!C2:F2), where 2010! represents the sheet with the data for the year 2010, C2 the cell with the drama number (if any) for elementary, and C2:F2 representing the four cells of elementary data. That's it. Use the fill tool for the rest of the cells.

If you're wondering why I have each year on a separate sheet---there's no good reason, other than it was handed to me that way. I could have easily pasted all the data into one spreadsheet. Do as the Excel spirit moves you.

Let's talk about color for a moment, before we dive into conditional formatting. If you need a two-value system (e.g. to represent gains/losses), remember to stay away from green/red. It's not friendly for those with colour vision issues. Orange and blue is better. For a single value system, blue works well. Why on earth did I pick purple, then? Aesthetic reasons. Blue worked fine, but seemed a bit boring. The purple just seems to suit this data set. If you need help with colour scales, you might start with the Color Brewer.

Also, why do I have ten categories? It seemed like a natural fit for a scale based on percentages---I stuck with the base 10 scheme. My full data set for this piece has over 9000 data points and I wanted to see some nuance. But I could just have easily picked quartiles or another factor. Which one should you choose? Ah, that's part of the art of data science. You really need to play with your visualization a bit to find out what cut values work best.

Okay, let's dive in to the conditional formatting. You're going to need 11 rules. Why 11? Because we also want to create one for any 0 or blank value.

For each rule, tell Excel to do with a range of values---your instructions should include the color for that part of the scale as both the font and fill. This way, you will "hide" the number in the box. Go ahead and put a bunch of 9s at the end of the upper limit. If you have a big data set (and who doesn't like 'em big?), you're going to want to account for all the little variations within it.

Your last formatting job has to do with the borders. In the menu for borders, make each cell have a thick white outline. This will make your coloured boxes look separated. Go into the View tab and uncheck the box next to "gridlines."

The invisible border dialogue

Okay, now you need to change the width of the columns and rows to create squares. Just do the old select and slide until everything looks square.

Finally, use the shape tools to add some very thin grey lines between each chunk of the data set. Yes, you're actually going to draw in those borders.

I made mine a little longer between the grade levels than between the different arts areas.

Guess what? You're done.

One of the things I like about this project is that it's simple, but rich and interesting. The more time you spend, the more you notice the little nuances...things you might never see from just the numbers alone. I think this project also illustrates how much of design is not about the number-crunching. There's just one formula here. The rest is making Excel turn that data into something visual. And yes, that takes some time and finesse (it is Excel, after all...not a tool that was developed for data design), but you can do it.

So get out there and make something that grabs people's attention because it's pretty. And when you've done that, come back and share it here.

A New Spin on Heat Maps in Excel

There are lots of heat maps out there. We saw many different versions during the election cycle---everything from campaign financing to election results. These visualizations use a scale of color values to communicate a range of data. Some vary the intensity of a single color value to represent a variable (e.g., the greater the population in an area, the darker the color). Others use two values to present a comparison (e.g., increases and decreases to population over a given time period).

Excel does not have a heat map option in its charts; however, you can use conditional formatting and some other design options to create a really fine looking visualization.

One of the graphics included with Stephen Few's workshop presentation was this one:

Market Segment from Stephen Few's Now You See It

I was really taken with it. It's a simple layout, but packs a punch in terms of showing the data. It was the perfect fit for a data set I was wrangling. Take a look at my version (there is an interactive version at the end of this post):

What you're looking at is some data about the different areas of the arts (dance, music, theatre, and visual arts) for three grade bands (elementary, middle, and high school) over three years (2010, 2011, 2012) for districts in Washington state. Please note that although this data is public, it is only available to those who ask. Therefore, I have made some modifications to what I am sharing here: no districts are identified by name and I am only including ~50 districts.

Never underestimate the power of pencil and paper when planning a visualization. Using the version from the workshop as a model, I sketched the layout for this, as well as how to fill the cells. I decided not to use number of students as a factor, as that would skew things based on the size of the district. Instead, I wanted to look at each arts area as a portion of overall enrollment in the arts. In other words, what percent of elementary students in a district had access to instruction in dance vs. music vs. theatre vs. visual arts?

There is a single color scale. The greater (darker) the intensity, the greater the percentage for that area of a grade band. Blank spaces represent a lack of data. This might be due to no instruction offered in that area, but it could also mean that we just don't have any data from the district. Don't read too much into those.

Even though I used a single color (purple) for this visualization, it is still simple to look at gains and losses over time. For example, for District A, we can see that at the middle school, music instruction has decreased over the last three years...and the percentage of students involved with the visual arts has increased.

Similarly, I can see some big trends when viewing the entire data set at once.

Notice how popular Music is at elementary? There are a lot of dark squares in that second column. While not as strong, there is a definite preference at the high school level for visual arts, based on what I see in the last column.

The benefit of a heat map like this one is that it allows you to visualize a lot of data at once (there are over 1500 data points in the image above, including blank spaces) while not losing any of the points.

In the next post, I'll get into the nitty gritty about how I built this. For something that looks rather elegant (if I do say so myself), there is only one formula involved, with some conditional formatting and shapes applied. In the meantime, if you want to play with the spreadsheet a bit, use the Excel Web App below. Sorry about the horizontal scrolling required, but you can also click the bottom right-hand corner to view the sheet full screen.

Ain't Misbehavin'

In the last post, we looked at one way to display information about non-academic behaviors in the classroom---things like work ethic, participation, and attendance. These are student attributes that we value and want to report on, but need to be separated from grades and other measures of learning.

Even though I am sure that I will revisit these ideas and fine tune the data displays over time, I thought I would lift the curtain to reveal the workbook innards. No doubt some of you would like to play, too.

Before we jump into the guts, I want to say that all of the attributes and scales are just examples. We could have a lively discussion about which attributes to measure, how you (or your students) could collect these data, and what the cut scores should be in order to be in the range of performance we'd like to see. These are critical pieces to consider...ones that I hope you ponder and kick around with your colleagues and students. I am going to set them aside in this post and strictly focus on the nuts and bolts.

 

On the Report Sheet, I set up a data validation for the students last names. I started by selecting the range of last names on the Attendance/Grade worksheet. I named the range "LastName." Then, on the Report worksheet in the cell where I wanted the dropdown to appear, I chose Data Validation on the Data tab. In the dialogue box, I selected List from the "Allow" dropdown and then in the "Source" box, I added a formula using my named range. For the "First Name" area on the Report worksheet and the selections at the bottom of each of the other worksheets, I used our old friend, the INDEX/MATCH formula.

Let's move on to a few new tricks. Most teachers keep attendance records by marking who is absent---not who is present. But on my graph, I wanted to chart attendance as a positive measure. There are a few ways to do this. The simplest would be to change the way we keep attendance. Mark students when they're present and just count up the number of days at the end of the grading period. I didn't take the easy way out. Instead, I left each "A" for Absent just where it fell. To get the total number of days present, I used this formula:  =30-(COUNTIF(C6:AF6,"A")). In other words, count the A's in a particular row (in this example, row 6 for Cathy Andrews) and subtract that number from the number of available days in the grading period (30). However, this swap won't work for creating the graphs. I need numbers to work with---not text. But a simple IF statement makes short work of that: =IF(C18=0,1,0), where "C18" is the cell from the INDEX/MATCH result for a particular student.

I also used a nested IF to have Excel convert the number of days attended (or points earned) into a level of performance. For example, here is the one that goes with the worksheets for Work Ethic, Participation, and Behavior:  =IF(AG6<=59,1,IF(AG6<=79,2,IF(AG6<=99,3,IF(AG6>=100,4))))
It looks worse than it really is. (Don't most things?) Notice the repeating "AG6," which is the cell with the total number of points I want Excel to use as a comparison. In the first part of the statement, we tell Excel that if that number is less than or equal to 59, to put a 1 in the cell. If not, Excel moves on to the next part of the statement. Even though we know that "30" is less than or equal to all of the cutoffs in the equation, Excel considers only one at a time.

Shall we talk about the graphs? I used the stacked bar from the charts menu. No doubt some of you out there will want to use a regular bar chart. That is appropriate, too. I picked the stacked bar for two reasons. One was that I wanted to do more than show a comparison. I wanted to illustrate a proportion. A viewer can certainly make that inference with a regular bar chart, but it's a little less work with the stacked bar. My second reason was purely about the aesthetics. I knew I was going to want a line graph in each of the four areas (attendance, participation, work ethic, behavior). The vertical real estate claimed by a bar chart threw off the look of things.

Anyhoo, after you select your data for the graph, don't fall apart of it looks like this:

Simply hit the "Switch Row and Column" option and you will see the stacked bar. After that, you can clean up the graph.

I won't go into detail with the line graph. What I will share is that it does take time to create the labels, standardize the sizes, and get everything in place. Take your time. Don't assume that Excel knows best. It's important to make things look good---otherwise, you are missing your chance to truly communicate with the data.

The radar/star/spider chart is found in the "Other Charts" menu. Again, this chart is not everyone's cup of tea. One of the ed research articles I read mentioned using the chart with college students as a form of feedback---and they hated it. This seemed to be due to the lack of familiarity with reading these types of graphs. We see line graphs and bar charts all the time. Radar charts? Not so much. There are other reasons not to choose this type of chart. For example, it works better with cyclical data. And, the area of the graph becomes distorted with the scale. For every point increase of the scale, the size of the area becomes squared.

I would suggest that if you select this type of chart to use with students (or parents) that you spend some time talking about how to read them. Show them how to look at the connections between the spokes and the area covered by the graph. Why did I choose it, given the difficulty in interpretation? Because the attributes of student performance are not simply about comparing them. There needs to be a broader view of what is happening in a classroom---how is the student performing in light of the overall goals...and more importantly, how might non-academic factors be affecting one another (as well as student learning)? I think that you lose some of these capabilities with a clustered bar graph.

What would you do differently with this workbook and these graphs? Would you choose to put the line graphs all on one chart for easier comparison? Do you like the idea of bar charts better than a stacked bar...and clustered columns instead of the radar graph? How would you organize the workbook?

Ain't She Sweet

A couple of months ago, I was working with some people who claimed to know nothing about data analysis. After we'd constructed the most godawful and useless table imaginable (they insisted that people would want it), their next step was for colour to be added. That's fine---I don't have anything against using colour in a purposeful way for communication. But that's not what they wanted. Their goal was to make the table "pretty." Not in a design-centered way. Just a "Let's whore it up and send it out on the street!" way.

I attended a conference this week in which data had a starring role. I stopped by to talk to a vendor about their product. We'll disregard, for the moment, the blatant FERPA violations with the actual student data they were so proudly displaying, and get to the moment where they were explaining the visualizations generated by the software. The "stoplight" effect (red, yellow, green) is very popular in education for sorting students, but is not so friendly for users with vision issues. I asked them about colour choice. And what did the salesmen say? "It's pretty."

At this same conference, several districts proudly showed off their data dashboards or Excel add-ins. The fact is, there is a lot of very exciting and in-depth work being done with all kinds of school data. I was energized by what I saw, except for one thing: sloppy and ineffectual data displays. I get that the numbers and analysis are the focus. But to get to the end of all the hard work with ginormous data sets and not pay attention to the output was like having sex without the big finish (or, more likely, a premature one): very unsatisfying and frustrating as a participant.

I understand that how easy it is to be blind to errors. I also know that once you've applied blood, sweat, and tears just to make your Excel spreadsheet to work that the last thing you want to do is fight with the output. But take the time, educators, to respect your data and audience. Yes, it's okay to want something "pretty," but that is not enough. Use your lines, colours, graph choice, layout, and text to create powerful ways to communicate. If you're not sure what looks good, ask for feedback. Collect samples of visualizations that you like and use those for inspiration. Read articles and find resources on using colour and line as a fine tool instead of a blunt instrument. Now get back in there and try it again.

How Stacked Bars Stack Up

I'm starting to put together a workshop on Excel dashboards that will be in December. I'm playing around with a few different data sets and options, and have a quandary I'd like your help with: What is the best layout option when using stacked bar charts?

If you don't know what a stacked bar chart is, here is an example:

There is one bar representing 100% of whatever is being measured: numbers of students, percentage of scores, speeds of African and European swallows, and so forth. In the stacked bar chart shown above, there are four categories which have been numbered. Instead of each category having its own private bar, they get together and party in one space. The size of the categories, indicated by different shades of green in the example, shows their proportion of the 100%. It is a simple way to compare the relative sizes of categories...and all without some ridiculous pie chart.

But stacked bar graphs do not have to take things lying down. They can be vertical, too. Which leads me to my problem: When should you use the horizontal format...and when should you use the vertical format? My Google Fu hasn't turned up any rules. Seems like everyone is just letting it all hang out.

There are three layout options shown below. I know I don't have the categories labeled, but I just want to consider layout at the moment. Just FYI, the data represent scores on the 2011 Washington state test for reading. The shades of green, from lightest to darkest, show the percent of students at a school who scored at Levels 1, 2, 3, and 4.

The first layout has a very traditional look:

Labels for each grade are to the left. The graphs are (more or less) placed so that you can make a quick comparison among the grade levels. But is this better?

Same data. Same graphs. Just rotated 90 degrees. I don't know about you, but I find this easier to "read" when looking across the grade levels. It's true that there would be some issues with how closely the graphs can be pushed together due to the labels, but the overall format is okay.

And finally, my least favourite, but deserving of discussion is this:

 

Considering that there will be more graphs for mathematics, science, and writing, would it be more important to look at performance for a subject area across and review data for a grade level along a vertical axis (not shown here, but imagine there are stacked bar charts for the other subject areas underneath). 

What do you think? Just from the standpoint of layout, which format is most meaningful for you?