03_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Analysis of Euros for Doctors workshop at DataHarvest 2017\n",
    "\n",
    "Run the load_data notebook before to get the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "from ipywidgets import interact\n",
    "\n",
    "from eurosfordoctors import checks\n",
    "from eurosfordoctors import utils\n",
    "\n",
    "\n",
    "MONEY_FIELDS = utils.MONEY_FIELDS\n",
    "MONEY_FIELDS_ONLY = utils.MONEY_FIELDS_ONLY\n",
    "DOC_MONEY_FIELDS = list(set(MONEY_FIELDS_ONLY) - {'sponsorship', 'donations_grants'})\n",
    "MONEY_FIELDS_DIRTY = ['%s_dirty' % x for x in MONEY_FIELDS_ONLY]\n",
    "\n",
    "pd.set_option('display.max_rows', 999)\n",
    "pd.set_option('display.float_format', lambda x: '{0:,.2f}'.format(x))\n",
    "\n",
    "CURRENCY = 'PLN'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>address</th>\n",
       "      <th>base_country</th>\n",
       "      <th>clean_name</th>\n",
       "      <th>company</th>\n",
       "      <th>company_doc_id</th>\n",
       "      <th>country</th>\n",
       "      <th>currency</th>\n",
       "      <th>donations_grants</th>\n",
       "      <th>donations_grants_dirty</th>\n",
       "      <th>...</th>\n",
       "      <th>travel_accommodation</th>\n",
       "      <th>travel_accommodation_dirty</th>\n",
       "      <th>type</th>\n",
       "      <th>uci</th>\n",
       "      <th>uid</th>\n",
       "      <th>year</th>\n",
       "      <th>computed_total</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "      <th>uid_original</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>al.Wojska Polskiego 30</td>\n",
       "      <td>PL</td>\n",
       "      <td>tomasz-aleksiejczyk</td>\n",
       "      <td>abbvie</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>...</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>hcp</td>\n",
       "      <td></td>\n",
       "      <td>659935bb-7b00-4bba-9be2-22a36b764ace</td>\n",
       "      <td>2015</td>\n",
       "      <td>2,600.00</td>\n",
       "      <td>53.80</td>\n",
       "      <td>20.48</td>\n",
       "      <td>659935bb-7b00-4bba-9be2-22a36b764ace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>ul.Szpitalna 27/33</td>\n",
       "      <td>PL</td>\n",
       "      <td>alicja-bartkowska-sniatkowska</td>\n",
       "      <td>abbvie</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>...</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>hcp</td>\n",
       "      <td></td>\n",
       "      <td>72da1abc-64bf-4750-b09b-bd1d6db01625</td>\n",
       "      <td>2015</td>\n",
       "      <td>1,849.00</td>\n",
       "      <td>52.41</td>\n",
       "      <td>16.88</td>\n",
       "      <td>72da1abc-64bf-4750-b09b-bd1d6db01625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>ul.Roentgena 5</td>\n",
       "      <td>PL</td>\n",
       "      <td>witold-bartnik</td>\n",
       "      <td>abbvie</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>...</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>hcp</td>\n",
       "      <td></td>\n",
       "      <td>d0af9ef0-d840-40f2-a2b3-076993d5fe58</td>\n",
       "      <td>2015</td>\n",
       "      <td>4,030.00</td>\n",
       "      <td>52.15</td>\n",
       "      <td>21.03</td>\n",
       "      <td>d0af9ef0-d840-40f2-a2b3-076993d5fe58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>ul.Zjednoczenia 10</td>\n",
       "      <td>PL</td>\n",
       "      <td>marek-beniowski</td>\n",
       "      <td>abbvie</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>...</td>\n",
       "      <td>9,517.00</td>\n",
       "      <td>9,517.00</td>\n",
       "      <td>hcp</td>\n",
       "      <td></td>\n",
       "      <td>0435e01e-af47-4c69-b29c-f917cc012285</td>\n",
       "      <td>2015</td>\n",
       "      <td>13,272.00</td>\n",
       "      <td>50.29</td>\n",
       "      <td>18.95</td>\n",
       "      <td>0435e01e-af47-4c69-b29c-f917cc012285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>ul. 3 Maja 13/15</td>\n",
       "      <td>PL</td>\n",
       "      <td>szymon-bialka</td>\n",
       "      <td>abbvie</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>...</td>\n",
       "      <td>154.00</td>\n",
       "      <td>154.00</td>\n",
       "      <td>hcp</td>\n",
       "      <td></td>\n",
       "      <td>e090df0e-5669-435e-bd15-5a1ae474fbad</td>\n",
       "      <td>2015</td>\n",
       "      <td>154.00</td>\n",
       "      <td>50.30</td>\n",
       "      <td>18.79</td>\n",
       "      <td>e090df0e-5669-435e-bd15-5a1ae474fbad</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 39 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index                 address base_country                     clean_name  \\\n",
       "0      0  al.Wojska Polskiego 30           PL            tomasz-aleksiejczyk   \n",
       "1      1      ul.Szpitalna 27/33           PL  alicja-bartkowska-sniatkowska   \n",
       "2      2          ul.Roentgena 5           PL                 witold-bartnik   \n",
       "3      3      ul.Zjednoczenia 10           PL                marek-beniowski   \n",
       "4      4        ul. 3 Maja 13/15           PL                  szymon-bialka   \n",
       "\n",
       "  company company_doc_id country currency  donations_grants  \\\n",
       "0  abbvie            NaN      PL      NaN               nan   \n",
       "1  abbvie            NaN      PL      NaN               nan   \n",
       "2  abbvie            NaN      PL      NaN               nan   \n",
       "3  abbvie            NaN      PL      NaN               nan   \n",
       "4  abbvie            NaN      PL      NaN               nan   \n",
       "\n",
       "   donations_grants_dirty                  ...                   \\\n",
       "0                     nan                  ...                    \n",
       "1                     nan                  ...                    \n",
       "2                     nan                  ...                    \n",
       "3                     nan                  ...                    \n",
       "4                     nan                  ...                    \n",
       "\n",
       "   travel_accommodation  travel_accommodation_dirty type  uci  \\\n",
       "0                   nan                         nan  hcp        \n",
       "1                   nan                         nan  hcp        \n",
       "2                   nan                         nan  hcp        \n",
       "3              9,517.00                    9,517.00  hcp        \n",
       "4                154.00                      154.00  hcp        \n",
       "\n",
       "                                    uid  year computed_total   lat   lng  \\\n",
       "0  659935bb-7b00-4bba-9be2-22a36b764ace  2015       2,600.00 53.80 20.48   \n",
       "1  72da1abc-64bf-4750-b09b-bd1d6db01625  2015       1,849.00 52.41 16.88   \n",
       "2  d0af9ef0-d840-40f2-a2b3-076993d5fe58  2015       4,030.00 52.15 21.03   \n",
       "3  0435e01e-af47-4c69-b29c-f917cc012285  2015      13,272.00 50.29 18.95   \n",
       "4  e090df0e-5669-435e-bd15-5a1ae474fbad  2015         154.00 50.30 18.79   \n",
       "\n",
       "                           uid_original  \n",
       "0  659935bb-7b00-4bba-9be2-22a36b764ace  \n",
       "1  72da1abc-64bf-4750-b09b-bd1d6db01625  \n",
       "2  d0af9ef0-d840-40f2-a2b3-076993d5fe58  \n",
       "3  0435e01e-af47-4c69-b29c-f917cc012285  \n",
       "4  e090df0e-5669-435e-bd15-5a1ae474fbad  \n",
       "\n",
       "[5 rows x 39 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/geocoded.csv', converters={'postcode': str, 'uci': str}, encoding='utf-8')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "394"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df[MONEY_FIELDS_ONLY] = df[MONEY_FIELDS_ONLY].applymap(lambda x: x if pd.isnull(x) else (np.nan if x == 0.0 else x))\n",
    "df['computed_total'] = df[MONEY_FIELDS_ONLY].sum(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>abbvie</td>\n",
       "      <td>13,023,266.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bayer</td>\n",
       "      <td>11,203,961.44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  company        amount\n",
       "0  abbvie 13,023,266.00\n",
       "1   bayer 11,203,961.44"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "company_df = pd.read_csv('data/pl/companies.csv')\n",
    "company_df = company_df[company_df['amount_rd'].notnull()]\n",
    "company_df = company_df.rename(columns={'amount_rd': 'amount', 'slug': 'company'})[['company', 'amount']]\n",
    "company_df['amount'] = pd.to_numeric(company_df['amount'])\n",
    "company_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company</th>\n",
       "      <th>year</th>\n",
       "      <th>type</th>\n",
       "      <th>label</th>\n",
       "      <th>count</th>\n",
       "      <th>amount</th>\n",
       "      <th>percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>abbvie</td>\n",
       "      <td>2015</td>\n",
       "      <td>hcp</td>\n",
       "      <td>registration_fees</td>\n",
       "      <td>757</td>\n",
       "      <td>1,077,571.00</td>\n",
       "      <td>96.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>abbvie</td>\n",
       "      <td>2015</td>\n",
       "      <td>hcp</td>\n",
       "      <td>travel_accommodation</td>\n",
       "      <td>891</td>\n",
       "      <td>2,323,174.00</td>\n",
       "      <td>94.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>abbvie</td>\n",
       "      <td>2015</td>\n",
       "      <td>hcp</td>\n",
       "      <td>fees</td>\n",
       "      <td>321</td>\n",
       "      <td>956,372.00</td>\n",
       "      <td>93.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>abbvie</td>\n",
       "      <td>2015</td>\n",
       "      <td>hcp</td>\n",
       "      <td>related_expenses</td>\n",
       "      <td>42</td>\n",
       "      <td>68,248.00</td>\n",
       "      <td>95.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>abbvie</td>\n",
       "      <td>2015</td>\n",
       "      <td>hco</td>\n",
       "      <td>donations_grants</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  company  year type                 label  count       amount  percent\n",
       "0  abbvie  2015  hcp     registration_fees    757 1,077,571.00    96.00\n",
       "1  abbvie  2015  hcp  travel_accommodation    891 2,323,174.00    94.00\n",
       "2  abbvie  2015  hcp                  fees    321   956,372.00    93.00\n",
       "3  abbvie  2015  hcp      related_expenses     42    68,248.00    95.00\n",
       "4  abbvie  2015  hco      donations_grants      0         0.00     0.00"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_df = pd.read_csv('data/pl/aggregated.csv').rename(columns={'slug': 'company'})\n",
    "agg_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Questions\n",
    "\n",
    "### 1: How many doctors have received money?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "mean_labels_per_doc = df[df['type'] == 'hcp'].groupby(['company', 'uid']).apply(lambda x: x[MONEY_FIELDS_ONLY].count().sum()).unstack().mean(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "company\n",
       "abbvie   1.41\n",
       "bayer    1.42\n",
       "dtype: float64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mean_labels_per_doc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "mean_labels_per_org = df[df['type'] == 'hco'].groupby(['company', 'uid']).apply(lambda x: x[MONEY_FIELDS_ONLY].count().sum()).unstack().mean(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Named HCP 145\n",
      "Named HCO 241\n"
     ]
    }
   ],
   "source": [
    "individual_count = len(df[df['type'] == 'hcp'].groupby('uid'))\n",
    "individual_org_count = len(df[df['type'] == 'hco'].groupby('uid'))\n",
    "print('Named HCP', individual_count)\n",
    "print('Named HCO', individual_org_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "summed_agg_docs = agg_df[agg_df['type'] == 'hcp'].groupby(['company'])['count'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.4183712121212122"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "avg_mean_labels_per_doc = mean_labels_per_doc.mean()\n",
    "avg_mean_labels_per_doc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "avg_mean_labels_per_org = mean_labels_per_org.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>count</th>\n",
       "      <th>estimate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>company</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>abbvie</th>\n",
       "      <td>1.41</td>\n",
       "      <td>2011</td>\n",
       "      <td>1,423.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bayer</th>\n",
       "      <td>1.42</td>\n",
       "      <td>2175</td>\n",
       "      <td>1,527.13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           0  count  estimate\n",
       "company                      \n",
       "abbvie  1.41   2011  1,423.72\n",
       "bayer   1.42   2175  1,527.13"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "estimated_agg_docs_per_company_df = pd.merge(mean_labels_per_doc.to_frame(), summed_agg_docs.to_frame(), how='outer',\n",
    "                                             left_index=True, right_index=True)\n",
    "\n",
    "estimated_agg_docs_per_company_df[0] = estimated_agg_docs_per_company_df[0].fillna(avg_mean_labels_per_doc)\n",
    "estimated_agg_docs_per_company_df['estimate'] = estimated_agg_docs_per_company_df['count'] / estimated_agg_docs_per_company_df[0]\n",
    "estimated_agg_docs_per_company_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4186"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "estimated_agg_docs_per_company_df['count'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.4183712121212122"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "estimated_agg_docs_per_company_df[0].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "summed_agg_orgs = agg_df[agg_df['type'] == 'hco'].groupby(['company'])['count'].sum()\n",
    "\n",
    "estimated_agg_orgs_per_company_df = pd.merge(mean_labels_per_org.to_frame(), summed_agg_orgs.to_frame(), how='outer',\n",
    "                                             left_index=True, right_index=True)\n",
    "estimated_agg_orgs_per_company_df\n",
    "estimated_agg_orgs_per_company_df[0] = estimated_agg_orgs_per_company_df[0].fillna(avg_mean_labels_per_org)\n",
    "estimated_agg_orgs_per_company_df['estimate'] = estimated_agg_orgs_per_company_df['count'] / estimated_agg_orgs_per_company_df[0]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0068965517241379"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_docs_by_company_count = df[df['type'] == 'hcp'].groupby(['uid', 'company']).size().unstack().sum(1)\n",
    "average_company_count_for_doc = top_docs_by_company_count.mean()\n",
    "average_company_count_for_doc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0290456431535269"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "average_company_count_for_org = df[df['type'] == 'hco'].groupby(['uid', 'company']).size().unstack().sum(1).mean()\n",
    "average_company_count_for_org"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2950.84447373376"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "estimated_agg_docs_per_company_df['estimate'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Estimated total count of HCP: 3076.0\n",
      "Estimated total count of HCO: 830.0\n"
     ]
    }
   ],
   "source": [
    "total_estimate_agg_docs = estimated_agg_docs_per_company_df['estimate'].sum()\n",
    "estimated_agg_doc_count = round(total_estimate_agg_docs / average_company_count_for_doc)\n",
    "estimated_agg_org_count = round(estimated_agg_orgs_per_company_df['estimate'].sum() / average_company_count_for_org)\n",
    "estimated_total_doc_count = estimated_agg_doc_count + individual_count\n",
    "estimated_total_org_count = estimated_agg_org_count + individual_org_count\n",
    "print('Estimated total count of HCP:', estimated_total_doc_count)\n",
    "print('Estimated total count of HCO:', estimated_total_org_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6d5e682efefd4c7ab2897bec59acc6ae"
      }
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "total_agg_amount = agg_df[agg_df['type'] == 'hcp']['amount'].sum()\n",
    "total_ind_amount = df[df['type'] == 'hcp']['computed_total'].sum()\n",
    "\n",
    "@interact(x=(1.0,55.0,0.2))\n",
    "def estime_doc_count(x=1.2):\n",
    "    count = round(total_estimate_agg_docs / x)\n",
    "    print('Estimated total count of HCP: %d (%f companies per HCP)' % (count + individual_count, x))\n",
    "    print('Average for named HCP: %d %s' % (total_ind_amount / individual_count, CURRENCY))\n",
    "    print('Average for aggregated HCP: %d %s' % (total_agg_amount / count, CURRENCY))\n",
    "    return"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2: How many HCP are known by name?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "145"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "individual_count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2 a) How many HCO are known by name?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "241"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "individual_org_count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3: How many HCP of all HCPs that got money are known by name?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4.71 %\n"
     ]
    }
   ],
   "source": [
    "print(round(individual_count / estimated_total_doc_count * 100, 2), '%')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3a) How many HCO of all HCOs that got money are known by name?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "29.04 %\n"
     ]
    }
   ],
   "source": [
    "print(round(individual_org_count / estimated_total_org_count * 100, 2), '%')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3b) How much HCP money went to named HCP?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sum of money for named HCP: 419,063.00 PLN\n",
      "Sum of money for aggregated HCP: 8,574,003.68 PLN\n",
      "Total: 8,993,066.68 PLN\n",
      "Percent named HCP amount to total: 4.66 %\n"
     ]
    }
   ],
   "source": [
    "individual_hcp_amount = df[df['type'] == 'hcp']['computed_total'].sum()\n",
    "agg_hcp_amount = agg_df[agg_df['type'] == 'hcp']['amount'].sum()\n",
    "print('Sum of money for named HCP: {:,.2f} {}'.format(individual_hcp_amount, CURRENCY))\n",
    "print('Sum of money for aggregated HCP: {:,.2f} {}'.format(agg_hcp_amount, CURRENCY))\n",
    "total_hcp_amount = individual_hcp_amount + agg_hcp_amount\n",
    "print('Total: {:,.2f} {}'.format(total_hcp_amount, CURRENCY))\n",
    "print('Percent named HCP amount to total:', round(individual_hcp_amount / total_hcp_amount * 100, 2), '%')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3c) How much HCO money went to named HCO?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sum for named HCO: 7,339,682.00 PLN\n",
      "Sum for aggregated HCO: 2,181,808.78 PLN\n",
      "Total HCO sum: 9,521,490.78 PLN\n",
      "Percent named HCO amount of total: 77.09 %\n"
     ]
    }
   ],
   "source": [
    "individual_hco_amount = df[df['type'] == 'hco']['computed_total'].sum()\n",
    "agg_hco_amount = agg_df[agg_df['type'] == 'hco']['amount'].sum()\n",
    "print('Sum for named HCO: {:,.2f} {}'.format(individual_hco_amount, CURRENCY))\n",
    "print('Sum for aggregated HCO: {:,.2f} {}'.format(agg_hco_amount, CURRENCY))\n",
    "total_hco_amount = individual_hco_amount + agg_hco_amount\n",
    "print('Total HCO sum: {:,.2f} {}'.format(total_hco_amount, CURRENCY))\n",
    "print('Percent named HCO amount of total:', round(individual_hco_amount / total_hco_amount * 100, 2), '%')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4: How much money have HCP gotten on average?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Individual Total Amount\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "type\n",
       "hco   7,339,682.00\n",
       "hcp     419,063.00\n",
       "dtype: float64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "individual_total_amount_by_type = df.groupby('type')[MONEY_FIELDS_ONLY].sum().sum(1)\n",
    "print('Individual Total Amount')\n",
    "individual_total_amount_by_type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Aggregated Total Amount\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "type\n",
       "hco   2,181,808.78\n",
       "hcp   8,574,003.68\n",
       "Name: amount, dtype: float64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_total_amount_by_type = agg_df.groupby('type')['amount'].sum()\n",
    "print('Aggregated Total Amount')\n",
    "agg_total_amount_by_type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sum of Total Amounts\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "type\n",
       "hco   9,521,490.78\n",
       "hcp   8,993,066.68\n",
       "dtype: float64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_amount_by_type = individual_total_amount_by_type + agg_total_amount_by_type\n",
    "print('Sum of Total Amounts')\n",
    "total_amount_by_type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average amount per HCP 2924.0 PLN\n"
     ]
    }
   ],
   "source": [
    "print('Average amount per HCP', round(total_amount_by_type['hcp'] / estimated_total_doc_count), CURRENCY)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average amount per HCO 11472.0 PLN\n"
     ]
    }
   ],
   "source": [
    "print('Average amount per HCO', round(total_amount_by_type['hco'] / estimated_total_org_count), CURRENCY)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5: How much money do named HCP get on average?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2890.0"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round(df[df['type'] == 'hcp']['computed_total'].sum() / individual_count)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6: Top 10 named HCP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Grażyna Cholewińska-Szymańska earned 24638.0\n",
      "Konrad Rejdak earned 20907.0\n",
      "Stanisław Sierakowski earned 15226.0\n",
      "Jacek Tadeusz Lelakowski earned 15000.0\n",
      "Andrzej Szczudlik earned 14659.0\n",
      "Tomasz Chorągiewicz earned 14615.0\n",
      "Marek Beniowski earned 13272.0\n",
      "Agnieszka Dębska-Cichońska earned 11725.0\n",
      "Tomasz Pytrus earned 11528.0\n",
      "Wesam Hussein Taslaq earned 10615.0\n"
     ]
    }
   ],
   "source": [
    "top_docs = df[df['type'] == 'hcp'].groupby(['uid'])['computed_total'].sum().sort_values(ascending=False).head(10)\n",
    "\n",
    "for uid, amount in top_docs.iteritems():\n",
    "    m = df[df['uid'] == uid].iloc[0]\n",
    "    print(m['name'], 'earned', amount)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>first_name</th>\n",
       "      <th>address</th>\n",
       "      <th>location</th>\n",
       "      <th>computed_total</th>\n",
       "      <th>uid</th>\n",
       "      <th>company</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [name, first_name, address, location, computed_total, uid, company]\n",
       "Index: []"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['name'].str.contains('Grunze')][['name', 'first_name', 'address', 'location', 'computed_total', 'uid', 'company']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7: Which HCP got most in each category?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Anna Dusza (ul.Hoża 19, Warszawa) got 3288.0 PLN in related_expenses.\n",
      "Tomasz Chorągiewicz (ul.Chmielna 1, Lublin) got 9900.0 PLN in registration_fees.\n",
      "Stanisław Sierakowski (ul.Skłodowskiej-Curie 24A, Białystok) got 12756.0 PLN in travel_accommodation.\n",
      "Konrad Rejdak (ul.Grażyny 18, Lublin) got 20000.0 PLN in fees.\n"
     ]
    }
   ],
   "source": [
    "label_sum_per_doc = df[df['type'] == 'hcp'].groupby('uid')[MONEY_FIELDS_ONLY].sum()\n",
    "for k in MONEY_FIELDS_ONLY:\n",
    "    idx = label_sum_per_doc[k].idxmax()\n",
    "    if pd.isnull(idx):\n",
    "        continue\n",
    "    doc = df[df['uid'] == label_sum_per_doc[k].idxmax()].iloc[0]\n",
    "    print('{name} ({address}, {location}) got {amount} {currency} in {cat}.'.format(\n",
    "            cat=k, name=doc['name'], address=doc['address'], location=doc['location'], currency=CURRENCY,\n",
    "            amount=round(label_sum_per_doc[k].max(), 2)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8: Which pharma company paid the most to doctors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "doc_total_by_company = df[df['type'] == 'hcp'].groupby('company')['computed_total'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "doc_agg_total_by_company = agg_df[agg_df['type'] == 'hcp'].groupby('company')['amount'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>rank</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>company</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>abbvie</th>\n",
       "      <td>4,611,690.00</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bayer</th>\n",
       "      <td>4,381,376.68</td>\n",
       "      <td>2.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   0  rank\n",
       "company                   \n",
       "abbvie  4,611,690.00  1.00\n",
       "bayer   4,381,376.68  2.00"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "doc_total_by_company = doc_total_by_company.add(doc_agg_total_by_company, fill_value=0)\n",
    "doc_total_by_company = doc_total_by_company.sort_values(ascending=False).to_frame()\n",
    "doc_total_by_company['rank'] = doc_total_by_company.rank(ascending=False)\n",
    "doc_total_by_company"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8 b) Total amount of money to doctors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8,993,066.68 €\n"
     ]
    }
   ],
   "source": [
    "print('{0:,.2f} €'.format(\n",
    "    doc_total_by_company[0].sum()\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8 c) Amount for research\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "24,227,227.44 €\n"
     ]
    }
   ],
   "source": [
    "print('{0:,.2f} €'.format(\n",
    "    company_df.groupby('company')['amount'].sum().sum()\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8 d) Total amounts per company"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>rank</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>company</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>abbvie</th>\n",
       "      <td>23,191,967.00</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bayer</th>\n",
       "      <td>19,549,817.90</td>\n",
       "      <td>2.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    0  rank\n",
       "company                    \n",
       "abbvie  23,191,967.00  1.00\n",
       "bayer   19,549,817.90  2.00"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ind_by_company = df.groupby('company')['computed_total'].sum()\n",
    "agg_by_company = agg_df.groupby('company')['amount'].sum()\n",
    "rnd_by_company = company_df.groupby('company')['amount'].sum()\n",
    "\n",
    "total_with_rnd_by_company = ind_by_company.add(agg_by_company, fill_value=0).add(rnd_by_company, fill_value=0)\n",
    "total_with_rnd_by_company = total_with_rnd_by_company.sort_values(ascending=False).to_frame()\n",
    "total_with_rnd_by_company['rank'] = total_with_rnd_by_company.rank(ascending=False)\n",
    "total_with_rnd_by_company"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "42,741,784.90 PLN\n"
     ]
    }
   ],
   "source": [
    "print('{:,.2f} {}'.format(\n",
    "    total_with_rnd_by_company[0].sum(), CURRENCY\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9: Which pharma company has the highest payment to HCPs on average?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>rank</th>\n",
       "      <th>estimated_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>company</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>abbvie</th>\n",
       "      <td>3,066.86</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1,503.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bayer</th>\n",
       "      <td>2,750.17</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1,593.13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               0  rank  estimated_count\n",
       "company                                \n",
       "abbvie  3,066.86  1.00         1,503.72\n",
       "bayer   2,750.17  2.00         1,593.13"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count_docs_per_company = df[df['type'] == 'hcp'].groupby(['company', 'uid']).size().unstack().sum(1)\n",
    "estimated_total_docs_per_company = count_docs_per_company + estimated_agg_docs_per_company_df['estimate']\n",
    "average_amount_per_doc_per_company = (doc_total_by_company[0] / estimated_total_docs_per_company).to_frame()\n",
    "average_amount_per_doc_per_company = average_amount_per_doc_per_company.sort_values(0, ascending=False)\n",
    "average_amount_per_doc_per_company['rank'] = average_amount_per_doc_per_company.rank(ascending=False)\n",
    "average_amount_per_doc_per_company['estimated_count'] = estimated_total_docs_per_company\n",
    "average_amount_per_doc_per_company"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10: Which HCP got money from the most companies?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "top_docs_by_company_count = top_docs_by_company_count.sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Piotr Landowski ul.Nowe Ogrody 1-6 Gdańsk 2.0\n",
      "company\n",
      "abbvie     258.00\n",
      "bayer    1,000.00\n",
      "Name: computed_total, dtype: float64\n",
      "Total 1258.0\n",
      "--------------------\n",
      "Marcin Hawro ul.Paderewskiego 5 Łańcut 1.0\n",
      "company\n",
      "abbvie   554.00\n",
      "Name: computed_total, dtype: float64\n",
      "Total 554.0\n",
      "--------------------\n",
      "Jerzy Paleczny ul.Wyzwolenia 18 Bielsko-Biała 1.0\n",
      "company\n",
      "abbvie   3,019.00\n",
      "Name: computed_total, dtype: float64\n",
      "Total 3019.0\n",
      "--------------------\n",
      "Tomasz Chorągiewicz ul.Chmielna 1 Lublin 1.0\n",
      "company\n",
      "bayer   14,615.00\n",
      "Name: computed_total, dtype: float64\n",
      "Total 14615.0\n",
      "--------------------\n",
      "Arkadiusz Kazimierczak Przecław 49a/5 Przecław 1.0\n",
      "company\n",
      "bayer   4,501.00\n",
      "Name: computed_total, dtype: float64\n",
      "Total 4501.0\n",
      "--------------------\n",
      "Marta Wilkowska-Trojniel Ul.Stołeczna 7 l. 9 Białystok 1.0\n",
      "company\n",
      "bayer   1,450.00\n",
      "Name: computed_total, dtype: float64\n",
      "Total 1450.0\n",
      "--------------------\n"
     ]
    }
   ],
   "source": [
    "for i, count in top_docs_by_company_count.head(6).iteritems():\n",
    "    matches = df[df['uid'] == i]\n",
    "    company_sums = matches.groupby('company')['computed_total'].sum()\n",
    "    total_sums = company_sums.sum()\n",
    "    doc = matches.iloc[0]\n",
    "    print(doc['name'], doc['address'], doc['location'], count)\n",
    "    print(company_sums)\n",
    "    print('Total', total_sums)\n",
    "    print('-' * 20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 11: Which pharma companies have a low rate of publication among HCPs?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>published_docs</th>\n",
       "      <th>estimate_non_published_docs</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>company</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>bayer</th>\n",
       "      <td>4.14</td>\n",
       "      <td>66.00</td>\n",
       "      <td>1,527.13</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>abbvie</th>\n",
       "      <td>5.32</td>\n",
       "      <td>80.00</td>\n",
       "      <td>1,423.72</td>\n",
       "      <td>2.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           0  published_docs  estimate_non_published_docs  rank\n",
       "company                                                        \n",
       "bayer   4.14           66.00                     1,527.13  1.00\n",
       "abbvie  5.32           80.00                     1,423.72  2.00"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_docs_per_company = estimated_agg_docs_per_company_df['estimate'] + count_docs_per_company\n",
    "publication_percentage = (count_docs_per_company / total_docs_per_company * 100).to_frame()\n",
    "publication_percentage['published_docs'] = count_docs_per_company\n",
    "publication_percentage['estimate_non_published_docs'] = estimated_agg_docs_per_company_df['estimate']\n",
    "publication_percentage = publication_percentage.sort_values(0)\n",
    "publication_percentage['rank'] = publication_percentage[0].rank()\n",
    "publication_percentage.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 12: Which pharma companies have a high rate of publication among HCPs?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>published_docs</th>\n",
       "      <th>estimate_non_published_docs</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>company</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>abbvie</th>\n",
       "      <td>5.32</td>\n",
       "      <td>80.00</td>\n",
       "      <td>1,423.72</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bayer</th>\n",
       "      <td>4.14</td>\n",
       "      <td>66.00</td>\n",
       "      <td>1,527.13</td>\n",
       "      <td>2.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           0  published_docs  estimate_non_published_docs  rank\n",
       "company                                                        \n",
       "abbvie  5.32           80.00                     1,423.72  1.00\n",
       "bayer   4.14           66.00                     1,527.13  2.00"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "publication_percentage = publication_percentage.sort_values(0, ascending=False)\n",
    "publication_percentage['rank'] = publication_percentage[0].rank(ascending=False)\n",
    "publication_percentage.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4.7314724207964716"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "publication_percentage[0].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Deciles for HCO computed total"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.10    1,000.00\n",
       "0.20    2,000.00\n",
       "0.30    3,011.50\n",
       "0.40    5,000.00\n",
       "0.50    8,000.00\n",
       "0.60   10,000.00\n",
       "0.70   16,533.60\n",
       "0.80   29,200.00\n",
       "0.90   60,117.30\n",
       "Name: computed_total, dtype: float64"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['type'] == 'hco']['computed_total'].quantile([x / 10 for x in range(1, 10)])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Gender split\n",
    "\n",
    "(if available)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series([], Name: computed_total, dtype: float64)"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('gender')['computed_total'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Percentage of 'real' doctors \n",
    "\n",
    "How many have a 'Dr.' degree."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "company\n",
       "abbvie    0.00\n",
       "bayer    42.42\n",
       "dtype: float64"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['has_doc'] = df['title'].str.contains('Dr|med')\n",
    "is_hcp = df['type'] == 'hcp'\n",
    "doc_percent = (df[is_hcp & df['has_doc']].groupby('company').size() / df[is_hcp].groupby('company').size() * 100).fillna(0)\n",
    "doc_percent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['bayer']"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "companies_with_titles = list(doc_percent.where(doc_percent > 0).dropna().index)\n",
    "companies_with_titles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "66"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "entries_with_possible_titles = df[df['company'].isin(companies_with_titles) & is_hcp]\n",
    "len(entries_with_possible_titles)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "HCPs that can have a title attached 66\n"
     ]
    }
   ],
   "source": [
    "hcps_with_possible_titles = entries_with_possible_titles.groupby('uid')\n",
    "print('HCPs that can have a title attached', len(hcps_with_possible_titles))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "HCPs that have a Dr title 28 , that is  42.4242424242 %\n"
     ]
    }
   ],
   "source": [
    "docs_with_titles = hcps_with_possible_titles['has_doc'].any().sum()\n",
    "print('HCPs that have a Dr title', docs_with_titles, ', that is ', docs_with_titles / len(hcps_with_possible_titles) * 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.0"
  },
  "widgets": {
   "state": {
    "12ac0ccc583d4b40a87e510b364c7be1": {
     "views": [
      {
       "cell_index": 23
      }
     ]
    }
   },
   "version": "1.2.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}