Jira lead time extraction

jira_extract.py

import pandas as pd
import requests

JIRA_TOKEN = "abcdefg12345"
JIRA_URL = "https://jitsejan.atlassian.net/"
JIRA_USER = "[email protected]"

# Setup a requests session
rsession = requests.Session()
# New Jira instances authenticate through a token
rsession.auth = (JIRA_USER, JIRA_TOKEN)
# Jira query
jql = "project = BT"
# Parameters with the changelog enabled
params = {
    'jql': jql,
    'expand': 'changelog',
}
# Execute the Jira query and get the issues field from the JSON response.
issues = rsession.get(f"{JIRA_URL}/rest/api/2/search", params=params).json()['issues']
# Get the base data
base = pd.json_normalize(issues)[['key', 'fields.created']]
base.rename(columns={'fields.created': 'created'}, inplace=True)
# Get the transitions
transitions = pd.json_normalize(data=issues,
                                record_path=['changelog', 'histories'],
                                meta=['fields', 'key'])[['created', 'items', 'key']]
# Filter and transform the status transitions
transitions = transitions\
    .join(transitions['items']\
            .explode()
            .apply(pd.Series)
    )\
    .query("field == 'status'")\
    .drop("items", axis=1)[['created', 'key', 'toString']]
# Create the backlog transitions
create_transitions = base.copy()
create_transitions['toString'] = 'Backlog'
# Combine the transitions and the Backlog dates
transitions = pd.concat([transitions, create_transitions])
# Pivot the statuses
statuses = transitions.pivot(index='key', values='created', columns='toString')
# Make sure all fields are of datetime
statuses = statuses.astype('datetime64[ns]')
statuses.rename(columns={'toString': 'Status'}, inplace=True)
# Calculate the lead time (in seconds) by calculating the timedelta
statuses['Duration'] = (statuses['Done'] - statuses['Backlog']).apply(lambda x: x.seconds)