data-analysis/048-pandas-melt/pandas-melt.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reshaping a DataFrame using Pandas melt()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a notebook for the medium article [Reshaping a DataFrame using Pandas melt()](https://bindichen.medium.com/reshaping-a-dataframe-using-pandas-melt-83a151ce1907)\n",
    "\n",
    "Please check out article for instructions\n",
    "\n",
    "**License**: [BSD 2-Clause](https://opensource.org/licenses/BSD-2-Clause)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Simplest melt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>22/01/2020</th>\n",
       "      <th>23/01/2020</th>\n",
       "      <th>24/01/2020</th>\n",
       "      <th>25/01/2020</th>\n",
       "      <th>26/01/2020</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>France</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>11</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>UK</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Country  22/01/2020  23/01/2020  24/01/2020  25/01/2020  26/01/2020\n",
       "0  France           1           4           7          10          13\n",
       "1      US           2           5           8          11          14\n",
       "2      UK           3           6           9          12          15"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_wide = pd.DataFrame({\n",
    "   \"Country\": [\"France\", \"US\", \"UK\"],\n",
    "   \"22/01/2020\": [1,2,3],\n",
    "   \"23/01/2020\": [4,5,6],\n",
    "   \"24/01/2020\": [7,8,9],\n",
    "   \"25/01/2020\": [10,11,12],\n",
    "   \"26/01/2020\": [13,14,15],\n",
    "  }\n",
    ")\n",
    "df_wide"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Country</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Country</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Country</td>\n",
       "      <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22/01/2020</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22/01/2020</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>22/01/2020</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>23/01/2020</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>23/01/2020</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>23/01/2020</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>24/01/2020</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>24/01/2020</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>24/01/2020</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>25/01/2020</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>25/01/2020</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>25/01/2020</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>26/01/2020</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>26/01/2020</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>26/01/2020</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      variable   value\n",
       "0      Country  France\n",
       "1      Country      US\n",
       "2      Country      UK\n",
       "3   22/01/2020       1\n",
       "4   22/01/2020       2\n",
       "5   22/01/2020       3\n",
       "6   23/01/2020       4\n",
       "7   23/01/2020       5\n",
       "8   23/01/2020       6\n",
       "9   24/01/2020       7\n",
       "10  24/01/2020       8\n",
       "11  24/01/2020       9\n",
       "12  25/01/2020      10\n",
       "13  25/01/2020      11\n",
       "14  25/01/2020      12\n",
       "15  26/01/2020      13\n",
       "16  26/01/2020      14\n",
       "17  26/01/2020      15"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# without any argument\n",
    "df_wide.melt()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Country</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>France</td>\n",
       "      <td>22/01/2020</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US</td>\n",
       "      <td>22/01/2020</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>UK</td>\n",
       "      <td>22/01/2020</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>France</td>\n",
       "      <td>23/01/2020</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US</td>\n",
       "      <td>23/01/2020</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>UK</td>\n",
       "      <td>23/01/2020</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>France</td>\n",
       "      <td>24/01/2020</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>US</td>\n",
       "      <td>24/01/2020</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>UK</td>\n",
       "      <td>24/01/2020</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>France</td>\n",
       "      <td>25/01/2020</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>US</td>\n",
       "      <td>25/01/2020</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>UK</td>\n",
       "      <td>25/01/2020</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>France</td>\n",
       "      <td>26/01/2020</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>US</td>\n",
       "      <td>26/01/2020</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>UK</td>\n",
       "      <td>26/01/2020</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Country    variable  value\n",
       "0   France  22/01/2020      1\n",
       "1       US  22/01/2020      2\n",
       "2       UK  22/01/2020      3\n",
       "3   France  23/01/2020      4\n",
       "4       US  23/01/2020      5\n",
       "5       UK  23/01/2020      6\n",
       "6   France  24/01/2020      7\n",
       "7       US  24/01/2020      8\n",
       "8       UK  24/01/2020      9\n",
       "9   France  25/01/2020     10\n",
       "10      US  25/01/2020     11\n",
       "11      UK  25/01/2020     12\n",
       "12  France  26/01/2020     13\n",
       "13      US  26/01/2020     14\n",
       "14      UK  26/01/2020     15"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_wide.melt(\n",
    "    id_vars='Country',\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. With custom name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Country</th>\n",
       "      <th>Date</th>\n",
       "      <th>Cases</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>France</td>\n",
       "      <td>22/01/2020</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US</td>\n",
       "      <td>22/01/2020</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>UK</td>\n",
       "      <td>22/01/2020</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>France</td>\n",
       "      <td>23/01/2020</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US</td>\n",
       "      <td>23/01/2020</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>UK</td>\n",
       "      <td>23/01/2020</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>France</td>\n",
       "      <td>24/01/2020</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>US</td>\n",
       "      <td>24/01/2020</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>UK</td>\n",
       "      <td>24/01/2020</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>France</td>\n",
       "      <td>25/01/2020</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>US</td>\n",
       "      <td>25/01/2020</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>UK</td>\n",
       "      <td>25/01/2020</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>France</td>\n",
       "      <td>26/01/2020</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>US</td>\n",
       "      <td>26/01/2020</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>UK</td>\n",
       "      <td>26/01/2020</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Country        Date  Cases\n",
       "0   France  22/01/2020      1\n",
       "1       US  22/01/2020      2\n",
       "2       UK  22/01/2020      3\n",
       "3   France  23/01/2020      4\n",
       "4       US  23/01/2020      5\n",
       "5       UK  23/01/2020      6\n",
       "6   France  24/01/2020      7\n",
       "7       US  24/01/2020      8\n",
       "8       UK  24/01/2020      9\n",
       "9   France  25/01/2020     10\n",
       "10      US  25/01/2020     11\n",
       "11      UK  25/01/2020     12\n",
       "12  France  26/01/2020     13\n",
       "13      US  26/01/2020     14\n",
       "14      UK  26/01/2020     15"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_wide.melt(\n",
    "    id_vars='Country',\n",
    "    var_name='Date',\n",
    "    value_name='Cases'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Multiple ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Country</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long</th>\n",
       "      <th>22/01/2020</th>\n",
       "      <th>23/01/2020</th>\n",
       "      <th>24/01/2020</th>\n",
       "      <th>25/01/2020</th>\n",
       "      <th>26/01/2020</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>France</td>\n",
       "      <td>31.8257</td>\n",
       "      <td>117.2264</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US</td>\n",
       "      <td>40.0000</td>\n",
       "      <td>-100.0000</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>11</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>UK</td>\n",
       "      <td>55.3781</td>\n",
       "      <td>-3.4360</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Country      Lat      Long  22/01/2020  23/01/2020  24/01/2020  25/01/2020  \\\n",
       "0  France  31.8257  117.2264           1           4           7          10   \n",
       "1      US  40.0000 -100.0000           2           5           8          11   \n",
       "2      UK  55.3781   -3.4360           3           6           9          12   \n",
       "\n",
       "   26/01/2020  \n",
       "0          13  \n",
       "1          14  \n",
       "2          15  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_wide = pd.DataFrame({\n",
    "   \"Country\": [\"France\", \"US\", \"UK\"],\n",
    "   \"Lat\": [31.8257, 40.0, 55.3781],\n",
    "   \"Long\": [117.2264, -100.0, -3.436],\n",
    "   \"22/01/2020\": [1,2,3],\n",
    "   \"23/01/2020\": [4,5,6],\n",
    "   \"24/01/2020\": [7,8,9],\n",
    "   \"25/01/2020\": [10,11,12],\n",
    "   \"26/01/2020\": [13,14,15],\n",
    "  }\n",
    ")\n",
    "df_wide"
   ]
  },
  {
   "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>Country</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long</th>\n",
       "      <th>Date</th>\n",
       "      <th>Cases</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>France</td>\n",
       "      <td>31.8257</td>\n",
       "      <td>117.2264</td>\n",
       "      <td>22/01/2020</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US</td>\n",
       "      <td>40.0000</td>\n",
       "      <td>-100.0000</td>\n",
       "      <td>22/01/2020</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>UK</td>\n",
       "      <td>55.3781</td>\n",
       "      <td>-3.4360</td>\n",
       "      <td>22/01/2020</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>France</td>\n",
       "      <td>31.8257</td>\n",
       "      <td>117.2264</td>\n",
       "      <td>23/01/2020</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US</td>\n",
       "      <td>40.0000</td>\n",
       "      <td>-100.0000</td>\n",
       "      <td>23/01/2020</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>UK</td>\n",
       "      <td>55.3781</td>\n",
       "      <td>-3.4360</td>\n",
       "      <td>23/01/2020</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>France</td>\n",
       "      <td>31.8257</td>\n",
       "      <td>117.2264</td>\n",
       "      <td>24/01/2020</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>US</td>\n",
       "      <td>40.0000</td>\n",
       "      <td>-100.0000</td>\n",
       "      <td>24/01/2020</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>UK</td>\n",
       "      <td>55.3781</td>\n",
       "      <td>-3.4360</td>\n",
       "      <td>24/01/2020</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>France</td>\n",
       "      <td>31.8257</td>\n",
       "      <td>117.2264</td>\n",
       "      <td>25/01/2020</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>US</td>\n",
       "      <td>40.0000</td>\n",
       "      <td>-100.0000</td>\n",
       "      <td>25/01/2020</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>UK</td>\n",
       "      <td>55.3781</td>\n",
       "      <td>-3.4360</td>\n",
       "      <td>25/01/2020</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>France</td>\n",
       "      <td>31.8257</td>\n",
       "      <td>117.2264</td>\n",
       "      <td>26/01/2020</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>US</td>\n",
       "      <td>40.0000</td>\n",
       "      <td>-100.0000</td>\n",
       "      <td>26/01/2020</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>UK</td>\n",
       "      <td>55.3781</td>\n",
       "      <td>-3.4360</td>\n",
       "      <td>26/01/2020</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Country      Lat      Long        Date  Cases\n",
       "0   France  31.8257  117.2264  22/01/2020      1\n",
       "1       US  40.0000 -100.0000  22/01/2020      2\n",
       "2       UK  55.3781   -3.4360  22/01/2020      3\n",
       "3   France  31.8257  117.2264  23/01/2020      4\n",
       "4       US  40.0000 -100.0000  23/01/2020      5\n",
       "5       UK  55.3781   -3.4360  23/01/2020      6\n",
       "6   France  31.8257  117.2264  24/01/2020      7\n",
       "7       US  40.0000 -100.0000  24/01/2020      8\n",
       "8       UK  55.3781   -3.4360  24/01/2020      9\n",
       "9   France  31.8257  117.2264  25/01/2020     10\n",
       "10      US  40.0000 -100.0000  25/01/2020     11\n",
       "11      UK  55.3781   -3.4360  25/01/2020     12\n",
       "12  France  31.8257  117.2264  26/01/2020     13\n",
       "13      US  40.0000 -100.0000  26/01/2020     14\n",
       "14      UK  55.3781   -3.4360  26/01/2020     15"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_wide.melt(\n",
    "    id_vars=['Country', 'Lat', 'Long'],\n",
    "    var_name='Date',\n",
    "    value_name='Cases'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Specify the columns to melt"
   ]
  },
  {
   "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>Country</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long</th>\n",
       "      <th>Date</th>\n",
       "      <th>Cases</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>France</td>\n",
       "      <td>31.8257</td>\n",
       "      <td>117.2264</td>\n",
       "      <td>24/01/2020</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US</td>\n",
       "      <td>40.0000</td>\n",
       "      <td>-100.0000</td>\n",
       "      <td>24/01/2020</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>UK</td>\n",
       "      <td>55.3781</td>\n",
       "      <td>-3.4360</td>\n",
       "      <td>24/01/2020</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>France</td>\n",
       "      <td>31.8257</td>\n",
       "      <td>117.2264</td>\n",
       "      <td>25/01/2020</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US</td>\n",
       "      <td>40.0000</td>\n",
       "      <td>-100.0000</td>\n",
       "      <td>25/01/2020</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>UK</td>\n",
       "      <td>55.3781</td>\n",
       "      <td>-3.4360</td>\n",
       "      <td>25/01/2020</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Country      Lat      Long        Date  Cases\n",
       "0  France  31.8257  117.2264  24/01/2020      7\n",
       "1      US  40.0000 -100.0000  24/01/2020      8\n",
       "2      UK  55.3781   -3.4360  24/01/2020      9\n",
       "3  France  31.8257  117.2264  25/01/2020     10\n",
       "4      US  40.0000 -100.0000  25/01/2020     11\n",
       "5      UK  55.3781   -3.4360  25/01/2020     12"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_wide.melt(\n",
    "    id_vars=['Country', 'Lat', 'Long'],\n",
    "    value_vars=[\"24/01/2020\", \"25/01/2020\"],\n",
    "    var_name='Date',\n",
    "    value_name='Cases'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Pandas melt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Country</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>France</td>\n",
       "      <td>31.8257</td>\n",
       "      <td>117.2264</td>\n",
       "      <td>22/01/2020</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US</td>\n",
       "      <td>40.0000</td>\n",
       "      <td>-100.0000</td>\n",
       "      <td>22/01/2020</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>UK</td>\n",
       "      <td>55.3781</td>\n",
       "      <td>-3.4360</td>\n",
       "      <td>22/01/2020</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>France</td>\n",
       "      <td>31.8257</td>\n",
       "      <td>117.2264</td>\n",
       "      <td>23/01/2020</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US</td>\n",
       "      <td>40.0000</td>\n",
       "      <td>-100.0000</td>\n",
       "      <td>23/01/2020</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>UK</td>\n",
       "      <td>55.3781</td>\n",
       "      <td>-3.4360</td>\n",
       "      <td>23/01/2020</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>France</td>\n",
       "      <td>31.8257</td>\n",
       "      <td>117.2264</td>\n",
       "      <td>24/01/2020</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>US</td>\n",
       "      <td>40.0000</td>\n",
       "      <td>-100.0000</td>\n",
       "      <td>24/01/2020</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>UK</td>\n",
       "      <td>55.3781</td>\n",
       "      <td>-3.4360</td>\n",
       "      <td>24/01/2020</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>France</td>\n",
       "      <td>31.8257</td>\n",
       "      <td>117.2264</td>\n",
       "      <td>25/01/2020</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>US</td>\n",
       "      <td>40.0000</td>\n",
       "      <td>-100.0000</td>\n",
       "      <td>25/01/2020</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>UK</td>\n",
       "      <td>55.3781</td>\n",
       "      <td>-3.4360</td>\n",
       "      <td>25/01/2020</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>France</td>\n",
       "      <td>31.8257</td>\n",
       "      <td>117.2264</td>\n",
       "      <td>26/01/2020</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>US</td>\n",
       "      <td>40.0000</td>\n",
       "      <td>-100.0000</td>\n",
       "      <td>26/01/2020</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>UK</td>\n",
       "      <td>55.3781</td>\n",
       "      <td>-3.4360</td>\n",
       "      <td>26/01/2020</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Country      Lat      Long    variable  value\n",
       "0   France  31.8257  117.2264  22/01/2020      1\n",
       "1       US  40.0000 -100.0000  22/01/2020      2\n",
       "2       UK  55.3781   -3.4360  22/01/2020      3\n",
       "3   France  31.8257  117.2264  23/01/2020      4\n",
       "4       US  40.0000 -100.0000  23/01/2020      5\n",
       "5       UK  55.3781   -3.4360  23/01/2020      6\n",
       "6   France  31.8257  117.2264  24/01/2020      7\n",
       "7       US  40.0000 -100.0000  24/01/2020      8\n",
       "8       UK  55.3781   -3.4360  24/01/2020      9\n",
       "9   France  31.8257  117.2264  25/01/2020     10\n",
       "10      US  40.0000 -100.0000  25/01/2020     11\n",
       "11      UK  55.3781   -3.4360  25/01/2020     12\n",
       "12  France  31.8257  117.2264  26/01/2020     13\n",
       "13      US  40.0000 -100.0000  26/01/2020     14\n",
       "14      UK  55.3781   -3.4360  26/01/2020     15"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# You can also call melt directly from pandas instead of your DataFrame. However, these are identical\n",
    "pd.melt(df_wide, id_vars=['Country', 'Lat', 'Long'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Bonus: Covid-19 time series data preprocessing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6.1 Loading confirmed, death, and recovered dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "confirmed_df = pd.read_csv('time_series_covid19_confirmed_global.csv')\n",
    "deaths_df = pd.read_csv('time_series_covid19_deaths_global.csv')\n",
    "recovered_df = pd.read_csv('time_series_covid19_recovered_global.csv')"
   ]
  },
  {
   "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>Province/State</th>\n",
       "      <th>Country/Region</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long</th>\n",
       "      <th>1/22/20</th>\n",
       "      <th>1/23/20</th>\n",
       "      <th>1/24/20</th>\n",
       "      <th>1/25/20</th>\n",
       "      <th>1/26/20</th>\n",
       "      <th>1/27/20</th>\n",
       "      <th>...</th>\n",
       "      <th>4/10/20</th>\n",
       "      <th>4/11/20</th>\n",
       "      <th>4/12/20</th>\n",
       "      <th>4/13/20</th>\n",
       "      <th>4/14/20</th>\n",
       "      <th>4/15/20</th>\n",
       "      <th>4/16/20</th>\n",
       "      <th>4/17/20</th>\n",
       "      <th>4/18/20</th>\n",
       "      <th>4/19/20</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>33.0000</td>\n",
       "      <td>65.0000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>521</td>\n",
       "      <td>555</td>\n",
       "      <td>607</td>\n",
       "      <td>665</td>\n",
       "      <td>714</td>\n",
       "      <td>784</td>\n",
       "      <td>840</td>\n",
       "      <td>906</td>\n",
       "      <td>933</td>\n",
       "      <td>996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>41.1533</td>\n",
       "      <td>20.1683</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>416</td>\n",
       "      <td>433</td>\n",
       "      <td>446</td>\n",
       "      <td>467</td>\n",
       "      <td>475</td>\n",
       "      <td>494</td>\n",
       "      <td>518</td>\n",
       "      <td>539</td>\n",
       "      <td>548</td>\n",
       "      <td>562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>28.0339</td>\n",
       "      <td>1.6596</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1761</td>\n",
       "      <td>1825</td>\n",
       "      <td>1914</td>\n",
       "      <td>1983</td>\n",
       "      <td>2070</td>\n",
       "      <td>2160</td>\n",
       "      <td>2268</td>\n",
       "      <td>2418</td>\n",
       "      <td>2534</td>\n",
       "      <td>2629</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>42.5063</td>\n",
       "      <td>1.5218</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>601</td>\n",
       "      <td>601</td>\n",
       "      <td>638</td>\n",
       "      <td>646</td>\n",
       "      <td>659</td>\n",
       "      <td>673</td>\n",
       "      <td>673</td>\n",
       "      <td>696</td>\n",
       "      <td>704</td>\n",
       "      <td>713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>-11.2027</td>\n",
       "      <td>17.8739</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>24</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 93 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Province/State Country/Region      Lat     Long  1/22/20  1/23/20  1/24/20  \\\n",
       "0            NaN    Afghanistan  33.0000  65.0000        0        0        0   \n",
       "1            NaN        Albania  41.1533  20.1683        0        0        0   \n",
       "2            NaN        Algeria  28.0339   1.6596        0        0        0   \n",
       "3            NaN        Andorra  42.5063   1.5218        0        0        0   \n",
       "4            NaN         Angola -11.2027  17.8739        0        0        0   \n",
       "\n",
       "   1/25/20  1/26/20  1/27/20  ...  4/10/20  4/11/20  4/12/20  4/13/20  \\\n",
       "0        0        0        0  ...      521      555      607      665   \n",
       "1        0        0        0  ...      416      433      446      467   \n",
       "2        0        0        0  ...     1761     1825     1914     1983   \n",
       "3        0        0        0  ...      601      601      638      646   \n",
       "4        0        0        0  ...       19       19       19       19   \n",
       "\n",
       "   4/14/20  4/15/20  4/16/20  4/17/20  4/18/20  4/19/20  \n",
       "0      714      784      840      906      933      996  \n",
       "1      475      494      518      539      548      562  \n",
       "2     2070     2160     2268     2418     2534     2629  \n",
       "3      659      673      673      696      704      713  \n",
       "4       19       19       19       19       24       24  \n",
       "\n",
       "[5 rows x 93 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "confirmed_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6.2 Reshaping them from wide to long format with the same date values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Province/State', 'Country/Region', 'Lat', 'Long', '1/22/20', '1/23/20',\n",
       "       '1/24/20', '1/25/20', '1/26/20', '1/27/20', '1/28/20', '1/29/20',\n",
       "       '1/30/20', '1/31/20', '2/1/20', '2/2/20', '2/3/20', '2/4/20', '2/5/20',\n",
       "       '2/6/20', '2/7/20', '2/8/20', '2/9/20', '2/10/20', '2/11/20', '2/12/20',\n",
       "       '2/13/20', '2/14/20', '2/15/20', '2/16/20', '2/17/20', '2/18/20',\n",
       "       '2/19/20', '2/20/20', '2/21/20', '2/22/20', '2/23/20', '2/24/20',\n",
       "       '2/25/20', '2/26/20', '2/27/20', '2/28/20', '2/29/20', '3/1/20',\n",
       "       '3/2/20', '3/3/20', '3/4/20', '3/5/20', '3/6/20', '3/7/20', '3/8/20',\n",
       "       '3/9/20', '3/10/20', '3/11/20', '3/12/20', '3/13/20', '3/14/20',\n",
       "       '3/15/20', '3/16/20', '3/17/20', '3/18/20', '3/19/20', '3/20/20',\n",
       "       '3/21/20', '3/22/20', '3/23/20', '3/24/20', '3/25/20', '3/26/20',\n",
       "       '3/27/20', '3/28/20', '3/29/20', '3/30/20', '3/31/20', '4/1/20',\n",
       "       '4/2/20', '4/3/20', '4/4/20', '4/5/20', '4/6/20', '4/7/20', '4/8/20',\n",
       "       '4/9/20', '4/10/20', '4/11/20', '4/12/20', '4/13/20', '4/14/20',\n",
       "       '4/15/20', '4/16/20', '4/17/20', '4/18/20', '4/19/20'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "confirmed_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['1/22/20', '1/23/20', '1/24/20', '1/25/20', '1/26/20', '1/27/20',\n",
       "       '1/28/20', '1/29/20', '1/30/20', '1/31/20', '2/1/20', '2/2/20',\n",
       "       '2/3/20', '2/4/20', '2/5/20', '2/6/20', '2/7/20', '2/8/20', '2/9/20',\n",
       "       '2/10/20', '2/11/20', '2/12/20', '2/13/20', '2/14/20', '2/15/20',\n",
       "       '2/16/20', '2/17/20', '2/18/20', '2/19/20', '2/20/20', '2/21/20',\n",
       "       '2/22/20', '2/23/20', '2/24/20', '2/25/20', '2/26/20', '2/27/20',\n",
       "       '2/28/20', '2/29/20', '3/1/20', '3/2/20', '3/3/20', '3/4/20', '3/5/20',\n",
       "       '3/6/20', '3/7/20', '3/8/20', '3/9/20', '3/10/20', '3/11/20', '3/12/20',\n",
       "       '3/13/20', '3/14/20', '3/15/20', '3/16/20', '3/17/20', '3/18/20',\n",
       "       '3/19/20', '3/20/20', '3/21/20', '3/22/20', '3/23/20', '3/24/20',\n",
       "       '3/25/20', '3/26/20', '3/27/20', '3/28/20', '3/29/20', '3/30/20',\n",
       "       '3/31/20', '4/1/20', '4/2/20', '4/3/20', '4/4/20', '4/5/20', '4/6/20',\n",
       "       '4/7/20', '4/8/20', '4/9/20', '4/10/20', '4/11/20', '4/12/20',\n",
       "       '4/13/20', '4/14/20', '4/15/20', '4/16/20', '4/17/20', '4/18/20',\n",
       "       '4/19/20'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a date list\n",
    "dates = confirmed_df.columns[4:]\n",
    "dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "confirmed_df_long = confirmed_df.melt(id_vars=['Province/State', 'Country/Region', 'Lat', 'Long'], \n",
    "                            value_vars=dates, var_name='Date', value_name='Confirmed')\n",
    "\n",
    "deaths_df_long = deaths_df.melt(id_vars=['Province/State', 'Country/Region', 'Lat', 'Long'], \n",
    "                            value_vars=dates, var_name='Date', value_name='Deaths')\n",
    "\n",
    "recovered_df_long = recovered_df.melt(id_vars=['Province/State', 'Country/Region', 'Lat', 'Long'], \n",
    "                            value_vars=dates, var_name='Date', value_name='Recovered')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((23496, 6), (23496, 6), (22250, 6))"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(confirmed_df_long.shape, deaths_df_long.shape, recovered_df_long.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6.3 Merging them"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>Province/State</th>\n",
       "      <th>Country/Region</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long</th>\n",
       "      <th>Date</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>33.000000</td>\n",
       "      <td>65.000000</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>41.153300</td>\n",
       "      <td>20.168300</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>28.033900</td>\n",
       "      <td>1.659600</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>42.506300</td>\n",
       "      <td>1.521800</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>-11.202700</td>\n",
       "      <td>17.873900</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23491</th>\n",
       "      <td>Saint Pierre and Miquelon</td>\n",
       "      <td>France</td>\n",
       "      <td>46.885200</td>\n",
       "      <td>-56.315900</td>\n",
       "      <td>4/19/20</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23492</th>\n",
       "      <td>NaN</td>\n",
       "      <td>South Sudan</td>\n",
       "      <td>6.877000</td>\n",
       "      <td>31.307000</td>\n",
       "      <td>4/19/20</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23493</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Western Sahara</td>\n",
       "      <td>24.215500</td>\n",
       "      <td>-12.885800</td>\n",
       "      <td>4/19/20</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23494</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Sao Tome and Principe</td>\n",
       "      <td>0.186360</td>\n",
       "      <td>6.613081</td>\n",
       "      <td>4/19/20</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23495</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>15.552727</td>\n",
       "      <td>48.516388</td>\n",
       "      <td>4/19/20</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23496 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Province/State         Country/Region        Lat       Long  \\\n",
       "0                            NaN            Afghanistan  33.000000  65.000000   \n",
       "1                            NaN                Albania  41.153300  20.168300   \n",
       "2                            NaN                Algeria  28.033900   1.659600   \n",
       "3                            NaN                Andorra  42.506300   1.521800   \n",
       "4                            NaN                 Angola -11.202700  17.873900   \n",
       "...                          ...                    ...        ...        ...   \n",
       "23491  Saint Pierre and Miquelon                 France  46.885200 -56.315900   \n",
       "23492                        NaN            South Sudan   6.877000  31.307000   \n",
       "23493                        NaN         Western Sahara  24.215500 -12.885800   \n",
       "23494                        NaN  Sao Tome and Principe   0.186360   6.613081   \n",
       "23495                        NaN                  Yemen  15.552727  48.516388   \n",
       "\n",
       "          Date  Confirmed  Deaths  Recovered  \n",
       "0      1/22/20          0       0        0.0  \n",
       "1      1/22/20          0       0        0.0  \n",
       "2      1/22/20          0       0        0.0  \n",
       "3      1/22/20          0       0        0.0  \n",
       "4      1/22/20          0       0        0.0  \n",
       "...        ...        ...     ...        ...  \n",
       "23491  4/19/20          1       0        0.0  \n",
       "23492  4/19/20          4       0        0.0  \n",
       "23493  4/19/20          6       0        0.0  \n",
       "23494  4/19/20          4       0        0.0  \n",
       "23495  4/19/20          1       0        0.0  \n",
       "\n",
       "[23496 rows x 8 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merging confirmed_df_long and deaths_df_long\n",
    "full_table = confirmed_df_long.merge(\n",
    "  right=deaths_df_long, \n",
    "  how='left',\n",
    "  on=['Province/State', 'Country/Region', 'Date', 'Lat', 'Long']\n",
    ")\n",
    "\n",
    "# Merging full_table and recovered_df_long\n",
    "full_table = full_table.merge(\n",
    "  right=recovered_df_long, \n",
    "  how='left',\n",
    "  on=['Province/State', 'Country/Region', 'Date', 'Lat', 'Long']\n",
    ")\n",
    "\n",
    "full_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Thanks for reading\n",
    "\n",
    "This is a notebook for the medium article [Reshaping a DataFrame using Pandas melt()](https://bindichen.medium.com/reshaping-a-dataframe-using-pandas-melt-83a151ce1907)\n",
    "\n",
    "Please check out article for instructions\n",
    "\n",
    "**License**: [BSD 2-Clause](https://opensource.org/licenses/BSD-2-Clause)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "8e5b3dc472797fe8bb513ecdb33a0a623de0b0e36e9698a9218d88c7353364d6"
  },
  "kernelspec": {
   "display_name": "tf-tutorial",
   "language": "python",
   "name": "tf-tutorial"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}