Pandas/Upsample/Upsample to average.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Upsample to average\n",
    "Notebook with example for the article on medium.com"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import io"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Downsample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=\"\"\"\n",
    "CAT|DATE|VALUE\n",
    "abc|0101|10\n",
    "abc|0103|20\n",
    "abc|0107|15\"\"\"\n",
    "df = pd.read_csv(io.StringIO(data), skiprows=1, sep=\"|\", dtype={\"DATE\":\"str\"})\n",
    "df[\"DATE\"] = pd.to_datetime(df[\"DATE\"], format=\"%m%d\")"
   ]
  },
  {
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"8\" halign=\"left\">VALUE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>median</th>\n",
       "      <th>first</th>\n",
       "      <th>last</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CAT</th>\n",
       "      <th>DATE</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>abc</th>\n",
       "      <th>1900-01-07</th>\n",
       "      <td>45</td>\n",
       "      <td>3</td>\n",
       "      <td>15</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>15</td>\n",
       "      <td>10</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               VALUE                                     \n",
       "                 sum count mean min max median first last\n",
       "CAT DATE                                                 \n",
       "abc 1900-01-07    45     3   15  10  20     15    10   15"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(\"CAT\").resample(\"2W\", on=\"DATE\").agg({\"VALUE\":[\"sum\",\"count\",\"mean\",\"min\",\"max\",\"median\",\"first\",\"last\"]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Upsample"
   ]
  },
  {
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"11\" halign=\"left\">VALUE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>backfill</th>\n",
       "      <th>bfill</th>\n",
       "      <th>pad</th>\n",
       "      <th>ffill</th>\n",
       "      <th>nearest</th>\n",
       "      <th>first</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CAT</th>\n",
       "      <th>DATE</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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"7\" valign=\"top\">abc</th>\n",
       "      <th>1900-01-01</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-02</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-03</th>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-04</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-05</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>15</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-06</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>15</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-07</th>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>15.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               VALUE                                                           \\\n",
       "                 sum count  mean   min   max backfill bfill pad ffill nearest   \n",
       "CAT DATE                                                                        \n",
       "abc 1900-01-01    10     1  10.0  10.0  10.0       10    10  10    10      10   \n",
       "    1900-01-02     0     0   NaN   NaN   NaN       20    20  10    10      20   \n",
       "    1900-01-03    20     1  20.0  20.0  20.0       20    20  20    20      20   \n",
       "    1900-01-04     0     0   NaN   NaN   NaN       15    15  20    20      20   \n",
       "    1900-01-05     0     0   NaN   NaN   NaN       15    15  20    20      15   \n",
       "    1900-01-06     0     0   NaN   NaN   NaN       15    15  20    20      15   \n",
       "    1900-01-07    15     1  15.0  15.0  15.0       15    15  15    15      15   \n",
       "\n",
       "                      \n",
       "               first  \n",
       "CAT DATE              \n",
       "abc 1900-01-01  10.0  \n",
       "    1900-01-02   NaN  \n",
       "    1900-01-03  20.0  \n",
       "    1900-01-04   NaN  \n",
       "    1900-01-05   NaN  \n",
       "    1900-01-06   NaN  \n",
       "    1900-01-07  15.0  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_index(\"DATE\").groupby(\"CAT\").resample(\"D\").agg({\"VALUE\":[\"sum\",\"count\",\"mean\",\"min\",\"max\",\"backfill\",\"bfill\",\"pad\",\"ffill\",\"nearest\",\"first\"]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Averaga `mean` didn't really calculate the mean, only filled in the gaps with the `NaN`. To find out the average, you have to use `.group` attribute of the `resample` method and divide the measured value by it. It's more tricky with multiple categories, so let's start with one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=\"\"\"\n",
    "CAT|DATE|VALUE\n",
    "abc|0101|10\n",
    "abc|0103|20\n",
    "abc|0106|15\"\"\"\n",
    "df = pd.read_csv(io.StringIO(data), skiprows=1, sep=\"|\", dtype={\"DATE\":\"str\"})\n",
    "df[\"DATE\"] = pd.to_datetime(df[\"DATE\"], format=\"%m%d\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "expected=\"\"\"\n",
    "CAT|DATE|VALUE\n",
    "abc|0102|10\n",
    "abc|0103|10\n",
    "abc|0104|5\n",
    "abc|0105|5\n",
    "abc|0106|5\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The resample method has two attributes:\n",
    "    \n",
    "* `indices` - showing where the input data appeared\n",
    "* `groups` - indexing each datatime value with its group index.\n",
    "\n",
    "https://pandas.pydata.org/pandas-docs/stable/reference/resampling.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "defaultdict(list,\n",
       "            {Timestamp('1900-01-01 00:00:00', freq='D'): [0],\n",
       "             Timestamp('1900-01-03 00:00:00', freq='D'): [1],\n",
       "             Timestamp('1900-01-06 00:00:00', freq='D'): [2]})"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the data came on first, third and sixth of Jan\n",
    "df.set_index(\"DATE\")\\\n",
    ".resample(\"D\").indices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{Timestamp('1900-01-02 00:00:00', freq='D'): 1,\n",
       " Timestamp('1900-01-03 00:00:00', freq='D'): 1,\n",
       " Timestamp('1900-01-04 00:00:00', freq='D'): 2,\n",
       " Timestamp('1900-01-05 00:00:00', freq='D'): 2,\n",
       " Timestamp('1900-01-06 00:00:00', freq='D'): 2,\n",
       " Timestamp('1900-01-07 00:00:00', freq='D'): 3}"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the groups are \n",
    "\"\"\" \n",
    "    Jan-1 Group 1\n",
    "    Jan-2 Group 1\n",
    "    Jan-3 Group 2\n",
    "    Jan-4 Group 2\n",
    "    Jan-5 Group 2\n",
    "    Jan-6 Group 3\n",
    "\"\"\"\n",
    "df.set_index(\"DATE\")\\\n",
    ".resample(\"D\", label=\"right\").groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In reality our data from Jan-1 cover unknown period of time, the value from Jan-3 covers the third and second and the last value covers fourth, fifth and sixth of Jan. We can turn the dictionary returned by the `.groups` parameter into a dataframe and shift the group ids by 1 (row) so that data match our scenario."
   ]
  },
  {
   "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>group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1900-01-02</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-03</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-04</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-05</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-06</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-07</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            group\n",
       "1900-01-02      1\n",
       "1900-01-03      1\n",
       "1900-01-04      2\n",
       "1900-01-05      2\n",
       "1900-01-06      2\n",
       "1900-01-07      3"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate the number of hours in each group\n",
    "resampled_groups = df.set_index(\"DATE\").resample(\"D\", label=\"right\").groups\n",
    "# alternatively shift the left labels by 1\n",
    "#df_groups = pd.DataFrame(df.set_index(\"DATE\").resample(\"D\").groups, index=[\"group\"]).T.shift(1)\n",
    "df_groups = pd.DataFrame(resampled_groups, index=[\"group\"]).T\n",
    "df_groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Knowing which dates belong to each group, let us calculate the number of occurences (days) between each measurement. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "group\n",
       "1    2\n",
       "2    3\n",
       "3    1\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = df_groups.groupby(\"group\").size()\n",
    "s.name = \"count\"\n",
    "s"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By joining the counts back to the groups we will see how many days were covered each date so that we can calculate the average - measured value at the end of the period/period lenght.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1900-01-02    2\n",
       "1900-01-03    2\n",
       "1900-01-04    3\n",
       "1900-01-05    3\n",
       "1900-01-06    3\n",
       "1900-01-07    1\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s_counts = df_groups.join(s, on=\"group\")[\"count\"]\n",
    "s_counts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mering this back to the original dataframe, with backfilled sum allow us to calculate the average. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>CAT</th>\n",
       "      <th>VALUE</th>\n",
       "      <th>count</th>\n",
       "      <th>average</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DATE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1900-01-01</th>\n",
       "      <td>abc</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-02</th>\n",
       "      <td>abc</td>\n",
       "      <td>20</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-03</th>\n",
       "      <td>abc</td>\n",
       "      <td>20</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-04</th>\n",
       "      <td>abc</td>\n",
       "      <td>15</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-05</th>\n",
       "      <td>abc</td>\n",
       "      <td>15</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-06</th>\n",
       "      <td>abc</td>\n",
       "      <td>15</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CAT  VALUE  count  average\n",
       "DATE                                  \n",
       "1900-01-01  abc     10    NaN      NaN\n",
       "1900-01-02  abc     20    2.0     10.0\n",
       "1900-01-03  abc     20    2.0     10.0\n",
       "1900-01-04  abc     15    3.0      5.0\n",
       "1900-01-05  abc     15    3.0      5.0\n",
       "1900-01-06  abc     15    3.0      5.0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res = df.set_index(\"DATE\").resample(\"D\").bfill()\n",
    "res = res.join(s_counts)\n",
    "res[\"average\"] = res[\"VALUE\"]/res[\"count\"]\n",
    "res"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Voila, we have the values split by average to each day. You might say \"hurray\", but there's one more pitfall. When you have multiple categories, the `.groups` attribute group all of the together, and doesn't create separate groups per category."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=\"\"\"\n",
    "CAT|DATE|VALUE\n",
    "abc|0101|10\n",
    "abc|0103|20\n",
    "abc|0106|15\n",
    "efg|0101|10\n",
    "efg|0105|40\n",
    "efg|0106|12\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "defaultdict(list,\n",
       "            {Timestamp('1900-01-01 00:00:00', freq='D'): [0, 1],\n",
       "             Timestamp('1900-01-03 00:00:00', freq='D'): [2],\n",
       "             Timestamp('1900-01-05 00:00:00', freq='D'): [3],\n",
       "             Timestamp('1900-01-06 00:00:00', freq='D'): [4, 5]})"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(io.StringIO(data), skiprows=1, sep=\"|\", dtype={\"DATE\":\"str\"})\n",
    "df[\"DATE\"] = pd.to_datetime(df[\"DATE\"], format=\"%m%d\")\n",
    "df.set_index(\"DATE\").groupby(\"CAT\").resample(\"D\").indices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{Timestamp('1900-01-01 00:00:00', freq='D'): 2,\n",
       " Timestamp('1900-01-02 00:00:00', freq='D'): 2,\n",
       " Timestamp('1900-01-03 00:00:00', freq='D'): 3,\n",
       " Timestamp('1900-01-04 00:00:00', freq='D'): 3,\n",
       " Timestamp('1900-01-05 00:00:00', freq='D'): 4,\n",
       " Timestamp('1900-01-06 00:00:00', freq='D'): 6}"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_index(\"DATE\").groupby(\"CAT\").resample(\"D\").groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For this reason you have to to the average calculation separatelly for each group."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Putting it all together"
   ]
  },
  {
   "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>CAT</th>\n",
       "      <th>VALUE</th>\n",
       "      <th>count</th>\n",
       "      <th>daily_average</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DATE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1900-01-02</th>\n",
       "      <td>abc</td>\n",
       "      <td>20</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-03</th>\n",
       "      <td>abc</td>\n",
       "      <td>20</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-04</th>\n",
       "      <td>abc</td>\n",
       "      <td>15</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-05</th>\n",
       "      <td>abc</td>\n",
       "      <td>15</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-06</th>\n",
       "      <td>abc</td>\n",
       "      <td>15</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-02</th>\n",
       "      <td>efg</td>\n",
       "      <td>40</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-03</th>\n",
       "      <td>efg</td>\n",
       "      <td>40</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-04</th>\n",
       "      <td>efg</td>\n",
       "      <td>40</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-05</th>\n",
       "      <td>efg</td>\n",
       "      <td>40</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900-01-06</th>\n",
       "      <td>efg</td>\n",
       "      <td>12</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CAT  VALUE  count  daily_average\n",
       "DATE                                        \n",
       "1900-01-02  abc     20    2.0           10.0\n",
       "1900-01-03  abc     20    2.0           10.0\n",
       "1900-01-04  abc     15    3.0            5.0\n",
       "1900-01-05  abc     15    3.0            5.0\n",
       "1900-01-06  abc     15    3.0            5.0\n",
       "1900-01-02  efg     40    4.0           10.0\n",
       "1900-01-03  efg     40    4.0           10.0\n",
       "1900-01-04  efg     40    4.0           10.0\n",
       "1900-01-05  efg     40    4.0           10.0\n",
       "1900-01-06  efg     12    1.0           12.0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data=\"\"\"\n",
    "CAT|DATE|VALUE\n",
    "abc|0101|10\n",
    "abc|0103|20\n",
    "abc|0106|15\n",
    "efg|0101|10\n",
    "efg|0105|40\n",
    "efg|0106|12\"\"\"\n",
    "df = pd.read_csv(io.StringIO(data), skiprows=1, sep=\"|\", dtype={\"DATE\":\"str\"})\n",
    "df[\"DATE\"] = pd.to_datetime(df[\"DATE\"], format=\"%m%d\")\n",
    "\n",
    "updated_df = []\n",
    "for gr in df[\"CAT\"].unique():\n",
    "    \n",
    "    subdf = df[df[\"CAT\"]==gr].set_index(\"DATE\").resample(\"1D\", label=\"right\")\n",
    "    \n",
    "    # back fill the data\n",
    "    res = subdf.bfill()\n",
    "    \n",
    "    # Calculate the number of hours in each group\n",
    "    resampled_groups = subdf.groups\n",
    "    df_groups = pd.DataFrame(resampled_groups, index=[\"group\"]).T\n",
    "    df_groups\n",
    "    \n",
    "    s = df_groups.groupby(\"group\").size()\n",
    "    s.name = \"count\"\n",
    "    s_counts = df_groups.join(s, on=\"group\")[\"count\"]\n",
    "    \n",
    "    res = res.join(s_counts)\n",
    "    res[\"daily_average\"] = res[\"VALUE\"]/res[\"count\"]\n",
    "    \n",
    "    # add to list\n",
    "    updated_df.append(res)\n",
    "    \n",
    "pd.concat(updated_df).dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the end, I've dropped all the empty values, because for the first day (the first measurement) we don't know for how long period it's measured."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You have smoothened your data to daily average, but maybe you don't want to feed the model with daily data. You just needed to cover the different times when the inputs are comming, but now want to input only weekly or quarterly data. That is practically useful in case of stock data, when most of the companies report in September while few does in October or November and some really unfriendly ones does it only every sixth months. In that case you can upsample to average monthly values and then caluclate the rolling average for each quarter and feed your model with lower amount of data. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's explore on this short sample of company data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=\"\"\"\n",
    "COM|DATE    |VALUE\n",
    "abc|20200131|100\n",
    "abc|20200430|80\n",
    "abc|20200731|125\n",
    "abc|20201031|110\n",
    "efg|20200331|25\n",
    "efg|20200930|75\n",
    "ijk|20200331|15\n",
    "ijk|20200630|45\n",
    "ijk|20200930|50\"\"\"\n",
    "\n",
    "# read the data into dataframe\n",
    "df = pd.read_csv(io.StringIO(data), skiprows=1, sep=\"|\", dtype={\"DATE    \":\"str\"}).rename(columns={\"DATE    \":\"DATE\"})\n",
    "df[\"DATE\"] = pd.to_datetime(df[\"DATE\"], format=\"%Y%m%d\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>COM</th>\n",
       "      <th>VALUE</th>\n",
       "      <th>count</th>\n",
       "      <th>monthly_average</th>\n",
       "      <th>3months</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DATE</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>2020-02-29</th>\n",
       "      <td>abc</td>\n",
       "      <td>80</td>\n",
       "      <td>3.0</td>\n",
       "      <td>26.666667</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-31</th>\n",
       "      <td>abc</td>\n",
       "      <td>80</td>\n",
       "      <td>3.0</td>\n",
       "      <td>26.666667</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-30</th>\n",
       "      <td>abc</td>\n",
       "      <td>80</td>\n",
       "      <td>3.0</td>\n",
       "      <td>26.666667</td>\n",
       "      <td>80.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-31</th>\n",
       "      <td>abc</td>\n",
       "      <td>125</td>\n",
       "      <td>3.0</td>\n",
       "      <td>41.666667</td>\n",
       "      <td>95.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-30</th>\n",
       "      <td>abc</td>\n",
       "      <td>125</td>\n",
       "      <td>3.0</td>\n",
       "      <td>41.666667</td>\n",
       "      <td>110.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-07-31</th>\n",
       "      <td>abc</td>\n",
       "      <td>125</td>\n",
       "      <td>3.0</td>\n",
       "      <td>41.666667</td>\n",
       "      <td>125.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-08-31</th>\n",
       "      <td>abc</td>\n",
       "      <td>110</td>\n",
       "      <td>3.0</td>\n",
       "      <td>36.666667</td>\n",
       "      <td>120.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-09-30</th>\n",
       "      <td>abc</td>\n",
       "      <td>110</td>\n",
       "      <td>3.0</td>\n",
       "      <td>36.666667</td>\n",
       "      <td>115.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-31</th>\n",
       "      <td>abc</td>\n",
       "      <td>110</td>\n",
       "      <td>3.0</td>\n",
       "      <td>36.666667</td>\n",
       "      <td>110.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-30</th>\n",
       "      <td>efg</td>\n",
       "      <td>75</td>\n",
       "      <td>6.0</td>\n",
       "      <td>12.500000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-31</th>\n",
       "      <td>efg</td>\n",
       "      <td>75</td>\n",
       "      <td>6.0</td>\n",
       "      <td>12.500000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-30</th>\n",
       "      <td>efg</td>\n",
       "      <td>75</td>\n",
       "      <td>6.0</td>\n",
       "      <td>12.500000</td>\n",
       "      <td>37.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-07-31</th>\n",
       "      <td>efg</td>\n",
       "      <td>75</td>\n",
       "      <td>6.0</td>\n",
       "      <td>12.500000</td>\n",
       "      <td>37.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-08-31</th>\n",
       "      <td>efg</td>\n",
       "      <td>75</td>\n",
       "      <td>6.0</td>\n",
       "      <td>12.500000</td>\n",
       "      <td>37.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-09-30</th>\n",
       "      <td>efg</td>\n",
       "      <td>75</td>\n",
       "      <td>6.0</td>\n",
       "      <td>12.500000</td>\n",
       "      <td>37.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-30</th>\n",
       "      <td>ijk</td>\n",
       "      <td>45</td>\n",
       "      <td>3.0</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-31</th>\n",
       "      <td>ijk</td>\n",
       "      <td>45</td>\n",
       "      <td>3.0</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-30</th>\n",
       "      <td>ijk</td>\n",
       "      <td>45</td>\n",
       "      <td>3.0</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>45.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-07-31</th>\n",
       "      <td>ijk</td>\n",
       "      <td>50</td>\n",
       "      <td>3.0</td>\n",
       "      <td>16.666667</td>\n",
       "      <td>46.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-08-31</th>\n",
       "      <td>ijk</td>\n",
       "      <td>50</td>\n",
       "      <td>3.0</td>\n",
       "      <td>16.666667</td>\n",
       "      <td>48.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-09-30</th>\n",
       "      <td>ijk</td>\n",
       "      <td>50</td>\n",
       "      <td>3.0</td>\n",
       "      <td>16.666667</td>\n",
       "      <td>50.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            COM  VALUE  count  monthly_average     3months\n",
       "DATE                                                      \n",
       "2020-02-29  abc     80    3.0        26.666667         NaN\n",
       "2020-03-31  abc     80    3.0        26.666667         NaN\n",
       "2020-04-30  abc     80    3.0        26.666667   80.000000\n",
       "2020-05-31  abc    125    3.0        41.666667   95.000000\n",
       "2020-06-30  abc    125    3.0        41.666667  110.000000\n",
       "2020-07-31  abc    125    3.0        41.666667  125.000000\n",
       "2020-08-31  abc    110    3.0        36.666667  120.000000\n",
       "2020-09-30  abc    110    3.0        36.666667  115.000000\n",
       "2020-10-31  abc    110    3.0        36.666667  110.000000\n",
       "2020-04-30  efg     75    6.0        12.500000         NaN\n",
       "2020-05-31  efg     75    6.0        12.500000         NaN\n",
       "2020-06-30  efg     75    6.0        12.500000   37.500000\n",
       "2020-07-31  efg     75    6.0        12.500000   37.500000\n",
       "2020-08-31  efg     75    6.0        12.500000   37.500000\n",
       "2020-09-30  efg     75    6.0        12.500000   37.500000\n",
       "2020-04-30  ijk     45    3.0        15.000000         NaN\n",
       "2020-05-31  ijk     45    3.0        15.000000         NaN\n",
       "2020-06-30  ijk     45    3.0        15.000000   45.000000\n",
       "2020-07-31  ijk     50    3.0        16.666667   46.666667\n",
       "2020-08-31  ijk     50    3.0        16.666667   48.333333\n",
       "2020-09-30  ijk     50    3.0        16.666667   50.000000"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "updated_df = []\n",
    "for gr in df[\"COM\"].unique():\n",
    "    \n",
    "    subdf = df[df[\"COM\"]==gr].set_index(\"DATE\").resample(\"M\",  label=\"right\", closed=\"left\")\n",
    "    \n",
    "    # back fill the data\n",
    "    res = subdf.bfill()\n",
    "    \n",
    "    # Calculate the number of hours in each group\n",
    "    resampled_groups = subdf.groups\n",
    "    df_groups = pd.DataFrame(resampled_groups, index=[\"group\"]).T\n",
    "    df_groups\n",
    "    \n",
    "    s = df_groups.groupby(\"group\").size()\n",
    "    s.name = \"count\"\n",
    "    s_counts = df_groups.join(s, on=\"group\")[\"count\"]\n",
    "    \n",
    "    res = res.join(s_counts)\n",
    "    res[\"monthly_average\"] = res[\"VALUE\"]/res[\"count\"]\n",
    "    \n",
    "    # add to list\n",
    "    updated_df.append(res)\n",
    "    \n",
    "#av_df = \n",
    "av_df = pd.concat(updated_df).dropna()#.reset_index().set_index(\"DATE\")\n",
    "av_df[\"3months\"] = av_df.groupby(\"COM\")[\"monthly_average\"].rolling(3).sum().values\n",
    "av_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>COM</th>\n",
       "      <th>VALUE</th>\n",
       "      <th>count</th>\n",
       "      <th>monthly_average</th>\n",
       "      <th>3months</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DATE</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>2020-06-30</th>\n",
       "      <td>abc</td>\n",
       "      <td>125</td>\n",
       "      <td>3.0</td>\n",
       "      <td>41.666667</td>\n",
       "      <td>110.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-30</th>\n",
       "      <td>efg</td>\n",
       "      <td>75</td>\n",
       "      <td>6.0</td>\n",
       "      <td>12.500000</td>\n",
       "      <td>37.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-30</th>\n",
       "      <td>ijk</td>\n",
       "      <td>45</td>\n",
       "      <td>3.0</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-09-30</th>\n",
       "      <td>abc</td>\n",
       "      <td>110</td>\n",
       "      <td>3.0</td>\n",
       "      <td>36.666667</td>\n",
       "      <td>115.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-09-30</th>\n",
       "      <td>efg</td>\n",
       "      <td>75</td>\n",
       "      <td>6.0</td>\n",
       "      <td>12.500000</td>\n",
       "      <td>37.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-09-30</th>\n",
       "      <td>ijk</td>\n",
       "      <td>50</td>\n",
       "      <td>3.0</td>\n",
       "      <td>16.666667</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            COM  VALUE  count  monthly_average  3months\n",
       "DATE                                                   \n",
       "2020-06-30  abc    125    3.0        41.666667    110.0\n",
       "2020-06-30  efg     75    6.0        12.500000     37.5\n",
       "2020-06-30  ijk     45    3.0        15.000000     45.0\n",
       "2020-09-30  abc    110    3.0        36.666667    115.0\n",
       "2020-09-30  efg     75    6.0        12.500000     37.5\n",
       "2020-09-30  ijk     50    3.0        16.666667     50.0"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# for your model you might want only the quartely data\n",
    "av_df.loc[[pd.to_datetime(\"2020-06-30\"), pd.to_datetime(\"2020-09-30\")]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>COM</th>\n",
       "      <th>abc</th>\n",
       "      <th>efg</th>\n",
       "      <th>ijk</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DATE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-06-30</th>\n",
       "      <td>110.0</td>\n",
       "      <td>37.5</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-09-30</th>\n",
       "      <td>115.0</td>\n",
       "      <td>37.5</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "COM           abc   efg   ijk\n",
       "DATE                         \n",
       "2020-06-30  110.0  37.5  45.0\n",
       "2020-09-30  115.0  37.5  50.0"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# for your model you might want only the quartely data\n",
    "av_df\\\n",
    ".loc[[pd.to_datetime(\"2020-06-30\"), pd.to_datetime(\"2020-09-30\")],[\"COM\",\"3months\"]]\\\n",
    ".pivot(columns=\"COM\" ,values=\"3months\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Why do you need to use label=\"right\", closed=\"left\" with \"M\"\n",
    "Daily and monthly resampling works a bit differently with the groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=\"\"\"\n",
    "COM|DATE    |VALUE\n",
    "efg|20200331|25\n",
    "efg|20200930|75\n",
    "efg|20201031|15\"\"\"\n",
    "\n",
    "# read the data into dataframe\n",
    "df = pd.read_csv(io.StringIO(data), skiprows=1, sep=\"|\", dtype={\"DATE    \":\"str\"}).rename(columns={\"DATE    \":\"DATE\"})\n",
    "df[\"DATE\"] = pd.to_datetime(df[\"DATE\"], format=\"%Y%m%d\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style  type=\"text/css\" >\n",
       "    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow0_col3 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow0_col5 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow1_col3 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow1_col5 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow2_col3 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow2_col5 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow3_col3 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow3_col5 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow4_col3 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow4_col5 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow5_col3 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow5_col5 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow6_col3 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow6_col5 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow7_col3 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow7_col5 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow8_col3 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow8_col5 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow9_col3 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow9_col5 {\n",
       "            background-color:  lightgreen;\n",
       "        }</style><table id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fd\" ><thead>    <tr>        <th class=\"blank level0\" ></th>        <th class=\"col_heading level0 col0\" >l:left_left</th>        <th class=\"col_heading level0 col1\" >l:left_right</th>        <th class=\"col_heading level0 col2\" >l:right_left</th>        <th class=\"col_heading level0 col3\" >l:right_right</th>        <th class=\"col_heading level0 col4\" >l:left</th>        <th class=\"col_heading level0 col5\" >l:right</th>    </tr></thead><tbody>\n",
       "                <tr>\n",
       "                        <th id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdlevel0_row0\" class=\"row_heading level0 row0\" >2020-02-29 00:00:00</th>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow0_col0\" class=\"data row0 col0\" >0</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow0_col1\" class=\"data row0 col1\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow0_col2\" class=\"data row0 col2\" >0</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow0_col3\" class=\"data row0 col3\" >0</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow0_col4\" class=\"data row0 col4\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow0_col5\" class=\"data row0 col5\" >0</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdlevel0_row1\" class=\"row_heading level0 row1\" >2020-03-31 00:00:00</th>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow1_col0\" class=\"data row1 col0\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow1_col1\" class=\"data row1 col1\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow1_col2\" class=\"data row1 col2\" >0</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow1_col3\" class=\"data row1 col3\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow1_col4\" class=\"data row1 col4\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow1_col5\" class=\"data row1 col5\" >1</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdlevel0_row2\" class=\"row_heading level0 row2\" >2020-04-30 00:00:00</th>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow2_col0\" class=\"data row2 col0\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow2_col1\" class=\"data row2 col1\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow2_col2\" class=\"data row2 col2\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow2_col3\" class=\"data row2 col3\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow2_col4\" class=\"data row2 col4\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow2_col5\" class=\"data row2 col5\" >1</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdlevel0_row3\" class=\"row_heading level0 row3\" >2020-05-31 00:00:00</th>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow3_col0\" class=\"data row3 col0\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow3_col1\" class=\"data row3 col1\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow3_col2\" class=\"data row3 col2\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow3_col3\" class=\"data row3 col3\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow3_col4\" class=\"data row3 col4\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow3_col5\" class=\"data row3 col5\" >1</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdlevel0_row4\" class=\"row_heading level0 row4\" >2020-06-30 00:00:00</th>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow4_col0\" class=\"data row4 col0\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow4_col1\" class=\"data row4 col1\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow4_col2\" class=\"data row4 col2\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow4_col3\" class=\"data row4 col3\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow4_col4\" class=\"data row4 col4\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow4_col5\" class=\"data row4 col5\" >1</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdlevel0_row5\" class=\"row_heading level0 row5\" >2020-07-31 00:00:00</th>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow5_col0\" class=\"data row5 col0\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow5_col1\" class=\"data row5 col1\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow5_col2\" class=\"data row5 col2\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow5_col3\" class=\"data row5 col3\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow5_col4\" class=\"data row5 col4\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow5_col5\" class=\"data row5 col5\" >1</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdlevel0_row6\" class=\"row_heading level0 row6\" >2020-08-31 00:00:00</th>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow6_col0\" class=\"data row6 col0\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow6_col1\" class=\"data row6 col1\" >2</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow6_col2\" class=\"data row6 col2\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow6_col3\" class=\"data row6 col3\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow6_col4\" class=\"data row6 col4\" >2</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow6_col5\" class=\"data row6 col5\" >1</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdlevel0_row7\" class=\"row_heading level0 row7\" >2020-09-30 00:00:00</th>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow7_col0\" class=\"data row7 col0\" >2</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow7_col1\" class=\"data row7 col1\" >3</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow7_col2\" class=\"data row7 col2\" >1</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow7_col3\" class=\"data row7 col3\" >2</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow7_col4\" class=\"data row7 col4\" >3</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow7_col5\" class=\"data row7 col5\" >2</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdlevel0_row8\" class=\"row_heading level0 row8\" >2020-10-31 00:00:00</th>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow8_col0\" class=\"data row8 col0\" >3</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow8_col1\" class=\"data row8 col1\" >0</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow8_col2\" class=\"data row8 col2\" >2</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow8_col3\" class=\"data row8 col3\" >3</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow8_col4\" class=\"data row8 col4\" >0</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow8_col5\" class=\"data row8 col5\" >3</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdlevel0_row9\" class=\"row_heading level0 row9\" >2020-11-30 00:00:00</th>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow9_col0\" class=\"data row9 col0\" >0</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow9_col1\" class=\"data row9 col1\" >0</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow9_col2\" class=\"data row9 col2\" >3</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow9_col3\" class=\"data row9 col3\" >0</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow9_col4\" class=\"data row9 col4\" >0</td>\n",
       "                        <td id=\"T_095950d8_05b9_11eb_aa4a_af65bbfbe9fdrow9_col5\" class=\"data row9 col5\" >0</td>\n",
       "            </tr>\n",
       "    </tbody></table>"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x7f2960b2cfd0>"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups = []\n",
    "for label in [\"left\",\"right\"]:\n",
    "    for closed in [\"left\",\"right\"]:\n",
    "        resampled_groups = df.set_index(\"DATE\").resample(\"M\", label=label, closed=closed).groups\n",
    "        groups.append(pd.DataFrame(resampled_groups, index=[\"l:\"+label+\"_\"+closed]).T)\n",
    "for label in [\"left\",\"right\"]:\n",
    "    resampled_groups = df.set_index(\"DATE\").resample(\"M\", label=label).groups\n",
    "    groups.append(pd.DataFrame(resampled_groups, index=[\"l:\"+label],dtype=\"int\").T)\n",
    "out = pd.concat(groups, axis=1).fillna(\"0\").astype(\"int\")\n",
    "\n",
    "def highlight_cols(s):\n",
    "    return 'background-color: lightgreen'\n",
    "\n",
    "out.style.applymap(highlight_cols, subset=pd.IndexSlice[:,[\"l:right_right\",\"l:right\"]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=\"\"\"\n",
    "CAT|DATE|VALUE\n",
    "abc|20200101|10\n",
    "abc|20200103|20\n",
    "abc|20200106|15\"\"\"\n",
    "df = pd.read_csv(io.StringIO(data), skiprows=1, sep=\"|\", dtype={\"DATE\":\"str\"})\n",
    "df[\"DATE\"] = pd.to_datetime(df[\"DATE\"], format=\"%Y%m%d\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style  type=\"text/css\" >\n",
       "    #T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow0_col2 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow0_col5 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow1_col2 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow1_col5 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow2_col2 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow2_col5 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow3_col2 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow3_col5 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow4_col2 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow4_col5 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow5_col2 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow5_col5 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow6_col2 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow6_col5 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow7_col2 {\n",
       "            background-color:  lightgreen;\n",
       "        }    #T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow7_col5 {\n",
       "            background-color:  lightgreen;\n",
       "        }</style><table id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fd\" ><thead>    <tr>        <th class=\"blank level0\" ></th>        <th class=\"col_heading level0 col0\" >l:left_c:left</th>        <th class=\"col_heading level0 col1\" >l:left_c:right</th>        <th class=\"col_heading level0 col2\" >l:right_c:left</th>        <th class=\"col_heading level0 col3\" >l:right_c:right</th>        <th class=\"col_heading level0 col4\" >l:left</th>        <th class=\"col_heading level0 col5\" >l:right</th>    </tr></thead><tbody>\n",
       "                <tr>\n",
       "                        <th id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdlevel0_row0\" class=\"row_heading level0 row0\" >2019-12-31 00:00:00</th>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow0_col0\" class=\"data row0 col0\" >0</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow0_col1\" class=\"data row0 col1\" >1</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow0_col2\" class=\"data row0 col2\" >0</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow0_col3\" class=\"data row0 col3\" >0</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow0_col4\" class=\"data row0 col4\" >0</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow0_col5\" class=\"data row0 col5\" >0</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdlevel0_row1\" class=\"row_heading level0 row1\" >2020-01-01 00:00:00</th>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow1_col0\" class=\"data row1 col0\" >1</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow1_col1\" class=\"data row1 col1\" >1</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow1_col2\" class=\"data row1 col2\" >0</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow1_col3\" class=\"data row1 col3\" >1</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow1_col4\" class=\"data row1 col4\" >1</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow1_col5\" class=\"data row1 col5\" >0</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdlevel0_row2\" class=\"row_heading level0 row2\" >2020-01-02 00:00:00</th>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow2_col0\" class=\"data row2 col0\" >1</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow2_col1\" class=\"data row2 col1\" >2</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow2_col2\" class=\"data row2 col2\" >1</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow2_col3\" class=\"data row2 col3\" >1</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow2_col4\" class=\"data row2 col4\" >1</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow2_col5\" class=\"data row2 col5\" >1</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdlevel0_row3\" class=\"row_heading level0 row3\" >2020-01-03 00:00:00</th>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow3_col0\" class=\"data row3 col0\" >2</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow3_col1\" class=\"data row3 col1\" >2</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow3_col2\" class=\"data row3 col2\" >1</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow3_col3\" class=\"data row3 col3\" >2</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow3_col4\" class=\"data row3 col4\" >2</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow3_col5\" class=\"data row3 col5\" >1</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdlevel0_row4\" class=\"row_heading level0 row4\" >2020-01-04 00:00:00</th>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow4_col0\" class=\"data row4 col0\" >2</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow4_col1\" class=\"data row4 col1\" >2</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow4_col2\" class=\"data row4 col2\" >2</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow4_col3\" class=\"data row4 col3\" >2</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow4_col4\" class=\"data row4 col4\" >2</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow4_col5\" class=\"data row4 col5\" >2</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdlevel0_row5\" class=\"row_heading level0 row5\" >2020-01-05 00:00:00</th>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow5_col0\" class=\"data row5 col0\" >2</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow5_col1\" class=\"data row5 col1\" >3</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow5_col2\" class=\"data row5 col2\" >2</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow5_col3\" class=\"data row5 col3\" >2</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow5_col4\" class=\"data row5 col4\" >2</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow5_col5\" class=\"data row5 col5\" >2</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdlevel0_row6\" class=\"row_heading level0 row6\" >2020-01-06 00:00:00</th>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow6_col0\" class=\"data row6 col0\" >3</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow6_col1\" class=\"data row6 col1\" >0</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow6_col2\" class=\"data row6 col2\" >2</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow6_col3\" class=\"data row6 col3\" >3</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow6_col4\" class=\"data row6 col4\" >3</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow6_col5\" class=\"data row6 col5\" >2</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdlevel0_row7\" class=\"row_heading level0 row7\" >2020-01-07 00:00:00</th>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow7_col0\" class=\"data row7 col0\" >0</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow7_col1\" class=\"data row7 col1\" >0</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow7_col2\" class=\"data row7 col2\" >3</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow7_col3\" class=\"data row7 col3\" >0</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow7_col4\" class=\"data row7 col4\" >0</td>\n",
       "                        <td id=\"T_0966f7b0_05b9_11eb_aa4a_af65bbfbe9fdrow7_col5\" class=\"data row7 col5\" >3</td>\n",
       "            </tr>\n",
       "    </tbody></table>"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x7f295ea2e0a0>"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups = []\n",
    "for label in [\"left\",\"right\"]:\n",
    "    for closed in [\"left\",\"right\"]:\n",
    "        resampled_groups = df.set_index(\"DATE\").resample(\"D\", label=label, closed=closed).groups\n",
    "        groups.append(pd.DataFrame(resampled_groups, index=[\"l:\"+label+\"_c:\"+closed]).T)\n",
    "\n",
    "for label in [\"left\",\"right\"]:\n",
    "    resampled_groups = df.set_index(\"DATE\").resample(\"D\", label=label).groups\n",
    "    groups.append(pd.DataFrame(resampled_groups, index=[\"l:\"+label]).T)\n",
    "        \n",
    "out = pd.concat(groups, axis=1).fillna(\"0\").astype(\"int\")\n",
    "\n",
    "def highlight_cols(s):\n",
    "    return 'background-color: lightgreen'\n",
    "\n",
    "out.style.applymap(highlight_cols, subset=[\"l:right_c:left\",\"l:right\"])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}