Data

Schema

Unfiltered IES Awards

LDI

Overview

There are 3 databases: bronze, silver, and gold. This project leveraged a medallion architecture.

flowchart TB

    %% Databases
    bronze[(Database Bronze)]
    silver[(Database Silver)]
    gold[(Database Gold)]
    
    %% Data
    A[IES Award Website]
    B[ies_award_links]
    C[ies_award_elements_short]
    D[ies_award_elements_long]
    E[ies_award_elements_urls]
    F[ies_award_elements_archive]    
    
    G["
    USA Spend
    Assistance/Grants & Contracts
    "]
    H[ALI]
    I[Awardee Lookup]
    J[State populations]
    K[Awardee lookup table]
    L[Matched awards]

    M[Filtered data]
    
    %% Flow
    A --> B
    B --> C --> bronze
    B --> D --> bronze
    B --> E --> bronze
    B --> F --> bronze

    bronze --> G --> L
    bronze --> H --> L
    bronze --> I --> L
    bronze --> J --> L
    bronze --> K --> L

    L --> silver

    silver --> M --> gold

Filters

• award year \(\ge\) 2016

Schema

• primary key: award_number_clean

ibis.Schema {
  award_number                                         string
  award_number_clean                                   string
  parent_award_id_piid                                 string
  status                                               string
  award_type                                           string
  name                                                 string
  amount                                               int64
  award_year                                           int64
  award_date_range_start                               date
  award_date_range_end                                 date
  awardee                                              string
  project_type                                         string
  principal_investigator                               string
  office                                               array<string>
  program                                              array<string>
  program_topics                                       array<string>
  evaluation_topics                                    array<string>
  awarding_agency_code                                 string
  awarding_agency_name                                 string
  awarding_sub_agency_code                             string
  awarding_sub_agency_name                             string
  recipient_uei                                        string
  recipient_name                                       string
  recipient_parent_uei                                 string
  recipient_parent_name                                string
  recipient_country_code                               string
  recipient_country_name                               string
  recipient_county_name                                string
  recipient_city_name                                  string
  recipient_state_code                                 string
  recipient_state_name                                 string
  recipient_zip_code                                   string
  recipient_county_fips_code                           string
  recipient_state_fips_code                            string
  primary_place_of_performance_country_code            string
  primary_place_of_performance_country_name            string
  primary_place_of_performance_city_name               string
  primary_place_of_performance_county_name             string
  primary_place_of_performance_state_name              string
  primary_place_of_performance_zip_code                string
  primary_place_of_performance_county_fips_code        string
  primary_place_of_performance_state_fips_code         string
  usaspending_permalink                                string
  as_of                                                date
  recipient_congressional_district                     string
  recipient_congressional_member                       string
  primary_place_of_performance_congressional_district  string
  primary_place_of_performance_congressional_member    string
  state_population                                     int64
}