DE_Exercises.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8d5a7fcd-c604-4208-9241-04e9803fbd95",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Exercise 1\n",
    "\n",
    "Produce a “people” file with the following schema. Save it as a CSV with a header line to the working directory."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4304ceb0-b7cb-4421-85f6-7b1df23a50c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "from typing import Dict, List, Any"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c91d58b4-2985-400d-9ae5-f29c71828ccc",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# here's a utility function to load columns as specified datatypes.\n",
    "\n",
    "def load_csv(filename: str,\n",
    "             col_dtypes: Dict[str,Any],\n",
    "             date_cols: List[str] = [],\n",
    "             index_col: str = None\n",
    "            ) -> pd.DataFrame:\n",
    "    \"\"\" Function loads specific columns from csv files as specific datatypes into a DataFrame\n",
    "    \n",
    "    Parameters:\n",
    "    filename (str): Path of file to load\n",
    "    col_dtypes (dict): Map of column name to the dtype we require in the output df\n",
    "    date_cols (list): List of column names that should be converted to datetime objects\n",
    "    index_col (str): Optional column name or index to use as the DataFrame row index\n",
    "    \n",
    "    Returns:\n",
    "    pandas.DataFrame\n",
    "    \n",
    "    Note: For timestamp types, we should specify str as the dtype in the col_dtypes \n",
    "    argument. The col_dtypes dict is passed directly to pandas read_csv() and datetime\n",
    "    conversion is performed as a second step after reading data in from file. \n",
    "    \n",
    "    The only date format allowed for columns listed in date_cols is as follows:\n",
    "\n",
    "        Example: \n",
    "            Mon, 1979-03-05 21:08:54\n",
    "\n",
    "        Format String:\n",
    "            %a, %Y-%m-%d %H:%M:%S\n",
    "        \n",
    "        See https://docs.python.org/3/library/datetime.html#strftime-and-strptime-behavior\n",
    "        for more information on strftime() format string conventions.\n",
    "        \n",
    "        Also see https://pandas.pydata.org/docs/reference/api/pandas.to_datetime.html for\n",
    "        details on the utility function used to convert date strings to datetime objects\n",
    "    \"\"\"\n",
    "    \n",
    "    # Read csv file - only load columns specified in col_dtypes_keys\n",
    "    df = pd.read_csv(filename, usecols=col_dtypes.keys(), dtype=col_dtypes)\n",
    "    \n",
    "    date_fmt = \"%a, %Y-%m-%d %H:%M:%S\"\n",
    "\n",
    "    for col in date_cols:\n",
    "        df[col] = pd.to_datetime(df[col], format=date_fmt)\n",
    "    \n",
    "    # Set the index column if provided\n",
    "    if index_col:\n",
    "        df = df.set_index(index_col)\n",
    "        \n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59cbe09a-41be-4e04-85e7-afbd2126d529",
   "metadata": {},
   "source": [
    "### Constinuent Information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d8f57621-cab1-4b10-ad7c-d4a8dac8fcaa",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "700000"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# cons.csv columns and data types to read\n",
    "cons_dtypes = {\n",
    "    'cons_id': int,\n",
    "    'source': str, # note - storing as a string here, but might be more efficient as category\n",
    "    'create_dt': str,\n",
    "    'modified_dt': str\n",
    "}\n",
    "\n",
    "# load as a DataFrame\n",
    "df_cons = load_csv('data/cons.csv',\n",
    "                   col_dtypes = cons_dtypes,\n",
    "                   date_cols = ['create_dt', 'modified_dt'],\n",
    "                   index_col = 'cons_id'\n",
    "                  )\n",
    "\n",
    "# change NaN values back to blank strings in the source\n",
    "df_cons.source = df_cons.source.fillna('')\n",
    "\n",
    "df_cons.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4b80a45b-72fb-4d3d-90ac-e11327d41526",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Now, let's examine the header line and first 3 rows of data\n",
    "# df_cons.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "71a05fd5-714e-48d3-93ec-6bfeeee326ea",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# # Let's check the size of the data file and the data types for each of its columns\n",
    "# df_info.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9188e0f6-7820-4ec2-97bf-113e970b8bf2",
   "metadata": {},
   "source": [
    "### Constinuent Email Addresses\n",
    "\n",
    "***Note:***\n",
    "* Boolean columns (including 'is_primary') in all of these datasets are 1/0 numeric values. 1 means True, 0 means False. (We only want primary email addresses)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f55efc41-f28f-472f-a266-61d17fdf8c63",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "605639"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# cons_email.csv columns and data types to read\n",
    "email_dtypes = {\n",
    "    'cons_email_id': int,\n",
    "    'cons_id': int,\n",
    "    'email': str,\n",
    "    'is_primary': bool # casting these as booleans, so output will be True/False rather than 0/1  \n",
    "}\n",
    "\n",
    "# load file as a DataFrame\n",
    "df_email = load_csv('data/cons_email.csv',\n",
    "                    col_dtypes = email_dtypes,\n",
    "                    index_col = 'cons_email_id'\n",
    "                   )\n",
    "# We only want to include primary emails (see schema for people file)\n",
    "# so filter to include only rows where values of is_primary is True \n",
    "df_email = df_email.loc[df_email['is_primary']]\n",
    "\n",
    "\n",
    "# We want to make sure each row has a unique email\n",
    "rows = df_email.shape[0] # count number of rows \n",
    "unique_emails = len(df_email.email.unique()) # count number of unique values in email column\n",
    "assert rows == unique_emails # if these are equal, there are no duplicate emails (otherwise will throw error)\n",
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "2f3bea7c-df54-4bcb-ad2d-107dd3b5f75c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# View first 3 rows\n",
    "# df_email.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ef8ce4f1-b3de-4083-80e5-206d4273d9ca",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# # View last 3 rows\n",
    "# df_email.tail(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "45af987d-c634-4807-9565-a6b943eea71e",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Check the size of the data file and data types for each column\n",
    "# df_email.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8f4f7ff-ee96-41c2-9f37-ef2d648b1e99",
   "metadata": {},
   "source": [
    "### Constinuent Subscription Status\n",
    "\n",
    "***Note:***\n",
    "* We only care about subscription statuses where chapter_id is 1.\n",
    "* If an email is not present in this table, it is assumed to still be subscribed where chapter_id is 1. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8aa1cd91-bacc-47f8-b71b-1fb1f09963b8",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "275484"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# cons_email_chapter_subscription.csv columns and data types to read\n",
    "subs_dtypes = {\n",
    "    'cons_email_chapter_subscription_id': int,\n",
    "    'cons_email_id': int,\n",
    "    'chapter_id': int,\n",
    "    'isunsub': bool\n",
    "}\n",
    "\n",
    "# load file as a DataFrame\n",
    "df_subs = load_csv('data/cons_email_chapter_subscription.csv',\n",
    "                   col_dtypes = subs_dtypes,\n",
    "                   index_col = 'cons_email_chapter_subscription_id'\n",
    "                  )\n",
    "\n",
    "# \"We only care about subscription statuses where chapter_id is 1\", so filter to \n",
    "# include only rows where chapter_id == 1\n",
    "df_subs = df_subs.loc[ df_subs.chapter_id == 1 ]\n",
    "df_subs.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "3e7ebce5-78ad-4eef-b3c7-527d5ac246e2",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "431649"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 431,649 email addresses have no subscriptions! (assume subscribed to chapter_id 1)\n",
    "# From instructions:\n",
    "# \"If an email is not present in this table, it is assumed to still be subscribed where chapter_id is 1\"\n",
    "# So, we need to know which email addresses in df_email do not have a subscription record in df_subs\n",
    "# To find this out, we do an anti-join between df_email and df_subs, then save as a new DataFrame\n",
    "df_sub_assumed = df_email.merge(df_subs, on='cons_email_id', how='outer', indicator=True) # add col called _merge containing str indicating which df record came from\n",
    "df_sub_assumed = df_sub_assumed[df_sub_assumed._merge == 'left_only'].drop('_merge', axis=1) # now drop _merge column, we don't need it any more\n",
    "df_sub_assumed = df_sub_assumed.set_index('cons_email_id') # setting index here for neatness\n",
    "df_sub_assumed = df_sub_assumed[['email']] # now limit columns to include only cons_email_id and email\n",
    "df_sub_assumed['isunsub'] = False # now add col called isunsub and set to False (because we're making the assumption these are all subscribed chapter 1)\n",
    "\n",
    "# Now let's ensure we have no duplicate emails in df_nosub\n",
    "rows = df_sub_assumed.shape[0] # check number of rows in df_nosub\n",
    "unique = len(df_sub_assumed.email.unique())\n",
    "assert rows == unique\n",
    "\n",
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "ec788f21-8bc8-48b8-adb6-6564e18a89f8",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "173990"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now create a new df containing every email from df_email that does have a record in df_subs\n",
    "df_sub_confirmed = df_email.merge(df_subs, on='cons_email_id', how='inner')\n",
    "df_sub_confirmed = df_sub_confirmed.set_index('cons_email_id')\n",
    "\n",
    "# We only want to include cons_email_id, cons_id, email, and isunsub cols, so drop the others\n",
    "df_sub_confirmed = df_sub_confirmed.drop(['is_primary', 'chapter_id'], axis=1)\n",
    "\n",
    "# Check that every email is unique\n",
    "rows = df_sub_confirmed.shape[0]\n",
    "unique = len(df_sub_confirmed.email.unique())\n",
    "assert rows == unique\n",
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "8c53f1f4-536b-420e-a32d-9ed213ba180e",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "605639"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Concatentate df_sub_assumed with df_sub_confirmed \n",
    "df_email_comb = pd.concat([df_sub_confirmed, df_sub_assumed])\n",
    "\n",
    "# Check that we have exactly one unique record for every primary email\n",
    "rows = df_email_comb.shape[0]\n",
    "assert rows == unique_emails\n",
    "assert rows == len(df_email.email.unique())\n",
    "rows"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63afbe4f-bb36-4005-8d48-c2555cf3c9c1",
   "metadata": {
    "tags": []
   },
   "source": [
    "### People"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e7be5f1a-3250-4f8b-9a97-1f583aa144a0",
   "metadata": {
    "tags": []
   },
   "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>email</th>\n",
       "      <th>is_unsub</th>\n",
       "      <th>code</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>updated_dt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>aaron64@yahoo.com</td>\n",
       "      <td>True</td>\n",
       "      <td></td>\n",
       "      <td>1992-06-01 06:07:45</td>\n",
       "      <td>1986-07-28 03:41:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>wyattvincent@hotmail.com</td>\n",
       "      <td>True</td>\n",
       "      <td></td>\n",
       "      <td>1993-05-23 08:00:18</td>\n",
       "      <td>1983-05-07 09:29:18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tspencer@hotmail.com</td>\n",
       "      <td>True</td>\n",
       "      <td>twitter</td>\n",
       "      <td>1986-10-31 03:24:05</td>\n",
       "      <td>1979-09-22 05:01:01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ogarcia@gmail.com</td>\n",
       "      <td>True</td>\n",
       "      <td></td>\n",
       "      <td>2010-10-03 05:49:12</td>\n",
       "      <td>1996-02-03 15:32:15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>madeline69@mccarthy-jackson.com</td>\n",
       "      <td>True</td>\n",
       "      <td>twitter</td>\n",
       "      <td>2007-08-22 02:46:01</td>\n",
       "      <td>2014-07-15 01:05:24</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>173985</th>\n",
       "      <td>omartin@cox.org</td>\n",
       "      <td>True</td>\n",
       "      <td></td>\n",
       "      <td>2002-12-07 16:39:10</td>\n",
       "      <td>2020-05-22 20:22:21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173986</th>\n",
       "      <td>josephpeterson@freeman-smith.biz</td>\n",
       "      <td>True</td>\n",
       "      <td></td>\n",
       "      <td>2017-05-05 10:31:49</td>\n",
       "      <td>2009-12-16 06:41:50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173987</th>\n",
       "      <td>sharonlong@gibson.com</td>\n",
       "      <td>True</td>\n",
       "      <td>twitter</td>\n",
       "      <td>1991-11-25 05:34:35</td>\n",
       "      <td>2002-01-03 14:24:17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173988</th>\n",
       "      <td>nicholas74@gibbs.com</td>\n",
       "      <td>True</td>\n",
       "      <td>organic</td>\n",
       "      <td>1977-01-22 11:14:18</td>\n",
       "      <td>2014-04-29 03:23:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173989</th>\n",
       "      <td>deckerwalter@hess.net</td>\n",
       "      <td>True</td>\n",
       "      <td>organic</td>\n",
       "      <td>1999-08-13 14:13:51</td>\n",
       "      <td>2018-11-30 07:11:08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>173990 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   email  is_unsub     code  \\\n",
       "0                      aaron64@yahoo.com      True            \n",
       "1               wyattvincent@hotmail.com      True            \n",
       "2                   tspencer@hotmail.com      True  twitter   \n",
       "3                      ogarcia@gmail.com      True            \n",
       "4        madeline69@mccarthy-jackson.com      True  twitter   \n",
       "...                                  ...       ...      ...   \n",
       "173985                   omartin@cox.org      True            \n",
       "173986  josephpeterson@freeman-smith.biz      True            \n",
       "173987             sharonlong@gibson.com      True  twitter   \n",
       "173988              nicholas74@gibbs.com      True  organic   \n",
       "173989             deckerwalter@hess.net      True  organic   \n",
       "\n",
       "                created_dt          updated_dt  \n",
       "0      1992-06-01 06:07:45 1986-07-28 03:41:12  \n",
       "1      1993-05-23 08:00:18 1983-05-07 09:29:18  \n",
       "2      1986-10-31 03:24:05 1979-09-22 05:01:01  \n",
       "3      2010-10-03 05:49:12 1996-02-03 15:32:15  \n",
       "4      2007-08-22 02:46:01 2014-07-15 01:05:24  \n",
       "...                    ...                 ...  \n",
       "173985 2002-12-07 16:39:10 2020-05-22 20:22:21  \n",
       "173986 2017-05-05 10:31:49 2009-12-16 06:41:50  \n",
       "173987 1991-11-25 05:34:35 2002-01-03 14:24:17  \n",
       "173988 1977-01-22 11:14:18 2014-04-29 03:23:45  \n",
       "173989 1999-08-13 14:13:51 2018-11-30 07:11:08  \n",
       "\n",
       "[173990 rows x 5 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Prepare \"people\" table\n",
    "df_ppl = df_email_comb.join(df_cons, on='cons_id', how='inner') #inner join because of emails with no constituent\n",
    "df_ppl = df_ppl.reset_index()\n",
    "df_ppl = df_ppl.drop(['cons_id', 'cons_email_id'], axis = 1)\n",
    "\n",
    "# now rename cols: source->code, isunsub->is_unsub, create_dt->created_dt, and modified_dt->updated_dt\n",
    "df_ppl.rename(columns={'source':'code', 'isunsub':'is_unsub', 'create_dt':'created_dt', 'modified_dt': 'updated_dt'}, inplace=True)\n",
    "df_ppl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "cc371ae5-d64c-455c-a427-7639948a1ea7",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_ppl.to_csv('people.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1dc8bdd-608a-4182-bd18-24ff9b651315",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Exercise 2\n",
    "\n",
    "Use the output of #1 to produce an “acquisition_facts” file with the following schema that aggregates stats about when people in the dataset were acquired. Save it to the working directory."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "905c9e06-6a7d-417f-8fdc-805fb621cc23",
   "metadata": {
    "tags": []
   },
   "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>acquisitions</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>acquisition_dt</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1970-01-01</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1970-01-02</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1970-01-03</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1970-01-04</th>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1970-01-05</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-27</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-28</th>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-29</th>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-30</th>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-07-01</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>18443 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                acquisitions\n",
       "acquisition_dt              \n",
       "1970-01-01                 8\n",
       "1970-01-02                 9\n",
       "1970-01-03                 8\n",
       "1970-01-04                11\n",
       "1970-01-05                13\n",
       "...                      ...\n",
       "2020-06-27                 7\n",
       "2020-06-28                11\n",
       "2020-06-29                11\n",
       "2020-06-30                11\n",
       "2020-07-01                 8\n",
       "\n",
       "[18443 rows x 1 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_acquisition = df_ppl\n",
    "# df_ppl.rename(columns={'source':'code', 'isunsub':'is_unsub', 'create_dt':'created_dt', 'modified_dt': 'updated_dt'}, inplace=True)\n",
    "\n",
    "df_acquisition['acquisition_dt'] = df_acquisition['created_dt'].dt.date\n",
    "df_acquisition = df_acquisition[['acquisition_dt']]\n",
    "df_acquisition = df_acquisition.groupby(['acquisition_dt']).size()\n",
    "df_acquisition = df_acquisition.to_frame('acquisitions')\n",
    "df_acquisition"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "94c03223-6fd7-48cc-b34c-5004899debc8",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "rows = df_ppl.shape[0]\n",
    "sum_all = df_acquisition.acquisitions.sum()\n",
    "# print(f\"{rows} {sum_all}\")\n",
    "assert rows == sum_all # ensur total num of acquisitions equals total num of people"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "9077c453-7b6d-4776-964e-8e01af705a66",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_acquisition.to_csv('acquisition_facts.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}