features.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import matplotlib\n",
    "matplotlib.style.use('ggplot')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "regions = {\n",
    "    \"New England\": [\"CT\", \"ME\", \"MA\", \"NH\", \"RI\", \"VT\"],\n",
    "    \"Mideast\": [\"DE\", \"DC\", \"MD\", \"NJ\", \"NY\", \"PA\"],\n",
    "    \"Great Lakes\": [\"IL\", \"IN\", \"MI\", \"OH\", \"WI\"],\n",
    "    \"Plains\": [\"IA\", \"KS\", \"MN\", \"MO\", \"MS\", \"NE\", \"ND\", \"SD\"],\n",
    "    \"Southeast\": [\"AL\", \"AR\", \"FL\", \"GA\", \"KY\", \"LA\", \"MS\", \"NC\", \"SC\", \"VA\", \"TN\", \"WV\"],\n",
    "    \"Southwest\": [\"AZ\", \"NM\", \"OK\", \"TX\"],\n",
    "    \"Rocky Mountain\": [\"CO\", \"ID\", \"MT\", \"UT\", \"WY\"],\n",
    "    \"Far West\": [\"AK\", \"CA\", \"HI\", \"NV\", \"OR\", \"WA\"]\n",
    "}\n",
    "\n",
    "\n",
    "states_by_region = [{state: region  for state in states}for (region, states) in regions.iteritems()]\n",
    "\n",
    "states = {}\n",
    "for d in states_by_region:\n",
    "    states.update(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_submission = pd.read_csv(\"data/sampleSubmission.csv\", index_col=0)\n",
    "df_outcomes_all = pd.read_csv(\"data/outcomes.csv\", index_col=0)\n",
    "df_results = df_outcomes_all[['is_exciting']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_projects_all = pd.read_csv(\"data/projects.csv\", index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def filter_by_date(projects, year):\n",
    "    is_new = projects.date_posted.apply(lambda date: int(date.split('-')[0]) >= year)\n",
    "    return projects.loc[is_new,:]\n",
    "\n",
    "df_projects = filter_by_date(df_projects_all, 2013)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_projects_train = pd.merge(df_results, df_projects, right_index=True, left_index=True, how=\"inner\")\n",
    "df_projects_submission = df_projects_all[df_projects_all.index.isin(df_submission.index)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "columns = [u'school_state', u'school_metro', u'school_district',\n",
    "       u'school_county', u'school_charter', u'school_magnet',\n",
    "       u'school_year_round', u'school_nlns', u'school_kipp',\n",
    "       u'school_charter_ready_promise', u'teacher_prefix',\n",
    "       u'teacher_teach_for_america', u'teacher_ny_teaching_fellow',\n",
    "       u'primary_focus_subject', u'primary_focus_area',\n",
    "       u'secondary_focus_subject', u'secondary_focus_area', u'resource_type',\n",
    "       u'poverty_level', u'grade_level', u'fulfillment_labor_materials',\n",
    "       u'total_price_excluding_optional_support',\n",
    "       u'total_price_including_optional_support', u'students_reached',\n",
    "       u'eligible_double_your_impact_match', u'eligible_almost_home_match']\n",
    "\n",
    "train_columns = [u'is_exciting', u'school_state', u'school_metro', u'school_district',\n",
    "       u'school_county', u'school_charter', u'school_magnet',\n",
    "       u'school_year_round', u'school_nlns', u'school_kipp',\n",
    "       u'school_charter_ready_promise', u'teacher_prefix',\n",
    "       u'teacher_teach_for_america', u'teacher_ny_teaching_fellow',\n",
    "       u'primary_focus_subject', u'primary_focus_area',\n",
    "       u'secondary_focus_subject', u'secondary_focus_area', u'resource_type',\n",
    "       u'poverty_level', u'grade_level', u'fulfillment_labor_materials',\n",
    "       u'total_price_excluding_optional_support',\n",
    "       u'total_price_including_optional_support', u'students_reached',\n",
    "       u'eligible_double_your_impact_match', u'eligible_almost_home_match']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_projects_train = df_projects_train[train_columns]\n",
    "df_projects_submission = df_projects_submission[columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def get_school_district_for_state(group):\n",
    "    most_commot_district = group[\"school_district\"].value_counts().index[0]\n",
    "    return pd.Series([most_commot_district], \n",
    "                         index=['most_commot_district'])\n",
    "\n",
    "def get_missing_district(project):\n",
    "    return project['school_district'] if not pd.isnull(project['school_district']) else districts.ix[project['school_state'], project['school_county']].values[0]\n",
    "\n",
    "districts = df_projects_train.groupby(['school_state', 'school_county']).apply(get_school_district_for_state)\n",
    "\n",
    "def fix_projects_missing_data(projects):\n",
    "    projects.students_reached.fillna(projects.students_reached.median(), inplace=True)\n",
    "    projects.primary_focus_subject.fillna(projects.primary_focus_subject.value_counts().index[0], inplace=True)\n",
    "    projects.primary_focus_area.fillna(projects.primary_focus_area.value_counts().index[0], inplace=True)\n",
    "    projects.resource_type.fillna(projects.resource_type.value_counts().index[0], inplace=True)\n",
    "    projects.grade_level.fillna(projects.grade_level.value_counts().index[0], inplace=True)\n",
    "    projects.school_district = projects.apply(get_missing_district, axis=1).values\n",
    "    return projects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_projects_train = fix_projects_missing_data(df_projects_train)\n",
    "df_projects_submission = fix_projects_missing_data(df_projects_submission)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_projects_train.to_csv(\"train-data/projects.csv\")\n",
    "df_projects_submission.to_csv(\"train-data/submissions.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_essays = pd.read_csv(\"data/essays.csv\", index_col=0)\n",
    "df_essays = df_essays[df_essays.index.isin(df_projects.index)]\n",
    "df_essays.fillna('', inplace=True)\n",
    "df_essays['essay_len'] = df_essays.essay.apply(len)\n",
    "df_essays['title_len'] = df_essays.title.apply(len)\n",
    "df_essays['need_statement_len'] = df_essays.need_statement.apply(len)\n",
    "df_essays['short_description_len'] = df_essays.short_description.apply(len)\n",
    "\n",
    "df_essays_len = df_essays[['essay_len', 'title_len', 'need_statement_len', 'short_description_len']]\n",
    "df_essays_len.to_csv(\"train-data/essays.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import nltk\n",
    "from gensim import corpora, models, similarities\n",
    "from nltk.corpus import stopwords\n",
    "from nltk.stem.porter import PorterStemmer\n",
    "porter_stemmer = PorterStemmer()\n",
    "en_stop = stopwords.words('english')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "punctuation_regex = u'[^\\u0400-\\u0500a-zA-Z\\d ]'\n",
    "\n",
    "def remove_punctuation(series):\n",
    "        return series.str.replace(punctuation_regex, '')\n",
    "    \n",
    "documents = df_essays.essay\n",
    "documents = documents.fillna(\"\")\n",
    "documents = documents.apply(lambda text: text.replace(\"\\r\\\\n\\r\\\\n\", \"\"))\n",
    "documents = remove_punctuation(documents)\n",
    "documents = documents.apply(str.lower)\n",
    "tokenized = documents.apply(lambda text: nltk.word_tokenize(text.decode('utf-8')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tokenized = tokenized.apply(lambda tokens: [porter_stemmer.stem(token) for token in tokens if token not in en_stop])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dictionary = corpora.Dictionary(tokenized.values)\n",
    "corpus = [dictionary.doc2bow(text) for text in tokenized.values]\n",
    "corpora.MmCorpus.serialize('corpus.mm', corpus)\n",
    "corpus = corpora.MmCorpus('corpus.mm')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tfidf = models.TfidfModel(corpus)\n",
    "corpus_tfidf = tfidf[corpus]\n",
    "lsi = models.LsiModel(corpus_tfidf, id2word=dictionary, num_topics=75)\n",
    "corpus_lsi = lsi[corpus_tfidf]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "get_main_topic = lambda document: sorted(document,key=lambda (index, relation): relation, reverse=True)[:2]\n",
    "main_topics = [get_main_topic(document) for document in corpus_lsi]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "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>expences_per_student</th>\n",
       "      <th>school_region</th>\n",
       "      <th>main_topic</th>\n",
       "      <th>secondary_topic</th>\n",
       "      <th>topics_explained</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>projectid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>316ed8fb3b81402ff6ac8f721bb31192</th>\n",
       "      <td>17.369062</td>\n",
       "      <td>Far West</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>0.388444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90de744e368a7e4883223ca49318ae30</th>\n",
       "      <td>13.475909</td>\n",
       "      <td>Southwest</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>0.332286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32943bb1063267de6ed19fc0ceb4b9a7</th>\n",
       "      <td>25.346471</td>\n",
       "      <td>Far West</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.325069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bb18f409abda2f264d5acda8cab577a9</th>\n",
       "      <td>48.005833</td>\n",
       "      <td>Mideast</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.389604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24761b686e18e5eace634607acbcc19f</th>\n",
       "      <td>17.016667</td>\n",
       "      <td>Far West</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.466246</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  expences_per_student school_region  \\\n",
       "projectid                                                              \n",
       "316ed8fb3b81402ff6ac8f721bb31192             17.369062      Far West   \n",
       "90de744e368a7e4883223ca49318ae30             13.475909     Southwest   \n",
       "32943bb1063267de6ed19fc0ceb4b9a7             25.346471      Far West   \n",
       "bb18f409abda2f264d5acda8cab577a9             48.005833       Mideast   \n",
       "24761b686e18e5eace634607acbcc19f             17.016667      Far West   \n",
       "\n",
       "                                  main_topic  secondary_topic  \\\n",
       "projectid                                                       \n",
       "316ed8fb3b81402ff6ac8f721bb31192           0               15   \n",
       "90de744e368a7e4883223ca49318ae30           0                8   \n",
       "32943bb1063267de6ed19fc0ceb4b9a7           0                1   \n",
       "bb18f409abda2f264d5acda8cab577a9           0                1   \n",
       "24761b686e18e5eace634607acbcc19f           0                1   \n",
       "\n",
       "                                  topics_explained  \n",
       "projectid                                           \n",
       "316ed8fb3b81402ff6ac8f721bb31192          0.388444  \n",
       "90de744e368a7e4883223ca49318ae30          0.332286  \n",
       "32943bb1063267de6ed19fc0ceb4b9a7          0.325069  \n",
       "bb18f409abda2f264d5acda8cab577a9          0.389604  \n",
       "24761b686e18e5eace634607acbcc19f          0.466246  "
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "main_topic = pd.Series([topic[0][0] if topic else None for topic in main_topics])\n",
    "secondary_topic = pd.Series([topic[1][0] if topic else None for topic in main_topics])\n",
    "topics_explained = pd.Series([topic[0][1] + topic[1][1] if topic else None for topic in main_topics])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "main_topic_prob = pd.Series([topic[0][1] if topic else None for topic in main_topics])\n",
    "secondary_topic_prob = pd.Series([topic[1][1] if topic else None for topic in main_topics])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/makambi/anaconda/lib/python2.7/site-packages/pandas/core/generic.py:2602: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self._update_inplace(new_data)\n",
      "/home/makambi/anaconda/lib/python2.7/site-packages/pandas/core/generic.py:2177: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self[name] = value\n"
     ]
    }
   ],
   "source": [
    "df_projects = fix_projects_missing_data(df_projects)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/makambi/anaconda/lib/python2.7/site-packages/IPython/kernel/__main__.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  app.launch_new_instance()\n",
      "/home/makambi/anaconda/lib/python2.7/site-packages/IPython/kernel/__main__.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    }
   ],
   "source": [
    "df_additional_data = df_projects[['school_state', 'total_price_excluding_optional_support', 'students_reached']]\n",
    "\n",
    "df_additional_data['expences_per_student'] =  df_additional_data.apply(lambda project: 1.0 * project['total_price_excluding_optional_support'] / project['students_reached'], axis=1)\n",
    "df_additional_data['school_region'] = df_additional_data.school_state.apply(lambda state: states[state])\n",
    "df_additional_data = df_additional_data [['expences_per_student', 'school_region']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_additional_data['main_topic'] = main_topic.values\n",
    "df_additional_data['secondary_topic'] = secondary_topic.values\n",
    "# df_additional_data['main_topic_prob'] = main_topic_prob.values\n",
    "# df_additional_data['secondary_topic_prob'] = secondary_topic_prob.values\n",
    "df_additional_data['topics_explained'] = topics_explained.values\n",
    "\n",
    "df_additional_data.main_topic.fillna(df_additional_data.main_topic.value_counts().index[0], inplace=True)\n",
    "df_additional_data.secondary_topic.fillna(df_additional_data[df_additional_data.main_topic == 0].secondary_topic.value_counts().index[0], inplace=True)\n",
    "df_additional_data.topics_explained.fillna(0, inplace=True)\n",
    "# df_additional_data.main_topic_prob.fillna(0, inplace=True)\n",
    "# df_additional_data.secondary_topic_prob.fillna(0, inplace=True)\n",
    "\n",
    "df_additional_data.to_csv(\"train-data/projects-additional.csv\")\n",
    "\n",
    "df_additional_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_donations_all = pd.read_csv(\"data/donations.csv\", index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_donations = df_donations_all[df_donations_all.projectid.isin(df_projects.index)]\n",
    "df_donations = pd.merge(df_donations, df_projects[['teacher_acctid', 'schoolid', 'grade_level', 'primary_focus_subject']], left_on=\"projectid\", right_index=True, how=\"inner\")\n",
    "df_donations = pd.merge(df_donations, df_outcomes_all[[\"is_exciting\"]], left_on=\"projectid\", right_index=True, how=\"inner\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>projectid</th>\n",
       "      <th>donor_acctid</th>\n",
       "      <th>donor_city</th>\n",
       "      <th>donor_state</th>\n",
       "      <th>donor_zip</th>\n",
       "      <th>is_teacher_acct</th>\n",
       "      <th>donation_timestamp</th>\n",
       "      <th>donation_to_project</th>\n",
       "      <th>donation_optional_support</th>\n",
       "      <th>donation_total</th>\n",
       "      <th>...</th>\n",
       "      <th>payment_included_web_purchased_gift_card</th>\n",
       "      <th>payment_was_promo_matched</th>\n",
       "      <th>via_giving_page</th>\n",
       "      <th>for_honoree</th>\n",
       "      <th>donation_message</th>\n",
       "      <th>teacher_acctid</th>\n",
       "      <th>schoolid</th>\n",
       "      <th>grade_level</th>\n",
       "      <th>primary_focus_subject</th>\n",
       "      <th>is_exciting</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>donationid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>44842bc73032bcc4b44f77dd9007a6eb</th>\n",
       "      <td>ffff2d9c769c8fb5335e949c615425eb</td>\n",
       "      <td>ca2858ac1c427f52e39ca066db0b50a1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ID</td>\n",
       "      <td>83201</td>\n",
       "      <td>f</td>\n",
       "      <td>2013-05-09 17:08:40.145</td>\n",
       "      <td>58.65</td>\n",
       "      <td>10.35</td>\n",
       "      <td>69</td>\n",
       "      <td>...</td>\n",
       "      <td>f</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>f</td>\n",
       "      <td>Education is the most important thing in the w...</td>\n",
       "      <td>485784e52a4c85ea6783285d09d8aea4</td>\n",
       "      <td>f8e49225a821cc04e1176303e55d89ec</td>\n",
       "      <td>Grades 3-5</td>\n",
       "      <td>Mathematics</td>\n",
       "      <td>t</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45b90c3d8cbf06a4c88410069c7fa9c7</th>\n",
       "      <td>ffff2d9c769c8fb5335e949c615425eb</td>\n",
       "      <td>38fcf7f544893c94a77ec58d4729ffb8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f</td>\n",
       "      <td>2013-03-06 22:48:04.013</td>\n",
       "      <td>85.00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>100</td>\n",
       "      <td>...</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>t</td>\n",
       "      <td>f</td>\n",
       "      <td>Matching donation by the DonorsChoose.org Boar...</td>\n",
       "      <td>485784e52a4c85ea6783285d09d8aea4</td>\n",
       "      <td>f8e49225a821cc04e1176303e55d89ec</td>\n",
       "      <td>Grades 3-5</td>\n",
       "      <td>Mathematics</td>\n",
       "      <td>t</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9047579ce6a5c45abd801aa9e4c16bce</th>\n",
       "      <td>ffff2d9c769c8fb5335e949c615425eb</td>\n",
       "      <td>2f471330531031f76a0dd43c03096be5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CO</td>\n",
       "      <td>80003</td>\n",
       "      <td>f</td>\n",
       "      <td>2013-03-06 22:47:57.516</td>\n",
       "      <td>85.00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>100</td>\n",
       "      <td>...</td>\n",
       "      <td>f</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>f</td>\n",
       "      <td>I gave because we should all pay it forward.</td>\n",
       "      <td>485784e52a4c85ea6783285d09d8aea4</td>\n",
       "      <td>f8e49225a821cc04e1176303e55d89ec</td>\n",
       "      <td>Grades 3-5</td>\n",
       "      <td>Mathematics</td>\n",
       "      <td>t</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9e0f234dfa2982619dacd0635019d53d</th>\n",
       "      <td>ffff2d9c769c8fb5335e949c615425eb</td>\n",
       "      <td>af1f03b856fc2979c01a6ad8b61f6fbe</td>\n",
       "      <td>Pocatello</td>\n",
       "      <td>ID</td>\n",
       "      <td>83201</td>\n",
       "      <td>f</td>\n",
       "      <td>2013-05-08 12:58:55.38</td>\n",
       "      <td>68.00</td>\n",
       "      <td>12.00</td>\n",
       "      <td>80</td>\n",
       "      <td>...</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>Idaho Power Company believes in education and ...</td>\n",
       "      <td>485784e52a4c85ea6783285d09d8aea4</td>\n",
       "      <td>f8e49225a821cc04e1176303e55d89ec</td>\n",
       "      <td>Grades 3-5</td>\n",
       "      <td>Mathematics</td>\n",
       "      <td>t</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fdcd840e3dd57016590cb8a69aed0c09</th>\n",
       "      <td>ffff2d9c769c8fb5335e949c615425eb</td>\n",
       "      <td>af1f03b856fc2979c01a6ad8b61f6fbe</td>\n",
       "      <td>Pocatello</td>\n",
       "      <td>ID</td>\n",
       "      <td>83201</td>\n",
       "      <td>f</td>\n",
       "      <td>2013-05-08 13:18:21.233</td>\n",
       "      <td>12.75</td>\n",
       "      <td>2.25</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>We beleive in Education and felt this was a go...</td>\n",
       "      <td>485784e52a4c85ea6783285d09d8aea4</td>\n",
       "      <td>f8e49225a821cc04e1176303e55d89ec</td>\n",
       "      <td>Grades 3-5</td>\n",
       "      <td>Mathematics</td>\n",
       "      <td>t</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                         projectid  \\\n",
       "donationid                                                           \n",
       "44842bc73032bcc4b44f77dd9007a6eb  ffff2d9c769c8fb5335e949c615425eb   \n",
       "45b90c3d8cbf06a4c88410069c7fa9c7  ffff2d9c769c8fb5335e949c615425eb   \n",
       "9047579ce6a5c45abd801aa9e4c16bce  ffff2d9c769c8fb5335e949c615425eb   \n",
       "9e0f234dfa2982619dacd0635019d53d  ffff2d9c769c8fb5335e949c615425eb   \n",
       "fdcd840e3dd57016590cb8a69aed0c09  ffff2d9c769c8fb5335e949c615425eb   \n",
       "\n",
       "                                                      donor_acctid donor_city  \\\n",
       "donationid                                                                      \n",
       "44842bc73032bcc4b44f77dd9007a6eb  ca2858ac1c427f52e39ca066db0b50a1        NaN   \n",
       "45b90c3d8cbf06a4c88410069c7fa9c7  38fcf7f544893c94a77ec58d4729ffb8        NaN   \n",
       "9047579ce6a5c45abd801aa9e4c16bce  2f471330531031f76a0dd43c03096be5        NaN   \n",
       "9e0f234dfa2982619dacd0635019d53d  af1f03b856fc2979c01a6ad8b61f6fbe  Pocatello   \n",
       "fdcd840e3dd57016590cb8a69aed0c09  af1f03b856fc2979c01a6ad8b61f6fbe  Pocatello   \n",
       "\n",
       "                                 donor_state  donor_zip is_teacher_acct  \\\n",
       "donationid                                                                \n",
       "44842bc73032bcc4b44f77dd9007a6eb          ID      83201               f   \n",
       "45b90c3d8cbf06a4c88410069c7fa9c7          NY        NaN               f   \n",
       "9047579ce6a5c45abd801aa9e4c16bce          CO      80003               f   \n",
       "9e0f234dfa2982619dacd0635019d53d          ID      83201               f   \n",
       "fdcd840e3dd57016590cb8a69aed0c09          ID      83201               f   \n",
       "\n",
       "                                       donation_timestamp  \\\n",
       "donationid                                                  \n",
       "44842bc73032bcc4b44f77dd9007a6eb  2013-05-09 17:08:40.145   \n",
       "45b90c3d8cbf06a4c88410069c7fa9c7  2013-03-06 22:48:04.013   \n",
       "9047579ce6a5c45abd801aa9e4c16bce  2013-03-06 22:47:57.516   \n",
       "9e0f234dfa2982619dacd0635019d53d   2013-05-08 12:58:55.38   \n",
       "fdcd840e3dd57016590cb8a69aed0c09  2013-05-08 13:18:21.233   \n",
       "\n",
       "                                  donation_to_project  \\\n",
       "donationid                                              \n",
       "44842bc73032bcc4b44f77dd9007a6eb                58.65   \n",
       "45b90c3d8cbf06a4c88410069c7fa9c7                85.00   \n",
       "9047579ce6a5c45abd801aa9e4c16bce                85.00   \n",
       "9e0f234dfa2982619dacd0635019d53d                68.00   \n",
       "fdcd840e3dd57016590cb8a69aed0c09                12.75   \n",
       "\n",
       "                                  donation_optional_support  donation_total  \\\n",
       "donationid                                                                    \n",
       "44842bc73032bcc4b44f77dd9007a6eb                      10.35              69   \n",
       "45b90c3d8cbf06a4c88410069c7fa9c7                      15.00             100   \n",
       "9047579ce6a5c45abd801aa9e4c16bce                      15.00             100   \n",
       "9e0f234dfa2982619dacd0635019d53d                      12.00              80   \n",
       "fdcd840e3dd57016590cb8a69aed0c09                       2.25              15   \n",
       "\n",
       "                                     ...      \\\n",
       "donationid                           ...       \n",
       "44842bc73032bcc4b44f77dd9007a6eb     ...       \n",
       "45b90c3d8cbf06a4c88410069c7fa9c7     ...       \n",
       "9047579ce6a5c45abd801aa9e4c16bce     ...       \n",
       "9e0f234dfa2982619dacd0635019d53d     ...       \n",
       "fdcd840e3dd57016590cb8a69aed0c09     ...       \n",
       "\n",
       "                                 payment_included_web_purchased_gift_card  \\\n",
       "donationid                                                                  \n",
       "44842bc73032bcc4b44f77dd9007a6eb                                        f   \n",
       "45b90c3d8cbf06a4c88410069c7fa9c7                                        f   \n",
       "9047579ce6a5c45abd801aa9e4c16bce                                        f   \n",
       "9e0f234dfa2982619dacd0635019d53d                                        f   \n",
       "fdcd840e3dd57016590cb8a69aed0c09                                        f   \n",
       "\n",
       "                                 payment_was_promo_matched via_giving_page  \\\n",
       "donationid                                                                   \n",
       "44842bc73032bcc4b44f77dd9007a6eb                         t               t   \n",
       "45b90c3d8cbf06a4c88410069c7fa9c7                         f               t   \n",
       "9047579ce6a5c45abd801aa9e4c16bce                         t               t   \n",
       "9e0f234dfa2982619dacd0635019d53d                         f               f   \n",
       "fdcd840e3dd57016590cb8a69aed0c09                         f               f   \n",
       "\n",
       "                                 for_honoree  \\\n",
       "donationid                                     \n",
       "44842bc73032bcc4b44f77dd9007a6eb           f   \n",
       "45b90c3d8cbf06a4c88410069c7fa9c7           f   \n",
       "9047579ce6a5c45abd801aa9e4c16bce           f   \n",
       "9e0f234dfa2982619dacd0635019d53d           f   \n",
       "fdcd840e3dd57016590cb8a69aed0c09           f   \n",
       "\n",
       "                                                                   donation_message  \\\n",
       "donationid                                                                            \n",
       "44842bc73032bcc4b44f77dd9007a6eb  Education is the most important thing in the w...   \n",
       "45b90c3d8cbf06a4c88410069c7fa9c7  Matching donation by the DonorsChoose.org Boar...   \n",
       "9047579ce6a5c45abd801aa9e4c16bce       I gave because we should all pay it forward.   \n",
       "9e0f234dfa2982619dacd0635019d53d  Idaho Power Company believes in education and ...   \n",
       "fdcd840e3dd57016590cb8a69aed0c09  We beleive in Education and felt this was a go...   \n",
       "\n",
       "                                                    teacher_acctid  \\\n",
       "donationid                                                           \n",
       "44842bc73032bcc4b44f77dd9007a6eb  485784e52a4c85ea6783285d09d8aea4   \n",
       "45b90c3d8cbf06a4c88410069c7fa9c7  485784e52a4c85ea6783285d09d8aea4   \n",
       "9047579ce6a5c45abd801aa9e4c16bce  485784e52a4c85ea6783285d09d8aea4   \n",
       "9e0f234dfa2982619dacd0635019d53d  485784e52a4c85ea6783285d09d8aea4   \n",
       "fdcd840e3dd57016590cb8a69aed0c09  485784e52a4c85ea6783285d09d8aea4   \n",
       "\n",
       "                                                          schoolid  \\\n",
       "donationid                                                           \n",
       "44842bc73032bcc4b44f77dd9007a6eb  f8e49225a821cc04e1176303e55d89ec   \n",
       "45b90c3d8cbf06a4c88410069c7fa9c7  f8e49225a821cc04e1176303e55d89ec   \n",
       "9047579ce6a5c45abd801aa9e4c16bce  f8e49225a821cc04e1176303e55d89ec   \n",
       "9e0f234dfa2982619dacd0635019d53d  f8e49225a821cc04e1176303e55d89ec   \n",
       "fdcd840e3dd57016590cb8a69aed0c09  f8e49225a821cc04e1176303e55d89ec   \n",
       "\n",
       "                                 grade_level primary_focus_subject is_exciting  \n",
       "donationid                                                                      \n",
       "44842bc73032bcc4b44f77dd9007a6eb  Grades 3-5           Mathematics           t  \n",
       "45b90c3d8cbf06a4c88410069c7fa9c7  Grades 3-5           Mathematics           t  \n",
       "9047579ce6a5c45abd801aa9e4c16bce  Grades 3-5           Mathematics           t  \n",
       "9e0f234dfa2982619dacd0635019d53d  Grades 3-5           Mathematics           t  \n",
       "fdcd840e3dd57016590cb8a69aed0c09  Grades 3-5           Mathematics           t  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_donations.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def get_donations(group, prefix):\n",
    "    total_donated = group['donation_total'].sum()\n",
    "    donation_to_project = group['donation_to_project'].sum()\n",
    "    donation_optional_support = group['donation_optional_support'].sum()\n",
    "    donated_promo_matched_total = group[group['payment_was_promo_matched'] == \"t\"]['donation_total'].sum() \n",
    "    donated_for_honoree = group[group['for_honoree'] == \"t\"]['donation_total'].sum() \n",
    "    donated_for_exciting = group[group['is_exciting'] == \"t\"]['donation_total'].sum() \n",
    "    \n",
    "    columns = ['total_donated', 'donation_to_project', 'donation_optional_support', \n",
    "               'donated_promo_matched_total', 'donated_for_honoree', 'donated_for_exciting_project']\n",
    "    \n",
    "    columns = [prefix + '_' + column for column in columns]\n",
    "    \n",
    "    return pd.Series([total_donated, donation_to_project, donation_optional_support,\n",
    "                     donated_promo_matched_total, donated_for_honoree, donated_for_exciting], \n",
    "                     index=[columns])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "get_donations_to_teacher = lambda(group): get_donations(group, 'teacher')\n",
    "df_teacher_donations = df_donations.groupby('teacher_acctid').apply(get_donations_to_teacher)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "get_donations_to_school = lambda(group): get_donations(group, 'school')\n",
    "df_school_donations = df_donations.groupby('schoolid').apply(get_donations_to_school)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "get_donations_for_school_grades = lambda(group): get_donations(group, 'school_grades')\n",
    "df_donations_for_school_grades = df_donations.groupby(['schoolid', 'grade_level']).apply(get_donations_for_school_grades)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "get_donations_for_school_primary_subject = lambda(group): get_donations(group, 'primary_subject')\n",
    "df_donations_for_school_subject = df_donations.groupby(['schoolid', 'primary_focus_subject']).apply(get_donations_for_school_primary_subject)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_teacher_donations.to_csv(\"train-data/donations_to_teacher.csv\")\n",
    "df_school_donations.to_csv(\"train-data/donations_to_school.csv\")\n",
    "df_donations_for_school_grades.to_csv(\"train-data/donations_for_school_grades.csv\")\n",
    "df_donations_for_school_subject.to_csv(\"train-data/donations_for_school_subject.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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></th>\n",
       "      <th>primary_subjecttotal_donated</th>\n",
       "      <th>primary_subjectdonation_to_project</th>\n",
       "      <th>primary_subjectdonation_optional_support</th>\n",
       "      <th>primary_subjectdonated_promo_matched_total</th>\n",
       "      <th>primary_subjectdonated_for_honoree</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>schoolid</th>\n",
       "      <th>primary_focus_subject</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">0000efec25428001c9dd83f651c5d181</th>\n",
       "      <th>Foreign Languages</th>\n",
       "      <td>718.41</td>\n",
       "      <td>645.45</td>\n",
       "      <td>72.96</td>\n",
       "      <td>167</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Visual Arts</th>\n",
       "      <td>20.00</td>\n",
       "      <td>18.50</td>\n",
       "      <td>1.50</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">00014d8717dd762910c815aceb2e5521</th>\n",
       "      <th>Literature &amp; Writing</th>\n",
       "      <td>344.54</td>\n",
       "      <td>292.86</td>\n",
       "      <td>51.68</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Special Needs</th>\n",
       "      <td>999.63</td>\n",
       "      <td>852.51</td>\n",
       "      <td>147.12</td>\n",
       "      <td>295</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00079011fea3d16aa2d3a4025aae6db6</th>\n",
       "      <th>Environmental Science</th>\n",
       "      <td>2005.59</td>\n",
       "      <td>1704.75</td>\n",
       "      <td>300.84</td>\n",
       "      <td>1006</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                        primary_subjecttotal_donated  \\\n",
       "schoolid                         primary_focus_subject                                 \n",
       "0000efec25428001c9dd83f651c5d181 Foreign Languages                            718.41   \n",
       "                                 Visual Arts                                   20.00   \n",
       "00014d8717dd762910c815aceb2e5521 Literature & Writing                         344.54   \n",
       "                                 Special Needs                                999.63   \n",
       "00079011fea3d16aa2d3a4025aae6db6 Environmental Science                       2005.59   \n",
       "\n",
       "                                                        primary_subjectdonation_to_project  \\\n",
       "schoolid                         primary_focus_subject                                       \n",
       "0000efec25428001c9dd83f651c5d181 Foreign Languages                                  645.45   \n",
       "                                 Visual Arts                                         18.50   \n",
       "00014d8717dd762910c815aceb2e5521 Literature & Writing                               292.86   \n",
       "                                 Special Needs                                      852.51   \n",
       "00079011fea3d16aa2d3a4025aae6db6 Environmental Science                             1704.75   \n",
       "\n",
       "                                                        primary_subjectdonation_optional_support  \\\n",
       "schoolid                         primary_focus_subject                                             \n",
       "0000efec25428001c9dd83f651c5d181 Foreign Languages                                         72.96   \n",
       "                                 Visual Arts                                                1.50   \n",
       "00014d8717dd762910c815aceb2e5521 Literature & Writing                                      51.68   \n",
       "                                 Special Needs                                            147.12   \n",
       "00079011fea3d16aa2d3a4025aae6db6 Environmental Science                                    300.84   \n",
       "\n",
       "                                                        primary_subjectdonated_promo_matched_total  \\\n",
       "schoolid                         primary_focus_subject                                               \n",
       "0000efec25428001c9dd83f651c5d181 Foreign Languages                                             167   \n",
       "                                 Visual Arts                                                    10   \n",
       "00014d8717dd762910c815aceb2e5521 Literature & Writing                                            0   \n",
       "                                 Special Needs                                                 295   \n",
       "00079011fea3d16aa2d3a4025aae6db6 Environmental Science                                        1006   \n",
       "\n",
       "                                                        primary_subjectdonated_for_honoree  \n",
       "schoolid                         primary_focus_subject                                      \n",
       "0000efec25428001c9dd83f651c5d181 Foreign Languages                                       0  \n",
       "                                 Visual Arts                                             0  \n",
       "00014d8717dd762910c815aceb2e5521 Literature & Writing                                    0  \n",
       "                                 Special Needs                                           0  \n",
       "00079011fea3d16aa2d3a4025aae6db6 Environmental Science                                   0  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x`.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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>expences_per_student</th>\n",
       "      <th>school_region</th>\n",
       "      <th>main_topic</th>\n",
       "      <th>secondary_topic</th>\n",
       "      <th>topics_explained</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>projectid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>316ed8fb3b81402ff6ac8f721bb31192</th>\n",
       "      <td>17.369062</td>\n",
       "      <td>Far West</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>0.388444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90de744e368a7e4883223ca49318ae30</th>\n",
       "      <td>13.475909</td>\n",
       "      <td>Southwest</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>0.332286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32943bb1063267de6ed19fc0ceb4b9a7</th>\n",
       "      <td>25.346471</td>\n",
       "      <td>Far West</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.325069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bb18f409abda2f264d5acda8cab577a9</th>\n",
       "      <td>48.005833</td>\n",
       "      <td>Mideast</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.389604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24761b686e18e5eace634607acbcc19f</th>\n",
       "      <td>17.016667</td>\n",
       "      <td>Far West</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.466246</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  expences_per_student school_region  \\\n",
       "projectid                                                              \n",
       "316ed8fb3b81402ff6ac8f721bb31192             17.369062      Far West   \n",
       "90de744e368a7e4883223ca49318ae30             13.475909     Southwest   \n",
       "32943bb1063267de6ed19fc0ceb4b9a7             25.346471      Far West   \n",
       "bb18f409abda2f264d5acda8cab577a9             48.005833       Mideast   \n",
       "24761b686e18e5eace634607acbcc19f             17.016667      Far West   \n",
       "\n",
       "                                  main_topic  secondary_topic  \\\n",
       "projectid                                                       \n",
       "316ed8fb3b81402ff6ac8f721bb31192           0               15   \n",
       "90de744e368a7e4883223ca49318ae30           0                8   \n",
       "32943bb1063267de6ed19fc0ceb4b9a7           0                1   \n",
       "bb18f409abda2f264d5acda8cab577a9           0                1   \n",
       "24761b686e18e5eace634607acbcc19f           0                1   \n",
       "\n",
       "                                  topics_explained  \n",
       "projectid                                           \n",
       "316ed8fb3b81402ff6ac8f721bb31192          0.388444  \n",
       "90de744e368a7e4883223ca49318ae30          0.332286  \n",
       "32943bb1063267de6ed19fc0ceb4b9a7          0.325069  \n",
       "bb18f409abda2f264d5acda8cab577a9          0.389604  \n",
       "24761b686e18e5eace634607acbcc19f          0.466246  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df_additional_data = pd.read_csv(\"train-data/projects-additional.csv\",index_col=0)\n",
    "df_additional_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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></th>\n",
       "      <th>school_grades_total_donated</th>\n",
       "      <th>school_grades_donation_to_project</th>\n",
       "      <th>school_grades_donation_optional_support</th>\n",
       "      <th>school_grades_donated_promo_matched_total</th>\n",
       "      <th>school_grades_donated_for_honoree</th>\n",
       "      <th>school_grades_donated_for_exciting_project</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>schoolid</th>\n",
       "      <th>grade_level</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0000efec25428001c9dd83f651c5d181</th>\n",
       "      <th>Grades 9-12</th>\n",
       "      <td>738.41</td>\n",
       "      <td>663.95</td>\n",
       "      <td>74.46</td>\n",
       "      <td>177</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">00014d8717dd762910c815aceb2e5521</th>\n",
       "      <th>Grades 3-5</th>\n",
       "      <td>999.63</td>\n",
       "      <td>852.51</td>\n",
       "      <td>147.12</td>\n",
       "      <td>295</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Grades PreK-2</th>\n",
       "      <td>344.54</td>\n",
       "      <td>292.86</td>\n",
       "      <td>51.68</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">00079011fea3d16aa2d3a4025aae6db6</th>\n",
       "      <th>Grades 3-5</th>\n",
       "      <td>45.00</td>\n",
       "      <td>38.25</td>\n",
       "      <td>6.75</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Grades 6-8</th>\n",
       "      <td>3876.34</td>\n",
       "      <td>3294.88</td>\n",
       "      <td>581.46</td>\n",
       "      <td>1006</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                school_grades_total_donated  \\\n",
       "schoolid                         grade_level                                  \n",
       "0000efec25428001c9dd83f651c5d181 Grades 9-12                         738.41   \n",
       "00014d8717dd762910c815aceb2e5521 Grades 3-5                          999.63   \n",
       "                                 Grades PreK-2                       344.54   \n",
       "00079011fea3d16aa2d3a4025aae6db6 Grades 3-5                           45.00   \n",
       "                                 Grades 6-8                         3876.34   \n",
       "\n",
       "                                                school_grades_donation_to_project  \\\n",
       "schoolid                         grade_level                                        \n",
       "0000efec25428001c9dd83f651c5d181 Grades 9-12                               663.95   \n",
       "00014d8717dd762910c815aceb2e5521 Grades 3-5                                852.51   \n",
       "                                 Grades PreK-2                             292.86   \n",
       "00079011fea3d16aa2d3a4025aae6db6 Grades 3-5                                 38.25   \n",
       "                                 Grades 6-8                               3294.88   \n",
       "\n",
       "                                                school_grades_donation_optional_support  \\\n",
       "schoolid                         grade_level                                              \n",
       "0000efec25428001c9dd83f651c5d181 Grades 9-12                                      74.46   \n",
       "00014d8717dd762910c815aceb2e5521 Grades 3-5                                      147.12   \n",
       "                                 Grades PreK-2                                    51.68   \n",
       "00079011fea3d16aa2d3a4025aae6db6 Grades 3-5                                        6.75   \n",
       "                                 Grades 6-8                                      581.46   \n",
       "\n",
       "                                                school_grades_donated_promo_matched_total  \\\n",
       "schoolid                         grade_level                                                \n",
       "0000efec25428001c9dd83f651c5d181 Grades 9-12                                          177   \n",
       "00014d8717dd762910c815aceb2e5521 Grades 3-5                                           295   \n",
       "                                 Grades PreK-2                                          0   \n",
       "00079011fea3d16aa2d3a4025aae6db6 Grades 3-5                                             0   \n",
       "                                 Grades 6-8                                          1006   \n",
       "\n",
       "                                                school_grades_donated_for_honoree  \\\n",
       "schoolid                         grade_level                                        \n",
       "0000efec25428001c9dd83f651c5d181 Grades 9-12                                    0   \n",
       "00014d8717dd762910c815aceb2e5521 Grades 3-5                                     0   \n",
       "                                 Grades PreK-2                                  0   \n",
       "00079011fea3d16aa2d3a4025aae6db6 Grades 3-5                                     0   \n",
       "                                 Grades 6-8                                     0   \n",
       "\n",
       "                                                school_grades_donated_for_exciting_project  \n",
       "schoolid                         grade_level                                                \n",
       "0000efec25428001c9dd83f651c5d181 Grades 9-12                                             0  \n",
       "00014d8717dd762910c815aceb2e5521 Grades 3-5                                              0  \n",
       "                                 Grades PreK-2                                           0  \n",
       "00079011fea3d16aa2d3a4025aae6db6 Grades 3-5                                              0  \n",
       "                                 Grades 6-8                                              0  "
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df_donations_for_school_grades.to_csv(\"train-data/donations_for_school_grades.csv\")\n",
    "df_donations_for_school_grades.head()"
   ]
  }
 ],
 "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}