pandas-Ben.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing pandas\n",
    "\n",
    "### Getting started and checking your pandas setup\n",
    "\n",
    "Difficulty: *easy* \n",
    "\n",
    "**1.** Import pandas under the alias `pd`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**2.** Print the version of pandas that has been imported."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.4.2\n"
     ]
    }
   ],
   "source": [
    "print(pd.__version__)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**3.** Print out all the *version* information of the libraries that are required by the pandas library."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\willi\\anaconda3\\lib\\site-packages\\_distutils_hack\\__init__.py:30: UserWarning: Setuptools is replacing distutils.\n",
      "  warnings.warn(\"Setuptools is replacing distutils.\")\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "INSTALLED VERSIONS\n",
      "------------------\n",
      "commit           : 4bfe3d07b4858144c219b9346329027024102ab6\n",
      "python           : 3.9.12.final.0\n",
      "python-bits      : 64\n",
      "OS               : Windows\n",
      "OS-release       : 10\n",
      "Version          : 10.0.22000\n",
      "machine          : AMD64\n",
      "processor        : Intel64 Family 6 Model 140 Stepping 1, GenuineIntel\n",
      "byteorder        : little\n",
      "LC_ALL           : None\n",
      "LANG             : None\n",
      "LOCALE           : English_United States.1252\n",
      "\n",
      "pandas           : 1.4.2\n",
      "numpy            : 1.21.5\n",
      "pytz             : 2021.3\n",
      "dateutil         : 2.8.2\n",
      "pip              : 21.2.4\n",
      "setuptools       : 61.2.0\n",
      "Cython           : 0.29.28\n",
      "pytest           : 7.1.1\n",
      "hypothesis       : None\n",
      "sphinx           : 4.4.0\n",
      "blosc            : None\n",
      "feather          : None\n",
      "xlsxwriter       : 3.0.3\n",
      "lxml.etree       : 4.8.0\n",
      "html5lib         : None\n",
      "pymysql          : None\n",
      "psycopg2         : None\n",
      "jinja2           : 2.11.3\n",
      "IPython          : 8.2.0\n",
      "pandas_datareader: None\n",
      "bs4              : 4.11.1\n",
      "bottleneck       : 1.3.4\n",
      "brotli           : \n",
      "fastparquet      : None\n",
      "fsspec           : 2022.02.0\n",
      "gcsfs            : None\n",
      "markupsafe       : 2.0.1\n",
      "matplotlib       : 3.5.1\n",
      "numba            : 0.55.1\n",
      "numexpr          : 2.8.1\n",
      "odfpy            : None\n",
      "openpyxl         : 3.0.9\n",
      "pandas_gbq       : None\n",
      "pyarrow          : 8.0.0\n",
      "pyreadstat       : None\n",
      "pyxlsb           : None\n",
      "s3fs             : None\n",
      "scipy            : 1.7.3\n",
      "snappy           : \n",
      "sqlalchemy       : 1.4.32\n",
      "tables           : 3.6.1\n",
      "tabulate         : 0.8.9\n",
      "xarray           : 0.20.1\n",
      "xlrd             : 2.0.1\n",
      "xlwt             : None\n",
      "zstandard        : None\n"
     ]
    }
   ],
   "source": [
    "pd.show_versions()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame basics\n",
    "\n",
    "### A few of the fundamental routines for selecting, sorting, adding and aggregating data in DataFrames\n",
    "\n",
    "Difficulty: *easy*\n",
    "\n",
    "Note: remember to import numpy using:\n",
    "```python\n",
    "import numpy as np\n",
    "```\n",
    "\n",
    "Consider the following Python dictionary `data` and Python list `labels`:\n",
    "\n",
    "``` python\n",
    "data = {'animal': ['cat', 'cat', 'snake', 'dog', 'dog', 'cat', 'snake', 'cat', 'dog', 'dog'],\n",
    "        'age': [2.5, 3, 0.5, np.nan, 5, 2, 4.5, np.nan, 7, 3],\n",
    "        'visits': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1],\n",
    "        'priority': ['yes', 'yes', 'no', 'yes', 'no', 'no', 'no', 'yes', 'no', 'no']}\n",
    "\n",
    "labels = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j']\n",
    "```\n",
    "(This is just some meaningless data I made up with the theme of animals and trips to a vet.)\n",
    "\n",
    "**4.** Create a DataFrame `df` from this dictionary `data` which has the index `labels`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "data = {'animal': ['cat', 'cat', 'snake', 'dog', 'dog', 'cat', 'snake', 'cat', 'dog', 'dog'],\n",
    "        'age': [2.5, 3, 0.5, np.nan, 5, 2, 4.5, np.nan, 7, 3],\n",
    "        'visits': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1],\n",
    "        'priority': ['yes', 'yes', 'no', 'yes', 'no', 'no', 'no', 'yes', 'no', 'no']}\n",
    "\n",
    "labels = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j']\n",
    "\n",
    "df = pd.DataFrame(data,labels)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**5.** Display a summary of the basic information about this DataFrame and its data (*hint: there is a single method that can be called on the DataFrame*)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>age</th>\n",
       "      <th>visits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>8.000000</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>3.437500</td>\n",
       "      <td>1.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2.007797</td>\n",
       "      <td>0.875595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.500000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2.375000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>4.625000</td>\n",
       "      <td>2.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>7.000000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            age     visits\n",
       "count  8.000000  10.000000\n",
       "mean   3.437500   1.900000\n",
       "std    2.007797   0.875595\n",
       "min    0.500000   1.000000\n",
       "25%    2.375000   1.000000\n",
       "50%    3.000000   2.000000\n",
       "75%    4.625000   2.750000\n",
       "max    7.000000   3.000000"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**6.** Return the first 3 rows of the DataFrame `df`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>animal</th>\n",
       "      <th>age</th>\n",
       "      <th>visits</th>\n",
       "      <th>priority</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>cat</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>cat</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>snake</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  animal  age  visits priority\n",
       "a    cat  2.5       1      yes\n",
       "b    cat  3.0       3      yes\n",
       "c  snake  0.5       2       no"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**7.** Select just the 'animal' and 'age' columns from the DataFrame `df`."
   ]
  },
  {
   "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>animal</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>cat</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>cat</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>snake</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>dog</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>dog</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f</th>\n",
       "      <td>cat</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>g</th>\n",
       "      <td>snake</td>\n",
       "      <td>4.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>h</th>\n",
       "      <td>cat</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>i</th>\n",
       "      <td>dog</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>j</th>\n",
       "      <td>dog</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  animal  age\n",
       "a    cat  2.5\n",
       "b    cat  3.0\n",
       "c  snake  0.5\n",
       "d    dog  NaN\n",
       "e    dog  5.0\n",
       "f    cat  2.0\n",
       "g  snake  4.5\n",
       "h    cat  NaN\n",
       "i    dog  7.0\n",
       "j    dog  3.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['animal','age']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**8.** Select the data in rows `[3, 4, 8]` *and* in columns `['animal', 'age']`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>animal</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>dog</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>dog</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>i</th>\n",
       "      <td>dog</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  animal  age\n",
       "d    dog  NaN\n",
       "e    dog  5.0\n",
       "i    dog  7.0"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['animal','age']].iloc[[3,4,8]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**9.** Select only the rows where the number of visits is greater than 3."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>animal</th>\n",
       "      <th>age</th>\n",
       "      <th>visits</th>\n",
       "      <th>priority</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [animal, age, visits, priority]\n",
       "Index: []"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('visits>3')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**10.** Select the rows where the age is missing, i.e. it is `NaN`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>animal</th>\n",
       "      <th>age</th>\n",
       "      <th>visits</th>\n",
       "      <th>priority</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>dog</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>h</th>\n",
       "      <td>cat</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  animal  age  visits priority\n",
       "d    dog  NaN       3      yes\n",
       "h    cat  NaN       1      yes"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('age.isnull()')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**11.** Select the rows where the animal is a cat *and* the age is less than 3."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>animal</th>\n",
       "      <th>age</th>\n",
       "      <th>visits</th>\n",
       "      <th>priority</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>cat</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f</th>\n",
       "      <td>cat</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  animal  age  visits priority\n",
       "a    cat  2.5       1      yes\n",
       "f    cat  2.0       3       no"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('animal==\"cat\" & age < 3')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**12.** Select the rows the age is between 2 and 4 (inclusive)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>animal</th>\n",
       "      <th>age</th>\n",
       "      <th>visits</th>\n",
       "      <th>priority</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>cat</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>cat</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>j</th>\n",
       "      <td>dog</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  animal  age  visits priority\n",
       "a    cat  2.5       1      yes\n",
       "b    cat  3.0       3      yes\n",
       "j    dog  3.0       1       no"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('age<4 & age>2')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**13.** Change the age in row 'f' to 1.5."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc['f','age']=1.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "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>animal</th>\n",
       "      <th>age</th>\n",
       "      <th>visits</th>\n",
       "      <th>priority</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>cat</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>cat</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>snake</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>dog</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>dog</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f</th>\n",
       "      <td>cat</td>\n",
       "      <td>1.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>g</th>\n",
       "      <td>snake</td>\n",
       "      <td>4.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>h</th>\n",
       "      <td>cat</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>i</th>\n",
       "      <td>dog</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>j</th>\n",
       "      <td>dog</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  animal  age  visits priority\n",
       "a    cat  2.5     1.0      yes\n",
       "b    cat  3.0     3.0      yes\n",
       "c  snake  0.5     2.0       no\n",
       "d    dog  NaN     3.0      yes\n",
       "e    dog  5.0     2.0       no\n",
       "f    cat  1.5     3.0       no\n",
       "g  snake  4.5     1.0       no\n",
       "h    cat  NaN     1.0      yes\n",
       "i    dog  7.0     2.0       no\n",
       "j    dog  3.0     1.0       no\n",
       "5    NaN  1.5     NaN      NaN"
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**14.** Calculate the sum of all visits in `df` (i.e. find the total number of visits)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "df.agg('sum','visits')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "19"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['visits'].agg('sum')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**15.** Calculate the mean age for each different animal in `df`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "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>age</th>\n",
       "      <th>0.5</th>\n",
       "      <th>2.0</th>\n",
       "      <th>2.5</th>\n",
       "      <th>3.0</th>\n",
       "      <th>4.5</th>\n",
       "      <th>5.0</th>\n",
       "      <th>7.0</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>animal</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>cat</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dog</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>snake</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "age     0.5  2.0  2.5  3.0  4.5  5.0  7.0  All\n",
       "animal                                        \n",
       "cat       0    1    1    1    0    0    0    3\n",
       "dog       0    0    0    1    0    1    1    3\n",
       "snake     1    0    0    0    1    0    0    2\n",
       "All       1    1    1    2    1    1    1    8"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(df['animal'],df['age'],margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**16.** Append a new row 'k' to `df` with your choice of values for each column. Then delete that row to return the original DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\willi\\AppData\\Local\\Temp\\ipykernel_21108\\1088757589.py:4: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df.append(df1)\n"
     ]
    },
    {
     "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>animal</th>\n",
       "      <th>age</th>\n",
       "      <th>visits</th>\n",
       "      <th>priority</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>cat</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>cat</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>snake</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>dog</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>dog</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f</th>\n",
       "      <td>cat</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>g</th>\n",
       "      <td>snake</td>\n",
       "      <td>4.5</td>\n",
       "      <td>1</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>h</th>\n",
       "      <td>cat</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>i</th>\n",
       "      <td>dog</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>j</th>\n",
       "      <td>dog</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  animal  age  visits priority\n",
       "a    cat  2.5       1      yes\n",
       "b    cat  3.0       3      yes\n",
       "c  snake  0.5       2       no\n",
       "d    dog  NaN       3      yes\n",
       "e    dog  5.0       2       no\n",
       "f    cat  2.0       3       no\n",
       "g  snake  4.5       1       no\n",
       "h    cat  NaN       1      yes\n",
       "i    dog  7.0       2       no\n",
       "j    dog  3.0       1       no"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1={'animal':'cat', 'age':4.0, 'visits':2, 'priority':'yes'}\n",
    "label1=['k']\n",
    "df1=pd.DataFrame(data1,label1)\n",
    "df.append(df1)\n",
    "df.drop(df1,axis=1)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**17.** Count the number of each type of animal in `df`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['animal'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**18.** Sort `df` first by the values in the 'age' in *decending* order, then by the value in the 'visits' column in *ascending* order (so row `i` should be first, and row `d` should be last)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "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>animal</th>\n",
       "      <th>age</th>\n",
       "      <th>visits</th>\n",
       "      <th>priority</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>i</th>\n",
       "      <td>dog</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>dog</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>g</th>\n",
       "      <td>snake</td>\n",
       "      <td>4.5</td>\n",
       "      <td>1</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>j</th>\n",
       "      <td>dog</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>cat</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>cat</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f</th>\n",
       "      <td>cat</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>snake</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>h</th>\n",
       "      <td>cat</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>dog</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  animal  age  visits priority\n",
       "i    dog  7.0       2       no\n",
       "e    dog  5.0       2       no\n",
       "g  snake  4.5       1       no\n",
       "j    dog  3.0       1       no\n",
       "b    cat  3.0       3      yes\n",
       "a    cat  2.5       1      yes\n",
       "f    cat  2.0       3       no\n",
       "c  snake  0.5       2       no\n",
       "h    cat  NaN       1      yes\n",
       "d    dog  NaN       3      yes"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(['age','visits'], ascending=[False, True])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**19.** The 'priority' column contains the values 'yes' and 'no'. Replace this column with a column of boolean values: 'yes' should be `True` and 'no' should be `False`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a      yes\n",
       "b      yes\n",
       "c    False\n",
       "d      yes\n",
       "e    False\n",
       "f    False\n",
       "g    False\n",
       "h      yes\n",
       "i    False\n",
       "j    False\n",
       "Name: priority, dtype: object"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['priority'].replace('yes',True)\n",
    "df['priority'].replace('no',False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**20.** In the 'animal' column, change the 'snake' entries to 'python'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a       cat\n",
       "b       cat\n",
       "c    python\n",
       "d       dog\n",
       "e       dog\n",
       "f       cat\n",
       "g    python\n",
       "h       cat\n",
       "i       dog\n",
       "j       dog\n",
       "Name: animal, dtype: object"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['animal'].replace('snake','python')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**21.** For each animal type and each number of visits, find the mean age. In other words, each row is an animal, each column is a number of visits and the values are the mean ages (*hint: use a pivot table*)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "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>col_0</th>\n",
       "      <th>3.4375</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>animal</th>\n",
       "      <th>visits</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">cat</th>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">dog</th>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">snake</th>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "col_0          3.4375\n",
       "animal visits        \n",
       "cat    1            2\n",
       "       3            2\n",
       "dog    1            1\n",
       "       2            2\n",
       "       3            1\n",
       "snake  1            1\n",
       "       2            1"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab([df['animal'],df['visits']],df['age'].mean())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrames: beyond the basics\n",
    "\n",
    "### Slightly trickier: you may need to combine two or more methods to get the right answer\n",
    "\n",
    "Difficulty: *medium*\n",
    "\n",
    "The previous section was tour through some basic but essential DataFrame operations. Below are some ways that you might need to cut your data, but for which there is no single \"out of the box\" method."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**22.** You have a DataFrame `df` with a column 'A' of integers. For example:\n",
    "```python\n",
    "df = pd.DataFrame({'A': [1, 2, 2, 3, 4, 5, 5, 5, 6, 7, 7]})\n",
    "```\n",
    "\n",
    "How do you filter out rows which contain the same integer as the row immediately above?\n",
    "\n",
    "You should be left with a column containing the following values:\n",
    "\n",
    "```python\n",
    "1, 2, 3, 4, 5, 6, 7\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.filter()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**23.** Given a DataFrame of numeric values, say\n",
    "```python\n",
    "df = pd.DataFrame(np.random.random(size=(5, 3))) # a 5x3 frame of float values\n",
    "```\n",
    "\n",
    "how do you subtract the row mean from each element in the row?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\willi\\AppData\\Local\\Temp\\ipykernel_21108\\2442767684.py:1: FutureWarning: Dropping of nuisance columns in DataFrame reductions (with 'numeric_only=None') is deprecated; in a future version this will raise TypeError.  Select only valid columns before calling the reduction.\n",
      "  df-df.mean()\n"
     ]
    },
    {
     "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>age</th>\n",
       "      <th>animal</th>\n",
       "      <th>priority</th>\n",
       "      <th>visits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>-0.9375</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>-0.4375</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>-2.9375</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>1.5625</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f</th>\n",
       "      <td>-1.4375</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>g</th>\n",
       "      <td>1.0625</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>h</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>i</th>\n",
       "      <td>3.5625</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>j</th>\n",
       "      <td>-0.4375</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      age animal priority  visits\n",
       "a -0.9375    NaN      NaN    -0.9\n",
       "b -0.4375    NaN      NaN     1.1\n",
       "c -2.9375    NaN      NaN     0.1\n",
       "d     NaN    NaN      NaN     1.1\n",
       "e  1.5625    NaN      NaN     0.1\n",
       "f -1.4375    NaN      NaN     1.1\n",
       "g  1.0625    NaN      NaN    -0.9\n",
       "h     NaN    NaN      NaN    -0.9\n",
       "i  3.5625    NaN      NaN     0.1\n",
       "j -0.4375    NaN      NaN    -0.9"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df-df.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**24.** Suppose you have DataFrame with 10 columns of real numbers, for example:\n",
    "\n",
    "```python\n",
    "df = pd.DataFrame(np.random.random(size=(5, 10)), columns=list('abcdefghij'))\n",
    "```\n",
    "Which column of numbers has the smallest sum?  Return that column's label."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**25.** How do you count how many unique rows a DataFrame has (i.e. ignore all rows that are duplicates)? As input, use a DataFrame of zeros and ones with 10 rows and 3 columns.\n",
    "\n",
    "```python\n",
    "df = pd.DataFrame(np.random.randint(0, 2, size=(10, 3)))\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "animal      3\n",
       "age         7\n",
       "visits      3\n",
       "priority    2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The next three puzzles are slightly harder.\n",
    "\n",
    "\n",
    "**26.** In the cell below, you have a DataFrame `df` that consists of 10 columns of floating-point numbers. Exactly 5 entries in each row are NaN values. \n",
    "\n",
    "For each row of the DataFrame, find the *column* which contains the *third* NaN value.\n",
    "\n",
    "You should return a Series of column labels: `e, c, d, h, d`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    e\n",
       "1    c\n",
       "2    d\n",
       "3    h\n",
       "4    d\n",
       "dtype: object"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nan = np.nan\n",
    "\n",
    "data = [[0.04,  nan,  nan, 0.25,  nan, 0.43, 0.71, 0.51,  nan,  nan],\n",
    "        [ nan,  nan,  nan, 0.04, 0.76,  nan,  nan, 0.67, 0.76, 0.16],\n",
    "        [ nan,  nan, 0.5 ,  nan, 0.31, 0.4 ,  nan,  nan, 0.24, 0.01],\n",
    "        [0.49,  nan,  nan, 0.62, 0.73, 0.26, 0.85,  nan,  nan,  nan],\n",
    "        [ nan,  nan, 0.41,  nan, 0.05,  nan, 0.61,  nan, 0.48, 0.68]]\n",
    "\n",
    "columns = list('abcdefghij')\n",
    "\n",
    "df = pd.DataFrame(data, columns=columns)\n",
    "\n",
    "(df.isnull().cumsum(axis=1)==3).idxmax(axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**27.** A DataFrame has a column of groups 'grps' and and column of integer values 'vals': \n",
    "\n",
    "```python\n",
    "df = pd.DataFrame({'grps': list('aaabbcaabcccbbc'), \n",
    "                   'vals': [12,345,3,1,45,14,4,52,54,23,235,21,57,3,87]})\n",
    "```\n",
    "For each *group*, find the sum of the three greatest values. You should end up with the answer as follows:\n",
    "```\n",
    "grps\n",
    "a    409\n",
    "b    156\n",
    "c    345\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "grps    aaaaa\n",
      "vals      416\n",
      "dtype: object\n",
      "grps    bbbbb\n",
      "vals      160\n",
      "dtype: object\n",
      "grps    ccccc\n",
      "vals      380\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame({'grps': list('aaabbcaabcccbbc'), \n",
    "                   'vals': [12,345,3,1,45,14,4,52,54,23,235,21,57,3,87]})\n",
    "\n",
    "a=df.query(\"grps=='a'\").sum()\n",
    "b=df.query(\"grps=='b'\").sum()\n",
    "c=df.query(\"grps=='c'\").sum()\n",
    "\n",
    "print(a)\n",
    "print(b)\n",
    "print(c)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**28.** The DataFrame `df` constructed below has two integer columns 'A' and 'B'. The values in 'A' are between 1 and 100 (inclusive). \n",
    "\n",
    "For each group of 10 consecutive integers in 'A' (i.e. `(0, 10]`, `(10, 20]`, ...), calculate the sum of the corresponding values in column 'B'.\n",
    "\n",
    "The answer should be a Series as follows:\n",
    "\n",
    "```\n",
    "A\n",
    "(0, 10]      635\n",
    "(10, 20]     360\n",
    "(20, 30]     315\n",
    "(30, 40]     306\n",
    "(40, 50]     750\n",
    "(50, 60]     284\n",
    "(60, 70]     424\n",
    "(70, 80]     526\n",
    "(80, 90]     835\n",
    "(90, 100]    852\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>46</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>75</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>49</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>33</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>71</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>60</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>57</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>86</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>50</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>97</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     A   B\n",
       "0   46  29\n",
       "1   75  22\n",
       "2   49  63\n",
       "3   33  43\n",
       "4   71  75\n",
       "..  ..  ..\n",
       "95  60  87\n",
       "96  57  40\n",
       "97  86  19\n",
       "98  50  56\n",
       "99  97  94\n",
       "\n",
       "[100 rows x 2 columns]"
      ]
     },
     "execution_count": 193,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(np.random.RandomState(8765).randint(1, 101, size=(100, 2)), columns = [\"A\", \"B\"])\n",
    "\n",
    "# write a solution to the question here\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrames: harder problems \n",
    "\n",
    "### These might require a bit of thinking outside the box...\n",
    "\n",
    "...but all are solvable using just the usual pandas/NumPy methods (and so avoid using explicit `for` loops).\n",
    "\n",
    "Difficulty: *hard*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**29.** Consider a DataFrame `df` where there is an integer column 'X':\n",
    "```python\n",
    "df = pd.DataFrame({'X': [7, 2, 0, 3, 4, 2, 5, 0, 3, 4]})\n",
    "```\n",
    "For each value, count the difference back to the previous zero (or the start of the Series, whichever is closer). These values should therefore be \n",
    "\n",
    "```\n",
    "[1, 2, 0, 1, 2, 3, 4, 0, 1, 2]\n",
    "```\n",
    "\n",
    "Make this a new column 'Y'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**30.** Consider the DataFrame constructed below which contains rows and columns of numerical data. \n",
    "\n",
    "Create a list of the column-row index locations of the 3 largest values in this DataFrame. In this case, the answer should be:\n",
    "```\n",
    "[(5, 7), (6, 4), (2, 5)]\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame(np.random.RandomState(30).randint(1, 101, size=(8, 8)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**31.** You are given the DataFrame below with a column of group IDs, 'grps', and a column of corresponding integer values, 'vals'.\n",
    "\n",
    "```python\n",
    "df = pd.DataFrame({\"vals\": np.random.RandomState(31).randint(-30, 30, size=15), \n",
    "                   \"grps\": np.random.RandomState(31).choice([\"A\", \"B\"], 15)})\n",
    "```\n",
    "\n",
    "Create a new column 'patched_values' which contains the same values as the 'vals' any negative values in 'vals' with the group mean:\n",
    "\n",
    "```\n",
    "    vals grps  patched_vals\n",
    "0    -12    A          13.6\n",
    "1     -7    B          28.0\n",
    "2    -14    A          13.6\n",
    "3      4    A           4.0\n",
    "4     -7    A          13.6\n",
    "5     28    B          28.0\n",
    "6     -2    A          13.6\n",
    "7     -1    A          13.6\n",
    "8      8    A           8.0\n",
    "9     -2    B          28.0\n",
    "10    28    A          28.0\n",
    "11    12    A          12.0\n",
    "12    16    A          16.0\n",
    "13   -24    A          13.6\n",
    "14   -12    A          13.6\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**32.** Implement a rolling mean over groups with window size 3, which ignores NaN value. For example consider the following DataFrame:\n",
    "\n",
    "```python\n",
    ">>> df = pd.DataFrame({'group': list('aabbabbbabab'),\n",
    "                       'value': [1, 2, 3, np.nan, 2, 3, np.nan, 1, 7, 3, np.nan, 8]})\n",
    ">>> df\n",
    "   group  value\n",
    "0      a    1.0\n",
    "1      a    2.0\n",
    "2      b    3.0\n",
    "3      b    NaN\n",
    "4      a    2.0\n",
    "5      b    3.0\n",
    "6      b    NaN\n",
    "7      b    1.0\n",
    "8      a    7.0\n",
    "9      b    3.0\n",
    "10     a    NaN\n",
    "11     b    8.0\n",
    "```\n",
    "The goal is to compute the Series:\n",
    "\n",
    "```\n",
    "0     1.000000\n",
    "1     1.500000\n",
    "2     3.000000\n",
    "3     3.000000\n",
    "4     1.666667\n",
    "5     3.000000\n",
    "6     3.000000\n",
    "7     2.000000\n",
    "8     3.666667\n",
    "9     2.000000\n",
    "10    4.500000\n",
    "11    4.000000\n",
    "```\n",
    "E.g. the first window of size three for group 'b' has values 3.0, NaN and 3.0 and occurs at row index 5. Instead of being NaN the value in the new column at this row index should be 3.0 (just the two non-NaN values are used to compute the mean (3+3)/2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Series and DatetimeIndex\n",
    "\n",
    "### Exercises for creating and manipulating Series with datetime data\n",
    "\n",
    "Difficulty: *easy/medium*\n",
    "\n",
    "pandas is fantastic for working with dates and times. These puzzles explore some of this functionality.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**33.** Create a DatetimeIndex that contains each business day of 2015 and use it to index a Series of random numbers. Let's call this Series `s`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "260"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "timestart=np.datetime64('2015-01-01')\n",
    "timeend=np.datetime64('2015-12-31')\n",
    "np.busday_count(timestart,timeend)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**34.** Find the sum of the values in `s` for every Wednesday."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**35.** For each calendar month in `s`, find the mean of values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**36.** For each group of four consecutive calendar months in `s`, find the date on which the highest value occurred."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**37.** Create a DateTimeIndex consisting of the third Thursday in each month for the years 2015 and 2016."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning Data\n",
    "\n",
    "### Making a DataFrame easier to work with\n",
    "\n",
    "Difficulty: *easy/medium*\n",
    "\n",
    "It happens all the time: someone gives you data containing malformed strings, Python, lists and missing data. How do you tidy it up so you can get on with the analysis?\n",
    "\n",
    "Take this monstrosity as the DataFrame to use in the following puzzles:\n",
    "\n",
    "```python\n",
    "df = pd.DataFrame({'From_To': ['LoNDon_paris', 'MAdrid_miLAN', 'londON_StockhOlm', \n",
    "                               'Budapest_PaRis', 'Brussels_londOn'],\n",
    "              'FlightNumber': [10045, np.nan, 10065, np.nan, 10085],\n",
    "              'RecentDelays': [[23, 47], [], [24, 43, 87], [13], [67, 32]],\n",
    "                   'Airline': ['KLM(!)', '<Air France> (12)', '(British Airways. )', \n",
    "                               '12. Air France', '\"Swiss Air\"']})\n",
    "```\n",
    "Formatted, it looks like this:\n",
    "\n",
    "```\n",
    "            From_To  FlightNumber  RecentDelays              Airline\n",
    "0      LoNDon_paris       10045.0      [23, 47]               KLM(!)\n",
    "1      MAdrid_miLAN           NaN            []    <Air France> (12)\n",
    "2  londON_StockhOlm       10065.0  [24, 43, 87]  (British Airways. )\n",
    "3    Budapest_PaRis           NaN          [13]       12. Air France\n",
    "4   Brussels_londOn       10085.0      [67, 32]          \"Swiss Air\"\n",
    "```\n",
    "\n",
    "\n",
    "(It's some flight data I made up; it's not meant to be accurate in any way.)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**38.** Some values in the the **FlightNumber** column are missing (they are `NaN`). These numbers are meant to increase by 10 with each row so 10055 and 10075 need to be put in place. Modify `df` to fill in these missing numbers and make the column an integer column (instead of a float column)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "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>From_To</th>\n",
       "      <th>FlightNumber</th>\n",
       "      <th>RecentDelays</th>\n",
       "      <th>Airline</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LoNDon_paris</td>\n",
       "      <td>10045</td>\n",
       "      <td>[23, 47]</td>\n",
       "      <td>KLM(!)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MAdrid_miLAN</td>\n",
       "      <td>10055</td>\n",
       "      <td>[]</td>\n",
       "      <td>&lt;Air France&gt; (12)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>londON_StockhOlm</td>\n",
       "      <td>10065</td>\n",
       "      <td>[24, 43, 87]</td>\n",
       "      <td>(British Airways. )</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Budapest_PaRis</td>\n",
       "      <td>10075</td>\n",
       "      <td>[13]</td>\n",
       "      <td>12. Air France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Brussels_londOn</td>\n",
       "      <td>10085</td>\n",
       "      <td>[67, 32]</td>\n",
       "      <td>\"Swiss Air\"</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            From_To  FlightNumber  RecentDelays              Airline\n",
       "0      LoNDon_paris         10045      [23, 47]               KLM(!)\n",
       "1      MAdrid_miLAN         10055            []    <Air France> (12)\n",
       "2  londON_StockhOlm         10065  [24, 43, 87]  (British Airways. )\n",
       "3    Budapest_PaRis         10075          [13]       12. Air France\n",
       "4   Brussels_londOn         10085      [67, 32]          \"Swiss Air\""
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "df = pd.DataFrame({'From_To': ['LoNDon_paris', 'MAdrid_miLAN', 'londON_StockhOlm', \n",
    "                               'Budapest_PaRis', 'Brussels_londOn'],\n",
    "              'FlightNumber': [10045, np.nan, 10065, np.nan, 10085],\n",
    "              'RecentDelays': [[23, 47], [], [24, 43, 87], [13], [67, 32]],\n",
    "                   'Airline': ['KLM(!)', '<Air France> (12)', '(British Airways. )', \n",
    "                               '12. Air France', '\"Swiss Air\"']})\n",
    "#df.astype({'FlightNumber':'int64'}).dytpes\n",
    "df=df.fillna(10055.0, limit=1)\n",
    "df=df.fillna(10075.0, limit=1)\n",
    "df\n",
    "df.astype({'FlightNumber':'int64'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**39.** The **From\\_To** column would be better as two separate columns! Split each string on the underscore delimiter `_` to give a new temporary DataFrame called 'temp' with the correct values. Assign the correct column names 'From' and 'To' to this temporary DataFrame. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "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>From</th>\n",
       "      <th>To</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LoNDon</td>\n",
       "      <td>paris</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MAdrid</td>\n",
       "      <td>miLAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>londON</td>\n",
       "      <td>StockhOlm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Budapest</td>\n",
       "      <td>PaRis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Brussels</td>\n",
       "      <td>londOn</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       From         To\n",
       "0    LoNDon      paris\n",
       "1    MAdrid      miLAN\n",
       "2    londON  StockhOlm\n",
       "3  Budapest      PaRis\n",
       "4  Brussels     londOn"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tempDict={'From': [],'To':[]}\n",
    "for i in range(len(df['From_To'])):\n",
    "    tempVal=df['From_To'][i].split('_')\n",
    "    tempDict['From'].append(tempVal[0])\n",
    "    tempDict['To'].append(tempVal[1])\n",
    "tempDict\n",
    "temp=pd.DataFrame(tempDict)\n",
    "temp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**40.** Notice how the capitalisation of the city names is all mixed up in this temporary DataFrame 'temp'. Standardise the strings so that only the first letter is uppercase (e.g. \"londON\" should become \"London\".)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "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>From</th>\n",
       "      <th>To</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>London</td>\n",
       "      <td>Paris</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Madrid</td>\n",
       "      <td>Milan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>London</td>\n",
       "      <td>Stockholm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Budapest</td>\n",
       "      <td>Paris</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Brussels</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       From         To\n",
       "0    London      Paris\n",
       "1    Madrid      Milan\n",
       "2    London  Stockholm\n",
       "3  Budapest      Paris\n",
       "4  Brussels     London"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for i in range(len(temp['From'])):\n",
    "    temp['From'][i]=temp['From'][i].capitalize()\n",
    "    temp['To'][i]=temp['To'][i].capitalize()\n",
    "temp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**41.** Delete the **From_To** column from `df` and attach the temporary DataFrame 'temp' from the previous questions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df.drop(columns=['From_To'])\n",
    "df=pd.concat([temp,df],axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**42**. In the **Airline** column, you can see some extra puctuation and symbols have appeared around the airline names. Pull out just the airline name. E.g. `'(British Airways. )'` should become `'British Airways'`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "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>From</th>\n",
       "      <th>To</th>\n",
       "      <th>FlightNumber</th>\n",
       "      <th>RecentDelays</th>\n",
       "      <th>Airline</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>London</td>\n",
       "      <td>Paris</td>\n",
       "      <td>10045.0</td>\n",
       "      <td>[23, 47]</td>\n",
       "      <td>KLM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Madrid</td>\n",
       "      <td>Milan</td>\n",
       "      <td>10055.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>AirFrance12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>London</td>\n",
       "      <td>Stockholm</td>\n",
       "      <td>10065.0</td>\n",
       "      <td>[24, 43, 87]</td>\n",
       "      <td>BritishAirways</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Budapest</td>\n",
       "      <td>Paris</td>\n",
       "      <td>10075.0</td>\n",
       "      <td>[13]</td>\n",
       "      <td>12AirFrance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Brussels</td>\n",
       "      <td>London</td>\n",
       "      <td>10085.0</td>\n",
       "      <td>[67, 32]</td>\n",
       "      <td>SwissAir</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       From         To  FlightNumber  RecentDelays         Airline\n",
       "0    London      Paris       10045.0      [23, 47]             KLM\n",
       "1    Madrid      Milan       10055.0            []     AirFrance12\n",
       "2    London  Stockholm       10065.0  [24, 43, 87]  BritishAirways\n",
       "3  Budapest      Paris       10075.0          [13]     12AirFrance\n",
       "4  Brussels     London       10085.0      [67, 32]        SwissAir"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Airline']=df['Airline'].str.replace('\\W', '', regex=True).str.strip()\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**43**. In the RecentDelays column, the values have been entered into the DataFrame as a list. We would like each first value in its own column, each second value in its own column, and so on. If there isn't an Nth value, the value should be NaN.\n",
    "\n",
    "Expand the Series of lists into a DataFrame named `delays`, rename the columns `delay_1`, `delay_2`, etc. and replace the unwanted RecentDelays column in `df` with `delays`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "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>From</th>\n",
       "      <th>To</th>\n",
       "      <th>FlightNumber</th>\n",
       "      <th>Airline</th>\n",
       "      <th>delay_1</th>\n",
       "      <th>delay_2</th>\n",
       "      <th>delay_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>London</td>\n",
       "      <td>Paris</td>\n",
       "      <td>10045.0</td>\n",
       "      <td>KLM</td>\n",
       "      <td>23.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Madrid</td>\n",
       "      <td>Milan</td>\n",
       "      <td>10055.0</td>\n",
       "      <td>AirFrance12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>London</td>\n",
       "      <td>Stockholm</td>\n",
       "      <td>10065.0</td>\n",
       "      <td>BritishAirways</td>\n",
       "      <td>24.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>87.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Budapest</td>\n",
       "      <td>Paris</td>\n",
       "      <td>10075.0</td>\n",
       "      <td>12AirFrance</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Brussels</td>\n",
       "      <td>London</td>\n",
       "      <td>10085.0</td>\n",
       "      <td>SwissAir</td>\n",
       "      <td>67.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       From         To  FlightNumber         Airline delay_1 delay_2 delay_3\n",
       "0    London      Paris       10045.0             KLM    23.0    47.0     NaN\n",
       "1    Madrid      Milan       10055.0     AirFrance12     NaN     NaN     NaN\n",
       "2    London  Stockholm       10065.0  BritishAirways    24.0    43.0    87.0\n",
       "3  Budapest      Paris       10075.0     12AirFrance    13.0     NaN     NaN\n",
       "4  Brussels     London       10085.0        SwissAir    67.0    32.0     NaN"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "delays={'delay_1':[],'delay_2':[],'delay_3':[]}\n",
    "for i in range(len(df['RecentDelays'])):\n",
    "    for j in range(3):\n",
    "        if len(df['RecentDelays'][i])-1<j:\n",
    "            if j==0:\n",
    "                delays['delay_1'].append('NaN')\n",
    "            elif j==1:\n",
    "                delays['delay_2'].append('NaN')\n",
    "            elif j==2:\n",
    "                delays['delay_3'].append('NaN')\n",
    "        elif j==0:\n",
    "            delays['delay_1'].append(float(df['RecentDelays'][i][j]))\n",
    "        elif j==1:\n",
    "            delays['delay_2'].append(float(df['RecentDelays'][i][j]))\n",
    "        elif j==2:\n",
    "            delays['delay_3'].append(float(df['RecentDelays'][i][j]))\n",
    "temp2=pd.DataFrame(delays)\n",
    "temp2\n",
    "df=df.drop(columns=['RecentDelays'])\n",
    "df=pd.concat([df,temp2],axis=1)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The DataFrame should look much better now.\n",
    "```\n",
    "   FlightNumber          Airline      From         To  delay_1  delay_2  delay_3\n",
    "0         10045              KLM    London      Paris     23.0     47.0      NaN\n",
    "1         10055       Air France    Madrid      Milan      NaN      NaN      NaN\n",
    "2         10065  British Airways    London  Stockholm     24.0     43.0     87.0\n",
    "3         10075       Air France  Budapest      Paris     13.0      NaN      NaN\n",
    "4         10085        Swiss Air  Brussels     London     67.0     32.0      NaN\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}