civiActivityReport_selectBestTrans.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "c4ddab6c",
   "metadata": {},
   "source": [
    "CIVI Report location: https://plum.greenehillfood.coop/civicrm/report/instance/101?reset=1&output=criteria <br> Select Activity Details - Membership Related"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "bde8f3ce-3a1e-4e65-b6c6-4b1bc3240b51",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import numpy as np\n",
    "import re\n",
    "import datetime\n",
    "import itertools\n",
    "from datetimerange import DateTimeRange\n",
    "import pickle\n",
    "import json\n",
    "from container_credentials import return_credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "25999b01-b7c2-4bac-8fdd-3bfb19df1e6f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/home/mofongo/Documents/ghfc/membershipReportsCIVI/greeneHill'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.getcwd()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "8a2f815e",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#os.chdir('/home/candela/Documents/greeneHill/membershipReportsCIVI')\n",
    "os.chdir('/home/mofongo/Documents/ghfc/membershipReportsCIVI/membershipReportingLogicSampleReports')\n",
    "#/home/candela/Documents/greeneHill/membershipReportsCIVI/membershipReportingLogicSampleReports"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "de52be19",
   "metadata": {},
   "source": [
    "Key 'activity reports' to import: 'selectActivityReport_20231112.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1e76ccc5-ecf3-4fa3-8ec6-56555f272227",
   "metadata": {},
   "outputs": [],
   "source": [
    "#activityReport = pd.read_csv('./selectActivityReport_20241013.csv')\n",
    "activityReport = pd.read_csv('./selectActivityReport_10072024.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "bf7b39b3-b410-46ef-a18d-6b0db053855e",
   "metadata": {},
   "outputs": [],
   "source": [
    "#data processing/grooming step\n",
    "activityReport.columns = [i.replace(' ','_')+'_act' for i in list(activityReport.columns)]\n",
    "#trim all string fields\n",
    "#strip all whitespace from each cell\n",
    "activityReport = activityReport.map(lambda x: x.strip() if isinstance(x,str) else x)\n",
    "#NOTE: Activity_Date_act field DOES NOT provide seconds\n",
    "activityReport = activityReport.assign(Activity_Date_DT_act = pd.to_datetime(activityReport['Activity_Date_act'], format = '%Y-%m-%d %H:%M'))\n",
    "activityReport.drop_duplicates(inplace=True, ignore_index = True, subset = ['Activity_Type_act', 'Subject_act', 'Activity_Date_act', 'Activity_Status_act', 'Activity_Date_DT_act','Target_Email_act'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8bcf169",
   "metadata": {},
   "source": [
    "### SELECT BEST TRANSACTION LOGIC\n",
    "This has been moved to civiActivityReport_selectBestTrans.py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a46199aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "#NOTE: real dupes have multiple entries on fields: 'Target_Email_act','Activity_Date_DT_act','Activity_Type_act'\n",
    "#NOTE 'Activity_Type_act' = specifies if change is for status or type\n",
    "# looks like the index is preserved on a groupby\n",
    "df_grouped = activityReport.groupby(['Target_Email_act','Activity_Date_DT_act','Activity_Type_act']).filter(lambda x: len(x) > 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "bdf51974",
   "metadata": {},
   "outputs": [],
   "source": [
    "#assign a row value to ea group member: used later for selection\n",
    "df_grouped['count'] = df_grouped.groupby(['Target_Email_act','Activity_Date_DT_act','Activity_Type_act']).cumcount()+1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92fcc000",
   "metadata": {},
   "source": [
    "Issues to handle:\n",
    "- multiple records for different versions of Target_Name_act (yet Target_Email_act is the same) <- will need to check that this doesn't delete Family account members\n",
    "- records made by different Source_Email_act ie systematic records made by CIVI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "392055d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_grouped['from'] = df_grouped['Subject_act'].str.extract(r'from\\s(\\w+)') #str.extract(r'from\\s(\\w+)')\n",
    "df_grouped['to'] = df_grouped['Subject_act'].str.extract(r'to\\s(\\w+)') #str.extract(r'to\\s(\\w+)')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b7ca315",
   "metadata": {},
   "source": [
    "The record to keep is that where the \"From\" = \"To\" of the companion line of the group. There are only at most two entries with the same Start_dt, so I only need to worry about passing back the derived \"To\" and \"From\" fields twice. Either of row = 1 or row = 2 can be the best one to keep. Essentially I'm searching for the best determinite record of the status going forward, ignoring the journey (implying that accurate status is more important than accurate/comprehensive journey).\n",
    "\n",
    "Will need to offset forwards and backwards. Ea row will then have a pair of offset values (from the row above and the one beneath). Depending on the \"count\" value (ie 1,2), only one from the pair will be relevant and tested to determine the \"best\" and \"final\" value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "9bf4f975",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_grouped.sort_values(['Target_Email_act','Activity_Type_act','Activity_Date_DT_act'],inplace= True)\n",
    "# -1 = the row below (lead); +1 = the row above (lagged)\n",
    "df_grouped[['from_-1','from_1']] = df_grouped['from'].shift(periods = [-1,1])\n",
    "df_grouped[['to_-1','to_1']] = df_grouped['to'].shift(periods = [-1,1])\n",
    "\n",
    "#do the same for Subject_act: this will help me sift through cases where Trial expirations are conflicting with new trial or membership starts (some kind of rollover - ex. taylor.m.posey@outlook.com)\n",
    "df_grouped[['Subject_act_-1','Subject_act_1']] = df_grouped['Subject_act'].shift(periods = [-1,1])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77122855",
   "metadata": {},
   "source": [
    "Handle cases where the -1 or +1 shift are irrelevant: have to do with different email addresses, as detectable by the 'count' field (ie 1,2)\n",
    "This will actually be handled by a filter"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b66ad7d1",
   "metadata": {},
   "source": [
    "## Print out the below to prove that some same-day transactions just reverse each other out and that the fix should be handled further upstream with Merch Committee"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "c537fb70",
   "metadata": {},
   "outputs": [],
   "source": [
    "#inspection:\n",
    "#df_grouped.to_csv('/home/mofongo/Documents/ghfc/membershipReportsCIVI/grouped_df_dedupWork.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "992b447d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_grouped.reset_index(names = 'index', inplace= True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "800fd9ba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(159, 20)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_grouped.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "578bb177",
   "metadata": {},
   "outputs": [],
   "source": [
    "#ideally just delete the irrelevant records: will need to identify the 'keep' and 'delete' records, specifically relying on index\n",
    "#CASE WHERE ROW NUMBER =1 THEN WE DON'T CARE ABOUT LAGGED DATA (ie only pertinent data is suffix = -1)\n",
    "#CASE WHERE ROW NUMBER =2 THEN WE DON'T CARE ABOUT LEAD DATA (ie only pertinent data is suffix = 1)\n",
    "#REVERSING TRANSACTIONS: DELETE/DROP BOTH\n",
    "#an example\n",
    "#df_grouped.loc[df_grouped['Activity_Type_act'] != 'Membership Signup',['Target_Email_act','Activity_Date_DT_act','Subject_act','Subject_act_-1','Subject_act_1','count','from', 'to', 'from_-1', 'from_1','to_-1', 'to_1']].head(5)\n",
    "# CASE WHERE ROW NUMBER = 1 AND to_-1 from -1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d6aee77",
   "metadata": {},
   "source": [
    "Select logic in words:\n",
    "- if row = 1 THEN real case: from_-1 <> to AND to_-1 = from (choose this row and discard where row = 2)\n",
    "- if row = 2 THEN real case: to_1 = from AND from_1 <> to (choose this row and discard where row = 1)\n",
    "- case where two status updates are made at the same time that don't conform to the above two logic statements: choose whichever row DOES NOT contain \"Expired\"\n",
    "- if Activity_Type_act = 'Membership Signup' THEN choose the row where Subject_act does not contain \"Expired\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "40945a0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#review the impact of removing records having \"expired\" in Subject_act field\n",
    "#each subset will be concatenated to a cumulative DF; the most important field is the index\n",
    "\n",
    "#case where the companion record contains the word \"Expired\" and the \"count = 1\" record does not\n",
    "# \n",
    "\n",
    "#46 rows returned\n",
    "a = df_grouped.loc[(df_grouped['Activity_Type_act'] == 'Membership Signup') & (df_grouped['count'] == 1) & (df_grouped['Subject_act_-1'].str.contains('Expired')) & (df_grouped['Subject_act'].str.contains('Expired') == False),:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "6803443a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#this model records/selects the records to \"keep\"\n",
    "\n",
    "#this returns 17 records, and so should be included\n",
    "#excluding Expirations selectively: the keyword appears on one row, but not the other\n",
    "# 2nd row of Membership Signups where the first row contains the word Expired and this row doesn't\n",
    "\n",
    "#pd.concat([concat_grouped_df,\n",
    "b = df_grouped.loc[(df_grouped['Activity_Type_act'] == 'Membership Signup') & (df_grouped['count'] == 2) & (df_grouped['Subject_act_1'].str.contains('Expired')) & (df_grouped['Subject_act'].str.contains('Expired') == False),:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "2271f4c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#returns 0 rows\n",
    "#concat_grouped_df = \n",
    "c = df_grouped.loc[(df_grouped['Activity_Type_act'] == 'Membership Signup') & (df_grouped['count'] == 1) & (df_grouped['Subject_act_-1'].str.contains('Trial')) & (df_grouped['Subject_act'].str.contains('Trial') == False),:]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "bcfc5d2f",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#returns 40, so should be included\n",
    "d = df_grouped.loc[(df_grouped['Activity_Type_act'] == 'Membership Signup') & (df_grouped['count'] == 2) & (df_grouped['Subject_act_1'].str.contains('Trial')) & (df_grouped['Subject_act'].str.contains('Trial') == False),:]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "acd17b45",
   "metadata": {},
   "outputs": [],
   "source": [
    "#returns 6 values, so should be used\n",
    "#concat_grouped_df = \n",
    "e = df_grouped.loc[(df_grouped['Activity_Type_act'] != 'Membership Signup') & (df_grouped['count'] == 2) & (df_grouped['to_1'] == df_grouped['from']) & (df_grouped['from_1'] != df_grouped['to']),:]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "ddd61cf8",
   "metadata": {},
   "outputs": [],
   "source": [
    "#returns 31 rows\n",
    "#else grab the first row and pray to god\n",
    "#row to keep (second row)\n",
    "#concat_grouped_df = \n",
    "f = df_grouped.loc[~(df_grouped['Target_Email_act'].isin(concat_grouped_df['Target_Email_act'])) & (df_grouped['count'] == 2),:]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "2d6163b5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(31, 20)"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "3c7a6f7b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0, 20)"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "concat_grouped_df = df_grouped.iloc[:0,:].copy()\n",
    "concat_grouped_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "d220168e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(94, 20)"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "concat_grouped_df = pd.concat([concat_grouped_df,a,b,c,d,e,f])\n",
    "concat_grouped_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "52c0cf2c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "74"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(concat_grouped_df['Target_Email_act'].drop_duplicates())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "b25ca740",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape is (0, 20)\n",
      "shape is (17, 20)\n",
      "shape is (0, 20)\n",
      "shape is (40, 20)\n",
      "shape is (6, 20)\n",
      "shape is (31, 20)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[None, None, None, None, None, None]"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[print(f'shape is {i.shape}') for i in (a,b,c,d,e,f)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "71b70abf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[0;31mSignature:\u001b[0m\n",
      "\u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdrop_duplicates\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\u001b[0m\n",
      "\u001b[0;34m\u001b[0m    \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\n",
      "\u001b[0;34m\u001b[0m    \u001b[0msubset\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0;34m'Hashable | Sequence[Hashable] | None'\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\n",
      "\u001b[0;34m\u001b[0m    \u001b[0;34m*\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\n",
      "\u001b[0;34m\u001b[0m    \u001b[0mkeep\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0;34m'DropKeep'\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m'first'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\n",
      "\u001b[0;34m\u001b[0m    \u001b[0minplace\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0;34m'bool'\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mFalse\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\n",
      "\u001b[0;34m\u001b[0m    \u001b[0mignore_index\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0;34m'bool'\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mFalse\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\n",
      "\u001b[0;34m\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m->\u001b[0m \u001b[0;34m'DataFrame | None'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mDocstring:\u001b[0m\n",
      "Return DataFrame with duplicate rows removed.\n",
      "\n",
      "Considering certain columns is optional. Indexes, including time indexes\n",
      "are ignored.\n",
      "\n",
      "Parameters\n",
      "----------\n",
      "subset : column label or sequence of labels, optional\n",
      "    Only consider certain columns for identifying duplicates, by\n",
      "    default use all of the columns.\n",
      "keep : {'first', 'last', ``False``}, default 'first'\n",
      "    Determines which duplicates (if any) to keep.\n",
      "\n",
      "    - 'first' : Drop duplicates except for the first occurrence.\n",
      "    - 'last' : Drop duplicates except for the last occurrence.\n",
      "    - ``False`` : Drop all duplicates.\n",
      "\n",
      "inplace : bool, default ``False``\n",
      "    Whether to modify the DataFrame rather than creating a new one.\n",
      "ignore_index : bool, default ``False``\n",
      "    If ``True``, the resulting axis will be labeled 0, 1, …, n - 1.\n",
      "\n",
      "Returns\n",
      "-------\n",
      "DataFrame or None\n",
      "    DataFrame with duplicates removed or None if ``inplace=True``.\n",
      "\n",
      "See Also\n",
      "--------\n",
      "DataFrame.value_counts: Count unique combinations of columns.\n",
      "\n",
      "Examples\n",
      "--------\n",
      "Consider dataset containing ramen rating.\n",
      "\n",
      ">>> df = pd.DataFrame({\n",
      "...     'brand': ['Yum Yum', 'Yum Yum', 'Indomie', 'Indomie', 'Indomie'],\n",
      "...     'style': ['cup', 'cup', 'cup', 'pack', 'pack'],\n",
      "...     'rating': [4, 4, 3.5, 15, 5]\n",
      "... })\n",
      ">>> df\n",
      "    brand style  rating\n",
      "0  Yum Yum   cup     4.0\n",
      "1  Yum Yum   cup     4.0\n",
      "2  Indomie   cup     3.5\n",
      "3  Indomie  pack    15.0\n",
      "4  Indomie  pack     5.0\n",
      "\n",
      "By default, it removes duplicate rows based on all columns.\n",
      "\n",
      ">>> df.drop_duplicates()\n",
      "    brand style  rating\n",
      "0  Yum Yum   cup     4.0\n",
      "2  Indomie   cup     3.5\n",
      "3  Indomie  pack    15.0\n",
      "4  Indomie  pack     5.0\n",
      "\n",
      "To remove duplicates on specific column(s), use ``subset``.\n",
      "\n",
      ">>> df.drop_duplicates(subset=['brand'])\n",
      "    brand style  rating\n",
      "0  Yum Yum   cup     4.0\n",
      "2  Indomie   cup     3.5\n",
      "\n",
      "To remove duplicates and keep last occurrences, use ``keep``.\n",
      "\n",
      ">>> df.drop_duplicates(subset=['brand', 'style'], keep='last')\n",
      "    brand style  rating\n",
      "1  Yum Yum   cup     4.0\n",
      "2  Indomie   cup     3.5\n",
      "4  Indomie  pack     5.0\n",
      "\u001b[0;31mFile:\u001b[0m      ~/anaconda3/envs/py12/lib/python3.12/site-packages/pandas/core/frame.py\n",
      "\u001b[0;31mType:\u001b[0m      function"
     ]
    }
   ],
   "source": [
    "?pd.DataFrame.drop_duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "bf604601",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(77, 20)"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#drop duplicates select first instance\n",
    "concat_grouped_df.drop_duplicates(subset=['Target_Email_act','Activity_Type_act','Activity_Date_DT_act'],keep = \"first\").shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "7f8d5466",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_grouped.loc[~df_grouped['index'].isin(concat_grouped_df['index']),:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "81e83b58",
   "metadata": {},
   "outputs": [],
   "source": [
    "#exclusion_list = a list - all indices of df_grouped OUTSIDE of those we want to keep (ie contact_grouped_df <- the cumulative DF)\n",
    "exclusion_list = df_grouped.loc[~df_grouped['index'].isin(concat_grouped_df['index']),'index'].to_list()\n",
    "#isolate the records to remove by negative indexing on concat_grouped_df\n",
    "inv_list = [-i for i in exclusion_list]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "3f4295b9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[-4205, -5528, -2085, -4792]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inv_list[0:4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "623cb896",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Assignee_Name_act                                            NaN\n",
       "Target_Name_act                                  Chomet, Allison\n",
       "Source_Email_act                          it@greenehillfood.coop\n",
       "Target_Email_act                               achomet@gmail.com\n",
       "Activity_Type_act                              Membership Signup\n",
       "Subject_act             Carrot Plan - None - Status: Deactivated\n",
       "Activity_Date_act                               2019-01-02 10:15\n",
       "Activity_Status_act                                    Completed\n",
       "Activity_Details_act                                         NaN\n",
       "Activity_Date_DT_act                         2019-01-02 10:15:00\n",
       "Name: 4205, dtype: object"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#manually verify that these values are in the index of activityReport\n",
    "activityReport.loc[4205,:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "c3fc8fca",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RangeIndex(start=0, stop=5, step=1)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "activityReport.index[0:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "9531afad",
   "metadata": {},
   "outputs": [],
   "source": [
    "#TODO inverse index activityReport by selecting indices NOT IN exclusion_list\n",
    "#inspection\n",
    "#df_grouped.loc[~df_grouped['index'].isin(concat_grouped_df['index']),:].sort_values(['Target_Email_act','Activity_Date_DT_act']).to_csv('/home/mofongo/Documents/ghfc/membershipReportsCIVI/records_to_remove.csv', index = False)\n",
    "#test = activityReport.loc[~df_grouped.loc[~df_grouped['index'].isin(concat_grouped_df['index']),'index'],:]\n",
    "test = activityReport.index.difference(exclusion_list)\n",
    "filtered_df = activityReport.loc[test,:]\n",
    "filtered_df.drop_duplicates(inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "825291af",
   "metadata": {},
   "source": [
    "### The last piece of logic: handles selection for cases where Subject_act contains 'to' and 'from' and cases where it doesn't, which tends to involve Membership Signups (Activity_Type_act = \"Membership Signup\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c34da23b",
   "metadata": {},
   "source": [
    "### END SELECT BEST TRANS LOGIC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "597401c7-7302-4027-89f6-fa8d85f99053",
   "metadata": {},
   "outputs": [],
   "source": [
    "#email selection logic: if Source Email is an admin, then select Target Email\n",
    "#alternatives: where Target Email is blank, select Source Email\n",
    "activityReport = activityReport.assign(email_grouping = activityReport.apply(lambda x: x['Target_Email_act'] if pd.notnull(x['Target_Email_act']) else x['Source_Email_act'], axis = 1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "bf090469",
   "metadata": {},
   "outputs": [],
   "source": [
    "class Member_transactions(object):\n",
    "    # a utility to process Member dictionaries: both status and type\n",
    "    #I need both the prevailing membership type as of a particular date and the status/trigger that led to it; \n",
    "    #it might be nice to place a range of beg and end dates for a membership type\n",
    "    #set the class attributes\n",
    "    #perhaps can employ a class inheritence to use this blueprint for status lookups\n",
    "    Subject_act = {'Trial Membership - ':'2 mo trial','Trial Membership - Status: New':'2 mo trial','Zucchini Plan -':'6 mo trial','Zucchini Plan - Status: New':'6 mo trial','to Zucchini Plan':'6 mo trial','Lettuce Plan -':'lettuce', 'Avocado Plan -':'avocado','to Avocado Plan':'avocado','Park Slope Member -':'park slope','Park Slope Member - Status: New':'park slope','Apple Plan -':'apple','to Apple Plan':'apple', 'Bushwick Member -':'bushwick','Bushwick Member - Status: New':'bushwick','Carrot Plan - ':'carrot', 'Household Plan - ':'household', 'to Lettuce Plan':'lettuce','to Carrot Plan':'carrot','to Bushwick Member':'bushwick'}\n",
    "\n",
    "# unique values of status_lookup\n",
    "#'cancelled', 'care giving leave', 'deactivated', 'disability leave', 'general leave', 'medical leave', 'parental leave', #'technical activation', 'technical reactivation', 'trial conversion', 'trial expiration', 'winback', 'suspended\n",
    "    status_lookup = {'Active to General Leave':'general leave', 'to Cancelled':'cancelled', 'General Leave to New':'winback',  \n",
    "                    'New to Expired':'trial expiration', 'to Deactivated':'deactivated', 'Deactivated to General Leave':'general leave','Deactivated to Expired':'trial expiration', 'to Expired':'trial expiration','Cancelled to Active':'winback','Active to Reactivated':'technical reactivation','to Parental Leave':'parental leave', 'General Leave to Active':'winback', 'Cancelled to Re-activated':'winback', 'to General Leave': 'general leave', 'Deactivated to Re-activated':'winback', 'Deactivated to Active':'winback','to Medical Leave':'medical leave', 'General Leave to Re-activated':'winback','Medical Leave to Re-activated':'winback', 'Expired to Active':'trial conversion', 'Parental Leave to Active':'winback','Expired to New':'trial conversion','Active to Re-activated':'technical reactivation','Disability Leave to Active':'winback','Active to Care Giving Leave':'care giving leave','Active to New':'technical activation','Expired to Re-activated':'trial conversion','New to Re-activated':'technical reactivation','Medical Leave to Active':'winback','Care Giving Leave to Active':'winback','Re-activated to New':'technical activation', 'Parental Leave to Re-activated':'winback','Active to Disability':'disability leave','Deactivated to New':'technical reactivation','Active to Suspended':'suspended','Suspended to Active':'winback','Suspended to Re-activated':'winback'}\n",
    "    #second/higher level lookup: to be conducted after lower level lookups\n",
    "    member_abstract={'2 mo trial':'trial','lettuce':'full member','6 mo trial':'trial','avocado':'full member',\n",
    "                     'carrot':'full member','bushwick':'full member','park slope':'full member','apple':'full member'}\n",
    "    lookup_dict_finder = {'type':Subject_act, 'status':status_lookup,'type_abstract':member_abstract}                     \n",
    "    ''' candidate 'winback' status changes: \n",
    "    Cancelled to Re-activated\n",
    "    Deactivated to Re-activated\n",
    "    General Leave to Re-activated\n",
    "    Medical Leave to Re-activated\n",
    "    '''\n",
    "    def __init__(self, dicty):\n",
    "        #accept the transactional dictionary (by member)\n",
    "        self.dicty = dicty\n",
    "        self.class_dict = None #placeholder (this will be revised, if need-be, in dict_loop elif statements)\n",
    "        return None\n",
    "    \n",
    "    #returns the key, which I use to look up the value in the lookup dict\n",
    "    #designed to be multi-faceted (accepts any lookup dict)\n",
    "    def dictCrawl(self,substring,lookup_dict):\n",
    "        result_list = [i for i in lookup_dict.keys() if i in substring] \n",
    "        if len(result_list) == 0:\n",
    "            return None\n",
    "        else:\n",
    "            #return the value from the lookup dict, after honing in on the matching key to the substring\n",
    "            return lookup_dict[result_list[0]]\n",
    "    \n",
    "    #loop through ea key of dicty, pass through a string search function and return the corresponding dict value \n",
    "#from the initial vetting dict into the new results dict\n",
    "#believe that the below also appends a seq number to the value retrieved from the dict\n",
    "# have to swap the key of \n",
    "    def dict_loop(self,lookup_dict_key):\n",
    "        \n",
    "        def apply_lookup(base_dict, lookup_key):\n",
    "            #base_dict will contain the raw values from activityReport\n",
    "            new_results = {} # will store the keys from the lookuptable and the values from the member dict\n",
    "            seq_num = 0\n",
    "            for k,v in base_dict.items(): # dicty = member dictionary\n",
    "                new_key = self.dictCrawl(k,self.lookup_dict_finder[lookup_key])\n",
    "                if new_key is None:\n",
    "                #do something\n",
    "                    print(f\"new member type doesn't match any lookup keys: {k}\")\n",
    "                else:\n",
    "                #write results to a new dict of format: {looked-up key: [original Subject_act,Date_DT_act]} <- this can be readily fed into the DF that is later imported to the db\n",
    "                    new_results[new_key+'_'+str(seq_num)] = [k,v]\n",
    "                    seq_num= seq_num+1\n",
    "            return new_results\n",
    "        \n",
    "        # if/then statements serves as a map of lookup_dict_key to \"base\" dict\n",
    "        if lookup_dict_key == 'type_abstract':\n",
    "            try: \n",
    "                #feed the class_dict (set at the elif statements below) into the apply lookup function to convert to abstract dict; \n",
    "                self.type_abstract_dict = apply_lookup(self.class_dict,'type_abstract')\n",
    "            except NameError: #check if class_dict has already been assigned\n",
    "                raise('the type dict does not yet exist; run dict_loop with lookup key = class')\n",
    "        elif lookup_dict_key == 'type':\n",
    "            self.class_dict = apply_lookup(self.dicty,'type')    \n",
    "            return self.class_dict\n",
    "        elif lookup_dict_key == 'status': # status should be handled at the class level so that all attributes are attached to same object\n",
    "            self.class_dict = apply_lookup(self.dicty,'status')\n",
    "            return self.class_dict\n",
    "        else: # OK to proceed\n",
    "            return None\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "61edca86-acc2-415f-b017-e0bdc6e1eb1c",
   "metadata": {},
   "outputs": [],
   "source": [
    "class Member(object):\n",
    "    #cobble together attributes of a particular member by extracting activity from the df\n",
    "    \n",
    "    #class attribute, bc never changes; the activity_df object would need to exist before this assignment\n",
    "    #membership_df = activity_df\n",
    "\n",
    "    #ideally I store the following attributes of a member: Ea status row carries with it: 1) applicable \"type\", 2) prevailing \"type\", 3) \"status\" lookup value (from Membership_transactions)\n",
    "    \n",
    "    def __init__(self, activity_df, email):\n",
    "        self.email = email\n",
    "        #instance attribute, but perhaps can be moved to a class attribute\n",
    "        self.membership_df = activity_df\n",
    "        \n",
    "        #can choose to call email_lookup() here\n",
    "        self.email_lookup()\n",
    "\n",
    "        self.convertType_toDict() #creates self.dicty_type\n",
    "        self.convertStatus_toDict() #creates self.dicty_status\n",
    "        \n",
    "\n",
    "    def email_lookup(self):\n",
    "        #search the activity df for all instances of the email: both membership type changes and status changes\n",
    "        try:\n",
    "            # membership type change\n",
    "            self.df = self.membership_df.loc[self.membership_df['email_grouping'].str.strip() ==self.email,:].sort_values(by = 'Activity_Date_DT_act')\n",
    "            #remove some trivial activities\n",
    "            self.df2 = self.df.loc[~self.df['Subject_act'].isin(['Status changed from New to Active','Status changed from Pending to New','Status changed from Re-activated to Active']),]\n",
    "            #write a sequential key to a series on the df: this is used to account for activity (Status or Type) that repeats (ex. multiple trials, etc), because when written to a dict below (convertType_toDict and convertStatus_toDict), the keys must be unique\n",
    "            self.df2 = self.df2.assign(seq_num = range(0,len(self.df2.index)))\n",
    "            #convert to str \n",
    "            self.df2 = self.df2.assign(seq_num = self.df2['seq_num'].astype(str))\n",
    "            #concat two Series\n",
    "            self.df2 = self.df2.assign(Subject_act = self.df2['Subject_act'].str.cat(self.df2['seq_num'], sep = '_'))\n",
    "\n",
    "            if self.df2.size == 0:\n",
    "                #print('no match for email found')\n",
    "                pass\n",
    "            else:\n",
    "                #print('email found')\n",
    "                pass\n",
    "        except Exception as e:\n",
    "            # return something if the member email is not found in the activty dataframe\n",
    "            print(e)\n",
    "            \n",
    "    #consider creating a separate class for all the data manipulation and lookup conversions\n",
    "    def convertType_toDict(self):\n",
    "        #filter on 'Activity_Type_act': {'Change Membership Status','Membership Signup','Change Membership Type'}\n",
    "        #convert two series from the member df to a dict for future mapping/conversion\n",
    "        dicty = {}\n",
    "        #select for Membership types\n",
    "        self.type_dict = self.df2.loc[self.df2['Activity_Type_act'].isin(['Membership Signup','Change Membership Type']),['Subject_act','Activity_Date_DT_act']]\n",
    "        for i in self.type_dict.values:\n",
    "            dicty[i[0]] = i[1]\n",
    "        self.dicty_type_member = dicty\n",
    "        return self.dicty_type_member\n",
    "    \n",
    "    def convertStatus_toDict(self):\n",
    "        #filter on 'Activity_Type_act': {'Change Membership Status','Membership Signup','Change Membership Type'}\n",
    "        #convert two series from the member df to a dict for future mapping/conversion\n",
    "        dicty = {}\n",
    "        #select for Membership types\n",
    "        for i in self.df2.loc[self.df2['Activity_Type_act'].isin(['Change Membership Status']),['Subject_act','Activity_Date_DT_act']].values:\n",
    "            dicty[i[0]] = i[1]\n",
    "        self.dicty_status = dicty\n",
    "        return self.dicty_status\n",
    "    \n",
    "    def interact_member_transactions(self):\n",
    "        #interact with the Member_transactions class returning normalized dictionaries for \"type\" and \"status\"\n",
    "        try:\n",
    "            #member_transactions is generated from the {subject_act:Date_act} dictionary processed in converStats_toDict and convertType_toDict above\n",
    "            member_trans = Member_transactions(self.dicty_type_member)\n",
    "            #dict_loop will return the post lookup dict (handled in Member_transactions), format: {looked-up subject: [original subject,activity date]}\n",
    "            dict_type = member_trans.dict_loop('type')\n",
    "            assert len(self.dicty_type_member) == len(dict_type), \"lengths of self.type_dict and dict_status in class Member don't match\"\n",
    "        except Exception as e:\n",
    "            print(\"error generated in class Member.interact_member_transactions on 'type' process\")\n",
    "            print(e)\n",
    "\n",
    "        try:\n",
    "            member_trans = Member_transactions(self.dicty_status)\n",
    "            #dict_loop will return the post lookup dict (handled in Member_transactions); dict format: {looked-up subject: [original subject,activity date]}\n",
    "            dict_status = member_trans.dict_loop('status')\n",
    "        except Exception as e:\n",
    "            print(\"error generated in class Member.interact_member_transactions on 'status' process\")\n",
    "            print(e)\n",
    "\n",
    "        return dict_type,dict_status\n",
    "\n",
    "    def normalize_member_class(self):\n",
    "        #will lookup whether a member is full, trial, et al\n",
    "        return None\n",
    "    \n",
    "    '''\n",
    "    #print intermittent output of the Member classes in order to determine best step at which to apply the trial functions\n",
    "aatiell_class = Member(activityReport,'aatiell@gmail.com')\n",
    "mt,ms = aatiell_class.interact_member_transactions()\n",
    "print(mt)\n",
    "print(ms)\n",
    "\n",
    "    '''"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "8bff326e",
   "metadata": {},
   "source": [
    "### Handling trial expirations and conversions\n",
    "Contemplating applying a ruleset: if the trial-related activity (cancellation, conversion, etc) occurs within the trial window (can be 1,2,6 months), then the 'type' activity is assigned to that trial\n",
    "If there is no subsequent 'type' initiation/activation after the latest trial, then assign the 'status' to the last trial -> this will cover activity outside of trial date range\n",
    "\n",
    "- Consider creating a trial rollover whereby a member rolls over from a 1 to 6 month trial\n",
    "\n",
    "CASES: \n",
    "- 405sarah@gmail.com enrolls in a two month trial, which I need to relabel at the Member level\n",
    "- 009janaki@gmail.com rolls a 1 month trial over to a 6 month trial INSIDE the window of the first 1 mo period\n",
    "- aaikolord@gmail.com converts a trial to a full membership after trial expiration - implement a time cap for conversion\n",
    "- aatiell@gmail.com: accurately encoded the status type to trial conversion\n",
    "- abrennan@wesleyan.edu: re-code for 2 month trial expiration\n",
    "- adam.b.neese@gmail.com: case study of a legit 1 mo trial\n",
    "- adwait.spotify@gmail.com: may have simultaneously enrolled in two trials (one and two month)\n",
    "- aef@fastmail.com: rolled from 1 month to 6 month\n",
    "- aelewis22@yahoo.com: two trials and two conversions to full\n",
    "- aidan.l.ling@gmail.com: conversion after trial expiration\n",
    "- akari@tribecapediatrics.com: logged two identical trials on the same date (effectively one should be ignored)\n",
    "- alembeck5@gmail.com: conversion to full within trial window and no expiration recorded for the corresponding trial\n",
    "- alina@alinarancier.com: convert after trial expiration, and later go on to have many winbacks"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "f8a5a8b2",
   "metadata": {},
   "source": [
    "### Regarding trials\n",
    "Easiest route may be to associate all activity to trials having a start date BEFORE the date of activity; this may handle cases of multiple trials and actions in an effort to avoiding associating activity to the wrong trial"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c0dd0161",
   "metadata": {},
   "outputs": [],
   "source": [
    "class Trial_pattern(object):\n",
    "    # TODO add a trial flag to each email grouping with a Trial indicator and the start_dt of their last trial\n",
    "    #purpose of this class is to create the infrastructure to properly evaluate trial members: relate status records to the appropriate membership types, including both expirations and conversions\n",
    "    #non-trials are more straightforward because the status change will correspond to the prevailing \"type\" on activity date\n",
    "    #this version of Trial_pattern class differs from the above in that it is suited to dataframe INPUT as opposed to the dictionaries from the intermittent output of the Member class\n",
    "    #designed to ingest the entire Member Type df at once, and set a \"theoretical\" expiration for ea trial based on Travis' critera:\n",
    "    '''\n",
    "    Travis: I can't find the exact date trials were changed from 30 days to 60, but it was around December 20th 2021.\n",
    "    \n",
    "    Me: So to be clear, are you saying that all trials (not Zucchini) prior to December '21 should have an expiration of one month and those initiated after that date should have a length of 2 months?\n",
    "     \n",
    "    '''\n",
    "    threshold = datetime.datetime.strptime('2021-12-20', '%Y-%m-%d')\n",
    "\n",
    "    def __init__(self, df):\n",
    "        #pull both the 'type' and 'status' dictionaries from a member\n",
    "        self.df_orig = df.copy()\n",
    "        #this will change the original df object as well\n",
    "        self.df_orig['trial_expiration'] = np.nan\n",
    "        #add filter for all records having 'trial' in the 'type' description\n",
    "        self.df_trial = df.loc[df['type'].str.contains('trial'),:]\n",
    "\n",
    "        #self.process()\n",
    "        #class variable switch date for trials\n",
    "        \n",
    "\n",
    "    def loop_thru(self):\n",
    "        # for ea record, select the expiration date by date of trial enrollment; per Travis 12/20/21 is the switch-over date from 1 mo to 2 mo trial lengths\n",
    "\n",
    "        def define_expirations(df_row):\n",
    "        # associate ea expiration entry in status changes to the appropriate trial (can be multiple trials for a member)\n",
    "        # the value of this dict can be added directly to a datetime object, ex. datetime.datetime(2020,3,7) + value\n",
    "        # a typical matching record will have {'trial type': datetime.date(2019, 12, 5)}\n",
    "            time_deltas = {'1 mo trial':datetime.timedelta(days = 30), '2 mo trial':datetime.timedelta(days = 60), '6 mo trial': datetime.timedelta(days = 180)}\n",
    "        \n",
    "            def assign_expiry(df_row):\n",
    "                if '6 mo trial' in df_row['type'] :\n",
    "                    trial_expiration = '6 mo trial'\n",
    "                elif df_row['start_dt'] < Trial_pattern.threshold:\n",
    "                    trial_expiration = '1 mo trial'\n",
    "                elif df_row['start_dt'] >= Trial_pattern.threshold:\n",
    "                    trial_expiration = '2 mo trial'\n",
    "                else:\n",
    "                    None\n",
    "                return trial_expiration\n",
    "            #define_expirations returns below value\n",
    "            return df_row['start_dt']+time_deltas[assign_expiry(df_row)]\n",
    "        #change the class variable\n",
    "        self.df_trial = self.df_trial.assign(trial_expiration = self.df_trial.apply(define_expirations, axis = 1))\n",
    "\n",
    "        return None\n",
    "        \n",
    "    def process(self):\n",
    "        #call function to alter self.df_trial\n",
    "        self.loop_thru()\n",
    "\n",
    "        self.df_non_trial = self.df_orig.loc[~self.df_orig['type'].str.contains('trial'),:]\n",
    "        #concatenate both dataframes\n",
    "        #FutureWarning: The behavior of DataFrame concatenation with empty or all-NA entries is deprecated. In a future version, this will no longer exclude empty or all-NA columns when determining the result dtypes. To retain the old behavior, exclude the relevant entries before the concat operation.\n",
    "\n",
    "        return pd.concat([self.df_trial,self.df_non_trial])\n",
    "    \n",
    "    #build the trial flag: has to take in the entire df whole, then conduct all grouping, etc\n",
    "    def append_last_trial(self,df_1):\n",
    "    #isolate the trials and perform idxmax\n",
    "    # if there are no trials, will need to catch and isolate\n",
    "    # in order to test, can run it on email = jodyq@me.com', \n",
    "        df_2 = df_1.copy()\n",
    "        df_2.reset_index(inplace=True)\n",
    "        df_len = len(df_1)\n",
    "    #print(f'length of df {df_len}')\n",
    "        try:\n",
    "            seg = df_2.loc[df_2['type_clean'].str.contains('trial'),:]\n",
    "            #handle two cases: whether a trial exists in the grouped df or not\n",
    "            if seg.size == 0:\n",
    "            #return an empty array: be sure that data types are equal (np.NAN)\n",
    "                temp_list = [{'start_dt': None,'type_clean':None}] * df_len\n",
    "                df_2['latest_trial'] = pd.Series(temp_list)\n",
    "                #print('returning seg size = 0 version')\n",
    "                return(df_2)\n",
    "            else:\n",
    "            #last trial is a series; idxmax() returns the index # for the row with the latest start_dt\n",
    "                last_trial = seg.loc[seg['start_dt'].idxmax(),['start_dt','type_clean']]\n",
    "            #TODO Is this causing me to lose the time component? convert the datetime object start_dt to isoformat for easier json serializing later \n",
    "                last_trial['start_dt'] = last_trial['start_dt'].isoformat()\n",
    "                hold = last_trial.to_dict()\n",
    "            #bc pandas doesn't have vectorization, I need to create my own: duplicating the list for as many rows in the df\n",
    "                temp_list = [hold] * df_len\n",
    "            #latest_trial Series is now a Series of lists of size two\n",
    "                sing = pd.DataFrame(pd.Series(temp_list))\n",
    "                sing.columns = ['latest_trial']\n",
    "                #print('returning seg size > 0 version')\n",
    "                df_3 = pd.concat([df_2,sing], axis = 1)\n",
    "                return(df_3)\n",
    "        except ValueError as e:\n",
    "            print(e)\n",
    "\n",
    "    def process_append_last_trial(self, df_1):\n",
    "        #merely interacts with append_last_trial function\n",
    "        #DeprecationWarning: DataFrameGroupBy.apply operated on the grouping columns. This behavior is deprecated, and in a future version of pandas the grouping columns will be excluded from the operation. Either pass `include_groups=False` to exclude the groupings or explicitly select the grouping columns after groupby to silence this warning.\n",
    "\n",
    "        processed_df = df_1.groupby('email').apply(self.append_last_trial)\n",
    "        return(processed_df.reset_index(drop=True))\n",
    "            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "2f76f7b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# STRICTLY FOR TESTING... IGNORE IGNORE\n",
    "def append_last_trial(df_1):\n",
    "    #isolate the trials and perform idxmax\n",
    "    # if there are no trials, will need to catch and isolate\n",
    "    df_2 = df_1.copy()\n",
    "    df_2.reset_index(inplace=True, drop = True)\n",
    "    df_len = len(df_1)\n",
    "    #print(f'length of df {df_len}')\n",
    "    try:\n",
    "        seg = df_2.loc[df_2['type_clean'].str.contains('trial'),:]\n",
    "        if seg.size == 0:\n",
    "            #return an empty array: be sure that data types are equal (np.NAN)\n",
    "            temp_list = [{'start_dt': None,'type_clean':None}] * df_len\n",
    "            df_2['latest_trial'] = pd.Series(temp_list)\n",
    "            df_2.reset_index(drop = True, inplace = True)\n",
    "            #print('returning seg size = 0 version')\n",
    "            return(df_2)\n",
    "            \n",
    "        else:\n",
    "            #last trial is a series; idxmax() returns the index # for the row with the latest start_dt\n",
    "            last_trial = seg.loc[seg['start_dt'].idxmax(),['start_dt','type_clean']]\n",
    "            hold = last_trial.to_dict()\n",
    "            #bc pandas doesn't have vectorization, I need to create my own: duplicating the list for as many rows in the df\n",
    "            temp_list = [hold] * df_len\n",
    "            #print(f'this it temp_list: {pd.Series(temp_list)}')\n",
    "            #print(f'this is the df just before writing:{df_2}')\n",
    "            #latest_trial Series is now a Series of lists of size two\n",
    "            sing = pd.DataFrame(pd.Series(temp_list))\n",
    "            sing.columns = ['latest_trial']\n",
    "            #print(f'this is the df just after writing:{df_3}')\n",
    "            #print('returning seg size > 0 version')\n",
    "            df_3 = pd.concat([df_2,sing], axis = 1, ignore_index = True)\n",
    "            df_3.reset_index(drop = True, inplace = True)\n",
    "            return(df_3)\n",
    "    except ValueError as e:\n",
    "        print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "2fe26cd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# compile by processing a dictionary of subject (post member_transactions lookup) and Activity_Date_DT_act and combine with columns for email and date range; NOTE: type field is present in both sets of DF (type & status)\n",
    "# any desired changes to the final df (those inserted into the mysql db) can be done here in addition to the Member and Member_transactions classes\n",
    "def create_df(mt_v):\n",
    "    if len(mt_v) == 0:\n",
    "        return None\n",
    "    else:\n",
    "    #will need to account for single row membership activity\n",
    "        dfj = pd.DataFrame.from_dict(mt_v, orient = 'index').reset_index()\n",
    "    #at this point can remove the sequencing from type_raw to write a cleaner \"subject\" to the db\n",
    "        dfj.columns = ['type','type_raw','start_dt']\n",
    "    #convert dtype\n",
    "    #TODO ensure that to.datetime isn't dropping the time component\n",
    "        dfj['start_dt'] = pd.to_datetime(dfj['start_dt'])\n",
    "        dfj.sort_values('start_dt', ascending = True)\n",
    "    #TODO: handle transactions where two or more made on same day... may actually want to delete all but the latest record\n",
    "        dfj['lead_date'] = dfj['start_dt'].shift(-1)\n",
    "        dfj['lead_date'] = dfj['lead_date'].apply(lambda x: x-datetime.timedelta(days = 1))\n",
    "    #replace NaN with today's date\n",
    "        djf = dfj.assign(lead_date = dfj['lead_date'].fillna(datetime.datetime.today().strftime(format = \"%Y-%m-%d %H:%M:%S\")))\n",
    "    # if dataframe is of row length = 1, then the \"shifted\" column should = today's date\n",
    "    #create a DateRange object per row\n",
    "        djf['datetimerange'] = djf.apply(lambda x: DateTimeRange(x['start_dt'],x['lead_date']), axis = 1)\n",
    "        # sanitize the type field by removing the sequence encoding and storing the abstracted type in its own field\n",
    "        djf['type_clean'] = djf['type'].str.split(pat = '_', n = 1,expand = True)[0]\n",
    "\n",
    "        return djf\n",
    "    \n",
    "#called independently from create_df()\n",
    "def compile(unq_email):\n",
    "    unq_mem_class = Member(activityReport,unq_email)\n",
    "    mt,ms = unq_mem_class.interact_member_transactions() #returns two dictionaries: 1) contains \"type\" records; 2) contains \"status\" records; the dicts are of format: {converted subject line (via lookup): [original subject line, activity date]}\n",
    "    return(mt,ms)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "fc07bc0a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "new member type doesn't match any lookup keys: Type changed from Apple Plan to Trial Membership_5\n",
      "error generated in class Member.interact_member_transactions on 'type' process\n",
      "lengths of self.type_dict and dict_status in class Member don't match\n",
      "error generated in class Member.interact_member_transactions on 'type' process\n",
      "argument of type 'float' is not iterable\n",
      "error encountered with zz33test@mailinator.com; error message (\"cannot access local variable 'dict_type' where it is not associated with a value\",)\n",
      "error generated in class Member.interact_member_transactions on 'type' process\n",
      "argument of type 'float' is not iterable\n",
      "error encountered with ghfc_test_account@mailinator.com; error message (\"cannot access local variable 'dict_type' where it is not associated with a value\",)\n",
      "error generated in class Member.interact_member_transactions on 'type' process\n",
      "argument of type 'float' is not iterable\n",
      "new member type doesn't match any lookup keys: Status changed from New to Pending_4\n",
      "new member type doesn't match any lookup keys: Status changed from New to Pending_6\n",
      "new member type doesn't match any lookup keys: Status changed from New to Pending_11\n",
      "error encountered with ghfc_test_member@mailinator.com; error message (\"cannot access local variable 'dict_type' where it is not associated with a value\",)\n",
      "new member type doesn't match any lookup keys: Status changed from Active to Deceased_5\n",
      "new member type doesn't match any lookup keys: Status changed from Medical Leave to Care Giving Leave_3\n",
      "new member type doesn't match any lookup keys: Status changed from Deactivated to Suspended_9\n",
      "new member type doesn't match any lookup keys: Status changed from General Leave to Care Giving Leave_4\n",
      "new member type doesn't match any lookup keys: Status changed from Care Giving Leave to Re-activated_5\n",
      "error generated in class Member.interact_member_transactions on 'type' process\n",
      "argument of type 'float' is not iterable\n",
      "error encountered with it@greenehillfood.coop; error message (\"cannot access local variable 'dict_type' where it is not associated with a value\",)\n",
      "new member type doesn't match any lookup keys: Status changed from Parental Leave to New_4\n",
      "error generated in class Member.interact_member_transactions on 'type' process\n",
      "argument of type 'float' is not iterable\n",
      "error encountered with test1@greenehillfood.coop; error message (\"cannot access local variable 'dict_type' where it is not associated with a value\",)\n",
      "new member type doesn't match any lookup keys: Status changed from Parental Leave to New_2\n",
      "new member type doesn't match any lookup keys: Status changed from General Leave to Care Giving Leave_4\n",
      "new member type doesn't match any lookup keys: Status changed from Cancelled to New_4\n"
     ]
    }
   ],
   "source": [
    "#collect both sets of activity (type and status) into their respective dataframes\n",
    "# each DF will have the same columns: type (activity name), start_dt, lead_dt (date offset from proceeding activity or today's date), datetimerange, email\n",
    "list_unq_email = activityReport.email_grouping.unique()\n",
    "cum_df_type = pd.DataFrame()\n",
    "cum_df_status = pd.DataFrame()\n",
    "#iterate through ea email address and process: create a Member object\n",
    "for i in list_unq_email:\n",
    "    try:\n",
    "        #generate both a type and status dict for ea email\n",
    "        mt,ms = compile(i)\n",
    "        mt_df = create_df(mt)\n",
    "        ms_df = create_df(ms)\n",
    "        if mt_df is None: # no type activity for member\n",
    "            next\n",
    "        else:\n",
    "        #add member email to the df\n",
    "            mt_df['email'] = i\n",
    "            cum_df_type = pd.concat([cum_df_type,mt_df], axis = 0)\n",
    "            \n",
    "        if ms_df is None: # if no status activity for member, proceed\n",
    "            next\n",
    "        else:\n",
    "        #add member email to the df\n",
    "            ms_df['email'] = i\n",
    "            cum_df_status = pd.concat([cum_df_status,ms_df], axis = 0)\n",
    "        \n",
    "    except Exception as e:\n",
    "        print(f'error encountered with {i}; error message {e.args}')   \n",
    "\n",
    "cum_df_type = cum_df_type.reset_index(drop = True)        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "428b966d",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_37723/2063382517.py:61: FutureWarning: The behavior of DataFrame concatenation with empty or all-NA entries is deprecated. In a future version, this will no longer exclude empty or all-NA columns when determining the result dtypes. To retain the old behavior, exclude the relevant entries before the concat operation.\n",
      "  return pd.concat([self.df_trial,self.df_non_trial])\n",
      "/tmp/ipykernel_37723/2063382517.py:102: DeprecationWarning: DataFrameGroupBy.apply operated on the grouping columns. This behavior is deprecated, and in a future version of pandas the grouping columns will be excluded from the operation. Either pass `include_groups=False` to exclude the groupings or explicitly select the grouping columns after groupby to silence this warning.\n",
      "  processed_df = df_1.groupby('email').apply(self.append_last_trial)\n"
     ]
    }
   ],
   "source": [
    "#apply the new trial expiration logic\n",
    "df = Trial_pattern(cum_df_type)\n",
    "#process() method of the Trial_pattern class adds a 'trial_expiration' field, which may be null in the cases of no trials; and that could trigger a Warning (concatenating to a NULL dataframe)\n",
    "df2 = df.process()\n",
    "df2 = df2.reset_index(drop = True)\n",
    "#print(f'df2 second rnd: {df2.head()}')\n",
    "type_df = df.process_append_last_trial(df2)\n",
    "type_df.drop('index',inplace = True, axis = 1)\n",
    "#print(f'status_df: {status_df.head()}')\n",
    "#df.process().sort_values(['email','start_dt']).to_csv('./inspect.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "2a678d51",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['type', 'type_raw', 'start_dt', 'lead_date', 'datetimerange',\n",
      "       'type_clean', 'email', 'trial_expiration'],\n",
      "      dtype='object')\n",
      "Index(['type', 'type_raw', 'start_dt', 'lead_date', 'datetimerange',\n",
      "       'type_clean', 'email'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(df2.columns)\n",
    "print(cum_df_type.columns)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "c335f043",
   "metadata": {},
   "source": [
    "## QA: date range, df shapes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "46efdf48",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2018-08-10 10:41:00\n",
      "2024-10-07 13:47:00\n"
     ]
    }
   ],
   "source": [
    "#QA: date range\n",
    "print(type_df['start_dt'].min())\n",
    "print(type_df['start_dt'].max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "10c379d0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2018-08-31 07:12:00\n",
      "2024-10-06 14:55:00\n"
     ]
    }
   ],
   "source": [
    "print(cum_df_status['start_dt'].min())\n",
    "print(cum_df_status['start_dt'].max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "097fd356",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(7147, 7)\n",
      "(10283, 9)\n",
      "(22074, 11)\n",
      "(17469, 11)\n"
     ]
    }
   ],
   "source": [
    "#inspect lengths of each df\n",
    "print(cum_df_status.shape)\n",
    "print(type_df.shape)\n",
    "print(activityReport.shape)\n",
    "#reconcile the difference btwn sum of status and type df to length of activityReport\n",
    "print(activityReport.loc[~activityReport['Subject_act'].isin(['Status changed from New to Active','Status changed from Pending to New','Status changed from Re-activated to Active']),].shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "f6a6ae65",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "type_clean\n",
       "2 mo trial    5511\n",
       "lettuce       2010\n",
       "carrot        1094\n",
       "6 mo trial     876\n",
       "park slope     524\n",
       "apple          188\n",
       "avocado         63\n",
       "bushwick        15\n",
       "household        2\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#QA the distribution of type_clean from either table\n",
    "type_df['type_clean'].value_counts(dropna = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "65d3b913",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "type_clean\n",
       "trial expiration          4655\n",
       "cancelled                  557\n",
       "deactivated                496\n",
       "general leave              493\n",
       "winback                    395\n",
       "trial conversion           282\n",
       "technical activation       115\n",
       "technical reactivation      58\n",
       "parental leave              47\n",
       "medical leave               30\n",
       "suspended                   10\n",
       "care giving leave            6\n",
       "disability leave             3\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#QA the distribution of type_clean from either table\n",
    "cum_df_status['type_clean'].value_counts(dropna = False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "1dafa4bb",
   "metadata": {},
   "source": [
    "At this point I have two tables that I'd like to import into the db: **type_df** and **cum_df_status**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "212efe36",
   "metadata": {},
   "outputs": [],
   "source": [
    "type_df = type_df.assign(latest_trial2 = type_df['latest_trial'].apply(lambda x: json.dumps(x)))\n",
    "type_df['ingest_date'] =  datetime.datetime.today() #datetime.datetime.strptime('2023-07-30', '%Y-%m-%d')\n",
    "cum_df_status['ingest_date'] = datetime.datetime.today() #datetime.datetime.strptime('2023-07-30', '%Y-%m-%d')\n",
    "#type_df.to_pickle('/home/candela/Documents/greeneHill/membershipReportsCIVI/membership_df2.p')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "6d902d60",
   "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>type</th>\n",
       "      <th>type_raw</th>\n",
       "      <th>start_dt</th>\n",
       "      <th>lead_date</th>\n",
       "      <th>datetimerange</th>\n",
       "      <th>type_clean</th>\n",
       "      <th>email</th>\n",
       "      <th>ingest_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>trial expiration_0</td>\n",
       "      <td>Status changed from Active to Expired_0</td>\n",
       "      <td>2024-08-15 02:54:00</td>\n",
       "      <td>2024-10-28 18:40:51</td>\n",
       "      <td>2024-08-15T02:54:00 - 2024-10-28T18:40:51</td>\n",
       "      <td>trial expiration</td>\n",
       "      <td>bmarierobles@gmail.com</td>\n",
       "      <td>2024-10-28 18:41:41.643699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cancelled_0</td>\n",
       "      <td>Status changed from Expired to Cancelled_0</td>\n",
       "      <td>2024-09-08 10:59:00</td>\n",
       "      <td>2024-10-28 18:40:51</td>\n",
       "      <td>2024-09-08T10:59:00 - 2024-10-28T18:40:51</td>\n",
       "      <td>cancelled</td>\n",
       "      <td>cecarey17@gmail.com</td>\n",
       "      <td>2024-10-28 18:41:41.643699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>trial expiration_0</td>\n",
       "      <td>Status changed from New to Expired_0</td>\n",
       "      <td>2024-08-15 02:54:00</td>\n",
       "      <td>2024-10-28 18:40:51</td>\n",
       "      <td>2024-08-15T02:54:00 - 2024-10-28T18:40:51</td>\n",
       "      <td>trial expiration</td>\n",
       "      <td>kristinmsteve@gmail.com</td>\n",
       "      <td>2024-10-28 18:41:41.643699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>trial expiration_0</td>\n",
       "      <td>Status changed from New to Expired_0</td>\n",
       "      <td>2024-08-15 02:54:00</td>\n",
       "      <td>2024-10-28 18:40:51</td>\n",
       "      <td>2024-08-15T02:54:00 - 2024-10-28T18:40:51</td>\n",
       "      <td>trial expiration</td>\n",
       "      <td>roobieflores@gmail.com</td>\n",
       "      <td>2024-10-28 18:41:41.643699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>trial conversion_0</td>\n",
       "      <td>Status changed from Expired to Active_0</td>\n",
       "      <td>2024-08-15 05:58:00</td>\n",
       "      <td>2024-10-28 18:40:51</td>\n",
       "      <td>2024-08-15T05:58:00 - 2024-10-28T18:40:51</td>\n",
       "      <td>trial conversion</td>\n",
       "      <td>birte@strunk-mg.de</td>\n",
       "      <td>2024-10-28 18:41:41.643699</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 type                                    type_raw  \\\n",
       "0  trial expiration_0     Status changed from Active to Expired_0   \n",
       "0         cancelled_0  Status changed from Expired to Cancelled_0   \n",
       "0  trial expiration_0        Status changed from New to Expired_0   \n",
       "0  trial expiration_0        Status changed from New to Expired_0   \n",
       "0  trial conversion_0     Status changed from Expired to Active_0   \n",
       "\n",
       "             start_dt           lead_date  \\\n",
       "0 2024-08-15 02:54:00 2024-10-28 18:40:51   \n",
       "0 2024-09-08 10:59:00 2024-10-28 18:40:51   \n",
       "0 2024-08-15 02:54:00 2024-10-28 18:40:51   \n",
       "0 2024-08-15 02:54:00 2024-10-28 18:40:51   \n",
       "0 2024-08-15 05:58:00 2024-10-28 18:40:51   \n",
       "\n",
       "                               datetimerange        type_clean  \\\n",
       "0  2024-08-15T02:54:00 - 2024-10-28T18:40:51  trial expiration   \n",
       "0  2024-09-08T10:59:00 - 2024-10-28T18:40:51         cancelled   \n",
       "0  2024-08-15T02:54:00 - 2024-10-28T18:40:51  trial expiration   \n",
       "0  2024-08-15T02:54:00 - 2024-10-28T18:40:51  trial expiration   \n",
       "0  2024-08-15T05:58:00 - 2024-10-28T18:40:51  trial conversion   \n",
       "\n",
       "                     email                ingest_date  \n",
       "0   bmarierobles@gmail.com 2024-10-28 18:41:41.643699  \n",
       "0      cecarey17@gmail.com 2024-10-28 18:41:41.643699  \n",
       "0  kristinmsteve@gmail.com 2024-10-28 18:41:41.643699  \n",
       "0   roobieflores@gmail.com 2024-10-28 18:41:41.643699  \n",
       "0       birte@strunk-mg.de 2024-10-28 18:41:41.643699  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cum_df_status.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "c6ffa9c9",
   "metadata": {},
   "source": [
    "## Pickle interaction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "11e334d1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/home/candela/Documents/greeneHill/membershipReportsCIVI/membershipReportingLogicSampleReports'"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "500b4c43",
   "metadata": {},
   "outputs": [],
   "source": [
    "#using pickle to persist objects; can also use pandas to_pickle() method, which is more reliable\n",
    "#filename1 = '/home/candela/Documents/greeneHill/membershipReportsCIVI/membership_df2.p'\n",
    "#filename2 = '/home/candela/Documents/greeneHill/membershipReportsCIVI/membership_cum_df_status.p'\n",
    "#filehandler = open(filename1, 'wb') \n",
    "#pickle.dump(df2, filehandler)\n",
    "#filehandler = open(filename2, 'wb') \n",
    "#pickle.dump(cum_df_status, filehandler)\n",
    "\n",
    "folder = '/home/candela/Documents/greeneHill/membershipReportsCIVI'\n",
    "type_df.to_pickle(folder+'/membership_df2.p')\n",
    "cum_df_status.to_pickle(folder+'/membership_cum_df_status.p')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "7f046b11",
   "metadata": {},
   "outputs": [],
   "source": [
    "type_df = pd.read_pickle('/home/candela/Documents/greeneHill/membershipReportsCIVI/membership_df2.p') #reads successfully\n",
    "#cum_df_status doesn't exist\n",
    "#cum_df_status = pd.read_pickle('/home/candela/Documents/greeneHill/membershipReportsCIVI/membership_cum_df_status.p')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "be1faa70",
   "metadata": {},
   "source": [
    "### SQL import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "801a6729",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "#from sqlalchemy import URL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "1e3d223b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connection to the 172.17.0.2 for user root created successfully.\n"
     ]
    }
   ],
   "source": [
    "# DEFINE THE DATABASE CREDENTIALS\n",
    "cred_dict = return_credentials()\n",
    "\n",
    "user = cred_dict['user'] \n",
    "password = cred_dict['pass'] \n",
    "host = cred_dict['host'] \n",
    "port = cred_dict['port'] \n",
    "database = cred_dict['database'] \n",
    "\n",
    "def get_connection():\n",
    "\treturn sqlalchemy.create_engine(\n",
    "\t\turl=\"mysql+pymysql://{0}:{1}@{2}:{3}/{4}\".format(\n",
    "\t\t\tuser, password, host, port, database\n",
    "\t\t)\n",
    "\t)\n",
    "\n",
    "if __name__ == '__main__':\n",
    "\n",
    "\ttry:\n",
    "\t\n",
    "\t\t# GET THE CONNECTION OBJECT (ENGINE) FOR THE DATABASE\n",
    "\t\tengine = get_connection()\n",
    "\t\tprint(\n",
    "\t\t\tf\"Connection to the {host} for user {user} created successfully.\")\n",
    "\texcept Exception as ex:\n",
    "\t\tprint(\"Connection could not be made due to the following error: \\n\", ex)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "2a13d3b0",
   "metadata": {},
   "source": [
    "### *Must delete the tables in mysql db or rename the tables before writing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "53b5fb7c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table mem_status created successfully.\n"
     ]
    }
   ],
   "source": [
    "#two DF names: type_df and cum_df_status\n",
    "try:\n",
    "    frame = cum_df_status.to_sql('mem_status_1007_ts', con=engine, if_exists='replace', index=False)\n",
    "    #drop latest_trial as field is redundant\n",
    "    #frame = type_df.drop('latest_trial', axis =1).to_sql('mem_type_1007_ts', con=engine, if_exists='replace', index=False)\n",
    "except ValueError as vx:\n",
    "    print(vx)\n",
    "except Exception as ex:   \n",
    "    print(ex)\n",
    "else:\n",
    "    print(\"Table %s created successfully.\"%'mem_status');   \n",
    "finally:\n",
    "    engine.dispose()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "859b27b5",
   "metadata": {},
   "source": [
    "## End of DB injection code"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "330515d0",
   "metadata": {},
   "source": [
    "### develop the program to join status activity to prevailing member type\n",
    "- if trial related, the actvity will be assigned to one of: the trial with an active date range that encompasses the activity OR the historical trial that occurred prior to the trial having a start_dt AFTER the activity date of the status \n",
    "- once a person becomes a member, they will typically stay in that category, and it's up to me to toggle them active/leave/cancelled depending on status updates\n",
    "- ultimately I care about: trial conversions, trial expirations/cancellations, "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "9479882f",
   "metadata": {},
   "source": [
    "Attempt to filter for trial members that do not upgrade and observe the permutations of subjects to learn which text is exclusive to trial memberships, so I can properly associate them/dismiss them when building the user journey"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "5deb0955",
   "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>type</th>\n",
       "      <th>start_dt</th>\n",
       "      <th>lead_date</th>\n",
       "      <th>datetimerange</th>\n",
       "      <th>email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1 mo trial_0</td>\n",
       "      <td>2019-12-23</td>\n",
       "      <td>2020-01-19</td>\n",
       "      <td>2019-12-23T00:00:00 - 2020-01-19T00:00:00</td>\n",
       "      <td>009janaki@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6 mo trial_1</td>\n",
       "      <td>2020-01-20</td>\n",
       "      <td>2023-05-14</td>\n",
       "      <td>2020-01-20T00:00:00 - 2023-05-14T00:00:00</td>\n",
       "      <td>009janaki@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1 mo trial_0</td>\n",
       "      <td>2022-02-18</td>\n",
       "      <td>2023-05-14</td>\n",
       "      <td>2022-02-18T00:00:00 - 2023-05-14T00:00:00</td>\n",
       "      <td>39@mailcomfort.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1 mo trial_0</td>\n",
       "      <td>2022-07-05</td>\n",
       "      <td>2022-09-08</td>\n",
       "      <td>2022-07-05T00:00:00 - 2022-09-08T00:00:00</td>\n",
       "      <td>405sarah@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>lettuce_1</td>\n",
       "      <td>2022-09-09</td>\n",
       "      <td>2023-05-14</td>\n",
       "      <td>2022-09-09T00:00:00 - 2023-05-14T00:00:00</td>\n",
       "      <td>405sarah@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1 mo trial_0</td>\n",
       "      <td>2021-05-05</td>\n",
       "      <td>2021-06-03</td>\n",
       "      <td>2021-05-05T00:00:00 - 2021-06-03T00:00:00</td>\n",
       "      <td>zoehoffmann88@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>lettuce_1</td>\n",
       "      <td>2021-06-04</td>\n",
       "      <td>2023-05-14</td>\n",
       "      <td>2021-06-04T00:00:00 - 2023-05-14T00:00:00</td>\n",
       "      <td>zoehoffmann88@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1 mo trial_0</td>\n",
       "      <td>2021-09-09</td>\n",
       "      <td>2023-05-14</td>\n",
       "      <td>2021-09-09T00:00:00 - 2023-05-14T00:00:00</td>\n",
       "      <td>zoewohlfeld@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6 mo trial_0</td>\n",
       "      <td>2021-11-07</td>\n",
       "      <td>2023-05-14</td>\n",
       "      <td>2021-11-07T00:00:00 - 2023-05-14T00:00:00</td>\n",
       "      <td>zoezphillips@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1 mo trial_0</td>\n",
       "      <td>2021-01-17</td>\n",
       "      <td>2023-05-14</td>\n",
       "      <td>2021-01-17T00:00:00 - 2023-05-14T00:00:00</td>\n",
       "      <td>zorasicherphoto@gmail.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2207 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            type   start_dt  lead_date  \\\n",
       "0   1 mo trial_0 2019-12-23 2020-01-19   \n",
       "1   6 mo trial_1 2020-01-20 2023-05-14   \n",
       "0   1 mo trial_0 2022-02-18 2023-05-14   \n",
       "0   1 mo trial_0 2022-07-05 2022-09-08   \n",
       "1      lettuce_1 2022-09-09 2023-05-14   \n",
       "..           ...        ...        ...   \n",
       "0   1 mo trial_0 2021-05-05 2021-06-03   \n",
       "1      lettuce_1 2021-06-04 2023-05-14   \n",
       "0   1 mo trial_0 2021-09-09 2023-05-14   \n",
       "0   6 mo trial_0 2021-11-07 2023-05-14   \n",
       "0   1 mo trial_0 2021-01-17 2023-05-14   \n",
       "\n",
       "                                datetimerange                      email  \n",
       "0   2019-12-23T00:00:00 - 2020-01-19T00:00:00        009janaki@gmail.com  \n",
       "1   2020-01-20T00:00:00 - 2023-05-14T00:00:00        009janaki@gmail.com  \n",
       "0   2022-02-18T00:00:00 - 2023-05-14T00:00:00         39@mailcomfort.com  \n",
       "0   2022-07-05T00:00:00 - 2022-09-08T00:00:00         405sarah@gmail.com  \n",
       "1   2022-09-09T00:00:00 - 2023-05-14T00:00:00         405sarah@gmail.com  \n",
       "..                                        ...                        ...  \n",
       "0   2021-05-05T00:00:00 - 2021-06-03T00:00:00    zoehoffmann88@gmail.com  \n",
       "1   2021-06-04T00:00:00 - 2023-05-14T00:00:00    zoehoffmann88@gmail.com  \n",
       "0   2021-09-09T00:00:00 - 2023-05-14T00:00:00      zoewohlfeld@gmail.com  \n",
       "0   2021-11-07T00:00:00 - 2023-05-14T00:00:00     zoezphillips@gmail.com  \n",
       "0   2021-01-17T00:00:00 - 2023-05-14T00:00:00  zorasicherphoto@gmail.com  \n",
       "\n",
       "[2207 rows x 5 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cum_df_type.groupby('email').filter(lambda x: any([re.search('trial',i) for i in x['type']])).sort_values(['email','start_dt'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "04be232b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2207, 5)\n"
     ]
    }
   ],
   "source": [
    "#output.groupby('email').filter(lambda x: len(x) == 2)\n",
    "#trials = output.groupby('email').filter(lambda x: any(x['membership_type'] == 'trial'))\n",
    "#output.groupby('email').filter(lambda x: any(x['membership_type'] == 'trial'))\n",
    "#print(trials.shape)\n",
    "#this version filters the groupby object by TWO conditions\n",
    "output2 = cum_df_type.groupby('email').filter(lambda x: any([re.search('trial',i) for i in x['type']])).sort_values(['email','start_dt'])\n",
    "print(output2.shape)\n",
    "#inspect output of the program\n",
    "output2.to_csv('./trial_type_sorted.csv', index = False)\n",
    "cum_df_status.loc[cum_df_status['email'].isin(output2['email'])].sort_values(['email','start_dt']).to_csv('./trial_status_sorted.csv',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a768b43",
   "metadata": {},
   "outputs": [],
   "source": [
    "#TODO print Output2 and arrive at the expected shape of the multi conditional (above)\n",
    "#output2\n",
    "size_one = output.groupby('email').filter(lambda x: len(x) == 1)\n",
    "activityReport.loc[activityReport['email_grouping'].isin(size_one.loc[size_one['membership_type'].str.contains('trial'),'email']),].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72568844",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#enables reviewing all unique subjects associated to trials (excluding all other member types)\n",
    "trials_only = activityReport.loc[activityReport['email_grouping'].isin(size_one.loc[size_one['membership_type'].str.contains('trial'),'email']),['email_grouping','Subject_act','Activity_Date_DT_act']].sort_values(['email_grouping','Activity_Date_DT_act'])\n",
    "trials_only.to_csv('./trialsOnly.csv', index = False)\n",
    "\n",
    "#consider only unique values of Subject_act\n",
    "activityReport.loc[activityReport['email_grouping'].isin(size_one.loc[size_one['membership_type'].str.contains('trial'),'email']),['Subject_act']]['Subject_act'].unique()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bd09d749",
   "metadata": {},
   "outputs": [],
   "source": [
    "#view output of above\n",
    "activityReport.loc[activityReport['email_grouping'].isin(output2['email']),].to_csv('./trialMembersFullOutput.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d25ed46a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# in an attempt to identify status changes that are artifacts of an initial trial, select for all status changes after there is a type change (conversion of trial to other membership type)\n",
    "# need to remove entries with Status cahnges: \"Active to New\", \"New to Active\", \"Pending to New\"\n",
    "mult_groups = output.groupby(['email']).filter(lambda x: len(x) >1)\n",
    "activityReport.loc[(activityReport['email_grouping'].isin(mult_groups['email'])) & (activityReport['email_grouping'].isin(output.loc[output['membership_type'].str.contains('trial'),'email'])),['email_grouping','Activity_Date_DT_act','Subject_act','Activity_Type_act']].sort_values(['email_grouping','Activity_Date_DT_act']).to_csv('./multi_w_trial.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c68831c",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.Series(activityReport.loc[activityReport['email_grouping'].isin(output2['email']),'Subject_act'].unique())[pd.Series(activityReport.loc[activityReport['email_grouping'].isin(output2['email']),'Subject_act'].unique()).str.contains('Status changed')]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "34ab97c8",
   "metadata": {},
   "source": [
    "### TODO: start going through event and type use cases, ex. Active to Expired for Trial (when within a two or 6 month period from Trial start)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "576a24cc-1d8f-4702-b8bf-fa6c88cfe573",
   "metadata": {},
   "outputs": [],
   "source": [
    "#best practice is always to sort this dataframe first\n",
    "class_df = pd.DataFrame.from_dict(s.dict_loop('status'), orient = 'index', columns = ['start_date']).reset_index()\n",
    "print(class_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3a4e5d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "#best practice is always to sort this dataframe first\n",
    "class_df = pd.DataFrame.from_dict(st.dict_loop('class'), orient = 'index', columns = ['start_date']).reset_index()\n",
    "print(class_df)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py12",
   "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.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}