notebooks/Read in semi-structured data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This data was generated in the Git repository\n",
    "\n",
    "`JavaOnAutobahn/spring-petclinic`\n",
    "\n",
    "with\n",
    "\n",
    "`git log --stat > git_log_stat.log`.\n",
    "\n",
    "This exports the history of the Git repository including some information about the file changes per commit.\n",
    "\n",
    "Here is an excerpt form this created dataset:\n",
    "\n",
    "```\n",
    "commit 4d3d9de655faa813781027d8b1baed819c6a56fe\n",
    "Author: Markus Harrer <feststelltaste@googlemail.com>\n",
    "Date:   Tue Mar 5 22:32:20 2019 +0100\n",
    "\n",
    "    add virtual bounded contexts\n",
    "\n",
    "20\t1\tjqassistant/business.adoc\n",
    "```\n",
    "\n",
    "It doesn't contain also any tabular structured data but more a row-based style of data (hint: if you want this, you can use Git's `--format` options to create such things).\n",
    "\n",
    "The question is: Can we get this kind of data into a pandas DataFrame?\n",
    "\n",
    "Warning: Please just read on if you can stand all the brain pain that follows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>raw</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>commit 4d3d9de655faa813781027d8b1baed819c6a56fe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Author: Markus Harrer &lt;feststelltaste@googlema...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Date:   Tue Mar 5 22:32:20 2019 +0100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>add virtual bounded contexts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>test\\ttest\\t</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 raw\n",
       "0    commit 4d3d9de655faa813781027d8b1baed819c6a56fe\n",
       "1  Author: Markus Harrer <feststelltaste@googlema...\n",
       "2              Date:   Tue Mar 5 22:32:20 2019 +0100\n",
       "3                       add virtual bounded contexts\n",
       "4                                       test\\ttest\\t"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "log = pd.read_csv(\n",
    "    \"../../joa_spring-petclinic/git_log_numstat.log\",\n",
    "    sep=\"\\n\",\n",
    "    names=['raw'])\n",
    "log.head()"
   ]
  },
  {
   "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>raw</th>\n",
       "      <th>sha</th>\n",
       "      <th>author</th>\n",
       "      <th>timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>commit 4d3d9de655faa813781027d8b1baed819c6a56fe</td>\n",
       "      <td>4d3d9de655faa813781027d8b1baed819c6a56fe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Author: Markus Harrer &lt;feststelltaste@googlema...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Markus Harrer &lt;feststelltaste@googlemail.com&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Date:   Tue Mar 5 22:32:20 2019 +0100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tue Mar 5 22:32:20 2019 +0100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>add virtual bounded contexts</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>test\\ttest\\t</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 raw  \\\n",
       "0    commit 4d3d9de655faa813781027d8b1baed819c6a56fe   \n",
       "1  Author: Markus Harrer <feststelltaste@googlema...   \n",
       "2              Date:   Tue Mar 5 22:32:20 2019 +0100   \n",
       "3                       add virtual bounded contexts   \n",
       "4                                       test\\ttest\\t   \n",
       "\n",
       "                                        sha  \\\n",
       "0  4d3d9de655faa813781027d8b1baed819c6a56fe   \n",
       "1                                       NaN   \n",
       "2                                       NaN   \n",
       "3                                       NaN   \n",
       "4                                       NaN   \n",
       "\n",
       "                                          author  \\\n",
       "0                                            NaN   \n",
       "1  Markus Harrer <feststelltaste@googlemail.com>   \n",
       "2                                            NaN   \n",
       "3                                            NaN   \n",
       "4                                            NaN   \n",
       "\n",
       "                         timestamp  \n",
       "0                              NaN  \n",
       "1                              NaN  \n",
       "2    Tue Mar 5 22:32:20 2019 +0100  \n",
       "3                              NaN  \n",
       "4                              NaN  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "log['sha'] = log.loc[log['raw'].str.startswith(\"commit \")]['raw'].str.split(\"commit \").str[1]\n",
    "log['author'] = log.loc[log['raw'].str.startswith(\"Author: \")]['raw'].str.split(\"Author: \").str[1]\n",
    "log['timestamp'] = log.loc[log['raw'].str.startswith(\"Date: \")]['raw'].str.split(\"Date: \").str[1]\n",
    "log.head()"
   ]
  },
  {
   "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>raw</th>\n",
       "      <th>sha</th>\n",
       "      <th>author</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>commit 4d3d9de655faa813781027d8b1baed819c6a56fe</td>\n",
       "      <td>4d3d9de655faa813781027d8b1baed819c6a56fe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Author: Markus Harrer &lt;feststelltaste@googlema...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Markus Harrer &lt;feststelltaste@googlemail.com&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Date:   Tue Mar 5 22:32:20 2019 +0100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tue Mar 5 22:32:20 2019 +0100</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>add virtual bounded contexts</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>add virtual bounded contexts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>test\\ttest\\t</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>test\\ttest\\t</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 raw  \\\n",
       "0    commit 4d3d9de655faa813781027d8b1baed819c6a56fe   \n",
       "1  Author: Markus Harrer <feststelltaste@googlema...   \n",
       "2              Date:   Tue Mar 5 22:32:20 2019 +0100   \n",
       "3                       add virtual bounded contexts   \n",
       "4                                       test\\ttest\\t   \n",
       "\n",
       "                                        sha  \\\n",
       "0  4d3d9de655faa813781027d8b1baed819c6a56fe   \n",
       "1                                       NaN   \n",
       "2                                       NaN   \n",
       "3                                       NaN   \n",
       "4                                       NaN   \n",
       "\n",
       "                                          author  \\\n",
       "0                                            NaN   \n",
       "1  Markus Harrer <feststelltaste@googlemail.com>   \n",
       "2                                            NaN   \n",
       "3                                            NaN   \n",
       "4                                            NaN   \n",
       "\n",
       "                         timestamp                       message  \n",
       "0                              NaN                           NaN  \n",
       "1                              NaN                           NaN  \n",
       "2    Tue Mar 5 22:32:20 2019 +0100                           NaN  \n",
       "3                              NaN  add virtual bounded contexts  \n",
       "4                              NaN                  test\\ttest\\t  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "log['message'] = log.loc[log['raw'].str.startswith(\" \"*4)]['raw'].str[4:]\n",
    "log.head()  "
   ]
  },
  {
   "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>raw</th>\n",
       "      <th>sha</th>\n",
       "      <th>author</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>message</th>\n",
       "      <th>no_entry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>commit 4d3d9de655faa813781027d8b1baed819c6a56fe</td>\n",
       "      <td>4d3d9de655faa813781027d8b1baed819c6a56fe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Author: Markus Harrer &lt;feststelltaste@googlema...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Markus Harrer &lt;feststelltaste@googlemail.com&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Date:   Tue Mar 5 22:32:20 2019 +0100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tue Mar 5 22:32:20 2019 +0100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>add virtual bounded contexts</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>add virtual bounded contexts</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>test\\ttest\\t</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>test\\ttest\\t</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 raw  \\\n",
       "0    commit 4d3d9de655faa813781027d8b1baed819c6a56fe   \n",
       "1  Author: Markus Harrer <feststelltaste@googlema...   \n",
       "2              Date:   Tue Mar 5 22:32:20 2019 +0100   \n",
       "3                       add virtual bounded contexts   \n",
       "4                                       test\\ttest\\t   \n",
       "\n",
       "                                        sha  \\\n",
       "0  4d3d9de655faa813781027d8b1baed819c6a56fe   \n",
       "1                                       NaN   \n",
       "2                                       NaN   \n",
       "3                                       NaN   \n",
       "4                                       NaN   \n",
       "\n",
       "                                          author  \\\n",
       "0                                            NaN   \n",
       "1  Markus Harrer <feststelltaste@googlemail.com>   \n",
       "2                                            NaN   \n",
       "3                                            NaN   \n",
       "4                                            NaN   \n",
       "\n",
       "                         timestamp                       message  no_entry  \n",
       "0                              NaN                           NaN     False  \n",
       "1                              NaN                           NaN     False  \n",
       "2    Tue Mar 5 22:32:20 2019 +0100                           NaN     False  \n",
       "3                              NaN  add virtual bounded contexts     False  \n",
       "4                              NaN                  test\\ttest\\t     False  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "log['no_entry'] = \\\n",
    "    log['sha'].isna() & \\\n",
    "    log['author'].isna() & \\\n",
    "    log['timestamp'].isna() & \\\n",
    "    log['message'].isna()\n",
    "log.head()"
   ]
  },
  {
   "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>raw</th>\n",
       "      <th>sha</th>\n",
       "      <th>author</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>message</th>\n",
       "      <th>no_entry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>commit 4d3d9de655faa813781027d8b1baed819c6a56fe</td>\n",
       "      <td>4d3d9de655faa813781027d8b1baed819c6a56fe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Author: Markus Harrer &lt;feststelltaste@googlema...</td>\n",
       "      <td>4d3d9de655faa813781027d8b1baed819c6a56fe</td>\n",
       "      <td>Markus Harrer &lt;feststelltaste@googlemail.com&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Date:   Tue Mar 5 22:32:20 2019 +0100</td>\n",
       "      <td>4d3d9de655faa813781027d8b1baed819c6a56fe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tue Mar 5 22:32:20 2019 +0100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>add virtual bounded contexts</td>\n",
       "      <td>4d3d9de655faa813781027d8b1baed819c6a56fe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>add virtual bounded contexts</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>test\\ttest\\t</td>\n",
       "      <td>4d3d9de655faa813781027d8b1baed819c6a56fe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>test\\ttest\\t</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 raw  \\\n",
       "0    commit 4d3d9de655faa813781027d8b1baed819c6a56fe   \n",
       "1  Author: Markus Harrer <feststelltaste@googlema...   \n",
       "2              Date:   Tue Mar 5 22:32:20 2019 +0100   \n",
       "3                       add virtual bounded contexts   \n",
       "4                                       test\\ttest\\t   \n",
       "\n",
       "                                        sha  \\\n",
       "0  4d3d9de655faa813781027d8b1baed819c6a56fe   \n",
       "1  4d3d9de655faa813781027d8b1baed819c6a56fe   \n",
       "2  4d3d9de655faa813781027d8b1baed819c6a56fe   \n",
       "3  4d3d9de655faa813781027d8b1baed819c6a56fe   \n",
       "4  4d3d9de655faa813781027d8b1baed819c6a56fe   \n",
       "\n",
       "                                          author  \\\n",
       "0                                            NaN   \n",
       "1  Markus Harrer <feststelltaste@googlemail.com>   \n",
       "2                                            NaN   \n",
       "3                                            NaN   \n",
       "4                                            NaN   \n",
       "\n",
       "                         timestamp                       message  no_entry  \n",
       "0                              NaN                           NaN     False  \n",
       "1                              NaN                           NaN     False  \n",
       "2    Tue Mar 5 22:32:20 2019 +0100                           NaN     False  \n",
       "3                              NaN  add virtual bounded contexts     False  \n",
       "4                              NaN                  test\\ttest\\t     False  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "log['sha'] = log['sha'].fillna(method=\"ffill\")\n",
    "log.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sha\n",
       "024811d252f8d8218e6795d46203cff25971bc19                        simplifying access to Integer\n",
       "0365d34d2977dd24ec0bb3e8b0edff5694908c80             downgrade jqassistant due to weird error\n",
       "0504ec9fe345d9d34b15c374333f709fb147e6d6    Update petclinic_db_setup_mysql.txt Correct in...\n",
       "053c84ecc95b246ef4a40fb3d4304e8908604af4                             migrated to Spring 4.0.1\n",
       "057015c14cce4791ff309419de8a8bd6339fd6e7    Spring MVC Test Framework and migration to Spr...\n",
       "Name: message, dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sha_msg = log.dropna(subset=['message']).groupby('sha')['message'].apply(' '.join)\n",
    "sha_msg.head()"
   ]
  },
  {
   "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>raw</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sha</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4d3d9de655faa813781027d8b1baed819c6a56fe</th>\n",
       "      <td>20\\t1\\tjqassistant/business.adoc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4d3d9de655faa813781027d8b1baed819c6a56fe</th>\n",
       "      <td>1\\t1\\tsrc/main/java/org/springframework/sample...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4d3d9de655faa813781027d8b1baed819c6a56fe</th>\n",
       "      <td>2\\t0\\tsrc/main/java/org/springframework/sample...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4d3d9de655faa813781027d8b1baed819c6a56fe</th>\n",
       "      <td>2\\t1\\tsrc/main/java/org/springframework/sample...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4d3d9de655faa813781027d8b1baed819c6a56fe</th>\n",
       "      <td>2\\t0\\tsrc/main/java/org/springframework/sample...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                        raw\n",
       "sha                                                                                        \n",
       "4d3d9de655faa813781027d8b1baed819c6a56fe                   20\\t1\\tjqassistant/business.adoc\n",
       "4d3d9de655faa813781027d8b1baed819c6a56fe  1\\t1\\tsrc/main/java/org/springframework/sample...\n",
       "4d3d9de655faa813781027d8b1baed819c6a56fe  2\\t0\\tsrc/main/java/org/springframework/sample...\n",
       "4d3d9de655faa813781027d8b1baed819c6a56fe  2\\t1\\tsrc/main/java/org/springframework/sample...\n",
       "4d3d9de655faa813781027d8b1baed819c6a56fe  2\\t0\\tsrc/main/java/org/springframework/sample..."
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sha_files = log[log['no_entry']][['sha', 'raw']]\n",
    "sha_files = sha_files.set_index('sha')\n",
    "sha_files.head()"
   ]
  },
  {
   "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>additions</th>\n",
       "      <th>deletions</th>\n",
       "      <th>filename</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sha</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4d3d9de655faa813781027d8b1baed819c6a56fe</th>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>jqassistant/business.adoc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4d3d9de655faa813781027d8b1baed819c6a56fe</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>src/main/java/org/springframework/samples/petc...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4d3d9de655faa813781027d8b1baed819c6a56fe</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>src/main/java/org/springframework/samples/petc...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4d3d9de655faa813781027d8b1baed819c6a56fe</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>src/main/java/org/springframework/samples/petc...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4d3d9de655faa813781027d8b1baed819c6a56fe</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>src/main/java/org/springframework/samples/petc...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         additions deletions  \\\n",
       "sha                                                            \n",
       "4d3d9de655faa813781027d8b1baed819c6a56fe        20         1   \n",
       "4d3d9de655faa813781027d8b1baed819c6a56fe         1         1   \n",
       "4d3d9de655faa813781027d8b1baed819c6a56fe         2         0   \n",
       "4d3d9de655faa813781027d8b1baed819c6a56fe         2         1   \n",
       "4d3d9de655faa813781027d8b1baed819c6a56fe         2         0   \n",
       "\n",
       "                                                                                   filename  \n",
       "sha                                                                                          \n",
       "4d3d9de655faa813781027d8b1baed819c6a56fe                          jqassistant/business.adoc  \n",
       "4d3d9de655faa813781027d8b1baed819c6a56fe  src/main/java/org/springframework/samples/petc...  \n",
       "4d3d9de655faa813781027d8b1baed819c6a56fe  src/main/java/org/springframework/samples/petc...  \n",
       "4d3d9de655faa813781027d8b1baed819c6a56fe  src/main/java/org/springframework/samples/petc...  \n",
       "4d3d9de655faa813781027d8b1baed819c6a56fe  src/main/java/org/springframework/samples/petc...  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sha_files[['additions', 'deletions', 'filename']] = sha_files['raw'].str.split(\"\\t\", expand=True)\n",
    "del(sha_files['raw'])\n",
    "sha_files.head()"
   ]
  },
  {
   "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>author</th>\n",
       "      <th>timestamp</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sha</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>024811d252f8d8218e6795d46203cff25971bc19</th>\n",
       "      <td>Mic &lt;misvy@vmware.com&gt;</td>\n",
       "      <td>Thu Mar 14 18:04:36 2013 +0800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0365d34d2977dd24ec0bb3e8b0edff5694908c80</th>\n",
       "      <td>Markus Harrer &lt;feststelltaste@googlemail.com&gt;</td>\n",
       "      <td>Mon Nov 12 10:28:34 2018 +0100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0504ec9fe345d9d34b15c374333f709fb147e6d6</th>\n",
       "      <td>thinksh &lt;thinkshihang@gmail.com&gt;</td>\n",
       "      <td>Wed Feb 3 23:19:46 2016 -0500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>053c84ecc95b246ef4a40fb3d4304e8908604af4</th>\n",
       "      <td>Mic &lt;misvy@vmware.com&gt;</td>\n",
       "      <td>Mon Feb 3 09:31:44 2014 +0800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>057015c14cce4791ff309419de8a8bd6339fd6e7</th>\n",
       "      <td>Mic &lt;misvy@vmware.com&gt;</td>\n",
       "      <td>Fri Feb 15 15:31:04 2013 +0800</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                 author  \\\n",
       "sha                                                                                       \n",
       "024811d252f8d8218e6795d46203cff25971bc19                         Mic <misvy@vmware.com>   \n",
       "0365d34d2977dd24ec0bb3e8b0edff5694908c80  Markus Harrer <feststelltaste@googlemail.com>   \n",
       "0504ec9fe345d9d34b15c374333f709fb147e6d6               thinksh <thinkshihang@gmail.com>   \n",
       "053c84ecc95b246ef4a40fb3d4304e8908604af4                         Mic <misvy@vmware.com>   \n",
       "057015c14cce4791ff309419de8a8bd6339fd6e7                         Mic <misvy@vmware.com>   \n",
       "\n",
       "                                                                 timestamp  \n",
       "sha                                                                         \n",
       "024811d252f8d8218e6795d46203cff25971bc19    Thu Mar 14 18:04:36 2013 +0800  \n",
       "0365d34d2977dd24ec0bb3e8b0edff5694908c80    Mon Nov 12 10:28:34 2018 +0100  \n",
       "0504ec9fe345d9d34b15c374333f709fb147e6d6     Wed Feb 3 23:19:46 2016 -0500  \n",
       "053c84ecc95b246ef4a40fb3d4304e8908604af4     Mon Feb 3 09:31:44 2014 +0800  \n",
       "057015c14cce4791ff309419de8a8bd6339fd6e7    Fri Feb 15 15:31:04 2013 +0800  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = log.groupby('sha')[['author', 'timestamp']].first()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": false
   },
   "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>author</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sha</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>024811d252f8d8218e6795d46203cff25971bc19</th>\n",
       "      <td>Mic &lt;misvy@vmware.com&gt;</td>\n",
       "      <td>Thu Mar 14 18:04:36 2013 +0800</td>\n",
       "      <td>simplifying access to Integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0365d34d2977dd24ec0bb3e8b0edff5694908c80</th>\n",
       "      <td>Markus Harrer &lt;feststelltaste@googlemail.com&gt;</td>\n",
       "      <td>Mon Nov 12 10:28:34 2018 +0100</td>\n",
       "      <td>downgrade jqassistant due to weird error</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0504ec9fe345d9d34b15c374333f709fb147e6d6</th>\n",
       "      <td>thinksh &lt;thinkshihang@gmail.com&gt;</td>\n",
       "      <td>Wed Feb 3 23:19:46 2016 -0500</td>\n",
       "      <td>Update petclinic_db_setup_mysql.txt Correct in...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>053c84ecc95b246ef4a40fb3d4304e8908604af4</th>\n",
       "      <td>Mic &lt;misvy@vmware.com&gt;</td>\n",
       "      <td>Mon Feb 3 09:31:44 2014 +0800</td>\n",
       "      <td>migrated to Spring 4.0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>057015c14cce4791ff309419de8a8bd6339fd6e7</th>\n",
       "      <td>Mic &lt;misvy@vmware.com&gt;</td>\n",
       "      <td>Fri Feb 15 15:31:04 2013 +0800</td>\n",
       "      <td>Spring MVC Test Framework and migration to Spr...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                 author  \\\n",
       "sha                                                                                       \n",
       "024811d252f8d8218e6795d46203cff25971bc19                         Mic <misvy@vmware.com>   \n",
       "0365d34d2977dd24ec0bb3e8b0edff5694908c80  Markus Harrer <feststelltaste@googlemail.com>   \n",
       "0504ec9fe345d9d34b15c374333f709fb147e6d6               thinksh <thinkshihang@gmail.com>   \n",
       "053c84ecc95b246ef4a40fb3d4304e8908604af4                         Mic <misvy@vmware.com>   \n",
       "057015c14cce4791ff309419de8a8bd6339fd6e7                         Mic <misvy@vmware.com>   \n",
       "\n",
       "                                                                 timestamp  \\\n",
       "sha                                                                          \n",
       "024811d252f8d8218e6795d46203cff25971bc19    Thu Mar 14 18:04:36 2013 +0800   \n",
       "0365d34d2977dd24ec0bb3e8b0edff5694908c80    Mon Nov 12 10:28:34 2018 +0100   \n",
       "0504ec9fe345d9d34b15c374333f709fb147e6d6     Wed Feb 3 23:19:46 2016 -0500   \n",
       "053c84ecc95b246ef4a40fb3d4304e8908604af4     Mon Feb 3 09:31:44 2014 +0800   \n",
       "057015c14cce4791ff309419de8a8bd6339fd6e7    Fri Feb 15 15:31:04 2013 +0800   \n",
       "\n",
       "                                                                                    message  \n",
       "sha                                                                                          \n",
       "024811d252f8d8218e6795d46203cff25971bc19                      simplifying access to Integer  \n",
       "0365d34d2977dd24ec0bb3e8b0edff5694908c80           downgrade jqassistant due to weird error  \n",
       "0504ec9fe345d9d34b15c374333f709fb147e6d6  Update petclinic_db_setup_mysql.txt Correct in...  \n",
       "053c84ecc95b246ef4a40fb3d4304e8908604af4                           migrated to Spring 4.0.1  \n",
       "057015c14cce4791ff309419de8a8bd6339fd6e7  Spring MVC Test Framework and migration to Spr...  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.join(sha_msg)\n",
    "df.head()"
   ]
  },
  {
   "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>author</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>message</th>\n",
       "      <th>additions</th>\n",
       "      <th>deletions</th>\n",
       "      <th>filename</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sha</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>024811d252f8d8218e6795d46203cff25971bc19</th>\n",
       "      <td>Mic &lt;misvy@vmware.com&gt;</td>\n",
       "      <td>Thu Mar 14 18:04:36 2013 +0800</td>\n",
       "      <td>simplifying access to Integer</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>src/main/java/org/springframework/samples/petc...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0365d34d2977dd24ec0bb3e8b0edff5694908c80</th>\n",
       "      <td>Markus Harrer &lt;feststelltaste@googlemail.com&gt;</td>\n",
       "      <td>Mon Nov 12 10:28:34 2018 +0100</td>\n",
       "      <td>downgrade jqassistant due to weird error</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>pom.xml</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0504ec9fe345d9d34b15c374333f709fb147e6d6</th>\n",
       "      <td>thinksh &lt;thinkshihang@gmail.com&gt;</td>\n",
       "      <td>Wed Feb 3 23:19:46 2016 -0500</td>\n",
       "      <td>Update petclinic_db_setup_mysql.txt Correct in...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>src/main/resources/db/mysql/petclinic_db_setup...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>053c84ecc95b246ef4a40fb3d4304e8908604af4</th>\n",
       "      <td>Mic &lt;misvy@vmware.com&gt;</td>\n",
       "      <td>Mon Feb 3 09:31:44 2014 +0800</td>\n",
       "      <td>migrated to Spring 4.0.1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>pom.xml</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>057015c14cce4791ff309419de8a8bd6339fd6e7</th>\n",
       "      <td>Mic &lt;misvy@vmware.com&gt;</td>\n",
       "      <td>Fri Feb 15 15:31:04 2013 +0800</td>\n",
       "      <td>Spring MVC Test Framework and migration to Spr...</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>.springBeans</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                 author  \\\n",
       "sha                                                                                       \n",
       "024811d252f8d8218e6795d46203cff25971bc19                         Mic <misvy@vmware.com>   \n",
       "0365d34d2977dd24ec0bb3e8b0edff5694908c80  Markus Harrer <feststelltaste@googlemail.com>   \n",
       "0504ec9fe345d9d34b15c374333f709fb147e6d6               thinksh <thinkshihang@gmail.com>   \n",
       "053c84ecc95b246ef4a40fb3d4304e8908604af4                         Mic <misvy@vmware.com>   \n",
       "057015c14cce4791ff309419de8a8bd6339fd6e7                         Mic <misvy@vmware.com>   \n",
       "\n",
       "                                                                 timestamp  \\\n",
       "sha                                                                          \n",
       "024811d252f8d8218e6795d46203cff25971bc19    Thu Mar 14 18:04:36 2013 +0800   \n",
       "0365d34d2977dd24ec0bb3e8b0edff5694908c80    Mon Nov 12 10:28:34 2018 +0100   \n",
       "0504ec9fe345d9d34b15c374333f709fb147e6d6     Wed Feb 3 23:19:46 2016 -0500   \n",
       "053c84ecc95b246ef4a40fb3d4304e8908604af4     Mon Feb 3 09:31:44 2014 +0800   \n",
       "057015c14cce4791ff309419de8a8bd6339fd6e7    Fri Feb 15 15:31:04 2013 +0800   \n",
       "\n",
       "                                                                                    message  \\\n",
       "sha                                                                                           \n",
       "024811d252f8d8218e6795d46203cff25971bc19                      simplifying access to Integer   \n",
       "0365d34d2977dd24ec0bb3e8b0edff5694908c80           downgrade jqassistant due to weird error   \n",
       "0504ec9fe345d9d34b15c374333f709fb147e6d6  Update petclinic_db_setup_mysql.txt Correct in...   \n",
       "053c84ecc95b246ef4a40fb3d4304e8908604af4                           migrated to Spring 4.0.1   \n",
       "057015c14cce4791ff309419de8a8bd6339fd6e7  Spring MVC Test Framework and migration to Spr...   \n",
       "\n",
       "                                         additions deletions  \\\n",
       "sha                                                            \n",
       "024811d252f8d8218e6795d46203cff25971bc19         1         1   \n",
       "0365d34d2977dd24ec0bb3e8b0edff5694908c80         1         1   \n",
       "0504ec9fe345d9d34b15c374333f709fb147e6d6         1         1   \n",
       "053c84ecc95b246ef4a40fb3d4304e8908604af4         1         1   \n",
       "057015c14cce4791ff309419de8a8bd6339fd6e7         1        18   \n",
       "\n",
       "                                                                                   filename  \n",
       "sha                                                                                          \n",
       "024811d252f8d8218e6795d46203cff25971bc19  src/main/java/org/springframework/samples/petc...  \n",
       "0365d34d2977dd24ec0bb3e8b0edff5694908c80                                            pom.xml  \n",
       "0504ec9fe345d9d34b15c374333f709fb147e6d6  src/main/resources/db/mysql/petclinic_db_setup...  \n",
       "053c84ecc95b246ef4a40fb3d4304e8908604af4                                            pom.xml  \n",
       "057015c14cce4791ff309419de8a8bd6339fd6e7                                       .springBeans  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.join(sha_files, how='right')\n",
    "df.head()"
   ]
  }
 ],
 "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}