data-analysis/074-pandas-explodes/explode-list-like-columns.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e1c091fd",
   "metadata": {},
   "source": [
    "#  Unnest/flatten/explode list-like columns with Pandas `explode()`\n",
    "\n",
    "This is a Notebook for the medium article [Exploding a list-like column with Pandas explode() method](https://bindichen.medium.com/exploding-a-list-like-column-with-pandas-explode-method-3ffd41f9f7e2)\n",
    "\n",
    "Please read article for instructions. \n",
    "\n",
    "**License**: [BSD 2-Clause](https://opensource.org/licenses/BSD-2-Clause). Not for commercial use!!!\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13398e8e",
   "metadata": {},
   "source": [
    "#### Version of packages used in this Notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5976f534",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "numpy:  1.21.5\n",
      "pandas:  1.4.2\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# Make sure your package version >= them\n",
    "print('numpy: ', np.__version__)\n",
    "print('pandas: ', pd.__version__)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64b73e26",
   "metadata": {},
   "source": [
    "#### Contents\n",
    "1. Basic usage\n",
    "2. Series use cases\n",
    " - 2.1 with other value\n",
    " - 2.2 with empty list\n",
    " - 2.3 String with delimiter\n",
    "3. DataFrame use cases\n",
    " - 3.1 Other value\n",
    " - 3.2 With empty value\n",
    " - 3.3 Multiple list-like columns\n",
    " - 3.4 String with delimiter"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bf0fbea",
   "metadata": {},
   "source": [
    "## 1. Basic usage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "48278100",
   "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>class</th>\n",
       "      <th>students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>[Tom, Jane]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>[Liz, James]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    class      students\n",
       "0  Year 1   [Tom, Jane]\n",
       "1  Year 2  [Liz, James]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "    'class': ['Year 1', 'Year 2'],\n",
    "    'students': [['Tom', 'Jane'], ['Liz', 'James']]\n",
    "})\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "be7d6254",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      Tom\n",
       "0     Jane\n",
       "1      Liz\n",
       "1    James\n",
       "Name: students, dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Availble in Series, notice that rows now have the same index value\n",
    "df['students'].explode()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a8b4b774",
   "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>class</th>\n",
       "      <th>students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>Tom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>Jane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>Liz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>James</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    class students\n",
       "0  Year 1      Tom\n",
       "0  Year 1     Jane\n",
       "1  Year 2      Liz\n",
       "1  Year 2    James"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pandas method, notice that rows now have the same index value\n",
    "df.explode('students')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "29367712",
   "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>class</th>\n",
       "      <th>students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>Tom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>Jane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>Liz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>James</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    class students\n",
       "0  Year 1      Tom\n",
       "1  Year 1     Jane\n",
       "2  Year 2      Liz\n",
       "3  Year 2    James"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reset index with ignore_index=True or reset_index()\n",
    "df.explode('students').reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "88533bbe",
   "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>class</th>\n",
       "      <th>students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>Tom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>Jane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>Liz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>James</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    class students\n",
       "0  Year 1      Tom\n",
       "1  Year 1     Jane\n",
       "2  Year 2      Liz\n",
       "3  Year 2    James"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.explode('students', ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e05f62ea",
   "metadata": {},
   "source": [
    "## 2. Series use cases "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "558bb3b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "s = pd.Series([['Tom', 'Jane'], ['Liz', 'James']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "be45841c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      Tom\n",
       "0     Jane\n",
       "1      Liz\n",
       "1    James\n",
       "dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.explode()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2586961",
   "metadata": {},
   "source": [
    "### 2.1 with other value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "bdf594c4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0           Tom\n",
       "0          Jane\n",
       "1           Liz\n",
       "1         James\n",
       "2    not a list\n",
       "3           101\n",
       "4         Katie\n",
       "4          Sean\n",
       "dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# non-list\n",
    "s = pd.Series(\n",
    "    [\n",
    "        ['Tom', 'Jane'], \n",
    "        ['Liz', 'James'], \n",
    "        'not a list', \n",
    "        101,\n",
    "        ['Katie', 'Sean']\n",
    "    ]\n",
    ")\n",
    "\n",
    "s.explode()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "227498b7",
   "metadata": {},
   "source": [
    "### 2.2 with empty list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b279a8cc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      Tom\n",
       "0     Jane\n",
       "1      Liz\n",
       "1    James\n",
       "2      NaN\n",
       "3    Katie\n",
       "3     Sean\n",
       "dtype: object"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Empty array, by default\n",
    "s = pd.Series([\n",
    "    ['Tom', 'Jane'], \n",
    "    ['Liz', 'James'], \n",
    "    [], \n",
    "    ['Katie', 'Sean']\n",
    "])\n",
    "\n",
    "s.explode()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89996312",
   "metadata": {},
   "source": [
    "### 2.3 String with delimiter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "a02a740b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      Tom, Jane\n",
       "1     Liz, James\n",
       "2    Katie, Sean\n",
       "dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# String with delimiter\n",
    "s = pd.Series([\n",
    "    'Tom, Jane', \n",
    "    'Liz, James', \n",
    "    'Katie, Sean'\n",
    "])\n",
    "\n",
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "ef9e0441",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       Tom\n",
       "0      Jane\n",
       "1       Liz\n",
       "1     James\n",
       "2     Katie\n",
       "2      Sean\n",
       "dtype: object"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.str.split(\",\").explode()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "713216c6",
   "metadata": {},
   "source": [
    "## 3. Dataframe use cases"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c697ca0",
   "metadata": {},
   "source": [
    "### 3.1 Other value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "7ddc9162",
   "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>class</th>\n",
       "      <th>students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>[Tom, Jane]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>I am a string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Year 3</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Year 4</td>\n",
       "      <td>[Katie, Sean]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    class       students\n",
       "0  Year 1    [Tom, Jane]\n",
       "1  Year 2  I am a string\n",
       "2  Year 3            101\n",
       "3  Year 4  [Katie, Sean]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Other value\n",
    "df = pd.DataFrame({\n",
    "    'class': ['Year 1', 'Year 2', 'Year 3', 'Year 4'],\n",
    "    'students': [\n",
    "        ['Tom', 'Jane'], \n",
    "        'I am a string', \n",
    "        101, \n",
    "        ['Katie', 'Sean']\n",
    "    ]\n",
    "})\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "aeccd119",
   "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>class</th>\n",
       "      <th>students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>Tom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>Jane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>I am a string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Year 3</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Year 4</td>\n",
       "      <td>Katie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Year 4</td>\n",
       "      <td>Sean</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    class       students\n",
       "0  Year 1            Tom\n",
       "0  Year 1           Jane\n",
       "1  Year 2  I am a string\n",
       "2  Year 3            101\n",
       "3  Year 4          Katie\n",
       "3  Year 4           Sean"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.explode('students')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad473c50",
   "metadata": {},
   "source": [
    "### 3.2 With empty value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "38a07d5a",
   "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>class</th>\n",
       "      <th>students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>[Tom, Jane]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>[Liz, James]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Year 3</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Year 4</td>\n",
       "      <td>[Katie, Sean]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    class       students\n",
       "0  Year 1    [Tom, Jane]\n",
       "1  Year 2   [Liz, James]\n",
       "2  Year 3             []\n",
       "3  Year 4  [Katie, Sean]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Empty\n",
    "df = pd.DataFrame({\n",
    "    'class': ['Year 1', 'Year 2', 'Year 3', 'Year 4'],\n",
    "    'students': [\n",
    "        ['Tom', 'Jane'], \n",
    "        ['Liz', 'James'], \n",
    "        [], \n",
    "        ['Katie', 'Sean']\n",
    "    ]\n",
    "})\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "26dc8d48",
   "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>class</th>\n",
       "      <th>students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>Tom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>Jane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>Liz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>James</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Year 3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Year 4</td>\n",
       "      <td>Katie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Year 4</td>\n",
       "      <td>Sean</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    class students\n",
       "0  Year 1      Tom\n",
       "0  Year 1     Jane\n",
       "1  Year 2      Liz\n",
       "1  Year 2    James\n",
       "2  Year 3      NaN\n",
       "3  Year 4    Katie\n",
       "3  Year 4     Sean"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.explode('students')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef610a5a",
   "metadata": {},
   "source": [
    "### 3.3 Multiple list-like columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "02384fbc",
   "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>class</th>\n",
       "      <th>students</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>[Tom, Jane]</td>\n",
       "      <td>[M, F]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>[Liz, James]</td>\n",
       "      <td>[F, M]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    class      students     sex\n",
       "0  Year 1   [Tom, Jane]  [M, F]\n",
       "1  Year 2  [Liz, James]  [F, M]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Multiple columns\n",
    "df = pd.DataFrame({\n",
    "    'class': ['Year 1', 'Year 2'],\n",
    "    'students': [['Tom', 'Jane'], ['Liz', 'James']],\n",
    "    'sex': [['M', 'F'], ['F', 'M']]\n",
    "})\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "14fbfa7e",
   "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>class</th>\n",
       "      <th>students</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>Tom</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>Jane</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>Liz</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>James</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    class students sex\n",
       "0  Year 1      Tom   M\n",
       "0  Year 1     Jane   F\n",
       "1  Year 2      Liz   F\n",
       "1  Year 2    James   M"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.explode(['students', 'sex'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "862a7f19",
   "metadata": {},
   "source": [
    "### 3.4 string with delimiter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "6e546b3f",
   "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>class</th>\n",
       "      <th>students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>Tom, Jane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>Liz, James</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    class    students\n",
       "0  Year 1   Tom, Jane\n",
       "1  Year 2  Liz, James"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# string with delimiter\n",
    "\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'class': ['Year 1', 'Year 2'],\n",
    "    'students': ['Tom, Jane', 'Liz, James']\n",
    "})\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "81c2f3d1",
   "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>class</th>\n",
       "      <th>students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>Tom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>Jane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>Liz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>James</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    class students\n",
       "0  Year 1      Tom\n",
       "0  Year 1     Jane\n",
       "1  Year 2      Liz\n",
       "1  Year 2    James"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.assign(students=df['students'].str.split(\",\")).explode(\"students\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51717039",
   "metadata": {},
   "source": [
    "## Thanks for reading\n",
    "\n",
    "This is a Notebook for the medium article [Exploding a list-like column with Pandas explode() method](https://bindichen.medium.com/exploding-a-list-like-column-with-pandas-explode-method-3ffd41f9f7e2)\n",
    "\n",
    "**License**: [BSD 2-Clause](https://opensource.org/licenses/BSD-2-Clause). Not for commercial use!!!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc54f124",
   "metadata": {},
   "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": 5
}