Monthly Member EDA.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Monthly Member EDA\n",
    "Brittany Bennett  \n",
    "January 2019\n",
    "\n",
    "I am so excited to work on the monthly member program this year. Monthly member programs are so fun because of the high reward: every donor you land has the potential to give for months, or years. The revenue potential is just so much greater than one-time small dollar giving. In fact, I believe that the purpose of your small dollar giving program is to feed people into your monthly giving program.  \n",
    "\n",
    "This document outlines my preliminary analysis of the monthly member program. To begin, I seek to explore:  \n",
    "\n",
    "* Who are our top monthly members? What makes them special?\n",
    "* How has the number of monthly members changed over time?\n",
    "* What is our retention rate month to month and year to year?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the necessary packages\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from plotSlope import slope\n",
    "import random\n",
    "import plotly.plotly as py\n",
    "import plotly.figure_factory as ff\n",
    "import seaborn as sns\n",
    "from plotSlope import slope\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read in the data\n",
    "data = pd.read_csv(\"data/monthly.csv\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Contact Name</th>\n",
       "      <th>Count</th>\n",
       "      <th>Contribution ID</th>\n",
       "      <th>Received On</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Designation</th>\n",
       "      <th>Contribution Type</th>\n",
       "      <th>Cycle</th>\n",
       "      <th>Period</th>\n",
       "      <th>Contribution.Note</th>\n",
       "      <th>Source Code Path</th>\n",
       "      <th>Contact ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3344</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VNW16EPKCJ9</td>\n",
       "      <td>2016-08-15</td>\n",
       "      <td>5.0</td>\n",
       "      <td>New Era Colorado Foundation</td>\n",
       "      <td>Monetary Contribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c3 Donors/Player contribution</td>\n",
       "      <td>VNX0EGHJ7D6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3903</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VNW16E2V624</td>\n",
       "      <td>2015-11-15</td>\n",
       "      <td>5.0</td>\n",
       "      <td>New Era Colorado Foundation</td>\n",
       "      <td>Monetary Contribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Primary</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c3 Donors/Player contribution</td>\n",
       "      <td>VNX0EGH8D82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3353</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VNW16DM0RJ5</td>\n",
       "      <td>2015-02-15</td>\n",
       "      <td>5.0</td>\n",
       "      <td>New Era Colorado Foundation</td>\n",
       "      <td>Monetary Contribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c3 Donors/Player contribution</td>\n",
       "      <td>VNX0EGJ17E3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3353</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VNW16DQ6KD7</td>\n",
       "      <td>2015-05-15</td>\n",
       "      <td>5.0</td>\n",
       "      <td>New Era Colorado Foundation</td>\n",
       "      <td>Monetary Contribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c3 Donors/Player contribution</td>\n",
       "      <td>VNX0EGJ17E3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3353</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VNW16DN7177</td>\n",
       "      <td>2014-12-15</td>\n",
       "      <td>5.0</td>\n",
       "      <td>New Era Colorado Foundation</td>\n",
       "      <td>Monetary Contribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c3 Donors/Player contribution</td>\n",
       "      <td>VNX0EGJ17E3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Contact Name  Count Contribution ID Received On  Amount  \\\n",
       "0           3344    NaN     VNW16EPKCJ9  2016-08-15     5.0   \n",
       "1           3903    NaN     VNW16E2V624  2015-11-15     5.0   \n",
       "2           3353    NaN     VNW16DM0RJ5  2015-02-15     5.0   \n",
       "3           3353    NaN     VNW16DQ6KD7  2015-05-15     5.0   \n",
       "4           3353    NaN     VNW16DN7177  2014-12-15     5.0   \n",
       "\n",
       "                   Designation      Contribution Type  Cycle   Period  \\\n",
       "0  New Era Colorado Foundation  Monetary Contribution    NaN      NaN   \n",
       "1  New Era Colorado Foundation  Monetary Contribution    NaN  Primary   \n",
       "2  New Era Colorado Foundation  Monetary Contribution    NaN      NaN   \n",
       "3  New Era Colorado Foundation  Monetary Contribution    NaN      NaN   \n",
       "4  New Era Colorado Foundation  Monetary Contribution    NaN      NaN   \n",
       "\n",
       "   Contribution.Note               Source Code Path   Contact ID  \n",
       "0                NaN  c3 Donors/Player contribution  VNX0EGHJ7D6  \n",
       "1                NaN  c3 Donors/Player contribution  VNX0EGH8D82  \n",
       "2                NaN  c3 Donors/Player contribution  VNX0EGJ17E3  \n",
       "3                NaN  c3 Donors/Player contribution  VNX0EGJ17E3  \n",
       "4                NaN  c3 Donors/Player contribution  VNX0EGJ17E3  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>lifetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3086</td>\n",
       "      <td>4755.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>795</td>\n",
       "      <td>3475.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7206</td>\n",
       "      <td>2545.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>629</td>\n",
       "      <td>2400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4374</td>\n",
       "      <td>1820.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name  lifetime\n",
       "0  3086    4755.0\n",
       "1   795    3475.0\n",
       "2  7206    2545.0\n",
       "3   629    2400.0\n",
       "4  4374    1820.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a dataframe of our monthly donors by lifetime value\n",
    "lifetime = data.groupby([' Contact Name'])['Amount'].sum().sort_values(ascending = False).to_frame().reset_index()\n",
    "lifetime.columns = [\"name\", \"lifetime\"]\n",
    "lifetime.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'3344'"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.iloc[0,0].astype('str')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a dictionary of dataframes of all gifts for each monthly donor\n",
    "dicts = {}\n",
    "\n",
    "for index, row in data.iterrows():\n",
    "    name = data.iloc[index, 0]\n",
    "    name_df = name.astype('str')\n",
    "    name_df = name_df.lower()\n",
    "    df = data.loc[data[\" Contact Name\"] == name]\n",
    "    df[\"Received On\"] = pd.to_datetime(df['Received On']).apply(lambda x: x.date())\n",
    "    df = df.set_index(\"Received On\")\n",
    "    DF = pd.DataFrame()\n",
    "    DF['amount'] = df[\"Amount\"]\n",
    "    DF = DF.set_index(df.index).sort_values(by = [\"Received On\"])\n",
    "    dicts[name_df] = DF\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "#find the unique gift amounts for each donor\n",
    "slopes = {}\n",
    "for name in dicts.keys():\n",
    "    slopes[name] = dicts[name].amount.unique()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6414</th>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>3.00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>18.00</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7350</th>\n",
       "      <td>25.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5591</th>\n",
       "      <td>20.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698</th>\n",
       "      <td>10.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>7.04</td>\n",
       "      <td>20.04</td>\n",
       "      <td>25.04</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4570</th>\n",
       "      <td>5.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>10.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6664</th>\n",
       "      <td>5.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>10.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005</th>\n",
       "      <td>5.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>10.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>10.00</td>\n",
       "      <td>12.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7206</th>\n",
       "      <td>120.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>10.00</td>\n",
       "      <td>25.00</td>\n",
       "      <td>50.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7139</th>\n",
       "      <td>5.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>10.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2507</th>\n",
       "      <td>5.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>12.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5604</th>\n",
       "      <td>10.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>13.00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5908</th>\n",
       "      <td>25.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>15.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4571</th>\n",
       "      <td>20.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536</th>\n",
       "      <td>10.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>15.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6570</th>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>17.00</td>\n",
       "      <td>15.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2122</th>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>18.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2462</th>\n",
       "      <td>5.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>20.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1487</th>\n",
       "      <td>10.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>20.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6571</th>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>20.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2208</th>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>20.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6895</th>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>21.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2982</th>\n",
       "      <td>10.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>25.00</td>\n",
       "      <td>30.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2877</th>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>40.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4374</th>\n",
       "      <td>25.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>45.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>410</th>\n",
       "      <td>15.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>50.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3086</th>\n",
       "      <td>25.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>50.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>795</th>\n",
       "      <td>100.0</td>\n",
       "      <td>125.0</td>\n",
       "      <td>225.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3344</th>\n",
       "      <td>5.0</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>3903</th>\n",
       "      <td>5.0</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>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3305</th>\n",
       "      <td>10.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5054</th>\n",
       "      <td>10.0</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>4616</th>\n",
       "      <td>5.0</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>722</th>\n",
       "      <td>50.0</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>1389</th>\n",
       "      <td>25.0</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>234</th>\n",
       "      <td>10.0</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>4827</th>\n",
       "      <td>20.0</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>4239</th>\n",
       "      <td>10.0</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>537</th>\n",
       "      <td>5.0</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>595</th>\n",
       "      <td>10.0</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>6693</th>\n",
       "      <td>10.0</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>3672</th>\n",
       "      <td>20.0</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>7569</th>\n",
       "      <td>5.0</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>5715</th>\n",
       "      <td>5.0</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>1836</th>\n",
       "      <td>5.0</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>1603</th>\n",
       "      <td>5.0</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>6972</th>\n",
       "      <td>10.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3722</th>\n",
       "      <td>15.0</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>2556</th>\n",
       "      <td>10.0</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>4707</th>\n",
       "      <td>10.0</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>4590</th>\n",
       "      <td>15.0</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>2068</th>\n",
       "      <td>15.0</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>692</th>\n",
       "      <td>5.0</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>6605</th>\n",
       "      <td>250.0</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>286</th>\n",
       "      <td>10.0</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>7183</th>\n",
       "      <td>5.0</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>6052</th>\n",
       "      <td>5.0</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>6967</th>\n",
       "      <td>10.0</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>4127</th>\n",
       "      <td>10.0</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>378</th>\n",
       "      <td>10.0</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",
       "<p>517 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          0      1       2      3      4     5\n",
       "6414    5.0   10.0    3.00  15.00  18.00  20.0\n",
       "7350   25.0   10.0    5.00    NaN    NaN   NaN\n",
       "5591   20.0   10.0    5.00    NaN    NaN   NaN\n",
       "698    10.0   13.0    7.04  20.04  25.04  40.0\n",
       "4570    5.0   15.0   10.00    NaN    NaN   NaN\n",
       "6664    5.0   15.0   10.00    NaN    NaN   NaN\n",
       "2005    5.0   50.0   10.00    NaN    NaN   NaN\n",
       "4       5.0    8.0   10.00  12.00    NaN   NaN\n",
       "7206  120.0   20.0   10.00  25.00  50.00   NaN\n",
       "7139    5.0    8.0   10.00    NaN    NaN   NaN\n",
       "2507    5.0    8.0   12.00    NaN    NaN   NaN\n",
       "5604   10.0    3.0   13.00  15.00    NaN   NaN\n",
       "5908   25.0   40.0   15.00    NaN    NaN   NaN\n",
       "4571   20.0   10.0   15.00    NaN    NaN   NaN\n",
       "536    10.0   14.0   15.00    NaN    NaN   NaN\n",
       "6570   10.0   12.0   17.00  15.00    NaN   NaN\n",
       "2122   10.0   15.0   18.00    NaN    NaN   NaN\n",
       "2462    5.0   15.0   20.00    NaN    NaN   NaN\n",
       "1487   10.0   25.0   20.00    NaN    NaN   NaN\n",
       "6571    5.0   10.0   20.00    NaN    NaN   NaN\n",
       "2208   10.0   15.0   20.00    NaN    NaN   NaN\n",
       "6895    5.0   10.0   21.00    NaN    NaN   NaN\n",
       "2982   10.0   20.0   25.00  30.00    NaN   NaN\n",
       "2877    5.0   10.0   40.00    NaN    NaN   NaN\n",
       "4374   25.0   20.0   45.00    NaN    NaN   NaN\n",
       "410    15.0   25.0   50.00    NaN    NaN   NaN\n",
       "3086   25.0   35.0   50.00    NaN    NaN   NaN\n",
       "795   100.0  125.0  225.00    NaN    NaN   NaN\n",
       "3344    5.0    NaN     NaN    NaN    NaN   NaN\n",
       "3903    5.0    NaN     NaN    NaN    NaN   NaN\n",
       "...     ...    ...     ...    ...    ...   ...\n",
       "3305   10.0    5.0     NaN    NaN    NaN   NaN\n",
       "5054   10.0    NaN     NaN    NaN    NaN   NaN\n",
       "4616    5.0    NaN     NaN    NaN    NaN   NaN\n",
       "722    50.0    NaN     NaN    NaN    NaN   NaN\n",
       "1389   25.0    NaN     NaN    NaN    NaN   NaN\n",
       "234    10.0    NaN     NaN    NaN    NaN   NaN\n",
       "4827   20.0    NaN     NaN    NaN    NaN   NaN\n",
       "4239   10.0    NaN     NaN    NaN    NaN   NaN\n",
       "537     5.0    NaN     NaN    NaN    NaN   NaN\n",
       "595    10.0    NaN     NaN    NaN    NaN   NaN\n",
       "6693   10.0    NaN     NaN    NaN    NaN   NaN\n",
       "3672   20.0    NaN     NaN    NaN    NaN   NaN\n",
       "7569    5.0    NaN     NaN    NaN    NaN   NaN\n",
       "5715    5.0    NaN     NaN    NaN    NaN   NaN\n",
       "1836    5.0    NaN     NaN    NaN    NaN   NaN\n",
       "1603    5.0    NaN     NaN    NaN    NaN   NaN\n",
       "6972   10.0   20.0     NaN    NaN    NaN   NaN\n",
       "3722   15.0    NaN     NaN    NaN    NaN   NaN\n",
       "2556   10.0    NaN     NaN    NaN    NaN   NaN\n",
       "4707   10.0    NaN     NaN    NaN    NaN   NaN\n",
       "4590   15.0    NaN     NaN    NaN    NaN   NaN\n",
       "2068   15.0    NaN     NaN    NaN    NaN   NaN\n",
       "692     5.0    NaN     NaN    NaN    NaN   NaN\n",
       "6605  250.0    NaN     NaN    NaN    NaN   NaN\n",
       "286    10.0    NaN     NaN    NaN    NaN   NaN\n",
       "7183    5.0    NaN     NaN    NaN    NaN   NaN\n",
       "6052    5.0    NaN     NaN    NaN    NaN   NaN\n",
       "6967   10.0    NaN     NaN    NaN    NaN   NaN\n",
       "4127   10.0    NaN     NaN    NaN    NaN   NaN\n",
       "378    10.0    NaN     NaN    NaN    NaN   NaN\n",
       "\n",
       "[517 rows x 6 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a datafram showing the change in gift size for each donor\n",
    "data = pd.DataFrame(dict([ (k,pd.Series(v)) for k,v in slopes.items() ]))\n",
    "data = data.T\n",
    "data.sort_values(by = 2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Time Spent as a Monthly Member"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "code.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "code.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "summary = code.groupby(\" Contact Name_y\").agg({'Amount_x' : np.mean,'Received On' : [np.min, np.max]})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "summary.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "donations = code.groupby(\" Contact Name_y\").agg({'Amount_x' : [np.min, np.max]})\n",
    "donations.columns = donations.columns.get_level_values(0)\n",
    "summary = summary.merge(donations, left_on = \" Contact Name_y\", right_on = \" Contact Name_y\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "summary.columns = [\"mean\", \"start\", \"end\", 'start_gift', 'end_gift' ]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "summary.loc[:,'start'] = pd.to_datetime(summary['start'], format='%Y-%m-%d')\n",
    "summary.loc[:,'end'] = pd.to_datetime(summary['end'], format='%Y-%m-%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "summary[\"duration\"] = summary['end'] - summary['start']\n",
    "summary = summary.reset_index()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "summary = summary.merge(lifetime, left_on=\" Contact Name_y\", right_on = \"name\")\n",
    "summary.sort_values(\"duration\", ascending = False).head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = summary.sort_values(\"duration\", ascending = True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(20,60))\n",
    "n = len(df)\n",
    "colors = plt.cm.jet(np.linspace(0,1,n))\n",
    "for i in range(len(df)):\n",
    "    plt.plot((df.iloc[i,2], df.iloc[i,3]), (i, i) ,color=colors[i],  marker='o')\n",
    "plt.savefig(\"duration.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "code[\"Received On\"] = pd.to_datetime(code['Received On']).apply(lambda x: x.date())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "code['month'] = code['Received On'].map(lambda x: x.month)\n",
    "\n",
    "code['year'] = code['Received On'].map(lambda x: x.year)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pivot_df = code.pivot_table(values='Amount_x', index=' Contact Name_y',\n",
    "                                columns=['year', 'month'])\n",
    "pivot_df = pivot_df.fillna(0)\n",
    "pivot_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "total = pivot_df.merge(df, left_on = \" Contact Name_y\", right_on = \" Contact Name_y\")\n",
    "total = total.set_index(\" Contact Name_y\")\n",
    "\n",
    "x = df.loc[df[\"end_gift\"] >= 75]\n",
    "total = total.drop(x[\" Contact Name_y\"])\n",
    "total = total.sort_values(['start', \"end_gift\"], ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(40,60))\n",
    "ax = sns.heatmap(total.iloc[:,:107],cmap=\"hot_r\")\n",
    "plt.savefig(\"heatmap.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[df[\"start_gift\"] == 100]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}