account_flows.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Workflow:\n",
    "1) pull combinations of current and previous status from the db -> in the form of a contingency table\n",
    "2) ensure their inclusion in the 'statusCombinationMatrix_acctFlows.ods' file\n",
    "3) assign values to the scoring vector (1 - for inclusion; 0 - for exclusion)\n",
    "4) import the .ods file <- use it to build vectors for each scoring category of the Acct Flows table\n",
    "5) **multiply the vectors**: the \"scoring vector\" and the freq table from the contingency table from #1. NOTE: ensure that the \"freq\" vector is in the same order as the scoring vector (sort by the combinations)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import re\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine, Table, text, MetaData # a CORE approach\n",
    "from sqlalchemy.sql import select\n",
    "from sqlalchemy.sql import func"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/home/candela/Documents/greeneHill/membershipReportsCIVI/github/greeneHill'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.getcwd()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Constructing the list of month-end tuples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#get the dates I want in string format\n",
    "# can conceivably create a matrix of scores (as long as the combos don't change)\n",
    "def last_day_of_month(any_day):\n",
    "    # The day 28 exists in every month. 4 days later, it's always next month\n",
    "    next_month = any_day.replace(day=28) + datetime.timedelta(days=4)\n",
    "    # subtracting the number of the current day brings us back one month\n",
    "    return next_month - datetime.timedelta(days=next_month.day)\n",
    "\n",
    "#for month in range(1, 13):\n",
    "#    print(last_day_of_month(datetime.date(2023, month, 7)))\n",
    "\n",
    "#ea of these is a seq that contains all the last days of each month\n",
    "twentythree = [last_day_of_month(datetime.date(2023, month, 7)) for month in range(1, 13)]\n",
    "twentyfour = [last_day_of_month(datetime.date(2024, month, 7)) for month in range(1, 13)] \n",
    "twentyfive = [last_day_of_month(datetime.date(2025, month, 7)) for month in range(1, 13)] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#month_end_df = pd.DataFrame({'twentythree':twentythree,'year':2023})\n",
    "month_end_df = pd.DataFrame({'twentyfour':twentyfour,'year':2024})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#make a replacement column for null values (expect it will only occur above the January record)\n",
    "month_end_df['prev_yr']=month_end_df.apply(lambda x: str(x['year']-1)+'-12-31', axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "month_end_shifted = month_end_df.shift(periods=[0,1],axis = 0)\n",
    "#below may not be necessary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#BE SURE TO CHANGE THE FIELD NAMES TO REFLECT THE MEASURE YEAR\n",
    "month_end_shifted['twentyfour_1'] = month_end_shifted['twentyfour_1'].fillna(month_end_shifted['prev_yr_0'])\n",
    "month_end_shifted = month_end_shifted.iloc[:,:].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# a collection of tuples, ea of which provides the dates combo for the query\n",
    "#BE SURE TO CHANGE THE FIELD NAMES TO REFLECT THE MEASURE YEAR\n",
    "calendar_range_iter = [tuple(i.values()) for i in month_end_shifted[['twentyfour_1','twentyfour_0']].to_dict('records')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to database to gather status combinations and counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connection to the 172.17.0.2 for user root created successfully.\n"
     ]
    }
   ],
   "source": [
    "#connect to the database instance\n",
    "user = 'root'\n",
    "password = 'baeldung'\n",
    "host = '172.17.0.2'\n",
    "port = 3306\n",
    "database = 'membership'\n",
    "\n",
    "def get_connection():\n",
    "\treturn 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)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The below query was initially developed in file \"membership_queries_dev.sql\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fill_query(tup_dates:tuple):\n",
    "    return \"WITH curr AS (select mt_email curr_mt_email, mem_type curr_mem_type, activity_calc curr_activity_calc, activity curr_activity from stack_job2 WHERE date('\"+tup_dates[1]+\"') between start_dt AND lead_date ORDER BY mt_email), prev AS (select mt_email prev_mt_email, mem_type prev_mem_type, activity_calc prev_activity_calc, activity prev_activity from stack_job2 WHERE date('\"+tup_dates[0]+\"') between start_dt AND lead_date ORDER BY mt_email), final_tbl AS (SELECT date('\"+tup_dates[1]+\"') current_month, curr_activity_calc, prev_activity_calc, count(distinct curr_mt_email) unq_email FROM curr LEFT JOIN prev ON curr_mt_email = prev_mt_email GROUP BY 1,2,3) SELECT * FROM final_tbl\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## MARGINALLY DIFFERENT QUERY TO THE ONE USED IN fill_query() - it also queries for the last trial type - this will enable counting/filling the currently vacant 'trial_conversion' field of the Account Flows table\n",
    "#NOTE: source code for table trial_meta_all located in membership_queries_dev.sql\n",
    "#NOTE 2: the workflow will need to be augmented to run the CREATE TABLE query for trial_meta_all\n",
    "'''WITH curr AS (\n",
    "select mt_email curr_mt_email, mem_type curr_mem_type, activity_calc curr_activity_calc, activity curr_activity, tma.last_trial_type \n",
    "from stack_job2 \n",
    "LEFT JOIN trial_meta_all tma ON mt_email = tma.email \n",
    "WHERE date('2024-05-31') between start_dt AND lead_date \n",
    "GROUP BY 1,2,3,4,5), \n",
    "prev AS (\n",
    "select mt_email prev_mt_email, mem_type prev_mem_type, activity_calc prev_activity_calc, activity prev_activity from stack_job2 WHERE date('2024-04-30') between start_dt AND lead_date ORDER BY mt_email)\n",
    "SELECT *\n",
    "FROM curr \n",
    "LEFT JOIN prev ON curr_mt_email = prev_mt_email\n",
    "ORDER BY curr_mt_email'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# a dictionary of the sql query string, with key = representative month\n",
    "#dict: key = latest date of the month tuple; value = sql query text\n",
    "query_cont = {}\n",
    "for min_tup in calendar_range_iter:\n",
    "    query_cont[min_tup[1]]=fill_query(min_tup)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#build a dictionary of the result sets, setting the key to the month\n",
    "df_dict = {}\n",
    "with engine.connect() as conn:\n",
    "    for k,query in query_cont.items():\n",
    "        df_dict[k] = pd.read_sql(query,conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TODO: Build a function that creates the tuple index for the \"scores\" vector. This function can then be iteratively applied to ea result set/DataFrame returned from the db (\"df_dict\" object)\n",
    "The key of each dictionary item contains the month of measure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#can theoretically apply this iteratively to ea DataFrame in the result set dict\n",
    "#function that takes in a dataframe of a month's scores along with status fields, which then combines the fields into a tuple of the status combination. Extracts the unq.email field and converts that into the scores Series with index corresponding to the combo tuple\n",
    "\n",
    "#TODO can refactor the below function to return TWO Series: the original \"scores_series_vector\" and a 2nd that returns the count of trials by \"combo\" tuple; this Series would then follow the same path and be used to multiply against the categories matrix - counting trial members for any category I wish (priority is 'trial_conversion' field)\n",
    "def package_scores_vector(df: pd.DataFrame):\n",
    "    #in order to avoid issues with \"None\" values in the \"member status\" combinations, convert None to \"None\" (string)\n",
    "    df = df.assign(prev_activity_calc = df['prev_activity_calc'].fillna('None'), curr_activity_calc = df['curr_activity_calc'].fillna('None'))\n",
    "\n",
    "    #make the 'combo' tuple\n",
    "    test_df = df.assign(combo = [tuple(i.values()) for i in df[['curr_activity_calc','prev_activity_calc']].to_dict('records')])\n",
    "\n",
    "    #the vector that I ultimately multiply by the 'category' matrix AFTER I compile the index intersection\n",
    "    scores_series_vector = pd.Series(data = test_df['unq_email'].values, index = test_df['combo'])\n",
    "\n",
    "    return scores_series_vector"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating the .ods file\n",
    "'df' DataFrame = resultset from db, and contains the counts/frequencies\n",
    "Don't create the .ods file explicitly. The original file is valuable because I types out the categories manually. If I want to somehow replicate the .ods file programmatically, I'll need to reverse engineer it after importing the pre-existing file.\n",
    "\n",
    "### only run this when I want to CREATE an ods from scratch"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "#a list of tuples - ea of which contain distinct combinations of actions\n",
    "# I copy this output to create the .ods file\n",
    "df = df.assign(combo = [tuple(i.values()) for i in df[['curr_activity_calc','prev_activity_calc']].to_dict('records')])\n",
    "\n",
    "#/github/statusCombinationMatrix_acctFlows.ods\n",
    "\n",
    "#[tuple(i.values()) for i in df[['curr_activity_calc','prev_activity_calc']].to_dict('records')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing the ODS file \n",
    "This file is a matrix table that controls what goes into each category (how to tally sums)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#function to accomplish all processes in this segment: a) import ODS; b) parse and reunify the status tuple that serves as the combo index; c) fillna() properly\n",
    "\n",
    "def import_treat_cat_df(filepath: str):\n",
    "    #NOTE: the combo tuple will be messed up, and needs to be re-formated and ultimately recognized as a tuple in python\n",
    "    cats_df = pd.read_excel(filepath, engine=\"odf\")\n",
    "    cats_df.columns = [i.replace(\" \",\"_\").lower() for i in cats_df.columns]\n",
    "\n",
    "    #use regex to remove the artifacts from the tuple encoding when the .ods file was created from the df copy (done manually)\n",
    "    pattern = re.compile(\"[a-zA-Z0-9_ ]+\")\n",
    "\n",
    "    cats_df = cats_df.assign(curr_activity_calc_norm = cats_df['curr_activity_calc'].apply(lambda x: list(re.findall(pattern, x))[-1].strip()), prev_activity_calc_norm = cats_df['prev_activity_calc'].apply(lambda x: list(re.findall(pattern, x))[-1].strip()))\n",
    "\n",
    "    #create the final tuple column\n",
    "    cats_df['tuple_index'] = tuple(zip(cats_df['curr_activity_calc_norm'], cats_df['prev_activity_calc_norm']))\n",
    "\n",
    "    #select relevant fields\n",
    "    cats_df = cats_df.loc[:,[i for i in list(cats_df.columns) if 'activity' not in i]]\n",
    "\n",
    "    #set the index\n",
    "    cats_df.set_index('tuple_index', inplace = True)\n",
    "\n",
    "    #converts the matrix to a 1,0\n",
    "    cats_df = cats_df.fillna(0)\n",
    "\n",
    "    return cats_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import the ods file\n",
    "#NOTE: the combo tuple will be messed up, and needs to be re-formated and ultimately recognized as a tuple in python\n",
    "cats_df = pd.read_excel(\"statusCombinationMatrix_acctFlows.ods\", engine=\"odf\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "cats_df.columns = [i.replace(\" \",\"_\").lower() for i in cats_df.columns]\n",
    "#scores_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "REGEX: Notice how the values returned from the spreadsheet contain artifacts of the tuple and list objects from python. These need to be removed.\n",
    "I may want to explore building a function then looping through the Series as the .str suite of functions doesn't help me well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "pattern = re.compile(\"[a-zA-Z0-9_ ]+\")\n",
    "\n",
    "cats_df = cats_df.assign(curr_activity_calc_norm = cats_df['curr_activity_calc'].apply(lambda x: list(re.findall(pattern, x))[-1].strip()), prev_activity_calc_norm = cats_df['prev_activity_calc'].apply(lambda x: list(re.findall(pattern, x))[-1].strip()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create the final tuple column\n",
    "cats_df['tuple_index'] = tuple(zip(cats_df['curr_activity_calc_norm'], cats_df['prev_activity_calc_norm']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Deliver the category matrix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "#converts the vector to a 1,0\n",
    "cats_df = cats_df.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Procedure to arrange, then multiply the scores vector & category matrix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#first, work on the result set df: grabbing the 'unq_email' field which contains the scores by combo, set it as its own Series and define the index as the combo values\n",
    "#predecessor to test_df  is df\n",
    "\n",
    "#NO NEED TO RUN THIS; THIS IS AN ATOMIC PROROTYPE TO A MORE ABSTRACT FUNCTION 'package_scores_vector()'\n",
    "scores_series_vector = pd.Series(data = test_df['unq_email'].values, index = test_df['combo'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#NO NEED TO RUN THIS\n",
    "cats_df2 = cats_df.loc[:,[i for i in list(cats_df.columns) if 'activity' not in i]]\n",
    "\n",
    "cats_df2.set_index('tuple_index', inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sequence of functions and master function that carry out the matrix-vector multiplication"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "#function to ingest the matrix and vector, compile the intersection and return the equal sized objects (ready to multiply) in a tuple\n",
    "# the cat_matrix is the ods file as processed above, terminating in the fillna(0) operation\n",
    "def sync_objects(scores_vector:pd.Series, cat_df:pd.DataFrame):\n",
    "    cats_df2_index= list(cat_df.index)\n",
    "    scores_index = list(scores_vector.index)\n",
    "\n",
    "    # quantify the # of columns in the scores/contingency vector and NOT in the categorical matrix\n",
    "    #outer = list(set() - set(cats_df2_index))\n",
    "    outer = list(set(scores_index).difference(cats_df2_index))\n",
    "    #main_list = list(set(list_2) - set(list_1))\n",
    "\n",
    "\n",
    "    index_intersection = list(set(cats_df2_index) & set(scores_index))\n",
    "\n",
    "    cats_df2 = cat_df.loc[index_intersection,:].sort_index()\n",
    "\n",
    "    scores_series_vector_prod = scores_vector[index_intersection].sort_index()\n",
    "\n",
    "\n",
    "    return scores_series_vector_prod, cats_df2, outer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#function to convert \"categories\" df to matrix, then execute the multiplcation\n",
    "#requires the vector and matrix to be \"right-sized\"\n",
    "def apply_multiply(scores_vec:pd.Series, cat_df:pd.DataFrame):\n",
    "    if scores_vec.shape[0] == cat_df.shape[0]:\n",
    "        mat = cat_df.to_numpy()\n",
    "#scores_series_vector = db resultset of status combinations and counts\n",
    "        new_mat = np.matmul(np.transpose(scores_vec.array), mat)\n",
    "\n",
    "        #returns a Series with the category balances\n",
    "        return pd.Series(new_mat, index = cat_df.columns)\n",
    "    \n",
    "    else:\n",
    "        raise TypeError(\"objects are not of appropriate size\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Applying all the functions at once.** Ideally each iteration will return the measures of ea category, and preserves the measure date as either an index or field value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "cats_df = import_treat_cat_df(\"statusCombinationMatrix_acctFlows.ods\")\n",
    "#scores_vector = package_scores_vector(df_dict['2023-03-31'])\n",
    "#score_vec, cat_df = sync_objects(scores_vector,cats_df)\n",
    "#scores_vector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "#iteratively apply the vector-matrix multiply to ea item in df_dict (first convert the stored df in df_dict to a score vector, then multiply by the categories matrix)\n",
    "monthlies_dict = {}\n",
    "for k,v in df_dict.items():\n",
    "    scores_vec = package_scores_vector(v)\n",
    "    score_vec2, cats_df2,_  = sync_objects(scores_vec,cats_df)\n",
    "    monthlies_dict[k]=apply_multiply(score_vec2, cats_df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthlies_df = pd.DataFrame.from_dict(monthlies_dict,orient = 'index')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>leave</th>\n",
       "      <th>active</th>\n",
       "      <th>winback</th>\n",
       "      <th>new_cancel/suspended</th>\n",
       "      <th>trial_conversion</th>\n",
       "      <th>new_signup</th>\n",
       "      <th>new_leave</th>\n",
       "      <th>new_winback</th>\n",
       "      <th>re-activation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-01-31</th>\n",
       "      <td>269.0</td>\n",
       "      <td>315.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-02-29</th>\n",
       "      <td>272.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-03-31</th>\n",
       "      <td>278.0</td>\n",
       "      <td>317.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-04-30</th>\n",
       "      <td>281.0</td>\n",
       "      <td>331.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-05-31</th>\n",
       "      <td>289.0</td>\n",
       "      <td>335.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            leave  active  winback  new_cancel/suspended  trial_conversion  \\\n",
       "2024-01-31  269.0   315.0     50.0                   6.0               0.0   \n",
       "2024-02-29  272.0   320.0     53.0                  10.0               0.0   \n",
       "2024-03-31  278.0   317.0     52.0                  12.0               0.0   \n",
       "2024-04-30  281.0   331.0     56.0                  13.0               0.0   \n",
       "2024-05-31  289.0   335.0     60.0                  14.0               0.0   \n",
       "\n",
       "            new_signup  new_leave  new_winback  re-activation  \n",
       "2024-01-31        20.0        8.0          5.0            1.0  \n",
       "2024-02-29         9.0        7.0          8.0            1.0  \n",
       "2024-03-31         7.0       10.0          9.0            0.0  \n",
       "2024-04-30        19.0        9.0         14.0            0.0  \n",
       "2024-05-31        13.0       14.0         12.0            0.0  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "monthlies_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthlies_df.to_csv('./monthlies_2024.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inspect all the orphaned records and potentially add them to the master categories matrix\n",
    "\n",
    "run the below operations, including to_csv exporting, then c+p the rows of the csv onto the 'statusCombinationMatrix_acctFlows.ods to consolidate all the status permutations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "from itertools import chain\n",
    "\n",
    "set(list(chain(*orphaned)))\n",
    "\n",
    "#turn into a dataframe or vector, then c+p into the .ods file\n",
    "pd.Series(list(chain(*orphaned))).drop_duplicates().to_csv('./orphaned.csv',index = False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py_312",
   "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": 2
}