IRS990_Tech_Exercise_DATA_ACCESS.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Extracting IRS 990 Data from Amazon AWS\n",
    "#### Marianne C. Halloran                                                                                                \n",
    "**October 12, 2017**\n",
    "\n",
    "**IRS 2016 Form 990s from AWS**, public dataset containing financinal information about NPOs: https://aws.amazon.com/public-datasets/irs-990/\n",
    "\n",
    "**IRS Style Sheet for Form 990**: [IRS990 StyleSheet](IRS990_Stylesheet.xsl)\n",
    "\n",
    "\n",
    "**A. Data Info and Access**\n",
    "- Vital information on the tax-exempt community, comprised mostly of 501(c)(3) organizations, but also includes 501(c),4947(a)(1) and 527.\n",
    "- Does not include donor information or other personally identifiable information (important from ethics' perspective).\n",
    "\n",
    "\n",
    "**B. Data format and contents**\n",
    "For the purposes of this exercise, I looked into the following fields:\n",
    "\n",
    "1. **EIN** (EIN): Employer identification number, format = integer.\n",
    "<br>\n",
    "2. **Contract termination** (contract_term):  organization has terminated its existence or ceased, format  = [0,1] for [not terminated, terminated]\n",
    "<br>\n",
    "3. **Tax_status** (tax_status): refer to the tax-exemption character of the NPO. Data format is categorical (integer): <br>\n",
    "    0 - 501(c)(3) organizations; <br>\n",
    "    1 - 501(c)organizations; <br>\n",
    "    2 - 4947(a)(1)organizations; <br>\n",
    "    3 - 527 organizations; <br>\n",
    "    4 - Not answered.<br>\n",
    "\n",
    "4. **Organization Name** (org_name), **City** and **State**: format = string.\n",
    "<br>\n",
    "5. **Activity** (activity): short description of the NPO's activities and mission, format = string. *Could be used in a NLP framework*\n",
    "<br>\n",
    "6. **Year formed** (year_formed): year of establishment of NPO, format = integer.\n",
    "<br>\n",
    "7. **Volunteer and Employee counts** (volunteer_ct, employee_ct): number of total volunteers and employees, format = integer.\n",
    "<br>\n",
    "8. **Total Revenues and Expenses** (total_revenue, total_expenses): format = float\n",
    "In specific here, we look at:\n",
    "- **Revenues** from fundraising events, campaigns, membership dues, government grants, gifts and program services.\n",
    "- **Expenses** related to management, compensation, and service. \n",
    "\n",
    "*** The idea is that, by understanding how a NPO obtains revenue and spends its funds, we will be better poised to understand its efficacy. It also answers the questions about the financial strength of the NPO (its ability to attract resources, level of reserves, financial accountability, etc).** \n",
    "\n",
    "9. **Net Assets** (net_assets): format = float\n",
    "<br>\n",
    "10. **Political and Lobbying Activity** (pol_act,lob_act): categorical representation for political or lobbying activity. True/False=(1,0)\n",
    "<br>\n",
    "11. **Foreign Affairs: offices, fundraising and assistance** (foreign_office, foreign_fundraising,foreign_assist): categorical representation of foreign offices, fundraising or assistance to individuals. True/False(1,0)\n",
    "<br>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#=================================================================#\n",
    "# LIBRARIES                                                       #\n",
    "#=================================================================#\n",
    "from __future__ import print_function\n",
    "import numpy as np\n",
    "import seaborn as sb\n",
    "import requests\n",
    "import csv\n",
    "import os\n",
    "from io import StringIO\n",
    "import pandas as pd\n",
    "from bs4 import BeautifulSoup as bs\n",
    "from IPython.display import FileLink, FileLinks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u0011 Retrieved 378420 NPOs names from: \n",
      "\n",
      "HARRIET AND HARMON KELLEY FOUNDATION FOR THE ARTS \n",
      "to \n",
      "SOUTH TOMS RIVER VOLUNTEER FIRST AID SQUAD INC\n"
     ]
    }
   ],
   "source": [
    "#=================================================================#\n",
    "# LOAD DATASET INDEX                                              #\n",
    "#=================================================================#\n",
    "# Index listings of available filings (JSON and CSV)\n",
    "# https://s3.amazonaws.com/irs-form-990/index_2016.csv \n",
    "# https://s3.amazonaws.com/irs-form-990/index_2016.json\n",
    "# Use field OBJECT_ID to download forms\n",
    "\n",
    "# Load and save for later\n",
    "save_file_name = 'index_990_2016.csv'\n",
    "url = 'https://s3.amazonaws.com/irs-form-990/index_2016.csv'\n",
    "   \n",
    "# Load for Pandas\n",
    "download=requests.get(url).content\n",
    "index_2016=pd.read_csv(StringIO(download.decode('utf-8')))\n",
    "\n",
    "print(u\"\\u0011\",'Retrieved %d NPOs names from: \\n\\n%s \\nto \\n%s' % \n",
    "      (len(index_2016.TAXPAYER_NAME),\n",
    "       index_2016.TAXPAYER_NAME.iloc[0],\n",
    "       index_2016.TAXPAYER_NAME.iloc[-1]    ))\n",
    "\n",
    "index_2016.to_csv('input/index.csv', index=False)\n",
    "del index_2016"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u0011 Retrieving records.\n"
     ]
    }
   ],
   "source": [
    "#=================================================================#\n",
    "# DOWNLOAD DATASET                                                #\n",
    "#=================================================================#\n",
    "\n",
    "# If loading dataset for the first time, uncomment line bellow\n",
    "meta = pd.read_csv('input/index.csv')\n",
    "NPO_meta = []; k=0\n",
    "\n",
    "# Read xml\n",
    "print(u\"\\u0011\",\"Retrieving records.\")\n",
    "for xmlid in meta['OBJECT_ID']:\n",
    "    try:\n",
    "        url = \"https://s3.amazonaws.com/irs-form-990/%d_public.xml\" % xmlid\n",
    "        NPOxml = requests.get(url)\n",
    "        NPOsoup = bs(NPOxml.text[3:], 'xml') # doing the [3:] takes care of some weird characters at front\n",
    "    except requests.exceptions.Timeout:\n",
    "        print(\"Timeout\")\n",
    "        pass\n",
    "    except requests.exceptions.TooManyRedirects:\n",
    "        print(\"Too Many Redirects\")\n",
    "        pass\n",
    "    except requests.exceptions.RequestException as e:\n",
    "        print(\"Request Exception: e\")\n",
    "        break\n",
    "\n",
    "\n",
    "    #=================================================================#\n",
    "    # GET NPO DATA                                                    #\n",
    "    #=================================================================#\n",
    "\n",
    "    ## EIN\n",
    "    try:\n",
    "        EIN = (NPOsoup.find('EIN').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        EIN = 0  \n",
    "\n",
    "    ## Contract Termination\n",
    "    # If the NPO discontinued operations or disposed of more than 25% of its assets\n",
    "    try:\n",
    "        contract_term = (NPOsoup.find('ContractTerminationInd').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        contract_term = 0    \n",
    "\n",
    "    ## Tax Exempt Status\n",
    "    tax_status = None\n",
    "    status_fields = ['Organization501c3Ind','Organization501cInd',\n",
    "                      'Organization4947a1Ind','Organization527Ind']\n",
    "    for status_field in status_fields:\n",
    "        try:\n",
    "            tax_status = (NPOsoup.find(status_field).contents[0]).encode('utf-8')\n",
    "            if tax_status == 'X':\n",
    "                tax_status = status_fields.index(status_field)\n",
    "            break\n",
    "        except AttributeError:\n",
    "            pass\n",
    "    if tax_status == None:\n",
    "        tax_status = 4\n",
    "\n",
    "    ## Name \n",
    "    try:\n",
    "        org_name = (NPOsoup.find('BusinessNameLine1Txt').contents[0]).encode('utf-8')    ## City\n",
    "    except AttributeError:\n",
    "        try:\n",
    "            org_name = (NPOsoup.find('Filer').BusinessName.BusinessNameLine1.contents[0]).encode('utf-8')\n",
    "        except AttributeError:\n",
    "            org_name = None\n",
    "\n",
    "    ## City\n",
    "    try:\n",
    "        city = (NPOsoup.find('Filer').USAddress.CityNm.contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        try:\n",
    "            city = (NPOsoup.find('Filer').USAddress.City.contents[0]).encode('utf-8')\n",
    "        except AttributeError:\n",
    "            try: \n",
    "                city = (NPOsoup.find('City').contents[0]).encode('utf-8')\n",
    "            except AttributeError:\n",
    "                city = None\n",
    "\n",
    "    ## State\n",
    "    try:\n",
    "        state = (NPOsoup.find('Filer').USAddress.StateAbbreviationCd.contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        try:\n",
    "            state = (NPOsoup.find('Filer').USAddress.State.contents[0]).encode('utf-8')\n",
    "        except AttributeError:\n",
    "            try:\n",
    "                state = (NPOsoup.find('State').contents[0]).encode('utf-8')\n",
    "            except AttributeError:\n",
    "                state = None\n",
    "\n",
    "    ## Tax Year\n",
    "    try:\n",
    "        tax_year = (NPOsoup.find('TaxYr').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        tax_year = 0\n",
    "\n",
    "    ## Activity\n",
    "    try:\n",
    "        activity = (NPOsoup.find('ActivityOrMissionDesc').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        activity = 0\n",
    "\n",
    "    ## Year formed\n",
    "    try:\n",
    "        year_formed = (NPOsoup.find('FormationYr').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        year_formed = 0\n",
    "\n",
    "    ## Volunteers\n",
    "    try:\n",
    "        volunteer_ct = (NPOsoup.find('TotalVolunteersCnt').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        volunteer_ct = 0\n",
    "\n",
    "    ## Employee Cnt\n",
    "    try:\n",
    "        employee_ct = (NPOsoup.find('TotalEmployeeCnt').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        employee_ct = 0\n",
    "\n",
    "\n",
    "    ## REVENUES ##\n",
    "\n",
    "    ## Campaigns (Part VIII, line 1a)\n",
    "    try:\n",
    "        rev_campaigns = (NPOsoup.find('FederatedCampaignsAmt').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        rev_campaigns = 0\n",
    "\n",
    "    ## Membership Dues (Part VIII, line 1b)\n",
    "    try:\n",
    "        rev_membership = (NPOsoup.find('MembershipDuesAmt').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        rev_membership = 0\n",
    "\n",
    "    ## Fundraising Events (Part VIII, line 1c)\n",
    "    try:\n",
    "        rev_fundraising = (NPOsoup.find('FundraisingAmt').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        rev_fundraising = 0\n",
    "\n",
    "    ## Government Grants (Part VIII, line 1e)\n",
    "    try:\n",
    "        rev_govgrants = (NPOsoup.find('GovernmentGrantsAmt').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        rev_govgrants = 0\n",
    "\n",
    "    ## Other gifts (Part VIII, line 1f)\n",
    "    try:\n",
    "        rev_other = (NPOsoup.find('AllOtherContributionsAmt').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        rev_other = 0\n",
    "\n",
    "    ## Program Service Revenue (Part VIII, line 2g)\n",
    "    try:\n",
    "        rev_progserv = (NPOsoup.find('TotalProgramServiceRevenueAmt').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        rev_progserv = 0\n",
    "\n",
    "\n",
    "    ## Net from Fundraising Events (Part VIII, line 3c)\n",
    "    try:\n",
    "        rev_netfundraising = (NPOsoup.find('NetIncmFromFundraisingEvtGrp/TotalRevenueColumnAmt').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        rev_netfundraising = 0\n",
    "\n",
    "\n",
    "    ## CY Total Revenue (Part VIII, line 1c)\n",
    "    total_revenue = None\n",
    "    revenue_fields = ['TotalRevenueCurrentYear', 'TotalRevenue', 'TotalRevenueAmt','CYTotalRevenueAmt']\n",
    "    for revenue_field in revenue_fields:\n",
    "        try:\n",
    "            total_revenue = (NPOsoup.find(revenue_field).contents[0]).encode('utf-8')\n",
    "            break\n",
    "        except AttributeError:\n",
    "            pass\n",
    "    if total_revenue == None:\n",
    "        total_revenue = 0\n",
    "\n",
    "    ## PY Total Revenue (Part VIII, line 1c)\n",
    "    try:\n",
    "        total_revenuePY = (NPOsoup.find('PYTotalRevenueAmt').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        total_revenuePY = 0\n",
    "\n",
    "\n",
    "    ## EXPENSES\n",
    "    ## Total Grant Expenses  (Part IX, line 25B)\n",
    "    try:\n",
    "        exp_grants = (NPOsoup.find('CYGrantsAndSimilarPaidAmt').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        exp_grants = 0\n",
    "\n",
    "    ## Total Service Expenses  (Part IX, line 25B)\n",
    "    try:\n",
    "        exp_progserv = (NPOsoup.find('CYBenefitsPaidToMembersAmt').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        exp_progserv = 0\n",
    "\n",
    "\n",
    "    ## Total Management Expenses (Part IX, line 25C)\n",
    "    try:\n",
    "        exp_management = (NPOsoup.find('CYSalariesCompEmpBnftPaidAmt').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        exp_management = 0\n",
    "\n",
    "    ## Total Fundraising Expensens (Part IX, line 25D)\n",
    "    try:\n",
    "        exp_fundraising = (NPOsoup.find('CYTotalFundraisingExpenseAmt').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        exp_fundraising = 0\n",
    "\n",
    "    ## CY Total Expenses    \n",
    "    total_expenses = None\n",
    "    expense_fields = ['CYTotalExpensesAmt','TotalExpenses','TotalExpensesAmt']\n",
    "    for expense_field in expense_fields:\n",
    "        try:\n",
    "            total_expenses = (NPOsoup.find(expense_field).contents[0]).encode('utf-8')\n",
    "            break\n",
    "        except AttributeError:\n",
    "            pass\n",
    "    if total_expenses == None:\n",
    "        total_expenses = 0\n",
    "\n",
    "    ## PY Total Expenses (Part VIII, column (A), line 25)\n",
    "    try:\n",
    "        total_expensesPY = (NPOsoup.find('PYTotalExpensesAmt').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        total_expensesPY = 0\n",
    "\n",
    "\n",
    "    ## COMPENSANTIONS\n",
    "\n",
    "    ## Total Compensations (PART VII)\n",
    "    try:\n",
    "        total_compensations = (NPOsoup.find('TotalReportableCompFromOrgAmt').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        total_compensations = 0\n",
    "\n",
    "    ## Compensations more than $100k (Part VII, line 2)\n",
    "    try:\n",
    "        comp_more100k = (NPOsoup.find('IndivRcvdGreaterThan100KCnt').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        comp_more100k = 0\n",
    "\n",
    "\n",
    "    ## Net Assessts of Fund Balances *End of Year*\n",
    "    try:\n",
    "        net_assets = (NPOsoup.find('NetAssetsOrFundBalancesEOYAmt').contents[0]).encode('utf-8')\n",
    "    except AttributeError:\n",
    "        net_assets = 0\n",
    "\n",
    "    ## Political Campaing Activity (NPO engage in direct or indirect political campaign \n",
    "    #  activities on behalf of or in opposition to candidates for public office?)\n",
    "    try:\n",
    "        pol_act = (NPOsoup.find('PoliticalCampaignActyInd').contents[0]).encode('utf-8')\n",
    "        if pol_act == 'false' or 'False':\n",
    "            pol_act = 0\n",
    "        if pol_act == 'true' or 'True' or 'X':\n",
    "            pol_act = 1\n",
    "        else:\n",
    "            pol_act = 2\n",
    "    except AttributeError:\n",
    "        pol_act = 2 #Not reported\n",
    "\n",
    "    ## Lobbying Activities (NPO engage in lobbying activities, or have a section \n",
    "    #  501(h) election in effect during the tax year?)\n",
    "    try:\n",
    "        lob_act = (NPOsoup.find('LobbyingActivitiesInd').contents[0]).encode('utf-8')\n",
    "        if lob_act == 'false' or 'False':\n",
    "            lob_act = 0\n",
    "        if lob_act == 'true' or 'True' or 'X':\n",
    "            lob_act = 1\n",
    "        else:\n",
    "            lob_act = 2\n",
    "    except AttributeError:\n",
    "        lob_act = 2 #Not reported\n",
    "\n",
    "\n",
    "    ## Foreign office (NPO have office, employees, or agents outside of the United States?)\n",
    "    try:\n",
    "        foreign_office = (NPOsoup.find('ForeignOfficeInd').contents[0]).encode('utf-8')\n",
    "        if foreign_office == 'false' or 'False':\n",
    "            foreign_office = 0\n",
    "        if foreign_office == 'true' or 'True' or 'X':\n",
    "            foreign_office = 1\n",
    "        else:\n",
    "            foreign_office = 2\n",
    "    except AttributeError:\n",
    "        foreign_office = 2 #Not reported\n",
    "\n",
    "    ## Foreign Fundraising\n",
    "    #  NPO aggregate revenues or expenses of more than $10,000 from grantmaking, fundraising, \n",
    "    #  business, investment, and program service activities outside the United States, or \n",
    "    #  aggregate foreign investments valued at $100,000 or more?\n",
    "    try:\n",
    "        foreign_fundraising = (NPOsoup.find('ForeignActivitiesInd').contents[0]).encode('utf-8')\n",
    "        if foreign_fundraising == 'false' or 'False':\n",
    "            foreign_fundraising = 0\n",
    "        if foreign_fundraising == 'true' or 'True' or 'X':\n",
    "            foreign_fundraising = 1\n",
    "        else:\n",
    "            foreign_fundraising = 2\n",
    "    except AttributeError:\n",
    "        foreign_fundraising = 2 #Not reported\n",
    "\n",
    "\n",
    "    ## Assistance to Foreign Individuals\n",
    "    #  more than $5,000 of grants or other assistance to or for any foreign organization?\n",
    "    #  more than $5,000 of aggregate grants or other assistance to or for foreign individuals?\n",
    "    foreign_assist = None\n",
    "    foreign_assist_fields= ['MoreThan5000KToOrgInd', 'MoreThan5000KToIndividualsInd']\n",
    "    for foreign_assist_field in foreign_assist_fields:\n",
    "        try:\n",
    "            foreign_assist = (NPOsoup.find(foreign_assist_field).contents[0]).encode('utf-8')\n",
    "            if foreign_assist == 'false' or 'False':\n",
    "                foreign_assist = 0\n",
    "            if foreign_assist == 'true' or 'True' or 'X':\n",
    "                    foreign_assist = 1\n",
    "            else:\n",
    "                foreign_assist = 2\n",
    "            break\n",
    "        except AttributeError:\n",
    "            pass\n",
    "    if foreign_assist == None:\n",
    "        foreign_assist = 2\n",
    "\n",
    "    NPO_meta.append([EIN,contract_term, tax_status,org_name, city, state, tax_year, activity, \n",
    "                     year_formed,volunteer_ct, employee_ct, rev_campaigns, \n",
    "                     rev_membership, rev_fundraising, rev_govgrants, rev_other, \n",
    "                     rev_progserv, rev_netfundraising, total_revenue, total_revenuePY,\n",
    "                     exp_grants, exp_progserv, exp_management, exp_fundraising, \n",
    "                     total_expenses, total_compensations, comp_more100k, net_assets, \n",
    "                     pol_act,lob_act, foreign_office, foreign_fundraising,\n",
    "                     foreign_assist])\n",
    "\n",
    "    k+=1\n",
    "    if k==1000:\n",
    "        break\n",
    "        \n",
    "NPO_meta_df = pd.DataFrame(NPO_meta)\n",
    "NPO_meta_df.to_csv('input/NPO_meta.csv', index=False)\n",
    "print(\"Metafile saved to NPO_meta.csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}