jira-data-download-and-analysis.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Download and Analyze Jira Issues\n",
    "\n",
    "by Nils Ackermann\n",
    "\n",
    "## Introduction\n",
    "\n",
    "Jira offers great reporting capabilities on its GUI. Nevertheless there are certain advanced reporting requirements that cannot be easily fulfilled. Also you might need to feed your Jira data into a data warehouse or other surrounding systems. It is always good to understand how to export your data from your Jira instance to process it elsewhere. \n",
    "\n",
    "In this notebook you will learn the following:\n",
    "\n",
    "- How to establish a secure connection to your personal Jira instance\n",
    "- How to download issue data\n",
    "- How to download board and sprint data\n",
    "- How to download project data\n",
    "- How to transform Jira data into a [Pandas](https://pandas.pydata.org) dataframe\n",
    "- How to visualize your Jira data with [Plotly](https://plotly.com/python/basic-charts/)\n",
    "\n",
    "The notebook uses functionality described in those API:\n",
    "\n",
    "- https://developer.atlassian.com/cloud/jira/platform/rest/v3/intro/\n",
    "- https://developer.atlassian.com/cloud/jira/software/rest/api-group-other-operations/\n",
    "- https://docs.atlassian.com/software/jira/docs/api/REST/7.6.1/\n",
    "\n",
    "**Remark:** Code has been testes with Jira Cloud.\n",
    "\n",
    "## 0. Setup Your Python Virtual Environment\n",
    "\n",
    "It is best to create a separate environment for each notebook / project that you orun. Run the following commands in sequence:\n",
    "\n",
    "- Create a virtual Python environment in the folder of this notebook:\n",
    "\n",
    "````\n",
    "python3 -m venv venv\n",
    "````\n",
    "\n",
    "- Activate the virtual environment:\n",
    "\n",
    "````\n",
    "source venv/bin/activate\n",
    "````\n",
    "\n",
    "- Install all necessary libraries:\n",
    "\n",
    "````\n",
    "pip install -r requirements.txt \n",
    "````\n",
    "\n",
    "- Change to the Python Kernel in your IDE that you use to open the Jupyter Notebook like Visual Studio Code\n",
    "\n",
    "- Create a .env file that will hold your Jira credentials:\n",
    "\n",
    "````\n",
    "touch ./.env\n",
    "````\n",
    "\n",
    "- The .env file must hold the following environment variables. The file content should look something like this:\n",
    "\n",
    "````\n",
    "export JIRA-USER=<your-jira-user-email-address>\n",
    "export JIRA-API-KEY=<your-jira-api-key>\n",
    "export JIRA-SERVER=<your-jira-server-url>\n",
    "````"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Imports and Variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import plotly.graph_objects as go\n",
    "from plotly.subplots import make_subplots\n",
    "import plotly.express as px\n",
    "from dotenv import load_dotenv\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load environment variable holding your secret credentials defined in the .env file\n",
    "load_dotenv()\n",
    "user = os.environ.get('JIRA-USER')\n",
    "apikey = os.environ.get('JIRA-API-KEY')\n",
    "server = os.environ.get('JIRA-SERVER')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define a few standard colors for the issue types\n",
    "COLOR_TASK = 'dodgerblue'\n",
    "COLOR_STORY = 'green'\n",
    "COLOR_SUBTASK = 'cyan'\n",
    "COLOR_BUG = 'red'\n",
    "COLOR_EPIC = 'darkmagenta'\n",
    "\n",
    "# Mapping of Jira colors to Python colors\n",
    "COLOR_DICT = {\n",
    "    'purple':'purple',\n",
    "    'dark_blue':'darkblue',\n",
    "    'yellow':'orange',\n",
    "    'grey':'grey',\n",
    "    'dark_purple':'purple',\n",
    "    'blue':'blue',\n",
    "    'dark_orange':'orange',\n",
    "    'dark_yellow':'gold',\n",
    "    'blue-gray':'dodgerblue',\n",
    "    'green':'green'\n",
    "}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# A subset of fields that will be selected from the many fields available in Jira\n",
    "RELEVANT_FIELDS = ['fields.project.name',\n",
    "    #'fields.project.key' # This attribute causes issues, unclear why, must investigate\n",
    "    'fields.project.id',\n",
    "    'id',\n",
    "    'key',\n",
    "    'fields.summary',\n",
    "    'fields.description',\n",
    "    'fields.issuetype.name',\n",
    "    'fields.customfield_10011',\n",
    "    'fields.customfield_10017',\n",
    "    'fields.created',\n",
    "    'fields.status.name',\n",
    "    'fields.status.statusCategory.key',\n",
    "    'fields.status.statusCategory.name',\n",
    "    'fields.status.statusCategory.colorName',\n",
    "    'fields.priority.name',\n",
    "    'fields.customfield_10026',\n",
    "    'fields.creator.displayName',\n",
    "    'fields.assignee.displayName',\n",
    "    'fields.reporter.displayName',\n",
    "    'fields.issuetype.subtask',\n",
    "    'fields.fixVersions',\n",
    "    'fields.parent.id',\n",
    "    'fields.parent.key',\n",
    "    'fields.parent.fields.summary',\n",
    "    'fields.parent.fields.status.name',\n",
    "    'fields.parent.fields.status.statusCategory.key',\n",
    "    'fields.parent.fields.status.statusCategory.name',\n",
    "    'fields.parent.fields.status.statusCategory.colorName',\n",
    "    'fields.parent.fields.priority.name',\n",
    "    'fields.parent.fields.issuetype.name',\n",
    "    'fields.parent.fields.issuetype.subtask',\n",
    "    'fields.aggregatetimespent',\n",
    "    'fields.resolution',\n",
    "    'fields.resolutiondate',\n",
    "    'fields.lastViewed',\n",
    "    'fields.labels',\n",
    "    'fields.aggregatetimeoriginalestimate',\n",
    "    'fields.aggregatetimeestimate',\n",
    "    'fields.timeestimate',\n",
    "    'fields.updated',\n",
    "    'fields.aggregateprogress.progress',\n",
    "    'fields.aggregateprogress.total',\n",
    "    'fields.aggregateprogress.percent',\n",
    "    'fields.progress.percent',\n",
    "    'fields.timeoriginalestimate',\n",
    "    'fields.duedate',\n",
    "    'fields.progress.progress',\n",
    "    'fields.progress.total',\n",
    "    'fields.resolution.name',\n",
    "    'fields.statuscategorychangedate',\n",
    "    'fields.status.statusCategory.colorName']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dictionary to change the standard Jira field name to our own wording\n",
    "COLUMN_NAME_DICT = {\n",
    "    'fields.project.name': 'project_name',\n",
    "    #'fields.project.key': 'project_key', # This attribute causes issues, unclear why, must investigate\n",
    "    'fields.project.id': 'project_id',\n",
    "    'id': 'issue_id',\n",
    "    'key': 'issue_key',\n",
    "    'fields.summary': 'summary', \n",
    "    'fields.description': 'description', \n",
    "    'fields.issuetype.name': 'issuetype', \n",
    "    'fields.customfield_10011': 'epic_name', \n",
    "    'fields.customfield_10017': 'epic_color', \n",
    "    'fields.created': 'created', \n",
    "    'fields.status.name': 'status', \n",
    "    'fields.status.statusCategory.key': 'status_cat_key', \n",
    "    'fields.status.statusCategory.name': 'status_cat_name', \n",
    "    'fields.status.statusCategory.colorName': 'status_cat_color', \n",
    "    'fields.priority.name': 'prio', \n",
    "    'fields.customfield_10026': 'storypoints', \n",
    "    'fields.creator.displayName': 'creator', \n",
    "    'fields.assignee.displayName': 'assignee', \n",
    "    'fields.reporter.displayName': 'reporter', \n",
    "    'fields.issuetype.subtask': 'is_subtask', \n",
    "    'fields.fixVersions': 'fix_versions', \n",
    "    'fields.parent.id': 'parent_id', \n",
    "    'fields.parent.key': 'parent_key', \n",
    "    'fields.parent.fields.summary': 'parent_summary', \n",
    "    'fields.parent.fields.status.name': 'parent_status', \n",
    "    'fields.parent.fields.status.statusCategory.key': 'parent_status_cat_key', \n",
    "    'fields.parent.fields.status.statusCategory.name': 'parent_status_cat', \n",
    "    'fields.parent.fields.status.statusCategory.colorName': 'parent_status_cat_color', \n",
    "    'fields.parent.fields.priority.name': 'parent_prio', \n",
    "    'fields.parent.fields.issuetype.name': 'parent_issuetype', \n",
    "    'fields.parent.fields.issuetype.subtask': 'parent_is_subtask', \n",
    "    'fields.aggregatetimespent': 'aggregatetimespent', \n",
    "    'fields.resolution': 'resolution', \n",
    "    'fields.resolutiondate': 'resolution_date', \n",
    "    'fields.lastViewed': 'last_viewed', \n",
    "    'fields.labels': 'labels', \n",
    "    'fields.aggregatetimeoriginalestimate': 'aggregatetimeoriginalestimate', \n",
    "    'fields.aggregatetimeestimate': 'aggregatetimeestimate', \n",
    "    'fields.timeestimate': 'timeestimate', \n",
    "    'fields.updated': 'updated', \n",
    "    'fields.aggregateprogress.progress': 'aggregateprogress', \n",
    "    'fields.aggregateprogress.total': 'aggregateprogress_total', \n",
    "    'fields.aggregateprogress.percent': 'aggregateprogress_percent', \n",
    "    'fields.progress.percent': 'progress_percent', \n",
    "    'fields.timeoriginalestimate': 'timeoriginalestimate', \n",
    "    'fields.duedate': 'duedate', \n",
    "    'fields.progress.progress': 'progress', \n",
    "    'fields.progress.total': 'progress_total', \n",
    "    'fields.resolution.name': 'resolution', \n",
    "    'fields.statuscategorychangedate': 'status_cat_changedate', \n",
    "    'fields.status.statusCategory.colorName': 'status_cat_color' \n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Helper Functions\n",
    "\n",
    "Various functions that are used throughtout the notebook that connect to the Jira server and download data. All functions return the data as a Pandas dataframe. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def jira_auth_get_connect(url_path, *args, **kwargs):\n",
    "\n",
    "    # Version: 1.00\n",
    "    # Last Updated: Feb-11-2023\n",
    "\n",
    "    # Establishes an authenticated connection to Jira API with the given URL\n",
    "    # Parameters must be added as variable param which should include a dictionary\n",
    "    # (example: params = {'jql':'project=abc','maxResults':80})\n",
    "\n",
    "    import base64\n",
    "    import requests\n",
    "    import json\n",
    "    from urllib.error import HTTPError\n",
    "    body = ''\n",
    "    if kwargs.get('body', None) is not None:\n",
    "            body = kwargs.get('body')\n",
    "            body = json.dumps(body)\n",
    "    url = server+url_path\n",
    "    auth_string = user + \":\" + apikey\n",
    "    signature = base64.b64encode(bytes(auth_string, \"utf-8\")).decode(\"utf-8\")\n",
    "    headers = {\n",
    "        \"Accept\":\"application/json\",\n",
    "        \"Content-Type\":\"application/json\",\n",
    "        \"Authorization\":\"Basic \" + signature\n",
    "    }\n",
    "    try:\n",
    "        if kwargs.get('param', None) is not None:\n",
    "            params = kwargs.get('param')\n",
    "            response = requests.get(url, params, headers=headers)\n",
    "        else:\n",
    "            response = requests.get(url, headers=headers)\n",
    "        response.raise_for_status()\n",
    "        print(f'HTTP connection {url} successful!')\n",
    "        return response\n",
    "    except HTTPError as http_err:\n",
    "        print(f'HTTP error occurred: {http_err}')\n",
    "        return http_err\n",
    "    except Exception as err:\n",
    "        print(f'Other error occurred: {err}')\n",
    "        return err"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def jira_get_issues(jql = '') -> pd.DataFrame:\n",
    "\n",
    "    # Version: 1.00\n",
    "    # Last Updated: Feb-11-2023\n",
    "\n",
    "    # Gets all issues for the given query. Jira REST API will be called multiple times\n",
    "    # until all issues are fetched. Note: maximum block size is 100. Returns a Pandas dataframe.\n",
    "\n",
    "    import json\n",
    "    all_issues = []\n",
    "    block_size = 100\n",
    "    start_position = 0\n",
    "    has_next = True\n",
    "    while has_next:\n",
    "        #params = {'jql':jql, 'startAt':start_position, 'maxResults':block_size}\n",
    "        params = {'startAt':start_position, 'maxResults':block_size}\n",
    "        if jql != '':\n",
    "            params['jql'] = jql\n",
    "        response = jira_auth_get_connect('/rest/api/latest/search',param=params)\n",
    "        #print(json.dumps(json.loads(response.text)['issues'], sort_keys=True, indent=4, separators=(\",\", \": \")))\n",
    "        issues_in_block = json.loads(response.text)['issues']\n",
    "        all_issues.extend(issues_in_block)\n",
    "        if len(issues_in_block) == 0:\n",
    "            has_next = False\n",
    "        start_position += block_size\n",
    "    df_issues = pd.json_normalize(all_issues)\n",
    "    return df_issues"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def jira_get_all_projects() -> pd.DataFrame:\n",
    "\n",
    "    # Version: 1.00\n",
    "    # Last Updated: Feb-11-2023\n",
    "\n",
    "    # Gets all projects. Jira REST API will be called multiple times\n",
    "    # until all projects are fetched. Note: maximum block size is 100.\n",
    "    # Returns a Pandas dataframe.\n",
    "\n",
    "    import json\n",
    "    all_projects = []\n",
    "    block_size = 100\n",
    "    start_position = 0\n",
    "    has_next = True\n",
    "    while has_next:\n",
    "        params = {'startAt':start_position, 'maxResults':block_size}\n",
    "        response = jira_auth_get_connect('/rest/api/latest/project/search',param=params)\n",
    "        #print(json.dumps(json.loads(response.text)['values'], sort_keys=True, indent=4, separators=(\",\", \": \")))\n",
    "        projects_in_block = json.loads(response.text)['values']\n",
    "        all_projects.extend(projects_in_block)\n",
    "        if len(projects_in_block) == 0:\n",
    "            has_next = False\n",
    "        start_position += block_size\n",
    "    df_projects = pd.json_normalize(all_projects)\n",
    "    return df_projects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def jira_get_versions(project_key) -> pd.DataFrame:\n",
    "\n",
    "    # Version: 1.00\n",
    "    # Last Updated: Feb-11-2023\n",
    "\n",
    "    # Gets all versions for the project key. Jira REST API will be called multiple times\n",
    "    # until all versions are fetched. Returns a Pandas dataframe.\n",
    "\n",
    "    import json\n",
    "    response = jira_auth_get_connect('/rest/api/latest/project/'+project_key)\n",
    "    #print(json.dumps(json.loads(response.text)['versions'], sort_keys=True, indent=4, separators=(\",\", \": \")))\n",
    "    all_versions = json.loads(response.text)['versions']\n",
    "    df_versions = pd.json_normalize(all_versions)\n",
    "    return df_versions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def jira_get_boards(project_key = '') -> pd.DataFrame:\n",
    "\n",
    "    # Version: 1.1\n",
    "    # Last Updated: April-02-2023\n",
    "\n",
    "    # Gets all boards. Jira REST API will be called multiple times\n",
    "    # until all boards are fetched. Note: maximum block size is 100.\n",
    "    # Returns a Pandas dataframe.\n",
    "\n",
    "    import json\n",
    "    all_boards = []\n",
    "    block_size = 100\n",
    "    start_position = 0\n",
    "    has_next = True\n",
    "    while has_next:\n",
    "        params = {'startAt':start_position, 'maxResults':block_size}\n",
    "        if project_key != '':\n",
    "            params['projectKeyOrId'] = project_key\n",
    "        response = jira_auth_get_connect('/rest/agile/1.0/board',param=params)\n",
    "        #print(json.dumps(json.loads(response.text)['values'], sort_keys=True, indent=4, separators=(\",\", \": \")))\n",
    "        boards_in_block = json.loads(response.text)['values']\n",
    "        all_boards.extend(boards_in_block)\n",
    "        if len(boards_in_block) == 0:\n",
    "            has_next = False\n",
    "        start_position += block_size\n",
    "    df_boards = pd.json_normalize(all_boards)\n",
    "    return df_boards"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def jira_get_sprints(board_id) -> pd.DataFrame:\n",
    "\n",
    "    # Version: 1.00\n",
    "    # Last Updated: Feb-11-2023\n",
    "\n",
    "    # Gets all sprints for the given board id. Jira REST API will be called multiple times\n",
    "    # until all sprints are fetched. Note: maximum block size is 100.\n",
    "    # Returns a Pandas dataframe.\n",
    "\n",
    "    import requests\n",
    "    import json\n",
    "    all_sprints = []\n",
    "    block_size = 100\n",
    "    start_position = 0\n",
    "    has_next = True\n",
    "    while has_next:\n",
    "        params = {'startAt':start_position, 'maxResults':block_size}\n",
    "        response = jira_auth_get_connect(f'/rest/agile/1.0/board/{board_id}/sprint',param=params)\n",
    "        if type(response) is not requests.exceptions.HTTPError:\n",
    "            sprints_in_block = json.loads(response.text)['values']\n",
    "            all_sprints.extend(sprints_in_block)\n",
    "            if len(sprints_in_block) == 0:\n",
    "                has_next = False\n",
    "            start_position += block_size\n",
    "        else:\n",
    "            break\n",
    "    df_sprints = pd.json_normalize(all_sprints)\n",
    "    return df_sprints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def jira_get_backlog_issues(board_id) -> pd.DataFrame:\n",
    "    \n",
    "    # Version: 1.00\n",
    "    # Last Updated: Feb-11-2023\n",
    "\n",
    "    # Gets all issues from backlog for the given board id. Jira REST API will be called multiple times\n",
    "    # until all issues of backlog are fetched. Note: maximum block size is 100.\n",
    "    # Returns a Pandas dataframe.  \n",
    "\n",
    "    import requests\n",
    "    all_backlog_issues = []\n",
    "    block_size = 100\n",
    "    start_position = 0\n",
    "    has_next = True\n",
    "    while has_next:\n",
    "        params = {'startAt':start_position, 'maxResults':block_size}\n",
    "        response = jira_auth_get_connect(f'/rest/agile/1.0/board/{board_id}/backlog',param=params)\n",
    "        if type(response) is not requests.exceptions.HTTPError:\n",
    "            backlogs_in_block = json.loads(response.text)['issues']\n",
    "            all_backlog_issues.extend(backlogs_in_block)\n",
    "            if len(backlogs_in_block) == 0:\n",
    "                has_next = False\n",
    "            start_position += block_size\n",
    "        else:\n",
    "            break\n",
    "    df_backlog_issues = pd.json_normalize(all_backlog_issues)\n",
    "    return df_backlog_issues"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def jira_get_sprint_issues(sprint_id) -> pd.DataFrame:\n",
    "\n",
    "    # Version: 1.00\n",
    "    # Last Updated: Feb-11-2023\n",
    "\n",
    "    # Gets all issues from sprint for the given sprint id. Jira REST API will be called multiple times\n",
    "    # until all issues of sprint are fetched. Note: maximum block size is 100.\n",
    "    # Returns a Pandas dataframe.  \n",
    "\n",
    "    import requests\n",
    "    import json\n",
    "    all_issues = []\n",
    "    block_size = 100\n",
    "    start_position = 0\n",
    "    has_next = True\n",
    "    while has_next:\n",
    "        params = {'startAt':start_position, 'maxResults':block_size}\n",
    "        response = jira_auth_get_connect(f'/rest/agile/1.0/sprint/{sprint_id}/issue',param=params)\n",
    "        if type(response) is not requests.exceptions.HTTPError:\n",
    "            issues_in_block = json.loads(response.text)['issues']\n",
    "            all_issues.extend(issues_in_block)\n",
    "            if len(issues_in_block) == 0:\n",
    "                has_next = False\n",
    "            start_position += block_size\n",
    "        else:\n",
    "            break\n",
    "    df_issues = pd.json_normalize(all_issues)\n",
    "    return df_issues"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def jira_get_board_issues(board_id) -> pd.DataFrame:\n",
    "\n",
    "    # Version: 1.00\n",
    "    # Last Updated: Feb-11-2023\n",
    "\n",
    "    # Gets basic data for all issues including their sprint reference \n",
    "    # for the given board id. Returns a Pandas dataframe.  \n",
    "\n",
    "    df_all_sprints = jira_get_sprints(board_id = board_id)\n",
    "    sprint_ids = df_all_sprints.id.to_list()\n",
    "    issue_rows = []\n",
    "    for sprint_id in sprint_ids:\n",
    "        df_issues = jira_get_sprint_issues(sprint_id=sprint_id)[['id','key','fields.project.name','fields.project.id','fields.issuetype.name','fields.status.name','fields.status.statusCategory.name','fields.priority.name','fields.customfield_10026']]\n",
    "        df_issues['sprint']=sprint_id\n",
    "        issues = df_issues.values.tolist()\n",
    "        issue_rows.extend(issues)\n",
    "    df_board_issues = pd.DataFrame(issue_rows)\n",
    "    df_board_issues.columns = ['issue_id','issue_key','project_name','project_id','issuetype','status','status_cat_name','prio','storypoints','sprint_id']\n",
    "    df_board_issues_enhanced = pd.merge(df_board_issues, df_all_sprints, how='left', left_on='sprint_id', right_on = 'id')\n",
    "    df_board_issues_enhanced.drop(['id','self','goal'], axis=1, inplace=True)\n",
    "    df_board_issues_enhanced.rename(columns = {'name':'sprint_name','state':'sprint_state','startDate':'sprint_start_date','endDate':'sprint_end_date','completeDate':'sprint_complete_date','originBoardId':'board_id'}, inplace = True)\n",
    "    return df_board_issues_enhanced"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Jira Issues\n",
    "\n",
    "Issues are at the core of Jira. Therefore we will start with the download of all issues. "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1 Get a Specific Issue\n",
    "\n",
    "The simplest case to retrieve one particular issue based on the issue key."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "response = jira_auth_get_connect('/rest/agile/1.0/issue/BEAS-298')\n",
    "df_issue = pd.json_normalize(json.loads(response.text))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2 Get Multiple Issues\n",
    "\n",
    "Get issues up to maxResults 100:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#params = {'jql':'project = abc','maxResults':80}\n",
    "#response = jira_auth_get_connect('/rest/api/3/search',param=params)\n",
    "#print(json.dumps(json.loads(response.text)['issues'], sort_keys=True, indent=4, separators=(\",\", \": \")))\n",
    "#ll_issues = json.loads(response.text)['issues']\n",
    "#df_issues = pd.json_normalize(all_issues)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3 Get All Issues for JQL Query\n",
    "\n",
    "Going forward, we will use the custom functions defined above. All functions are written in a way that they retrieve all data in batches. The first query retrieves all issues that match the given jql query. If the query is left blank, all issues available in your Jira instance are returned. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_issues = jira_get_issues('project=abc')\n",
    "df_issues = jira_get_issues()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the following code block, we will enhance the raw Jira issue data as following:\n",
    "\n",
    "- Only a subset of fields is retained (all fields in variable RELEVANT_FIELDS)\n",
    "- The selected fields are renamed (in line with COLUMN_NAME_DICT dictionary)\n",
    "- The issue data is merged with the epic data\n",
    "- Jira colors are replaced with more appropriate colors\n",
    "\n",
    "The resulting dataframe df_issues_enhanced is used in the subsequent code snippets of this Jupyter Notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_issues_enhanced = df_issues[df_issues.columns[df_issues.columns.isin(RELEVANT_FIELDS)]]\n",
    "df_issues_enhanced.rename(columns=COLUMN_NAME_DICT, inplace=True)\n",
    "# Adding the associated epic name and color to every issue (if available)\n",
    "df_epics = df_issues_enhanced[['issue_id','epic_name','epic_color']]\n",
    "df_issues_enhanced = pd.merge(df_issues_enhanced, df_epics, how='left', left_on='parent_id', right_on = 'issue_id')\n",
    "df_issues_enhanced.loc[df_issues_enhanced['epic_name_x'].isnull(),'epic_name_x'] = df_issues_enhanced['epic_name_y']\n",
    "df_issues_enhanced.loc[df_issues_enhanced['epic_color_x'].isnull(),'epic_color_x'] = df_issues_enhanced['epic_color_y']\n",
    "df_issues_enhanced.drop(['issue_id_y','epic_name_y','epic_color_y'], axis=1, inplace=True)\n",
    "df_issues_enhanced.rename(columns = {'issue_id_x':'issue_id','epic_name_x':'epic_name','epic_color_x':'epic_color'}, inplace = True)\n",
    "df_issues_enhanced.epic_name = df_issues_enhanced.epic_name.fillna('No Epic')\n",
    "# Replace colors\n",
    "df_issues_enhanced['epic_color'] = df_issues_enhanced['epic_color'].apply(lambda x: COLOR_DICT.get(x,'white'))\n",
    "df_issues_enhanced['status_cat_color'] = df_issues_enhanced['status_cat_color'].apply(lambda x: COLOR_DICT.get(x,'white'))\n",
    "df_issues_enhanced['parent_status_cat_color'] = df_issues_enhanced['parent_status_cat_color'].apply(lambda x: COLOR_DICT.get(x,'white'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4 Basic Visualization of Issues"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract colors for status category\n",
    "STATUS_CAT_COLOR_DICT = df_issues_enhanced[['status_cat_name','status_cat_color']].drop_duplicates().set_index('status_cat_name')['status_cat_color'].to_dict()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.1 Show Storypoints Grouped by Issue Type\n",
    "\n",
    "**Purpose:** As a program manager I want to see the total volume of work (in story points) per type or work (e.g. tasks, bug-fixing) so that I can better understand where the teams spent the majority of their time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query\n",
    "df_issues_per_type = df_issues_enhanced.query(\"issuetype != 'Epic'\")[['issuetype','status_cat_name','storypoints','prio']].groupby(['issuetype','status_cat_name','prio'])['storypoints'].agg(storypoints='sum', issue_count='count').reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# histogram instead of bar to merge the priority values into one color\n",
    "fig = px.histogram(df_issues_per_type, x=\"issuetype\", y=\"storypoints\", color='status_cat_name',\n",
    "    title='Storypoints per Issue Type',\n",
    "    color_discrete_map= STATUS_CAT_COLOR_DICT,\n",
    "    labels={'status_cat_name':'Status','storypoints':'Storypoints','issue_count':'Number of Issues','issuetype':'Issue Type'})\n",
    "fig.update_layout(\n",
    "    yaxis_title=\"Storypoints\",\n",
    ")\n",
    "fig.show()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.2 Show Number of Issues Grouped by Issue Type\n",
    "\n",
    "**Purpose:** As a program manager I want to see the total volume of generated work per type (e.g. tasks, bug-fixing) so that I can better understand the volume of work for each work type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = px.pie(df_issues_per_type,\n",
    "    values='issue_count',\n",
    "    names='issuetype',\n",
    "    color='issuetype',\n",
    "    title='Number of Issues per Issue Type',\n",
    "    labels={'issue_count':'Number of Issues','issuetype':'Issue Type'},\n",
    "    color_discrete_map={\n",
    "        \"Story\": COLOR_STORY,\n",
    "        \"Bug\": COLOR_BUG,\n",
    "        \"Task\": COLOR_TASK,\n",
    "        \"Sub-task\": COLOR_SUBTASK})\n",
    "fig.update_traces(textinfo=\"percent+label\")\n",
    "fig.update_layout(showlegend=False)\n",
    "fig.show()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.3 Show Volume of Work per Epic\n",
    "\n",
    "**Purpose:** As a program manager I want to see the total volume of work (in story points and by ticket volume) for each Epic so that I can better understand on which features we spent most of our time. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query\n",
    "df_issues_per_epic = df_issues_enhanced.query(\"issuetype != 'Epic'\")[['issuetype','epic_name','status_cat_name','storypoints','prio']].groupby(['issuetype','epic_name','status_cat_name','prio'])['storypoints'].agg(storypoints='sum', issue_count='count').reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = px.treemap(df_issues_per_epic, path=[px.Constant(\"All Work\"), 'epic_name', 'issuetype', 'prio'],\n",
    "    values='storypoints',\n",
    "    color='epic_name',\n",
    "    title='Epic Treemap (Based on Storypoints)',\n",
    "    labels={'storypoints':'Storypoints','labels':'Labels','parent':'Parent','id':'ID','epic_name':'Epic'})\n",
    "fig.update_traces(root_color=\"white\")\n",
    "fig.update_layout(margin = dict(t=50, l=25, r=25, b=25))\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = px.treemap(df_issues_per_epic, path=[px.Constant(\"All Work\"), 'epic_name', 'issuetype', 'prio'],\n",
    "    values='issue_count',\n",
    "    color='epic_name',\n",
    "    title='Epic Treemap (Based on Issue Count)',\n",
    "    labels={'issue_count':'Issue Count','labels':'Labels','parent':'Parent','id':'ID','epic_name':'Epic'})\n",
    "fig.update_traces(root_color=\"lightgrey\")\n",
    "fig.update_layout(margin = dict(t=50, l=25, r=25, b=25))\n",
    "fig.show()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.4 Show Current Progress per Project\n",
    "\n",
    "**Purpose:** As a program manager I want to see the progress for each project by comparing story points for ongoing and already completed issues so that I can understand how much work is still ahead of us."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query\n",
    "df_issues_per_project = df_issues_enhanced.query(\"issuetype != 'Epic'\")[['project_name','issuetype','status_cat_name','storypoints']].groupby(['project_name','issuetype','status_cat_name'])['storypoints'].agg(storypoints='sum', issue_count='count').reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = px.histogram(df_issues_per_project,\n",
    "    y=\"project_name\",\n",
    "    x=\"storypoints\",\n",
    "    orientation='h',\n",
    "    color='status_cat_name',\n",
    "    title='Progress per Project',\n",
    "    color_discrete_map= STATUS_CAT_COLOR_DICT,\n",
    "    labels={'project_name':'Project','status_cat_name':'Status','storypoints':'Storypoints','issue_count':'Number of Issues','issuetype':'Issue Type'})\n",
    "fig.update_layout(\n",
    "    yaxis_title=\"Projects\",\n",
    "    xaxis_title=\"Storypoints\",\n",
    ")\n",
    "fig.show()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Jira Boards and Sprints\n",
    "\n",
    "### 5.1 Get All Available Boards"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_boards = jira_get_boards()\n",
    "df_boards[['location.projectId','location.projectKey','location.projectName','id','name','type','location.displayName','location.name']].head(10)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.2 Get All Sprints for a Specific Board"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_sprints = jira_get_sprints(board_id=7)\n",
    "#df_sprints[['id','state','name','startDate','endDate','completeDate','originBoardId','goal']]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.3 Get All Issues From a Board Backlog"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_backlog_issues = jira_get_backlog_issues(board_id=7)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.4 Get All Issues From a Particular Sprint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_sprint_issues = jira_get_sprint_issues(sprint_id=32)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.5 Get All Issues From Board Including Sprint Reference\n",
    "\n",
    "**Attention:** Query takes long since it must make dedicated HTTP calls for every sprint in the board"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_issues_in_sprints = jira_get_board_issues(board_id=7)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.6 Visualize Sprint Progress\n",
    "\n",
    "Similar to the Jira sprint velocity report, we will generate a similar chart. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query\n",
    "df_issues_per_sprint = df_issues_in_sprints.query(\"project_name == 'abc'\")[['project_name','issuetype','prio','storypoints','sprint_name','sprint_start_date','sprint_end_date']].groupby(['project_name','issuetype','prio','sprint_name','sprint_start_date','sprint_end_date'])['storypoints'].agg(storypoints='sum').reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# histogram instead of bar to merge values into one color\n",
    "fig = px.histogram(df_issues_per_sprint, x=\"sprint_name\", y=\"storypoints\", color='issuetype',\n",
    "    title='Storypoints per Issue Type',\n",
    "    labels={'sprint_name':'Sprint Name','issue_count':'Number of Issues','issuetype':'Issue Type'},\n",
    "    color_discrete_map={\n",
    "        \"Story\": COLOR_STORY,\n",
    "        \"Bug\": COLOR_BUG,\n",
    "        \"Task\": COLOR_TASK,\n",
    "        \"Sub-task\": COLOR_SUBTASK})\n",
    "fig.update_layout(\n",
    "    yaxis_title=\"Storypoints\",\n",
    "    xaxis_title=\"Sprints\",\n",
    ")\n",
    "fig.show()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Jira Projects\n",
    "\n",
    "### 6.1 Get all Jira Projects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_projects = jira_get_all_projects()\n",
    "df_projects[['id','key','name','projectTypeKey','simplified','style']]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6.2 Get All Versions for One Project"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_versions = jira_get_versions('BEAS')\n",
    "df_versions[['projectId','id','description','name','archived','released','startDate','releaseDate','userStartDate','userReleaseDate']].head(10)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv",
   "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.7.16"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "64b0ee0bc7ee3867c463a47c5d4a818d2eef0a36ac46eebc1933b0333b5995a7"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}