monitor.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "! pip install --quiet environs cyksuid toolz psycopg2-binary typing_json backoff xxhash pyyaml geopandas dtreeviz facebook_business"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      45.454545\n",
       "1     142.857143\n",
       "2      76.923077\n",
       "3      58.823529\n",
       "4     100.000000\n",
       "         ...    \n",
       "75     66.666667\n",
       "76     37.037037\n",
       "77     66.666667\n",
       "78    125.000000\n",
       "79     47.619048\n",
       "Name: cost_per_completion, Length: 80, dtype: float64"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('outs/ma.csv').sort_values('disthash').cost_per_completion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.read_csv('outs/dist_info.csv').sort_values('clusterid')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from environs import Env\n",
    "env = Env()\n",
    "env.read_env('.env-upswell-vlab', override=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from adopt.malaria import get_conf, get_df, lookalike, get_ad_token\n",
    "from adopt.marketing import Marketing\n",
    "from adopt.facebook.state import CampaignState\n",
    "\n",
    "# cnf = get_conf(env)\n",
    "# token = get_ad_token(cnf[\"survey_user\"], cnf[\"chatbase\"])\n",
    "\n",
    "# state = CampaignState(env, token)\n",
    "# m = Marketing(env, state)\n",
    "\n",
    "# TODO: make new strata format conf parse\n",
    "# TODO: make get_df work with new strata format\n",
    "# df = get_df(cnf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cnf = { 'chatbase':  {\n",
    "        \"db\": \"chatroach\",\n",
    "        \"user\": \"chatreader\",\n",
    "        \"host\": \"localhost\",\n",
    "        \"port\": \"5432\",\n",
    "        \"password\": None,\n",
    "    }}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "\n",
    "def get_dist_info(path):\n",
    "    geod = gpd.read_file(path)\n",
    "    district_info = geod.groupby('disthash').apply(lambda df: df.iloc[0][['disthash', 'tot_p']]).reset_index(drop=True)\n",
    "    return district_info\n",
    "\n",
    "def load_cities(path):\n",
    "    cities = pd.read_csv(path)\n",
    "    cities = cities[cities.rad >= 1.0]\n",
    "    return cities\n",
    "\n",
    "dist_info = get_dist_info('outs/cluster.shp')\n",
    "cities = load_cities('output/cities-22.csv')\n",
    "cities = cities.merge(dist_info, how='left', on='disthash')\n",
    "\n",
    "districts = cities.groupby('distname') \\\n",
    "                  .head(1) \\\n",
    "                  .reset_index(drop=True) \\\n",
    "                  .drop(columns=['rad', 'lng', 'lat', 'distcode', 'id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from adopt.responses import get_forms\n",
    "from datetime import datetime\n",
    "\n",
    "survey_user = '10383123-9fb1-429b-8312-447c1b54b17a'\n",
    "# shortcodes = [ 'follow1eng', 'follow1hin']\n",
    "shortcodes = [ 'baselineeng', 'baselinehin', 'baselineodi']\n",
    "\n",
    "timestamp = datetime(2020, 9, 1)\n",
    "\n",
    "forms = list(get_forms(survey_user, shortcodes, timestamp, cnf['chatbase']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "questions =[('malaria_now', '58583e72-7036-4c74-9ebd-179598cba59f'),\n",
    "             ('net_lastnight', '1f812d37-a7a5-400d-8a31-145fbb49f577'),]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "questions = [\n",
    "    ('dist_medical', 'd7573919-8a7e-457f-9a1d-1f8c389127a7'),\n",
    "    ('education', 'e40fa1c6-13a1-4a02-91cd-0eaade11864d'),\n",
    "    ('owns_net', 'bd4802c6-7bdb-40f0-aac1-18cc6df7da6e'),\n",
    "    ('malaria_incidence', 'f6e69027-97cc-494e-8d52-318b75047e23'),\n",
    "    ('malaria_now', 'dad23031-8468-4900-89cc-d01841d8b660'),\n",
    "    ('net_lastnight', 'b5b1ff58-c8df-4890-9b1c-0cd40ce6edc0'),\n",
    "    ('home', '4d0ae478-3893-4b46-ab39-d6848c69245d'), \n",
    "    ('occupation', '4fc929c7-132d-49b1-a164-515e5cc9064f')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from adopt.responses import get_response_df\n",
    "from datetime import datetime, timezone\n",
    "from adopt.clustering import only_target_users\n",
    "import pandas as pd\n",
    "from adopt.clustering import add_res_cols\n",
    "from adopt.forms import response_translator\n",
    "from adopt.malaria import shape_df\n",
    "import re\n",
    "\n",
    "\n",
    "def filter_time(df, min_date, max_date):\n",
    "    min_date = datetime(*min_date, tzinfo=timezone.utc)\n",
    "    max_date = datetime(*max_date, tzinfo=timezone.utc)\n",
    "\n",
    "    users = df[(df.timestamp > min_date) & (df.timestamp < max_date)].userid.unique()\n",
    "    return df[df.userid.isin(users)]\n",
    "\n",
    "def ref_translation(eng_form, other_form):\n",
    "    vals = [(a['ref'], b['ref']) for a, b \n",
    "            in zip(eng_form['fields'], other_form['fields'])]\n",
    "    lookup = dict(vals)\n",
    "    rev_lookup = {v:k for k,v in lookup.items()}\n",
    "    return lookup, rev_lookup\n",
    "\n",
    "\n",
    "def get_filtered_responses(survey_user, eng_form, other_form, shortcodes, questions, db_cnf):\n",
    "    ref_lookup, rev_ref_lookup = ref_translation(eng_form, other_form)\n",
    "    refs = [ref_lookup[r] for _, r in questions]\n",
    "    responses = get_response_df(survey_user, shortcodes, refs, db_cnf)\n",
    "\n",
    "    _, last_ref = questions[-1]\n",
    "    \n",
    "    answered = responses[responses.question_ref == last_ref].userid.unique()    \n",
    "\n",
    "    return responses[responses.userid.isin(answered)].reset_index(drop=True)\n",
    "\n",
    "def malaria_prob(groupby, key='malaria'):\n",
    "    d = groupby[key].value_counts(normalize=True)\n",
    "    d.name = 'count'\n",
    "    d = d.reset_index(level=-1)\n",
    "    d = d[d[key] == True].drop(columns=key)\n",
    "    return d\n",
    "\n",
    "\n",
    "def stats(df):\n",
    "    targets = df.kutcha.sum()\n",
    "    tot = df.shape[0]\n",
    "    non_target = tot - targets\n",
    "    return pd.Series([targets/tot, targets, non_target, tot], index=['perc', 'target', 'non_target', 'tot'])\n",
    "\n",
    "\n",
    "def col_translators(a, b, questions):\n",
    "    lookup, _ = ref_translation(a, b)\n",
    "\n",
    "    refs = [ref for _, ref in questions]\n",
    "    ts = { qt['ref']: response_translator(q, qt)\n",
    "           for qt, q in zip(a['fields'], b['fields'])\n",
    "           if qt['ref'] in refs}\n",
    "\n",
    "    return [(n, lookup[ref], ts[ref]) for n, ref in questions]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# filtered_res = get_filtered_responses(survey_user, forms[0], forms[1], ['follow1hin'], questions, cnf['chatbase'])\n",
    "filtered_res = get_filtered_responses(survey_user, forms[0], forms[1], ['baselinehin', 'baselinehinexc'], questions, cnf['chatbase'])\n",
    "\n",
    "col_names = col_translators(forms[0], forms[1], questions)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\n",
    "def shape_df(df):\n",
    "    df = df \\\n",
    "        .groupby(['surveyid', 'shortcode']) \\\n",
    "        .apply(lambda df: df.pivot('userid', 'question_ref', 'response')) \\\n",
    "        .reset_index()\n",
    "\n",
    "    df['timestamp'] = df['md:startTime'].map(lambda x: datetime.fromtimestamp(x/1000,\n",
    "                                                                              tz=timezone.utc))\n",
    "\n",
    "    # Clean anyone who answered multiple surveys in this group of shortcodes\n",
    "    # in theory should only be testers.\n",
    "    # Keep the lastest survey they took.\n",
    "    return df \\\n",
    "        .sort_values('timestamp') \\\n",
    "        .drop_duplicates(['userid'], keep='last')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "r = shape_df(filtered_res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "r[r.timestamp <= datetime(2021, 4, 30, tzinfo=timezone.utc)].userid.unique().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "r.surveyid.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "rr = add_res_cols(col_names, r) \\\n",
    "    .reset_index(drop=True) \\\n",
    "    .dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(23084,)"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rr.userid.unique().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dd = rr.merge(districts, left_on='md:clusterid', right_on='disthash')\n",
    "qrefs = [c for c in dd.columns if len(c) > 35]\n",
    "dd = dd.drop(columns=qrefs)\n",
    "\n",
    "dd['under_net'] = dd.net_lastnight == 'Yes'\n",
    "dd['malaria'] = dd.malaria_incidence == 'Yes'\n",
    "dd['malaria_now'] = dd.malaria_now == 'Yes'\n",
    "dd['kutcha'] = dd.home == 'Kutcha (made of mud, tin, straw)'\n",
    "dd['pucca'] = dd.home == 'Pucca (have cement/brick wall and floor'\n",
    "dd['university'] = dd.education == 'University degree or higher'\n",
    "dd['unemployed'] = dd.occupation == 'Unemployed'\n",
    "dd['disthash'] = dd['md:clusterid']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(22978,)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dd.userid.unique().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "perc = dd.groupby('disthash').apply(stats).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# treatment_assignment = pd.read_csv(\"outs/ma-with-treatment.csv\")[['disthash', 'treatment']]\n",
    "# dd.merge(treatment_assignment).to_csv('outs/individual-with-treatment.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(160, 19)"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filter_time(dd, (2020,8,24), (2020,8,25)).shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(204, 19)"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filter_time(dd, (2020,8,25), (2020,8,26)).shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(57, 19)"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filter_time(dd, (2020,8,26), (2020,8,27)).shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(223, 19)"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filter_time(dd, (2020,8,27), (2020,8,28)).shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1072, 19)"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filter_time(dd, (2020,8,28), (2020,8,29)).shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(383, 19)"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filter_time(dd, (2020,8,29), (2020,8,30)).shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(212, 19)"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filter_time(dd, (2020,8,30), (2020,9,1)).shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from adopt.clustering import get_budget_lookup\n",
    "from adopt.malaria import window, days_left, get_df, get_cluster_from_adset\n",
    "from adopt.facebook.state import CampaignState, BudgetWindow\n",
    "\n",
    "# w = BudgetWindow(datetime(2020,8,10), datetime.now())\n",
    "\n",
    "w = window(96)\n",
    "state = CampaignState(env, w)\n",
    "\n",
    "spend = {get_cluster_from_adset(n): i\n",
    "         for n, i in state.spend.items()}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "budget_lookup, prices = get_budget_lookup(df,\n",
    "                                          stratum,\n",
    "                                          float('inf'),\n",
    "                                          10000,\n",
    "                                          84,\n",
    "                                          6,\n",
    "                                          w,\n",
    "                                          spend, \n",
    "                                          return_price=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "bb = pd.Series(budget_lookup) / 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "with pd.option_context('display.max_rows', None):\n",
    "    display(bb.sort_values()[:80])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1460.1925714285717"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bb.sort_values()[:80].sum() / 70 * 12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def add_budget(r, lookup, key='budget'):\n",
    "    r[key] = lookup.get(r['disthash'], None)\n",
    "    return r\n",
    "\n",
    "perc = perc.apply(lambda r: add_budget(r, budget_lookup), 1)\n",
    "perc = perc.apply(lambda r: add_budget(r, prices, 'price'), 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "mn_lookup = dd.groupby('disthash').apply(lambda df: pd.Series({k: df[k].mean() for k in ['malaria_now', 'malaria']})).reset_index()\n",
    "perc = perc.merge(mn_lookup)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "excluded = cities[cities.creative_group == 'exclusions'].disthash.unique()\n",
    "perc['exclusion'] = perc.disthash.isin(excluded)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "audienced = cities[cities.include_audience == True].disthash.unique()\n",
    "perc['audienced'] = perc.disthash.isin(audienced)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "perc = perc.merge(dd.groupby('disthash').under_net.mean().reset_index(), how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "saturated = (only_target_users(df, surveys, 'target_questions')\n",
    "             .groupby('md:clusterid')\n",
    "             .userid.count() >= 250) \\\n",
    "             .reset_index() \\\n",
    "             .pipe(lambda df: df[df.userid == True])['md:clusterid']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# cities.loc[cities.disthash == 'c12daf4e', 'creative_group'] = 'exclusions'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "final_clusters = pd.read_csv('outs/final-clusters.csv').disthash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "perc['final'] = perc.disthash.isin(final_clusters)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "with pd.option_context('display.max_rows', None, 'display.expand_frame_repr', False):\n",
    "    display(perc.sort_values('tot'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "treatment_assignment = pd.read_csv(\"outs/ma-with-treatment.csv\")[['disthash', 'treatment']]\n",
    "# dd.merge(treatment_assignment).to_csv('outs/individual-with-treatment.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "perc = dd.groupby('disthash').apply(lambda df: pd.Series({'tot': df.shape[0]})).reset_index()\n",
    "treatment_assignment = pd.read_csv(\"outs/ma-with-treatment.csv\")[['disthash', 'treatment']]\n",
    "perc = perc.merge(treatment_assignment)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "with pd.option_context('display.max_rows', None, 'display.expand_frame_repr', False):\n",
    "    display(perc.sort_values('tot'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def matching_stats(df):\n",
    "    vals = {\n",
    "        'kutchas': df.kutcha.mean(),\n",
    "        'puccas': df.pucca.mean(),\n",
    "        'university': df.university.mean(),\n",
    "        'unemployed': df.unemployed.mean(),\n",
    "        'malaria': df.malaria.mean(),\n",
    "        'malaria_now': df.malaria_now.mean(),\n",
    "        'under_net': df.under_net.mean(),\n",
    "        'population': df.tot_p.max()\n",
    "    }\n",
    "\n",
    "    return pd.Series(vals)\n",
    "\n",
    "\n",
    "def get_cluster_from_adset(adset_name: str) -> str:\n",
    "    pat = r\"(?<=vlab-)\\w+\"\n",
    "    matches = re.search(pat, adset_name)\n",
    "    if not matches:\n",
    "        raise Exception(f\"Cannot extract cluster id from adset: {adset_name}\")\n",
    "\n",
    "    return matches[0]\n",
    "\n",
    "def prep_facebook_data(path, dd, final_clusters):\n",
    "    capture_count = dd[(dd.timestamp > datetime(2020,7,23,tzinfo=timezone.utc)) & \n",
    "                       (dd.timestamp < datetime(2020,7,24,tzinfo=timezone.utc))] \\\n",
    "        .groupby('disthash') \\\n",
    "        .userid.count() \\\n",
    "        .reset_index() \\\n",
    "        .rename(columns={'userid': 'completed_survey'})\n",
    "\n",
    "    df = pd.read_csv(path)\n",
    "    df['disthash'] = df['Ad Set Name'].map(get_cluster_from_adset)\n",
    "    df = df.merge(capture_count, how='left')\n",
    "    df = df[df.disthash.isin(final_clusters)].reset_index(drop=True)\n",
    "\n",
    "    spend = 'Amount Spent (INR)'\n",
    "    df['cost_per_completion'] = df.apply(lambda r: r[spend] / r.completed_survey, 1)\n",
    "    df = df.rename(columns = {'CTR (All)': 'CTR', \n",
    "                              'Cost per Results': 'cost_per_message', \n",
    "                              'CPM (Cost per 1,000 Impressions) (INR)': 'CPM'})\n",
    "\n",
    "    return df[['disthash', 'cost_per_completion', 'cost_per_message', 'CTR', 'CPM']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dd = dd.rename(columns={'tot_p_x': 'tot_p'})\n",
    "final_clusters = pd.read_csv('outs/final-clusters.csv').disthash\n",
    "\n",
    "fb = prep_facebook_data('outs/fb-export-vlab-1.csv', dd, final_clusters)\n",
    "\n",
    "ma = dd.groupby('disthash').apply(matching_stats).reset_index()\n",
    "ma = ma[ma.disthash.isin(final_clusters)]\n",
    "ma.loc[ma.population.isna(), 'population'] = ma.population.quantile(.2) # random data filling :/\n",
    "\n",
    "ma = ma.merge(fb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      45.454545\n",
       "1     142.857143\n",
       "2      76.923077\n",
       "3      55.555556\n",
       "4      90.909091\n",
       "         ...    \n",
       "75     66.666667\n",
       "76     37.037037\n",
       "77     66.666667\n",
       "78    125.000000\n",
       "79     45.454545\n",
       "Name: cost_per_completion, Length: 80, dtype: float64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ma.sort_values('disthash').cost_per_completion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "perc['saturated'] = perc.disthash.isin(saturated)\n",
    "\n",
    "perc_stats = perc[['disthash', 'tot', 'audienced', 'exclusion', 'price', 'saturated']].rename(columns={'tot': 'current_total'})\n",
    "\n",
    "treatment_assignment = pd.read_csv('outs/ma-with-treatment.csv')[['disthash', 'treatment']]\n",
    "perc = perc.merge(treatment_assignment, how='left')\n",
    "\n",
    "ma.merge(treatment_assignment).merge(perc_stats).to_csv('outs/ma-3.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# perc.groupby('treatment').under_net.mean()\n",
    "# perc['nn'] = perc.apply(lambda r: max(0, (250 - r.tot)) * r.under_net, 1)\n",
    "# perc.groupby('treatment').nn.sum()\n",
    "# dd.merge(treatment_assignment).groupby('treatment').under_net.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# saturated = perc[perc.non_target > 175].disthash\n",
    "# audienced = perc[(perc.perc < .3) & ((perc.disthash.isin(saturated) | (perc.price < 30000)))].disthash\n",
    "# cities['creative_group'] = 'hindi'\n",
    "# cities.loc[cities.disthash.isin(saturated), 'creative_group'] = 'exclusions'\n",
    "# cities['include_audience'] = False\n",
    "# cities.loc[cities.disthash.isin(audienced), 'include_audience'] = True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tot = 196952 + 17682 + 40222 + 89997 + 113061 + 50145 + 141910 + 4255509 + 46000 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tot / 70"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "argv": [
    "python",
    "-m",
    "ipykernel_launcher",
    "-f",
    "{connection_file}"
   ],
   "display_name": "Python 3",
   "env": null,
   "interrupt_mode": "signal",
   "language": "python",
   "metadata": null,
   "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.8.2"
  },
  "name": "monitor.ipynb"
 },
 "nbformat": 4,
 "nbformat_minor": 4
}