Lab 14 - Data and information visualizations

If you've followed the steps in the book chapter carefully, you should have a pretty good idea how to manage key concepts of spreadsheet application use like conditional formatting, "What if?" scenarios, and filtering. But as we saw in Lab 13, there's more to data than manipulating it in its tabular form.

Taking a cue from the ManyEyes visualizations, you'll return and download one of the datasets for the Titanic. You'll import that into a spreadsheet application, and try out some of that application's native visualization abilities.

To consider:

Finding the right way to look at data

In this fourteenth lab, you'll continue the hands-on work with spreadsheet applications you started in Lab 13. As you explore, review your answers from the To consider: section above. Have you changed your mind about any of them?

Learning more

  1. Spreadsheet applications are powerful tools that can do more than organize information into tables. They can be capable of performing complex mathematical functions, perform statistical analysis, and more. In addition to the in-application help menus, knowing where to find help online is one of the first tools to put in a good toolbox.

    • Take a look at this ManyEyes dataset that has information about the Titanic's passengers. The format likely looks a bit odd, since you're looking at a text file version of the tabular data displayed in a browser window. Once you've taken a look, save the .txt file to your desktop.
    • Next, import the .txt file (or drag and drop it) into your spreadsheet application. You should see a table 9 columns wide. Here's one example:

      Imported Titanic passenger spreadsheet
    • Do the column labels and the values in the columns make sense to you? This dataset was uploaded from a user who included no notes, so you're on your own for interpreting this. Additionally, there was no attribution of where this data was found, so there's no way of knowing if the values in the table are accurate. Still, this will give you a good set of data points to make visual sense out of.

  2. There are things to note about this table before you begin. Some of them are:

    • The table is currently sorted by class of passage (First Class, Second Class, Third Class).
    • Ages are given in decimal values carried to two places, and are sometimes not present.
    • Survival status is given by 1 (survived) or zero (died).
    • Sibling/spouse/parent/child relationships are noted, but not in a way that can group families together.

    Try IT: Making chart
    Even with this relatively small dataset, some interesting questions can be answered, and the answers shown in chart form. Try answering the following:

    • How many total female passengers were on board? What percentage of them survived?

      Let's walk through this one together, assuming you are using Excel:

      • At the bottom of your spreadsheet, find two empty cells together in a row. In the left-hand cell, type the label Total Passengers.
      • In the right-hand cell, type the start of this formula:=COUNT( in the formula bar, then select the range of cells to count. You can begin with A2 and select everything in that column that has an entry. When your selection is complete, type the closing ) and press Enter. This will give you a count of the number of rows, which in this dataset represent passengers.
      • Now make a new column label called Is Female. In the first cell below the label, enter this formula: =IFERROR(IF(MATCH("female",C2,0),1,0),0). This formula says, if the value in column C is "female", put a 1 in this column. If the cell value is anything else, put a zero. Use the fill handle to use the same formula all the way down to the last entry in the dataset. You may want to sort by column C to make sure the formula is working correctly.
      • Now you can use Excel's SUM function to get the total number of female passengers.
      • Use a formula to divide the number of male passengers by the total number, and format that cell as a percentage. Be careful to keep enough decimal point values that your total percentage adds up to 100%.
      • Choose a chart that will help you tell the story you want. With these two numbers, the percentages are parts of a whole, so a pie chart should work, but feel free to try other options. Select the cells you want to turn into a chart, then select the chart type from the Insert menu. If you chose a pie chart, you should end up with something like this:

        Titanic passengers by gender - chart

        How did you do? Ready to try the other ones? Remember to keep in mind which chart might work best for the data you are presenting.

    • What class of passengers had the best survival rate? Was that true for both the male and female passengers in that class?
    • What percentage of passengers did not travel with any other family members?
    • Of the passengers who made it to lifeboats, what percentage survived?
    • What was the range of First Class ticket prices for each point of embarkment (coded C, Q, S, or not present)?
    • For each lifeboat, what percentage of the passengers were female?
    • What percentage of the passengers were children (under 18 years old), and how many of those survived?

Moving on

Working with charts is just one kind of information visualization. Some information can be delivered in timeline form, like Timeline, one of MIT's SIMILE project widgets. The SIMILE widgets are a set of free, open-source data visualization applications.

For more personal information visualization, consider mind mapping, a technique for note-taking, brainstorming, and generally getting ideas down. This very visual approach can be done with pen and paper, or with a free online application like draw.io.