In-Class Exercise 7 [Student Fees]

In observance of the campus strike occuring today we're not holding class on campus but we will doing a mapping exercise relevant to the strike:  web based class today on mapping student fees data. 

Please download the folder called "StudentFees" from the class Box / Data folder Links to an external site. and put it in your local Documents folder.

The class is being held as a bCourses Conference.

 We'll be using data from this site

http://nces.ed.gov/ipeds/datacenter/Default.aspx Links to an external site.

Choose Download Custom Data Files

Use Provisional Data > Continue

By Groups > EZ Group > check All Institutions

Select Variables (tab along top) > Browse/Search Variables

Institutional Characteristics > Directory Info > Select Year(s) 1999-2000

Institution Entity Name (UnitID) and Inst. Name Alias

Choose Student Charges > Institutions > Year (1999-2000) [repeat using 2012-2013]

Export as CSV.

Clean up in Excel

Open and clean up Row1 because these become the Field Names in Arcmap and you have to use simple Alphanumeric (no symbols). Spaces may be problematic. To be safe use <12 character alphanumeric in Row 1.

For the purposes of this demo we'll map the Average tuition/fees for IN-STATE and OUT-OF-STATE.

Create a new column to receive the Average value for each group call it Instate

Use the Average function in Excel and it should look something like this in the new Column

=AVERAGE (F2 : K2) 

Then Fill Down to populate the whole column 

Repeat for Outofstate

Paste Values and remove empties

Select the InState column and Insert New Column and Paste Special ... Values to break the link with the source for Averages 

Repeat for OutofState

Sort by InState and inspect and empty or Error values. Perhaps those should not include as many columns to avoid dividing by Zero?

Repeat for OutofState.

Select Rows with empty values in both InState and OutofState (they should be contiguous) and delete.

Finally save to CSV with just the UnitID and InState and OutofState Averages.

Bring into Arcmap

Join to geocoded USInstitutions shapefile. Save the output as "Fees2012"

Experiment with Symbolizing using Quantities.

Note that it's very busy. How can we generalize these to states?

Bring in Admin2004 shapefile.

Do a Defnition Query (Properities > Definition Query > Query Builder button...) and select CountryName = US

Do a Spatial Join between Admin2004 and the Fees2012 using Average and Min and Max amounts.

Experiment with Symbology using Quantities > By Color.

Map

Copy the Layers Data Frame and Paste. Make two maps on one page with one showing Instate and another showing Out of State.

Save the PDF and upload as your assignment.