3_Merging_Outputs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merging GRASS outputs and then cep attributes to the merged output\n",
    "execution time ~= 1 min"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "output_directory = os.environ['GRASS_OUTPUT_DIRECTORY']\n",
    "cep_attributes_path = os.environ['CEP_ATTRIBUTES_CSV_PATH']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "class MergingDataframes():\n",
    "    \"\"\"\n",
    "    Contains functions Merge the outputs of the GRASS script into a single dataframe with the CEP attributes\n",
    "    \"\"\"\n",
    "    def __init__(self, output_directory,cep_attributes_path):\n",
    "        self.df = None\n",
    "        self.output_directory = output_directory\n",
    "        self.cep_attributes_path = cep_attributes_path\n",
    "    \n",
    "\n",
    "    def aggregate_grass_outputs(self):\n",
    "        \"\"\"\n",
    "        Aggregate the outputs of the GRASS script into a single dataframe with the CEP attributes\n",
    "        \"\"\"\n",
    "        dataframes = []\n",
    "        # step through all csv files in the output directory\n",
    "        for csv_file in os.listdir(self.output_directory):\n",
    "            if csv_file.endswith('.csv'):\n",
    "                # if csv file empty, skip\n",
    "                if os.path.getsize(os.path.join(output_directory, csv_file)) == 0:\n",
    "                    continue\n",
    "                columns = ['transition_band','cep_id', 'area']\n",
    "                df = pd.read_csv(os.path.join(output_directory, csv_file), names=columns)\n",
    "                # for each csv append to list of dataframes\n",
    "                dataframes.append(df)\n",
    "\n",
    "        # concatenate all dataframes into multi-indexed dataframe (cep_id, transition_band)\n",
    "        df = pd.concat(dataframes)\n",
    "        # group by cep_id and transition_band and sum the areas (to create a multi-indexed dataframe with sum of areas)\n",
    "        df = df.groupby(['cep_id', 'transition_band']).sum()\n",
    "        #transpose the dataframe so that the transition bands are columns and fill NaN with 0 and set column names to transition_i\n",
    "        df = df.unstack().fillna(0)\n",
    "        #fix column names to remove multi-index\n",
    "        df.columns = df.columns.droplevel()\n",
    "        df.columns = [f'transition_{i}' for i in range(11)]\n",
    "        # change the index to be the cep_id\n",
    "        df.index = df.index.astype(int)\n",
    "        self.df = df\n",
    "\n",
    "    def merge_cep_attributes(self, cep_df, to_csv=False):\n",
    "        \"\"\"\n",
    "        Merge the aggregated outputs of the GRASS script with the CEP attributes\n",
    "        :param cep_df: dataframe containing the CEP attributes\n",
    "        :param to_csv: if True, save the final dataframe to a csv file\n",
    "        \"\"\"\n",
    "        # join the two dataframes on cep_id and cid to get the attributes\n",
    "        df = self.df.join(cep_df, on='cep_id')\n",
    "        # convert dtypes of id fields back to int (keep dtype from original csv)\n",
    "        df['pa'] = pd.to_numeric(df['pa'], errors='coerce').astype('Int64')\n",
    "        df['eco'] = pd.to_numeric(df['eco'], errors='coerce').astype('Int64')\n",
    "        df['country'] = pd.to_numeric(df['country'], errors='coerce').astype('Int64')\n",
    "        self.df = df\n",
    "\n",
    "        if to_csv:\n",
    "            self.df.to_csv('final_output.csv')\n",
    "        \n",
    "\n",
    "    # def find_tile_with_id(self, cep_id, output_directory):\n",
    "    #     # step through all csv files in the output directory\n",
    "    #     for csv_file in os.listdir(output_directory):\n",
    "    #         if csv_file.endswith('.csv'):\n",
    "    #             # if csv file empty, skip\n",
    "    #             if os.path.getsize(os.path.join(output_directory, csv_file)) == 0:\n",
    "    #                 continue\n",
    "    #             columns = ['transition_band','cep_id', 'area']\n",
    "    #             df = pd.read_csv(os.path.join(output_directory, csv_file), names=columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>country</th>\n",
       "      <th>country_name</th>\n",
       "      <th>iso3</th>\n",
       "      <th>eco</th>\n",
       "      <th>eco_name</th>\n",
       "      <th>is_marine</th>\n",
       "      <th>pa</th>\n",
       "      <th>pa_name</th>\n",
       "      <th>is_protected</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>80412</td>\n",
       "      <td>Central European mixed forests</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Area Beyond National Jurisdiction</td>\n",
       "      <td>ABNJ</td>\n",
       "      <td>1</td>\n",
       "      <td>Antarctic</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Area Beyond National Jurisdiction</td>\n",
       "      <td>ABNJ</td>\n",
       "      <td>1</td>\n",
       "      <td>Antarctic</td>\n",
       "      <td>True</td>\n",
       "      <td>478191</td>\n",
       "      <td>South Orkney Islands Southern Shelf Marine Pro...</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Area Beyond National Jurisdiction</td>\n",
       "      <td>ABNJ</td>\n",
       "      <td>1</td>\n",
       "      <td>Antarctic</td>\n",
       "      <td>True</td>\n",
       "      <td>555547601</td>\n",
       "      <td>South Georgia and South Sandwich Islands Marin...</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>Area Beyond National Jurisdiction</td>\n",
       "      <td>ABNJ</td>\n",
       "      <td>1</td>\n",
       "      <td>Antarctic</td>\n",
       "      <td>True</td>\n",
       "      <td>555624810</td>\n",
       "      <td>Ross Sea Region Marine Protected Area</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     country                       country_name  iso3    eco  \\\n",
       "cid                                                            \n",
       "1          0                                NaN   NaN  80412   \n",
       "2          1  Area Beyond National Jurisdiction  ABNJ      1   \n",
       "3          1  Area Beyond National Jurisdiction  ABNJ      1   \n",
       "4          1  Area Beyond National Jurisdiction  ABNJ      1   \n",
       "5          1  Area Beyond National Jurisdiction  ABNJ      1   \n",
       "\n",
       "                           eco_name  is_marine         pa  \\\n",
       "cid                                                         \n",
       "1    Central European mixed forests      False          0   \n",
       "2                         Antarctic       True          0   \n",
       "3                         Antarctic       True     478191   \n",
       "4                         Antarctic       True  555547601   \n",
       "5                         Antarctic       True  555624810   \n",
       "\n",
       "                                               pa_name  is_protected  \n",
       "cid                                                                   \n",
       "1                                                  NaN         False  \n",
       "2                                                  NaN         False  \n",
       "3    South Orkney Islands Southern Shelf Marine Pro...          True  \n",
       "4    South Georgia and South Sandwich Islands Marin...          True  \n",
       "5                Ross Sea Region Marine Protected Area          True  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cep_df = pd.read_csv(cep_attributes_path, index_col='cid')\n",
    "cep_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>country</th>\n",
       "      <th>country_name</th>\n",
       "      <th>iso3</th>\n",
       "      <th>eco</th>\n",
       "      <th>eco_name</th>\n",
       "      <th>is_marine</th>\n",
       "      <th>pa</th>\n",
       "      <th>pa_name</th>\n",
       "      <th>is_protected</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>80412</td>\n",
       "      <td>Central European mixed forests</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     country country_name iso3    eco                        eco_name  \\\n",
       "cid                                                                     \n",
       "1          0          NaN  NaN  80412  Central European mixed forests   \n",
       "\n",
       "     is_marine  pa pa_name  is_protected  \n",
       "cid                                       \n",
       "1        False   0     NaN         False  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get rows where NaN or null country values\n",
    "cep_df[cep_df['country_name'].isnull()] # {cid: 1}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{295147}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# in cep_df check if there are any missing ids from 1 to 463710 ids (inclusive)\n",
    "missing_ids = set(range(1, 463711)) - set(cep_df.index)\n",
    "missing_ids # {cid: 295147}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#update cid 1 with country (code): 171, country name: Lithuania, iso3: LTU\n",
    "cep_df.loc[1, 'country'] = 171\n",
    "cep_df.loc[1, 'country_name'] = 'Lithuania'\n",
    "cep_df.loc[1, 'iso3'] = 'LTU'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# maunally insert missing record for id 295147 (copy from 339212 with different id)\n",
    "cep_df.loc[295147] = cep_df.loc[339212].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# aggregate GRASS outputs into df\n",
    "merger = MergingDataframes(output_directory,cep_attributes_path)\n",
    "merger.aggregate_grass_outputs()\n",
    "#merge cep attributes with GRASS outputs\n",
    "merger.merge_cep_attributes(cep_df, to_csv=True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "env",
   "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.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}