scenario_1_data_manipulation_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Scenario 1 Data Creation\n",
    "This notebook creates 4 datasets to be used for Scenario 1 and then solves each problem to ensure that the results make reasonable sense."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 295,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import names\n",
    "import random\n",
    "import requests\n",
    "import re\n",
    "from numpy.random import choice\n",
    "from datetime import datetime, timedelta\n",
    "import hashlib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 518,
   "metadata": {},
   "outputs": [],
   "source": [
    "bureaus_weighted = [('Administration (A)',3,2,5),\n",
    "('African Affairs (AF)',10,4,2),\n",
    "('Allowances (A/OPR/ALS)',1,2,1),\n",
    "('Arms Control, Verification and Compliance (AVC)',2,2,1),\n",
    "('Authentications',1,5,1),\n",
    "('Budget and Planning (BP)',2,4,2),\n",
    "('Chief Information Officer (CIO)',1,7,1),\n",
    "('Chief Economist, of the Department',1,8,1),\n",
    "('Chief of Staff',1,8,1),\n",
    "('Civil Rights, Office of',1,3,2),\n",
    "('Comptroller and Global Financial Services (CGFS)',5,2,5),\n",
    "('Conflict and Stabilization Operations (CSO)',5,2,4),\n",
    "('Consular Affairs (CA)',10,1,8),\n",
    "('Counterterrorism (CT)',2,3,6),\n",
    "('Democracy, Human Rights, and Labor (DRL)',10,2,3),\n",
    "('Department Spokesperson',1,9,1),\n",
    "('Diplomatic Reception Rooms',1,5,1),\n",
    "('Diplomatic Security (DS)',10,2,10),\n",
    "('Director General of the Foreign Service and Director of Human Resources (DGHR)',1,7,2),\n",
    "('East Asian and Pacific Affairs (EAP)',10,5,8),\n",
    "('Economic and Business Affairs (EB)',5,5,3),\n",
    "('Educational and Cultural Affairs (ECA)',5,3,6),\n",
    "('Energy Resources (ENR)',1,7,2),\n",
    "('European and Eurasian Affairs (EUR)',10,4,10),\n",
    "('Executive Secretariat (S/ES)',2,4,3),\n",
    "('Foreign Assistance (F)',2,5,8),\n",
    "('Foreign Missions (OFM)',1,5,3),\n",
    "('Foreign Service Institute (FSI)',5,4,4),\n",
    "('Global AIDS Coordinator (S/GAC)',1,7,2),\n",
    "('Global Criminal Justice (GCJ)',1,7,2),\n",
    "('Global Engagement Center(GEC)',1,7,4),\n",
    "('Global Food Security',1,7,2),\n",
    "(\"Global Women's Issues (S/GWI)\",1,7,2),\n",
    "('Global Youth Issues (GYI)',1,7,2),\n",
    "('Human Resources (DGHR)',10,1,6),\n",
    "('Information Resource Management (IRM)',10,1,10),\n",
    "('Inspector General (OIG)',1,5,2),\n",
    "('Intelligence and Research (INR)',5,4,3),\n",
    "('International Information Programs (IIP)',5,3,5),\n",
    "('International Narcotics and Law Enforcement Affairs (INL)',5,3,7),\n",
    "('International Organization Affairs (IO)',5,5,3),\n",
    "('International Security and Nonproliferation (ISN)',5,3,2),\n",
    "('Legal Adviser (L)',1,6,1),\n",
    "('Legislative Affairs (H)',1,6,2),\n",
    "('Management Policy, Rightsizing and Innovation (PRI)',2,3,3),\n",
    "('Medical Services (MED)',2,3,5),\n",
    "('Mission to the United Nations (USUN)',2,6,5),\n",
    "('Near Eastern Affairs (NEA)',10,5,7),\n",
    "('Oceans and International Environmental and Scientific Affairs(OES)',1,5,2),\n",
    "('Office of Terrorism Finance and Economic Sanctions Policy',1,6,1),\n",
    "('Ombudsman, Office of',1,5,1),\n",
    "('Overseas Buildings Operations (OBO)',5,5,10),\n",
    "('Policy, Planning, and Resources for Public Diplomacy and Public Affairs (PPR)',2,4,4),\n",
    "('Policy Planning Staff (S/P)',1,7,3),\n",
    "('Political-Military Affairs (PM)',5,3,8),\n",
    "('Population, Refugees, and Migration (PRM)',1,6,2),\n",
    "('Protocol (S/CPR)',1,5,5),\n",
    "('Public Affairs (PA)',5,5,7),\n",
    "('Quadrennial Diplomacy and Development Review (QDDR)',2,6,7),\n",
    "('Science & Technology Adviser (STAS)',1,6,1),\n",
    "('South and Central Asian Affairs (SCA)',10,6,6),\n",
    "('Trafficking in Persons (TIP)',1,5,2),\n",
    "('Western Hemisphere Affairs (WHA)',10,5,7)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 519,
   "metadata": {},
   "outputs": [],
   "source": [
    "bureaus_weighted = [i for i in bureaus_weighted if '(' in i[0]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 520,
   "metadata": {},
   "outputs": [],
   "source": [
    "def size_adjust(x):\n",
    "    if x >= 5:\n",
    "        x = x * 3\n",
    "    return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 521,
   "metadata": {},
   "outputs": [],
   "source": [
    "b_df = pd.DataFrame(bureaus_weighted, columns=['Bureau','Size','Seniority','Budget'])\n",
    "# b_df['Size3x'] = b_df.Size.apply(lambda x: size_adjust(x))\n",
    "b_df['Size_cum'] = b_df['Size'].cumsum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 522,
   "metadata": {},
   "outputs": [],
   "source": [
    "min_bureau = 0\n",
    "max_bureau = b_df.Size_cum.max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Generate Staffing by Bureau"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 523,
   "metadata": {},
   "outputs": [],
   "source": [
    "def gen_person():\n",
    "    # random variables\n",
    "    random_cat = random.random()\n",
    "    random_bureau = random.randint(min_bureau,max_bureau)\n",
    "    random_senior = random.random()\n",
    "    random_ws_trail = random.random()\n",
    "    random_ws_start = random.random()    \n",
    "    random_last_only = random.random()\n",
    "    random_grade_nodash = random.random()\n",
    "    random_grade_space = random.random()\n",
    "    random_title_adjust = random.random()\n",
    "    random_TDY = random.random()\n",
    "\n",
    "    # select Bureau\n",
    "    Bureau = b_df.loc[b_df['Size_cum'] >= random_bureau][:1].Bureau.values[0]    \n",
    "    \n",
    "    seniority_weighting = b_df.loc[b_df['Bureau'] == Bureau].Seniority.values[0]\n",
    "    random_mgmt = random.randint(0,10)\n",
    "    if random_mgmt >= seniority_weighting:\n",
    "        Title = 'Team Member'\n",
    "    else:\n",
    "        Title = 'Manager'        \n",
    "    \n",
    "    # select Gender\n",
    "    Gender = random.choice(['male','female'])\n",
    "    \n",
    "    # select Name\n",
    "    if random_last_only > 0.9:\n",
    "        Name = names.get_last_name()\n",
    "    else:\n",
    "        Name = names.get_full_name(gender=Gender)\n",
    "        \n",
    "    # add random trailing space to Name\n",
    "    if random_ws_trail > 0.7:\n",
    "        num_spaces = random.randint(1,5)\n",
    "        for i in range(num_spaces):\n",
    "            Name += ' '\n",
    "    \n",
    "    # add random leading space to Name\n",
    "    if random_ws_start > 0.7:\n",
    "        num_spaces = random.randint(1,5)\n",
    "        for i in range(num_spaces):\n",
    "            Name = ' ' + Name\n",
    "    \n",
    "    # generate years in service\n",
    "    YearsService = random.randint(1,25)\n",
    "    \n",
    "    # generate category/grade\n",
    "    if random_cat < 0.2:\n",
    "        Category = 'Contractor'\n",
    "        Grade = 'N/A'\n",
    "    elif random_cat < 0.5:\n",
    "        Category = 'Civil Service'\n",
    "        if random_senior > 0.95:\n",
    "            gs = random.randint(1,5)\n",
    "            Grade = 'SES-' + str(gs)\n",
    "            YearsService = random.randint(18,25)\n",
    "            Title = 'Executive'\n",
    "        else:\n",
    "            if Title == 'Manager':\n",
    "                gs = random.randint(13,15)\n",
    "            else:\n",
    "                gs = random.randint(7,13)\n",
    "            Grade = 'GS-' + str(gs)\n",
    "    else:\n",
    "        Category = 'Foreign Service'\n",
    "        if random_senior > 0.95:\n",
    "            fs = random.randint(1,5)\n",
    "            Grade = 'SFS-' + str(fs)\n",
    "            YearsService = random.randint(18,25)\n",
    "            Title = 'Executive'\n",
    "        else:\n",
    "            if Title == 'Manager':\n",
    "                fs = random.randint(1,3)\n",
    "            else:\n",
    "                fs = random.randint(4,9)\n",
    "            Grade = 'FS-' + str(fs)\n",
    "    \n",
    "    # Remove dash from Grade\n",
    "    if Grade != 'N/A':\n",
    "        if random_grade_nodash > 0.9:\n",
    "            Grade = Grade.replace('-','')\n",
    "        elif random_grade_space > 0.9:\n",
    "            Grade = Grade.replace('-',' ')\n",
    "    \n",
    "    # Edit title\n",
    "    if random_title_adjust > 0.9:\n",
    "        if Title == 'Manager':\n",
    "            Title = 'Mgmt'\n",
    "        elif Title == 'Team Member':\n",
    "            Title = 'Analyst'\n",
    "            \n",
    "    if random_TDY > 0.95:\n",
    "        TDY = 'Yes'\n",
    "    else:\n",
    "        TDY = 'No'\n",
    "    \n",
    "    EE = {'Gender': Gender,\n",
    "          'Name': Name,\n",
    "          'Bureau': Bureau,\n",
    "          'YearsService': YearsService,\n",
    "          'Category': Category,\n",
    "          'Grade': Grade,\n",
    "          'Title': Title,\n",
    "          'TDY': TDY}\n",
    "\n",
    "    return EE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 524,
   "metadata": {},
   "outputs": [],
   "source": [
    "cont = False\n",
    "while cont == False:\n",
    "    check = gen_person()\n",
    "    if check['Category'] != 'Contractor':\n",
    "        if ' ' in check['Grade']:\n",
    "            cont = True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 525,
   "metadata": {},
   "outputs": [],
   "source": [
    "EEs = []\n",
    "for i in range(10000):\n",
    "    EEs.append(gen_person())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 526,
   "metadata": {},
   "outputs": [],
   "source": [
    "EE = pd.DataFrame(columns=['Name','Gender','Grade','Bureau','Category','Title','YearsService','TDY'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 527,
   "metadata": {},
   "outputs": [],
   "source": [
    "EE = EE.from_records(EEs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 528,
   "metadata": {},
   "outputs": [],
   "source": [
    "EE = EE[['Bureau','Gender','Grade','Title','Name','YearsService','TDY']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 529,
   "metadata": {},
   "outputs": [],
   "source": [
    "EE.to_csv('../Class Files/Scenario 1/Staffing_by_Bureau.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 530,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Bureau\n",
       "European and Eurasian Affairs (EUR)                                               489\n",
       "Near Eastern Affairs (NEA)                                                        479\n",
       "Democracy, Human Rights, and Labor (DRL)                                          476\n",
       "Western Hemisphere Affairs (WHA)                                                  468\n",
       "Diplomatic Security (DS)                                                          464\n",
       "Information Resource Management (IRM)                                             460\n",
       "Human Resources (DGHR)                                                            451\n",
       "East Asian and Pacific Affairs (EAP)                                              450\n",
       "South and Central Asian Affairs (SCA)                                             445\n",
       "Consular Affairs (CA)                                                             442\n",
       "African Affairs (AF)                                                              419\n",
       "International Narcotics and Law Enforcement Affairs (INL)                         248\n",
       "Foreign Service Institute (FSI)                                                   247\n",
       "Educational and Cultural Affairs (ECA)                                            246\n",
       "International Organization Affairs (IO)                                           245\n",
       "Political-Military Affairs (PM)                                                   240\n",
       "Economic and Business Affairs (EB)                                                240\n",
       "Intelligence and Research (INR)                                                   237\n",
       "International Information Programs (IIP)                                          227\n",
       "Public Affairs (PA)                                                               225\n",
       "Comptroller and Global Financial Services (CGFS)                                  221\n",
       "International Security and Nonproliferation (ISN)                                 212\n",
       "Overseas Buildings Operations (OBO)                                               211\n",
       "Conflict and Stabilization Operations (CSO)                                       202\n",
       "Administration (A)                                                                163\n",
       "Policy, Planning, and Resources for Public Diplomacy and Public Affairs (PPR)     103\n",
       "Medical Services (MED)                                                            103\n",
       "Mission to the United Nations (USUN)                                              101\n",
       "Counterterrorism (CT)                                                              96\n",
       "Executive Secretariat (S/ES)                                                       95\n",
       "Budget and Planning (BP)                                                           90\n",
       "Arms Control, Verification and Compliance (AVC)                                    89\n",
       "Foreign Assistance (F)                                                             86\n",
       "Management Policy, Rightsizing and Innovation (PRI)                                85\n",
       "Quadrennial Diplomacy and Development Review (QDDR)                                76\n",
       "Global Women's Issues (S/GWI)                                                      68\n",
       "Global AIDS Coordinator (S/GAC)                                                    60\n",
       "Legal Adviser (L)                                                                  51\n",
       "Trafficking in Persons (TIP)                                                       51\n",
       "Global Youth Issues (GYI)                                                          49\n",
       "Chief Information Officer (CIO)                                                    48\n",
       "Science & Technology Adviser (STAS)                                                48\n",
       "Energy Resources (ENR)                                                             46\n",
       "Allowances (A/OPR/ALS)                                                             46\n",
       "Foreign Missions (OFM)                                                             45\n",
       "Protocol (S/CPR)                                                                   45\n",
       "Legislative Affairs (H)                                                            43\n",
       "Director General of the Foreign Service and Director of Human Resources (DGHR)     42\n",
       "Policy Planning Staff (S/P)                                                        42\n",
       "Global Engagement Center(GEC)                                                      41\n",
       "Global Criminal Justice (GCJ)                                                      40\n",
       "Population, Refugees, and Migration (PRM)                                          36\n",
       "Oceans and International Environmental and Scientific Affairs(OES)                 34\n",
       "Inspector General (OIG)                                                            34\n",
       "Name: Name, dtype: int64"
      ]
     },
     "execution_count": 530,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "EE.groupby('Bureau').Name.count().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Generate Budget by Bureau"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 531,
   "metadata": {},
   "outputs": [],
   "source": [
    "base = 100000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 532,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "100000000"
      ]
     },
     "execution_count": 532,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "base*(10**3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 533,
   "metadata": {},
   "outputs": [],
   "source": [
    "def choose_budget(x):\n",
    "    base = 100000\n",
    "    if x < 2:\n",
    "        b = random.randint(base,base*(10**1))\n",
    "    elif x < 5:\n",
    "        b = random.randint(base*(10**1),base*(10**2))\n",
    "    elif x < 7:\n",
    "        b = random.randint(base*(10**2),base*(10**3))\n",
    "    else:\n",
    "        b = random.randint(base*(10**3),base*(10**4))        \n",
    "    return b\n",
    "\n",
    "def get_acronym(x):\n",
    "    return x.split('(')[1].split(')')[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 534,
   "metadata": {},
   "outputs": [],
   "source": [
    "b_df['Budget Size (USD)'] = b_df.Budget.apply(lambda x: choose_budget(x))\n",
    "b_df['Bureau Acronym'] = b_df.Bureau.apply(lambda x: get_acronym(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 535,
   "metadata": {},
   "outputs": [],
   "source": [
    "budget = b_df[['Bureau Acronym','Budget Size (USD)']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 536,
   "metadata": {},
   "outputs": [],
   "source": [
    "budget.to_csv('../Class Files/Scenario 1/Budget_by_Bureau.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Generate Real Estate Allocations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 537,
   "metadata": {},
   "outputs": [],
   "source": [
    "annexes = []\n",
    "for i in range(1,30):\n",
    "    annexes.append('SA{}'.format(i))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 538,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "400"
      ]
     },
     "execution_count": 538,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "20*20"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 539,
   "metadata": {},
   "outputs": [],
   "source": [
    "def gen_office(Bureau):\n",
    "    # random variables\n",
    "    random_building = random.random()\n",
    "    \n",
    "    if random_building < 0.65:\n",
    "        Building = 'HST'\n",
    "        Location = random.randint(1000,7999)\n",
    "    else:\n",
    "        Building = 'SA{}'.format(random.randint(1,10))\n",
    "        Location = random.randint(100,5000)\n",
    "    \n",
    "    Size = round(random.randint(500,2000),-1)\n",
    "\n",
    "    office = {'Bureau': Bureau,\n",
    "          'Building': Building,\n",
    "          'Size': Size,\n",
    "          'Location': Location}\n",
    "\n",
    "    return office"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 540,
   "metadata": {},
   "outputs": [],
   "source": [
    "offices = []\n",
    "for i in bureaus_weighted:\n",
    "    Bureau = i[0]\n",
    "    for off in range(1,i[1]*3):\n",
    "        offices.append(gen_office(Bureau))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 541,
   "metadata": {},
   "outputs": [],
   "source": [
    "RE = pd.DataFrame(columns=['Building','Bureau','Location','Size'])\n",
    "RE = df.from_records(offices)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 542,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Bureau\n",
       "Administration (A)                                                                 9840\n",
       "African Affairs (AF)                                                              35500\n",
       "Allowances (A/OPR/ALS)                                                             2160\n",
       "Arms Control, Verification and Compliance (AVC)                                    6220\n",
       "Budget and Planning (BP)                                                           7490\n",
       "Chief Information Officer (CIO)                                                    3260\n",
       "Comptroller and Global Financial Services (CGFS)                                  14990\n",
       "Conflict and Stabilization Operations (CSO)                                       16010\n",
       "Consular Affairs (CA)                                                             34980\n",
       "Counterterrorism (CT)                                                              7010\n",
       "Democracy, Human Rights, and Labor (DRL)                                          36090\n",
       "Diplomatic Security (DS)                                                          34770\n",
       "Director General of the Foreign Service and Director of Human Resources (DGHR)     2250\n",
       "East Asian and Pacific Affairs (EAP)                                              36190\n",
       "Economic and Business Affairs (EB)                                                20400\n",
       "Educational and Cultural Affairs (ECA)                                            16060\n",
       "Energy Resources (ENR)                                                             2580\n",
       "European and Eurasian Affairs (EUR)                                               34110\n",
       "Executive Secretariat (S/ES)                                                       6980\n",
       "Foreign Assistance (F)                                                             7750\n",
       "Foreign Missions (OFM)                                                             2700\n",
       "Foreign Service Institute (FSI)                                                   20490\n",
       "Global AIDS Coordinator (S/GAC)                                                    3890\n",
       "Global Criminal Justice (GCJ)                                                      2100\n",
       "Global Engagement Center(GEC)                                                      1720\n",
       "Global Women's Issues (S/GWI)                                                      2420\n",
       "Global Youth Issues (GYI)                                                          2690\n",
       "Human Resources (DGHR)                                                            40280\n",
       "Information Resource Management (IRM)                                             32720\n",
       "Inspector General (OIG)                                                            2400\n",
       "Intelligence and Research (INR)                                                   16780\n",
       "International Information Programs (IIP)                                          19820\n",
       "International Narcotics and Law Enforcement Affairs (INL)                         19260\n",
       "International Organization Affairs (IO)                                           18900\n",
       "International Security and Nonproliferation (ISN)                                 17210\n",
       "Legal Adviser (L)                                                                  3580\n",
       "Legislative Affairs (H)                                                            2560\n",
       "Management Policy, Rightsizing and Innovation (PRI)                                6140\n",
       "Medical Services (MED)                                                             5050\n",
       "Mission to the United Nations (USUN)                                               7400\n",
       "Near Eastern Affairs (NEA)                                                        33270\n",
       "Oceans and International Environmental and Scientific Affairs(OES)                 2670\n",
       "Overseas Buildings Operations (OBO)                                               15840\n",
       "Policy Planning Staff (S/P)                                                        1220\n",
       "Policy, Planning, and Resources for Public Diplomacy and Public Affairs (PPR)      5920\n",
       "Political-Military Affairs (PM)                                                   19350\n",
       "Population, Refugees, and Migration (PRM)                                          3450\n",
       "Protocol (S/CPR)                                                                   2200\n",
       "Public Affairs (PA)                                                               19020\n",
       "Quadrennial Diplomacy and Development Review (QDDR)                                4840\n",
       "Science & Technology Adviser (STAS)                                                2080\n",
       "South and Central Asian Affairs (SCA)                                             32410\n",
       "Trafficking in Persons (TIP)                                                       1960\n",
       "Western Hemisphere Affairs (WHA)                                                  38840\n",
       "Name: Size, dtype: int64"
      ]
     },
     "execution_count": 542,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "RE.groupby(['Bureau']).Size.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 543,
   "metadata": {},
   "outputs": [],
   "source": [
    "RE.to_csv('../Class Files/Scenario 1/DoS_Real_Estate_Allocation.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Generate Papers by Bureau"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 582,
   "metadata": {},
   "outputs": [],
   "source": [
    "def gen_paper():\n",
    "    # random variables\n",
    "    random_bureau = random.randint(min_bureau,max_bureau)   \n",
    "\n",
    "    # select Bureau\n",
    "    Bureau = b_df.loc[b_df['Size_cum'] >= random_bureau][:1].Bureau.values[0]  \n",
    "    \n",
    "    startdate = datetime(year=2018,month=1,day=1)\n",
    "    Date = startdate+timedelta(random.randint(1,364))\n",
    "    Date = Date.strftime('%m-%d-%Y')\n",
    "    \n",
    "    hash_seed = random.random()\n",
    "    Paper_ID = hashlib.md5(str(hash_seed).encode('utf-8')).hexdigest()\n",
    "\n",
    "    paper = {'Bureau': Bureau,\n",
    "             'Date': Date,\n",
    "             'Paper ID': Paper_ID}\n",
    "\n",
    "    return paper"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 593,
   "metadata": {},
   "outputs": [],
   "source": [
    "papers = []\n",
    "for i in range(15000):\n",
    "    papers.append(gen_paper())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 594,
   "metadata": {},
   "outputs": [],
   "source": [
    "paper = pd.DataFrame(columns=['Bureau','Date','Paper ID'])\n",
    "paper = df.from_records(papers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 595,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Bureau\n",
       "East Asian and Pacific Affairs (EAP)                                              713\n",
       "Information Resource Management (IRM)                                             699\n",
       "Near Eastern Affairs (NEA)                                                        697\n",
       "European and Eurasian Affairs (EUR)                                               694\n",
       "Consular Affairs (CA)                                                             685\n",
       "Western Hemisphere Affairs (WHA)                                                  679\n",
       "Diplomatic Security (DS)                                                          670\n",
       "Human Resources (DGHR)                                                            668\n",
       "African Affairs (AF)                                                              668\n",
       "South and Central Asian Affairs (SCA)                                             647\n",
       "Democracy, Human Rights, and Labor (DRL)                                          640\n",
       "Comptroller and Global Financial Services (CGFS)                                  381\n",
       "Economic and Business Affairs (EB)                                                374\n",
       "International Narcotics and Law Enforcement Affairs (INL)                         366\n",
       "Educational and Cultural Affairs (ECA)                                            357\n",
       "Intelligence and Research (INR)                                                   356\n",
       "Overseas Buildings Operations (OBO)                                               353\n",
       "Public Affairs (PA)                                                               342\n",
       "International Security and Nonproliferation (ISN)                                 334\n",
       "International Organization Affairs (IO)                                           333\n",
       "Political-Military Affairs (PM)                                                   333\n",
       "Foreign Service Institute (FSI)                                                   332\n",
       "Conflict and Stabilization Operations (CSO)                                       329\n",
       "International Information Programs (IIP)                                          327\n",
       "Administration (A)                                                                286\n",
       "Budget and Planning (BP)                                                          160\n",
       "Quadrennial Diplomacy and Development Review (QDDR)                               155\n",
       "Management Policy, Rightsizing and Innovation (PRI)                               148\n",
       "Policy, Planning, and Resources for Public Diplomacy and Public Affairs (PPR)     144\n",
       "Mission to the United Nations (USUN)                                              138\n",
       "Arms Control, Verification and Compliance (AVC)                                   137\n",
       "Counterterrorism (CT)                                                             136\n",
       "Medical Services (MED)                                                            135\n",
       "Executive Secretariat (S/ES)                                                      122\n",
       "Foreign Assistance (F)                                                            121\n",
       "Legislative Affairs (H)                                                            89\n",
       "Legal Adviser (L)                                                                  85\n",
       "Global Youth Issues (GYI)                                                          78\n",
       "Foreign Missions (OFM)                                                             78\n",
       "Inspector General (OIG)                                                            76\n",
       "Oceans and International Environmental and Scientific Affairs(OES)                 73\n",
       "Population, Refugees, and Migration (PRM)                                          73\n",
       "Global Women's Issues (S/GWI)                                                      73\n",
       "Allowances (A/OPR/ALS)                                                             73\n",
       "Protocol (S/CPR)                                                                   72\n",
       "Energy Resources (ENR)                                                             69\n",
       "Global AIDS Coordinator (S/GAC)                                                    69\n",
       "Science & Technology Adviser (STAS)                                                68\n",
       "Chief Information Officer (CIO)                                                    66\n",
       "Trafficking in Persons (TIP)                                                       66\n",
       "Global Criminal Justice (GCJ)                                                      63\n",
       "Director General of the Foreign Service and Director of Human Resources (DGHR)     61\n",
       "Policy Planning Staff (S/P)                                                        59\n",
       "Global Engagement Center(GEC)                                                      50\n",
       "Name: Paper ID, dtype: int64"
      ]
     },
     "execution_count": 595,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "paper.groupby('Bureau')['Paper ID'].count().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 596,
   "metadata": {},
   "outputs": [],
   "source": [
    "paper.to_csv('../Class Files/Scenario 1/SES_Papers_Processed_2018.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Checking\n",
    "Now we should check results using the four dataframes:\n",
    "\n",
    "* `EE`\n",
    "* `budget`\n",
    "* `RE`\n",
    "* `paper`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 1\n",
    "Which bureaus, with respect to overall operating budget (the portion of their budget which excludes human resource cost), are least well-staffed?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 555,
   "metadata": {},
   "outputs": [],
   "source": [
    "EE['Bureau Acronym'] = EE.Bureau.apply(lambda x: get_acronym(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 556,
   "metadata": {},
   "outputs": [],
   "source": [
    "num_EEs = EE.groupby('Bureau Acronym').Bureau.count()\n",
    "num_EEs = pd.DataFrame(num_EEs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 557,
   "metadata": {},
   "outputs": [],
   "source": [
    "budget = budget.join(num_EEs, on='Bureau Acronym')\n",
    "budget['budget_per_EE'] = budget['Budget Size (USD)'] / budget['Bureau']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 558,
   "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>Bureau Acronym</th>\n",
       "      <th>Budget Size (USD)</th>\n",
       "      <th>Bureau</th>\n",
       "      <th>budget_per_EE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AVC</td>\n",
       "      <td>549436</td>\n",
       "      <td>89</td>\n",
       "      <td>6173.438202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>DRL</td>\n",
       "      <td>3061496</td>\n",
       "      <td>476</td>\n",
       "      <td>6431.714286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AF</td>\n",
       "      <td>2883929</td>\n",
       "      <td>419</td>\n",
       "      <td>6882.885442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A/OPR/ALS</td>\n",
       "      <td>407713</td>\n",
       "      <td>46</td>\n",
       "      <td>8863.326087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>L</td>\n",
       "      <td>470522</td>\n",
       "      <td>51</td>\n",
       "      <td>9225.921569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>IO</td>\n",
       "      <td>2437063</td>\n",
       "      <td>245</td>\n",
       "      <td>9947.195918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>ISN</td>\n",
       "      <td>2595371</td>\n",
       "      <td>212</td>\n",
       "      <td>12242.316038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>DGHR</td>\n",
       "      <td>7483067</td>\n",
       "      <td>493</td>\n",
       "      <td>15178.634888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>FSI</td>\n",
       "      <td>3852414</td>\n",
       "      <td>247</td>\n",
       "      <td>15596.817814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>EB</td>\n",
       "      <td>4312936</td>\n",
       "      <td>240</td>\n",
       "      <td>17970.566667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Bureau Acronym  Budget Size (USD)  Bureau  budget_per_EE\n",
       "3             AVC             549436      89    6173.438202\n",
       "10            DRL            3061496     476    6431.714286\n",
       "1              AF            2883929     419    6882.885442\n",
       "2       A/OPR/ALS             407713      46    8863.326087\n",
       "35              L             470522      51    9225.921569\n",
       "33             IO            2437063     245    9947.195918\n",
       "34            ISN            2595371     212   12242.316038\n",
       "12           DGHR            7483067     493   15178.634888\n",
       "21            FSI            3852414     247   15596.817814\n",
       "14             EB            4312936     240   17970.566667"
      ]
     },
     "execution_count": 558,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "budget.sort_values('budget_per_EE', ascending=True)[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 2\n",
    "Which bureaus have the highest proportion of managerial staff relative to their number of action officers?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 563,
   "metadata": {},
   "outputs": [],
   "source": [
    "titles = pd.DataFrame(EE.groupby(['Bureau','Title']).Name.count())\n",
    "titles = titles.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 564,
   "metadata": {},
   "outputs": [],
   "source": [
    "titles = titles.replace('Mgmt','Manager')\n",
    "titles = titles.replace('Executive','Manager')\n",
    "titles = titles.replace('Analyst','Team Member')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 565,
   "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>Title</th>\n",
       "      <th>Bureau</th>\n",
       "      <th>Manager</th>\n",
       "      <th>Team Member</th>\n",
       "      <th>ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Global Criminal Justice (GCJ)</td>\n",
       "      <td>31</td>\n",
       "      <td>9</td>\n",
       "      <td>0.290323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Global Youth Issues (GYI)</td>\n",
       "      <td>35</td>\n",
       "      <td>14</td>\n",
       "      <td>0.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Mission to the United Nations (USUN)</td>\n",
       "      <td>72</td>\n",
       "      <td>29</td>\n",
       "      <td>0.402778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Chief Information Officer (CIO)</td>\n",
       "      <td>33</td>\n",
       "      <td>15</td>\n",
       "      <td>0.454545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Legislative Affairs (H)</td>\n",
       "      <td>28</td>\n",
       "      <td>15</td>\n",
       "      <td>0.535714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Quadrennial Diplomacy and Development Review (...</td>\n",
       "      <td>49</td>\n",
       "      <td>27</td>\n",
       "      <td>0.551020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Global AIDS Coordinator (S/GAC)</td>\n",
       "      <td>38</td>\n",
       "      <td>22</td>\n",
       "      <td>0.578947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Global Women's Issues (S/GWI)</td>\n",
       "      <td>43</td>\n",
       "      <td>25</td>\n",
       "      <td>0.581395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Energy Resources (ENR)</td>\n",
       "      <td>29</td>\n",
       "      <td>17</td>\n",
       "      <td>0.586207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Science &amp; Technology Adviser (STAS)</td>\n",
       "      <td>30</td>\n",
       "      <td>18</td>\n",
       "      <td>0.600000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Title                                             Bureau  Manager  \\\n",
       "23                         Global Criminal Justice (GCJ)       31   \n",
       "26                             Global Youth Issues (GYI)       35   \n",
       "39                  Mission to the United Nations (USUN)       72   \n",
       "5                        Chief Information Officer (CIO)       33   \n",
       "36                               Legislative Affairs (H)       28   \n",
       "49     Quadrennial Diplomacy and Development Review (...       49   \n",
       "22                       Global AIDS Coordinator (S/GAC)       38   \n",
       "25                         Global Women's Issues (S/GWI)       43   \n",
       "16                                Energy Resources (ENR)       29   \n",
       "50                   Science & Technology Adviser (STAS)       30   \n",
       "\n",
       "Title  Team Member     ratio  \n",
       "23               9  0.290323  \n",
       "26              14  0.400000  \n",
       "39              29  0.402778  \n",
       "5               15  0.454545  \n",
       "36              15  0.535714  \n",
       "49              27  0.551020  \n",
       "22              22  0.578947  \n",
       "25              25  0.581395  \n",
       "16              17  0.586207  \n",
       "50              18  0.600000  "
      ]
     },
     "execution_count": 565,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "titles = titles.groupby(['Title','Bureau']).Name.sum().unstack().transpose().reset_index()\n",
    "titles['ratio'] = titles['Team Member'] / titles['Manager']\n",
    "titles.sort_values('ratio',ascending=True)[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 3\n",
    "Which bureaus receive the highest workload with respect to their overall resourcing?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 570,
   "metadata": {},
   "outputs": [],
   "source": [
    "titles_b = titles.set_index('Bureau')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 597,
   "metadata": {},
   "outputs": [],
   "source": [
    "paper_ = pd.DataFrame(paper.groupby('Bureau')['Paper ID'].count()).reset_index()\n",
    "paper_ = paper_.join(titles_b, on='Bureau')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 598,
   "metadata": {},
   "outputs": [],
   "source": [
    "paper_['Count Papers'] = paper_['Paper ID'] \n",
    "paper_['ratio'] = paper_['Count Papers'] / paper_['Team Member']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 599,
   "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>Bureau</th>\n",
       "      <th>Paper ID</th>\n",
       "      <th>Manager</th>\n",
       "      <th>Team Member</th>\n",
       "      <th>ratio</th>\n",
       "      <th>Count Papers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Global Criminal Justice (GCJ)</td>\n",
       "      <td>63</td>\n",
       "      <td>31</td>\n",
       "      <td>9</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Legislative Affairs (H)</td>\n",
       "      <td>89</td>\n",
       "      <td>28</td>\n",
       "      <td>15</td>\n",
       "      <td>5.933333</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Quadrennial Diplomacy and Development Review (...</td>\n",
       "      <td>155</td>\n",
       "      <td>49</td>\n",
       "      <td>27</td>\n",
       "      <td>5.740741</td>\n",
       "      <td>155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Global Youth Issues (GYI)</td>\n",
       "      <td>78</td>\n",
       "      <td>35</td>\n",
       "      <td>14</td>\n",
       "      <td>5.571429</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Mission to the United Nations (USUN)</td>\n",
       "      <td>138</td>\n",
       "      <td>72</td>\n",
       "      <td>29</td>\n",
       "      <td>4.758621</td>\n",
       "      <td>138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Chief Information Officer (CIO)</td>\n",
       "      <td>66</td>\n",
       "      <td>33</td>\n",
       "      <td>15</td>\n",
       "      <td>4.400000</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Energy Resources (ENR)</td>\n",
       "      <td>69</td>\n",
       "      <td>29</td>\n",
       "      <td>17</td>\n",
       "      <td>4.058824</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Oceans and International Environmental and Sci...</td>\n",
       "      <td>73</td>\n",
       "      <td>16</td>\n",
       "      <td>18</td>\n",
       "      <td>4.055556</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Population, Refugees, and Migration (PRM)</td>\n",
       "      <td>73</td>\n",
       "      <td>17</td>\n",
       "      <td>19</td>\n",
       "      <td>3.842105</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Inspector General (OIG)</td>\n",
       "      <td>76</td>\n",
       "      <td>14</td>\n",
       "      <td>20</td>\n",
       "      <td>3.800000</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               Bureau  Paper ID  Manager  \\\n",
       "23                      Global Criminal Justice (GCJ)        63       31   \n",
       "36                            Legislative Affairs (H)        89       28   \n",
       "49  Quadrennial Diplomacy and Development Review (...       155       49   \n",
       "26                          Global Youth Issues (GYI)        78       35   \n",
       "39               Mission to the United Nations (USUN)       138       72   \n",
       "5                     Chief Information Officer (CIO)        66       33   \n",
       "16                             Energy Resources (ENR)        69       29   \n",
       "41  Oceans and International Environmental and Sci...        73       16   \n",
       "46          Population, Refugees, and Migration (PRM)        73       17   \n",
       "29                            Inspector General (OIG)        76       14   \n",
       "\n",
       "    Team Member     ratio  Count Papers  \n",
       "23            9  7.000000            63  \n",
       "36           15  5.933333            89  \n",
       "49           27  5.740741           155  \n",
       "26           14  5.571429            78  \n",
       "39           29  4.758621           138  \n",
       "5            15  4.400000            66  \n",
       "16           17  4.058824            69  \n",
       "41           18  4.055556            73  \n",
       "46           19  3.842105            73  \n",
       "29           20  3.800000            76  "
      ]
     },
     "execution_count": 599,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "paper_.sort_values('ratio', ascending=False)[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 4\n",
    "Which bureaus are most often contributing staff to task forces or forced to send staff on TDY?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 600,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdy = pd.DataFrame(EE.groupby(['Bureau','TDY']).Name.count()).reset_index()\n",
    "tdy = tdy[tdy['TDY'] == 'Yes']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 602,
   "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>Bureau</th>\n",
       "      <th>TDY</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Diplomatic Security (DS)</td>\n",
       "      <td>Yes</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>Western Hemisphere Affairs (WHA)</td>\n",
       "      <td>Yes</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>Human Resources (DGHR)</td>\n",
       "      <td>Yes</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>Information Resource Management (IRM)</td>\n",
       "      <td>Yes</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>South and Central Asian Affairs (SCA)</td>\n",
       "      <td>Yes</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>Near Eastern Affairs (NEA)</td>\n",
       "      <td>Yes</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>East Asian and Pacific Affairs (EAP)</td>\n",
       "      <td>Yes</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Democracy, Human Rights, and Labor (DRL)</td>\n",
       "      <td>Yes</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>African Affairs (AF)</td>\n",
       "      <td>Yes</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>European and Eurasian Affairs (EUR)</td>\n",
       "      <td>Yes</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       Bureau  TDY  Name\n",
       "23                   Diplomatic Security (DS)  Yes    32\n",
       "105          Western Hemisphere Affairs (WHA)  Yes    28\n",
       "55                     Human Resources (DGHR)  Yes    27\n",
       "57      Information Resource Management (IRM)  Yes    26\n",
       "101     South and Central Asian Affairs (SCA)  Yes    25\n",
       "81                 Near Eastern Affairs (NEA)  Yes    24\n",
       "27       East Asian and Pacific Affairs (EAP)  Yes    24\n",
       "21   Democracy, Human Rights, and Labor (DRL)  Yes    22\n",
       "3                        African Affairs (AF)  Yes    20\n",
       "35        European and Eurasian Affairs (EUR)  Yes    17"
      ]
     },
     "execution_count": 602,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tdy.sort_values('Name', ascending=False)[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 5\n",
    "Which bureaus have excess office space that might be allocated to other, less well-equipped bureaus?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 614,
   "metadata": {},
   "outputs": [],
   "source": [
    "RE_size = pd.DataFrame(RE.groupby('Bureau').Size.sum()).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 617,
   "metadata": {},
   "outputs": [],
   "source": [
    "titles['Total EEs'] = titles['Manager'] + titles['Team Member']\n",
    "titles_ = titles[['Bureau','Total EEs']]\n",
    "RE_size = RE_size.join(titles_.set_index('Bureau'), on='Bureau')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 619,
   "metadata": {},
   "outputs": [],
   "source": [
    "RE_size['Ratio'] = RE_size.Size / RE_size['Total EEs']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 624,
   "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>Bureau</th>\n",
       "      <th>Size</th>\n",
       "      <th>Total EEs</th>\n",
       "      <th>Ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Population, Refugees, and Migration (PRM)</td>\n",
       "      <td>3450</td>\n",
       "      <td>36</td>\n",
       "      <td>95.833333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Foreign Assistance (F)</td>\n",
       "      <td>7750</td>\n",
       "      <td>86</td>\n",
       "      <td>90.116279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Human Resources (DGHR)</td>\n",
       "      <td>40280</td>\n",
       "      <td>451</td>\n",
       "      <td>89.312639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>International Information Programs (IIP)</td>\n",
       "      <td>19820</td>\n",
       "      <td>227</td>\n",
       "      <td>87.312775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Economic and Business Affairs (EB)</td>\n",
       "      <td>20400</td>\n",
       "      <td>240</td>\n",
       "      <td>85.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>African Affairs (AF)</td>\n",
       "      <td>35500</td>\n",
       "      <td>419</td>\n",
       "      <td>84.725537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Public Affairs (PA)</td>\n",
       "      <td>19020</td>\n",
       "      <td>225</td>\n",
       "      <td>84.533333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Budget and Planning (BP)</td>\n",
       "      <td>7490</td>\n",
       "      <td>90</td>\n",
       "      <td>83.222222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>Western Hemisphere Affairs (WHA)</td>\n",
       "      <td>38840</td>\n",
       "      <td>468</td>\n",
       "      <td>82.991453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Foreign Service Institute (FSI)</td>\n",
       "      <td>20490</td>\n",
       "      <td>247</td>\n",
       "      <td>82.955466</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       Bureau   Size  Total EEs      Ratio\n",
       "46  Population, Refugees, and Migration (PRM)   3450         36  95.833333\n",
       "19                     Foreign Assistance (F)   7750         86  90.116279\n",
       "27                     Human Resources (DGHR)  40280        451  89.312639\n",
       "31   International Information Programs (IIP)  19820        227  87.312775\n",
       "14         Economic and Business Affairs (EB)  20400        240  85.000000\n",
       "1                        African Affairs (AF)  35500        419  84.725537\n",
       "48                        Public Affairs (PA)  19020        225  84.533333\n",
       "4                    Budget and Planning (BP)   7490         90  83.222222\n",
       "53           Western Hemisphere Affairs (WHA)  38840        468  82.991453\n",
       "21            Foreign Service Institute (FSI)  20490        247  82.955466"
      ]
     },
     "execution_count": 624,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "RE_size.sort_values('Ratio',ascending=False)[:10]"
   ]
  }
 ],
 "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.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}