Financial Aid and Degree offered by US Higher Education Institutions (2020 - 2024)

Description

This dataset provides a normalized, relational structure for analyzing U.S. higher education institutions. It integrates institutional profiles, geographic data, instructional program classifications (CIP), yearly degree conferrals, financial aid metrics, and institutional rankings. Furthermore, it aims to provide details regarding institutions offering Artificial Intelligence, and Data Science programs. The data has been cleaned and normalized to 3rd Normal Form (3NF) to reduce redundancy and ensure referential integrity. *Database Schema The dataset is organized into a relational structure with the following entities and relationships: Institutions: Master list of schools. Locations: Shared geographic data (linked to Institutions). Programs: Master list of instructional programs (CIP codes). Award_Conferred: Transactional records of degrees granted per year/program. Institution_Ranking: Yearly institutional rankings. Financial_Aid: Yearly financial metrics (grants and loans). Institution_Status: Yearly activity status of institutions. *File Descriptions & Data Dictionary 1. INSTITUTIONS.csv `ID` (Primary Key): Unique IPEDS/UNITID identifier for the institution. `Name`: Official name of the institution. `ADDR`: Street address. `LOCATIONID` (Surrogate Foreign Key): Link to the `LOCATIONS` table. 2. LOCATIONS.csv `LOCATIONID` (Primary Key): Unique identifier for a city/state/zip combination. `CITY`: City name. `STATE`: Two-letter state abbreviation. `ZIP`: Zip code (preserved as text to maintain leading zeros). 3. PROGRAMS.csv `CIPCODE` (Primary Key): Classification of Instructional Programs code. `CIPTITLE`: The name of the instructional program. 4. AWARD_CONFERRED.csv `YEAR`, `ID`, `CIPCODE` (Composite Primary Key). `CTOTALT`: Total awards conferred. `CTOTALM`: Total awards conferred to men. `CTOTALW`: Total awards conferred to women. Foreign Keys: `ID` -> `Institutions(ID)`, `CIPCODE` -> `Programs(CIPCODE)`. 5. FINANCIAL_AID.csv `YEAR`, `ID` (Composite Primary Key). `SCFY2`: Total number of undergraduates - full-year cohort `SCFY1N`: Number of students in full-year cohort `UAGRNTN`: Number of undergrads receiving grant aid. `UAGRNTT`: Total amount of grant aid awarded to undergrads. `UAGRNTA`: Average amount of grant aid awarded to undergrads. `UFLOANN`: Number of undergrads receiving federal loans. `UFLOANT`: Total amount of federal loans awarded to undergrads. `UFLOANA`: Average amount of federal loans awarded to undergrads. Foreign Keys: `ID` -> `Institutions(ID)` 6. INSTITUTION_RANKING.csv `YEAR`, `ID` (Composite Primary Key). `RANKING_VALUE`: Yearly ranking (Numeric or NULL for unranked). Foreign Keys: `ID` -> `Institutions(ID)` 7. INSTITUTION_STATUS.csv `YEAR`, `ID` (Composite Primary Key). `CYACTIVE`: Yearly status. Foreign Keys: `ID` -> `Institutions(ID)`

Steps to reproduce

Data Collection This database was built with data from the websites IPEDS and Andrew G. Reiter Link 1 to IPEDS: https://nces.ed.gov/ipeds/datacenter/DataFiles.aspx?gotoReportId=7&fromIpeds=true&sid=16c40440-bbf7-47d3-9e7f-5fb5d0807554&rtid=1 * From this IPEDS link, I downloaded the CSV file from 2020 to 2024 (Most recent 5YR period during which data is available) for he below survey: ** Directory Information - General information about US higher education institutions ** Student financial aid and net price - Financial aid information for undergrads in US higher education institutions ** Awards/degrees conferred by program (6-digit CIP code), award level, race/ethnicity, and gender - Degree offered by US higher education institutions Link 2 to IPEDS: https://nces.ed.gov/ipeds/cipcode/browse.aspx?y=55 * From his IPEDS link, I downloaded the CIPCODE and their respective program name Link to Andrew G. Reiter: https://www.andyreiter.com/datasets/ * From Andrew G. Reiter, I downloaded the excel file containing the ranks for US higher education institutions over the past years ** National University Rankings - Yearly university ranks Data Processing * Utilized excel ** To combine time series data into 1 spreadsheet as I could only download 1 csv file per year. ** To select the features, I believe were most relevant to institutional profiles, geographic data, instructional program classifications (CIP), yearly degree conferrals, financial aid metrics, and institutional rankings. *Utilized SQL ** To create a database schema ** To create placeholder tables to load the collected datasets onto SQL *** Notably, "Data" for Directory Information, "Ranking" for National University Rankings, "Award" for Awards/degrees conferred by program, "Finance" for Student financial aid and net price, and "Code_name" for CIP program ** To normalize the data to eliminate redundancy and ensure referential integrity *** Normalized tables: Table 1: institutions | Columns: ID int PK; NAME varchar(255); ADDR varchar(255); LOCATIONID int. Table 2: locations | Columns: LOCATIONID int PK; CITY varchar(100); STATE varchar(50); ZIP varchar(20). FK: LOCATIONID -> institutions(LOCATIONID) Table 3: institution_ranking | Columns: YEAR int PK; ID int PK; RANKING_VALUE varchar(20). FK: ID -> institutions(ID) Table 4: institution_status | Columns: ID int PK; YEAR int PK; CYACTIVE varchar(5). FK: ID -> institutions(ID) Table 5: program | Columns: CIPCODE varchar(20) PK; CIPTITLE varchar(1000). Table 6: award_conferred | Columns: YEAR int PK; ID int PK; CIPCODE varchar(20) PK; CTOTALT int; CTOTALM int; CTOTALW int. FK: ID -> institutions(ID) FK: CIPCODE -> program(CIPCODE) Table 7: financial_aid | Columns: YEAR int PK; ID int PK; SCFY2 int; SCFY1N int; UAGRNTN int; UAGRNTT bigint; UAGRNTA decimal(15,2); UFLOANN int; UFLOANT bigint; UFLOANA decimal(15,2). FK: ID -> institutions(ID)

Institutions

• Wentworth Institute of Technology

  Massachusetts, Boston

Categories

Licence