lectures/10 Big data/10a Fitting in memory.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f01c4105",
   "metadata": {},
   "source": [
    "# Module 10. Big data\n",
    "# Fitting data in memory\n",
    "## Lecture objectives\n",
    "\n",
    "1. Introduce some of the strategies to handle large data sets\n",
    "2. Discuss how data types affect memory requirements\n",
    "3. Demonstrate how to sample to reduce the size of a dataset\n",
    "\n",
    "Big data is often characterized by the 3 \"Vs\": velocity, volume, and variety.\n",
    "\n",
    "We've talked implicitly about the **variety** throughout the course — how to clean, join, and otherwise wrangle datasets. Here, we'll focus on issues of **volume**. In short, what can you do when a dataset doesn't fit into memory, or your code takes too long to run.\n",
    "\n",
    "Here, I'll set out a hierarchy of approaches to dealing with large datasets. Start with the simplest, and move on to the next group of solutions as needed.\n",
    "\n",
    "* Testing and scaling up\n",
    "* Running overnight\n",
    "* Finding a secondary computer\n",
    "* Profiling\n",
    "* Being economical with data types\n",
    "* Sampling\n",
    "\n",
    "These strategies are all about being efficient and thoughtful with your analysis. They don't need any special procedures or coding, and some of the strategies are self explanatory.\n",
    "\n",
    "*Testing and scaling up*: get your code working on a tiny subset of the data, before running it on your full dataset. This seems obvious, but will save you a lot of time. For example: run your code on Ventura County data before doing Los Angeles County, or take a small city in the county.\n",
    "\n",
    "*Running overnight*: Again, this seems obvious. But remember: your time is much more valuable than the computer's. You could spend the time to make your code more efficient, or you could just leave your computer running in the kitchen or in a closet while you sleep / go to the beach / do something fun.\n",
    "\n",
    "*Finding a secondary computer*: Maybe you have an old laptop with a cracked screen, an erratic keyboard, and a battery that holds about 15 minutes of charge? This is the perfect machine to do some web scraping or similar tasks in the background. Leave it running for a few days/weeks/months and plug in an external hard drive. \n",
    "\n",
    "*Profiling*: identify bottlenecks in your code. We'll discuss this in the next lecture.\n",
    "\n",
    "In this lecture, we'll focus on fitting large datasets in memory, through judicious use of *data types* and *sampling*."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0bb4a099",
   "metadata": {},
   "source": [
    "### Being economical with data types\n",
    "\n",
    "Data types refer to how an object (integer, string, etc.) is stored by Python. `pandas` generally uses the underlying `numpy` data types, [which are described here](https://numpy.org/doc/stable/user/basics.types.html).\n",
    "\n",
    "Why should you care about data types? Often, it doesn't matter - if you have integers, storing them as a float will usually work just as well. But we've seen several instances where we need to convert the data type.\n",
    "\n",
    "In particular, numbers can be stored as strings, but we need to convert them to integers or floats to do arithmetic.\n",
    "\n",
    "And census geoidentifiers (e.g. tract) can be stored as numbers, but that loses the leading zero, so they are hard to join if one table has the geoid as a string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84f4e78a",
   "metadata": {},
   "outputs": [],
   "source": [
    "x = '1'\n",
    "y = '2'\n",
    "print(x*y)  # fails because they are strings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "784d7657",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(int(x)*int(y))   # converting to integer works"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f898338",
   "metadata": {},
   "source": [
    "The other major time when you have to worry about data types is to increase the efficiency of your data storage. In general, the order from most- to least-efficient is:\n",
    "* boolean (True or False)\n",
    "* integer\n",
    "* float\n",
    "* string / object\n",
    "\n",
    "The relevant consideration is how many *bits* they consume. For example, an integer can be represented in various ways:\n",
    "* `int64` is the default, which can represent integers from  from -9223372036854775808 to +9223372036854775807 \n",
    "* `int32` consumes half as much space, but can only represent -2147483648 to +2147483647\n",
    "* `int16` and `int8` are also possibilities, if you are sure that you won't have large numbers\n",
    "* You can also have unsigned integers (positive numbers only)\n",
    "\n",
    "Similar logic applies to floats: `float64`, `float32`, etc. Here, it's the precision rather than the range that's affected.\n",
    "\n",
    "We can use `info()` to get the memory consumption of a dataframe. Let's compare the different data types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b96dcc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# create a dataframe with one column of ones\n",
    "df = pd.DataFrame(np.ones(50000), columns=['ones'])\n",
    "\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a80015d6-008f-4b02-8ad4-4f4b023ffaba",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "    <strong>Exercise:</strong> Suppose you change the datatype to <strong>int16</strong> or <strong>int8</strong>. How would that affect memory usage?\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9bfff38",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['ones'] = df.ones.astype('int8')\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a77dad4",
   "metadata": {},
   "source": [
    "We reduce the size of our dataframe from 391k to 49k – an eightfold reduction (which makes sense as we are going from 64 bit to 8 bit).\n",
    "\n",
    "Here's a less trivial example, using the voting dataset that we used before."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40f1b761",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('../data/c037_g20_sov_data_by_g20_srprec.csv')\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ed87f97-c4c2-41c2-8725-63680f6e8f60",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "    <strong>Exercise:</strong> What's the smallest (most efficient) data type that you could use for these columns?\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07c22277",
   "metadata": {},
   "source": [
    "Let's look at the maximum number of votes per precinct, to see what data type might be appropriate.\n",
    "\n",
    "Here, we use `.max()` to get the maximum value within each column (after dropping the non-numeric `srprec` column), and then `.max()` again to get the maximum across columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1cb92804",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns=['srprec']).max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97b7265b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns=['srprec']).max().max()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31d94230",
   "metadata": {},
   "source": [
    "That won't fit into `int16` (-32767 to +32767), but we could use `int32`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37363e45",
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in df.columns:\n",
    "    if df[col].dtype != 'object': # skip the string column (srprec)\n",
    "        df[col] = df[col].astype('int32')\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "012e649e",
   "metadata": {},
   "source": [
    "So we halve the memory usage. Let's check to see whether our data is unaffected."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ccbebec",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns=['srprec']).max().max()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afbaa2b5",
   "metadata": {},
   "source": [
    "That wouldn't be the case if we converted to (say) `int16`. Beware!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "782a4dbe",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df['SENDEM01'].max())\n",
    "print(df['SENDEM01'].astype('int16').max())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0dde49d1",
   "metadata": {},
   "source": [
    "Note that `pandas` can skip columns, skip rows, and force particular data types when reading in a `csv` file. Check out the options."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3af61b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_csv?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8985ced",
   "metadata": {},
   "source": [
    "For example, we could read only the Proposition 14 (yes/no) and precinct columns, and read the numeric columns as `int32`.\n",
    "\n",
    "The `usecols` argument gives us the subset of columns. The `dtype` argument is a dictionary of data types.\n",
    "\n",
    "Note that we don't specify a type for `PR_14_Y`, so it gets read in as `int64`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55421b77",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('../data/c037_g20_sov_data_by_g20_srprec.csv', \n",
    "                 usecols=['srprec', 'PR_14_N', 'PR_14_Y'],\n",
    "                 dtype={'srprec':'str', 'PR_14_N':'int32'})\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6a573d5",
   "metadata": {},
   "source": [
    "### Sampling\n",
    "Sampling is often useful because there are diminishing returns (in terms of predictive power) to the size of your dataset. In statistical terms, remember that your standard errors go down by $1/\\sqrt{N}$, not by $1/N$.\n",
    "\n",
    "So if you have a million observations, you *might* get similar results through using a sample of half that size. This is likely to be the case for statistical models (e.g. logistic regression). It might not hold for machine learning models which are more flexible in the way that they consider interactions.\n",
    "\n",
    "Let's recreate the ADU prediction model that we saw in the classification notebook a couple of weeks ago."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3cefb6a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# all this code is identical to what we had before\n",
    "\n",
    "# read in and join parcels and permits\n",
    "permits = pd.read_csv('../data/ADUs/ADU_permits.csv') \n",
    "parcels = pd.read_csv('../data/ADUs/parcels.csv')\n",
    "permits.dropna(subset=['Assessor Book', 'Assessor Page','Assessor Parcel'], inplace=True)\n",
    "permits = permits[permits['Assessor Parcel']!='***']\n",
    "permits['APN'] = (permits['Assessor Book'].astype(int).astype(str).str.zfill(4) + '-' \n",
    "                   + permits['Assessor Page'].astype(int).astype(str).str.zfill(3) + '-'\n",
    "                   + permits['Assessor Parcel'].astype(int).astype(str).str.zfill(3))\n",
    "permits = permits.groupby('APN').first()\n",
    "parcels = parcels.groupby('APN').first()\n",
    "joinedDf = parcels.join(permits, how='left')\n",
    "\n",
    "# add dependent variable\n",
    "joinedDf['hasADU'] = joinedDf['# of Accessory Dwelling Units'].apply(lambda x: 0 if pd.isnull(x) else 1)\n",
    "\n",
    "# split the data and fit the model\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "from sklearn.metrics import ConfusionMatrixDisplay\n",
    "\n",
    "xvars =  ['YearBuilt1', 'Units1', 'Bedrooms1', 'Bathrooms1', 'SQFTmain1', 'Roll_LandValue', \n",
    "         'Roll_ImpValue','CENTER_LAT', 'CENTER_LON']\n",
    "yvar = 'hasADU'\n",
    "df_to_fit = joinedDf[xvars+[yvar]].dropna()\n",
    "X_train, X_test, y_train, y_test = train_test_split(df_to_fit[xvars], df_to_fit[yvar], test_size = 0.25, random_state = 1)\n",
    "\n",
    "rf = RandomForestClassifier(n_estimators = 10, random_state = 1)  # 10 trees to save time\n",
    "rf.fit(X_train, y_train)\n",
    "y_pred = rf.predict(X_test)\n",
    "ConfusionMatrixDisplay.from_predictions(y_test, y_pred)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac681cdd",
   "metadata": {},
   "source": [
    "What if we just estimate this model on half the data? We can use the `sample` function in `pandas`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "edd9ad7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_to_fit = df_to_fit.sample(frac=0.5) # take 50% sample\n",
    "    \n",
    "# this code is identical\n",
    "X_train, X_test, y_train, y_test = train_test_split(df_to_fit[xvars], df_to_fit[yvar], test_size = 0.25, random_state = 1)\n",
    "\n",
    "rf = RandomForestClassifier(n_estimators = 10, random_state = 1)  # 10 estimators to save time\n",
    "rf.fit(X_train, y_train)\n",
    "y_pred = rf.predict(X_test)\n",
    "ConfusionMatrixDisplay.from_predictions(y_test, y_pred)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fdf13b9e",
   "metadata": {},
   "source": [
    "This isn't so bad. (Remember all of the entries in the confusion matrix should have half the observations, because we have half the sample size.)\n",
    "\n",
    "However, a better way forward here might be to:\n",
    "1. Estimate the model on a sample of the data\n",
    "2. Look at the feature importances\n",
    "3. Run the model on the full data, but excluding some of the variables that don't increase predictive performance (i.e., low importance)\n",
    "\n",
    "I'll leave that for you as an exercise."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cac96c47",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<h3>Key Takeaways</h3>\n",
    "<ul>\n",
    "  <li>Start simple! Most \"big\" datasets can be handled on any laptop through judicious use of datatypes, and working on a chunk of your data at one time.</li>\n",
    "    <li>You will rarely need to do more than this, but the UCLA Hoffman2 cluster and cloud service providers are there if you need them.</li>\n",
    "</ul>\n",
    "</div>"
   ]
  }
 ],
 "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}