01-prep_SwissNames3D-preparation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Prepare SwissNames3D\n",
    "We project all records to lat,lon and we create unique IDs for each row, rather than for each geographical feature (which may have several rows when it has multiple names). The new UUIDs will sit in a new column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import time\n",
    "\n",
    "# own modules\n",
    "from gazmatch import gazetteers\n",
    "from gazmatch import projection"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Start from the original SwissNames3D data\n",
    "The data can be downloaded from here: https://shop.swisstopo.admin.ch/en/products/landscape/names3D ."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 223818 point records in the dataset and 13 columns\n",
      "There are 10518 line records in the dataset and 12 columns\n",
      "There are 79226 polygon records in the dataset and 12 columns\n",
      "The final dataset has 313562 rows and 17 columns\n"
     ]
    }
   ],
   "source": [
    "# path to SwissNames3D gazetteer CSV data, LV03 projection\n",
    "swissnames_data_dir = \"data\\\\SwissNames3D\\\\swissNAMES3D_LV03\\\\csv_LV03_LN02\\\\\"\n",
    "swissnames = gazetteers.SwissNames3D(data_dir=swissnames_data_dir, verbose=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>UUID</th>\n",
       "      <th>NAME</th>\n",
       "      <th>GEOMTYPE</th>\n",
       "      <th>OBJEKTKLASSE_TLM</th>\n",
       "      <th>OBJEKTART</th>\n",
       "      <th>SPRACHCODE</th>\n",
       "      <th>E</th>\n",
       "      <th>N</th>\n",
       "      <th>Z</th>\n",
       "      <th>HOEHE</th>\n",
       "      <th>EINWOHNERKATEGORIE</th>\n",
       "      <th>POPCATINT</th>\n",
       "      <th>GEBAEUDENUTZUNG</th>\n",
       "      <th>KUNSTBAUTE</th>\n",
       "      <th>NAMENGRUPPE_UUID</th>\n",
       "      <th>NAMEN_TYP</th>\n",
       "      <th>NAME_UUID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{B5BB01ED-B7FD-4885-8EA7-C36284A02BD2}</td>\n",
       "      <td>Inseli</td>\n",
       "      <td>Point</td>\n",
       "      <td>TLM_FLURNAME</td>\n",
       "      <td>Flurname swisstopo</td>\n",
       "      <td>Hochdeutsch inkl. Lokalsprachen</td>\n",
       "      <td>745472.134</td>\n",
       "      <td>263766.530</td>\n",
       "      <td>464.643</td>\n",
       "      <td>-999998.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Einfacher Name</td>\n",
       "      <td>{98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{0EF4EB85-FEDB-47D8-8A91-110685CA0F2F}</td>\n",
       "      <td>Inseli</td>\n",
       "      <td>Point</td>\n",
       "      <td>TLM_FLURNAME</td>\n",
       "      <td>Flurname swisstopo</td>\n",
       "      <td>Hochdeutsch inkl. Lokalsprachen</td>\n",
       "      <td>746368.863</td>\n",
       "      <td>270477.034</td>\n",
       "      <td>395.859</td>\n",
       "      <td>-999998.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Einfacher Name</td>\n",
       "      <td>{98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{93C4A671-93FC-441D-87EF-0BEBB0EB493B}</td>\n",
       "      <td>Inseli</td>\n",
       "      <td>Point</td>\n",
       "      <td>TLM_FLURNAME</td>\n",
       "      <td>Flurname swisstopo</td>\n",
       "      <td>Hochdeutsch inkl. Lokalsprachen</td>\n",
       "      <td>735713.639</td>\n",
       "      <td>268124.711</td>\n",
       "      <td>444.269</td>\n",
       "      <td>-999998.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Einfacher Name</td>\n",
       "      <td>{98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{2217592E-7014-475B-9756-B879F5FBA0EB}</td>\n",
       "      <td>Inseli</td>\n",
       "      <td>Point</td>\n",
       "      <td>TLM_FLURNAME</td>\n",
       "      <td>Flurname swisstopo</td>\n",
       "      <td>Hochdeutsch inkl. Lokalsprachen</td>\n",
       "      <td>714751.671</td>\n",
       "      <td>272368.015</td>\n",
       "      <td>397.686</td>\n",
       "      <td>-999998.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Einfacher Name</td>\n",
       "      <td>{98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{FF40EF90-6608-48E1-B8F1-21FB845C6494}</td>\n",
       "      <td>Inseli</td>\n",
       "      <td>Point</td>\n",
       "      <td>TLM_FLURNAME</td>\n",
       "      <td>Flurname swisstopo</td>\n",
       "      <td>Hochdeutsch inkl. Lokalsprachen</td>\n",
       "      <td>700430.150</td>\n",
       "      <td>272104.005</td>\n",
       "      <td>374.386</td>\n",
       "      <td>-999998.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Einfacher Name</td>\n",
       "      <td>{98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     UUID    NAME GEOMTYPE OBJEKTKLASSE_TLM  \\\n",
       "0  {B5BB01ED-B7FD-4885-8EA7-C36284A02BD2}  Inseli    Point     TLM_FLURNAME   \n",
       "1  {0EF4EB85-FEDB-47D8-8A91-110685CA0F2F}  Inseli    Point     TLM_FLURNAME   \n",
       "2  {93C4A671-93FC-441D-87EF-0BEBB0EB493B}  Inseli    Point     TLM_FLURNAME   \n",
       "3  {2217592E-7014-475B-9756-B879F5FBA0EB}  Inseli    Point     TLM_FLURNAME   \n",
       "4  {FF40EF90-6608-48E1-B8F1-21FB845C6494}  Inseli    Point     TLM_FLURNAME   \n",
       "\n",
       "            OBJEKTART                       SPRACHCODE           E  \\\n",
       "0  Flurname swisstopo  Hochdeutsch inkl. Lokalsprachen  745472.134   \n",
       "1  Flurname swisstopo  Hochdeutsch inkl. Lokalsprachen  746368.863   \n",
       "2  Flurname swisstopo  Hochdeutsch inkl. Lokalsprachen  735713.639   \n",
       "3  Flurname swisstopo  Hochdeutsch inkl. Lokalsprachen  714751.671   \n",
       "4  Flurname swisstopo  Hochdeutsch inkl. Lokalsprachen  700430.150   \n",
       "\n",
       "            N        Z     HOEHE EINWOHNERKATEGORIE  POPCATINT  \\\n",
       "0  263766.530  464.643 -999998.0                NaN          0   \n",
       "1  270477.034  395.859 -999998.0                NaN          0   \n",
       "2  268124.711  444.269 -999998.0                NaN          0   \n",
       "3  272368.015  397.686 -999998.0                NaN          0   \n",
       "4  272104.005  374.386 -999998.0                NaN          0   \n",
       "\n",
       "  GEBAEUDENUTZUNG KUNSTBAUTE NAMENGRUPPE_UUID       NAMEN_TYP  \\\n",
       "0             NaN        NaN              NaN  Einfacher Name   \n",
       "1             NaN        NaN              NaN  Einfacher Name   \n",
       "2             NaN        NaN              NaN  Einfacher Name   \n",
       "3             NaN        NaN              NaN  Einfacher Name   \n",
       "4             NaN        NaN              NaN  Einfacher Name   \n",
       "\n",
       "                                NAME_UUID  \n",
       "0  {98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}  \n",
       "1  {98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}  \n",
       "2  {98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}  \n",
       "3  {98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}  \n",
       "4  {98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "swissnames.df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Project each record to lat lon"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(313562, 17)\n"
     ]
    }
   ],
   "source": [
    "swissnames_copy = swissnames.df.copy()\n",
    "print(swissnames_copy.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Start index was 0\n",
      "Started with 25000 swiss coordinates.\n",
      "Ended up with 25000 lat-lon coordinates.\n",
      "Start index was 25000\n",
      "Started with 25000 swiss coordinates.\n",
      "Ended up with 25000 lat-lon coordinates.\n",
      "Start index was 50000\n",
      "Started with 25000 swiss coordinates.\n",
      "Ended up with 25000 lat-lon coordinates.\n",
      "Start index was 75000\n",
      "Started with 25000 swiss coordinates.\n",
      "Ended up with 25000 lat-lon coordinates.\n",
      "Start index was 100000\n",
      "Started with 25000 swiss coordinates.\n",
      "Ended up with 25000 lat-lon coordinates.\n",
      "Start index was 125000\n",
      "Started with 25000 swiss coordinates.\n",
      "Ended up with 25000 lat-lon coordinates.\n",
      "Start index was 150000\n",
      "Started with 25000 swiss coordinates.\n",
      "Ended up with 25000 lat-lon coordinates.\n",
      "Start index was 175000\n",
      "Started with 25000 swiss coordinates.\n",
      "Ended up with 25000 lat-lon coordinates.\n",
      "Start index was 200000\n",
      "Started with 25000 swiss coordinates.\n",
      "Ended up with 25000 lat-lon coordinates.\n",
      "Start index was 225000\n",
      "Started with 25000 swiss coordinates.\n",
      "Ended up with 25000 lat-lon coordinates.\n",
      "Start index was 250000\n",
      "Started with 25000 swiss coordinates.\n",
      "Ended up with 25000 lat-lon coordinates.\n",
      "Start index was 275000\n",
      "Started with 25000 swiss coordinates.\n",
      "Ended up with 25000 lat-lon coordinates.\n",
      "Start index was 300000\n",
      "Started with 13562 swiss coordinates.\n",
      "Ended up with 13562 lat-lon coordinates.\n",
      "13\n"
     ]
    }
   ],
   "source": [
    "max_features = 25000\n",
    "start_index = 0\n",
    "dfs = []\n",
    "while (start_index+1) <= swissnames_copy.shape[0]:\n",
    "    print(\"Start index was %s\" %start_index)\n",
    "    swissnames_subset = swissnames_copy.iloc[start_index:start_index+max_features].copy() # select 25k features\n",
    "    Es = swissnames_subset['E'].tolist()\n",
    "    Ns = swissnames_subset['N'].tolist()\n",
    "    swiss_coords = list(zip(Es, Ns))\n",
    "    time1 = time.time()\n",
    "    print(\"Started with %s swiss coordinates.\" %len(swiss_coords))\n",
    "    lat_lon_coords = projection.swiss_to_latlon_batch(swiss_coords)\n",
    "    print(\"Ended up with %s lat-lon coordinates.\" %len(lat_lon_coords))\n",
    "    time2 = time.time()\n",
    "    #print('Projection took %0.3fs' % ((time2-time1)))\n",
    "    lats, lons = zip(*lat_lon_coords)\n",
    "    swissnames_subset['sn_lat'] = pd.Series(list(lats), index=swissnames_subset.index)\n",
    "    swissnames_subset['sn_lon'] = pd.Series(list(lons), index=swissnames_subset.index)\n",
    "    dfs.append(swissnames_subset)\n",
    "    start_index += max_features\n",
    "\n",
    "print(len(dfs))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(313562, 19)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# concatenate back into one\n",
    "df_all = pd.concat(dfs, axis=0)\n",
    "df_all.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(0, 19)\n",
      "(0, 19)\n"
     ]
    }
   ],
   "source": [
    "# sanity check (there should not be any nulls)\n",
    "print(df_all[df_all['sn_lat'].isnull()].shape)\n",
    "print(df_all[df_all['sn_lon'].isnull()].shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prepare to create new UUIDs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "We have 312242 id groups for 313562 features\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1    311077\n",
       "2      1062\n",
       "3        54\n",
       "4        46\n",
       "5         3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# how many UUIDs appear more than once and how many times?\n",
    "id_groups = df_all.groupby(['UUID'])\n",
    "print(\"We have %s id groups for %s features\" %(len(id_groups), df_all.shape[0]))\n",
    "id_groups.size().value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['UUID_old', 'NAME', 'GEOMTYPE', 'OBJEKTKLASSE_TLM', 'OBJEKTART',\n",
       "       'SPRACHCODE', 'E', 'N', 'Z', 'HOEHE', 'EINWOHNERKATEGORIE', 'POPCATINT',\n",
       "       'GEBAEUDENUTZUNG', 'KUNSTBAUTE', 'NAMENGRUPPE_UUID', 'NAMEN_TYP',\n",
       "       'NAME_UUID', 'sn_lat', 'sn_lon'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we want the new column to be called 'UUID', so rename the current one\n",
    "df_all = df_all.rename(columns={'UUID': 'UUID_old'})\n",
    "df_all.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Hochdeutsch inkl. Lokalsprachen           236367\n",
       "Franzoesisch inkl. Lokalsprachen           47043\n",
       "Italienisch inkl. Lokalsprachen            17642\n",
       "Rumantsch Grischun inkl. Lokalsprachen     12447\n",
       "Mehrsprachig                                  55\n",
       "ub                                             5\n",
       "NaN                                            3\n",
       "Name: SPRACHCODE, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# current sprachcode values\n",
    "df_all['SPRACHCODE'].value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# use sprachcodes as a second disambiguator in the new UUID, alongside the name\n",
    "sprachcode_to_lang = {}\n",
    "sprachcode_to_lang['Hochdeutsch inkl. Lokalsprachen'] = 'de'\n",
    "sprachcode_to_lang['Franzoesisch inkl. Lokalsprachen'] = 'fr'\n",
    "sprachcode_to_lang['Italienisch inkl. Lokalsprachen'] = 'it'\n",
    "sprachcode_to_lang['Rumantsch Grischun inkl. Lokalsprachen'] = 'rm'\n",
    "sprachcode_to_lang['Mehrsprachig'] = 'me'\n",
    "sprachcode_to_lang['ub'] = 'na'\n",
    "sprachcode_to_lang['nan'] = 'na'\n",
    "\n",
    "def add_sprach_code(row):\n",
    "    lang_id = sprachcode_to_lang[str(row['SPRACHCODE'])]\n",
    "    return lang_id\n",
    "\n",
    "# add a row with our shorter sprachcode strings\n",
    "df_all['lang_id'] = df_all.apply(add_sprach_code, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "de    236367\n",
       "fr     47043\n",
       "it     17642\n",
       "rm     12447\n",
       "me        55\n",
       "na         8\n",
       "Name: lang_id, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sanity check\n",
    "df_all['lang_id'].value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create new UUIDs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def deduplicate_uuids(row):\n",
    "    name_no_spaces = str(row['NAME']).replace(' ', '')\n",
    "    new_uuid = row['UUID_old'] + '-' + name_no_spaces + '-' + row['lang_id']\n",
    "    return new_uuid\n",
    "\n",
    "# make the new UUIDs for all records\n",
    "df_all['UUID'] = df_all.apply(deduplicate_uuids, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "We now have 313562 id groups for 313562 records\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1    313562\n",
       "dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check that our new UUIDs are all unique\n",
    "id_groups = df_all.groupby(['UUID'])\n",
    "print(\"We now have %s id groups for %s records\" %(len(id_groups), df_all.shape[0]))\n",
    "id_groups.size().value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['{EAE6DEA8-72F3-4BA8-B335-8F70E3FD7835}-Mätteligraben-de',\n",
       " '{D215AE81-B0FD-4250-AE28-A6EE4126B893}-Diesselbach-de',\n",
       " '{8CC7562B-6180-4E1B-B24B-C68797E0A5B6}-Lochmatte-de',\n",
       " '{32632752-A3D5-4077-A9DC-32DD58A973FA}-Summeregg-de',\n",
       " '{D64C1717-2984-40F8-9F0A-F9C0E28B1C58}-Sagimoos-de',\n",
       " '{9C75D3DA-6BAA-4B38-808C-73D45782FE18}-Cheller-de',\n",
       " '{4C2F938E-D794-4DD9-BB7D-4698D39EBA5B}-Hangete-de',\n",
       " '{19ABF510-57B0-45E7-BA57-00AC0369806F}-Geisshalta-de',\n",
       " '{A275FB5E-A5DC-42A9-8026-A501DB14124A}-Halde-de',\n",
       " '{0804D516-0719-48FF-8B27-DE1D68490F6C}-Unterbäch-de']"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view a sample of the new UUIDs\n",
    "df_all['UUID'].sample(n=10).tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Export to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['UUID_old', 'NAME', 'GEOMTYPE', 'OBJEKTKLASSE_TLM', 'OBJEKTART',\n",
       "       'SPRACHCODE', 'E', 'N', 'Z', 'HOEHE', 'EINWOHNERKATEGORIE', 'POPCATINT',\n",
       "       'GEBAEUDENUTZUNG', 'KUNSTBAUTE', 'NAMENGRUPPE_UUID', 'NAMEN_TYP',\n",
       "       'NAME_UUID', 'sn_lat', 'sn_lon', 'lang_id', 'UUID'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>UUID</th>\n",
       "      <th>UUID_old</th>\n",
       "      <th>NAME</th>\n",
       "      <th>GEOMTYPE</th>\n",
       "      <th>OBJEKTKLASSE_TLM</th>\n",
       "      <th>OBJEKTART</th>\n",
       "      <th>SPRACHCODE</th>\n",
       "      <th>lang_id</th>\n",
       "      <th>E</th>\n",
       "      <th>N</th>\n",
       "      <th>...</th>\n",
       "      <th>HOEHE</th>\n",
       "      <th>sn_lat</th>\n",
       "      <th>sn_lon</th>\n",
       "      <th>EINWOHNERKATEGORIE</th>\n",
       "      <th>POPCATINT</th>\n",
       "      <th>GEBAEUDENUTZUNG</th>\n",
       "      <th>KUNSTBAUTE</th>\n",
       "      <th>NAMENGRUPPE_UUID</th>\n",
       "      <th>NAMEN_TYP</th>\n",
       "      <th>NAME_UUID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{B5BB01ED-B7FD-4885-8EA7-C36284A02BD2}-Inseli-de</td>\n",
       "      <td>{B5BB01ED-B7FD-4885-8EA7-C36284A02BD2}</td>\n",
       "      <td>Inseli</td>\n",
       "      <td>Point</td>\n",
       "      <td>TLM_FLURNAME</td>\n",
       "      <td>Flurname swisstopo</td>\n",
       "      <td>Hochdeutsch inkl. Lokalsprachen</td>\n",
       "      <td>de</td>\n",
       "      <td>745472.134</td>\n",
       "      <td>263766.530</td>\n",
       "      <td>...</td>\n",
       "      <td>-999998.0</td>\n",
       "      <td>47.508534</td>\n",
       "      <td>9.369821</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Einfacher Name</td>\n",
       "      <td>{98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{0EF4EB85-FEDB-47D8-8A91-110685CA0F2F}-Inseli-de</td>\n",
       "      <td>{0EF4EB85-FEDB-47D8-8A91-110685CA0F2F}</td>\n",
       "      <td>Inseli</td>\n",
       "      <td>Point</td>\n",
       "      <td>TLM_FLURNAME</td>\n",
       "      <td>Flurname swisstopo</td>\n",
       "      <td>Hochdeutsch inkl. Lokalsprachen</td>\n",
       "      <td>de</td>\n",
       "      <td>746368.863</td>\n",
       "      <td>270477.034</td>\n",
       "      <td>...</td>\n",
       "      <td>-999998.0</td>\n",
       "      <td>47.568670</td>\n",
       "      <td>9.383929</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Einfacher Name</td>\n",
       "      <td>{98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{93C4A671-93FC-441D-87EF-0BEBB0EB493B}-Inseli-de</td>\n",
       "      <td>{93C4A671-93FC-441D-87EF-0BEBB0EB493B}</td>\n",
       "      <td>Inseli</td>\n",
       "      <td>Point</td>\n",
       "      <td>TLM_FLURNAME</td>\n",
       "      <td>Flurname swisstopo</td>\n",
       "      <td>Hochdeutsch inkl. Lokalsprachen</td>\n",
       "      <td>de</td>\n",
       "      <td>735713.639</td>\n",
       "      <td>268124.711</td>\n",
       "      <td>...</td>\n",
       "      <td>-999998.0</td>\n",
       "      <td>47.549809</td>\n",
       "      <td>9.241650</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Einfacher Name</td>\n",
       "      <td>{98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{2217592E-7014-475B-9756-B879F5FBA0EB}-Inseli-de</td>\n",
       "      <td>{2217592E-7014-475B-9756-B879F5FBA0EB}</td>\n",
       "      <td>Inseli</td>\n",
       "      <td>Point</td>\n",
       "      <td>TLM_FLURNAME</td>\n",
       "      <td>Flurname swisstopo</td>\n",
       "      <td>Hochdeutsch inkl. Lokalsprachen</td>\n",
       "      <td>de</td>\n",
       "      <td>714751.671</td>\n",
       "      <td>272368.015</td>\n",
       "      <td>...</td>\n",
       "      <td>-999998.0</td>\n",
       "      <td>47.591965</td>\n",
       "      <td>8.964333</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Einfacher Name</td>\n",
       "      <td>{98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{FF40EF90-6608-48E1-B8F1-21FB845C6494}-Inseli-de</td>\n",
       "      <td>{FF40EF90-6608-48E1-B8F1-21FB845C6494}</td>\n",
       "      <td>Inseli</td>\n",
       "      <td>Point</td>\n",
       "      <td>TLM_FLURNAME</td>\n",
       "      <td>Flurname swisstopo</td>\n",
       "      <td>Hochdeutsch inkl. Lokalsprachen</td>\n",
       "      <td>de</td>\n",
       "      <td>700430.150</td>\n",
       "      <td>272104.005</td>\n",
       "      <td>...</td>\n",
       "      <td>-999998.0</td>\n",
       "      <td>47.591940</td>\n",
       "      <td>8.773898</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Einfacher Name</td>\n",
       "      <td>{98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               UUID  \\\n",
       "0  {B5BB01ED-B7FD-4885-8EA7-C36284A02BD2}-Inseli-de   \n",
       "1  {0EF4EB85-FEDB-47D8-8A91-110685CA0F2F}-Inseli-de   \n",
       "2  {93C4A671-93FC-441D-87EF-0BEBB0EB493B}-Inseli-de   \n",
       "3  {2217592E-7014-475B-9756-B879F5FBA0EB}-Inseli-de   \n",
       "4  {FF40EF90-6608-48E1-B8F1-21FB845C6494}-Inseli-de   \n",
       "\n",
       "                                 UUID_old    NAME GEOMTYPE OBJEKTKLASSE_TLM  \\\n",
       "0  {B5BB01ED-B7FD-4885-8EA7-C36284A02BD2}  Inseli    Point     TLM_FLURNAME   \n",
       "1  {0EF4EB85-FEDB-47D8-8A91-110685CA0F2F}  Inseli    Point     TLM_FLURNAME   \n",
       "2  {93C4A671-93FC-441D-87EF-0BEBB0EB493B}  Inseli    Point     TLM_FLURNAME   \n",
       "3  {2217592E-7014-475B-9756-B879F5FBA0EB}  Inseli    Point     TLM_FLURNAME   \n",
       "4  {FF40EF90-6608-48E1-B8F1-21FB845C6494}  Inseli    Point     TLM_FLURNAME   \n",
       "\n",
       "            OBJEKTART                       SPRACHCODE lang_id           E  \\\n",
       "0  Flurname swisstopo  Hochdeutsch inkl. Lokalsprachen      de  745472.134   \n",
       "1  Flurname swisstopo  Hochdeutsch inkl. Lokalsprachen      de  746368.863   \n",
       "2  Flurname swisstopo  Hochdeutsch inkl. Lokalsprachen      de  735713.639   \n",
       "3  Flurname swisstopo  Hochdeutsch inkl. Lokalsprachen      de  714751.671   \n",
       "4  Flurname swisstopo  Hochdeutsch inkl. Lokalsprachen      de  700430.150   \n",
       "\n",
       "            N  ...     HOEHE     sn_lat    sn_lon  EINWOHNERKATEGORIE  \\\n",
       "0  263766.530  ... -999998.0  47.508534  9.369821                 NaN   \n",
       "1  270477.034  ... -999998.0  47.568670  9.383929                 NaN   \n",
       "2  268124.711  ... -999998.0  47.549809  9.241650                 NaN   \n",
       "3  272368.015  ... -999998.0  47.591965  8.964333                 NaN   \n",
       "4  272104.005  ... -999998.0  47.591940  8.773898                 NaN   \n",
       "\n",
       "  POPCATINT  GEBAEUDENUTZUNG KUNSTBAUTE NAMENGRUPPE_UUID       NAMEN_TYP  \\\n",
       "0         0              NaN        NaN              NaN  Einfacher Name   \n",
       "1         0              NaN        NaN              NaN  Einfacher Name   \n",
       "2         0              NaN        NaN              NaN  Einfacher Name   \n",
       "3         0              NaN        NaN              NaN  Einfacher Name   \n",
       "4         0              NaN        NaN              NaN  Einfacher Name   \n",
       "\n",
       "                                NAME_UUID  \n",
       "0  {98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}  \n",
       "1  {98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}  \n",
       "2  {98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}  \n",
       "3  {98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}  \n",
       "4  {98BFFEB5-D24F-41F6-89DA-54CEDA59ADAD}  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# put the UUID column where we want it: at the front and next to the UUID_old\n",
    "col_order = ['UUID', 'UUID_old', 'NAME', 'GEOMTYPE', 'OBJEKTKLASSE_TLM', 'OBJEKTART', 'SPRACHCODE', 'lang_id', \n",
    "             'E', 'N', 'Z', 'HOEHE', 'sn_lat', 'sn_lon', 'EINWOHNERKATEGORIE', 'POPCATINT', 'GEBAEUDENUTZUNG', \n",
    "             'KUNSTBAUTE', 'NAMENGRUPPE_UUID', 'NAMEN_TYP', 'NAME_UUID']\n",
    "df_all = df_all[col_order]\n",
    "df_all.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# export to csv\n",
    "output_dir = 'data'\n",
    "filename_csv = 'swissnames_new_uuids.csv'\n",
    "df_all.to_csv(path_or_buf=os.path.join(output_dir, filename_csv), sep='\\t', index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}