{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_This mini series introduces you to various sources of software data_\n",
    "\n",
    "SonarQube is the de facto standard when Java development teams want check the internal software quality of their software systems. It's an easy tools that integrates well into an existing continuous integration landscape. SonarQube is also a huge gold mine with hundreds of metrics and rules that check you code.\n",
    "\n",
    "Albeit I'm personally not a big fan of context-free software analysis, we can use the data source from SonarQube to enrich our specific data analysis if needed.  Let's have a look at the API that let's us retrieve some data that could help us to improve software systematically."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Key links\n",
    "* The API documentation can be found: https://sonarcloud.io/web_api/api/issues/search"
   ]
  },
  {
   "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>component</th>\n",
       "      <th>creationDate</th>\n",
       "      <th>debt</th>\n",
       "      <th>effort</th>\n",
       "      <th>flows</th>\n",
       "      <th>fromHotspot</th>\n",
       "      <th>hash</th>\n",
       "      <th>key</th>\n",
       "      <th>line</th>\n",
       "      <th>message</th>\n",
       "      <th>...</th>\n",
       "      <th>rule</th>\n",
       "      <th>severity</th>\n",
       "      <th>status</th>\n",
       "      <th>tags</th>\n",
       "      <th>textRange.endLine</th>\n",
       "      <th>textRange.endOffset</th>\n",
       "      <th>textRange.startLine</th>\n",
       "      <th>textRange.startOffset</th>\n",
       "      <th>type</th>\n",
       "      <th>updateDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>org.springframework.samples:spring-petclinic:b...</td>\n",
       "      <td>2018-03-07T07:45:21+0100</td>\n",
       "      <td>2min</td>\n",
       "      <td>2min</td>\n",
       "      <td>[]</td>\n",
       "      <td>False</td>\n",
       "      <td>48c1dff4bc3cd8f9b9b8c76f49484c2a</td>\n",
       "      <td>AWLJtvMj-pl6AHs2EogL</td>\n",
       "      <td>5</td>\n",
       "      <td>Rename this field \"INSTANCE\" to match the regu...</td>\n",
       "      <td>...</td>\n",
       "      <td>squid:S3008</td>\n",
       "      <td>MINOR</td>\n",
       "      <td>OPEN</td>\n",
       "      <td>[convention]</td>\n",
       "      <td>5</td>\n",
       "      <td>42</td>\n",
       "      <td>5</td>\n",
       "      <td>34</td>\n",
       "      <td>CODE_SMELL</td>\n",
       "      <td>2018-04-15T16:28:40+0200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>org.springframework.samples:spring-petclinic:b...</td>\n",
       "      <td>2018-03-07T07:45:21+0100</td>\n",
       "      <td>20min</td>\n",
       "      <td>20min</td>\n",
       "      <td>[]</td>\n",
       "      <td>False</td>\n",
       "      <td>48c1dff4bc3cd8f9b9b8c76f49484c2a</td>\n",
       "      <td>AWLJtvMj-pl6AHs2EogM</td>\n",
       "      <td>5</td>\n",
       "      <td>Make this \"public static INSTANCE\" field final</td>\n",
       "      <td>...</td>\n",
       "      <td>squid:S1444</td>\n",
       "      <td>MINOR</td>\n",
       "      <td>OPEN</td>\n",
       "      <td>[cert, cwe]</td>\n",
       "      <td>5</td>\n",
       "      <td>42</td>\n",
       "      <td>5</td>\n",
       "      <td>34</td>\n",
       "      <td>VULNERABILITY</td>\n",
       "      <td>2018-04-15T16:28:40+0200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>org.springframework.samples:spring-petclinic:b...</td>\n",
       "      <td>2018-03-07T07:45:21+0100</td>\n",
       "      <td>10min</td>\n",
       "      <td>10min</td>\n",
       "      <td>[]</td>\n",
       "      <td>False</td>\n",
       "      <td>48c1dff4bc3cd8f9b9b8c76f49484c2a</td>\n",
       "      <td>AWLJtvMj-pl6AHs2EogN</td>\n",
       "      <td>5</td>\n",
       "      <td>Make INSTANCE a static final constant or non-p...</td>\n",
       "      <td>...</td>\n",
       "      <td>squid:ClassVariableVisibilityCheck</td>\n",
       "      <td>MINOR</td>\n",
       "      <td>OPEN</td>\n",
       "      <td>[cwe]</td>\n",
       "      <td>5</td>\n",
       "      <td>42</td>\n",
       "      <td>5</td>\n",
       "      <td>34</td>\n",
       "      <td>VULNERABILITY</td>\n",
       "      <td>2018-04-15T16:28:40+0200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>org.springframework.samples:spring-petclinic:b...</td>\n",
       "      <td>2017-11-22T17:54:34+0100</td>\n",
       "      <td>5min</td>\n",
       "      <td>5min</td>\n",
       "      <td>[{'locations': [{'component': 'org.springframe...</td>\n",
       "      <td>False</td>\n",
       "      <td>815a0864eec54976893cbe650dfba48d</td>\n",
       "      <td>AWLJtvMo-pl6AHs2EogP</td>\n",
       "      <td>11</td>\n",
       "      <td>Remove this unused method parameter \"id\".</td>\n",
       "      <td>...</td>\n",
       "      <td>squid:S1172</td>\n",
       "      <td>MAJOR</td>\n",
       "      <td>OPEN</td>\n",
       "      <td>[cert, misra, unused]</td>\n",
       "      <td>11</td>\n",
       "      <td>60</td>\n",
       "      <td>11</td>\n",
       "      <td>58</td>\n",
       "      <td>CODE_SMELL</td>\n",
       "      <td>2018-04-15T16:28:40+0200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>org.springframework.samples:spring-petclinic:b...</td>\n",
       "      <td>2017-11-22T17:54:34+0100</td>\n",
       "      <td>15min</td>\n",
       "      <td>15min</td>\n",
       "      <td>[]</td>\n",
       "      <td>False</td>\n",
       "      <td>eb375774c265dedeefeb29283ceea9bc</td>\n",
       "      <td>AWLJtvMo-pl6AHs2EogR</td>\n",
       "      <td>15</td>\n",
       "      <td>Either re-interrupt this method or rethrow the...</td>\n",
       "      <td>...</td>\n",
       "      <td>squid:S2142</td>\n",
       "      <td>MAJOR</td>\n",
       "      <td>OPEN</td>\n",
       "      <td>[cwe, multi-threading]</td>\n",
       "      <td>15</td>\n",
       "      <td>39</td>\n",
       "      <td>15</td>\n",
       "      <td>17</td>\n",
       "      <td>BUG</td>\n",
       "      <td>2018-04-15T16:28:40+0200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           component  \\\n",
       "0  org.springframework.samples:spring-petclinic:b...   \n",
       "1  org.springframework.samples:spring-petclinic:b...   \n",
       "2  org.springframework.samples:spring-petclinic:b...   \n",
       "3  org.springframework.samples:spring-petclinic:b...   \n",
       "4  org.springframework.samples:spring-petclinic:b...   \n",
       "\n",
       "               creationDate   debt effort  \\\n",
       "0  2018-03-07T07:45:21+0100   2min   2min   \n",
       "1  2018-03-07T07:45:21+0100  20min  20min   \n",
       "2  2018-03-07T07:45:21+0100  10min  10min   \n",
       "3  2017-11-22T17:54:34+0100   5min   5min   \n",
       "4  2017-11-22T17:54:34+0100  15min  15min   \n",
       "\n",
       "                                               flows  fromHotspot  \\\n",
       "0                                                 []        False   \n",
       "1                                                 []        False   \n",
       "2                                                 []        False   \n",
       "3  [{'locations': [{'component': 'org.springframe...        False   \n",
       "4                                                 []        False   \n",
       "\n",
       "                               hash                   key  line  \\\n",
       "0  48c1dff4bc3cd8f9b9b8c76f49484c2a  AWLJtvMj-pl6AHs2EogL     5   \n",
       "1  48c1dff4bc3cd8f9b9b8c76f49484c2a  AWLJtvMj-pl6AHs2EogM     5   \n",
       "2  48c1dff4bc3cd8f9b9b8c76f49484c2a  AWLJtvMj-pl6AHs2EogN     5   \n",
       "3  815a0864eec54976893cbe650dfba48d  AWLJtvMo-pl6AHs2EogP    11   \n",
       "4  eb375774c265dedeefeb29283ceea9bc  AWLJtvMo-pl6AHs2EogR    15   \n",
       "\n",
       "                                             message  \\\n",
       "0  Rename this field \"INSTANCE\" to match the regu...   \n",
       "1     Make this \"public static INSTANCE\" field final   \n",
       "2  Make INSTANCE a static final constant or non-p...   \n",
       "3          Remove this unused method parameter \"id\".   \n",
       "4  Either re-interrupt this method or rethrow the...   \n",
       "\n",
       "             ...                                           rule severity  \\\n",
       "0            ...                                    squid:S3008    MINOR   \n",
       "1            ...                                    squid:S1444    MINOR   \n",
       "2            ...             squid:ClassVariableVisibilityCheck    MINOR   \n",
       "3            ...                                    squid:S1172    MAJOR   \n",
       "4            ...                                    squid:S2142    MAJOR   \n",
       "\n",
       "  status                    tags textRange.endLine textRange.endOffset  \\\n",
       "0   OPEN            [convention]                 5                  42   \n",
       "1   OPEN             [cert, cwe]                 5                  42   \n",
       "2   OPEN                   [cwe]                 5                  42   \n",
       "3   OPEN   [cert, misra, unused]                11                  60   \n",
       "4   OPEN  [cwe, multi-threading]                15                  39   \n",
       "\n",
       "   textRange.startLine  textRange.startOffset           type  \\\n",
       "0                    5                     34     CODE_SMELL   \n",
       "1                    5                     34  VULNERABILITY   \n",
       "2                    5                     34  VULNERABILITY   \n",
       "3                   11                     58     CODE_SMELL   \n",
       "4                   15                     17            BUG   \n",
       "\n",
       "                 updateDate  \n",
       "0  2018-04-15T16:28:40+0200  \n",
       "1  2018-04-15T16:28:40+0200  \n",
       "2  2018-04-15T16:28:40+0200  \n",
       "3  2018-04-15T16:28:40+0200  \n",
       "4  2018-04-15T16:28:40+0200  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import requests\n",
    "from pandas.io.json import json_normalize\n",
    "ALL_ISSUES_URL = \"https://sonarcloud.io/api/issues/search?languages=java&componentKeys=org.springframework.samples:spring-petclinic:boundedcontexts\"\n",
    "# load issues\n",
    "json_data = requests.get(ALL_ISSUES_URL).json()['issues']\n",
    "# flatten json structure and create DataFrame\n",
    "df = json_normalize(json_data)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 55 entries, 0 to 54\n",
      "Data columns (total 22 columns):\n",
      "component                55 non-null object\n",
      "creationDate             55 non-null object\n",
      "debt                     55 non-null object\n",
      "effort                   55 non-null object\n",
      "flows                    55 non-null object\n",
      "fromHotspot              55 non-null bool\n",
      "hash                     55 non-null object\n",
      "key                      55 non-null object\n",
      "line                     55 non-null int64\n",
      "message                  55 non-null object\n",
      "organization             55 non-null object\n",
      "project                  55 non-null object\n",
      "rule                     55 non-null object\n",
      "severity                 55 non-null object\n",
      "status                   55 non-null object\n",
      "tags                     55 non-null object\n",
      "textRange.endLine        55 non-null int64\n",
      "textRange.endOffset      55 non-null int64\n",
      "textRange.startLine      55 non-null int64\n",
      "textRange.startOffset    55 non-null int64\n",
      "type                     55 non-null object\n",
      "updateDate               55 non-null object\n",
      "dtypes: bool(1), int64(5), object(16)\n",
      "memory usage: 9.2+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df.to_excel(\"sonar_issues_spring_petclinic.xlsx\", columns=['project','component', 'rule', 'type', 'severity', 'debt', 'status', 'message'], index=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://sonarcloud.io/api/webservices/list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "348770"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import requests\n",
    "url = \"https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9\"\n",
    "\n",
    "\n",
    "data = []\n",
    "start = 1\n",
    "end = 499\n",
    "\n",
    "def get_url(start, end):\n",
    "    return url + \"&p={}&ps={}\".format(start,end)\n",
    "\n",
    "total = requests.get(get_url(1,1)).json()['total']\n",
    "total"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(698, 468)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=1&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=2&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=3&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=4&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=5&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=6&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=7&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=8&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=9&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=10&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=11&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=12&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=13&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=14&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=15&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=16&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=17&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=18&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=19&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=20&ps=499\n",
      "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=21&ps=499\n"
     ]
    },
    {
     "ename": "KeyError",
     "evalue": "'issues'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-36-456825bf66f8>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m     14\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     15\u001b[0m \u001b[1;32mwhile\u001b[0m \u001b[0mcounter\u001b[0m \u001b[1;33m<\u001b[0m \u001b[1;36m698\u001b[0m \u001b[1;33m+\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 16\u001b[1;33m     \u001b[0madd_data\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcounter\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     17\u001b[0m     \u001b[0mcounter\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mcounter\u001b[0m \u001b[1;33m+\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     18\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<ipython-input-36-456825bf66f8>\u001b[0m in \u001b[0;36madd_data\u001b[1;34m(counter)\u001b[0m\n\u001b[0;32m     11\u001b[0m     \u001b[0mu\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0murl\u001b[0m \u001b[1;33m+\u001b[0m \u001b[1;34m\"&p={}&ps=499\"\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcounter\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     12\u001b[0m     \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mu\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 13\u001b[1;33m     \u001b[0mdata\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mextend\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mrequests\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mu\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mjson\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'issues'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     14\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     15\u001b[0m \u001b[1;32mwhile\u001b[0m \u001b[0mcounter\u001b[0m \u001b[1;33m<\u001b[0m \u001b[1;36m698\u001b[0m \u001b[1;33m+\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: 'issues'"
     ]
    }
   ],
   "source": [
    "divident = 499\n",
    "integer = int(total / divident)\n",
    "remainder = total % divident\n",
    "(integer,remainder)\n",
    "\n",
    "counter = 1\n",
    "\n",
    "data = []\n",
    "\n",
    "def add_data(counter):\n",
    "    u = url + \"&p={}&ps=499\".format(counter)\n",
    "    print(u)\n",
    "    data.extend(requests.get(u).json()['issues'])\n",
    "    \n",
    "while counter < 698 + 1:\n",
    "    add_data(counter)\n",
    "    counter = counter + 1\n",
    "    \n",
    "req = u = url + \"&p={}&ps={}\".format(counter, remainder)\n",
    "data.extend(requests.get(u).json()['issues'])\n",
    "len(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "with open (\"jdk9_issues_excerpt.json\", mode='w') as f:\n",
    "    f.write(json.dumps(data))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>closeDate</th>\n",
       "      <th>component</th>\n",
       "      <th>creationDate</th>\n",
       "      <th>debt</th>\n",
       "      <th>effort</th>\n",
       "      <th>flows</th>\n",
       "      <th>hash</th>\n",
       "      <th>key</th>\n",
       "      <th>line</th>\n",
       "      <th>...</th>\n",
       "      <th>severity</th>\n",
       "      <th>status</th>\n",
       "      <th>subProject</th>\n",
       "      <th>tags</th>\n",
       "      <th>textRange.endLine</th>\n",
       "      <th>textRange.endOffset</th>\n",
       "      <th>textRange.startLine</th>\n",
       "      <th>textRange.startOffset</th>\n",
       "      <th>type</th>\n",
       "      <th>updateDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>mchung</td>\n",
       "      <td>NaN</td>\n",
       "      <td>net.java.openjdk:jdk9:jdk:src/java.base/share/...</td>\n",
       "      <td>2016-11-10T16:11:08+0100</td>\n",
       "      <td>5min</td>\n",
       "      <td>5min</td>\n",
       "      <td>[]</td>\n",
       "      <td>ceb3b90ca0cef1868923530915cc87b9</td>\n",
       "      <td>AVhO6lMH0AIB2Vhfjr-u</td>\n",
       "      <td>226.0</td>\n",
       "      <td>...</td>\n",
       "      <td>MINOR</td>\n",
       "      <td>OPEN</td>\n",
       "      <td>net.java.openjdk:jdk9:jdk</td>\n",
       "      <td>[convention]</td>\n",
       "      <td>226.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>226.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>CODE_SMELL</td>\n",
       "      <td>2016-11-10T16:11:08+0100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mchung</td>\n",
       "      <td>NaN</td>\n",
       "      <td>net.java.openjdk:jdk9:jdk:src/java.base/share/...</td>\n",
       "      <td>2016-11-10T16:11:08+0100</td>\n",
       "      <td>5min</td>\n",
       "      <td>5min</td>\n",
       "      <td>[]</td>\n",
       "      <td>b640fdfabab5d1b62582d4e25d59c606</td>\n",
       "      <td>AVhO6lMI0AIB2Vhfjr-v</td>\n",
       "      <td>386.0</td>\n",
       "      <td>...</td>\n",
       "      <td>MINOR</td>\n",
       "      <td>OPEN</td>\n",
       "      <td>net.java.openjdk:jdk9:jdk</td>\n",
       "      <td>[convention]</td>\n",
       "      <td>386.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>386.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>CODE_SMELL</td>\n",
       "      <td>2016-11-10T16:11:08+0100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>duke</td>\n",
       "      <td>NaN</td>\n",
       "      <td>net.java.openjdk:jdk9:jdk:src/java.base/share/...</td>\n",
       "      <td>2016-11-10T16:11:08+0100</td>\n",
       "      <td>13min</td>\n",
       "      <td>13min</td>\n",
       "      <td>[{'locations': [{'component': 'net.java.openjd...</td>\n",
       "      <td>5eb1048a65cef9f848fe69a1337d77e1</td>\n",
       "      <td>AVhO6lSS0AIB2Vhfjr-x</td>\n",
       "      <td>322.0</td>\n",
       "      <td>...</td>\n",
       "      <td>CRITICAL</td>\n",
       "      <td>OPEN</td>\n",
       "      <td>net.java.openjdk:jdk9:jdk</td>\n",
       "      <td>[brain-overload]</td>\n",
       "      <td>322.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>322.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>CODE_SMELL</td>\n",
       "      <td>2016-11-10T16:11:08+0100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>mchung</td>\n",
       "      <td>NaN</td>\n",
       "      <td>net.java.openjdk:jdk9:jdk:src/java.base/share/...</td>\n",
       "      <td>2016-11-10T16:11:08+0100</td>\n",
       "      <td>2min</td>\n",
       "      <td>2min</td>\n",
       "      <td>[]</td>\n",
       "      <td>f0130b8994e5c47e50e1a2e79d2a7236</td>\n",
       "      <td>AVhO6lSR0AIB2Vhfjr-w</td>\n",
       "      <td>484.0</td>\n",
       "      <td>...</td>\n",
       "      <td>MINOR</td>\n",
       "      <td>OPEN</td>\n",
       "      <td>net.java.openjdk:jdk9:jdk</td>\n",
       "      <td>[convention]</td>\n",
       "      <td>484.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>484.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>CODE_SMELL</td>\n",
       "      <td>2016-11-10T16:11:08+0100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>smarks</td>\n",
       "      <td>NaN</td>\n",
       "      <td>net.java.openjdk:jdk9:jdk:src/java.base/share/...</td>\n",
       "      <td>2016-11-10T16:11:08+0100</td>\n",
       "      <td>5min</td>\n",
       "      <td>5min</td>\n",
       "      <td>[]</td>\n",
       "      <td>2ebc8d0c1f68c70bb912de5d30f06d41</td>\n",
       "      <td>AVhO6myN0AIB2Vhfjr-y</td>\n",
       "      <td>95.0</td>\n",
       "      <td>...</td>\n",
       "      <td>MINOR</td>\n",
       "      <td>OPEN</td>\n",
       "      <td>net.java.openjdk:jdk9:jdk</td>\n",
       "      <td>[convention]</td>\n",
       "      <td>95.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>CODE_SMELL</td>\n",
       "      <td>2016-11-10T16:11:08+0100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   author closeDate                                          component  \\\n",
       "0  mchung       NaN  net.java.openjdk:jdk9:jdk:src/java.base/share/...   \n",
       "1  mchung       NaN  net.java.openjdk:jdk9:jdk:src/java.base/share/...   \n",
       "2    duke       NaN  net.java.openjdk:jdk9:jdk:src/java.base/share/...   \n",
       "3  mchung       NaN  net.java.openjdk:jdk9:jdk:src/java.base/share/...   \n",
       "4  smarks       NaN  net.java.openjdk:jdk9:jdk:src/java.base/share/...   \n",
       "\n",
       "               creationDate   debt effort  \\\n",
       "0  2016-11-10T16:11:08+0100   5min   5min   \n",
       "1  2016-11-10T16:11:08+0100   5min   5min   \n",
       "2  2016-11-10T16:11:08+0100  13min  13min   \n",
       "3  2016-11-10T16:11:08+0100   2min   2min   \n",
       "4  2016-11-10T16:11:08+0100   5min   5min   \n",
       "\n",
       "                                               flows  \\\n",
       "0                                                 []   \n",
       "1                                                 []   \n",
       "2  [{'locations': [{'component': 'net.java.openjd...   \n",
       "3                                                 []   \n",
       "4                                                 []   \n",
       "\n",
       "                               hash                   key   line  \\\n",
       "0  ceb3b90ca0cef1868923530915cc87b9  AVhO6lMH0AIB2Vhfjr-u  226.0   \n",
       "1  b640fdfabab5d1b62582d4e25d59c606  AVhO6lMI0AIB2Vhfjr-v  386.0   \n",
       "2  5eb1048a65cef9f848fe69a1337d77e1  AVhO6lSS0AIB2Vhfjr-x  322.0   \n",
       "3  f0130b8994e5c47e50e1a2e79d2a7236  AVhO6lSR0AIB2Vhfjr-w  484.0   \n",
       "4  2ebc8d0c1f68c70bb912de5d30f06d41  AVhO6myN0AIB2Vhfjr-y   95.0   \n",
       "\n",
       "             ...             severity status                 subProject  \\\n",
       "0            ...                MINOR   OPEN  net.java.openjdk:jdk9:jdk   \n",
       "1            ...                MINOR   OPEN  net.java.openjdk:jdk9:jdk   \n",
       "2            ...             CRITICAL   OPEN  net.java.openjdk:jdk9:jdk   \n",
       "3            ...                MINOR   OPEN  net.java.openjdk:jdk9:jdk   \n",
       "4            ...                MINOR   OPEN  net.java.openjdk:jdk9:jdk   \n",
       "\n",
       "               tags textRange.endLine textRange.endOffset textRange.startLine  \\\n",
       "0      [convention]             226.0                29.0               226.0   \n",
       "1      [convention]             386.0                25.0               386.0   \n",
       "2  [brain-overload]             322.0                26.0               322.0   \n",
       "3      [convention]             484.0                41.0               484.0   \n",
       "4      [convention]              95.0                21.0                95.0   \n",
       "\n",
       "  textRange.startOffset        type                updateDate  \n",
       "0                  25.0  CODE_SMELL  2016-11-10T16:11:08+0100  \n",
       "1                  14.0  CODE_SMELL  2016-11-10T16:11:08+0100  \n",
       "2                  18.0  CODE_SMELL  2016-11-10T16:11:08+0100  \n",
       "3                  27.0  CODE_SMELL  2016-11-10T16:11:08+0100  \n",
       "4                  16.0  CODE_SMELL  2016-11-10T16:11:08+0100  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pandas.io.json import json_normalize\n",
    "# flatten json structure and create DataFrame\n",
    "df = json_normalize(data)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CODE_SMELL       8883\n",
       "BUG               951\n",
       "VULNERABILITY     146\n",
       "Name: type, dtype: int64"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.type.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>closeDate</th>\n",
       "      <th>component</th>\n",
       "      <th>creationDate</th>\n",
       "      <th>debt</th>\n",
       "      <th>effort</th>\n",
       "      <th>flows</th>\n",
       "      <th>hash</th>\n",
       "      <th>key</th>\n",
       "      <th>line</th>\n",
       "      <th>...</th>\n",
       "      <th>severity</th>\n",
       "      <th>status</th>\n",
       "      <th>subProject</th>\n",
       "      <th>tags</th>\n",
       "      <th>textRange.endLine</th>\n",
       "      <th>textRange.endOffset</th>\n",
       "      <th>textRange.startLine</th>\n",
       "      <th>textRange.startOffset</th>\n",
       "      <th>type</th>\n",
       "      <th>updateDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>306</th>\n",
       "      <td>vromero</td>\n",
       "      <td>NaN</td>\n",
       "      <td>net.java.openjdk:jdk9:langtools:src/jdk.compil...</td>\n",
       "      <td>2016-11-03T16:10:03+0100</td>\n",
       "      <td>10min</td>\n",
       "      <td>10min</td>\n",
       "      <td>[]</td>\n",
       "      <td>1fbe8f7a2d5b0d6eb6e60053c78abe06</td>\n",
       "      <td>AVgq2iKqc0c4MVP5bbg7</td>\n",
       "      <td>2854.0</td>\n",
       "      <td>...</td>\n",
       "      <td>MINOR</td>\n",
       "      <td>OPEN</td>\n",
       "      <td>net.java.openjdk:jdk9:langtools</td>\n",
       "      <td>[cwe]</td>\n",
       "      <td>2854.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>2854.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>VULNERABILITY</td>\n",
       "      <td>2016-11-03T16:10:03+0100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>308</th>\n",
       "      <td>vromero</td>\n",
       "      <td>NaN</td>\n",
       "      <td>net.java.openjdk:jdk9:langtools:src/jdk.compil...</td>\n",
       "      <td>2016-11-03T16:10:03+0100</td>\n",
       "      <td>10min</td>\n",
       "      <td>10min</td>\n",
       "      <td>[]</td>\n",
       "      <td>7835c4aaa972d2ee661a3fb912743535</td>\n",
       "      <td>AVgq2iKqc0c4MVP5bbg8</td>\n",
       "      <td>2890.0</td>\n",
       "      <td>...</td>\n",
       "      <td>MINOR</td>\n",
       "      <td>OPEN</td>\n",
       "      <td>net.java.openjdk:jdk9:langtools</td>\n",
       "      <td>[cwe]</td>\n",
       "      <td>2890.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>2890.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>VULNERABILITY</td>\n",
       "      <td>2016-11-03T16:10:03+0100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>716</th>\n",
       "      <td>jlahoda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>net.java.openjdk:jdk9:nashorn:src/jdk.scriptin...</td>\n",
       "      <td>2016-10-27T16:10:33+0200</td>\n",
       "      <td>10min</td>\n",
       "      <td>10min</td>\n",
       "      <td>[]</td>\n",
       "      <td>fa31896ede321997dd0bc03a6e788f6c</td>\n",
       "      <td>AVgGm45Ec0c4MVP5YKpW</td>\n",
       "      <td>81.0</td>\n",
       "      <td>...</td>\n",
       "      <td>MINOR</td>\n",
       "      <td>OPEN</td>\n",
       "      <td>net.java.openjdk:jdk9:nashorn</td>\n",
       "      <td>[error-handling]</td>\n",
       "      <td>81.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>81.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>VULNERABILITY</td>\n",
       "      <td>2016-10-27T16:10:33+0200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>956</th>\n",
       "      <td>ant</td>\n",
       "      <td>NaN</td>\n",
       "      <td>net.java.openjdk:jdk9:jdk:src/java.desktop/mac...</td>\n",
       "      <td>2016-10-14T16:12:15+0200</td>\n",
       "      <td>10min</td>\n",
       "      <td>10min</td>\n",
       "      <td>[]</td>\n",
       "      <td>5c5bde86fe26ed1be60a8b58f305bfb5</td>\n",
       "      <td>AVfDqqJob6bLTkrkqbLq</td>\n",
       "      <td>103.0</td>\n",
       "      <td>...</td>\n",
       "      <td>MINOR</td>\n",
       "      <td>OPEN</td>\n",
       "      <td>net.java.openjdk:jdk9:jdk</td>\n",
       "      <td>[error-handling]</td>\n",
       "      <td>103.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>103.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>VULNERABILITY</td>\n",
       "      <td>2016-10-14T16:12:15+0200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1015</th>\n",
       "      <td>ksrini</td>\n",
       "      <td>NaN</td>\n",
       "      <td>net.java.openjdk:jdk9:langtools:src/jdk.javado...</td>\n",
       "      <td>2016-10-14T16:12:15+0200</td>\n",
       "      <td>10min</td>\n",
       "      <td>10min</td>\n",
       "      <td>[]</td>\n",
       "      <td>2dd9b2ef22845fa74d57e565807327fb</td>\n",
       "      <td>AVfDqktTb6bLTkrkqbKa</td>\n",
       "      <td>284.0</td>\n",
       "      <td>...</td>\n",
       "      <td>MINOR</td>\n",
       "      <td>OPEN</td>\n",
       "      <td>net.java.openjdk:jdk9:langtools</td>\n",
       "      <td>[cwe]</td>\n",
       "      <td>284.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>284.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>VULNERABILITY</td>\n",
       "      <td>2016-10-14T16:12:15+0200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       author closeDate                                          component  \\\n",
       "306   vromero       NaN  net.java.openjdk:jdk9:langtools:src/jdk.compil...   \n",
       "308   vromero       NaN  net.java.openjdk:jdk9:langtools:src/jdk.compil...   \n",
       "716   jlahoda       NaN  net.java.openjdk:jdk9:nashorn:src/jdk.scriptin...   \n",
       "956       ant       NaN  net.java.openjdk:jdk9:jdk:src/java.desktop/mac...   \n",
       "1015   ksrini       NaN  net.java.openjdk:jdk9:langtools:src/jdk.javado...   \n",
       "\n",
       "                  creationDate   debt effort flows  \\\n",
       "306   2016-11-03T16:10:03+0100  10min  10min    []   \n",
       "308   2016-11-03T16:10:03+0100  10min  10min    []   \n",
       "716   2016-10-27T16:10:33+0200  10min  10min    []   \n",
       "956   2016-10-14T16:12:15+0200  10min  10min    []   \n",
       "1015  2016-10-14T16:12:15+0200  10min  10min    []   \n",
       "\n",
       "                                  hash                   key    line  \\\n",
       "306   1fbe8f7a2d5b0d6eb6e60053c78abe06  AVgq2iKqc0c4MVP5bbg7  2854.0   \n",
       "308   7835c4aaa972d2ee661a3fb912743535  AVgq2iKqc0c4MVP5bbg8  2890.0   \n",
       "716   fa31896ede321997dd0bc03a6e788f6c  AVgGm45Ec0c4MVP5YKpW    81.0   \n",
       "956   5c5bde86fe26ed1be60a8b58f305bfb5  AVfDqqJob6bLTkrkqbLq   103.0   \n",
       "1015  2dd9b2ef22845fa74d57e565807327fb  AVfDqktTb6bLTkrkqbKa   284.0   \n",
       "\n",
       "                ...            severity status  \\\n",
       "306             ...               MINOR   OPEN   \n",
       "308             ...               MINOR   OPEN   \n",
       "716             ...               MINOR   OPEN   \n",
       "956             ...               MINOR   OPEN   \n",
       "1015            ...               MINOR   OPEN   \n",
       "\n",
       "                           subProject              tags textRange.endLine  \\\n",
       "306   net.java.openjdk:jdk9:langtools             [cwe]            2854.0   \n",
       "308   net.java.openjdk:jdk9:langtools             [cwe]            2890.0   \n",
       "716     net.java.openjdk:jdk9:nashorn  [error-handling]              81.0   \n",
       "956         net.java.openjdk:jdk9:jdk  [error-handling]             103.0   \n",
       "1015  net.java.openjdk:jdk9:langtools             [cwe]             284.0   \n",
       "\n",
       "     textRange.endOffset textRange.startLine textRange.startOffset  \\\n",
       "306                 51.0              2854.0                  46.0   \n",
       "308                 42.0              2890.0                  27.0   \n",
       "716                 42.0                81.0                  27.0   \n",
       "956                 55.0               103.0                  40.0   \n",
       "1015                30.0               284.0                  19.0   \n",
       "\n",
       "               type                updateDate  \n",
       "306   VULNERABILITY  2016-11-03T16:10:03+0100  \n",
       "308   VULNERABILITY  2016-11-03T16:10:03+0100  \n",
       "716   VULNERABILITY  2016-10-27T16:10:33+0200  \n",
       "956   VULNERABILITY  2016-10-14T16:12:15+0200  \n",
       "1015  VULNERABILITY  2016-10-14T16:12:15+0200  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "v = df[df.type == 'VULNERABILITY']\n",
    "v.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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></th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>severity</th>\n",
       "      <th>component</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>BLOCKER</th>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.base/share/classes/com/sun/security/ntlm/NTLM.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"28\" valign=\"top\">MINOR</th>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.base/share/classes/java/util/concurrent/LinkedTransferQueue.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.base/share/classes/java/util/concurrent/PriorityBlockingQueue.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.base/share/classes/java/util/zip/ZipFile.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:langtools:src/jdk.jshell/share/classes/jdk/internal/jshell/tool/ConsoleIOContext.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.base/share/classes/sun/security/util/AnchorCertificates.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/macosx/classes/sun/lwawt/macosx/CAccessibility.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/javax/imageio/ImageIO.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/javax/imageio/spi/ServiceRegistry.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/javax/imageio/stream/FileCacheImageInputStream.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/javax/imageio/stream/FileCacheImageOutputStream.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/applet/Main.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/awt/SunToolkit.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/awt/util/PerformanceLogger.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/font/CreatedFontTracker.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/font/FileFont.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/font/Type1Font.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/print/PrintJob2D.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/print/ServiceDialog.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/unix/classes/sun/awt/X11/InfoWindow.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/unix/classes/sun/print/PrintServiceLookupProvider.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/unix/classes/sun/print/UnixPrintJob.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/jdk.jlink/share/classes/jdk/tools/jlink/builder/DefaultImageBuilder.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/jdk.jlink/share/classes/jdk/tools/jlink/internal/ImagePluginStack.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:langtools:src/jdk.compiler/share/classes/com/sun/tools/javac/file/Locations.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:langtools:src/jdk.javadoc/share/classes/jdk/javadoc/internal/doclets/toolkit/Configuration.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.base/share/classes/java/io/DeleteOnExitHook.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.base/share/classes/com/sun/java/util/jar/pack/UnpackerImpl.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:nashorn:src/jdk.scripting.nashorn.shell/share/classes/jdk/nashorn/tools/jjs/Console.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">MAJOR</th>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.httpclient/share/classes/sun/net/httpclient/hpack/Huffman.java</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.base/share/classes/sun/security/tools/keytool/Main.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"7\" valign=\"top\">MINOR</th>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/jdk.jlink/share/classes/jdk/tools/jlink/internal/ImageFileCreator.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.httpclient/share/classes/java/net/http/WSTransmitter.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/jdk.attach/aix/classes/sun/tools/attach/VirtualMachineImpl.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/print/PSPrinterJob.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/jdk.jartool/share/classes/sun/tools/jar/Main.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/jdk.attach/linux/classes/sun/tools/attach/VirtualMachineImpl.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.base/share/classes/sun/nio/fs/AbstractWatchService.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MAJOR</th>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/jdk.jartool/share/classes/sun/security/tools/jarsigner/Main.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"7\" valign=\"top\">MINOR</th>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.base/share/classes/java/util/concurrent/DelayQueue.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.base/share/classes/java/util/concurrent/ThreadPoolExecutor.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.base/share/classes/jdk/internal/module/SystemModules.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:corba:src/java.corba/share/classes/com/sun/corba/se/impl/naming/pcosnaming/ServantManagerImpl.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/font/SunFontManager.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/java/awt/Font.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/java/awt/Desktop.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MAJOR</th>\n",
       "      <th>net.java.openjdk:jdk9:langtools:src/jdk.jshell/share/classes/jdk/jshell/execution/Util.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">MINOR</th>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/javax/swing/TimerQueue.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/awt/image/OffScreenImageSource.java</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MAJOR</th>\n",
       "      <th>net.java.openjdk:jdk9:langtools:src/jdk.jshell/share/classes/jdk/jshell/execution/ExecutionControlForwarder.java</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">MINOR</th>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.base/share/classes/java/util/concurrent/ScheduledThreadPoolExecutor.java</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.logging/share/classes/java/util/logging/FileHandler.java</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">MAJOR</th>\n",
       "      <th>net.java.openjdk:jdk9:langtools:src/jdk.jshell/share/classes/jdk/jshell/execution/DirectExecutionControl.java</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.httpclient/share/classes/java/net/http/AsyncSSLDelegate.java</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">MINOR</th>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.base/aix/classes/sun/nio/ch/AixPollPort.java</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.base/linux/classes/sun/nio/ch/EPollPort.java</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.base/macosx/classes/sun/nio/ch/KQueuePort.java</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.base/share/classes/com/sun/java/util/jar/pack/Driver.java</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.prefs/unix/classes/java/util/prefs/FileSystemPreferences.java</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MAJOR</th>\n",
       "      <th>net.java.openjdk:jdk9:jdk:src/java.base/share/classes/java/util/concurrent/CompletableFuture.java</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>80 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                             type\n",
       "severity component                                               \n",
       "BLOCKER  net.java.openjdk:jdk9:jdk:src/java.base/share/c...     1\n",
       "MINOR    net.java.openjdk:jdk9:jdk:src/java.base/share/c...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.base/share/c...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.base/share/c...     1\n",
       "         net.java.openjdk:jdk9:langtools:src/jdk.jshell/...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.base/share/c...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/maco...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/shar...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/shar...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/shar...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/shar...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/shar...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/shar...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/shar...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/shar...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/shar...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/shar...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/shar...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/shar...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/unix...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/unix...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/unix...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/jdk.jlink/share/c...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/jdk.jlink/share/c...     1\n",
       "         net.java.openjdk:jdk9:langtools:src/jdk.compile...     1\n",
       "         net.java.openjdk:jdk9:langtools:src/jdk.javadoc...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.base/share/c...     1\n",
       "         net.java.openjdk:jdk9:jdk:src/java.base/share/c...     1\n",
       "         net.java.openjdk:jdk9:nashorn:src/jdk.scripting...     1\n",
       "MAJOR    net.java.openjdk:jdk9:jdk:src/java.httpclient/s...     1\n",
       "...                                                           ...\n",
       "         net.java.openjdk:jdk9:jdk:src/java.base/share/c...     2\n",
       "MINOR    net.java.openjdk:jdk9:jdk:src/jdk.jlink/share/c...     2\n",
       "         net.java.openjdk:jdk9:jdk:src/java.httpclient/s...     2\n",
       "         net.java.openjdk:jdk9:jdk:src/jdk.attach/aix/cl...     2\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/shar...     2\n",
       "         net.java.openjdk:jdk9:jdk:src/jdk.jartool/share...     2\n",
       "         net.java.openjdk:jdk9:jdk:src/jdk.attach/linux/...     2\n",
       "         net.java.openjdk:jdk9:jdk:src/java.base/share/c...     2\n",
       "MAJOR    net.java.openjdk:jdk9:jdk:src/jdk.jartool/share...     2\n",
       "MINOR    net.java.openjdk:jdk9:jdk:src/java.base/share/c...     2\n",
       "         net.java.openjdk:jdk9:jdk:src/java.base/share/c...     2\n",
       "         net.java.openjdk:jdk9:jdk:src/java.base/share/c...     2\n",
       "         net.java.openjdk:jdk9:corba:src/java.corba/shar...     2\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/shar...     2\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/shar...     2\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/shar...     2\n",
       "MAJOR    net.java.openjdk:jdk9:langtools:src/jdk.jshell/...     2\n",
       "MINOR    net.java.openjdk:jdk9:jdk:src/java.desktop/shar...     2\n",
       "         net.java.openjdk:jdk9:jdk:src/java.desktop/shar...     2\n",
       "MAJOR    net.java.openjdk:jdk9:langtools:src/jdk.jshell/...     3\n",
       "MINOR    net.java.openjdk:jdk9:jdk:src/java.base/share/c...     3\n",
       "         net.java.openjdk:jdk9:jdk:src/java.logging/shar...     3\n",
       "MAJOR    net.java.openjdk:jdk9:langtools:src/jdk.jshell/...     4\n",
       "         net.java.openjdk:jdk9:jdk:src/java.httpclient/s...     4\n",
       "MINOR    net.java.openjdk:jdk9:jdk:src/java.base/aix/cla...     4\n",
       "         net.java.openjdk:jdk9:jdk:src/java.base/linux/c...     4\n",
       "         net.java.openjdk:jdk9:jdk:src/java.base/macosx/...     4\n",
       "         net.java.openjdk:jdk9:jdk:src/java.base/share/c...     5\n",
       "         net.java.openjdk:jdk9:jdk:src/java.prefs/unix/c...     9\n",
       "MAJOR    net.java.openjdk:jdk9:jdk:src/java.base/share/c...     9\n",
       "\n",
       "[80 rows x 1 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "v2 = v.groupby(['severity', 'component'])[['type']].count()\n",
    "v2.sort_values(by='type')"
   ]
  }
 ],
 "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
}