create-linestring-to-nearest.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "from shapely import LineString"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.width', 500)\n",
    "pd.set_option('display.max_rows', 50)\n",
    "pd.set_option('display.max_columns', 500)\n",
    "pd.set_option('display.max_colwidth', 500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "point_df = pd.read_excel('sample-folder/trial-calculate-line-distance.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>SiteID_Source_Alternative</th>\n",
       "      <th>Longitude_Source</th>\n",
       "      <th>Latitude_Source</th>\n",
       "      <th>SiteID_Target_Alternative</th>\n",
       "      <th>Longitude_Target</th>\n",
       "      <th>Latitude_Target</th>\n",
       "      <th>distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SiteID-1</td>\n",
       "      <td>106.041358</td>\n",
       "      <td>-6.003581</td>\n",
       "      <td>SiteID-3698</td>\n",
       "      <td>106.043200</td>\n",
       "      <td>-6.006975</td>\n",
       "      <td>431.734531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SiteID-2</td>\n",
       "      <td>106.042389</td>\n",
       "      <td>-6.009056</td>\n",
       "      <td>SiteID-3698</td>\n",
       "      <td>106.043200</td>\n",
       "      <td>-6.006975</td>\n",
       "      <td>249.776735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SiteID-3</td>\n",
       "      <td>106.069482</td>\n",
       "      <td>-6.025184</td>\n",
       "      <td>SiteID-15302</td>\n",
       "      <td>106.071811</td>\n",
       "      <td>-6.026569</td>\n",
       "      <td>302.117152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SiteID-4</td>\n",
       "      <td>106.044056</td>\n",
       "      <td>-6.015861</td>\n",
       "      <td>SiteID-4526</td>\n",
       "      <td>106.046111</td>\n",
       "      <td>-6.013611</td>\n",
       "      <td>340.277799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SiteID-5</td>\n",
       "      <td>106.049439</td>\n",
       "      <td>-6.013789</td>\n",
       "      <td>SiteID-4526</td>\n",
       "      <td>106.046111</td>\n",
       "      <td>-6.013611</td>\n",
       "      <td>370.980627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15299</th>\n",
       "      <td>SiteID-15300</td>\n",
       "      <td>106.065819</td>\n",
       "      <td>-6.046281</td>\n",
       "      <td>SiteID-15299</td>\n",
       "      <td>106.069061</td>\n",
       "      <td>-6.043261</td>\n",
       "      <td>494.435046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15300</th>\n",
       "      <td>SiteID-15301</td>\n",
       "      <td>106.075556</td>\n",
       "      <td>-6.037694</td>\n",
       "      <td>SiteID-15297</td>\n",
       "      <td>106.074100</td>\n",
       "      <td>-6.040900</td>\n",
       "      <td>393.719346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15301</th>\n",
       "      <td>SiteID-15302</td>\n",
       "      <td>106.071811</td>\n",
       "      <td>-6.026569</td>\n",
       "      <td>SiteID-3</td>\n",
       "      <td>106.069482</td>\n",
       "      <td>-6.025184</td>\n",
       "      <td>302.117152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15302</th>\n",
       "      <td>SiteID-15303</td>\n",
       "      <td>105.883292</td>\n",
       "      <td>-6.107036</td>\n",
       "      <td>SiteID-15304</td>\n",
       "      <td>105.881914</td>\n",
       "      <td>-6.105358</td>\n",
       "      <td>242.498344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15303</th>\n",
       "      <td>SiteID-15304</td>\n",
       "      <td>105.881914</td>\n",
       "      <td>-6.105358</td>\n",
       "      <td>SiteID-15303</td>\n",
       "      <td>105.883292</td>\n",
       "      <td>-6.107036</td>\n",
       "      <td>242.498344</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15304 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      SiteID_Source_Alternative  Longitude_Source  Latitude_Source SiteID_Target_Alternative  Longitude_Target  Latitude_Target    distance\n",
       "0                      SiteID-1        106.041358        -6.003581               SiteID-3698        106.043200        -6.006975  431.734531\n",
       "1                      SiteID-2        106.042389        -6.009056               SiteID-3698        106.043200        -6.006975  249.776735\n",
       "2                      SiteID-3        106.069482        -6.025184              SiteID-15302        106.071811        -6.026569  302.117152\n",
       "3                      SiteID-4        106.044056        -6.015861               SiteID-4526        106.046111        -6.013611  340.277799\n",
       "4                      SiteID-5        106.049439        -6.013789               SiteID-4526        106.046111        -6.013611  370.980627\n",
       "...                         ...               ...              ...                       ...               ...              ...         ...\n",
       "15299              SiteID-15300        106.065819        -6.046281              SiteID-15299        106.069061        -6.043261  494.435046\n",
       "15300              SiteID-15301        106.075556        -6.037694              SiteID-15297        106.074100        -6.040900  393.719346\n",
       "15301              SiteID-15302        106.071811        -6.026569                  SiteID-3        106.069482        -6.025184  302.117152\n",
       "15302              SiteID-15303        105.883292        -6.107036              SiteID-15304        105.881914        -6.105358  242.498344\n",
       "15303              SiteID-15304        105.881914        -6.105358              SiteID-15303        105.883292        -6.107036  242.498344\n",
       "\n",
       "[15304 rows x 7 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "point_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "point_df['linestring'] = \"LINESTRING (\" + point_df['Longitude_Source'].astype(str) + \" \" + point_df['Latitude_Source'].astype(str) + \", \" + point_df['Longitude_Target'].astype(str) + \" \" + point_df['Latitude_Target'].astype(str) + \")\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "point_df.to_csv('result/ISD-with_linestring.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".python-3.10",
   "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.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}