Original notebook of the post group-by-date-as-column.md

group-by-date-as-column.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Assume you have data set as follows:\n",
    "\n",
    "ID | Date | Value\n",
    "---|------|------\n",
    "x   |  x    |x\n",
    "\n",
    "where each row contains an ID, a date (given as `pd.Datetime`) and a value.\n",
    "The objective is to count how many rows occur in each day."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime\n",
    "import random"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def random_date(start, end):\n",
    "    \"\"\"Generate a random datetime between `start` and `end`\n",
    "    \n",
    "    Thanks to https://stackoverflow.com/a/8170651/671013\n",
    "    \"\"\"\n",
    "    return start + datetime.timedelta(\n",
    "        # Get a random amount of seconds between `start` and `end`\n",
    "        seconds=random.randint(0, int((end - start).total_seconds())),\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's generate a random data set:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "N = 100\n",
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"date\": [random_date(datetime.datetime(2017,5,1), datetime.datetime(2017,7,1)) for x in range(N)],\n",
    "        \"val\": np.random.choice([0,1], size=N)\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Magic:**\n",
    "\n",
    "(Based on [this answer](https://stackoverflow.com/a/11397052/671013))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date  date\n",
       "5     1       4\n",
       "      2       2\n",
       "      3       2\n",
       "      4       1\n",
       "      5       4\n",
       "      6       2\n",
       "      7       1\n",
       "      9       1\n",
       "      10      2\n",
       "      11      1\n",
       "      12      1\n",
       "      13      2\n",
       "      14      2\n",
       "      15      1\n",
       "      16      1\n",
       "      17      4\n",
       "      19      4\n",
       "      20      2\n",
       "      21      1\n",
       "      22      1\n",
       "      24      1\n",
       "      25      1\n",
       "      26      3\n",
       "      27      1\n",
       "      28      2\n",
       "      29      1\n",
       "      31      1\n",
       "6     1       2\n",
       "      2       2\n",
       "      3       2\n",
       "      4       3\n",
       "      5       2\n",
       "      6       1\n",
       "      7       1\n",
       "      9       4\n",
       "      10      2\n",
       "      11      1\n",
       "      12      1\n",
       "      13      1\n",
       "      15      1\n",
       "      16      2\n",
       "      18      1\n",
       "      19      5\n",
       "      20      3\n",
       "      21      2\n",
       "      23      3\n",
       "      24      3\n",
       "      26      2\n",
       "      27      3\n",
       "      29      1\n",
       "      30      3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(\n",
    "    [df['date'].map(lambda x: x.month), \n",
    "     df['date'].map(lambda x: x.day)]\n",
    ").size()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}