danryan.us

As always, the results to be presented in a professional manner

Look at the Data

FIRST. Have a look at this data data.¹

Book1.xlsx

"Download" and Do a Little "Data Cleaning"

Highlight the data table (including the header) and COPY.
Open an Excel spreadsheet and PASTE SPECIAL > TEXT in the upper left hand cell. If all is well, the data will mostly correctly copy.

Examine the results to identify what did not go where it should and move and/or retype as necessary so that you are looking at this:

Problem 1: Draw pie chart showing proportion of students beginning in 9th grade in Oakland in 2005-6 who drop out each year and/or graduate.

Here is what we want:

and here is the data with which we begin

The first thing we need to do is understand our data enough to know what to do. Read the column headers over carefully. Go back to the original data documentation if necessary.

We determine that the data is following the 9th grade entering class from school year 2005-06. The first data column tells us how many dropped out in/during/after (hard to tell) 9th grade, then 10th, etc. The 5th column is a total of these and the 6th column is the number who graduated. It would appear that

Entire Class = 9th grade dropouts + 10th grade dropouts + 11th grade dropouts + 12th grade dropouts + graduates

This would appear to be just the right set of data for a pie chart. But we cannot just select a rectangular data range because all these data are not contiguous. So we use a "non-contiguous selection" technique — otherwise known as "Control-Select." Hold down the control key and select these data cells AND the column headings above them. It will look like this:

Now we can create the basic pie chart with INSERT > Pie Chart.

To customize it, we'll need to make a number of tweaks:

1. Add title
2. Subtitle can be created by including it in the title and then inserting a carriage-return/line feed by double clicking in the title text and then hitting return/enter. The subtitle can be selected and made slightly smaller.
3. Delete the legend
4. Add data labels by right-clicking a pie segment. Then right-click a data label for FORMAT DATA LABELS and indicate that we want to use "category name" instead of value.
5. Adjust size of pie and the chart window so you have room to work, room for the title, room for notes and such.
6. Go back to the source web page and select and copy the California Department of… info at top. Then INSERT > Shapes > Text Box and paste it in. Then, go back and get the full URL of the webpage and add it as last line. Then put a "Chart made by…" line in. Adjust size, font, and placement.
7. Move data labels around as needed. To get uniform appearance, you can click into the text and add a CR/LF by SHIFT-ENTER (or SHIFT-RETURN).

Finished product looks like this:

NOTE: we did not include percentages in the pie chart because we need to think a little bit carefully about what they would mean. When we talk about drop out rates we have to be careful about whether they are "of the kids who started" or "of the kids who were still in school that year." For now, we'll set this question to the side.

Problem 2:

Problem 3:

Problem 4:

Problem 5:

Problem 6

The boss wonders whether there is anything interesting going on in terms of patterns of freshman year retention vs. senior year retention. She requests a chart that shows the 9th grade retention rate vs. the 12 grade retention rate with bubbles sized by total 9th grade class size.