Skip to content

Instantly share code, notes, and snippets.

@beckermr

beckermr/risk_census.ipynb

Last active December 9, 2020 14:22
Show Gist options
  • Save beckermr/9c0f5aa71720cf1b18646ccd0c3ab40f to your computer and use it in GitHub Desktop.
Save beckermr/9c0f5aa71720cf1b18646ccd0c3ab40f to your computer and use it in GitHub Desktop.
Download ZIP
risk_census.ipynb
Display the source blob
Display the rendered blob
Raw
risk_census.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"import os\n",
"from openpyxl import load_workbook\n",
"import numpy as np"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [],
"source": [
"wb = load_workbook(os.path.expanduser(\"~/Downloads/Infrastructure risk review.xlsx\"))"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"['Template', 'CB', 'CJ', 'MRB', 'Scopatz', 'UK', 'WV']"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"wb.sheetnames"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [],
"source": [
"import string\n",
"\n",
"nums = list(range(6, 18))\n",
"alphas = [c.upper() for c in string.ascii_lowercase if ord(c.upper()) >= ord(\"C\") and ord(c.upper()) <= ord(\"V\")]"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [],
"source": [
"if \"total\" not in wb.sheetnames:\n",
" tot = wb.create_sheet(\"total\")\n",
"\n",
"temp = wb[\"Template\"]\n",
"\n",
"for i in range(1, 19):\n",
" for a in string.ascii_uppercase:\n",
" key = \"%s%d\" % (a, i)\n",
" tot[key] = temp[key].value"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [],
"source": [
"for i in nums:\n",
" for a in alphas:\n",
" key = \"%s%d\" % (a, i)\n",
" tot[key] = 0.0\n",
" num = 0\n",
" for sheet in wb.sheetnames:\n",
" if sheet not in [\"Template\", \"total\"] and wb[sheet][key].value is not None:\n",
" tot[key] = tot[key].value + wb[sheet][key].value\n",
" num += 1\n",
" if num > 0:\n",
" tot[key] = tot[key].value / num"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd\n",
"pd.set_option('display.max_columns', None)\n",
"pd.set_option('display.max_rows', None)"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"scrolled": false
},
"outputs": [],
"source": [
"data = pd.DataFrame(tot.values)"
]
},
{
"cell_type": "code",
"execution_count": 9,
"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>0</th>\n",
" <th>1</th>\n",
" <th>2</th>\n",
" <th>3</th>\n",
" <th>4</th>\n",
" <th>5</th>\n",
" <th>6</th>\n",
" <th>7</th>\n",
" <th>8</th>\n",
" <th>9</th>\n",
" <th>10</th>\n",
" <th>11</th>\n",
" <th>12</th>\n",
" <th>13</th>\n",
" <th>14</th>\n",
" <th>15</th>\n",
" <th>16</th>\n",
" <th>17</th>\n",
" <th>18</th>\n",
" <th>19</th>\n",
" <th>20</th>\n",
" <th>21</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>None</td>\n",
" <td>Description</td>\n",
" <td>Staged-recipes</td>\n",
" <td>Travis CI</td>\n",
" <td>Azure</td>\n",
" <td>Drone</td>\n",
" <td>CFEP-13 Output Validation</td>\n",
" <td>Other CI/build infra</td>\n",
" <td>autotick bot</td>\n",
" <td>webservices</td>\n",
" <td>anaconda.org</td>\n",
" <td>social media/public relations</td>\n",
" <td>conda</td>\n",
" <td>conda build</td>\n",
" <td>conda smithy</td>\n",
" <td>mamba</td>\n",
" <td>boa</td>\n",
" <td>quetz</td>\n",
" <td>compilers</td>\n",
" <td>languages (Python, R, Rust, C, C++, etc.)</td>\n",
" <td>Authentication/keys/access</td>\n",
" <td>Docs</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>Knowledge of system</td>\n",
" <td>How well do you know this system? (1 very well...</td>\n",
" <td>2.5</td>\n",
" <td>2.5</td>\n",
" <td>2.5</td>\n",
" <td>3</td>\n",
" <td>3.25</td>\n",
" <td>2.66667</td>\n",
" <td>2.6</td>\n",
" <td>3</td>\n",
" <td>3</td>\n",
" <td>2.33333</td>\n",
" <td>2.75</td>\n",
" <td>2.75</td>\n",
" <td>2.75</td>\n",
" <td>2.5</td>\n",
" <td>3.75</td>\n",
" <td>4.25</td>\n",
" <td>2.75</td>\n",
" <td>2</td>\n",
" <td>2.25</td>\n",
" <td>1.5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>Documentation of system</td>\n",
" <td>How well do you think this system is documente...</td>\n",
" <td>2.16667</td>\n",
" <td>2.5</td>\n",
" <td>2.5</td>\n",
" <td>2.5</td>\n",
" <td>3.5</td>\n",
" <td>4</td>\n",
" <td>3.8</td>\n",
" <td>3.75</td>\n",
" <td>3.6</td>\n",
" <td>3.33333</td>\n",
" <td>3.5</td>\n",
" <td>3.5</td>\n",
" <td>3.75</td>\n",
" <td>4.5</td>\n",
" <td>4.5</td>\n",
" <td>4.25</td>\n",
" <td>2.75</td>\n",
" <td>2.5</td>\n",
" <td>3.5</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>Code quality (where applicable)</td>\n",
" <td>How good is the code quality? (including tests...</td>\n",
" <td>2.66667</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>2.2</td>\n",
" <td>2.75</td>\n",
" <td>3</td>\n",
" <td>3</td>\n",
" <td>3.66667</td>\n",
" <td>2.5</td>\n",
" <td>1</td>\n",
" <td>3.5</td>\n",
" <td>4</td>\n",
" <td>3</td>\n",
" <td>2.75</td>\n",
" <td>2.33333</td>\n",
" <td>2.33333</td>\n",
" <td>2.25</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>Key person risk</td>\n",
" <td>Is this infrastrucutre maintained by multiple ...</td>\n",
" <td>2.5</td>\n",
" <td>2.16667</td>\n",
" <td>3.16667</td>\n",
" <td>2.66667</td>\n",
" <td>3.75</td>\n",
" <td>3.33333</td>\n",
" <td>4</td>\n",
" <td>3.25</td>\n",
" <td>4.2</td>\n",
" <td>2.33333</td>\n",
" <td>2.75</td>\n",
" <td>3.25</td>\n",
" <td>1.75</td>\n",
" <td>4</td>\n",
" <td>4</td>\n",
" <td>3.66667</td>\n",
" <td>3</td>\n",
" <td>2.75</td>\n",
" <td>2.25</td>\n",
" <td>1.25</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>Security Risk (including DDOS)</td>\n",
" <td>Does this infra have security holes? How robus...</td>\n",
" <td>3.16667</td>\n",
" <td>2.16667</td>\n",
" <td>2</td>\n",
" <td>2.33333</td>\n",
" <td>3.25</td>\n",
" <td>2.66667</td>\n",
" <td>3.2</td>\n",
" <td>2.75</td>\n",
" <td>2.4</td>\n",
" <td>2</td>\n",
" <td>1.25</td>\n",
" <td>1.5</td>\n",
" <td>1.75</td>\n",
" <td>2</td>\n",
" <td>1.66667</td>\n",
" <td>1.66667</td>\n",
" <td>1.5</td>\n",
" <td>1.5</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>Size Risk</td>\n",
" <td>If CF grew by 5x in 6 months would this infra ...</td>\n",
" <td>2.83333</td>\n",
" <td>4</td>\n",
" <td>2.66667</td>\n",
" <td>3.66667</td>\n",
" <td>3.5</td>\n",
" <td>2.66667</td>\n",
" <td>3.4</td>\n",
" <td>2.33333</td>\n",
" <td>2.5</td>\n",
" <td>1</td>\n",
" <td>3.75</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>1.33333</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>11</th>\n",
" <td>Replacability (where applicable)</td>\n",
" <td>If this piece of infrastructure was removed wi...</td>\n",
" <td>3.33333</td>\n",
" <td>3.16667</td>\n",
" <td>4.5</td>\n",
" <td>3</td>\n",
" <td>2.5</td>\n",
" <td>2.66667</td>\n",
" <td>3.6</td>\n",
" <td>4</td>\n",
" <td>4.6</td>\n",
" <td>1.66667</td>\n",
" <td>4.4</td>\n",
" <td>4.8</td>\n",
" <td>4.8</td>\n",
" <td>3.2</td>\n",
" <td>2.25</td>\n",
" <td>2.25</td>\n",
" <td>4.33333</td>\n",
" <td>3</td>\n",
" <td>3</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>Confidence in assessment</td>\n",
" <td>How confident are you in this assessment, did ...</td>\n",
" <td>2.33333</td>\n",
" <td>2.16667</td>\n",
" <td>2</td>\n",
" <td>2.33333</td>\n",
" <td>2.75</td>\n",
" <td>2.33333</td>\n",
" <td>2.2</td>\n",
" <td>2.25</td>\n",
" <td>2</td>\n",
" <td>1.75</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>1.75</td>\n",
" <td>2</td>\n",
" <td>2.75</td>\n",
" <td>2.75</td>\n",
" <td>1.75</td>\n",
" <td>2.25</td>\n",
" <td>1.66667</td>\n",
" <td>1.33333</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 0 \\\n",
"4 None \n",
"5 Knowledge of system \n",
"6 Documentation of system \n",
"7 Code quality (where applicable) \n",
"8 Key person risk \n",
"9 Security Risk (including DDOS) \n",
"10 Size Risk \n",
"11 Replacability (where applicable) \n",
"16 Confidence in assessment \n",
"\n",
" 1 2 \\\n",
"4 Description Staged-recipes \n",
"5 How well do you know this system? (1 very well... 2.5 \n",
"6 How well do you think this system is documente... 2.16667 \n",
"7 How good is the code quality? (including tests... 2.66667 \n",
"8 Is this infrastrucutre maintained by multiple ... 2.5 \n",
"9 Does this infra have security holes? How robus... 3.16667 \n",
"10 If CF grew by 5x in 6 months would this infra ... 2.83333 \n",
"11 If this piece of infrastructure was removed wi... 3.33333 \n",
"16 How confident are you in this assessment, did ... 2.33333 \n",
"\n",
" 3 4 5 6 \\\n",
"4 Travis CI Azure Drone CFEP-13 Output Validation \n",
"5 2.5 2.5 3 3.25 \n",
"6 2.5 2.5 2.5 3.5 \n",
"7 2 2 2.2 2.75 \n",
"8 2.16667 3.16667 2.66667 3.75 \n",
"9 2.16667 2 2.33333 3.25 \n",
"10 4 2.66667 3.66667 3.5 \n",
"11 3.16667 4.5 3 2.5 \n",
"16 2.16667 2 2.33333 2.75 \n",
"\n",
" 7 8 9 10 \\\n",
"4 Other CI/build infra autotick bot webservices anaconda.org \n",
"5 2.66667 2.6 3 3 \n",
"6 4 3.8 3.75 3.6 \n",
"7 3 3 3.66667 2.5 \n",
"8 3.33333 4 3.25 4.2 \n",
"9 2.66667 3.2 2.75 2.4 \n",
"10 2.66667 3.4 2.33333 2.5 \n",
"11 2.66667 3.6 4 4.6 \n",
"16 2.33333 2.2 2.25 2 \n",
"\n",
" 11 12 13 14 15 \\\n",
"4 social media/public relations conda conda build conda smithy mamba \n",
"5 2.33333 2.75 2.75 2.75 2.5 \n",
"6 3.33333 3.5 3.5 3.75 4.5 \n",
"7 1 3.5 4 3 2.75 \n",
"8 2.33333 2.75 3.25 1.75 4 \n",
"9 2 1.25 1.5 1.75 2 \n",
"10 1 3.75 2 2 1 \n",
"11 1.66667 4.4 4.8 4.8 3.2 \n",
"16 1.75 2 2 1.75 2 \n",
"\n",
" 16 17 18 19 \\\n",
"4 boa quetz compilers languages (Python, R, Rust, C, C++, etc.) \n",
"5 3.75 4.25 2.75 2 \n",
"6 4.5 4.25 2.75 2.5 \n",
"7 2.33333 2.33333 2.25 2 \n",
"8 4 3.66667 3 2.75 \n",
"9 1.66667 1.66667 1.5 1.5 \n",
"10 1 1 1 1.33333 \n",
"11 2.25 2.25 4.33333 3 \n",
"16 2.75 2.75 1.75 2.25 \n",
"\n",
" 20 21 \n",
"4 Authentication/keys/access Docs \n",
"5 2.25 1.5 \n",
"6 3.5 2 \n",
"7 1 2 \n",
"8 2.25 1.25 \n",
"9 2 1 \n",
"10 1 1 \n",
"11 3 2 \n",
"16 1.66667 1.33333 "
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"data.iloc[[4, 5, 6, 7, 8, 9, 10, 11, 16], 0:22]"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"gist": {
"data": {
"description": "risk_census.ipynb",
"public": true
},
"id": ""
},
"kernelspec": {
"display_name": "Python [conda env:root] *",
"language": "python",
"name": "conda-root-py"
},
"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.8.6"
}
},
"nbformat": 4,
"nbformat_minor": 4
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment