pandas_performance_2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Performance considerations for Pandas - Part II\n",
    "Sometimes there is more than one way to do something in Pandas. Some investigation and care in implementation will benefit in the long term.\n",
    "\n",
    "The demonstration below uses the weather data from noaa.gov.\n",
    "ftp://ftp.ncdc.noaa.gov/pub/data/ghcn/daily/\n",
    "\n",
    "The country name to two letter country code is given in the ghcnd-countries.txt. The weather station details are in ghcnd-stations.txt. The station name starts with the country name prefix. The goal of this exercise is to enhance the ghcnd-stations.txt table to have the country name where the station is located. There are about 114k station, so the table is not very big. It takes about 7 MB in memory."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First read and examine data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of countries :  219\n",
      "Number of stations :  113951\n",
      "Size of stations dataframe in memory :  8.2  MB\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "countries_df = pd.read_fwf(\"ghcnd-countries.txt\",header=None, names=['code','country']) \n",
    "stations_df = pd.read_fwf('ghcnd-stations.txt', header=None, \n",
    "                names=['station', 'latitude','longitude','elevation','state','name','gsn_flag',\n",
    "                      'hcn_crn_flag','wmo_id'])\n",
    "print(\"Number of countries : \", len(countries_df))\n",
    "print(\"Number of stations : \", len(stations_df))\n",
    "print(\"Size of stations dataframe in memory : \", round(stations_df.memory_usage().sum()*1e-6,2), \" MB\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>station</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>elevation</th>\n",
       "      <th>state</th>\n",
       "      <th>name</th>\n",
       "      <th>gsn_flag</th>\n",
       "      <th>hcn_crn_flag</th>\n",
       "      <th>wmo_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ACW00011604</td>\n",
       "      <td>17.1167</td>\n",
       "      <td>-61.7833</td>\n",
       "      <td>10.1</td>\n",
       "      <td>ST JOHNS COOLIDGE FLD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ACW00011647</td>\n",
       "      <td>17.1333</td>\n",
       "      <td>-61.7833</td>\n",
       "      <td>19.2</td>\n",
       "      <td>ST JOHNS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AE000041196</td>\n",
       "      <td>25.3330</td>\n",
       "      <td>55.5170</td>\n",
       "      <td>34.0</td>\n",
       "      <td>SHARJAH INTER. AIRP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GSN</td>\n",
       "      <td>41196.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AEM00041194</td>\n",
       "      <td>25.2550</td>\n",
       "      <td>55.3640</td>\n",
       "      <td>10.4</td>\n",
       "      <td>DUBAI INTL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41194.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AEM00041217</td>\n",
       "      <td>24.4330</td>\n",
       "      <td>54.6510</td>\n",
       "      <td>26.8</td>\n",
       "      <td>ABU DHABI INTL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41217.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       station  latitude  longitude  elevation                  state name  \\\n",
       "0  ACW00011604   17.1167   -61.7833       10.1  ST JOHNS COOLIDGE FLD  NaN   \n",
       "1  ACW00011647   17.1333   -61.7833       19.2               ST JOHNS  NaN   \n",
       "2  AE000041196   25.3330    55.5170       34.0    SHARJAH INTER. AIRP  NaN   \n",
       "3  AEM00041194   25.2550    55.3640       10.4             DUBAI INTL  NaN   \n",
       "4  AEM00041217   24.4330    54.6510       26.8         ABU DHABI INTL  NaN   \n",
       "\n",
       "  gsn_flag  hcn_crn_flag  wmo_id  \n",
       "0      NaN           NaN     NaN  \n",
       "1      NaN           NaN     NaN  \n",
       "2      GSN       41196.0     NaN  \n",
       "3      NaN       41194.0     NaN  \n",
       "4      NaN       41217.0     NaN  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stations_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First create a dictionary of prefix to country name mapping. This is a list of about 200 countries and is very quick. This dictionary is used in subsequent attempts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 0 ns\n"
     ]
    }
   ],
   "source": [
    "%time\n",
    "countries_map = pd.Series(countries_df['country'].values, index=countries_df['code'].values).to_dict()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Method 1: Iterate through every row\n",
    "Iterate through every row, and determine the country name based on the prefix. Note that when using `iterrows()`, the `row` variable here is a copy of the dataframe, so need to use `.loc` to specifically set a value in the dataframe. This is probably the first thought for pandas beginners"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 8min 48s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "stations_df['country_1'] = \"\"\n",
    "for i, row in stations_df.iterrows():\n",
    "    prefix = row['station'][0:2]\n",
    "    stations_df.loc[i,'country_1'] = countries_map[prefix]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Method 2: Iterate through every row, create a temporary Series\n",
    "This is similar to Method 1, but creates a temporary Series to hold the column. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 8min 26s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "country_column = pd.Series()\n",
    "for i, row in stations_df.iterrows():\n",
    "    prefix = row['station'][0:2]\n",
    "    country_column.at[i]= countries_map[prefix]\n",
    "stations_df['country_2'] = country_column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Method 3: Iterate through every row, create a temporary Series of known length\n",
    "This is similar to Method 2, but the Series is initalized to be the size of stations_df. The preformance increases dramatically from Method 2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 14.5 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "country_column = pd.Series(index=stations_df.index,dtype=str)\n",
    "for i, row in stations_df.iterrows():\n",
    "    prefix = row['station'][0:2]\n",
    "    country_column.at[i]= countries_map[prefix]\n",
    "stations_df['country_3'] = country_column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Method 4: Iterate through every row, create a temporary list\n",
    "This is similar to Method 2, but creates a temporary list to hold the column. Note that the performance is similar to Method 3, but did not need the size of the list to be pre-determined."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 13.6 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "country_list = []\n",
    "for i, row in stations_df.iterrows():\n",
    "    prefix = row['station'][0:2]\n",
    "    country_list.append(countries_map[prefix])\n",
    "stations_df['country_4'] = country_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Method 5: Use dictionary and map\n",
    "Get a list of stations from each country, build a dictionary of station to country mapping. Use this dictionary to create a new column of country names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 8.53 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "stations_map1 = {}\n",
    "for prefix, country in countries_map.items():\n",
    "    sub_stations_list = stations_df[stations_df['station'].str.startswith(prefix)]['station'].values.tolist()\n",
    "    stations_map1.update({st : country for st in sub_stations_list })\n",
    "stations_df['country_5'] = stations_df['station'].map(stations_map1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Method 6: Apply with custom function\n",
    "Use the pandas `apply` method to iterate over every row to get the country name. Note that the logic is very similar to Method 1, using iterrows, but is much faster than that one. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 1.61 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "def get_country_name(row):    \n",
    "    prefix = row['station'][0:2]\n",
    "    return countries_map[prefix]\n",
    "\n",
    "stations_df['country_6'] = stations_df.apply(get_country_name, axis='columns')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Method 7: Apply and Map\n",
    "1. First create a Series with just the prefixes\n",
    "stations_df[0].apply(lambda x : x[0:2])\n",
    "2. Then use map to get the country names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 45.5 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "stations_df['country_7'] = stations_df['station'].apply(lambda x : x[0:2]).map(countries_map)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>station</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>elevation</th>\n",
       "      <th>state</th>\n",
       "      <th>name</th>\n",
       "      <th>gsn_flag</th>\n",
       "      <th>hcn_crn_flag</th>\n",
       "      <th>wmo_id</th>\n",
       "      <th>country_1</th>\n",
       "      <th>country_2</th>\n",
       "      <th>country_3</th>\n",
       "      <th>country_4</th>\n",
       "      <th>country_5</th>\n",
       "      <th>country_6</th>\n",
       "      <th>country_7</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ACW00011604</td>\n",
       "      <td>17.1167</td>\n",
       "      <td>-61.7833</td>\n",
       "      <td>10.1</td>\n",
       "      <td>ST JOHNS COOLIDGE FLD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ACW00011647</td>\n",
       "      <td>17.1333</td>\n",
       "      <td>-61.7833</td>\n",
       "      <td>19.2</td>\n",
       "      <td>ST JOHNS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AE000041196</td>\n",
       "      <td>25.3330</td>\n",
       "      <td>55.5170</td>\n",
       "      <td>34.0</td>\n",
       "      <td>SHARJAH INTER. AIRP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GSN</td>\n",
       "      <td>41196.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AEM00041194</td>\n",
       "      <td>25.2550</td>\n",
       "      <td>55.3640</td>\n",
       "      <td>10.4</td>\n",
       "      <td>DUBAI INTL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41194.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AEM00041217</td>\n",
       "      <td>24.4330</td>\n",
       "      <td>54.6510</td>\n",
       "      <td>26.8</td>\n",
       "      <td>ABU DHABI INTL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41217.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       station  latitude  longitude  elevation                  state name  \\\n",
       "0  ACW00011604   17.1167   -61.7833       10.1  ST JOHNS COOLIDGE FLD  NaN   \n",
       "1  ACW00011647   17.1333   -61.7833       19.2               ST JOHNS  NaN   \n",
       "2  AE000041196   25.3330    55.5170       34.0    SHARJAH INTER. AIRP  NaN   \n",
       "3  AEM00041194   25.2550    55.3640       10.4             DUBAI INTL  NaN   \n",
       "4  AEM00041217   24.4330    54.6510       26.8         ABU DHABI INTL  NaN   \n",
       "\n",
       "  gsn_flag  hcn_crn_flag  wmo_id             country_1             country_2  \\\n",
       "0      NaN           NaN     NaN   Antigua and Barbuda   Antigua and Barbuda   \n",
       "1      NaN           NaN     NaN   Antigua and Barbuda   Antigua and Barbuda   \n",
       "2      GSN       41196.0     NaN  United Arab Emirates  United Arab Emirates   \n",
       "3      NaN       41194.0     NaN  United Arab Emirates  United Arab Emirates   \n",
       "4      NaN       41217.0     NaN  United Arab Emirates  United Arab Emirates   \n",
       "\n",
       "              country_3             country_4             country_5  \\\n",
       "0   Antigua and Barbuda   Antigua and Barbuda   Antigua and Barbuda   \n",
       "1   Antigua and Barbuda   Antigua and Barbuda   Antigua and Barbuda   \n",
       "2  United Arab Emirates  United Arab Emirates  United Arab Emirates   \n",
       "3  United Arab Emirates  United Arab Emirates  United Arab Emirates   \n",
       "4  United Arab Emirates  United Arab Emirates  United Arab Emirates   \n",
       "\n",
       "              country_6             country_7  \n",
       "0   Antigua and Barbuda   Antigua and Barbuda  \n",
       "1   Antigua and Barbuda   Antigua and Barbuda  \n",
       "2  United Arab Emirates  United Arab Emirates  \n",
       "3  United Arab Emirates  United Arab Emirates  \n",
       "4  United Arab Emirates  United Arab Emirates  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stations_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conclusion\n",
    "The methods are roughly arraged from worst performance to best in terms of runtime. Note that memory is not a consideration here. As far as possible, use the DataFrame columns directly to get best performance. Avoid using `.iterrows()`. Try different methods to see which one is best. In this case, we went from about 7 minues to 46 ms - a 9130x improvement!!! Method 7 is **more than Nine Thousand times faster** !!!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DAILY GLOBAL HISTORICAL CLIMATOLOGY NETWORK (GHCN-DAILY) Version 3.26\n",
    "\n",
    "Data has been downloaded from ftp://ftp.ncdc.noaa.gov/pub/data/ghcn/daily/\n",
    "\n",
    "Menne, M.J., I. Durre, B. Korzeniewski, S. McNeal, K. Thomas, X. Yin, S. Anthony, R. Ray, R.S. Vose, B.E.Gleason, and T.G. Houston, 2012: Global Historical Climatology Network - Daily (GHCN-Daily), Version 3.26. NOAA National Climatic Data Center. http://doi.org/10.7289/V5D21VHZ May 27 2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:PythonData]",
   "language": "python",
   "name": "conda-env-PythonData-py"
  },
  "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}