Charity Navigator (9b) - Merge and Variable Creation for Additional Robustness Tests.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/gregorysaxton/Google Drive/SOX\n"
     ]
    }
   ],
   "source": [
    "cd '/Users/gregorysaxton/Google Drive/SOX'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pandas import DataFrame\n",
    "from pandas import Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Set PANDAS to show all columns in DataFrame\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('max_colwidth',200)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from __future__ import division"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "304\n",
      "85401\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>org_id</th>\n",
       "      <th>EIN</th>\n",
       "      <th>org_url</th>\n",
       "      <th>name</th>\n",
       "      <th>category</th>\n",
       "      <th>category-full</th>\n",
       "      <th>Date Published</th>\n",
       "      <th>Form 990 FYE</th>\n",
       "      <th>Form 990 FYE, v2</th>\n",
       "      <th>FYE</th>\n",
       "      <th>Earliest Rating Publication Date</th>\n",
       "      <th>ratings_system</th>\n",
       "      <th>Overall Score</th>\n",
       "      <th>Overall Rating</th>\n",
       "      <th>advisory text - current advisory</th>\n",
       "      <th>advisory text - past advisory</th>\n",
       "      <th>current_or_past_donor_advisory</th>\n",
       "      <th>current_donor_advisory</th>\n",
       "      <th>past_donor_advisory</th>\n",
       "      <th>latest_entry</th>\n",
       "      <th>current_ratings_url</th>\n",
       "      <th>ein_2016</th>\n",
       "      <th>Publication_date_and_FY_2016</th>\n",
       "      <th>Publication Date_2016</th>\n",
       "      <th>FYE_2016</th>\n",
       "      <th>donor_alert_2016</th>\n",
       "      <th>overall_rating_2016</th>\n",
       "      <th>efficiency_rating_rating_2016</th>\n",
       "      <th>AT_rating_2016</th>\n",
       "      <th>overall_rating_star_2016</th>\n",
       "      <th>financial_rating_star_2016</th>\n",
       "      <th>AT_rating_star_2016</th>\n",
       "      <th>program_expense_percent_2016</th>\n",
       "      <th>admin_expense_percent_2016</th>\n",
       "      <th>fund_expense_percent_2016</th>\n",
       "      <th>fund_efficiency_2016</th>\n",
       "      <th>working_capital_ratio_2016</th>\n",
       "      <th>program_expense_growth_2016</th>\n",
       "      <th>liabilities_to_assets_2016</th>\n",
       "      <th>independent_board_2016</th>\n",
       "      <th>no_material_division_2016</th>\n",
       "      <th>audited_financials_2016</th>\n",
       "      <th>no_loans_related_2016</th>\n",
       "      <th>documents_minutes_2016</th>\n",
       "      <th>form_990_2016</th>\n",
       "      <th>conflict_of_interest_policy_2016</th>\n",
       "      <th>whistleblower_policy_2016</th>\n",
       "      <th>records_retention_policy_2016</th>\n",
       "      <th>CEO_listed_2016</th>\n",
       "      <th>process_CEO_compensation_2016</th>\n",
       "      <th>no_board_compensation_2016</th>\n",
       "      <th>donor_privacy_policy_2016</th>\n",
       "      <th>board_listed_2016</th>\n",
       "      <th>audited_financials_web_2016</th>\n",
       "      <th>form_990_web_2016</th>\n",
       "      <th>staff_listed_2016</th>\n",
       "      <th>contributions_gifts_grants_2016</th>\n",
       "      <th>federated_campaigns_2016</th>\n",
       "      <th>membership_dues_2016</th>\n",
       "      <th>fundraising_events_2016</th>\n",
       "      <th>related_organizations_2016</th>\n",
       "      <th>government_grants_2016</th>\n",
       "      <th>total_contributions_2016</th>\n",
       "      <th>program_service_revenue_2016</th>\n",
       "      <th>total_primary_revenue_2016</th>\n",
       "      <th>other_revenue_2016</th>\n",
       "      <th>total_revenue_2016</th>\n",
       "      <th>program_expenses_2016</th>\n",
       "      <th>administrative_expenses_2016</th>\n",
       "      <th>fundraising_expenses_2016</th>\n",
       "      <th>total_functional_expenses_2016</th>\n",
       "      <th>payments_to_affiliates_2016</th>\n",
       "      <th>excess_or_deficit_2016</th>\n",
       "      <th>net_assets_2016</th>\n",
       "      <th>comp_2016</th>\n",
       "      <th>cp_2016</th>\n",
       "      <th>mission_2016</th>\n",
       "      <th>2011_data</th>\n",
       "      <th>charity_name_2011</th>\n",
       "      <th>category_2011</th>\n",
       "      <th>city_2011</th>\n",
       "      <th>state_2011</th>\n",
       "      <th>cause_2011</th>\n",
       "      <th>tag_line_2011</th>\n",
       "      <th>url_2011</th>\n",
       "      <th>ein_2011</th>\n",
       "      <th>fye_2011</th>\n",
       "      <th>overall_rating_2011</th>\n",
       "      <th>overall_rating_2011_plus_30</th>\n",
       "      <th>overall_rating_2011_plus_30_v2</th>\n",
       "      <th>overall_rating_star_2011</th>\n",
       "      <th>overall_rating_star_2011_text</th>\n",
       "      <th>efficiency_rating_2011</th>\n",
       "      <th>AT_rating_2011</th>\n",
       "      <th>financial_rating_star_2011</th>\n",
       "      <th>AT_rating_star_2011</th>\n",
       "      <th>program_expense_percent_2011</th>\n",
       "      <th>admin_expense_percent_2011</th>\n",
       "      <th>fund_expense_percent_2011</th>\n",
       "      <th>fund_efficiency_2011</th>\n",
       "      <th>primary_revenue_growth_2011</th>\n",
       "      <th>program_expense_growth_2011</th>\n",
       "      <th>working_capital_ratio_2011</th>\n",
       "      <th>independent_board_2011</th>\n",
       "      <th>no_material_division_2011</th>\n",
       "      <th>audited_financials_2011</th>\n",
       "      <th>no_loans_related_2011</th>\n",
       "      <th>documents_minutes_2011</th>\n",
       "      <th>form_990_2011</th>\n",
       "      <th>conflict_of_interest_policy_2011</th>\n",
       "      <th>whistleblower_policy_2011</th>\n",
       "      <th>records_retention_policy_2011</th>\n",
       "      <th>CEO_listed_2011</th>\n",
       "      <th>process_CEO_compensation_2011</th>\n",
       "      <th>no_board_compensation_2011</th>\n",
       "      <th>donor_privacy_policy_2011</th>\n",
       "      <th>board_listed_2011</th>\n",
       "      <th>audited_financials_web_2011</th>\n",
       "      <th>form_990_web_2011</th>\n",
       "      <th>staff_listed_2011</th>\n",
       "      <th>primary_revenue_2011</th>\n",
       "      <th>other_revenue_2011</th>\n",
       "      <th>total_revenue_2011</th>\n",
       "      <th>govt_revenue_2011</th>\n",
       "      <th>program_expense_2011</th>\n",
       "      <th>admin_expense_2011</th>\n",
       "      <th>fund_expense_2011</th>\n",
       "      <th>total_functional_expense_2011</th>\n",
       "      <th>affiliate_payments_2011</th>\n",
       "      <th>budget_surplus_2011</th>\n",
       "      <th>net_assets_2011</th>\n",
       "      <th>leader_comp_2011</th>\n",
       "      <th>leader_comp_percent_2011</th>\n",
       "      <th>email_2011</th>\n",
       "      <th>website_2011</th>\n",
       "      <th>2016 Advisory - Date Posted</th>\n",
       "      <th>2016 Advisory - Charity Name</th>\n",
       "      <th>2016 Advisory - advisory_url</th>\n",
       "      <th>2016 Advisory - advisory</th>\n",
       "      <th>_merge_v1</th>\n",
       "      <th>to_be_merged</th>\n",
       "      <th>NEW ROW</th>\n",
       "      <th>NAME_2015_BMF</th>\n",
       "      <th>STREET_2015_BMF</th>\n",
       "      <th>CITY_2015_BMF</th>\n",
       "      <th>STATE_2015_BMF</th>\n",
       "      <th>ZIP_2015_BMF</th>\n",
       "      <th>RULING_2015_BMF</th>\n",
       "      <th>ACTIVITY_2015_BMF</th>\n",
       "      <th>TAX_PERIOD_2015_BMF</th>\n",
       "      <th>ASSET_AMT_2015_BMF</th>\n",
       "      <th>INCOME_AMT_2015_BMF</th>\n",
       "      <th>REVENUE_AMT_2015_BMF</th>\n",
       "      <th>NTEE_CD_2015_BMF</th>\n",
       "      <th>2015 BMF</th>\n",
       "      <th>ruledate_2004_BMF</th>\n",
       "      <th>name_MSTRALL</th>\n",
       "      <th>state_MSTRALL</th>\n",
       "      <th>NTEE1_MSTRALL</th>\n",
       "      <th>nteecc_MSTRALL</th>\n",
       "      <th>zip_MSTRALL</th>\n",
       "      <th>fips_MSTRALL</th>\n",
       "      <th>taxper_MSTRALL</th>\n",
       "      <th>income_MSTRALL</th>\n",
       "      <th>F990REV_MSTRALL</th>\n",
       "      <th>assets_MSTRALL</th>\n",
       "      <th>ruledate_MSTRALL</th>\n",
       "      <th>deductcd_MSTRALL</th>\n",
       "      <th>accper_MSTRALL</th>\n",
       "      <th>rule_date_v1</th>\n",
       "      <th>taxpd</th>\n",
       "      <th>NAME_SOI</th>\n",
       "      <th>yr_frmtn</th>\n",
       "      <th>pt1_num_vtng_gvrn_bdy_mems</th>\n",
       "      <th>pt1_num_ind_vtng_mems</th>\n",
       "      <th>num_vtng_gvrn_bdy_mems</th>\n",
       "      <th>num_ind_vtng_mems</th>\n",
       "      <th>tot_num_empls</th>\n",
       "      <th>tot_num_vlntrs</th>\n",
       "      <th>contri_grnts_cy</th>\n",
       "      <th>prog_srvc_rev_cy</th>\n",
       "      <th>invst_incm_cy</th>\n",
       "      <th>oth_rev_cy</th>\n",
       "      <th>grnts_and_smlr_amts_cy</th>\n",
       "      <th>tot_prof_fndrsng_exp_cy</th>\n",
       "      <th>tot_fndrsng_exp_cy</th>\n",
       "      <th>pt1_tot_asts_eoy</th>\n",
       "      <th>aud_fincl_stmts</th>\n",
       "      <th>mtrl_divrsn_or_misuse</th>\n",
       "      <th>cnflct_int_plcy</th>\n",
       "      <th>whistleblower_plcy</th>\n",
       "      <th>doc_retention_plcy</th>\n",
       "      <th>federated_campaigns</th>\n",
       "      <th>memshp_dues</th>\n",
       "      <th>rltd_orgs</th>\n",
       "      <th>govt_grnts</th>\n",
       "      <th>all_oth_contri</th>\n",
       "      <th>nncsh_contri</th>\n",
       "      <th>tot_contri</th>\n",
       "      <th>psr_tot</th>\n",
       "      <th>inv_incm_tot_rev</th>\n",
       "      <th>bonds_tot_rev</th>\n",
       "      <th>roylrev_tot_rev</th>\n",
       "      <th>net_rent_tot_rev</th>\n",
       "      <th>gain_or_loss_sec</th>\n",
       "      <th>gain_or_loss_oth</th>\n",
       "      <th>oth_rev_tot</th>\n",
       "      <th>tot_rev</th>\n",
       "      <th>mgmt_srvc_fee_tot</th>\n",
       "      <th>fee_for_srvc_leg_tot</th>\n",
       "      <th>fee_for_srvc_acct_tot</th>\n",
       "      <th>fee_for_srvc_lbby_tot</th>\n",
       "      <th>fee_for_srvc_prof_tot</th>\n",
       "      <th>fee_for_srvc_invst_tot</th>\n",
       "      <th>fee_for_srvc_oth_tot</th>\n",
       "      <th>fs_audited</th>\n",
       "      <th>audit_committee</th>\n",
       "      <th>vlntr_hrs</th>\n",
       "      <th>_merge_v2</th>\n",
       "      <th>rule_date</th>\n",
       "      <th>ruledate_2004_BMF_v2</th>\n",
       "      <th>ruledate_MSTRALL_v2</th>\n",
       "      <th>yr_frmtn_v2</th>\n",
       "      <th>age</th>\n",
       "      <th>category_Animals</th>\n",
       "      <th>category_Arts, Culture, Humanities</th>\n",
       "      <th>category_Community Development</th>\n",
       "      <th>category_Education</th>\n",
       "      <th>category_Environment</th>\n",
       "      <th>category_Health</th>\n",
       "      <th>category_Human Services</th>\n",
       "      <th>category_Human and Civil Rights</th>\n",
       "      <th>category_International</th>\n",
       "      <th>category_Religion</th>\n",
       "      <th>category_Research and Public Policy</th>\n",
       "      <th>govt_revenue_2011_binary</th>\n",
       "      <th>other_revenue_2011_binary</th>\n",
       "      <th>complexity_2011</th>\n",
       "      <th>advisory</th>\n",
       "      <th>SOX_policies_2011</th>\n",
       "      <th>total_revenue_2011_logged</th>\n",
       "      <th>total_revenue</th>\n",
       "      <th>total_revenue_logged</th>\n",
       "      <th>program_efficiency_2016</th>\n",
       "      <th>state</th>\n",
       "      <th>tot_func_expns_prg_srvcs</th>\n",
       "      <th>tot_func_expns_tot</th>\n",
       "      <th>_merge_v3</th>\n",
       "      <th>program_expenses</th>\n",
       "      <th>total_expenses</th>\n",
       "      <th>program_efficiency</th>\n",
       "      <th>fndrsng_events</th>\n",
       "      <th>_merge_v4</th>\n",
       "      <th>other_revenue_SOI</th>\n",
       "      <th>complexity_2016</th>\n",
       "      <th>complexity_SOI</th>\n",
       "      <th>complexity</th>\n",
       "      <th>conflict_of_interest_policy</th>\n",
       "      <th>whistleblower_policy</th>\n",
       "      <th>records_retention_policy</th>\n",
       "      <th>conflict_of_interest_policy_v2</th>\n",
       "      <th>records_retention_policy_v2</th>\n",
       "      <th>whistleblower_policy_v2</th>\n",
       "      <th>SOX_policies</th>\n",
       "      <th>SOX_policies_binary</th>\n",
       "      <th>2016_data</th>\n",
       "      <th>Advisory Text</th>\n",
       "      <th>donor_advisory</th>\n",
       "      <th>donor_advisory_2016</th>\n",
       "      <th>donor_advisory_2011_to_2016</th>\n",
       "      <th>SOX_policies_all_binary</th>\n",
       "      <th>total_revenue_no_neg</th>\n",
       "      <th>EIN_47</th>\n",
       "      <th>conflict_of_interest_policy_47</th>\n",
       "      <th>records_retention_policy_47</th>\n",
       "      <th>whistleblower_policy_47</th>\n",
       "      <th>SOX_policies_47</th>\n",
       "      <th>SOX_policies_all_binary_47</th>\n",
       "      <th>SOX_policies_binary_47</th>\n",
       "      <th>tot_rev_47</th>\n",
       "      <th>total_revenue_logged_47</th>\n",
       "      <th>program_expenses_47</th>\n",
       "      <th>total_expenses_47</th>\n",
       "      <th>program_efficiency_47</th>\n",
       "      <th>complexity_47</th>\n",
       "      <th>_merge_47</th>\n",
       "      <th>OrganizationName_efile</th>\n",
       "      <th>URL_efile</th>\n",
       "      <th>SubmittedOn_efile</th>\n",
       "      <th>TaxPeriod_efile</th>\n",
       "      <th>whistleblower_policy_efile</th>\n",
       "      <th>conflict_of_interest_policy_efile</th>\n",
       "      <th>records_retention_policy_efile</th>\n",
       "      <th>SOX_policies_efile</th>\n",
       "      <th>SOX_policies_binary_efile</th>\n",
       "      <th>SOX_policies_all_binary_efile</th>\n",
       "      <th>tot_rev_efile</th>\n",
       "      <th>tot_rev_no_neg_efile</th>\n",
       "      <th>total_revenue_logged_efile</th>\n",
       "      <th>program_expenses_efile</th>\n",
       "      <th>total_expenses_efile</th>\n",
       "      <th>program_efficiency_efile</th>\n",
       "      <th>complexity_efile</th>\n",
       "      <th>_merge_efile</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16722</td>\n",
       "      <td>020503776</td>\n",
       "      <td>http://www.charitynavigator.org/index.cfm?bay=search.summary&amp;orgid=16722</td>\n",
       "      <td>Portsmouth Girls Softball Association</td>\n",
       "      <td>Human Services</td>\n",
       "      <td>Human Services : Multipurpose Human Service Organizations</td>\n",
       "      <td>2016-08-12 00:00:00</td>\n",
       "      <td>current</td>\n",
       "      <td>2015-01-01</td>\n",
       "      <td>current</td>\n",
       "      <td>NaN</td>\n",
       "      <td>current</td>\n",
       "      <td>NaN</td>\n",
       "      <td>current (2016) donor advisory</td>\n",
       "      <td>On August 1, 2016, the New Hampshire Union Leader published an article titled, \"Former Portsmouth youth softball president accused of stealing thousands from nonprofit.\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "      <td>http://www.charitynavigator.org/index.cfm?bay=search.summary&amp;orgid=16722</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>current</td>\n",
       "      <td>current donor advisory 2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PORTSMOUTH GIRLS SOFTBALL ASSOCIATION</td>\n",
       "      <td>PO BOX 8092</td>\n",
       "      <td>PORTSMOUTH</td>\n",
       "      <td>NH</td>\n",
       "      <td>03802-8092</td>\n",
       "      <td>201104.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>201309.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>N63</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2011</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "      <td>2011</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>On August 1, 2016, the New Hampshire Union Leader published an article titled, \"Former Portsmouth youth softball president accused of stealing thousands from nonprofit.\"</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  org_id        EIN  \\\n",
       "0  16722  020503776   \n",
       "\n",
       "                                                                    org_url  \\\n",
       "0  http://www.charitynavigator.org/index.cfm?bay=search.summary&orgid=16722   \n",
       "\n",
       "                                    name        category  \\\n",
       "0  Portsmouth Girls Softball Association  Human Services   \n",
       "\n",
       "                                               category-full  \\\n",
       "0  Human Services : Multipurpose Human Service Organizations   \n",
       "\n",
       "        Date Published Form 990 FYE Form 990 FYE, v2      FYE  \\\n",
       "0  2016-08-12 00:00:00      current       2015-01-01  current   \n",
       "\n",
       "  Earliest Rating Publication Date ratings_system Overall Score  \\\n",
       "0                              NaN        current           NaN   \n",
       "\n",
       "                  Overall Rating  \\\n",
       "0  current (2016) donor advisory   \n",
       "\n",
       "                                                                                                                                            advisory text - current advisory  \\\n",
       "0  On August 1, 2016, the New Hampshire Union Leader published an article titled, \"Former Portsmouth youth softball president accused of stealing thousands from nonprofit.\"   \n",
       "\n",
       "  advisory text - past advisory  current_or_past_donor_advisory  \\\n",
       "0                           NaN                             1.0   \n",
       "\n",
       "   current_donor_advisory  past_donor_advisory latest_entry  \\\n",
       "0                     1.0                  0.0         True   \n",
       "\n",
       "                                                        current_ratings_url  \\\n",
       "0  http://www.charitynavigator.org/index.cfm?bay=search.summary&orgid=16722   \n",
       "\n",
       "  ein_2016 Publication_date_and_FY_2016 Publication Date_2016 FYE_2016  \\\n",
       "0      NaN                          NaN                   NaN  current   \n",
       "\n",
       "              donor_alert_2016 overall_rating_2016  \\\n",
       "0  current donor advisory 2016                 NaN   \n",
       "\n",
       "  efficiency_rating_rating_2016 AT_rating_2016 overall_rating_star_2016  \\\n",
       "0                           NaN            NaN                      NaN   \n",
       "\n",
       "  financial_rating_star_2016 AT_rating_star_2016 program_expense_percent_2016  \\\n",
       "0                        NaN                 NaN                          NaN   \n",
       "\n",
       "  admin_expense_percent_2016 fund_expense_percent_2016 fund_efficiency_2016  \\\n",
       "0                        NaN                       NaN                  NaN   \n",
       "\n",
       "  working_capital_ratio_2016 program_expense_growth_2016  \\\n",
       "0                        NaN                         NaN   \n",
       "\n",
       "  liabilities_to_assets_2016 independent_board_2016 no_material_division_2016  \\\n",
       "0                        NaN                    NaN                       NaN   \n",
       "\n",
       "  audited_financials_2016 no_loans_related_2016 documents_minutes_2016  \\\n",
       "0                     NaN                   NaN                    NaN   \n",
       "\n",
       "  form_990_2016 conflict_of_interest_policy_2016 whistleblower_policy_2016  \\\n",
       "0           NaN                              NaN                       NaN   \n",
       "\n",
       "  records_retention_policy_2016 CEO_listed_2016 process_CEO_compensation_2016  \\\n",
       "0                           NaN             NaN                           NaN   \n",
       "\n",
       "  no_board_compensation_2016 donor_privacy_policy_2016 board_listed_2016  \\\n",
       "0                        NaN                       NaN               NaN   \n",
       "\n",
       "  audited_financials_web_2016 form_990_web_2016 staff_listed_2016  \\\n",
       "0                         NaN               NaN               NaN   \n",
       "\n",
       "   contributions_gifts_grants_2016  federated_campaigns_2016  \\\n",
       "0                              NaN                       NaN   \n",
       "\n",
       "   membership_dues_2016  fundraising_events_2016  related_organizations_2016  \\\n",
       "0                   NaN                      NaN                         NaN   \n",
       "\n",
       "   government_grants_2016 total_contributions_2016  \\\n",
       "0                     NaN                      NaN   \n",
       "\n",
       "   program_service_revenue_2016 total_primary_revenue_2016  \\\n",
       "0                           NaN                        NaN   \n",
       "\n",
       "   other_revenue_2016  total_revenue_2016  program_expenses_2016  \\\n",
       "0                 NaN                 NaN                    NaN   \n",
       "\n",
       "  administrative_expenses_2016 fundraising_expenses_2016  \\\n",
       "0                          NaN                       NaN   \n",
       "\n",
       "   total_functional_expenses_2016 payments_to_affiliates_2016  \\\n",
       "0                             NaN                         NaN   \n",
       "\n",
       "  excess_or_deficit_2016 net_assets_2016 comp_2016 cp_2016 mission_2016  \\\n",
       "0                    NaN             NaN       NaN     NaN          NaN   \n",
       "\n",
       "   2011_data charity_name_2011 category_2011 city_2011 state_2011 cause_2011  \\\n",
       "0        0.0               NaN           NaN       NaN        NaN        NaN   \n",
       "\n",
       "  tag_line_2011 url_2011 ein_2011 fye_2011  overall_rating_2011  \\\n",
       "0           NaN      NaN      NaN      NaN                  NaN   \n",
       "\n",
       "  overall_rating_2011_plus_30 overall_rating_2011_plus_30_v2  \\\n",
       "0                         NaN                            NaN   \n",
       "\n",
       "   overall_rating_star_2011 overall_rating_star_2011_text  \\\n",
       "0                       NaN                           NaN   \n",
       "\n",
       "   efficiency_rating_2011  AT_rating_2011  financial_rating_star_2011  \\\n",
       "0                     NaN             NaN                         NaN   \n",
       "\n",
       "   AT_rating_star_2011  program_expense_percent_2011  \\\n",
       "0                  NaN                           NaN   \n",
       "\n",
       "   admin_expense_percent_2011  fund_expense_percent_2011  \\\n",
       "0                         NaN                        NaN   \n",
       "\n",
       "   fund_efficiency_2011  primary_revenue_growth_2011  \\\n",
       "0                   NaN                          NaN   \n",
       "\n",
       "   program_expense_growth_2011  working_capital_ratio_2011  \\\n",
       "0                          NaN                         NaN   \n",
       "\n",
       "  independent_board_2011 no_material_division_2011 audited_financials_2011  \\\n",
       "0                    NaN                       NaN                     NaN   \n",
       "\n",
       "  no_loans_related_2011 documents_minutes_2011 form_990_2011  \\\n",
       "0                   NaN                    NaN           NaN   \n",
       "\n",
       "  conflict_of_interest_policy_2011 whistleblower_policy_2011  \\\n",
       "0                              NaN                       NaN   \n",
       "\n",
       "  records_retention_policy_2011 CEO_listed_2011 process_CEO_compensation_2011  \\\n",
       "0                           NaN             NaN                           NaN   \n",
       "\n",
       "  no_board_compensation_2011 donor_privacy_policy_2011 board_listed_2011  \\\n",
       "0                        NaN                       NaN               NaN   \n",
       "\n",
       "  audited_financials_web_2011 form_990_web_2011 staff_listed_2011  \\\n",
       "0                         NaN               NaN               NaN   \n",
       "\n",
       "   primary_revenue_2011  other_revenue_2011  total_revenue_2011  \\\n",
       "0                   NaN                 NaN                 NaN   \n",
       "\n",
       "  govt_revenue_2011  program_expense_2011  admin_expense_2011  \\\n",
       "0               NaN                   NaN                 NaN   \n",
       "\n",
       "   fund_expense_2011  total_functional_expense_2011  affiliate_payments_2011  \\\n",
       "0                NaN                            NaN                      NaN   \n",
       "\n",
       "   budget_surplus_2011  net_assets_2011  leader_comp_2011  \\\n",
       "0                  NaN              NaN               NaN   \n",
       "\n",
       "   leader_comp_percent_2011 email_2011 website_2011  \\\n",
       "0                       NaN        NaN          NaN   \n",
       "\n",
       "  2016 Advisory - Date Posted 2016 Advisory - Charity Name  \\\n",
       "0                         NaN                          NaN   \n",
       "\n",
       "  2016 Advisory - advisory_url 2016 Advisory - advisory  _merge_v1  \\\n",
       "0                          NaN                      NaN  left_only   \n",
       "\n",
       "   to_be_merged NEW ROW                          NAME_2015_BMF  \\\n",
       "0           0.0     NaN  PORTSMOUTH GIRLS SOFTBALL ASSOCIATION   \n",
       "\n",
       "  STREET_2015_BMF CITY_2015_BMF STATE_2015_BMF ZIP_2015_BMF  RULING_2015_BMF  \\\n",
       "0     PO BOX 8092    PORTSMOUTH             NH   03802-8092         201104.0   \n",
       "\n",
       "   ACTIVITY_2015_BMF  TAX_PERIOD_2015_BMF  ASSET_AMT_2015_BMF  \\\n",
       "0                0.0             201309.0                 0.0   \n",
       "\n",
       "   INCOME_AMT_2015_BMF  REVENUE_AMT_2015_BMF NTEE_CD_2015_BMF  2015 BMF  \\\n",
       "0                  0.0                   0.0              N63       1.0   \n",
       "\n",
       "   ruledate_2004_BMF name_MSTRALL state_MSTRALL NTEE1_MSTRALL nteecc_MSTRALL  \\\n",
       "0                NaN          NaN           NaN           NaN            NaN   \n",
       "\n",
       "  zip_MSTRALL fips_MSTRALL taxper_MSTRALL  income_MSTRALL  F990REV_MSTRALL  \\\n",
       "0         NaN          NaN            NaN             NaN              NaN   \n",
       "\n",
       "   assets_MSTRALL ruledate_MSTRALL deductcd_MSTRALL accper_MSTRALL  \\\n",
       "0             NaN              NaN              NaN            NaN   \n",
       "\n",
       "  rule_date_v1 taxpd NAME_SOI  yr_frmtn  pt1_num_vtng_gvrn_bdy_mems  \\\n",
       "0         2011   NaN      NaN       NaN                         NaN   \n",
       "\n",
       "   pt1_num_ind_vtng_mems  num_vtng_gvrn_bdy_mems  num_ind_vtng_mems  \\\n",
       "0                    NaN                     NaN                NaN   \n",
       "\n",
       "   tot_num_empls  tot_num_vlntrs  contri_grnts_cy  prog_srvc_rev_cy  \\\n",
       "0            NaN             NaN              NaN               NaN   \n",
       "\n",
       "   invst_incm_cy  oth_rev_cy  grnts_and_smlr_amts_cy  tot_prof_fndrsng_exp_cy  \\\n",
       "0            NaN         NaN                     NaN                      NaN   \n",
       "\n",
       "   tot_fndrsng_exp_cy  pt1_tot_asts_eoy aud_fincl_stmts mtrl_divrsn_or_misuse  \\\n",
       "0                 NaN               NaN             NaN                   NaN   \n",
       "\n",
       "  cnflct_int_plcy whistleblower_plcy doc_retention_plcy  federated_campaigns  \\\n",
       "0             NaN                NaN                NaN                  NaN   \n",
       "\n",
       "   memshp_dues  rltd_orgs  govt_grnts  all_oth_contri  nncsh_contri  \\\n",
       "0          NaN        NaN         NaN             NaN           NaN   \n",
       "\n",
       "   tot_contri  psr_tot  inv_incm_tot_rev  bonds_tot_rev  roylrev_tot_rev  \\\n",
       "0         NaN      NaN               NaN            NaN              NaN   \n",
       "\n",
       "   net_rent_tot_rev  gain_or_loss_sec  gain_or_loss_oth  oth_rev_tot  tot_rev  \\\n",
       "0               NaN               NaN               NaN          NaN      NaN   \n",
       "\n",
       "   mgmt_srvc_fee_tot  fee_for_srvc_leg_tot  fee_for_srvc_acct_tot  \\\n",
       "0                NaN                   NaN                    NaN   \n",
       "\n",
       "   fee_for_srvc_lbby_tot  fee_for_srvc_prof_tot  fee_for_srvc_invst_tot  \\\n",
       "0                    NaN                    NaN                     NaN   \n",
       "\n",
       "   fee_for_srvc_oth_tot fs_audited audit_committee  vlntr_hrs  _merge_v2  \\\n",
       "0                   NaN        NaN             NaN        NaN  left_only   \n",
       "\n",
       "  rule_date  ruledate_2004_BMF_v2 ruledate_MSTRALL_v2 yr_frmtn_v2  age  \\\n",
       "0      2011                   NaN                 NaN         nan  5.0   \n",
       "\n",
       "   category_Animals  category_Arts, Culture, Humanities  \\\n",
       "0               0.0                                 0.0   \n",
       "\n",
       "   category_Community Development  category_Education  category_Environment  \\\n",
       "0                             0.0                 0.0                   0.0   \n",
       "\n",
       "   category_Health  category_Human Services  category_Human and Civil Rights  \\\n",
       "0              0.0                      1.0                              0.0   \n",
       "\n",
       "   category_International  category_Religion  \\\n",
       "0                     0.0                0.0   \n",
       "\n",
       "   category_Research and Public Policy  govt_revenue_2011_binary  \\\n",
       "0                                  0.0                       NaN   \n",
       "\n",
       "   other_revenue_2011_binary  complexity_2011  advisory  SOX_policies_2011  \\\n",
       "0                        NaN              NaN       1.0                NaN   \n",
       "\n",
       "   total_revenue_2011_logged  total_revenue  total_revenue_logged  \\\n",
       "0                        NaN            NaN                   NaN   \n",
       "\n",
       "   program_efficiency_2016 state  tot_func_expns_prg_srvcs  \\\n",
       "0                      NaN    NH                       NaN   \n",
       "\n",
       "   tot_func_expns_tot  _merge_v3  program_expenses  total_expenses  \\\n",
       "0                 NaN  left_only               NaN             NaN   \n",
       "\n",
       "   program_efficiency  fndrsng_events  _merge_v4  other_revenue_SOI  \\\n",
       "0                 NaN             NaN  left_only                NaN   \n",
       "\n",
       "   complexity_2016  complexity_SOI  complexity conflict_of_interest_policy  \\\n",
       "0              0.0             0.0         0.0                         NaN   \n",
       "\n",
       "  whistleblower_policy records_retention_policy  \\\n",
       "0                  NaN                      NaN   \n",
       "\n",
       "   conflict_of_interest_policy_v2  records_retention_policy_v2  \\\n",
       "0                             NaN                          NaN   \n",
       "\n",
       "   whistleblower_policy_v2  SOX_policies  SOX_policies_binary  2016_data  \\\n",
       "0                      NaN           NaN                  NaN        1.0   \n",
       "\n",
       "                                                                                                                                                               Advisory Text  \\\n",
       "0  On August 1, 2016, the New Hampshire Union Leader published an article titled, \"Former Portsmouth youth softball president accused of stealing thousands from nonprofit.\"   \n",
       "\n",
       "   donor_advisory  donor_advisory_2016  donor_advisory_2011_to_2016  \\\n",
       "0             1.0                  1.0                          1.0   \n",
       "\n",
       "   SOX_policies_all_binary  total_revenue_no_neg EIN_47  \\\n",
       "0                      NaN                   NaN    NaN   \n",
       "\n",
       "   conflict_of_interest_policy_47  records_retention_policy_47  \\\n",
       "0                             NaN                          NaN   \n",
       "\n",
       "   whistleblower_policy_47  SOX_policies_47  SOX_policies_all_binary_47  \\\n",
       "0                      NaN              NaN                         NaN   \n",
       "\n",
       "   SOX_policies_binary_47  tot_rev_47  total_revenue_logged_47  \\\n",
       "0                     NaN         NaN                      NaN   \n",
       "\n",
       "   program_expenses_47  total_expenses_47  program_efficiency_47  \\\n",
       "0                  NaN                NaN                    NaN   \n",
       "\n",
       "   complexity_47  _merge_47 OrganizationName_efile URL_efile  \\\n",
       "0            NaN  left_only                    NaN       NaN   \n",
       "\n",
       "  SubmittedOn_efile  TaxPeriod_efile  whistleblower_policy_efile  \\\n",
       "0               NaN              NaN                         NaN   \n",
       "\n",
       "   conflict_of_interest_policy_efile  records_retention_policy_efile  \\\n",
       "0                                NaN                             NaN   \n",
       "\n",
       "   SOX_policies_efile  SOX_policies_binary_efile  \\\n",
       "0                 NaN                        NaN   \n",
       "\n",
       "   SOX_policies_all_binary_efile  tot_rev_efile  tot_rev_no_neg_efile  \\\n",
       "0                            NaN            NaN                   NaN   \n",
       "\n",
       "   total_revenue_logged_efile  program_expenses_efile  total_expenses_efile  \\\n",
       "0                         NaN                     NaN                   NaN   \n",
       "\n",
       "   program_efficiency_efile  complexity_efile _merge_efile  \n",
       "0                       NaN               NaN    left_only  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_pickle('Merged CN dataset with Age, State, Category, Total Revenues, Efficiency, Complexity, SOX, Donor Advisory (with added 990 data).pkl')\n",
    "print len(df.columns)\n",
    "print len(df)\n",
    "df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "'fund_expense_percent_2016', 'program_expense_growth_2016', 'independent_board_2016', 'audited_financials_2016',\n",
    "'no_loans_related_2016', 'contributions_gifts_grants_2016', 'total_contributions_2016',\n",
    "'fund_expense_percent_2011', 'fund_expense_2011', 'primary_revenue_growth_2011', 'program_expense_growth_2011',\n",
    "'independent_board_2011', 'audited_financials_2011', 'no_loans_related_2011',\n",
    "'pt1_num_vtng_gvrn_bdy_mems', 'pt1_num_ind_vtng_mems', 'num_vtng_gvrn_bdy_mems', 'num_ind_vtng_mems',\n",
    "'contri_grnts_cy', 'tot_fndrsng_exp_cy', 'aud_fincl_stmts', 'fs_audited', 'audit_committee',"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "85401\n",
      "85401 \n",
      "\n",
      "FY2014     15155\n",
      "FY2013      8671\n",
      "FY2009      7865\n",
      "FY2012      7792\n",
      "FY2010      7145\n",
      "FY2011      6879\n",
      "FY2008      4770\n",
      "FY2007      4441\n",
      "FY2006      4315\n",
      "FY2005      4117\n",
      "FY2004      3887\n",
      "FY2003      3178\n",
      "FY2015      2386\n",
      "FY2002      2190\n",
      "FY2001      1698\n",
      "FY2000       591\n",
      "current      321\n",
      "Name: FYE, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print len(df)\n",
    "print df['FYE'].value_counts().sum(), '\\n'\n",
    "print df['FYE'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Limit DF to 2016"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    75593\n",
       "True      8304\n",
       "False       48\n",
       "Name: latest_entry, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['latest_entry'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8304"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df[df['latest_entry']=='True']\n",
    "len(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge in data for missing orgs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "152\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EIN</th>\n",
       "      <th>FYE</th>\n",
       "      <th>audit_committee</th>\n",
       "      <th>board_size</th>\n",
       "      <th>independent_members</th>\n",
       "      <th>audited_financials</th>\n",
       "      <th>fundraising_exp</th>\n",
       "      <th>low_quality_reporting</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>030498214</td>\n",
       "      <td>FY2012</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>140183</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>042129889</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         EIN     FYE  audit_committee board_size independent_members  \\\n",
       "0  030498214  FY2012              0.0          6                   6   \n",
       "1  042129889  FY2014              1.0         23                  19   \n",
       "\n",
       "   audited_financials fundraising_exp  low_quality_reporting  \n",
       "0                   0          140183                      0  \n",
       "1                   1               0                      1  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_advisories = pd.read_pickle('Additional Variables for Robustness Tests - 152 advisory orgs.pkl')\n",
    "print len(df_advisories)\n",
    "df_advisories[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8304\n",
      "8304\n",
      "left_only     8152\n",
      "both           152\n",
      "right_only       0\n",
      "Name: _merge, dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>org_id</th>\n",
       "      <th>EIN</th>\n",
       "      <th>org_url</th>\n",
       "      <th>name</th>\n",
       "      <th>category</th>\n",
       "      <th>category-full</th>\n",
       "      <th>Date Published</th>\n",
       "      <th>Form 990 FYE</th>\n",
       "      <th>Form 990 FYE, v2</th>\n",
       "      <th>FYE_x</th>\n",
       "      <th>Earliest Rating Publication Date</th>\n",
       "      <th>ratings_system</th>\n",
       "      <th>Overall Score</th>\n",
       "      <th>Overall Rating</th>\n",
       "      <th>advisory text - current advisory</th>\n",
       "      <th>advisory text - past advisory</th>\n",
       "      <th>current_or_past_donor_advisory</th>\n",
       "      <th>current_donor_advisory</th>\n",
       "      <th>past_donor_advisory</th>\n",
       "      <th>latest_entry</th>\n",
       "      <th>current_ratings_url</th>\n",
       "      <th>ein_2016</th>\n",
       "      <th>Publication_date_and_FY_2016</th>\n",
       "      <th>Publication Date_2016</th>\n",
       "      <th>FYE_2016</th>\n",
       "      <th>donor_alert_2016</th>\n",
       "      <th>overall_rating_2016</th>\n",
       "      <th>efficiency_rating_rating_2016</th>\n",
       "      <th>AT_rating_2016</th>\n",
       "      <th>overall_rating_star_2016</th>\n",
       "      <th>financial_rating_star_2016</th>\n",
       "      <th>AT_rating_star_2016</th>\n",
       "      <th>program_expense_percent_2016</th>\n",
       "      <th>admin_expense_percent_2016</th>\n",
       "      <th>fund_expense_percent_2016</th>\n",
       "      <th>fund_efficiency_2016</th>\n",
       "      <th>working_capital_ratio_2016</th>\n",
       "      <th>program_expense_growth_2016</th>\n",
       "      <th>liabilities_to_assets_2016</th>\n",
       "      <th>independent_board_2016</th>\n",
       "      <th>no_material_division_2016</th>\n",
       "      <th>audited_financials_2016</th>\n",
       "      <th>no_loans_related_2016</th>\n",
       "      <th>documents_minutes_2016</th>\n",
       "      <th>form_990_2016</th>\n",
       "      <th>conflict_of_interest_policy_2016</th>\n",
       "      <th>whistleblower_policy_2016</th>\n",
       "      <th>records_retention_policy_2016</th>\n",
       "      <th>CEO_listed_2016</th>\n",
       "      <th>process_CEO_compensation_2016</th>\n",
       "      <th>no_board_compensation_2016</th>\n",
       "      <th>donor_privacy_policy_2016</th>\n",
       "      <th>board_listed_2016</th>\n",
       "      <th>audited_financials_web_2016</th>\n",
       "      <th>form_990_web_2016</th>\n",
       "      <th>staff_listed_2016</th>\n",
       "      <th>contributions_gifts_grants_2016</th>\n",
       "      <th>federated_campaigns_2016</th>\n",
       "      <th>membership_dues_2016</th>\n",
       "      <th>fundraising_events_2016</th>\n",
       "      <th>related_organizations_2016</th>\n",
       "      <th>government_grants_2016</th>\n",
       "      <th>total_contributions_2016</th>\n",
       "      <th>program_service_revenue_2016</th>\n",
       "      <th>total_primary_revenue_2016</th>\n",
       "      <th>other_revenue_2016</th>\n",
       "      <th>total_revenue_2016</th>\n",
       "      <th>program_expenses_2016</th>\n",
       "      <th>administrative_expenses_2016</th>\n",
       "      <th>fundraising_expenses_2016</th>\n",
       "      <th>total_functional_expenses_2016</th>\n",
       "      <th>payments_to_affiliates_2016</th>\n",
       "      <th>excess_or_deficit_2016</th>\n",
       "      <th>net_assets_2016</th>\n",
       "      <th>comp_2016</th>\n",
       "      <th>cp_2016</th>\n",
       "      <th>mission_2016</th>\n",
       "      <th>2011_data</th>\n",
       "      <th>charity_name_2011</th>\n",
       "      <th>category_2011</th>\n",
       "      <th>city_2011</th>\n",
       "      <th>state_2011</th>\n",
       "      <th>cause_2011</th>\n",
       "      <th>tag_line_2011</th>\n",
       "      <th>url_2011</th>\n",
       "      <th>ein_2011</th>\n",
       "      <th>fye_2011</th>\n",
       "      <th>overall_rating_2011</th>\n",
       "      <th>overall_rating_2011_plus_30</th>\n",
       "      <th>overall_rating_2011_plus_30_v2</th>\n",
       "      <th>overall_rating_star_2011</th>\n",
       "      <th>overall_rating_star_2011_text</th>\n",
       "      <th>efficiency_rating_2011</th>\n",
       "      <th>AT_rating_2011</th>\n",
       "      <th>financial_rating_star_2011</th>\n",
       "      <th>AT_rating_star_2011</th>\n",
       "      <th>program_expense_percent_2011</th>\n",
       "      <th>admin_expense_percent_2011</th>\n",
       "      <th>fund_expense_percent_2011</th>\n",
       "      <th>fund_efficiency_2011</th>\n",
       "      <th>primary_revenue_growth_2011</th>\n",
       "      <th>program_expense_growth_2011</th>\n",
       "      <th>working_capital_ratio_2011</th>\n",
       "      <th>independent_board_2011</th>\n",
       "      <th>no_material_division_2011</th>\n",
       "      <th>audited_financials_2011</th>\n",
       "      <th>no_loans_related_2011</th>\n",
       "      <th>documents_minutes_2011</th>\n",
       "      <th>form_990_2011</th>\n",
       "      <th>conflict_of_interest_policy_2011</th>\n",
       "      <th>whistleblower_policy_2011</th>\n",
       "      <th>records_retention_policy_2011</th>\n",
       "      <th>CEO_listed_2011</th>\n",
       "      <th>process_CEO_compensation_2011</th>\n",
       "      <th>no_board_compensation_2011</th>\n",
       "      <th>donor_privacy_policy_2011</th>\n",
       "      <th>board_listed_2011</th>\n",
       "      <th>audited_financials_web_2011</th>\n",
       "      <th>form_990_web_2011</th>\n",
       "      <th>staff_listed_2011</th>\n",
       "      <th>primary_revenue_2011</th>\n",
       "      <th>other_revenue_2011</th>\n",
       "      <th>total_revenue_2011</th>\n",
       "      <th>govt_revenue_2011</th>\n",
       "      <th>program_expense_2011</th>\n",
       "      <th>admin_expense_2011</th>\n",
       "      <th>fund_expense_2011</th>\n",
       "      <th>total_functional_expense_2011</th>\n",
       "      <th>affiliate_payments_2011</th>\n",
       "      <th>budget_surplus_2011</th>\n",
       "      <th>net_assets_2011</th>\n",
       "      <th>leader_comp_2011</th>\n",
       "      <th>leader_comp_percent_2011</th>\n",
       "      <th>email_2011</th>\n",
       "      <th>website_2011</th>\n",
       "      <th>2016 Advisory - Date Posted</th>\n",
       "      <th>2016 Advisory - Charity Name</th>\n",
       "      <th>2016 Advisory - advisory_url</th>\n",
       "      <th>2016 Advisory - advisory</th>\n",
       "      <th>_merge_v1</th>\n",
       "      <th>to_be_merged</th>\n",
       "      <th>NEW ROW</th>\n",
       "      <th>NAME_2015_BMF</th>\n",
       "      <th>STREET_2015_BMF</th>\n",
       "      <th>CITY_2015_BMF</th>\n",
       "      <th>STATE_2015_BMF</th>\n",
       "      <th>ZIP_2015_BMF</th>\n",
       "      <th>RULING_2015_BMF</th>\n",
       "      <th>ACTIVITY_2015_BMF</th>\n",
       "      <th>TAX_PERIOD_2015_BMF</th>\n",
       "      <th>ASSET_AMT_2015_BMF</th>\n",
       "      <th>INCOME_AMT_2015_BMF</th>\n",
       "      <th>REVENUE_AMT_2015_BMF</th>\n",
       "      <th>NTEE_CD_2015_BMF</th>\n",
       "      <th>2015 BMF</th>\n",
       "      <th>ruledate_2004_BMF</th>\n",
       "      <th>name_MSTRALL</th>\n",
       "      <th>state_MSTRALL</th>\n",
       "      <th>NTEE1_MSTRALL</th>\n",
       "      <th>nteecc_MSTRALL</th>\n",
       "      <th>zip_MSTRALL</th>\n",
       "      <th>fips_MSTRALL</th>\n",
       "      <th>taxper_MSTRALL</th>\n",
       "      <th>income_MSTRALL</th>\n",
       "      <th>F990REV_MSTRALL</th>\n",
       "      <th>assets_MSTRALL</th>\n",
       "      <th>ruledate_MSTRALL</th>\n",
       "      <th>deductcd_MSTRALL</th>\n",
       "      <th>accper_MSTRALL</th>\n",
       "      <th>rule_date_v1</th>\n",
       "      <th>taxpd</th>\n",
       "      <th>NAME_SOI</th>\n",
       "      <th>yr_frmtn</th>\n",
       "      <th>pt1_num_vtng_gvrn_bdy_mems</th>\n",
       "      <th>pt1_num_ind_vtng_mems</th>\n",
       "      <th>num_vtng_gvrn_bdy_mems</th>\n",
       "      <th>num_ind_vtng_mems</th>\n",
       "      <th>tot_num_empls</th>\n",
       "      <th>tot_num_vlntrs</th>\n",
       "      <th>contri_grnts_cy</th>\n",
       "      <th>prog_srvc_rev_cy</th>\n",
       "      <th>invst_incm_cy</th>\n",
       "      <th>oth_rev_cy</th>\n",
       "      <th>grnts_and_smlr_amts_cy</th>\n",
       "      <th>tot_prof_fndrsng_exp_cy</th>\n",
       "      <th>tot_fndrsng_exp_cy</th>\n",
       "      <th>pt1_tot_asts_eoy</th>\n",
       "      <th>aud_fincl_stmts</th>\n",
       "      <th>mtrl_divrsn_or_misuse</th>\n",
       "      <th>cnflct_int_plcy</th>\n",
       "      <th>whistleblower_plcy</th>\n",
       "      <th>doc_retention_plcy</th>\n",
       "      <th>federated_campaigns</th>\n",
       "      <th>memshp_dues</th>\n",
       "      <th>rltd_orgs</th>\n",
       "      <th>govt_grnts</th>\n",
       "      <th>all_oth_contri</th>\n",
       "      <th>nncsh_contri</th>\n",
       "      <th>tot_contri</th>\n",
       "      <th>psr_tot</th>\n",
       "      <th>inv_incm_tot_rev</th>\n",
       "      <th>bonds_tot_rev</th>\n",
       "      <th>roylrev_tot_rev</th>\n",
       "      <th>net_rent_tot_rev</th>\n",
       "      <th>gain_or_loss_sec</th>\n",
       "      <th>gain_or_loss_oth</th>\n",
       "      <th>oth_rev_tot</th>\n",
       "      <th>tot_rev</th>\n",
       "      <th>mgmt_srvc_fee_tot</th>\n",
       "      <th>fee_for_srvc_leg_tot</th>\n",
       "      <th>fee_for_srvc_acct_tot</th>\n",
       "      <th>fee_for_srvc_lbby_tot</th>\n",
       "      <th>fee_for_srvc_prof_tot</th>\n",
       "      <th>fee_for_srvc_invst_tot</th>\n",
       "      <th>fee_for_srvc_oth_tot</th>\n",
       "      <th>fs_audited</th>\n",
       "      <th>audit_committee_x</th>\n",
       "      <th>vlntr_hrs</th>\n",
       "      <th>_merge_v2</th>\n",
       "      <th>rule_date</th>\n",
       "      <th>ruledate_2004_BMF_v2</th>\n",
       "      <th>ruledate_MSTRALL_v2</th>\n",
       "      <th>yr_frmtn_v2</th>\n",
       "      <th>age</th>\n",
       "      <th>category_Animals</th>\n",
       "      <th>category_Arts, Culture, Humanities</th>\n",
       "      <th>category_Community Development</th>\n",
       "      <th>category_Education</th>\n",
       "      <th>category_Environment</th>\n",
       "      <th>category_Health</th>\n",
       "      <th>category_Human Services</th>\n",
       "      <th>category_Human and Civil Rights</th>\n",
       "      <th>category_International</th>\n",
       "      <th>category_Religion</th>\n",
       "      <th>category_Research and Public Policy</th>\n",
       "      <th>govt_revenue_2011_binary</th>\n",
       "      <th>other_revenue_2011_binary</th>\n",
       "      <th>complexity_2011</th>\n",
       "      <th>advisory</th>\n",
       "      <th>SOX_policies_2011</th>\n",
       "      <th>total_revenue_2011_logged</th>\n",
       "      <th>total_revenue</th>\n",
       "      <th>total_revenue_logged</th>\n",
       "      <th>program_efficiency_2016</th>\n",
       "      <th>state</th>\n",
       "      <th>tot_func_expns_prg_srvcs</th>\n",
       "      <th>tot_func_expns_tot</th>\n",
       "      <th>_merge_v3</th>\n",
       "      <th>program_expenses</th>\n",
       "      <th>total_expenses</th>\n",
       "      <th>program_efficiency</th>\n",
       "      <th>fndrsng_events</th>\n",
       "      <th>_merge_v4</th>\n",
       "      <th>other_revenue_SOI</th>\n",
       "      <th>complexity_2016</th>\n",
       "      <th>complexity_SOI</th>\n",
       "      <th>complexity</th>\n",
       "      <th>conflict_of_interest_policy</th>\n",
       "      <th>whistleblower_policy</th>\n",
       "      <th>records_retention_policy</th>\n",
       "      <th>conflict_of_interest_policy_v2</th>\n",
       "      <th>records_retention_policy_v2</th>\n",
       "      <th>whistleblower_policy_v2</th>\n",
       "      <th>SOX_policies</th>\n",
       "      <th>SOX_policies_binary</th>\n",
       "      <th>2016_data</th>\n",
       "      <th>Advisory Text</th>\n",
       "      <th>donor_advisory</th>\n",
       "      <th>donor_advisory_2016</th>\n",
       "      <th>donor_advisory_2011_to_2016</th>\n",
       "      <th>SOX_policies_all_binary</th>\n",
       "      <th>total_revenue_no_neg</th>\n",
       "      <th>EIN_47</th>\n",
       "      <th>conflict_of_interest_policy_47</th>\n",
       "      <th>records_retention_policy_47</th>\n",
       "      <th>whistleblower_policy_47</th>\n",
       "      <th>SOX_policies_47</th>\n",
       "      <th>SOX_policies_all_binary_47</th>\n",
       "      <th>SOX_policies_binary_47</th>\n",
       "      <th>tot_rev_47</th>\n",
       "      <th>total_revenue_logged_47</th>\n",
       "      <th>program_expenses_47</th>\n",
       "      <th>total_expenses_47</th>\n",
       "      <th>program_efficiency_47</th>\n",
       "      <th>complexity_47</th>\n",
       "      <th>_merge_47</th>\n",
       "      <th>OrganizationName_efile</th>\n",
       "      <th>URL_efile</th>\n",
       "      <th>SubmittedOn_efile</th>\n",
       "      <th>TaxPeriod_efile</th>\n",
       "      <th>whistleblower_policy_efile</th>\n",
       "      <th>conflict_of_interest_policy_efile</th>\n",
       "      <th>records_retention_policy_efile</th>\n",
       "      <th>SOX_policies_efile</th>\n",
       "      <th>SOX_policies_binary_efile</th>\n",
       "      <th>SOX_policies_all_binary_efile</th>\n",
       "      <th>tot_rev_efile</th>\n",
       "      <th>tot_rev_no_neg_efile</th>\n",
       "      <th>total_revenue_logged_efile</th>\n",
       "      <th>program_expenses_efile</th>\n",
       "      <th>total_expenses_efile</th>\n",
       "      <th>program_efficiency_efile</th>\n",
       "      <th>complexity_efile</th>\n",
       "      <th>_merge_efile</th>\n",
       "      <th>FYE_y</th>\n",
       "      <th>audit_committee_y</th>\n",
       "      <th>board_size</th>\n",
       "      <th>independent_members</th>\n",
       "      <th>audited_financials</th>\n",
       "      <th>fundraising_exp</th>\n",
       "      <th>low_quality_reporting</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16722</td>\n",
       "      <td>020503776</td>\n",
       "      <td>http://www.charitynavigator.org/index.cfm?bay=search.summary&amp;orgid=16722</td>\n",
       "      <td>Portsmouth Girls Softball Association</td>\n",
       "      <td>Human Services</td>\n",
       "      <td>Human Services : Multipurpose Human Service Organizations</td>\n",
       "      <td>2016-08-12 00:00:00</td>\n",
       "      <td>current</td>\n",
       "      <td>2015-01-01</td>\n",
       "      <td>current</td>\n",
       "      <td>NaN</td>\n",
       "      <td>current</td>\n",
       "      <td>NaN</td>\n",
       "      <td>current (2016) donor advisory</td>\n",
       "      <td>On August 1, 2016, the New Hampshire Union Leader published an article titled, \"Former Portsmouth youth softball president accused of stealing thousands from nonprofit.\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "      <td>http://www.charitynavigator.org/index.cfm?bay=search.summary&amp;orgid=16722</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>current</td>\n",
       "      <td>current donor advisory 2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PORTSMOUTH GIRLS SOFTBALL ASSOCIATION</td>\n",
       "      <td>PO BOX 8092</td>\n",
       "      <td>PORTSMOUTH</td>\n",
       "      <td>NH</td>\n",
       "      <td>03802-8092</td>\n",
       "      <td>201104.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>201309.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>N63</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2011</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "      <td>2011</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>On August 1, 2016, the New Hampshire Union Leader published an article titled, \"Former Portsmouth youth softball president accused of stealing thousands from nonprofit.\"</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  org_id        EIN  \\\n",
       "0  16722  020503776   \n",
       "\n",
       "                                                                    org_url  \\\n",
       "0  http://www.charitynavigator.org/index.cfm?bay=search.summary&orgid=16722   \n",
       "\n",
       "                                    name        category  \\\n",
       "0  Portsmouth Girls Softball Association  Human Services   \n",
       "\n",
       "                                               category-full  \\\n",
       "0  Human Services : Multipurpose Human Service Organizations   \n",
       "\n",
       "        Date Published Form 990 FYE Form 990 FYE, v2    FYE_x  \\\n",
       "0  2016-08-12 00:00:00      current       2015-01-01  current   \n",
       "\n",
       "  Earliest Rating Publication Date ratings_system Overall Score  \\\n",
       "0                              NaN        current           NaN   \n",
       "\n",
       "                  Overall Rating  \\\n",
       "0  current (2016) donor advisory   \n",
       "\n",
       "                                                                                                                                            advisory text - current advisory  \\\n",
       "0  On August 1, 2016, the New Hampshire Union Leader published an article titled, \"Former Portsmouth youth softball president accused of stealing thousands from nonprofit.\"   \n",
       "\n",
       "  advisory text - past advisory  current_or_past_donor_advisory  \\\n",
       "0                           NaN                             1.0   \n",
       "\n",
       "   current_donor_advisory  past_donor_advisory latest_entry  \\\n",
       "0                     1.0                  0.0         True   \n",
       "\n",
       "                                                        current_ratings_url  \\\n",
       "0  http://www.charitynavigator.org/index.cfm?bay=search.summary&orgid=16722   \n",
       "\n",
       "  ein_2016 Publication_date_and_FY_2016 Publication Date_2016 FYE_2016  \\\n",
       "0      NaN                          NaN                   NaN  current   \n",
       "\n",
       "              donor_alert_2016 overall_rating_2016  \\\n",
       "0  current donor advisory 2016                 NaN   \n",
       "\n",
       "  efficiency_rating_rating_2016 AT_rating_2016 overall_rating_star_2016  \\\n",
       "0                           NaN            NaN                      NaN   \n",
       "\n",
       "  financial_rating_star_2016 AT_rating_star_2016 program_expense_percent_2016  \\\n",
       "0                        NaN                 NaN                          NaN   \n",
       "\n",
       "  admin_expense_percent_2016 fund_expense_percent_2016 fund_efficiency_2016  \\\n",
       "0                        NaN                       NaN                  NaN   \n",
       "\n",
       "  working_capital_ratio_2016 program_expense_growth_2016  \\\n",
       "0                        NaN                         NaN   \n",
       "\n",
       "  liabilities_to_assets_2016 independent_board_2016 no_material_division_2016  \\\n",
       "0                        NaN                    NaN                       NaN   \n",
       "\n",
       "  audited_financials_2016 no_loans_related_2016 documents_minutes_2016  \\\n",
       "0                     NaN                   NaN                    NaN   \n",
       "\n",
       "  form_990_2016 conflict_of_interest_policy_2016 whistleblower_policy_2016  \\\n",
       "0           NaN                              NaN                       NaN   \n",
       "\n",
       "  records_retention_policy_2016 CEO_listed_2016 process_CEO_compensation_2016  \\\n",
       "0                           NaN             NaN                           NaN   \n",
       "\n",
       "  no_board_compensation_2016 donor_privacy_policy_2016 board_listed_2016  \\\n",
       "0                        NaN                       NaN               NaN   \n",
       "\n",
       "  audited_financials_web_2016 form_990_web_2016 staff_listed_2016  \\\n",
       "0                         NaN               NaN               NaN   \n",
       "\n",
       "   contributions_gifts_grants_2016  federated_campaigns_2016  \\\n",
       "0                              NaN                       NaN   \n",
       "\n",
       "   membership_dues_2016  fundraising_events_2016  related_organizations_2016  \\\n",
       "0                   NaN                      NaN                         NaN   \n",
       "\n",
       "   government_grants_2016 total_contributions_2016  \\\n",
       "0                     NaN                      NaN   \n",
       "\n",
       "   program_service_revenue_2016 total_primary_revenue_2016  \\\n",
       "0                           NaN                        NaN   \n",
       "\n",
       "   other_revenue_2016  total_revenue_2016  program_expenses_2016  \\\n",
       "0                 NaN                 NaN                    NaN   \n",
       "\n",
       "  administrative_expenses_2016 fundraising_expenses_2016  \\\n",
       "0                          NaN                       NaN   \n",
       "\n",
       "   total_functional_expenses_2016 payments_to_affiliates_2016  \\\n",
       "0                             NaN                         NaN   \n",
       "\n",
       "  excess_or_deficit_2016 net_assets_2016 comp_2016 cp_2016 mission_2016  \\\n",
       "0                    NaN             NaN       NaN     NaN          NaN   \n",
       "\n",
       "   2011_data charity_name_2011 category_2011 city_2011 state_2011 cause_2011  \\\n",
       "0        0.0               NaN           NaN       NaN        NaN        NaN   \n",
       "\n",
       "  tag_line_2011 url_2011 ein_2011 fye_2011  overall_rating_2011  \\\n",
       "0           NaN      NaN      NaN      NaN                  NaN   \n",
       "\n",
       "  overall_rating_2011_plus_30 overall_rating_2011_plus_30_v2  \\\n",
       "0                         NaN                            NaN   \n",
       "\n",
       "   overall_rating_star_2011 overall_rating_star_2011_text  \\\n",
       "0                       NaN                           NaN   \n",
       "\n",
       "   efficiency_rating_2011  AT_rating_2011  financial_rating_star_2011  \\\n",
       "0                     NaN             NaN                         NaN   \n",
       "\n",
       "   AT_rating_star_2011  program_expense_percent_2011  \\\n",
       "0                  NaN                           NaN   \n",
       "\n",
       "   admin_expense_percent_2011  fund_expense_percent_2011  \\\n",
       "0                         NaN                        NaN   \n",
       "\n",
       "   fund_efficiency_2011  primary_revenue_growth_2011  \\\n",
       "0                   NaN                          NaN   \n",
       "\n",
       "   program_expense_growth_2011  working_capital_ratio_2011  \\\n",
       "0                          NaN                         NaN   \n",
       "\n",
       "  independent_board_2011 no_material_division_2011 audited_financials_2011  \\\n",
       "0                    NaN                       NaN                     NaN   \n",
       "\n",
       "  no_loans_related_2011 documents_minutes_2011 form_990_2011  \\\n",
       "0                   NaN                    NaN           NaN   \n",
       "\n",
       "  conflict_of_interest_policy_2011 whistleblower_policy_2011  \\\n",
       "0                              NaN                       NaN   \n",
       "\n",
       "  records_retention_policy_2011 CEO_listed_2011 process_CEO_compensation_2011  \\\n",
       "0                           NaN             NaN                           NaN   \n",
       "\n",
       "  no_board_compensation_2011 donor_privacy_policy_2011 board_listed_2011  \\\n",
       "0                        NaN                       NaN               NaN   \n",
       "\n",
       "  audited_financials_web_2011 form_990_web_2011 staff_listed_2011  \\\n",
       "0                         NaN               NaN               NaN   \n",
       "\n",
       "   primary_revenue_2011  other_revenue_2011  total_revenue_2011  \\\n",
       "0                   NaN                 NaN                 NaN   \n",
       "\n",
       "  govt_revenue_2011  program_expense_2011  admin_expense_2011  \\\n",
       "0               NaN                   NaN                 NaN   \n",
       "\n",
       "   fund_expense_2011  total_functional_expense_2011  affiliate_payments_2011  \\\n",
       "0                NaN                            NaN                      NaN   \n",
       "\n",
       "   budget_surplus_2011  net_assets_2011  leader_comp_2011  \\\n",
       "0                  NaN              NaN               NaN   \n",
       "\n",
       "   leader_comp_percent_2011 email_2011 website_2011  \\\n",
       "0                       NaN        NaN          NaN   \n",
       "\n",
       "  2016 Advisory - Date Posted 2016 Advisory - Charity Name  \\\n",
       "0                         NaN                          NaN   \n",
       "\n",
       "  2016 Advisory - advisory_url 2016 Advisory - advisory  _merge_v1  \\\n",
       "0                          NaN                      NaN  left_only   \n",
       "\n",
       "   to_be_merged NEW ROW                          NAME_2015_BMF  \\\n",
       "0           0.0     NaN  PORTSMOUTH GIRLS SOFTBALL ASSOCIATION   \n",
       "\n",
       "  STREET_2015_BMF CITY_2015_BMF STATE_2015_BMF ZIP_2015_BMF  RULING_2015_BMF  \\\n",
       "0     PO BOX 8092    PORTSMOUTH             NH   03802-8092         201104.0   \n",
       "\n",
       "   ACTIVITY_2015_BMF  TAX_PERIOD_2015_BMF  ASSET_AMT_2015_BMF  \\\n",
       "0                0.0             201309.0                 0.0   \n",
       "\n",
       "   INCOME_AMT_2015_BMF  REVENUE_AMT_2015_BMF NTEE_CD_2015_BMF  2015 BMF  \\\n",
       "0                  0.0                   0.0              N63       1.0   \n",
       "\n",
       "   ruledate_2004_BMF name_MSTRALL state_MSTRALL NTEE1_MSTRALL nteecc_MSTRALL  \\\n",
       "0                NaN          NaN           NaN           NaN            NaN   \n",
       "\n",
       "  zip_MSTRALL fips_MSTRALL taxper_MSTRALL  income_MSTRALL  F990REV_MSTRALL  \\\n",
       "0         NaN          NaN            NaN             NaN              NaN   \n",
       "\n",
       "   assets_MSTRALL ruledate_MSTRALL deductcd_MSTRALL accper_MSTRALL  \\\n",
       "0             NaN              NaN              NaN            NaN   \n",
       "\n",
       "  rule_date_v1 taxpd NAME_SOI  yr_frmtn  pt1_num_vtng_gvrn_bdy_mems  \\\n",
       "0         2011   NaN      NaN       NaN                         NaN   \n",
       "\n",
       "   pt1_num_ind_vtng_mems  num_vtng_gvrn_bdy_mems  num_ind_vtng_mems  \\\n",
       "0                    NaN                     NaN                NaN   \n",
       "\n",
       "   tot_num_empls  tot_num_vlntrs  contri_grnts_cy  prog_srvc_rev_cy  \\\n",
       "0            NaN             NaN              NaN               NaN   \n",
       "\n",
       "   invst_incm_cy  oth_rev_cy  grnts_and_smlr_amts_cy  tot_prof_fndrsng_exp_cy  \\\n",
       "0            NaN         NaN                     NaN                      NaN   \n",
       "\n",
       "   tot_fndrsng_exp_cy  pt1_tot_asts_eoy aud_fincl_stmts mtrl_divrsn_or_misuse  \\\n",
       "0                 NaN               NaN             NaN                   NaN   \n",
       "\n",
       "  cnflct_int_plcy whistleblower_plcy doc_retention_plcy  federated_campaigns  \\\n",
       "0             NaN                NaN                NaN                  NaN   \n",
       "\n",
       "   memshp_dues  rltd_orgs  govt_grnts  all_oth_contri  nncsh_contri  \\\n",
       "0          NaN        NaN         NaN             NaN           NaN   \n",
       "\n",
       "   tot_contri  psr_tot  inv_incm_tot_rev  bonds_tot_rev  roylrev_tot_rev  \\\n",
       "0         NaN      NaN               NaN            NaN              NaN   \n",
       "\n",
       "   net_rent_tot_rev  gain_or_loss_sec  gain_or_loss_oth  oth_rev_tot  tot_rev  \\\n",
       "0               NaN               NaN               NaN          NaN      NaN   \n",
       "\n",
       "   mgmt_srvc_fee_tot  fee_for_srvc_leg_tot  fee_for_srvc_acct_tot  \\\n",
       "0                NaN                   NaN                    NaN   \n",
       "\n",
       "   fee_for_srvc_lbby_tot  fee_for_srvc_prof_tot  fee_for_srvc_invst_tot  \\\n",
       "0                    NaN                    NaN                     NaN   \n",
       "\n",
       "   fee_for_srvc_oth_tot fs_audited audit_committee_x  vlntr_hrs  _merge_v2  \\\n",
       "0                   NaN        NaN               NaN        NaN  left_only   \n",
       "\n",
       "  rule_date  ruledate_2004_BMF_v2 ruledate_MSTRALL_v2 yr_frmtn_v2  age  \\\n",
       "0      2011                   NaN                 NaN         nan  5.0   \n",
       "\n",
       "   category_Animals  category_Arts, Culture, Humanities  \\\n",
       "0               0.0                                 0.0   \n",
       "\n",
       "   category_Community Development  category_Education  category_Environment  \\\n",
       "0                             0.0                 0.0                   0.0   \n",
       "\n",
       "   category_Health  category_Human Services  category_Human and Civil Rights  \\\n",
       "0              0.0                      1.0                              0.0   \n",
       "\n",
       "   category_International  category_Religion  \\\n",
       "0                     0.0                0.0   \n",
       "\n",
       "   category_Research and Public Policy  govt_revenue_2011_binary  \\\n",
       "0                                  0.0                       NaN   \n",
       "\n",
       "   other_revenue_2011_binary  complexity_2011  advisory  SOX_policies_2011  \\\n",
       "0                        NaN              NaN       1.0                NaN   \n",
       "\n",
       "   total_revenue_2011_logged  total_revenue  total_revenue_logged  \\\n",
       "0                        NaN            NaN                   NaN   \n",
       "\n",
       "   program_efficiency_2016 state  tot_func_expns_prg_srvcs  \\\n",
       "0                      NaN    NH                       NaN   \n",
       "\n",
       "   tot_func_expns_tot  _merge_v3  program_expenses  total_expenses  \\\n",
       "0                 NaN  left_only               NaN             NaN   \n",
       "\n",
       "   program_efficiency  fndrsng_events  _merge_v4  other_revenue_SOI  \\\n",
       "0                 NaN             NaN  left_only                NaN   \n",
       "\n",
       "   complexity_2016  complexity_SOI  complexity conflict_of_interest_policy  \\\n",
       "0              0.0             0.0         0.0                         NaN   \n",
       "\n",
       "  whistleblower_policy records_retention_policy  \\\n",
       "0                  NaN                      NaN   \n",
       "\n",
       "   conflict_of_interest_policy_v2  records_retention_policy_v2  \\\n",
       "0                             NaN                          NaN   \n",
       "\n",
       "   whistleblower_policy_v2  SOX_policies  SOX_policies_binary  2016_data  \\\n",
       "0                      NaN           NaN                  NaN        1.0   \n",
       "\n",
       "                                                                                                                                                               Advisory Text  \\\n",
       "0  On August 1, 2016, the New Hampshire Union Leader published an article titled, \"Former Portsmouth youth softball president accused of stealing thousands from nonprofit.\"   \n",
       "\n",
       "   donor_advisory  donor_advisory_2016  donor_advisory_2011_to_2016  \\\n",
       "0             1.0                  1.0                          1.0   \n",
       "\n",
       "   SOX_policies_all_binary  total_revenue_no_neg EIN_47  \\\n",
       "0                      NaN                   NaN    NaN   \n",
       "\n",
       "   conflict_of_interest_policy_47  records_retention_policy_47  \\\n",
       "0                             NaN                          NaN   \n",
       "\n",
       "   whistleblower_policy_47  SOX_policies_47  SOX_policies_all_binary_47  \\\n",
       "0                      NaN              NaN                         NaN   \n",
       "\n",
       "   SOX_policies_binary_47  tot_rev_47  total_revenue_logged_47  \\\n",
       "0                     NaN         NaN                      NaN   \n",
       "\n",
       "   program_expenses_47  total_expenses_47  program_efficiency_47  \\\n",
       "0                  NaN                NaN                    NaN   \n",
       "\n",
       "   complexity_47  _merge_47 OrganizationName_efile URL_efile  \\\n",
       "0            NaN  left_only                    NaN       NaN   \n",
       "\n",
       "  SubmittedOn_efile  TaxPeriod_efile  whistleblower_policy_efile  \\\n",
       "0               NaN              NaN                         NaN   \n",
       "\n",
       "   conflict_of_interest_policy_efile  records_retention_policy_efile  \\\n",
       "0                                NaN                             NaN   \n",
       "\n",
       "   SOX_policies_efile  SOX_policies_binary_efile  \\\n",
       "0                 NaN                        NaN   \n",
       "\n",
       "   SOX_policies_all_binary_efile  tot_rev_efile  tot_rev_no_neg_efile  \\\n",
       "0                            NaN            NaN                   NaN   \n",
       "\n",
       "   total_revenue_logged_efile  program_expenses_efile  total_expenses_efile  \\\n",
       "0                         NaN                     NaN                   NaN   \n",
       "\n",
       "   program_efficiency_efile  complexity_efile _merge_efile FYE_y  \\\n",
       "0                       NaN               NaN    left_only   NaN   \n",
       "\n",
       "   audit_committee_y board_size independent_members  audited_financials  \\\n",
       "0                NaN        NaN                 NaN                 NaN   \n",
       "\n",
       "  fundraising_exp  low_quality_reporting     _merge  \n",
       "0             NaN                    NaN  left_only  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print len(pd.merge(df, df_advisories, left_on='EIN', right_on='EIN', how='left'))\n",
    "merged = pd.merge(df, df_advisories, left_on='EIN', right_on='EIN', how='left', indicator=True)\n",
    "print len(merged)\n",
    "print merged['_merge'].value_counts()\n",
    "merged[:1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>donor_advisory_2016</th>\n",
       "      <th>0.0</th>\n",
       "      <th>1.0</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_merge</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>left_only</th>\n",
       "      <td>7983</td>\n",
       "      <td>169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>right_only</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>both</th>\n",
       "      <td>0</td>\n",
       "      <td>152</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "donor_advisory_2016   0.0  1.0\n",
       "_merge                        \n",
       "left_only            7983  169\n",
       "right_only              0    0\n",
       "both                    0  152"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(merged['_merge'], merged['donor_advisory_2016'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "columns = ['org_id', 'EIN', 'FYE_x', \n",
    "           'fund_expense_percent_2016', 'independent_board_2016', #'program_expense_growth_2016', \n",
    "           'audited_financials_2016', #'no_loans_related_2016', 'contributions_gifts_grants_2016', \n",
    "           #'total_contributions_2016', #'fund_expense_percent_2011', 'fund_expense_2011', \n",
    "           #'primary_revenue_growth_2011', 'program_expense_growth_2011', 'independent_board_2011', \n",
    "           #'audited_financials_2011', 'no_loans_related_2011', \n",
    "           'pt1_num_vtng_gvrn_bdy_mems', 'pt1_num_ind_vtng_mems', \n",
    "           'num_vtng_gvrn_bdy_mems', 'num_ind_vtng_mems', 'contri_grnts_cy', 'tot_fndrsng_exp_cy', 'aud_fincl_stmts', \n",
    "           'fs_audited', 'audit_committee_x', 'FYE_y', 'audit_committee_y', 'board_size', \n",
    "           'independent_members', 'audited_financials', 'fundraising_exp', 'low_quality_reporting']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'merged' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-1-7786e17ed6e8>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mmerged\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m115\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;36m125\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m: name 'merged' is not defined"
     ]
    }
   ],
   "source": [
    "merged[columns][115:125]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Independent Board"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "merged['independent_members'] = merged['independent_members'].astype('float')\n",
    "merged['board_size'] = merged['board_size'].astype('float')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8152\n",
      "152\n"
     ]
    }
   ],
   "source": [
    "print len(merged[merged['independent_members'].isnull()])\n",
    "print len(merged[merged['independent_members'].notnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0    83\n",
       "0.0    69\n",
       "Name: independent_board, dtype: int64"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged['independent_board'] = np.nan\n",
    "merged['independent_board'] = np.where(( (merged['independent_members'].notnull()) \n",
    "                                & (merged['independent_members']>4)\n",
    "                                & (merged['independent_members']>(merged['board_size']/2))), \n",
    "                                       1, 0 ) #merged['independent_board'])\n",
    "merged['independent_board'] = np.where(merged['independent_members'].isnull(), np.nan, merged['independent_board']) \n",
    "merged['independent_board'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "columns = ['org_id', 'EIN', 'FYE_x', 'fund_expense_percent_2016', 'independent_board_2016', \n",
    "           'board_size', 'independent_members', 'independent_board',\n",
    "           'audited_financials_2016', 'pt1_num_vtng_gvrn_bdy_mems', 'pt1_num_ind_vtng_mems', \n",
    "           'num_vtng_gvrn_bdy_mems', 'num_ind_vtng_mems', 'contri_grnts_cy', 'tot_fndrsng_exp_cy', \n",
    "           'aud_fincl_stmts', 'fs_audited', 'audit_committee_x', 'FYE_y', 'audit_committee_y', 'audited_financials', \n",
    "           'fundraising_exp', 'low_quality_reporting']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>org_id</th>\n",
       "      <th>EIN</th>\n",
       "      <th>FYE_x</th>\n",
       "      <th>fund_expense_percent_2016</th>\n",
       "      <th>independent_board_2016</th>\n",
       "      <th>board_size</th>\n",
       "      <th>independent_members</th>\n",
       "      <th>independent_board</th>\n",
       "      <th>audited_financials_2016</th>\n",
       "      <th>pt1_num_vtng_gvrn_bdy_mems</th>\n",
       "      <th>pt1_num_ind_vtng_mems</th>\n",
       "      <th>num_vtng_gvrn_bdy_mems</th>\n",
       "      <th>num_ind_vtng_mems</th>\n",
       "      <th>contri_grnts_cy</th>\n",
       "      <th>tot_fndrsng_exp_cy</th>\n",
       "      <th>aud_fincl_stmts</th>\n",
       "      <th>fs_audited</th>\n",
       "      <th>audit_committee_x</th>\n",
       "      <th>FYE_y</th>\n",
       "      <th>audit_committee_y</th>\n",
       "      <th>audited_financials</th>\n",
       "      <th>fundraising_exp</th>\n",
       "      <th>low_quality_reporting</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5798</th>\n",
       "      <td>4292</td>\n",
       "      <td>941347046</td>\n",
       "      <td>current</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3146137</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5887</th>\n",
       "      <td>16234</td>\n",
       "      <td>451258469</td>\n",
       "      <td>current</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5917</th>\n",
       "      <td>16254</td>\n",
       "      <td>710987457</td>\n",
       "      <td>current</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5954</th>\n",
       "      <td>15512</td>\n",
       "      <td>450579664</td>\n",
       "      <td>current</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY2011</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>26827</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6059</th>\n",
       "      <td>16633</td>\n",
       "      <td>330807324</td>\n",
       "      <td>current</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY2012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6104</th>\n",
       "      <td>11327</td>\n",
       "      <td>720760857</td>\n",
       "      <td>current</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>63530</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6310</th>\n",
       "      <td>13735</td>\n",
       "      <td>141368361</td>\n",
       "      <td>current</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY2015</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6561</th>\n",
       "      <td>13601</td>\n",
       "      <td>742463670</td>\n",
       "      <td>current</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>28560</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6636</th>\n",
       "      <td>16139</td>\n",
       "      <td>510138358</td>\n",
       "      <td>current</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>33086</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6648</th>\n",
       "      <td>4441</td>\n",
       "      <td>042129889</td>\n",
       "      <td>current</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     org_id        EIN    FYE_x fund_expense_percent_2016  \\\n",
       "5798   4292  941347046  current                       NaN   \n",
       "5887  16234  451258469  current                       NaN   \n",
       "5917  16254  710987457  current                       NaN   \n",
       "5954  15512  450579664  current                       NaN   \n",
       "6059  16633  330807324  current                       NaN   \n",
       "6104  11327  720760857  current                       NaN   \n",
       "6310  13735  141368361  current                       NaN   \n",
       "6561  13601  742463670  current                       NaN   \n",
       "6636  16139  510138358  current                       NaN   \n",
       "6648   4441  042129889  current                       NaN   \n",
       "\n",
       "     independent_board_2016  board_size  independent_members  \\\n",
       "5798                    NaN        22.0                 19.0   \n",
       "5887                    NaN         4.0                  2.0   \n",
       "5917                    NaN         1.0                  0.0   \n",
       "5954                    NaN         2.0                  2.0   \n",
       "6059                    NaN        16.0                 16.0   \n",
       "6104                    NaN        30.0                 30.0   \n",
       "6310                    NaN        15.0                 12.0   \n",
       "6561                    NaN         6.0                  4.0   \n",
       "6636                    NaN        23.0                 23.0   \n",
       "6648                    NaN        23.0                 19.0   \n",
       "\n",
       "      independent_board audited_financials_2016  pt1_num_vtng_gvrn_bdy_mems  \\\n",
       "5798                  1                     NaN                         NaN   \n",
       "5887                  0                     NaN                         NaN   \n",
       "5917                  0                     NaN                         NaN   \n",
       "5954                  0                     NaN                         NaN   \n",
       "6059                  1                     NaN                         NaN   \n",
       "6104                  1                     NaN                         NaN   \n",
       "6310                  1                     NaN                         NaN   \n",
       "6561                  0                     NaN                         NaN   \n",
       "6636                  1                     NaN                         NaN   \n",
       "6648                  1                     NaN                         NaN   \n",
       "\n",
       "      pt1_num_ind_vtng_mems  num_vtng_gvrn_bdy_mems  num_ind_vtng_mems  \\\n",
       "5798                    NaN                     NaN                NaN   \n",
       "5887                    NaN                     NaN                NaN   \n",
       "5917                    NaN                     NaN                NaN   \n",
       "5954                    NaN                     NaN                NaN   \n",
       "6059                    NaN                     NaN                NaN   \n",
       "6104                    NaN                     NaN                NaN   \n",
       "6310                    NaN                     NaN                NaN   \n",
       "6561                    NaN                     NaN                NaN   \n",
       "6636                    NaN                     NaN                NaN   \n",
       "6648                    NaN                     NaN                NaN   \n",
       "\n",
       "      contri_grnts_cy  tot_fndrsng_exp_cy aud_fincl_stmts fs_audited  \\\n",
       "5798              NaN                 NaN             NaN        NaN   \n",
       "5887              NaN                 NaN             NaN        NaN   \n",
       "5917              NaN                 NaN             NaN        NaN   \n",
       "5954              NaN                 NaN             NaN        NaN   \n",
       "6059              NaN                 NaN             NaN        NaN   \n",
       "6104              NaN                 NaN             NaN        NaN   \n",
       "6310              NaN                 NaN             NaN        NaN   \n",
       "6561              NaN                 NaN             NaN        NaN   \n",
       "6636              NaN                 NaN             NaN        NaN   \n",
       "6648              NaN                 NaN             NaN        NaN   \n",
       "\n",
       "     audit_committee_x   FYE_y  audit_committee_y  audited_financials  \\\n",
       "5798               NaN  FY2014                1.0                 0.0   \n",
       "5887               NaN  FY2013                NaN                 0.0   \n",
       "5917               NaN  FY2014                NaN                 0.0   \n",
       "5954               NaN  FY2011                NaN                 0.0   \n",
       "6059               NaN  FY2012                NaN                 0.0   \n",
       "6104               NaN  FY2014                1.0                 1.0   \n",
       "6310               NaN  FY2015                1.0                 1.0   \n",
       "6561               NaN  FY2014                1.0                 1.0   \n",
       "6636               NaN  FY2014                0.0                 0.0   \n",
       "6648               NaN  FY2014                1.0                 1.0   \n",
       "\n",
       "     fundraising_exp  low_quality_reporting  \n",
       "5798         3146137                    0.0  \n",
       "5887               0                    1.0  \n",
       "5917               0                    1.0  \n",
       "5954           26827                    0.0  \n",
       "6059               0                    1.0  \n",
       "6104           63530                    0.0  \n",
       "6310               0                    1.0  \n",
       "6561           28560                    0.0  \n",
       "6636           33086                    0.0  \n",
       "6648               0                    1.0  "
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged[merged['independent_members'].notnull()][columns][110:120]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "66\n",
      "0\n",
      "321\n",
      "321\n",
      "321\n",
      "7667\n",
      "7667\n",
      "7667\n",
      "7667\n",
      "7667\n",
      "7667\n",
      "7667\n",
      "7667\n",
      "7667\n",
      "8152\n",
      "8181\n",
      "8152\n",
      "8152\n",
      "8152\n",
      "8152\n",
      "8152\n"
     ]
    }
   ],
   "source": [
    "print len(merged[merged[columns[0]].isnull()])\n",
    "print len(merged[merged[columns[1]].isnull()])\n",
    "print len(merged[merged[columns[2]].isnull()])\n",
    "print len(merged[merged[columns[3]].isnull()])\n",
    "print len(merged[merged[columns[4]].isnull()])\n",
    "print len(merged[merged[columns[5]].isnull()])\n",
    "print len(merged[merged[columns[6]].isnull()])\n",
    "print len(merged[merged[columns[7]].isnull()])\n",
    "print len(merged[merged[columns[8]].isnull()])\n",
    "print len(merged[merged[columns[9]].isnull()])\n",
    "print len(merged[merged[columns[10]].isnull()])\n",
    "print len(merged[merged[columns[11]].isnull()])\n",
    "print len(merged[merged[columns[12]].isnull()])\n",
    "print len(merged[merged[columns[13]].isnull()])\n",
    "print len(merged[merged[columns[14]].isnull()])\n",
    "print len(merged[merged[columns[15]].isnull()])\n",
    "print len(merged[merged[columns[16]].isnull()])\n",
    "print len(merged[merged[columns[17]].isnull()])\n",
    "print len(merged[merged[columns[18]].isnull()])\n",
    "print len(merged[merged[columns[19]].isnull()])\n",
    "print len(merged[merged[columns[20]].isnull()])\n",
    "print len(merged[merged[columns[21]].isnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for index, row in merged[10:].iterrows():\n",
    "    if type(row['independent_board_2016'])==list:\n",
    "        #print 'yup'\n",
    "        merged.ix[index, 'independent_board_2016'] = row['independent_board_2016'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<type 'float'> 0.0 nan\n",
      "<type 'float'> 0.0 _gfx_/icons/checkboxX.gif\n",
      "<type 'float'> 0.0 _gfx_/icons/checked.gif\n",
      "<type 'float'> 0.0 _gfx_/icons/checked.gif\n",
      "<type 'float'> 0.0 _gfx_/icons/checked.gif\n",
      "<type 'float'> 0.0 _gfx_/icons/checked.gif\n",
      "<type 'float'> 0.0 _gfx_/icons/checked.gif\n",
      "<type 'float'> 0.0 _gfx_/icons/checked.gif\n",
      "<type 'float'> 0.0 nan\n",
      "<type 'float'> 0.0 nan\n"
     ]
    }
   ],
   "source": [
    "for index, row in merged[:10].iterrows():\n",
    "    print type(row['independent_board_v2']), row['independent_board_v2'], row['independent_board_2016']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7983 \n",
      "\n",
      "_gfx_/icons/checked.gif      7658\n",
      "_gfx_/icons/checkboxX.gif     325\n",
      "Name: independent_board_2016, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print merged['independent_board_2016'].value_counts().sum(),'\\n'\n",
    "print merged['independent_board_2016'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "7983\n",
      "8135\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "_gfx_/icons/checked.gif      7658\n",
       "_gfx_/icons/checkboxX.gif     325\n",
       "1.0                            83\n",
       "0.0                            69\n",
       "Name: independent_board_v2, dtype: int64"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged['independent_board_v2'] = np.nan\n",
    "print len(merged[merged['independent_board_v2'].notnull()])\n",
    "merged['independent_board_v2'] = np.where(merged['independent_board_2016'].notnull(), merged['independent_board_2016'], \n",
    "                                      merged['independent_board_v2'] )\n",
    "print len(merged[merged['independent_board_v2'].notnull()])\n",
    "merged['independent_board_v2'] = np.where( ((merged['independent_board_v2'].isnull()) & \n",
    "                                      (merged['independent_board'].notnull())), \n",
    "                         merged['independent_board'], merged['independent_board_v2'] )\n",
    "print len(merged[merged['independent_board_v2'].notnull()])\n",
    "merged['independent_board_v2'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    7741\n",
       "0     394\n",
       "Name: independent_board_v2, dtype: int64"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged['independent_board_v2'] = np.where(merged['independent_board_v2']== '_gfx_/icons/checked.gif', \n",
    "                                             1, merged['independent_board_v2']) \n",
    "merged['independent_board_v2'] = np.where(merged['independent_board_v2']== '_gfx_/icons/checkboxX.gif', \n",
    "                                             0, merged['independent_board_v2']) \n",
    "merged['independent_board_v2'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8135"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged['independent_board_v2'].value_counts().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Audited Financials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "merged.rename(columns={'FYE_x':'FYE'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "columns = ['org_id', 'EIN', 'FYE', #'independent_board_2016', \n",
    "           #'board_size', 'independent_members', 'independent_board', \n",
    "           #'pt1_num_vtng_gvrn_bdy_mems', 'pt1_num_ind_vtng_mems', \n",
    "           'independent_board_v2',\n",
    "           'audited_financials_2016', \n",
    "           #'FYE_y',\n",
    "           'audited_financials', 'aud_fincl_stmts', 'fs_audited', 'audit_committee_x',  'audit_committee_y', \n",
    "           #'num_vtng_gvrn_bdy_mems', 'num_ind_vtng_mems', 'contri_grnts_cy', 'tot_fndrsng_exp_cy', \n",
    "           #'fund_expense_percent_2016', \n",
    "           #'fundraising_exp', 'low_quality_reporting'\n",
    "          ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>org_id</th>\n",
       "      <th>EIN</th>\n",
       "      <th>FYE</th>\n",
       "      <th>independent_board_v2</th>\n",
       "      <th>audited_financials_2016</th>\n",
       "      <th>audited_financials</th>\n",
       "      <th>aud_fincl_stmts</th>\n",
       "      <th>fs_audited</th>\n",
       "      <th>audit_committee_x</th>\n",
       "      <th>audit_committee_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16722</td>\n",
       "      <td>020503776</td>\n",
       "      <td>current</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10166</td>\n",
       "      <td>043314346</td>\n",
       "      <td>FY2013</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6466</td>\n",
       "      <td>953667812</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12098</td>\n",
       "      <td>581974429</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12123</td>\n",
       "      <td>237172077</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  org_id        EIN      FYE independent_board_v2 audited_financials_2016  \\\n",
       "0  16722  020503776  current                    0                     NaN   \n",
       "1  10166  043314346   FY2013                    0                       1   \n",
       "2   6466  953667812   FY2014                    1                       1   \n",
       "3  12098  581974429   FY2014                    1                       1   \n",
       "4  12123  237172077   FY2014                    1                       1   \n",
       "\n",
       "   audited_financials aud_fincl_stmts fs_audited audit_committee_x  \\\n",
       "0                 NaN             NaN        NaN               NaN   \n",
       "1                 NaN             NaN        NaN               NaN   \n",
       "2                 NaN             NaN        NaN               NaN   \n",
       "3                 NaN             NaN        NaN               NaN   \n",
       "4                 NaN             NaN        NaN               NaN   \n",
       "\n",
       "   audit_committee_y  \n",
       "0                NaN  \n",
       "1                NaN  \n",
       "2                NaN  \n",
       "3                NaN  \n",
       "4                NaN  "
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged[columns][:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "66\n",
      "0\n",
      "0\n",
      "321\n",
      "8152\n",
      "7667\n",
      "7667\n",
      "7667\n",
      "8181\n"
     ]
    }
   ],
   "source": [
    "print len(merged[merged[columns[0]].isnull()])\n",
    "print len(merged[merged[columns[1]].isnull()])\n",
    "print len(merged[merged[columns[2]].isnull()])\n",
    "print len(merged[merged[columns[3]].isnull()])\n",
    "print len(merged[merged[columns[4]].isnull()])\n",
    "print len(merged[merged[columns[5]].isnull()])\n",
    "print len(merged[merged[columns[6]].isnull()])\n",
    "print len(merged[merged[columns[7]].isnull()])\n",
    "print len(merged[merged[columns[8]].isnull()])\n",
    "print len(merged[merged[columns[9]].isnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for index, row in merged.iterrows():\n",
    "    if type(row['audited_financials_2016'])==list:\n",
    "        merged.ix[index, 'audited_financials_2016'] = row['audited_financials_2016'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>org_id</th>\n",
       "      <th>EIN</th>\n",
       "      <th>FYE</th>\n",
       "      <th>independent_board_v2</th>\n",
       "      <th>audited_financials_2016</th>\n",
       "      <th>aud_fincl_stmts</th>\n",
       "      <th>fs_audited</th>\n",
       "      <th>audit_committee_x</th>\n",
       "      <th>audit_committee_y</th>\n",
       "      <th>audited_financials</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16722</td>\n",
       "      <td>020503776</td>\n",
       "      <td>current</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10166</td>\n",
       "      <td>043314346</td>\n",
       "      <td>FY2013</td>\n",
       "      <td>0</td>\n",
       "      <td>_gfx_/icons/checked.gif</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6466</td>\n",
       "      <td>953667812</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>1</td>\n",
       "      <td>_gfx_/icons/checked.gif</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12098</td>\n",
       "      <td>581974429</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>1</td>\n",
       "      <td>_gfx_/icons/checked.gif</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12123</td>\n",
       "      <td>237172077</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>1</td>\n",
       "      <td>_gfx_/icons/checked.gif</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  org_id        EIN      FYE independent_board_v2  audited_financials_2016  \\\n",
       "0  16722  020503776  current                    0                      NaN   \n",
       "1  10166  043314346   FY2013                    0  _gfx_/icons/checked.gif   \n",
       "2   6466  953667812   FY2014                    1  _gfx_/icons/checked.gif   \n",
       "3  12098  581974429   FY2014                    1  _gfx_/icons/checked.gif   \n",
       "4  12123  237172077   FY2014                    1  _gfx_/icons/checked.gif   \n",
       "\n",
       "  aud_fincl_stmts fs_audited audit_committee_x  audit_committee_y  \\\n",
       "0             NaN        NaN               NaN                NaN   \n",
       "1             NaN        NaN               NaN                NaN   \n",
       "2             NaN        NaN               NaN                NaN   \n",
       "3             NaN        NaN               NaN                NaN   \n",
       "4             NaN        NaN               NaN                NaN   \n",
       "\n",
       "   audited_financials  \n",
       "0                 NaN  \n",
       "1                 NaN  \n",
       "2                 NaN  \n",
       "3                 NaN  \n",
       "4                 NaN  "
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged[columns][:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "_gfx_/icons/checked.gif           7247\n",
      "_gfx_/icons/checkboxX.gif          372\n",
      "_gfx_/icons/checkboxOptOut.png     364\n",
      "Name: audited_financials_2016, dtype: int64\n",
      "1                                 7247\n",
      "0                                  372\n",
      "_gfx_/icons/checkboxOptOut.png     364\n",
      "Name: audited_financials_2016, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print merged['audited_financials_2016'].value_counts()\n",
    "merged['audited_financials_2016'] = np.where(merged['audited_financials_2016']== '_gfx_/icons/checked.gif', \n",
    "                                             1, merged['audited_financials_2016']) \n",
    "merged['audited_financials_2016'] = np.where(merged['audited_financials_2016']== '_gfx_/icons/checkboxX.gif', \n",
    "                                             0, merged['audited_financials_2016']) \n",
    "print merged['audited_financials_2016'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1    7611\n",
      "0     372\n",
      "Name: audited_financials_2016_v2, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "merged['audited_financials_2016_v2'] = np.where(merged['audited_financials_2016']== '_gfx_/icons/checkboxOptOut.png', \n",
    "                                             1, merged['audited_financials_2016']) \n",
    "print merged['audited_financials_2016_v2'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7667\n",
      "7667\n",
      "Y    433\n",
      "N    204\n",
      "Name: aud_fincl_stmts, dtype: int64 \n",
      "\n",
      "Y    629\n",
      "N      8\n",
      "Name: fs_audited, dtype: int64 \n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>fs_audited</th>\n",
       "      <th>N</th>\n",
       "      <th>Y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aud_fincl_stmts</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>N</th>\n",
       "      <td>7</td>\n",
       "      <td>197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Y</th>\n",
       "      <td>1</td>\n",
       "      <td>432</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "fs_audited       N    Y\n",
       "aud_fincl_stmts        \n",
       "N                7  197\n",
       "Y                1  432"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print len(merged[merged[columns[5]].isnull()])\n",
    "print len(merged[merged[columns[6]].isnull()])\n",
    "print merged[columns[5]].value_counts(), '\\n'\n",
    "print merged[columns[6]].value_counts(), '\\n'\n",
    "pd.crosstab(merged[columns[5]],merged[columns[6]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "637"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "432+197+1+7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7983\n"
     ]
    }
   ],
   "source": [
    "print len(merged[merged['audited_financials_2016_v2'].notnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "7983\n",
      "8135\n",
      "8135\n",
      "8135\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1    7703\n",
       "0     432\n",
       "Name: audited_financials_v2, dtype: int64"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged['audited_financials_v2'] = np.nan\n",
    "print len(merged[merged['audited_financials_v2'].notnull()])\n",
    "merged['audited_financials_v2'] = np.where(merged['audited_financials_2016_v2'].notnull(), \n",
    "                                        merged['audited_financials_2016_v2'], \n",
    "                                      merged['audited_financials_v2'] )\n",
    "print len(merged[merged['audited_financials_v2'].notnull()])\n",
    "merged['audited_financials_v2'] = np.where( ((merged['audited_financials_v2'].isnull()) & \n",
    "                                      (merged['audited_financials'].notnull())), \n",
    "                         merged['audited_financials'], merged['audited_financials_v2'] )\n",
    "print len(merged[merged['audited_financials_v2'].notnull()])\n",
    "merged['audited_financials_v2'] = np.where( ((merged['audited_financials_v2'].isnull()) & \n",
    "                                      (merged['aud_fincl_stmts'].notnull())), \n",
    "                         merged['aud_fincl_stmts'], merged['audited_financials_v2'] )\n",
    "print len(merged[merged['audited_financials_v2'].notnull()])\n",
    "merged['audited_financials_v2'].value_counts()\n",
    "merged['audited_financials_v2'] = np.where( ((merged['audited_financials_v2'].isnull()) & \n",
    "                                      (merged['fs_audited'].notnull())), \n",
    "                         merged['fs_audited'], merged['audited_financials_v2'] )\n",
    "print len(merged[merged['audited_financials_v2'].notnull()])\n",
    "merged['audited_financials_v2'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0    92\n",
       "0.0    60\n",
       "Name: audited_financials, dtype: int64"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged['audited_financials'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Save DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "columns_v2 = ['org_id', 'EIN', 'FYE', #'independent_board_2016', \n",
    "           #'board_size', 'independent_members', 'independent_board', \n",
    "           #'pt1_num_vtng_gvrn_bdy_mems', 'pt1_num_ind_vtng_mems', \n",
    "           'independent_board_v2',\n",
    "           #'audited_financials_2016', \n",
    "           'audited_financials_v2',\n",
    "           #'FYE_y',\n",
    "           #'audited_financials', 'aud_fincl_stmts', 'fs_audited', 'audit_committee_x',  'audit_committee_y', \n",
    "           #'num_vtng_gvrn_bdy_mems', 'num_ind_vtng_mems', 'contri_grnts_cy', 'tot_fndrsng_exp_cy', \n",
    "           #'fund_expense_percent_2016', \n",
    "           #'fundraising_exp', 'low_quality_reporting'\n",
    "          ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "org_id                   object\n",
       "EIN                      object\n",
       "FYE                      object\n",
       "independent_board_v2     object\n",
       "audited_financials_v2    object\n",
       "dtype: object"
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged[columns_v2].dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "merged['independent_board_v2'] = merged['independent_board_v2'].astype('float')\n",
    "merged['audited_financials_v2'] = merged['audited_financials_v2'].astype('float')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>independent_board_v2</th>\n",
       "      <td>8135.0</td>\n",
       "      <td>0.951567</td>\n",
       "      <td>0.214692</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>audited_financials_v2</th>\n",
       "      <td>8135.0</td>\n",
       "      <td>0.946896</td>\n",
       "      <td>0.224254</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        count      mean       std  min  25%  50%  75%  max\n",
       "independent_board_v2   8135.0  0.951567  0.214692  0.0  NaN  NaN  NaN  1.0\n",
       "audited_financials_v2  8135.0  0.946896  0.224254  0.0  NaN  NaN  NaN  1.0"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged[columns_v2].describe().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "merged[columns_v2].to_pickle('additional variables for testing.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8304\n"
     ]
    }
   ],
   "source": [
    "print len(merged)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8238\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Col1</th>\n",
       "      <th>A2011_data</th>\n",
       "      <th>A2016_data</th>\n",
       "      <th>ein</th>\n",
       "      <th>fye</th>\n",
       "      <th>Form_990_FYE</th>\n",
       "      <th>SOX_policies</th>\n",
       "      <th>SOX_policies_all_binary</th>\n",
       "      <th>SOX_policies_binary</th>\n",
       "      <th>age</th>\n",
       "      <th>category</th>\n",
       "      <th>category_Animals</th>\n",
       "      <th>category_Arts__Culture__Humaniti</th>\n",
       "      <th>category_Community_Development</th>\n",
       "      <th>category_Education</th>\n",
       "      <th>category_Environment</th>\n",
       "      <th>category_Health</th>\n",
       "      <th>category_Human_Services</th>\n",
       "      <th>category_Human_and_Civil_Rights</th>\n",
       "      <th>category_International</th>\n",
       "      <th>category_Religion</th>\n",
       "      <th>category_Research_and_Public_Pol</th>\n",
       "      <th>complexity</th>\n",
       "      <th>complexity_2011</th>\n",
       "      <th>conflict_of_interest_policy_v2</th>\n",
       "      <th>donor_advisory</th>\n",
       "      <th>donor_advisory_2011_to_2016</th>\n",
       "      <th>donor_advisory_2016</th>\n",
       "      <th>org_id</th>\n",
       "      <th>program_efficiency</th>\n",
       "      <th>ratings_system</th>\n",
       "      <th>records_retention_policy_v2</th>\n",
       "      <th>state</th>\n",
       "      <th>tot_rev</th>\n",
       "      <th>total_revenue_logged</th>\n",
       "      <th>whistleblower_policy_v2</th>\n",
       "      <th>ncategory</th>\n",
       "      <th>revs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>50709</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>010202467</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>2014-12</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>Research and Public Policy</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5954</td>\n",
       "      <td>0.794457</td>\n",
       "      <td>CN 2.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>ME</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.377993</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Research and Public Policy</td>\n",
       "      <td>12967968.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>76320</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>010211478</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>2014-06</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>Community Development</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12517</td>\n",
       "      <td>0.808606</td>\n",
       "      <td>CN 2.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>ME</td>\n",
       "      <td>3054612.0</td>\n",
       "      <td>14.932163</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Community Development</td>\n",
       "      <td>3054612.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Col1  A2011_data  A2016_data        ein     fye Form_990_FYE  \\\n",
       "0  50709           0           1  010202467  FY2014      2014-12   \n",
       "1  76320           0           1  010211478  FY2014      2014-06   \n",
       "\n",
       "   SOX_policies  SOX_policies_all_binary  SOX_policies_binary   age  \\\n",
       "0           3.0                      1.0                  1.0  62.0   \n",
       "1           3.0                      1.0                  1.0  57.0   \n",
       "\n",
       "                     category  category_Animals  \\\n",
       "0  Research and Public Policy                 0   \n",
       "1       Community Development                 0   \n",
       "\n",
       "   category_Arts__Culture__Humaniti  category_Community_Development  \\\n",
       "0                                 0                               0   \n",
       "1                                 0                               1   \n",
       "\n",
       "   category_Education  category_Environment  category_Health  \\\n",
       "0                   0                     0                0   \n",
       "1                   0                     0                0   \n",
       "\n",
       "   category_Human_Services  category_Human_and_Civil_Rights  \\\n",
       "0                        0                                0   \n",
       "1                        0                                0   \n",
       "\n",
       "   category_International  category_Religion  \\\n",
       "0                       0                  0   \n",
       "1                       0                  0   \n",
       "\n",
       "   category_Research_and_Public_Pol  complexity  complexity_2011  \\\n",
       "0                                 1         6.0              NaN   \n",
       "1                                 0         3.0              NaN   \n",
       "\n",
       "   conflict_of_interest_policy_v2  donor_advisory  \\\n",
       "0                             1.0               0   \n",
       "1                             1.0               0   \n",
       "\n",
       "   donor_advisory_2011_to_2016  donor_advisory_2016 org_id  \\\n",
       "0                            0                    0   5954   \n",
       "1                            0                    0  12517   \n",
       "\n",
       "   program_efficiency ratings_system  records_retention_policy_v2 state  \\\n",
       "0            0.794457         CN 2.1                          1.0    ME   \n",
       "1            0.808606         CN 2.1                          1.0    ME   \n",
       "\n",
       "     tot_rev  total_revenue_logged  whistleblower_policy_v2  \\\n",
       "0        NaN             16.377993                      1.0   \n",
       "1  3054612.0             14.932163                      1.0   \n",
       "\n",
       "                    ncategory        revs  \n",
       "0  Research and Public Policy  12967968.0  \n",
       "1       Community Development   3054612.0  "
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4 = pd.read_stata('Test 4 data.dta')\n",
    "print len(df4)\n",
    "df4[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8238\n",
      "both          8238\n",
      "right_only       0\n",
      "left_only        0\n",
      "Name: _merge, dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Col1</th>\n",
       "      <th>A2011_data</th>\n",
       "      <th>A2016_data</th>\n",
       "      <th>ein</th>\n",
       "      <th>fye</th>\n",
       "      <th>Form_990_FYE</th>\n",
       "      <th>SOX_policies</th>\n",
       "      <th>SOX_policies_all_binary</th>\n",
       "      <th>SOX_policies_binary</th>\n",
       "      <th>age</th>\n",
       "      <th>category</th>\n",
       "      <th>category_Animals</th>\n",
       "      <th>category_Arts__Culture__Humaniti</th>\n",
       "      <th>category_Community_Development</th>\n",
       "      <th>category_Education</th>\n",
       "      <th>category_Environment</th>\n",
       "      <th>category_Health</th>\n",
       "      <th>category_Human_Services</th>\n",
       "      <th>category_Human_and_Civil_Rights</th>\n",
       "      <th>category_International</th>\n",
       "      <th>category_Religion</th>\n",
       "      <th>category_Research_and_Public_Pol</th>\n",
       "      <th>complexity</th>\n",
       "      <th>complexity_2011</th>\n",
       "      <th>conflict_of_interest_policy_v2</th>\n",
       "      <th>donor_advisory</th>\n",
       "      <th>donor_advisory_2011_to_2016</th>\n",
       "      <th>donor_advisory_2016</th>\n",
       "      <th>org_id_x</th>\n",
       "      <th>program_efficiency</th>\n",
       "      <th>ratings_system</th>\n",
       "      <th>records_retention_policy_v2</th>\n",
       "      <th>state</th>\n",
       "      <th>tot_rev</th>\n",
       "      <th>total_revenue_logged</th>\n",
       "      <th>whistleblower_policy_v2</th>\n",
       "      <th>ncategory</th>\n",
       "      <th>revs</th>\n",
       "      <th>org_id_y</th>\n",
       "      <th>EIN</th>\n",
       "      <th>FYE</th>\n",
       "      <th>independent_board_v2</th>\n",
       "      <th>audited_financials_v2</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>50709</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>010202467</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>2014-12</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>Research and Public Policy</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5954</td>\n",
       "      <td>0.794457</td>\n",
       "      <td>CN 2.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>ME</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.377993</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Research and Public Policy</td>\n",
       "      <td>12967968.0</td>\n",
       "      <td>5954</td>\n",
       "      <td>010202467</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>76320</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>010211478</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>2014-06</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>Community Development</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12517</td>\n",
       "      <td>0.808606</td>\n",
       "      <td>CN 2.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>ME</td>\n",
       "      <td>3054612.0</td>\n",
       "      <td>14.932163</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Community Development</td>\n",
       "      <td>3054612.0</td>\n",
       "      <td>12517</td>\n",
       "      <td>010211478</td>\n",
       "      <td>FY2014</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Col1  A2011_data  A2016_data        ein     fye Form_990_FYE  \\\n",
       "0  50709           0           1  010202467  FY2014      2014-12   \n",
       "1  76320           0           1  010211478  FY2014      2014-06   \n",
       "\n",
       "   SOX_policies  SOX_policies_all_binary  SOX_policies_binary   age  \\\n",
       "0           3.0                      1.0                  1.0  62.0   \n",
       "1           3.0                      1.0                  1.0  57.0   \n",
       "\n",
       "                     category  category_Animals  \\\n",
       "0  Research and Public Policy                 0   \n",
       "1       Community Development                 0   \n",
       "\n",
       "   category_Arts__Culture__Humaniti  category_Community_Development  \\\n",
       "0                                 0                               0   \n",
       "1                                 0                               1   \n",
       "\n",
       "   category_Education  category_Environment  category_Health  \\\n",
       "0                   0                     0                0   \n",
       "1                   0                     0                0   \n",
       "\n",
       "   category_Human_Services  category_Human_and_Civil_Rights  \\\n",
       "0                        0                                0   \n",
       "1                        0                                0   \n",
       "\n",
       "   category_International  category_Religion  \\\n",
       "0                       0                  0   \n",
       "1                       0                  0   \n",
       "\n",
       "   category_Research_and_Public_Pol  complexity  complexity_2011  \\\n",
       "0                                 1         6.0              NaN   \n",
       "1                                 0         3.0              NaN   \n",
       "\n",
       "   conflict_of_interest_policy_v2  donor_advisory  \\\n",
       "0                             1.0               0   \n",
       "1                             1.0               0   \n",
       "\n",
       "   donor_advisory_2011_to_2016  donor_advisory_2016 org_id_x  \\\n",
       "0                            0                    0     5954   \n",
       "1                            0                    0    12517   \n",
       "\n",
       "   program_efficiency ratings_system  records_retention_policy_v2 state  \\\n",
       "0            0.794457         CN 2.1                          1.0    ME   \n",
       "1            0.808606         CN 2.1                          1.0    ME   \n",
       "\n",
       "     tot_rev  total_revenue_logged  whistleblower_policy_v2  \\\n",
       "0        NaN             16.377993                      1.0   \n",
       "1  3054612.0             14.932163                      1.0   \n",
       "\n",
       "                    ncategory        revs org_id_y        EIN     FYE  \\\n",
       "0  Research and Public Policy  12967968.0     5954  010202467  FY2014   \n",
       "1       Community Development   3054612.0    12517  010211478  FY2014   \n",
       "\n",
       "   independent_board_v2  audited_financials_v2 _merge  \n",
       "0                   1.0                    1.0   both  \n",
       "1                   1.0                    1.0   both  "
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged2 = pd.merge(df4, merged[columns_v2], left_on='ein', right_on='EIN', how='left', indicator=True)\n",
    "print len(merged2)\n",
    "print merged2['_merge'].value_counts()\n",
    "merged2[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    7983\n",
       "1     255\n",
       "Name: donor_advisory_2016, dtype: int64"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged2['donor_advisory_2016'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "merged2.to_pickle('Test 4 with independent_board and audited_financials.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "columns = ['screen_name', 'followers_first', 'followers_end', 'followers_last', \n",
    "   'orgid', 'retweeted_count', 'favorited_count']\n",
    "aggregations = {'orgid': 'first',\n",
    "                'screen_name': 'first',\n",
    "                'retweeted_count': 'sum',\n",
    "                'favorited_count': 'sum',\n",
    "                'followers_first': 'first',\n",
    "                'followers_end': 'last',\n",
    "                }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "columns = ['screen_name', 'followers_first', 'followers_end', 'followers_last', \n",
    "   'orgid', 'retweeted_count', 'favorited_count']\n",
    "aggregations = {'orgid': 'first',\n",
    "                'screen_name': 'first',\n",
    "                'retweeted_count': 'sum',\n",
    "                'favorited_count': 'sum',\n",
    "                'followers_first': 'first',\n",
    "                'followers_end': 'last',\n",
    "                }\n",
    "org_df = df[columns].groupby('orgid').agg(aggregations)\n",
    "print len(org_df)\n",
    "org_df = org_df.dropna()\n",
    "org_df['followers_first'] = org_df['followers_first'].astype('int')\n",
    "org_df['followers_end'] = org_df['followers_end'].astype('int')\n",
    "org_df['favorited_count'] = org_df['favorited_count'].astype('int')\n",
    "org_df['followers_change'] = org_df['followers_end'] - org_df['followers_first']\n",
    "org_df['followers_pct_change'] = (org_df['followers_end'] - org_df['followers_first'])/org_df['followers_first']\n",
    "print len(org_df)\n",
    "org_df[:5]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}