Import-Trees-from-CSV.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Importing trees from a CSV\n",
    "\n",
    "Step one: find and load the CSV file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "arboladolineal.csv  \u001b[34mmendoza_trees\u001b[m\u001b[m/      \u001b[34mvenv\u001b[m\u001b[m/\r\n"
     ]
    }
   ],
   "source": [
    "ls .."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "ename": "UnicodeDecodeError",
     "evalue": "'utf-8' codec can't decode byte 0xda in position 12: invalid continuation byte",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mUnicodeDecodeError\u001b[0m                        Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-2-465f8e499103>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdata\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mopen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'../arboladolineal.csv'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/usr/local/Cellar/python/3.6.5/Frameworks/Python.framework/Versions/3.6/lib/python3.6/codecs.py\u001b[0m in \u001b[0;36mdecode\u001b[0;34m(self, input, final)\u001b[0m\n\u001b[1;32m    319\u001b[0m         \u001b[0;31m# decode input (taking the buffer into account)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    320\u001b[0m         \u001b[0mdata\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbuffer\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0minput\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 321\u001b[0;31m         \u001b[0;34m(\u001b[0m\u001b[0mresult\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconsumed\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_buffer_decode\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0merrors\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfinal\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    322\u001b[0m         \u001b[0;31m# keep undecoded input until the next call\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    323\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbuffer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mconsumed\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mUnicodeDecodeError\u001b[0m: 'utf-8' codec can't decode byte 0xda in position 12: invalid continuation byte"
     ]
    }
   ],
   "source": [
    "data = open('../arboladolineal.csv').read()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This file clearly isn't UTF-8 - but then what is it?\n",
    "\n",
    "We can read the data in as bytes using `mode='rb'`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = open('../arboladolineal.csv', 'rb').read()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "b'FID;Especie;\\xdaltima modificaci\\xf3n;Altura;Circunferencia tronco (cm);Di\\xe1metro tronco;Inclinaci\\xf3n;Longitud;Latitud\\r\\n0;Jacarand;6/5/2011 14:49;Medio (4 - 8 mts);600.000.000;Mediano;Nula (< 15\\xf8);-68.840.414.843.900.000;-32.874.731.181.900.000\\r\\n1;Jacarand;6/5/2011 14:48;Medio (4 - 8 mts);560.000.000;Mediano;Nula (< 15\\xf8);-68.840.399.524.299.900;-32.874.687.014.599.900\\r\\n2;Jacarand;6/5/2011 14:47;Medio (4 - 8 mts);500.000.000;Mediano;Nula (< 15\\xf8);-68.840.393.443.200.000;-32.874.642.858.999.900\\r\\n3;Fresno europeo;2/3/2012 13:26;Bajo (2 - 4 mts);520.000.000;Mediano;Nula (< 15\\xf8);-68.857.421.264.899.900;-32.895.994.448.300.000\\r\\n4;Fresno europeo;2/3/2012 13:25;Bajo (2 - 4 mts);480.000.000;Mediano;Nula (< 15\\xf8);-68.857.416.222.900.000;-32.895.916.002.600.000\\r\\n5;Morera;2/3/2012 13:23;Medio (4 - 8 mts);700.000.000;Grande;Leve (15\\xf8 - 30);-68.857.408.047.199.900;-32.895.857.598.900.000\\r\\n6;Fresno europeo;2/3/2012 13:21;Bajo (2 - 4 mts);600.000.000;Mediano;Nula (< 15\\xf8);-68.857.357.071.400.000;-32.895.826.568.'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[:1000]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since we don't know the file's character encoding, we can use the `chardet` library to make an educated guess:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import chardet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'encoding': 'ISO-8859-1', 'confidence': 0.73, 'language': ''}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chardet.detect(data[:1000])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "OK, let's try opening it as ISO-8859-1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = open('../arboladolineal.csv', encoding='ISO-8859-1').read()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'FID;Especie;Última modificación;Altura;Circunferencia tronco (cm);Diámetro tronco;Inclinación;Longitud;Latitud\\n0;Jacarand;6/5/2011 14:49;Medio (4 - 8 mts);600.000.000;Mediano;Nula (< 15ø);-68.840.414.843.900.000;-32.874.731.181.900.000\\n1;Jacarand;6/5/2011 14:48;Medio (4 - 8 mts);560.000.000;Mediano;Nula (< 15ø);-68.840.399.524.299.900;-32.874.687.014.599.900\\n2;Jacarand;6/5/2011 14:47;Medio (4 - 8 mts);500.000.000;Mediano;Nula (< 15ø);-68.840.393.443.200.000;-32.874.642.858.999.900\\n3;Fresno europeo;2/3/2012 13:26;Bajo (2 - 4 mts);520.000.000;Mediano;Nula (< 15ø);-68.857.421.264.899.900;-32.895.994.448.300.000\\n4;Fresno europeo;2/3/2012 13:25;Bajo (2 - 4 mts);480.000.000;Mediano;Nula (< 15ø);-68.857.416.222.900.000;-32.895.916.002.600.000\\n5;Morera;2/3/2012 13:23;Medio (4 - 8 mts);700.000.000;Grande;Leve (15ø - 30);-68.857.408.047.199.900;-32.895.857.598.900.000\\n6;Fresno europeo;2/3/2012 13:21;Bajo (2 - 4 mts);600.000.000;Mediano;Nula (< 15ø);-68.857.357.071.400.000;-32.895.826.568.600.000'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[:1000]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That looks right - the accents appear to be in the correct place.\n",
    "\n",
    "Now let's split the file into lines:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "lines = data.split('\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'FID;Especie;Última modificación;Altura;Circunferencia tronco (cm);Diámetro tronco;Inclinación;Longitud;Latitud'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lines[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'0;Jacarand;6/5/2011 14:49;Medio (4 - 8 mts);600.000.000;Mediano;Nula (< 15ø);-68.840.414.843.900.000;-32.874.731.181.900.000'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lines[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "''"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lines[-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We only want lines that aren't blank. Let's strip any outstanding whitespace too:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "lines = [l.strip() for l in lines if l.strip()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'48418;Fresno europeo;29/8/2011 10:19;Alto (> 8 mts);1.300.000.000;Grande;Leve (15ø - 30;-68.829.120.483.500.000;-32.868.771.131.599.900'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lines[-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This isn't CSV, it's delimited by semicolons instead."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['0',\n",
       " 'Jacarand',\n",
       " '6/5/2011 14:49',\n",
       " 'Medio (4 - 8 mts)',\n",
       " '600.000.000',\n",
       " 'Mediano',\n",
       " 'Nula (< 15ø)',\n",
       " '-68.840.414.843.900.000',\n",
       " '-32.874.731.181.900.000']"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lines[1].split(';')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Those latitudes and longitudes are in a really weird format. Let's write a function to fix them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "weird = '-68.840.414.843.900.000'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "parts = weird.split('.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'-68'"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parts[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'840414843900000'"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "''.join(parts[1:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fix(value):\n",
    "    parts = value.split('.')\n",
    "    return float('{}.{}'.format(parts[0], ''.join(parts[1:])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-32.8747311819"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fix('-32.874.731.181.900.000')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import each row into a corresponding Django model\n",
    "\n",
    "We have the raw pieces we need to read our CSV file. Let's create a Django Tree instance for every row.\n",
    "\n",
    "First we'll import the Django models and demonstrate that they work. Thanks to `django_extensions` we don't need to do anything special to set up our import environemnt - everything just works:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "from trees.models import Tree, Species"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Tree.objects.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_row(row):\n",
    "    \"Given a row from the CSV, create a Tree in our database\"\n",
    "    # The species name is the second column:\n",
    "    species_name = row[1]\n",
    "    # lat/long are the last two columns, counting from the end:\n",
    "    latitude = row[-1]\n",
    "    longitude = row[-2]\n",
    "    # We want one `Species` object for each unique species name.\n",
    "    # Django's get_or_create is a convenient way of making these:\n",
    "    species, created = Species.objects.get_or_create(\n",
    "        name=species_name\n",
    "    )\n",
    "    # Create the tree instance:\n",
    "    Tree.objects.create(\n",
    "        species=species,\n",
    "        latitude=fix(latitude),\n",
    "        longitude=fix(longitude),\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The first line is the headings, so skip it:\n",
    "for line in lines[1:]:\n",
    "    row = line.split(';')\n",
    "    load_row(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This step takes a few minutes. Once it's done, we can confirm that the trees have been created:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "48419"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Tree.objects.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "33"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Species.objects.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Jacarand', -32.8747311819, -68.8404148439),\n",
       " ('Jacarand', -32.8746870145999, -68.8403995242999),\n",
       " ('Jacarand', -32.8746428589999, -68.8403934432),\n",
       " ('Fresno europeo', -32.8959944483, -68.8574212648999),\n",
       " ('Fresno europeo', -32.8959160026, -68.8574162229),\n",
       " ('Morera', -32.8958575989, -68.8574080471999),\n",
       " ('Fresno europeo', -32.8958265686, -68.8573570714),\n",
       " ('Morera', -32.8958308492999, -68.8572981214),\n",
       " ('Morera', -32.895838834, -68.8572185353999),\n",
       " ('Morera', -32.8958462025, -68.8571426345999)]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[(t.species.name, t.latitude, t.longitude) for t in Tree.objects.all()[:10]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Django Shell-Plus",
   "language": "python",
   "name": "django_extensions"
  },
  "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}