gridviz_tiled_csv.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5844e53f-c747-491f-b4e7-6b525ece8d88",
   "metadata": {},
   "source": [
    "# Tiling csv-files for GridViz  \n",
    "see file format specification at  \n",
    "https://eurostat.github.io/gridviz/docs/tiledformat.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ded0f67b-eb45-45ee-b57d-8a905e8906eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "import math\n",
    "import os\n",
    "pd.options.mode.chained_assignment = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8bbcbf16-0dee-4679-b14e-c984c18a7465",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2.0.3'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "9babf6de-16be-4732-967c-4a2e9c7977a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# file handling\n",
    "inputFile = \"whgHeizTyp100m2011lowerleft00.csv\"\n",
    "outDir = r\"./tiled_data_test/\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ec298cbe-f27b-4960-8c77-6c0a0643e557",
   "metadata": {},
   "outputs": [],
   "source": [
    "# geo settings\n",
    "resolutionGeo=100\n",
    "tileSizeCell=125"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8c9277e7-d1cd-4139-b995-51551a54dd9f",
   "metadata": {},
   "outputs": [],
   "source": [
    "griddf = pd.read_csv(inputFile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0e3686fb-9ba6-4693-b14d-c80a56d32b32",
   "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>x</th>\n",
       "      <th>y</th>\n",
       "      <th>Insg</th>\n",
       "      <th>fernw</th>\n",
       "      <th>etagen</th>\n",
       "      <th>zentral</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4335700</td>\n",
       "      <td>2686500</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4337000</td>\n",
       "      <td>2689100</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4341200</td>\n",
       "      <td>2691200</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         x        y  Insg  fernw  etagen  zentral\n",
       "0  4335700  2686500     3    NaN     NaN      3.0\n",
       "1  4337000  2689100     3    NaN     NaN      NaN\n",
       "2  4341200  2691200     3    NaN     NaN      3.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "griddf.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "740218b3-6546-46e9-9327-cabd57a78105",
   "metadata": {},
   "outputs": [],
   "source": [
    "yMin = griddf[\"y\"].min()\n",
    "yMax = griddf[\"y\"].max()\n",
    "xMax = griddf[\"x\"].max()\n",
    "xMin = griddf[\"x\"].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "97c56c9f-5bed-463b-b3a4-0f50a7f2b608",
   "metadata": {},
   "outputs": [],
   "source": [
    "xIncrements = math.ceil((xMax-xMin)/(tileSizeCell*resolutionGeo))\n",
    "yIncrements = math.ceil((yMax-yMin)/(tileSizeCell*resolutionGeo))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "2bf7f1e6-8d96-48bf-8106-9e55ee517609",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(52, 69)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xIncrements, yIncrements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1940a6d1-c31b-48b5-b0aa-d27074b6561d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare info.json\n",
    "dims = griddf.columns.tolist()[2:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "5e0cb08f-f333-49ac-a7c0-4db97f731bbc",
   "metadata": {},
   "outputs": [],
   "source": [
    "infoDict = {\n",
    "    \"dims\": [],\n",
    "    \"crs\": \"3035\",\n",
    "    \"tileSizeCell\": 128,\n",
    "    \"originPoint\": {\n",
    "        \"x\": 0,\n",
    "        \"y\": 0\n",
    "    },\n",
    "    \"resolutionGeo\": 0,\n",
    "    \"tilingBounds\": {\n",
    "        \"yMin\": 0,\n",
    "        \"xMin\": 0,\n",
    "        \"yMax\": 0,\n",
    "        \"xMax\": 0\n",
    "        }\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a9332f37-c947-4d91-892f-e6bc0db92817",
   "metadata": {},
   "outputs": [],
   "source": [
    "infoDict[\"dims\"] = dims\n",
    "infoDict[\"tileSizeCell\"] = tileSizeCell\n",
    "infoDict[\"resolutionGeo\"] = resolutionGeo\n",
    "infoDict[\"originPoint\"][\"x\"] = int(xMin)\n",
    "infoDict[\"originPoint\"][\"y\"] = int(yMin)\n",
    "infoDict[\"tilingBounds\"][\"yMax\"] = yIncrements-1\n",
    "infoDict[\"tilingBounds\"][\"xMax\"] = xIncrements-1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "112c7b12-d8b3-44ac-8956-fd50f7cb1543",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'dims': ['Insg', 'fernw', 'etagen', 'zentral'],\n",
       " 'crs': '3035',\n",
       " 'tileSizeCell': 125,\n",
       " 'originPoint': {'x': 4031700, 'y': 2686500},\n",
       " 'resolutionGeo': 100,\n",
       " 'tilingBounds': {'yMin': 0, 'xMin': 0, 'yMax': 68, 'xMax': 51}}"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "infoDict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "f8a9004c-5c14-4a4f-ab2c-48ed298894be",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create numbered folders for xIncrements\n",
    "for i in range(xIncrements):\n",
    "    \n",
    "    newpath = outDir+str(i) \n",
    "    if not os.path.exists(newpath):\n",
    "        os.makedirs(newpath)\n",
    "\n",
    "    # filter data on x-axis\n",
    "    tmpXdf = griddf[(griddf.x >= xMin + (i*tileSizeCell*resolutionGeo)) & \n",
    "                (griddf.x < (xMin + (i+1)*tileSizeCell*resolutionGeo))]\n",
    "        \n",
    "    # iterate over the y-axis\n",
    "    for j in range(yIncrements):\n",
    "        \n",
    "        # filter data to tile\n",
    "        tmpXYdf = tmpXdf[(tmpXdf.y >= yMin + (j*tileSizeCell*resolutionGeo)) & \n",
    "                    (tmpXdf.y < (yMin + (j+1)*tileSizeCell*resolutionGeo))]\n",
    "\n",
    "        # reduce grid coordinates to reference tile instead of originPoint\n",
    "        tmpXYdf[\"x\"] = tmpXYdf[\"x\"].apply(lambda n: (n - xMin\n",
    "                                                     - i*tileSizeCell*resolutionGeo)/resolutionGeo)\n",
    "        tmpXYdf[\"y\"] = tmpXYdf[\"y\"].apply(lambda n: (n - yMin\n",
    "                                                     - j*tileSizeCell*resolutionGeo)/resolutionGeo)\n",
    "\n",
    "        # write to csv if tile contains cells\n",
    "        if len(tmpXYdf)>0:\n",
    "            \n",
    "            tmpXYdf.to_csv(f'{outDir}{i}/{j}.csv', float_format='%.0f', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "dc6090fb-0f4e-4313-80cf-a3131001b33f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# write info.json in the root of the folder structure\n",
    "with open(f'{outDir}info.json', 'w') as fp:\n",
    "    json.dump(infoDict, fp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b5e101ef-9280-4901-b614-03b222894ad0",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}