The goal of Exercise C is for our seminar to compile publicly-available school-level data to update Robert Cotto & Kenny Feder’s Choice Watch report (based on 2011-12 data) to include 2012-13 and 2013-14, and to analyze any patterns in the data over this three-year period.
You and your randomly-assigned partner will collaborate on this exercise, and you may share ideas and strategies with others in our seminar.
Begin with this Google Spreadsheet from Cotto, which includes his 2011-12 data on charter, magnet, and vo-tech schools:
1) Add more recent data on school enrollment by race/ethnicity for your assigned tab:
Charter 1213 – by AL AG
Charter 1314 – by HH LW
Magnet Hartford-area 1213 – by AF CM*
Magnet Hartford-area 1314 – by AM SS*
Magnet other-area 1213 – by LS CB*
Magnet other-area 1314 – by SG EK*
Vo-Tech 1213 – by NT RU
Vo-Tech 1314 – by IF JC
*Updated: “Hartford-area” is defined for this exercise as towns inside the RSCO transportation area, which are listed in this new tab in our Google Spreadsheet.
2) Search the Education section of the Connecticut Open Data site (https://data.ct.gov/browse?category=Education) for relevant files:
School Enrollment 12-13
School Enrollment 13-14
Education Directory as of April 2014 (to add any new schools of your type)
Note that CT Open Data has Indicators of Educational Need (such as special education, ELL, FRPM etc.) at the district-level, but not yet at the school-level. (See my request, which might be fulfilled later this semester.)
Also note that CT Open Data does not yet include data on the Open Choice city-suburban transfer program.
Also note that in CT Open Data, “Cells are suppressed (marked with an asterisk *) where the cell count is less than or equal to five.”
3) Add the relevant data to your assigned Google Spreadsheet tab, using these hints:
– On CT Open Data site, use the Filter tool to identify schools by type, then Export
– If desired, in the Google Spreadsheet you may File > Download As. . . to prepare your files in another spreadsheet tool.
– Find accurate and efficient ways to align and update data rows (such as Select all > Sort by district and school codes, then copy and paste). Advanced hint: try the VLookup function to lookup matching data rows in one spreadsheet and copy them to another.
4) CT Open Data does not define “racial minority,” nor provide this number or percentage, so you will need to calculate these. Decide how to define this category and describe it in a note immediately below your data.
5) Learn how to write simple spreadsheet formulas, as shown below, and copy and paste (or drag the bottom-right corner of the cell) to automatically calculate for additional rows. Check your work carefully.
= B2+C2+D2+E2
= sum(B2:E2)
= P2/H2
6) Note that data represented by asterisks (*) will not be recognized in numerical calculations. Decide how to deal with this issue and describe it in a note immediately below your data.
7) Compare the data in YOUR spreadsheet tab to Cotto & Feder’s 2011-12 table and their report. Can you identify any trends about racial isolation in your subgroup of public choice schools between then and now? Write a few sentences in your spreadsheet tab, immediately below any notes you have added.
8) This exercise is due on Wednesday Feb 18th at 4pm, and everything must be included on your Google Spreadsheet tab. But you do NOT need to “hand it in” or share it, because I already have access to this file. That evening we will discuss what we are learning with our guest, Robert Cotto Jr.
NEW: Check out my 9-minute video screencast with helpful hints on working with spreadsheets and the CT Open Data site to successfully complete this assignment.