Idiomatic Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Idiomatic Pandas\n",
    "\n",
    "© MetaSnake 2022, CC BY-NC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 0
   },
   "outputs": [],
   "source": [
    "import glob\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "!pip install pandas matplotlib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "pd.show_versions()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!ls *.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [pd.read_csv(f, parse_dates=['time'], na_values='-') for f in glob.glob('tweet_activity_metrics___mharrison___*')]\n",
    "df = pd.concat(data, ignore_index=True).sort_values('time')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('__mharrison__2020-2021.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "pd.read_csv('__mharrison__2020-2021.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load data from Web"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://github.com/mattharrison/datasets/raw/master/data/__mharrison__2020-2021.csv'\n",
    "df = pd.read_csv(url, parse_dates=['time'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Data Exercise\n",
    "\n",
    "* Load the data using the cell above.\n",
    "* If you can't do this please alert!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring\n",
    "\n",
    "Definitions\n",
    "\n",
    "* *Impressions* - Number of times people saw the tweet\n",
    "* *Engagements* - Number of \"interactions\" (clicks, replies, retweets, likes)\n",
    "* *Engagement rate* - Engagements divided by impressions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.options.display.max_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from IPython.display import display\n",
    "with pd.option_context('display.max_columns', 240):\n",
    "    display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Explore Exercise\n",
    "* Use `.describe` to view the summary statistics\n",
    "* Use `.corr` to view column correlations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.memory_usage()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.memory_usage(deep=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.memory_usage(deep=True).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(df\n",
    " .select_dtypes(int).describe()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(df\n",
    " #.select_dtypes(float)\n",
    " .select_dtypes('float64')\n",
    " .describe()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(df\n",
    " .impressions\n",
    " .astype(int))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.assign?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(df\n",
    " .assign(impressions=df.impressions.astype(int),\n",
    "         engagements=df.engagements.astype(int)\n",
    "         # lots of this here\n",
    "        )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# also note\n",
    "(df\n",
    " .assign(impressions=df.impressions.astype(int),\n",
    "         engagement rate=df.engagements rate.astype(int)\n",
    "         # lots of this here\n",
    "        )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# fix names\n",
    "(df\n",
    " .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.filter(regex=r'promoted')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(df\n",
    " .drop(columns=[c for c in df.columns if 'promoted' in c])\n",
    " .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    " .describe()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# be careful with renaming\n",
    "(df\n",
    " .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    " .drop(columns=[c for c in df.columns if 'promoted' in c])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def drop_col(df_, pattern):\n",
    "     return df_.drop(columns=[c for c in df_.columns if pattern in c])\n",
    "\n",
    "(df\n",
    " .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    " #.pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    " .pipe(drop_col, pattern='promoted')\n",
    " .drop(columns=['Tweet_id', 'permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 0,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "\n",
    "(df\n",
    " .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    " .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    " .drop(columns=['Tweet_id', 'permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    " .memory_usage(deep=True)\n",
    " .sum()  # 3 megs\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 0
   },
   "outputs": [],
   "source": [
    "df.pipe?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Column Cleanup Exercise\n",
    "(Please don't mutate here!)\n",
    "\n",
    "* Use `.loc` to select the *impressions* and *engagement* columns\n",
    "* Use `.drop` to select the *impressions* and *engagement* columns\n",
    "* Use `.rename` to rename *impressions* to *imp* and *engagement* to *eng*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ok, Types for real"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "\n",
    "(df\n",
    " .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    " .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    " .drop(columns=['Tweet_id', 'permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    " .describe()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.iinfo('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "for size in ['uint8', 'uint16', 'uint32', 'int8', 'int16', 'int32', 'int64']:\n",
    "    print(f'{size=} {np.iinfo(size)}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "\n",
    "(df\n",
    " .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    " .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    " .drop(columns=['Tweet_id', 'permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    " .assign(impressions=df.impressions.astype('uint32'),\n",
    "         engagements=df.engagements.astype('uint16'),\n",
    "        )\n",
    " .describe()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "kwargs = {}\n",
    "for col in df.select_dtypes(float).columns:\n",
    "    print(col)\n",
    "    kwargs[col] = df[col].astype(int)\n",
    "kwargs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# use dict comp if you don't want to type every column\n",
    "# assign w/ dict comp. and lambda\n",
    "(df\n",
    " .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    " .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    " .drop(columns=['Tweet_id', 'permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    " .assign(impressions=df.impressions.astype('uint32'),\n",
    "         engagements=df.engagements.astype('uint16'),\n",
    "         **{c:lambda df_, c=c:df_[c].astype('uint8') for c in ['replies', 'hashtag_clicks', 'follows']}  # less than 255\n",
    "        )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# why c=c?\n",
    "(df\n",
    " .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    " .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    " .drop(columns=['Tweet_id', 'permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    " .assign(impressions=df.impressions.astype('uint32'),\n",
    "         engagements=df.engagements.astype('uint16'),\n",
    "         **{c:lambda df_:df_[c].astype('uint8') for c in ['replies', 'hashtag_clicks', 'follows']},  # less than 255\n",
    "         **{c:lambda df_:df_[c].astype('uint16') for c in ['retweets', 'likes', 'user_profile_clicks', 'url_clicks', \n",
    "                                          'detail_expands', 'media_views', 'media_engagements']}  # less than 65,535\n",
    "        )\n",
    " #.corr()\n",
    " .describe()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://docs.python.org/3/faq/programming.html#why-do-lambdas-defined-in-a-loop-with-different-values-all-return-the-same-result\n",
    "squares = []\n",
    "for x in range(5):\n",
    "    squares.append(lambda: x**2)\n",
    "for s in squares:\n",
    "    print(s())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://docs.python.org/3/faq/programming.html#why-do-lambdas-defined-in-a-loop-with-different-values-all-return-the-same-result\n",
    "squares = []\n",
    "for x in range(5):\n",
    "    squares.append(lambda x=x: x**2)\n",
    "for s in squares:\n",
    "    print(s())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(df\n",
    " .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    " .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    " .drop(columns=['Tweet_id', 'permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    " .assign(impressions=df.impressions.astype('uint32'),\n",
    "         engagements=df.engagements.astype('uint16'),\n",
    "         **{c:lambda df_, c=c:df_[c].astype('uint8') for c in ['replies', 'hashtag_clicks', 'follows']},  # less than 255\n",
    "         **{c:lambda df_, c=c:df_[c].astype('uint16') for c in ['retweets', 'likes', 'user_profile_clicks', 'url_clicks', \n",
    "                                          'detail_expands', 'media_views', 'media_engagements']}  # less than 65,535\n",
    "        )\n",
    " .describe()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(df\n",
    " .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    " .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    " .drop(columns=['Tweet_id', 'permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    " .assign(impressions=df.impressions.astype('uint32'),\n",
    "         engagements=df.engagements.astype('uint16'),\n",
    "         **{c:lambda df_, c=c:df_[c].astype('uint8') for c in ['replies', 'hashtag_clicks', 'follows']},  # less than 255\n",
    "         **{c:lambda df_, c=c:df_[c].astype('uint16') for c in ['retweets', 'likes', 'user_profile_clicks', 'url_clicks', \n",
    "                                          'detail_expands', 'media_views', 'media_engagements']}  # less than 65,535\n",
    "         \n",
    "        )\n",
    " .memory_usage(deep=True) \n",
    " .sum()  # was 3 megs\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# most is from text\n",
    "(df\n",
    " .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    " .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    " .drop(columns=['Tweet_id', 'permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    " .assign(impressions=df.impressions.astype('uint32'),\n",
    "         engagements=df.engagements.astype('uint16'),\n",
    "         **{c:lambda df_, c=c:df_[c].astype('uint8') for c in ['replies', 'hashtag_clicks', 'follows']},  # less than 255\n",
    "         **{c:lambda df_, c=c:df_[c].astype('uint16') for c in ['retweets', 'likes', 'user_profile_clicks', 'url_clicks', \n",
    "                                          'detail_expands', 'media_views', 'media_engagements']}  # less than 65,535\n",
    "         \n",
    "        )\n",
    " .memory_usage(deep=True) \n",
    " .pipe(lambda ser: ser/ser.sum()*100)\n",
    "# .sum()  # was 3 megs\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# convert first part of permalink to category and add back tweet_id\n",
    "(df\n",
    " .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    " .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    " .drop(columns=['permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    " .assign(impressions=df.impressions.astype('uint32'),\n",
    "         engagements=df.engagements.astype('uint16'),\n",
    "         **{c:lambda df_, c=c:df_[c].astype('uint8') for c in ['replies', 'hashtag_clicks', 'follows']},  # less than 255\n",
    "         **{c:lambda df_, c=c:df_[c].astype('uint16') for c in ['retweets', 'likes', 'user_profile_clicks', 'url_clicks', \n",
    "                                          'detail_expands', 'media_views', 'media_engagements']},  # less than 65,535\n",
    "         Tweet_permalink=lambda df_: pd.Series('https://twitter.com/__mharrison__/status/', dtype='category', \n",
    "                                               index=df_.index),\n",
    "        )\n",
    " .memory_usage(deep=True) \n",
    " .sum()  # was 3 megs\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# convert first part of permalink to category and add back tweet_id\n",
    "(df\n",
    " .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    " .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    " .drop(columns=['permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    " .assign(impressions=df.impressions.astype('uint32'),\n",
    "         engagements=df.engagements.astype('uint16'),\n",
    "         **{c:lambda df_, c=c:df_[c].astype('uint8') for c in ['replies', 'hashtag_clicks', 'follows']},  # less than 255\n",
    "         **{c:lambda df_, c=c:df_[c].astype('uint16') for c in ['retweets', 'likes', 'user_profile_clicks', 'url_clicks', \n",
    "                                          'detail_expands', 'media_views', 'media_engagements']},  # less than 65,535\n",
    "         Tweet_permalink=lambda df_: pd.Series('https://twitter.com/__mharrison__/status/', dtype='category', \n",
    "                                               index=df_.index),\n",
    "        )\n",
    " .describe()\n",
    " #.memory_usage(deep=True) \n",
    " #.sum()  # was 3 megs\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Alternate Integer Conversion Exercise\n",
    "(Again, no mutation!)\n",
    "\n",
    "* Use `.select_dtypes` to filter all `int` columns from `df`\n",
    "* Use `.astype` with above to convert all columns to `uint8`\n",
    "* Use `.assign` with above to create new dataframe with updated integer columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Other Types\n",
    "Can apply similar logic to floats, and strings.\n",
    "\n",
    "Converting \"Tweet_text\" to a category doesn't make sense because it is high cardinality"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Uses MORE memory if tweet text is a category!\n",
    "(df\n",
    " .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    " .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    " .drop(columns=['permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    " .assign(impressions=df.impressions.astype('uint32'),\n",
    "         engagements=df.engagements.astype('uint16'),\n",
    "         **{c:lambda df_, c=c:df_[c].astype('uint8') for c in ['replies', 'hashtag_clicks', 'follows']},  # less than 255\n",
    "         **{c:lambda df_, c=c:df_[c].astype('uint16') for c in ['retweets', 'likes', 'user_profile_clicks', 'url_clicks', \n",
    "                                          'detail_expands', 'media_views', 'media_engagements']},  # less than 65,535\n",
    "         Tweet_permalink=lambda df_: pd.Series('https://twitter.com/__mharrison__/status/', dtype='category', \n",
    "                                               index=df_.index),\n",
    "         Tweet_text=lambda df_:df_.Tweet_text.astype('category')\n",
    "        )\n",
    " .memory_usage(deep=True) \n",
    " .sum()  # was 3 megs\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Other types Exercise\n",
    "* Use the `%%timeit` cell magic to see how long it takes to run `.str.lower()` on the original *Tweet permalink* column\n",
    "* Create a new dataframe, `df2`, with our current chain\n",
    "* Use the `%%timeit` cell magic to see how long it takes to run `.str.lower()` on the *df2.Tweet_permalink* column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(df\n",
    " .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    " .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    " .drop(columns=['permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    " .assign(impressions=df.impressions.astype('uint32'),\n",
    "         engagements=df.engagements.astype('uint16'),\n",
    "         **{c:lambda df_, c=c:df_[c].astype('uint8') for c in ['replies', 'hashtag_clicks', 'follows']},  # less than 255\n",
    "         **{c:lambda df_, c=c:df_[c].astype('uint16') for c in ['retweets', 'likes', 'user_profile_clicks', 'url_clicks', \n",
    "                                          'detail_expands', 'media_views', 'media_engagements']},  # less than 65,535\n",
    "         Tweet_permalink=lambda df_: pd.Series('https://twitter.com/__mharrison__/status/', dtype='category', \n",
    "                                               index=df_.index),\n",
    "        )\n",
    " .time\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Convert to Local Time (already in UTC)\n",
    "(df\n",
    " .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    " .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    " .drop(columns=['permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    " .astype({c:'uint8' for c in ['replies', 'hashtag_clicks', 'follows']})  # less than 255)\n",
    " .assign(impressions=df.impressions.astype('uint32'),\n",
    "         engagements=df.engagements.astype('uint16'),\n",
    "         #**{c:lambda df_, c=c:df_[c].astype('uint8') for c in ['replies', 'hashtag_clicks', 'follows']},  # less than 255\n",
    "         **{c:lambda df_, c=c:df_[c].astype('uint16') for c in ['retweets', 'likes', 'user_profile_clicks', 'url_clicks', \n",
    "                                          'detail_expands', 'media_views', 'media_engagements']},  # less than 65,535\n",
    "         Tweet_permalink=lambda df_: pd.Series('https://twitter.com/__mharrison__/status/', dtype='category', \n",
    "                                               index=df_.index),\n",
    "         time=lambda df_: df_.time.dt.tz_convert('America/Denver')\n",
    "        )\n",
    " .time\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dates Exercise\n",
    "* Create a series with the months of the *time* column\n",
    "* Convert the *time* column to UTC\n",
    "* Convert the *time* column to `America/New_York`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Chain\n",
    "\n",
    "Chaining is also called \"flow\" programming. Rather than making intermediate variables, just leverage the fact that most operations return a new object and work on that.\n",
    "\n",
    "The chain should read like a recipe of ordered steps.\n",
    "\n",
    "(BTW, this is actually what we did above.)\n",
    "\n",
    "<div class='alert alert-warning'>\n",
    "    Hint: Leverage <tt>.pipe</tt> if you can't find a way to chain 😉🐼💪\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert to a function\n",
    "def tweak_twitter(df):\n",
    "    return (df\n",
    "     .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    "     .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    "     .drop(columns=['permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    "     .assign(impressions=df.impressions.astype('uint32'),\n",
    "             engagements=df.engagements.astype('uint16'),\n",
    "             **{c:lambda df_, c=c:df_[c].astype('uint8') for c in ['replies', 'hashtag_clicks', 'follows']},  # less than 255\n",
    "             **{c:lambda df_, c=c:df_[c].astype('uint16') for c in ['retweets', 'likes', 'user_profile_clicks', 'url_clicks', \n",
    "                                              'detail_expands', 'media_views', 'media_engagements']},  # less than 65,535\n",
    "             Tweet_permalink=lambda df_: pd.Series('https://twitter.com/__mharrison__/status/', dtype='category', \n",
    "                                                   index=df_.index),\n",
    "             time=lambda df_: df_.time.dt.tz_convert('America/Denver')\n",
    "            )\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# I would want my notebook to start off like this:\n",
    "import glob\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "data = [pd.read_csv(f, parse_dates=['time'], na_values='-') for f in glob.glob('tweet_activity_metrics___mharrison___*')]\n",
    "df = pd.concat(data, ignore_index=True).sort_values('time')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def tweak_twitter(df):\n",
    "    return (df\n",
    "     .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    "     .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    "     .drop(columns=['permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    "     .assign(impressions=df.impressions.astype('uint32'),\n",
    "             engagements=df.engagements.astype('uint16'),\n",
    "             **{c:lambda df_, c=c:df_[c].astype('uint8') for c in ['replies', 'hashtag_clicks', 'follows']},  # less than 255\n",
    "             **{c:lambda df_, c=c:df_[c].astype('uint16') for c in ['retweets', 'likes', 'user_profile_clicks', 'url_clicks', \n",
    "                                              'detail_expands', 'media_views', 'media_engagements']},  # less than 65,535\n",
    "             Tweet_permalink=lambda df_: pd.Series('https://twitter.com/__mharrison__/status/', dtype='category', \n",
    "                                                   index=df_.index),\n",
    "             time=lambda df_: df_.time.dt.tz_convert('America/Denver')\n",
    "            )\n",
    "    )\n",
    "twit_df = tweak_twitter(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# compare with non-chain\n",
    "df1 = df.rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    "keep = [c for c in df1.columns if 'promoted' not in c]\n",
    "df2 = df1[keep]\n",
    "keep2 = [c for c in df2 if c not in ['permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone']]\n",
    "df3 = df2[keep2]\n",
    "imps = df3.impressions.astype('uint32')\n",
    "df3.impressions = imps\n",
    "eng = df3.engagements.astype('uint16')\n",
    "df3['engagements'] = eng\n",
    "df3['replies'] = df3.replies.astype('uint8')\n",
    "df3['hashtag_clicks'] = df3.hashtag_clicks.astype('uint8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# easy to debug\n",
    "#  - assign to var (renamed_df)\n",
    "#  - comment out\n",
    "#  - pipe to display\n",
    "\n",
    "from IPython.display import display\n",
    "\n",
    "def get_var(df, var_name):\n",
    "    globals()[var_name] = df\n",
    "    return df\n",
    "\n",
    "def tweak_twitter(df):\n",
    "    return (df\n",
    "     .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    "     .pipe(get_var, 'renamed_df')\n",
    "     .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    "     .drop(columns=['permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    "     .pipe(lambda df_:display(df_) or df_)\n",
    "     .assign(impressions=df.impressions.astype('uint32'),\n",
    "             engagements=df.engagements.astype('uint16'),\n",
    "             **{c:lambda df_, c=c:df_[c].astype('uint8') for c in ['replies', 'hashtag_clicks', 'follows']},  # less than 255\n",
    "             **{c:lambda df_, c=c:df_[c].astype('uint16') for c in ['retweets', 'likes', 'user_profile_clicks', 'url_clicks', \n",
    "                                            'detail_expands', 'media_views', 'media_engagements']},  # less than 65,535\n",
    "             Tweet_permalink=lambda df_: pd.Series('https://twitter.com/__mharrison__/status/', dtype='category', \n",
    "                                                   index=df_.index),\n",
    "            time=lambda df_: df_.time.dt.tz_convert('America/Denver')\n",
    "            )\n",
    "    )\n",
    "twit_df = tweak_twitter(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "renamed_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def tweak_twitter(df):\n",
    "    return (df\n",
    "     .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    "     .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    "     .drop(columns=['permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    "     .assign(impressions=df.impressions.astype('uint32'),\n",
    "             engagements=df.engagements.astype('uint16'),\n",
    "             **{c:lambda df_, c=c:df_[c].astype('uint8') for c in ['replies', 'hashtag_clicks', 'follows']},  # less than 255\n",
    "             **{c:lambda df_, c=c:df_[c].astype('uint16') for c in ['retweets', 'likes', 'user_profile_clicks', 'url_clicks', \n",
    "                                              'detail_expands', 'media_views', 'media_engagements']},  # less than 65,535\n",
    "             Tweet_permalink=lambda df_: pd.Series('https://twitter.com/__mharrison__/status/', dtype='category', \n",
    "                                                   index=df_.index),\n",
    "             time=lambda df_: df_.time.dt.tz_convert('America/Denver')\n",
    "            )\n",
    "    )\n",
    "twit_df = tweak_twitter(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Chain Exercise\n",
    "* Use `.pipe` to print the shape of the dataframe after every step in the chain of the `tweak_twitter` function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Don't Mutate\n",
    "\n",
    "> \"you are missing the point, inplace rarely actually does something inplace, you are thinking that you are saving memory but you are not.\"\n",
    ">\n",
    "> **jreback** - Pandas core dev\n",
    "\n",
    "\n",
    "\n",
    "https://github.com/pandas-dev/pandas/issues/16529#issuecomment-676518136\n",
    "\n",
    "* In general, no performance benefits\n",
    "* Prohibits chaining\n",
    "* ``SettingWithCopyWarning`` fun"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Don't Apply (if you can)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def tweak_twitter(df):\n",
    "    return (df\n",
    "     .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    "     .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    "     .drop(columns=['permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    "     .assign(impressions=df.impressions.astype('uint32'),\n",
    "             engagements=df.engagements.astype('uint16'),\n",
    "             **{c:lambda df_, c=c:df_[c].astype('uint8') for c in ['replies', 'hashtag_clicks', 'follows']},  # less than 255\n",
    "             **{c:lambda df_, c=c:df_[c].astype('uint16') for c in ['retweets', 'likes', 'user_profile_clicks', 'url_clicks', \n",
    "                                              'detail_expands', 'media_views', 'media_engagements']},  # less than 65,535\n",
    "             Tweet_permalink=lambda df_: pd.Series('https://twitter.com/__mharrison__/status/', dtype='category', \n",
    "                                                   index=df_.index),\n",
    "             time=lambda df_: df_.time.dt.tz_convert('America/Denver')\n",
    "            )\n",
    "    )\n",
    "twit_df = tweak_twitter(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "twit_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def to_percent(val):\n",
    "    return val * 100\n",
    "twit_df.engagement_rate.apply(to_percent)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "# same result\n",
    "twit_df.engagement_rate * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%timeit\n",
    "# however ...\n",
    "twit_df.engagement_rate.apply(to_percent)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%timeit\n",
    "twit_df.engagement_rate * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 14X slower!\n",
    "1008 / 71"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# How would we check if text had unicode?\n",
    "'Hello \\U0001f600'.encode('ascii', errors='replace').decode('ascii')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'Hello \\U0001f600'.encode('utf8', errors='replace').decode('utf8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# story is a little different with text\n",
    "\n",
    "def is_unicode(val):\n",
    "    return val.encode('ascii', errors='replace').decode('ascii') != val"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%lsmagic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%timeit?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%timeit\n",
    "twit_df.Tweet_text.apply(is_unicode)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%timeit\n",
    "twit_df.Tweet_text.str.encode('ascii', errors='replace').str.decode('ascii') == twit_df.Tweet_text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%timeit\n",
    "twit_df.Tweet_text.str.startswith('@')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def startswith_at(txt):\n",
    "    return txt.startswith('@')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%timeit\n",
    "twit_df.Tweet_text.apply(startswith_at)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def tweak_twitter(df):\n",
    "    return (df\n",
    "     .rename(columns=lambda col_name: col_name.replace(' ', '_'))\n",
    "     .pipe(lambda df_: df_.drop(columns=[c for c in df_.columns if 'promoted' in c]))\n",
    "     .drop(columns=['permalink_clicks', 'app_opens', 'app_installs', 'email_tweet', 'dial_phone'])\n",
    "     .assign(impressions=df.impressions.astype('uint32'),\n",
    "             engagements=df.engagements.astype('uint16'),\n",
    "             **{c:lambda df_, c=c:df_[c].astype('uint8') for c in ['replies', 'hashtag_clicks', 'follows']},  # less than 255\n",
    "             **{c:lambda df_, c=c:df_[c].astype('uint16') for c in ['retweets', 'likes', 'user_profile_clicks', 'url_clicks', \n",
    "                                              'detail_expands', 'media_views', 'media_engagements']},  # less than 65,535\n",
    "             Tweet_permalink=lambda df_: pd.Series('https://twitter.com/__mharrison__/status/', dtype='category', \n",
    "                                                   index=df_.index),\n",
    "             time=lambda df_: df_.time.dt.tz_convert('America/Denver'),\n",
    "             is_reply=lambda df_: df_.Tweet_text.str.startswith('@'),\n",
    "             length=lambda df_:df_.Tweet_text.str.len(),\n",
    "             num_words=lambda df_:df_.Tweet_text.str.split().apply(len),\n",
    "             is_unicode=lambda df_:df_.Tweet_text.str.encode('ascii', errors='replace').str.decode('ascii') != df_.Tweet_text,\n",
    "             hour=lambda df_:df_.time.dt.hour,\n",
    "             dom=lambda df_:df_.time.dt.day,  #day of month\n",
    "             dow=lambda df_:df_.time.dt.dayofweek,  #day of week\n",
    "             at_tweet=lambda df_:df_.Tweet_text.str.contains('@'),\n",
    "             has_newlines=lambda df_:df_.Tweet_text.str.contains('\\n'),\n",
    "             num_lines=lambda df_:df_.Tweet_text.str.count('\\n'),\n",
    "             num_mentions=lambda df_:df_.Tweet_text.str.count('@'),\n",
    "             has_hashtag=lambda df_:df_.Tweet_text.str.count('#'),\n",
    "            )\n",
    "    )\n",
    "twit_df = tweak_twitter(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "twit_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Apply Exercise\n",
    "* Calculate engagement ratio by dividing *engagements* by *impressions*\n",
    "* Calculate engagement ratio 2 by dividing the sum of *replies*, *retweets*, *likes*, *user_profile_clicks*, and *detail_expands* by *impressions*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Master Aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(twit_df\n",
    " .groupby(twit_df.time.dt.year)\n",
    " .mean()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "twit_df.groupby(twit_df.time.dt.year).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "(twit_df\n",
    " .groupby(twit_df.time.dt.year)\n",
    " .impressions\n",
    " .mean()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%timeit\n",
    "(twit_df\n",
    " .groupby(twit_df.time.dt.year)\n",
    " .mean()\n",
    " [['impressions', 'replies']]  # index operation with a list inside \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%timeit\n",
    "(twit_df\n",
    " .groupby(twit_df.time.dt.year)\n",
    " [['impressions', 'replies']]  # index operation with a list inside \n",
    "  .mean()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "twit_df.Tweet_text.str."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "twit_df.time.dt.year.rename('year')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.options.display.float_format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(twit_df\n",
    " .groupby([twit_df.time.dt.year.rename('year'), twit_df.time.dt.month.rename('month')])\n",
    " [['impressions', 'replies']]\n",
    " .mean()\n",
    " #.round(2)\n",
    " .style\n",
    " .format({'replies': '{:.3f}', 'impressions': '{:e}'})\n",
    " \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(twit_df\n",
    " .groupby([twit_df.time.dt.year, twit_df.time.dt.month])\n",
    " [['impressions', 'replies']]\n",
    " #.mean()\n",
    " .median()\n",
    " .plot()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(twit_df\n",
    " #.groupby([twit_df.time.dt.year, twit_df.time.dt.month])\n",
    " .groupby(pd.Grouper(key='time', freq='2M'))\n",
    " [['impressions', 'replies']]\n",
    " #.mean()\n",
    " .median()\n",
    " .plot()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(twit_df\n",
    " #.groupby([twit_df.time.dt.year, twit_df.time.dt.month])\n",
    " .groupby(pd.Grouper(key='time', freq='2w'))\n",
    " [['impressions', 'replies']]\n",
    " .mean()\n",
    " .plot()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "(twit_df\n",
    " #.groupby([twit_df.time.dt.year, twit_df.time.dt.month])\n",
    " .groupby(pd.Grouper(key='time', freq='7d5h'))\n",
    " [['impressions', 'replies']]\n",
    " .mean()\n",
    " #.plot()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "(twit_df\n",
    " #.groupby([twit_df.time.dt.year, twit_df.time.dt.month])\n",
    " .groupby([pd.Grouper(key='time', freq='7d5h'), 'is_unicode'])\n",
    " [['impressions', 'replies']]\n",
    " .mean()\n",
    " #.plot()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# multiple aggregates\n",
    "def second_to_last(ser):\n",
    "    try:\n",
    "        return ser.iloc[-2]\n",
    "    except IndexError:\n",
    "        return 0\n",
    "\n",
    "(twit_df\n",
    " .groupby([pd.Grouper(key='time', freq='7d5h'), 'is_unicode'])\n",
    " [['impressions', 'replies']]\n",
    " .agg(['mean', 'median', second_to_last])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# multiple aggregates\n",
    "\n",
    "(twit_df\n",
    " .groupby([pd.Grouper(key='time', freq='7d5h'), 'is_unicode'])\n",
    " [['impressions', 'replies']]\n",
    " .agg(['mean', 'median', second_to_last])\n",
    " .plot()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# multiple aggregates\n",
    "\n",
    "(twit_df\n",
    " .groupby([pd.Grouper(key='time', freq='7d'), 'is_unicode'])\n",
    " [['impressions', 'replies']]\n",
    " .agg(['mean', 'median', second_to_last])\n",
    " .unstack()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# multiple aggregates\n",
    "\n",
    "(twit_df\n",
    " .groupby([pd.Grouper(key='time', freq='7d'), 'is_unicode'])\n",
    " [['impressions', 'replies']]\n",
    " .agg(['mean', 'median', second_to_last])\n",
    " .unstack()\n",
    " .impressions\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# multiple aggregates\n",
    "(twit_df\n",
    " .groupby([pd.Grouper(key='time', freq='7d'), 'is_unicode'])\n",
    " [['impressions', 'replies']]\n",
    " .agg(['mean', 'median', second_to_last])\n",
    " .unstack()\n",
    " .impressions\n",
    " ['mean']  # note have to use index syntax here\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# multiple aggregates\n",
    "(twit_df\n",
    " .groupby([pd.Grouper(key='time', freq='7d'), 'is_unicode'])\n",
    " [['impressions', 'replies']]\n",
    " .agg(['mean', 'median', second_to_last])\n",
    " .unstack()\n",
    " .impressions\n",
    " .mean  # note have to use index syntax here\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# multiple aggregates\n",
    "(twit_df\n",
    " .groupby([pd.Grouper(key='time', freq='7d'), 'is_unicode'])\n",
    " [['impressions', 'replies']]\n",
    " .agg(['mean', 'median', second_to_last])\n",
    " .unstack()\n",
    " .impressions\n",
    " ['mean']\n",
    " .plot()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# multiple aggregates\n",
    "# dealing with missing values\n",
    "(twit_df\n",
    " .groupby([pd.Grouper(key='time', freq='7d'), 'is_unicode'])\n",
    " [['impressions', 'replies']]\n",
    " .agg(['mean', 'median', second_to_last])\n",
    " .unstack()\n",
    " .impressions\n",
    " ['mean']\n",
    " #.fillna(0)\n",
    " #.interpolate()\n",
    " #.bfill()\n",
    " #.dropna()\n",
    " .loc['2021/07':'2021/08']\n",
    " #.plot()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# multiple aggregates\n",
    "(twit_df\n",
    " .groupby([pd.Grouper(key='time', freq='3d'), 'is_unicode'])\n",
    " [['impressions', 'replies']]\n",
    " .agg(['mean', 'median', second_to_last])\n",
    " .unstack()\n",
    " .impressions\n",
    " ['mean']\n",
    " .interpolate()\n",
    " .rolling(7)\n",
    " .mean()\n",
    " .plot()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# named aggregation\n",
    "\n",
    "(twit_df\n",
    " .groupby([pd.Grouper(key='time', freq='M'), 'is_unicode'])\n",
    " .agg(total_views=('impressions', 'sum'),\n",
    "     mean_views=('impressions', 'mean'),\n",
    "     profile_clicks=('user_profile_clicks', lambda ser: ser.sum()))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# named aggregation - fails with resample\n",
    "\n",
    "(twit_df\n",
    " #.groupby([pd.Grouper(key='time', freq='M'), 'is_unicode'])\n",
    " .set_index('time')\n",
    " .resample('M')\n",
    " .agg(total_views=('impressions', 'sum'),\n",
    "     mean_views=('impressions', 'mean'),\n",
    "     profile_clicks=('user_profile_clicks', lambda ser: ser.sum()))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 0,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# named aggregation\n",
    "\n",
    "(twit_df\n",
    " .groupby([pd.Grouper(key='time', freq='M'), 'is_unicode'])\n",
    " .agg(total_views=('impressions', 'sum'),\n",
    "     mean_views=('impressions', 'mean'),\n",
    "     profile_clicks=('user_profile_clicks', lambda ser: ser.sum()))\n",
    " .unstack()\n",
    " .profile_clicks\n",
    " .plot()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aggregation Exercise\n",
    "* What were the total impressions for each year?\n",
    "* What were the total impressions for each month?\n",
    "* Plot the previous\n",
    "* What were the total impressions for unicode and non-unicode tweets for each month?\n",
    "* Plot the previous\n",
    "* What were the total impressions for reply and non-reply tweets for each month?\n",
    "* Plot the previous"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary\n",
    "\n",
    "* Correct types save space and enable convenient math, string, and date functionality\n",
    "* Chaining operations will:\n",
    "   * Make code readable\n",
    "   * Remove bugs\n",
    "   * Easier to debug\n",
    "* Don't mutate (there's no point). Embrace chaining.\n",
    "* ``.apply`` is slow for math\n",
    "* Aggregations are powerful. Play with them until they make sense\n",
    "\n",
    "Connect with me on LinkedIn or Twitter (@\\_\\_mharrison\\_\\_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "jupytext": {
   "encoding": "# -*- coding: utf-8 -*-",
   "formats": "ipynb,py:light"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}