191002_c_groupby.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Groupby\n",
    "* often we want to split up and work with data based on groups\n",
    "* pandas allows us to iterate through rows and columns in a dataframe, but this is sort of slow\n",
    "* pandas also supports `groupby()` through a split-apply-combine pattern"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Splitting\n",
    "* Let's get motivated first"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv('datasets/census.csv')\n",
    "df = df[df['SUMLEV']==50]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "%%timeit -n 3\n",
    "for state in df['STNAME'].unique():\n",
    "    avg = np.average(df.where(df['STNAME']==state).dropna()['CENSUS2010POP'])\n",
    "    print('Counties in state ' + state + ' have an average population of ' + str(avg))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "%%timeit -n 3\n",
    "for group, frame in df.groupby('STNAME'):\n",
    "    avg = np.average(frame['CENSUS2010POP'])\n",
    "    print('Counties in state ' + group + ' have an average population of ' + str(avg))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "* ok, so groupby rocks\n",
    "* usually you'll group by data in a column, but you can also provide a function to groupby and use that to segment your data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "df = df.set_index('STNAME')\n",
    "\n",
    "def set_batch_number(item):\n",
    "    if item[0]<'M':\n",
    "        return 0\n",
    "    if item[0]<'Q':\n",
    "        return 1\n",
    "    return 2\n",
    "\n",
    "for group, frame in df.groupby(set_batch_number):\n",
    "    print('There are ' + str(len(frame)) + ' records in group ' + str(group) + ' for processing.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "* we can also group by multiple columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "#airbnb data\n",
    "df=pd.read_csv(\"datasets/listings.csv\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "df=df.set_index([\"cancellation_policy\",\"review_scores_value\"])\n",
    "\n",
    "# When we have a multiindex we need to pass in the levels we are interested in grouping by\n",
    "for group, frame in df.groupby(level=(0,1)):\n",
    "    print(group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# We can also do this with a function, which is passed a tuple of the index\n",
    "def grouping_fun(item):\n",
    "    if item[1] == 10.0:\n",
    "        return (item[0],\"10.0\")\n",
    "    else:\n",
    "        return (item[0],\"not 10.0\")\n",
    "\n",
    "for group, frame in df.groupby(by=grouping_fun):\n",
    "    print(group)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Applying\n",
    "* so far we have just looked at splitting up data\n",
    "* we have three broad kinds of applying for data: aggregation, transformation, and filtering."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# We should just be able to aggregate by calling .agg\n",
    "df=df.reset_index()\n",
    "df.groupby(\"cancellation_policy\").agg({\"review_scores_value\":np.average})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# That didn't seem to work at all, NaN!\n",
    "# The issue is actually in the function that we sent to aggregate. np.average does not ignore nans! \n",
    "df.groupby(\"cancellation_policy\").agg({\"review_scores_value\":np.nanmean})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# We can just extend this dictionary to aggregate by multiple functions or multiple columns.\n",
    "df.groupby(\"cancellation_policy\").agg({\"review_scores_value\":(np.nanmean,np.nanstd),\n",
    "                                      \"reviews_per_month\":np.nanmean})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Transformation\n",
    "* Transformation broadcasts the function you supply over the grouped dataframe, returning a new dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "cols=['cancellation_policy','review_scores_value']\n",
    "transform_df=df[cols].groupby('cancellation_policy').transform(np.nanmean)\n",
    "transform_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# lets clean this up\n",
    "transform_df.rename({'review_scores_value':'mean_review_scores'}, axis='columns', inplace=True)\n",
    "# and merge back to our original dataframe\n",
    "df=df.merge(transform_df, left_index=True, right_index=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# Now we can create the difference between a given row and it's group (the cancellation policy) means.\n",
    "df['mean_diff']=np.absolute(df['review_scores_value']-df['mean_review_scores'])\n",
    "df['mean_diff'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Filtering\n",
    "* You can also use `filter()` to drop data, sort of like `where()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "df.groupby('cancellation_policy').filter(lambda x: np.nanmean(x['review_scores_value'])>9.2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Applying\n",
    "* this is 95% of what I actually do with groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "df=pd.read_csv(\"datasets/listings.csv\")\n",
    "df=df[['cancellation_policy','review_scores_value']]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "def calc_mean_review_scores(group):\n",
    "    # we can treat this as the complete dataframe\n",
    "    avg=np.nanmean(group[\"review_scores_value\"])\n",
    "    # now broadcast our formula and create a new column\n",
    "    group[\"review_scores_mean\"]=np.abs(avg-group[\"review_scores_value\"])\n",
    "    return group\n",
    "\n",
    "# Now just apply this to the groups\n",
    "df.groupby('cancellation_policy').apply(calc_mean_review_scores).head()"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}