connecting_spatial_data.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "mount_file_id": "1PUm-0EQ2DwGacqsrWYLlEmbWbEvTJFnN",
      "authorship_tag": "ABX9TyMEVS+2ic9Va89XxvU59dxx",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/jbloewencolon/Creating-Dataset-for-The-Demographics-of-Faerun/blob/main/connecting_spatial_data.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "u7YlFAfaOQl3",
        "outputId": "a2199659-db1f-452b-c5cc-4d390c33678d"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "                NAME              Name\n",
            "0             Bremen      Icewind Dale\n",
            "1         Lonelywood      Icewind Dale\n",
            "2       Caer-Dineval      Icewind Dale\n",
            "3         Termalaine      Icewind Dale\n",
            "4             Targos      Icewind Dale\n",
            "..               ...               ...\n",
            "657    Ulf of Thuger  The Purple Rocks\n",
            "658        Vilkstead  The Purple Rocks\n",
            "659          Gunbarg         Gundarlun\n",
            "729          Everska          Evereska\n",
            "730  The Halfway Inn          Evereska\n",
            "\n",
            "[811 rows x 2 columns]\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/IPython/core/interactiveshell.py:3473: FutureWarning: The `op` parameter is deprecated and will be removed in a future release. Please use the `predicate` parameter instead.\n",
            "  if (await self.run_code(code, result,  async_=asy)):\n"
          ]
        }
      ],
      "source": [
        "import pandas as pd\n",
        "import geopandas as gpd\n",
        "\n",
        "# Load the shapefiles\n",
        "cities = gpd.read_file(\"/content/drive/MyDrive/Colab Notebooks/DnD/Map Files/points.shp\")\n",
        "regions = gpd.read_file(\"/content/drive/MyDrive/Colab Notebooks/DnD/Map Files/polygons.shp\")\n",
        "\n",
        "# Ensure both are using the same CRS (Coordinate Reference System)\n",
        "cities = cities.to_crs(regions.crs)\n",
        "\n",
        "# Perform the spatial join\n",
        "joined = gpd.sjoin(cities, regions, how=\"inner\", op=\"within\")\n",
        "\n",
        "# The resulting GeoDataFrame (joined) now has information about which city belongs to which region\n",
        "print(joined[['NAME', 'Name']])\n"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Save the joined data to a CSV file\n",
        "joined.to_csv('/content/drive/MyDrive/Colab Notebooks/DnD/joined_data.csv', index=False)"
      ],
      "metadata": {
        "id": "BSoyf_ntUj3W"
      },
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# 1. Load both datasets\n",
        "fictional_data = pd.read_excel('ficional_data.xlsx')\n",
        "city_region_updates = pd.read_csv('city_region_updates.csv')\n",
        "\n",
        "# 2. Merge datasets based on the 'settlement' column\n",
        "merged = fictional_data.merge(city_region_updates[['settlement', 'region_kingdom', 'size']],\n",
        "                              on='settlement',\n",
        "                              how='left',\n",
        "                              suffixes=('', '_update'))\n",
        "\n",
        "# Identify the unmatched rows\n",
        "unmatched = city_region_updates[~city_region_updates['settlement'].isin(merged['settlement'])]\n",
        "\n",
        "# 3. Update the columns based on the merge\n",
        "merged['region_kingdom'] = merged['region_kingdom_update'].combine_first(merged['region_kingdom'])\n",
        "merged.drop('region_kingdom_update', axis=1, inplace=True)\n",
        "\n",
        "# 4. Add unmatched rows to the merged dataset\n",
        "merged = pd.concat([merged, unmatched], ignore_index=True)\n",
        "\n",
        "# 5. Notify the user about unmatched rows\n",
        "print(f\"{len(unmatched)} unmatched rows from 'city_region_updates.csv' were added to the merged dataset.\")\n",
        "\n",
        "# 6. Save the merged dataset\n",
        "merged.to_csv('updated_dataset.csv', index=False)\n"
      ],
      "metadata": {
        "id": "Wnz4E80BVPDD"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}