Get an Excel report on the number of comments in JIRA tickets

jira.py

from jira import JIRA
from dateutil.parser import parse
import pandas as pd

# For any ticket that was externally reported or labeled a hotfix in the last 90 days, compute:
# 1. Number of comments
# 2. How long it took for it to be labeled hotfix
# 3. Number of transitions across squads

username = 'carlos.aguayo'

f = open('password.txt', 'r')
password = f.readline()
f.close()

j = JIRA("https://issues.appian.com", basic_auth=(username, password))

query = 'created > -90d AND (filter = ExternallyReported OR Urgency = Hotfix) AND ' \
        'issueFunction in hasComments("+1") ORDER BY cf[12140] ASC, created ASC'

issues = j.search_issues(query, maxResults=3000)

df = pd.DataFrame(columns=['key',
                           'summary',
                           'squad',
                           'tribe',
                           'comments',
                           'number_of_tribe_transitions',
                           'is_hotfix',
                           'days_passed',
                           'created',
                           'created_epoch',
                           'declared_hotfix',
                           'declared_hotfix_epoch',
                           'closed',
                           'closed_epoch',
                           'days_to_close'])

count = 0
for issue in issues:
    i = j.issue(issue.key, expand='changelog')
    created = i.fields.created
    is_hotfix = False if i.fields.customfield_11546 is None else i.fields.customfield_11546[0].value == 'Hotfix'
    declared_hotfix = None
    date_closed = None
    number_of_tribe_transitions = 0

    # Check for transitions
    for history in i.changelog.histories:
        for item in history.items:
            if item.field == 'status' and item.toString == 'Closed':
                date_closed = history.created
            if is_hotfix and item.field == 'Urgency' and item.toString == 'Hotfix':
                declared_hotfix = history.created
            if item.field == 'Tribe' and item.fromString is not None:
                number_of_tribe_transitions += 1

    p = '{}/{},' \
        'key:{}, ' \
        'comments:{}, ' \
        'number_of_transitions:{}, ' \
        'is_hotfix:{}, ' \
        'days_passed:{}, ' \
        'created:{}, ' \
        'created_epoch:{}, ' \
        'declared_hotfix:{}, ' \
        'declared_hotfix_epoch:{}'

    days = 0 if declared_hotfix is None else (parse(declared_hotfix) - parse(created)).days
    days_to_close = 0 if date_closed is None else (parse(date_closed) - parse(created)).days

    df.loc[count] = [
        i.key,
        i.fields.summary,
        '' if i.fields.customfield_10240 is None else i.fields.customfield_10240.value,  # squad
        '' if i.fields.customfield_12140 is None else i.fields.customfield_12140.value,  # tribe
        len(i.fields.comment.comments),
        number_of_tribe_transitions,
        is_hotfix,
        days,
        created,
        parse(created).strftime('%s'),
        declared_hotfix,
        '0' if declared_hotfix is None else parse(declared_hotfix).strftime('%s'),
        date_closed,
        '0' if date_closed is None else parse(date_closed).strftime('%s'),
        days_to_close
    ]
    count += 1

    print p.format(count,
                   len(issues),
                   i.key,
                   len(i.fields.comment.comments),
                   number_of_tribe_transitions,
                   is_hotfix,
                   days,
                   created,
                   parse(created).strftime('%s'),
                   declared_hotfix,
                   '0' if declared_hotfix is None else parse(declared_hotfix).strftime('%s'))


# http://xlsxwriter.readthedocs.io/example_pandas_simple.html
writer = pd.ExcelWriter('tickets.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
writer.save()