This assignment will introduce you to two very useful technologies for aggregating data to create charts/graphs: Excel pivot tables and Tableau. It will also walk you through an emulation process. The final result of this assignment will be two charts of the same data that look very different:
STEP 1: Download the data file: the number of people entering the IMA (the exercise facility at the UW) per hour from July 2013 through May 2014, divided by gender and role (faculty/staff, students or guests). The file contains 4658 rows of data.
STEP 2: To see what all 4658 x 6 data values look like, make a line chart (in Excel). You don't need to save this, it's just a useful exercise:
As you can see, this display shows many more data points (27,948) than the final versions shown above which show just 64. In this assignment, you'll learn about how to turn those 27,948 data points into 64 data points by aggregating the values with Pivot Tables and Tableau.
STEP 3: The two videos below show how to create a Pivot Table that aggregates the values by finding the average for each category of visitor for each hour of the day. One video uses the Mac 2011 version of Excel and the other uses the Windows 2013 version. Watch one (or both) and create the same pivot table and chart yourself:
Excel for Mac:
this is a media comment
Excel for Windows
this is a media comment
STEP 4: Paste the Excel chart you created into Illustrator (after changing the text to Arial or some other font that Illustrator will display properly).
STEP 5: Follow the steps below to emulate the model.
NOTE: I am aware/concerned that the way I've set up this assignment makes it easy to go through the motions without learning a lot. To avoid wasting your time, pay attention to my comments about the decisions I'm making as I go along. I want to model how to think about creating an emulation.
Your original chart should look something like these two displays (one made with the Mac version, the other with the Windows version of Excel):
The chart you will be emulating is below on the left. The infographic it is from is on the right:
a. Remove extra elements:
Compare the model with your Excel file and think about what elements in the Excel file are missing from the model.
Here's what I see: The model has no boundary box, no vertical axis line and fewer grid lines.
Think about how to reduce the number of grid lines in your Excel chart (you start with lines at increments of 20 that go up to 180).
Here is what I came up with: I figured out that if I reduced it to increments of 50 and made my top grid line at 150, that would give me a total of 3 grid lines. The data extends a little above 150, but when I look at the model I see that the data extends above the grid lines there as well so that seemed fine.
HINT: To insert a grid line at 50, make a copy of an existing grid lines and use the alignment/distribution tools the place it half way between 40 and 60). Do the same for the 150 grid line:
b. Color the highest line (male students) to have the same fill and stroke colors as the model:
HINT: Use the eye-dropper tool to get the fill color, then use the eyedropper tool again, but this time click on the stroke box in the tool bar to select it...
...then, after selecting the eyedropper tool, hold down the shift key and click on the line. This will leave the fill color unchanged, but just pick up the stroke color. Your result should look like this (I increased the thickness of the line to match the model as well):
c. Decide how to color the other lines.
In the model, the chart has only one line so it doesn't provide an example of how to display multiple lines. This is the type of challenge you will face over and over again when you emulate models...how to create an equally effective and attractive display when you don't have an identical example to guide you.
Think about options you might try. Then look at the options I tried:
OPTION 1: I tried making all of the lines the same color. This looks pretty good:
OPTION 2: I separated the chart into individual charts so each has just 1 line. Then placed them side-by-side to facilitate comparison:
OPTION 3: I did the same, but placed the charts on top of each other. I find this makes it easier to compare how they differ at a single time of day, but harder to compare heights:
OPTION 4: I looked at the infographic my model came from and saw that there is a map that uses several shades of the brown/tan color. So I experimented with using these colors. I noticed that in the map, the borders between states were white so I made the lines separating the colors white...then found by experimenting that this looked best with the two darkest colors only:
These options are all valid displays of this data. I decided I preferred the version with multiple colors.
d. Make the same change to your chart...i.e. make it look like this:
e. Change the x-axis labels.
Look at the model and notice where the x-axis labels are:
Think about how to emulate this in the IMA chart. Think about how many labels you should have (in the original Excel version, there are 17), and where they should be placed (in the original Excel version, they are placed below the x axis with a tic mark for each). Also think about the role these axis labels play in the chart as a whole; do you find yourself needing to look at the axis labels when you read the information on the chart?
Here is what I came up with after going through this decision process (my choices were by no means obvious ones and another designer would have made different choices). You'll notice that it looks very different from the model which has 5 labels and puts them inside the chart. I didn't put them inside the chart because there isn't enough space to do so without having them overlap lines and get messy. To decide about how many labels I would need, here are things I thought about:
Why does the model have labels every 10 years? It looks tidy to not have the baseline crowded with labels every year and it easy easier to get a sense of time periods (at just a glance I can see the decades, whereas if there were 36 labels I would have to look more closely. I also realize that it doesn't need more fine detail because the labels include years in them.
In thinking about my data, I thought about what would give the viewer a quick sense of what the baseline represents. Again having a label every hour seems like too much. I considered every four hours, but that gave me somewhat awkward times (10:00, 2:00). Then I tried just having the start and end hours as you see and decided I liked that because it lets you see at a glance when the IMA opens and closes. I also decided that the annotations I was going to add would add the additional details people would be interested in (like "what time of day does that peak happen?").
Note that I moved the 10:30 data point to half-way from 10:00.
f. Add annotations
Since I have removed the Excel key that indicated which line referred to which population, I needed to find an alternative way to convey that information. The model doesn't have a key (since there is only one line), so I don't have a guide to follow. I've decided to use the annotations to label these lines. (I'm aware that this may be a risky move since it isn't what people expect to see. I'll need to test the result with potential viewers to see if it really works).
I spent some time deciding how to annotate the chart in a way that would help the viewers interpret the information. Then I added arrows and dashed lines at the points where I wanted the annotations to be, as in my model. Then I inserted the text (matching the narrow text style and use of mostly right-aligned text blocks). I also added a title in all caps:
TIP: To make a line dashed or dotted, go to the Stroke panel and click on the "Dashed Line" box:
TIP: To make a triangle, choose the polygon tool...
...then click anywhere to get the dialog box the lets you choose the number of sides for your polygon (choose 3)
Your final result should look like mine.
The next step is to create the second version of the chart starting with the same data, but using Tableau.
STEP 6: Use the same data to create a layered bar chart using Tableau. The video below shows you how. Watch the video and then create the chart yourself.
this is a media comment
Summary of the Tableau Tricks mentioned in the video:
- How to change from military time (right click, format, Dates:)
- How to make one chart rather than parallel charts (start with a line chart and convert to bar chart only after combining 2 series)
- How to create a non-stacked bar chart (Analysis -> Stack Marks -> Off)
- How to vary the gap between bars (size slider in Marks box)
- How to change the sequence of the series (Drag to order them)
Your chart should look something like this:
STEP 7: Save this chart as a pdf file, open it in Illustrator and paste it into the file with your formatted Excel chart from above
STEP 8: Follow the steps below to emulate the model. This is the model:
a. Change aspect ratio, reduce number of axis labels and reformat them, remove tic lines (see notes below). The result of these steps looks like this:
To change the aspect ratio, I simply selected everything and resized in one dimension. I let the text get distorted and just retyped the labels using the same size and color of text (the size for the y-axis labels isn't right, but you'll see that I correct that later).
To change the labels, I first made the decision about how many labels to include. I wanted to end up with 4 horizontal bands in the background (as in the model), but felt I had to make a split that would include 100 so it was either every 50 or every 33.33, which seemed too awkward.
For the x-axis, since the model labels every other year, I chose to label every other hour.
b. Change the colors of the data bars and add the background bands of gray.
NOTES: As I started to experiment with coloring the bars, I decided to remove the two "guest" categories. They felt unimportant for the message and the additional information they provided felt more distracting than helpful.
I experimented with several color schemes (shown below), trying to stay consistent with the model:
Color Scheme experiment #1: I alternated the blue and orange colors. This created an awkward impression of the blue bars being cut in the middle rather than 4 different sets of bars:
Color Scheme experiment #2 & 3: I experimented with using lighter versions of the blue and orange hues as you see below, but this felt like too much of a departure from the model and lost the bold feel of the model that I felt was desirable:
Color Scheme experiment #4: I split the chart into two charts so I could keep to the two colors and compare students and faculty/staff side-by-side. This has some potential, but I was going to need to figure out how to deal with all of the empty space in the faculty/staff chart:
Color Scheme experiment #5: I solved this problem by creating a mirrored chart with the faculty/staff values below the horizontal axis. This is risky since it violates people's expectations (and is therefore something I'll need to test with viewers). But I'm hopeful that it will work. I do feel that symmetry this creates helps to see the pattern in the data (the differences in the student and faculty/staff patterns).
c. Add the title and key to match the model
NOTE: On my computer, I found that Bell Gothcc Std, black was close to the title text and Arial Narrow was close to the key text.
d. Add annotations (and increase the size of the y-axis labels). I noticed that in the model, annotations corresponded to peaks and dips in the data: peaks are labeled with blue annotations while dips are labeled with orange annotations. I did the same:
e. Think about the grid. I experimented with adding the white grid. (You don't need to do that). I ultimately decided against it. An important part of the effect in the model is that the grid size matches the bar width, but with many fewer bars, this created a very different feel as you can see below. I've decided to accept that departure from the model recognizing that it makes the annotations less prominent and the data bars more prominent.
UPLOAD: pdf file including the your original charts (the one created in Excel and the one created in Tableau) along with your modified charts
FYI: To show you more ideas for how this data can be aggregated and displayed, I created the infographic below (you can click on the image to download the pdf if you would like to see it in more detail):