agency-gh-org-stats.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "from graphqlclient import GraphQLClient\n",
    "import json\n",
    "import csv\n",
    "from datetime import datetime\n",
    "import time\n",
    "from dotenv import DotEnv\n",
    "import os\n",
    "from IPython.display import clear_output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "environment = DotEnv()\n",
    "github_token = environment.get('GITHUB_TOKEN')\n",
    "client = GraphQLClient('https://api.github.com/graphql')\n",
    "client.inject_token(f'token {github_token}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "agencies = {\n",
    "    'usaid':['usaid'],\n",
    "    'usda': ['usda','usda-ars-agil','USDA-ERS','USDA-FSA','usda-vs','WFMRDA',],\n",
    "    'doc': ['businessus','commercedataservice','commercegov','internationaltradeadministration','ioos','nesii','NMML','noaa-gfdl','NOAA-ORR-ERD','ntia','SelectUSA','us-bea','uscensusbureau','useda','USPTO',],\n",
    "    'dod': ['adl-aicc','adlnet','afrl','defense-cyber-crime-center','deptofdefense','dhs-ncats','erdc-cm','M-O-S-E-S','missioncommand','NationalGuard','redhawksdr','virtual-world-framework','afseo','erdc-itl','iadgov','info-sharing-environment','NationalSecurityAgency','ngageoint','nsacyber','project-interoperability','psns-imf','screamlab','usarmyresearchlab','USNavalResearchLaboratory',],\n",
    "    'ed': ['usedgov'],\n",
    "    'doe': ['doecode','energyapps','GIST-ORNL','ngds','petsc','llnl',],\n",
    "    'hhs': ['blue-button','CDCgov','cmsgov','demand-driven-open-data','didsr','fda','hhs','HHS-AHRQ','HHSDigitalMediaAPIPlatform','HHSIDEAlab','informaticslab','nhanes','nidcd',],\n",
    "    'dhs': ['US-CBP', 'uscis',],\n",
    "    'doj': ['NIEM', 'usdoj',],\n",
    "    'dol': ['EEOC', 'usdepartmentoflabor',],\n",
    "    'dos': ['CA-CST-Library','CA-CST-SII','historyatstate','IIP-Design','state-hiu','usstatedept',],\n",
    "    'doi': ['arcticlcc','GreatSmokyMountainsNationalPark','nationalparkservice','NCRN','usbr','USFWS','usgs','USGS-Astrogeology','USGS-CIDA','usgs-cmg','usgs-eros','USGS-OWI','USGS-R','USGS-WiM','usindianaffairs','usinterior',],\n",
    "    'treasury': ['fedspendingtransparency', 'IRSgov',],\n",
    "    'dot': ['Federal-Aviation-Administration','IMDProjects','usdot-jpo-ode',],\n",
    "    'dva': ['department-of-veterans-affairs','USDeptVeteransAffairs','VHAINNOVATIONS',],\n",
    "    'epa': ['regulationsgov', 'usepa',],\n",
    "    'gsa': ['18f','digital-analytics-program','eregs','government-services','gsa','gsa-oes','Innovation-Toolkit','opengovplatform','presidential-innovation-fellows','project-open-data','sbstusa','usagov','usasearch',],\n",
    "    'HUD': ['hudgov'],\n",
    "    'nasa': ['hudgov','ccmc','globegit','keplergo','nasa','nasa-develop','nasa-gibs','NASA-rdt','nasa-tournament-lab','nasaworldwind','NeoGeographyToolkit','Open-Sat','SERVIR','sunpy','visionworkbench',],\n",
    "    'nsf': ['nsf-open',],\n",
    "    'opm': ['usajobs',],\n",
    "    'sba': ['ussba',],\n",
    "    'ssa': ['ssagov',],\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_gh_query(org, date_from, size=100, cursor=None):\n",
    "    \"\"\"\n",
    "    Get and fill out the Github query to be used\n",
    "    \"\"\"\n",
    "    size = size if size <= 100 else 100\n",
    "    return '''\n",
    "        query {\n",
    "            search(first:%s, query:\"org:%s created:>%s\", type: ISSUE, after:%s){\n",
    "                issueCount\n",
    "                edges {\n",
    "                    node {\n",
    "                        __typename\n",
    "                        ... on Issue {\n",
    "                            title\n",
    "                            createdAt\n",
    "                            lastEditedAt\n",
    "                            state\n",
    "                            updatedAt\n",
    "                            repository {\n",
    "                                name\n",
    "                                owner {\n",
    "                                    login\n",
    "                                }\n",
    "                                issues {\n",
    "                                    totalCount\n",
    "                                }\n",
    "                                forks {\n",
    "                                    totalCount\n",
    "                                }\n",
    "                                stargazers {\n",
    "                                    totalCount\n",
    "                                }\n",
    "                                watchers {\n",
    "                                    totalCount\n",
    "                                }\n",
    "                                forkCount\n",
    "                                nameWithOwner\n",
    "                                createdAt\n",
    "                                isPrivate\n",
    "                            }\n",
    "                        }\n",
    "                        ... on PullRequest {\n",
    "                            title\n",
    "                            createdAt\n",
    "                            lastEditedAt\n",
    "                            state\n",
    "                            updatedAt\n",
    "                            repository {\n",
    "                                name\n",
    "                                owner {\n",
    "                                    login\n",
    "                                }\n",
    "                                issues {\n",
    "                                    totalCount\n",
    "                                }\n",
    "                                forks {\n",
    "                                    totalCount\n",
    "                                }\n",
    "                                stargazers {\n",
    "                                    totalCount\n",
    "                                }\n",
    "                                watchers {\n",
    "                                    totalCount\n",
    "                                }\n",
    "                                forkCount\n",
    "                                nameWithOwner\n",
    "                                createdAt\n",
    "                                isPrivate\n",
    "                            }\n",
    "                        }\n",
    "                    }\n",
    "                }\n",
    "                pageInfo {\n",
    "                    endCursor\n",
    "                    hasNextPage\n",
    "                }\n",
    "            }\n",
    "        }\n",
    "    ''' % (size, org, date_from, f'\"{cursor}\"' if cursor else 'null')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_stars_forks_query():\n",
    "    return '''\n",
    "        query($org: String!, $repoName: String!, $starsCursor: String, $forksCursor: String) {\n",
    "            repository(owner: $org, name: $repoName) {\n",
    "                stargazers(first: 100, after: $starsCursor, orderBy: {field:STARRED_AT, direction: ASC}) {\n",
    "                  edges {\n",
    "                    starredAt\n",
    "                  }\n",
    "                  pageInfo {\n",
    "                    endCursor\n",
    "                    hasNextPage\n",
    "                  }\n",
    "                }\n",
    "                forks(first: 100, after: $forksCursor, orderBy: {field:CREATED_AT, direction:ASC}) {\n",
    "                  edges {\n",
    "                    node {\n",
    "                      createdAt\n",
    "                    }\n",
    "                  }\n",
    "                  pageInfo {\n",
    "                    endCursor\n",
    "                    hasNextPage\n",
    "                  }\n",
    "                }\n",
    "            }\n",
    "            rateLimit {\n",
    "                limit\n",
    "                remaining\n",
    "                resetAt\n",
    "            }\n",
    "        }\n",
    "    '''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_issues_data(org, date_from, query_limit, cursor=None):\n",
    "    \"\"\"\n",
    "    Get Github data for the supplied org\n",
    "    \"\"\"\n",
    "    issues = []\n",
    "    repos = []\n",
    "    query = get_gh_query(org, date_from, query_limit, cursor)\n",
    "    results = json.loads(client.execute(query))\n",
    "\n",
    "    if results['data']['search']['edges']:\n",
    "        nodes = [ edge['node'] for edge in results['data']['search']['edges']]\n",
    "        for node in nodes:\n",
    "            issue, repo = parse_data(node)\n",
    "            repo_name = repo['full_name']\n",
    "            print(f'Processed repo: {repo_name}')\n",
    "\n",
    "            issues.append(issue)\n",
    "            repos.append(repo)\n",
    "    \n",
    "    has_next = results['data']['search']['pageInfo']['hasNextPage']\n",
    "    cursor = results['data']['search']['pageInfo']['endCursor']\n",
    "    \n",
    "    return issues, repos, has_next, cursor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_stars_forks_data(org, repo, stars_cursor=None, forks_cursor=None):\n",
    "    stars = []\n",
    "    stars_has_next = False\n",
    "    forks = []\n",
    "    forks_has_next = False\n",
    "    variables = {\n",
    "        \"org\": org,\n",
    "        \"repoName\": repo, \n",
    "        \"starsCursor\": stars_cursor, \n",
    "        \"forksCursor\": forks_cursor,\n",
    "    }\n",
    "    query = get_stars_forks_query()\n",
    "    results = json.loads(client.execute(query, variables))\n",
    "    \n",
    "    if results['data']['repository']['stargazers']['edges']:\n",
    "        for edge in results['data']['repository']['stargazers']['edges']:\n",
    "            stars.append({\n",
    "                'owner': org,\n",
    "                'repo': repo,\n",
    "                'starredAt': edge['starredAt'],\n",
    "            })\n",
    "    \n",
    "        stars_has_next = results['data']['repository']['stargazers']['pageInfo']['hasNextPage']\n",
    "        stars_cursor = results['data']['repository']['stargazers']['pageInfo']['endCursor']\n",
    "    \n",
    "    if results['data']['repository']['forks']['edges']:\n",
    "        nodes = [ edge['node'] for edge in results['data']['repository']['forks']['edges']]\n",
    "        for node in nodes:\n",
    "            forks.append({\n",
    "                'owner': org,\n",
    "                'repo': repo,\n",
    "                'createdAt': node['createdAt']\n",
    "            })\n",
    "    \n",
    "        forks_has_next = results['data']['repository']['forks']['pageInfo']['hasNextPage']\n",
    "        forks_cursor = results['data']['repository']['forks']['pageInfo']['endCursor']\n",
    "\n",
    "    return stars, forks, stars_has_next, stars_cursor, forks_has_next, forks_cursor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_rate_limit():\n",
    "    \"\"\"\n",
    "    Get the Github API rate limit current state for the used token\n",
    "    \"\"\"\n",
    "    query = '''query {\n",
    "        rateLimit {\n",
    "            limit\n",
    "            remaining\n",
    "            resetAt\n",
    "        }\n",
    "    }'''\n",
    "    response = client.execute(query)\n",
    "    json_response = json.loads(response)\n",
    "    return json_response['data']['rateLimit']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def handle_rate_limit(rate_limit):\n",
    "    \"\"\"\n",
    "    Handle Github API rate limit and wait times\n",
    "    \"\"\"\n",
    "    remaining = rate_limit['remaining']\n",
    "    limit = rate_limit['limit']\n",
    "    percent_remaining = remaining / limit\n",
    "    reset_at = rate_limit['resetAt']\n",
    "    if percent_remaining < 0.15:\n",
    "        reset_at = datetime.strptime(reset_at, '%Y-%m-%dT%H:%M:%SZ')\n",
    "        current_time = datetime.now()\n",
    "        time_diff = reset_at - current_time\n",
    "        seconds = time_diff.total_seconds()\n",
    "        time.sleep(seconds)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "def parse_data(node):\n",
    "    \"\"\"\n",
    "    Parse Github node data.\n",
    "    \"\"\"\n",
    "    repo = {\n",
    "        'name': node['repository']['name'],\n",
    "        'owner': node['repository']['owner']['login'],\n",
    "        'issues': node['repository']['issues']['totalCount'],\n",
    "        'forks': node['repository']['forks']['totalCount'],\n",
    "        'stargazers': node['repository']['stargazers']['totalCount'],\n",
    "        'watchers': node['repository']['watchers']['totalCount'],\n",
    "        'forkCount': node['repository']['forkCount'],\n",
    "        'full_name': node['repository']['nameWithOwner'],\n",
    "        'created_at': node['repository']['createdAt'],\n",
    "        'isPrivate': node['repository']['isPrivate'],\n",
    "    }\n",
    "\n",
    "    issue = {\n",
    "        'type': node['__typename'],\n",
    "        'owner': node['repository']['owner']['login'],\n",
    "        'repo_name': node['repository']['name'],\n",
    "        'title': node['title'],\n",
    "        'created_at': node['createdAt'],\n",
    "        'last_edit_date': node['lastEditedAt'],\n",
    "        'state': node['state'],\n",
    "        'updated_at': node['updatedAt'],\n",
    "    }\n",
    "    return issue, repo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_csv(file_name, data, fields):\n",
    "    \"\"\"\n",
    "    Create a CSV file from the supplied data and fields\n",
    "    \"\"\"\n",
    "    print(f'Creating {file_name}')\n",
    "    with open(file_name, 'w') as csv_file:\n",
    "        \n",
    "        writer = csv.DictWriter(csv_file, fieldnames=fields)\n",
    "        writer.writeheader()\n",
    "        writer.writerows(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "def write_issues_csv(owner, run_time, data):\n",
    "    \"\"\"\n",
    "    Write issues to a CSV file\n",
    "    \"\"\"\n",
    "    fields = [\n",
    "        'type',\n",
    "        'owner',\n",
    "        'repo_name',\n",
    "        'title',\n",
    "        'created_at',\n",
    "        'last_edit_date',\n",
    "        'state',\n",
    "        'updated_at',\n",
    "    ]\n",
    "    create_csv(f'github-{owner}-issues-data-{run_time}.csv', data, fields)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "def write_repos_csv(owner, run_time, data):\n",
    "    \"\"\"\n",
    "    Write repos to a CSV file\n",
    "    \"\"\"\n",
    "    fields = [\n",
    "        'name',\n",
    "        'owner',\n",
    "        'issues',\n",
    "        'forks',\n",
    "        'stargazers',\n",
    "        'watchers',\n",
    "        'forkCount',\n",
    "        'full_name',\n",
    "        'created_at',\n",
    "        'isPrivate',\n",
    "    ]\n",
    "    create_csv(f'github-{owner}-repos-data-{run_time}.csv', data, fields)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def filter_repos(repos):\n",
    "    \"\"\"\n",
    "    Filter the repo objects into a single entry\n",
    "    \"\"\"\n",
    "    filtered_repos = {}\n",
    "    for repo in repos:\n",
    "        repo_name = repo['full_name']\n",
    "        filtered_repos[repo_name] = repo\n",
    "    return filtered_repos.values()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "processed ['usaid', 'usda', 'usda-ars-agil', 'USDA-ERS', 'USDA-FSA', 'usda-vs', 'WFMRDA', 'businessus', 'commercedataservice', 'commercegov', 'internationaltradeadministration', 'ioos', 'nesii', 'NMML', 'noaa-gfdl', 'NOAA-ORR-ERD', 'ntia', 'SelectUSA', 'us-bea', 'uscensusbureau', 'useda', 'USPTO', 'adl-aicc', 'adlnet', 'afrl', 'defense-cyber-crime-center', 'deptofdefense', 'dhs-ncats', 'erdc-cm', 'M-O-S-E-S', 'missioncommand', 'NationalGuard', 'redhawksdr', 'virtual-world-framework', 'afseo', 'erdc-itl', 'iadgov', 'info-sharing-environment', 'NationalSecurityAgency', 'ngageoint', 'nsacyber', 'project-interoperability', 'psns-imf', 'screamlab', 'usarmyresearchlab', 'USNavalResearchLaboratory', 'usedgov', 'doecode', 'energyapps', 'GIST-ORNL', 'ngds', 'petsc', 'llnl', 'blue-button', 'CDCgov', 'cmsgov', 'demand-driven-open-data', 'didsr', 'fda', 'hhs', 'HHS-AHRQ', 'HHSDigitalMediaAPIPlatform', 'HHSIDEAlab', 'informaticslab', 'nhanes', 'nidcd', 'US-CBP', 'uscis', 'NIEM', 'usdoj', 'EEOC', 'usdepartmentoflabor', 'CA-CST-Library', 'CA-CST-SII', 'historyatstate', 'IIP-Design', 'state-hiu', 'usstatedept', 'arcticlcc', 'GreatSmokyMountainsNationalPark', 'nationalparkservice', 'NCRN', 'usbr', 'USFWS', 'usgs', 'USGS-Astrogeology', 'USGS-CIDA', 'usgs-cmg', 'usgs-eros', 'USGS-OWI', 'USGS-R', 'USGS-WiM', 'usindianaffairs', 'usinterior', 'fedspendingtransparency', 'IRSgov', 'Federal-Aviation-Administration', 'IMDProjects', 'usdot-jpo-ode', 'department-of-veterans-affairs', 'USDeptVeteransAffairs', 'VHAINNOVATIONS', 'regulationsgov', 'usepa', '18f', 'digital-analytics-program', 'eregs', 'government-services', 'gsa', 'gsa-oes', 'Innovation-Toolkit', 'opengovplatform', 'presidential-innovation-fellows', 'project-open-data', 'sbstusa', 'usagov', 'usasearch', 'hudgov', 'hudgov', 'ccmc', 'globegit', 'keplergo', 'nasa', 'nasa-develop', 'nasa-gibs', 'NASA-rdt', 'nasa-tournament-lab', 'nasaworldwind', 'NeoGeographyToolkit', 'Open-Sat', 'SERVIR', 'sunpy', 'visionworkbench', 'nsf-open', 'usajobs', 'ussba', 'ssagov']\n"
     ]
    }
   ],
   "source": [
    "processed_orgs = []\n",
    "run_time = datetime.now()\n",
    "for agency, gh_orgs in agencies.items():\n",
    "    issues = []\n",
    "    repos = []\n",
    "\n",
    "    for org in gh_orgs:\n",
    "        has_next = True\n",
    "        cursor = None\n",
    "\n",
    "        processed_orgs.append(org)\n",
    "        print(f'Fetching data for {org}')\n",
    "\n",
    "        while has_next:\n",
    "            rate_limit = get_rate_limit()\n",
    "            handle_rate_limit(rate_limit)\n",
    "\n",
    "            result_issues, result_repos, has_next, cursor = get_issues_data(org, '2016-08-01', 100, cursor)\n",
    "            issues.extend(result_issues)\n",
    "            repos.extend(result_repos)\n",
    "    \n",
    "    if issues:\n",
    "        write_issues_csv(agency, run_time, issues)\n",
    "\n",
    "    if repos:\n",
    "        stars = []\n",
    "        forks = []\n",
    "        repos = filter_repos(repos)\n",
    "        write_repos_csv(agency, run_time, repos)\n",
    "        \n",
    "        for repo in repos:\n",
    "            full_name = repo['full_name']\n",
    "            print(f'Getting stars and forks for {full_name}')\n",
    "            stars_has_next = True\n",
    "            forks_has_next = True\n",
    "            stars_cursor = None\n",
    "            forks_cursor = None\n",
    "            org = repo['owner']\n",
    "            repo = repo['name']\n",
    "            \n",
    "            while stars_has_next or forks_has_next:\n",
    "                rate_limit = get_rate_limit()\n",
    "                handle_rate_limit(rate_limit)\n",
    "                \n",
    "                result_stars, result_forks, stars_has_next, stars_cursor, forks_has_next, forks_cursor = get_stars_forks_data(org, repo, stars_cursor, forks_cursor)\n",
    "                stars.extend(result_stars)\n",
    "                forks.extend(result_forks)\n",
    "        create_csv(f'github-{agency}-stars-data-{run_time}.csv', stars, ['owner', 'repo', 'starredAt'])\n",
    "        create_csv(f'github-{agency}-forks-data-{run_time}.csv', forks, ['owner', 'repo', 'createdAt'])\n",
    "\n",
    "    clear_output()\n",
    "    print(f'processed {processed_orgs}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}