notebooks/examples/2 - Importing Assignments.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2 - Importing Assignments into Workforce using the ArcGIS API for Python\n",
    "\n",
    "This is a continuation of the previous notebook that was used to configure a new Workforce Project. If you recall, we configured our Project with the following:\n",
    "- 3 Assignment Types\n",
    "    - Fill in Pothole\n",
    "    - Sidewalk Repair\n",
    "    - Paint Crosswalk\n",
    "- 2 Additional Dispatchers\n",
    "- 3 Workers\n",
    "- Assignment Integration with Explorer for ArcGIS\n",
    "\n",
    "Many organizations have existing data in various formats such as CSV files, SQL databases, and existing services and layers. These data may represent assets that need to be inspected, work orders that need to be completed, or some other task that needs to be assigned. In this guide, we'll demonstrate how to add assignments to a Workforce Project from various data sources.\n",
    "\n",
    "This guide uses the ArcGIS API for Python version 1.5.1 with Python 3.6+."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fetching the Project\n",
    "Let's import the ArcGIS API for Python and find the project we previously configured."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter password: ········\n"
     ]
    }
   ],
   "source": [
    "from arcgis.gis import GIS\n",
    "from arcgis.apps import workforce\n",
    "\n",
    "gis = GIS(\"https://arcgis.com\", \"workforce_scripts\")\n",
    "project = workforce.Project(gis.content.search(\"type:'Workforce Project' 'Public Works Work Orders'\")[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importing Assignments From a CSV File\n",
    "Suppose we have a CSV file of potholes (potholes.csv) that need to filled. We can use [pandas](https://pandas.pydata.org/) to read this file and then use the workforce module to create an assignment for each row in the file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Description</th>\n",
       "      <th>Priority</th>\n",
       "      <th>Work_Order_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>State St &amp; W G St</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>CA</td>\n",
       "      <td>Fill in the pothole on State Street near the i...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15th St &amp; E St</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>CA</td>\n",
       "      <td>Fill in the pothole in the middle of the inter...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3119 Ocean View Blvd</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>CA</td>\n",
       "      <td>Fill in the pothole near the curb</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2901 B St</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>CA</td>\n",
       "      <td>Fill in the pothole on the south side</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>598 Ash St</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>CA</td>\n",
       "      <td>Fill in the pothole near Domino's</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>841 N Harbor</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>CA</td>\n",
       "      <td>Fill in the pothole near the curb</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Address       City State  \\\n",
       "0     State St & W G St  San Diego    CA   \n",
       "1        15th St & E St  San Diego    CA   \n",
       "2  3119 Ocean View Blvd  San Diego    CA   \n",
       "3             2901 B St  San Diego    CA   \n",
       "4            598 Ash St  San Diego    CA   \n",
       "5          841 N Harbor  San Diego    CA   \n",
       "\n",
       "                                         Description  Priority  Work_Order_ID  \n",
       "0  Fill in the pothole on State Street near the i...         2              1  \n",
       "1  Fill in the pothole in the middle of the inter...         2              2  \n",
       "2                  Fill in the pothole near the curb         2              3  \n",
       "3              Fill in the pothole on the south side         3              4  \n",
       "4                  Fill in the pothole near Domino's         2              5  \n",
       "5                  Fill in the pothole near the curb         2              6  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "potholes_df = pd.read_csv('data/potholes.csv')\n",
    "potholes_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's iterate over the different potholes that need to be fixed, and create an [Assignment](https://esri.github.io/arcgis-python-api/apidoc/html/arcgis.apps.workforce.html#assignment) object for it. We'll leverage the default geocoder for the organization to find the location for each assignment. It's important to note that we specify the output spatial reference to be WGS 84 Web Mercator Auxiliary Sphere (wkid: 102100), as that is what the Assignments Feature Layer expects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from arcgis.geocoding import geocode\n",
    "\n",
    "pothole_assignments = []\n",
    "for _, row in potholes_df.iterrows():\n",
    "    geometry = geocode(f\"{row['Address']} {row['City']} {row['State']}\", out_sr=102100)[0][\"location\"]\n",
    "    pothole_assignments.append(\n",
    "        workforce.Assignment(\n",
    "            project,\n",
    "            geometry=geometry,\n",
    "            location=row[\"Address\"],\n",
    "            description=row[\"Description\"],\n",
    "            priority=int(row[\"Priority\"]),\n",
    "            work_order_id=row[\"Work_Order_ID\"],\n",
    "            assignment_type=\"Fill In Pothole\",\n",
    "            status=\"unassigned\"\n",
    "        )\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we'll use the [batch_add](https://esri.github.io/arcgis-python-api/apidoc/html/arcgis.apps.workforce.managers.html#arcgis.apps.workforce.managers.AssignmentManager.batch_add) method, which is a method on the [AssignmentManager](https://esri.github.io/arcgis-python-api/apidoc/html/arcgis.apps.workforce.html#assignment) class, to add all of these assignments to the Project."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Assignment 100>,\n",
       " <Assignment 101>,\n",
       " <Assignment 102>,\n",
       " <Assignment 103>,\n",
       " <Assignment 104>,\n",
       " <Assignment 105>]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "project.assignments.batch_add(pothole_assignments)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importing from Feature Layer\n",
    "Suppose our organziation has a hosted feature layer of crosswalks that need to be re-painted. We will use the ArcGIS API for Python to find this layer, query the features, and then create a new [assignment](https://esri.github.io/arcgis-python-api/apidoc/html/arcgis.apps.workforce.html#assignment) for each feature. First, we'll search for the crosswalk item in our organization."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://Nitro.maps.arcgis.com/home/item.html?id=2ec0051ba3834714a639365dace5c4c0' target='_blank'>\n",
       "                        <img src='data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAMgAAACFCAYAAAAenrcsAAAF/0lEQVR4nO2YvXHrOhBGb6MKFTJjwsQzWsdsgA2gBHbAIlgIw4cXQYIgYAHK95qS9nwzJ7DHkvWzh8R+f0TkPwDI80dEPAA88v39jSAAJRAEQAFBABQQBEABQQAUEARAAUEAFBAEQAFBABQQBEABQQAUEARAAUEAFBAEQAFBABQQBEABQQAUEARAAUEAFBAEQAFBABQQBEABQQAUEARAAUEAFBAEQAFBABQQ5E25XC67OPr1visI8gbkBv0ngiBOOwjygvxUhmfEQZg8CNLAbx1balJ8fX0VGYZB/TmmRZajP/NXAkEKhEHRBuqnsmhS5CSYpslP0+Sdc36e5yzhb8Zx9MMw3NEiTfq6rIMgGWpytJ7x9/5tPLTjOHrnnF+Wxa/r6p/Ntm1+XVe/LIt3znkRKUqDJI8gSEIqxziOu48rewnPJyJ+nucfCdEqTRCm7/s7UZDkHgRJSK/23nu/rqt3zj11ZGnZI8Kd4ogMw3CVJLwnmq4bCJIhDPM0TXfDFI4r4agSD5YmTIqIXKXYtu0QMULGcfRd12XvJEd/D68AgkSkV/t5ntXhis/38YKcEhbodV0PFyKNc86fz+cHSbiLIMgDqSBHHXt+M/M8+9Pp5M/nM4JkQJCIVJB/vSy/QtZ1vQrSdR2CJCBIRLpMv9px6F9k27Y7Qfq+R5AIBImIBRGRo2f313I+n9U95Ojv5UgQJKHUYH1yRMSfTqesIEd/H0eDIHITY0+D9UlxzrGoI0ibIGH/sNBghcRNFos6gqiCWGqwQtImi0UdQYqCWGqwQkpNFos6gmQFEbHTYIXQZCFIFYsNVogITVYOBBHbDVYITRaCVAWx2GCF0GQhSFUQiw1WCE0WglQFsdhghdBkIUiTICL2GqwQmiwEUSWx2mCFiNBkpZgXhAbrFposBCkKYrnBCqHJQpCiIJYbrBCaLAQpCmK5wQqhyUIQVRARuw1WCE0WgmQlsd5ghYjQZCFIJAYN1n1oshDkQRAarFtoshDkQRAarFtoshDkQRAarFtoshAkK4gIDVYITRaC3ElCg3UfEZos84LQYJVDk4UgNFhKaLIQhAZLCU0WgtBgKaHJQhAarEposowLEiShwcpHhCbLrCA0WPXQZCEIDZYSmiwEocFSQpOFIDRYSmiyEIQGqxKaLKOCBElosPSI0GSZE4QGqz00WYYFocGqhybLsCA0WPXQZBkWhAarHpos44KI0GDVYr3JMidIkIQGqy0itpssU4LQYO2P9SbLpCA0WO2x3mSZFIQGqz3WmyyTgtBgtcd6k2VWEBEarNZYbrJMCRIkocHaFxG7TZYZQWiwno/lJsucIDRY+2O5yTInyDs3WNu2+XVdvXPOT9N0fT9d1/nT6aTSdd31s3DOeeecX5al6XNImywE+UDescEKMlwul6oAKWGxTtEEGsfRO+cepCk1WQjyQcSCjOP4kneQbdv8six+HMenhr/rOpXwN63yDMNwFSZ+vKWq16QgX19ffhgGPwyDn6bJz/N8mDDhLtH3fZMQNQn6vq/SIlIsTa3mRZAPIN1BhmG4Dkt89QzS7Dmj75FhWZbr/tByl6gNfxA9JVwI4otBjr3yIMiHUhIklqR25IifKyy6JeL/t3dnKEmREyCVoUR898w9tiZO/BrS4xWCfADhS0yPWbkrZ+mMHh859i7N2g6xR4jc4If3FA+sRvq4EtqdKRYDQT6EdEjSIWg9r2sCadR2Bk2I1uHPvd/SZ9AikSblp8thUpCSKNpV85lFt7Y8567MqRitAuw56jzznKXHfLocIsYEiQcgNwy1c3xJnlii9He1BbrlLpG+7t/4fOL/Vfrd0d/lb2BOkNwX3HrMKEnUuijnlmbtiv4qC/Deu9QnYVaQ0gCUhqK27OakSuWq7Q0WB/DVQZACf+Oc3vocR79XKIMgT/A3BIH3AEEAFBAEQAFBABQQBEABQQAUEARAAUEAFBAEQAFBABQQBEABQQAUEARAAUEAFBAEQAFBABQQBEABQQAUEARAAUEAFBAEQAFBABQQBEABQQAUEARAAUEAFP4HOwgCKst/IRIAAAAASUVORK5CYII=' width='200' height='133' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://Nitro.maps.arcgis.com/home/item.html?id=2ec0051ba3834714a639365dace5c4c0' target='_blank'><b>Crosswalk Painting</b>\n",
       "                        </a>\n",
       "                        <br/><img src='https://Nitro.maps.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\">Feature Layer Collection by workforce_scripts\n",
       "                        <br/>Last Modified: November 01, 2018\n",
       "                        <br/>0 comments, 4 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"Crosswalk Painting\" type:Feature Layer Collection owner:workforce_scripts>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crosswalks_item = gis.content.search(\"Crosswalk Painting\")[0]\n",
    "crosswalks_item"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's find all of the crosswalks that need to be painted by querying the first layer in the service. Then we'll convert the results to a spatially enabled dataframe.You can see in the table below, there are 5 crosswalks that need to be painted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>OBJECTID</th>\n",
       "      <th>SHAPE</th>\n",
       "      <th>description</th>\n",
       "      <th>location</th>\n",
       "      <th>priority</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>{\"x\": -13042100.444693483, \"y\": 3856655.475106...</td>\n",
       "      <td>The lines need to be re-painted</td>\n",
       "      <td>6th Ave &amp; K St</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>{\"x\": -13040238.93433077, \"y\": 3856927.4834379...</td>\n",
       "      <td>The cross walks need to be re-painted</td>\n",
       "      <td>Island Ave &amp; 24th St</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>{\"x\": -13039276.604087984, \"y\": 3855691.054789...</td>\n",
       "      <td>New lines need to be painted here.</td>\n",
       "      <td>Ocean View Blvd &amp; South 28th St</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>{\"x\": -13042522.117323115, \"y\": 3858883.420402...</td>\n",
       "      <td>A new crosswalk should be painted here</td>\n",
       "      <td>2nd Ave &amp; Fir St</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>{\"x\": -13039290.712094398, \"y\": 3857929.525105...</td>\n",
       "      <td>The cross walk needs cross-lines painted</td>\n",
       "      <td>B St &amp; 28th St</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   OBJECTID                                              SHAPE  \\\n",
       "0         1  {\"x\": -13042100.444693483, \"y\": 3856655.475106...   \n",
       "1         2  {\"x\": -13040238.93433077, \"y\": 3856927.4834379...   \n",
       "2         3  {\"x\": -13039276.604087984, \"y\": 3855691.054789...   \n",
       "3         4  {\"x\": -13042522.117323115, \"y\": 3858883.420402...   \n",
       "4         5  {\"x\": -13039290.712094398, \"y\": 3857929.525105...   \n",
       "\n",
       "                                description                         location  \\\n",
       "0           The lines need to be re-painted                   6th Ave & K St   \n",
       "1     The cross walks need to be re-painted             Island Ave & 24th St   \n",
       "2        New lines need to be painted here.  Ocean View Blvd & South 28th St   \n",
       "3    A new crosswalk should be painted here                 2nd Ave & Fir St   \n",
       "4  The cross walk needs cross-lines painted                   B St & 28th St   \n",
       "\n",
       "   priority  \n",
       "0         3  \n",
       "1         3  \n",
       "2         1  \n",
       "3         1  \n",
       "4         2  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crosswalks_df = crosswalks_item.layers[0].query().sdf\n",
    "crosswalks_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we'll iterate over the dataframe and create an [Assignment](https://esri.github.io/arcgis-python-api/apidoc/html/arcgis.apps.workforce.html#assignment) object for each row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "crosswalk_assignments = []\n",
    "for _, row in crosswalks_df.iterrows():\n",
    "    crosswalk_assignments.append(\n",
    "        workforce.Assignment(\n",
    "            project,\n",
    "            geometry=row[\"SHAPE\"],\n",
    "            location=row[\"location\"],\n",
    "            description=row[\"description\"],\n",
    "            priority=int(row[\"priority\"]),\n",
    "            assignment_type=\"Paint Crosswalk\",\n",
    "            status=\"unassigned\"\n",
    "        )\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then we'll use the batch_add method to add all of these assignments to the Project like we did before."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Assignment 106>,\n",
       " <Assignment 107>,\n",
       " <Assignment 108>,\n",
       " <Assignment 109>,\n",
       " <Assignment 110>]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "project.assignments.batch_add(crosswalk_assignments)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importing from a SQL Database\n",
    "Suppose our organziation has a SQLite table of work orders that need to be completed. We can use pandas and the standard [sqlite3 module](https://docs.python.org/3/library/sqlite3.html) to read this table and then use the workforce module to create an assignment for each row in the table. It's also possible to use [pyodbc](https://github.com/mkleehammer/pyodbc) to connect to other databases like SQL Server, Postgres, and Oracle."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "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>index</th>\n",
       "      <th>id</th>\n",
       "      <th>due_date</th>\n",
       "      <th>address</th>\n",
       "      <th>priority</th>\n",
       "      <th>type</th>\n",
       "      <th>status</th>\n",
       "      <th>description</th>\n",
       "      <th>notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2018-07-12 00:00:00</td>\n",
       "      <td>Colombia St &amp; Broadway, San Diego, CA</td>\n",
       "      <td>1</td>\n",
       "      <td>Sidewalk Repair</td>\n",
       "      <td>Completed</td>\n",
       "      <td>The sidewalk needs to be fixed.</td>\n",
       "      <td>Done</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2018-07-13 00:00:00</td>\n",
       "      <td>1800 Fifth Ave, San Diego, CA</td>\n",
       "      <td>1</td>\n",
       "      <td>Sidewalk Repair</td>\n",
       "      <td>Completed</td>\n",
       "      <td>The sidewalk is uneven due to tree roots.</td>\n",
       "      <td>Finished</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2018-07-14 00:00:00</td>\n",
       "      <td>2115 Imperial Ave, San Diego, CA</td>\n",
       "      <td>2</td>\n",
       "      <td>Sidewalk Repair</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>The sidewalk is very uneven.</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2018-07-15 00:00:00</td>\n",
       "      <td>South Evans St &amp; Franklin Ave, San Diego, CA</td>\n",
       "      <td>2</td>\n",
       "      <td>Sidewalk Repair</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>Please fix the sidewalk near the intersection</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2018-07-16 00:00:00</td>\n",
       "      <td>Market St &amp; 31st St, San Diego, CA</td>\n",
       "      <td>3</td>\n",
       "      <td>Sidewalk Repair</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>Fix my side walk</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>2018-07-12 00:00:00</td>\n",
       "      <td>Ivy St &amp; Fern St, San Diego, CA</td>\n",
       "      <td>3</td>\n",
       "      <td>Sidewalk Repair</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>Fix the side walk in front of my shop</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  id             due_date  \\\n",
       "0      0   1  2018-07-12 00:00:00   \n",
       "1      1   2  2018-07-13 00:00:00   \n",
       "2      2   3  2018-07-14 00:00:00   \n",
       "3      3   4  2018-07-15 00:00:00   \n",
       "4      4   5  2018-07-16 00:00:00   \n",
       "5      5   6  2018-07-12 00:00:00   \n",
       "\n",
       "                                        address  priority             type  \\\n",
       "0         Colombia St & Broadway, San Diego, CA         1  Sidewalk Repair   \n",
       "1                 1800 Fifth Ave, San Diego, CA         1  Sidewalk Repair   \n",
       "2              2115 Imperial Ave, San Diego, CA         2  Sidewalk Repair   \n",
       "3  South Evans St & Franklin Ave, San Diego, CA         2  Sidewalk Repair   \n",
       "4            Market St & 31st St, San Diego, CA         3  Sidewalk Repair   \n",
       "5               Ivy St & Fern St, San Diego, CA         3  Sidewalk Repair   \n",
       "\n",
       "      status                                    description     notes  \n",
       "0  Completed                The sidewalk needs to be fixed.      Done  \n",
       "1  Completed      The sidewalk is uneven due to tree roots.  Finished  \n",
       "2    Backlog                   The sidewalk is very uneven.      None  \n",
       "3    Backlog  Please fix the sidewalk near the intersection      None  \n",
       "4    Backlog                               Fix my side walk      None  \n",
       "5    Backlog          Fix the side walk in front of my shop      None  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3\n",
    "connection = sqlite3.connect(\"data/work_orders.sqlite3\")\n",
    "work_orders_df = pd.read_sql_query(\"select * from work_orders\", connection)\n",
    "work_orders_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice how some of the work orders have already been completed. We'll re-issue our query to only find work orders that are in the backlog and that are Sidewalk Repairs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>index</th>\n",
       "      <th>id</th>\n",
       "      <th>due_date</th>\n",
       "      <th>address</th>\n",
       "      <th>priority</th>\n",
       "      <th>type</th>\n",
       "      <th>status</th>\n",
       "      <th>description</th>\n",
       "      <th>notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2018-07-14 00:00:00</td>\n",
       "      <td>2115 Imperial Ave, San Diego, CA</td>\n",
       "      <td>2</td>\n",
       "      <td>Sidewalk Repair</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>The sidewalk is very uneven.</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2018-07-15 00:00:00</td>\n",
       "      <td>South Evans St &amp; Franklin Ave, San Diego, CA</td>\n",
       "      <td>2</td>\n",
       "      <td>Sidewalk Repair</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>Please fix the sidewalk near the intersection</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2018-07-16 00:00:00</td>\n",
       "      <td>Market St &amp; 31st St, San Diego, CA</td>\n",
       "      <td>3</td>\n",
       "      <td>Sidewalk Repair</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>Fix my side walk</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>2018-07-12 00:00:00</td>\n",
       "      <td>Ivy St &amp; Fern St, San Diego, CA</td>\n",
       "      <td>3</td>\n",
       "      <td>Sidewalk Repair</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>Fix the side walk in front of my shop</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  id             due_date  \\\n",
       "0      2   3  2018-07-14 00:00:00   \n",
       "1      3   4  2018-07-15 00:00:00   \n",
       "2      4   5  2018-07-16 00:00:00   \n",
       "3      5   6  2018-07-12 00:00:00   \n",
       "\n",
       "                                        address  priority             type  \\\n",
       "0              2115 Imperial Ave, San Diego, CA         2  Sidewalk Repair   \n",
       "1  South Evans St & Franklin Ave, San Diego, CA         2  Sidewalk Repair   \n",
       "2            Market St & 31st St, San Diego, CA         3  Sidewalk Repair   \n",
       "3               Ivy St & Fern St, San Diego, CA         3  Sidewalk Repair   \n",
       "\n",
       "    status                                    description notes  \n",
       "0  Backlog                   The sidewalk is very uneven.  None  \n",
       "1  Backlog  Please fix the sidewalk near the intersection  None  \n",
       "2  Backlog                               Fix my side walk  None  \n",
       "3  Backlog          Fix the side walk in front of my shop  None  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "work_orders_df = pd.read_sql_query(\"select * from work_orders where status='Backlog' and type='Sidewalk Repair'\", connection)\n",
    "work_orders_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's iterate over the records and create an assignment for each work order. We'll geocode the address field to find the x,y coordinates of the work order. We'll also set the due date since it's available in the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "sidewalk_assignments = []\n",
    "for _, row in work_orders_df.iterrows():\n",
    "    geometry = geocode(f\"{row['address']}\", out_sr=102100)[0][\"location\"]\n",
    "    sidewalk_assignments.append(\n",
    "        workforce.Assignment(\n",
    "            project,\n",
    "            geometry=geometry,\n",
    "            location=row[\"address\"],\n",
    "            description=row[\"description\"],\n",
    "            priority=int(row[\"priority\"]),\n",
    "            assignment_type=\"Sidewalk Repair\",\n",
    "            status=\"unassigned\",\n",
    "            due_date=pd.to_datetime(row.due_date)\n",
    "        )\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll again use the batch_add method to add the assignments."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Assignment 111>, <Assignment 112>, <Assignment 113>, <Assignment 114>]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "project.assignments.batch_add(sidewalk_assignments)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Summary\n",
    "\n",
    "We demostrated how to use the power of pandas dataframes to create assignments. We were able to convert a CSV file, a SQL table, and a feature layer query result into a dataframe. It's also possible to create dataframes from Excel files, shapefiles, file geodatabases, and other data sources. By using the workforce module to add assignments, all of the underlying Workforce rules are enforced to ensure the integrity of the project."
   ]
  }
 ],
 "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.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}