mergeClean.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Replace the following file with your dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "FILENAME = \"data/lstGAF.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(FILENAME)\n",
    "df.head(5) # can't plot it because it contains the GAF scores (privacy issues)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_cols = [\"pt\", \"X\", \"Y\", \"GAF\", \"GAF_ADJ\"]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create list of sub data frames for each year and mean and median. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "years = [1998, 2008, 2018]\n",
    "select_columns = []\n",
    "for year in years:\n",
    "    for m in [\"mean\", \"median\"]:\n",
    "        tmp = []\n",
    "        for col in df.columns:\n",
    "            if str(year) in col and m in col.lower():\n",
    "                tmp.append(col)\n",
    "        select_columns.append(tmp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp_frames = []\n",
    "for col in select_columns:\n",
    "    sel_cols = final_cols + col\n",
    "    tmp_frames.append(df[sel_cols].copy())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "for frame in tmp_frames:\n",
    "    frame.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [tmp.set_index('pt') for tmp in tmp_frames]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Clean duplicated rows and cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "final = pd.concat(dfs, axis=1)\n",
    "final.drop_duplicates(inplace=True)\n",
    "final = final.loc[:,~final.columns.duplicated()].copy()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert K to °C"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "lst_cols = [col for col in final.columns if \"lst\" in col.lower()]\n",
    "final[lst_cols] -= 273.15"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate Delta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "final[\"DelaLstMedian9818\"] = final.lstMedian2018 - final.lstMedian1998\n",
    "final[\"DelaLstMean9818\"] = final.lstMean2018 - final.lstMean1998\n",
    "final[\"DelaLstMedian0818\"] = final.lstMedian2018 - final.lstMedian2008\n",
    "final[\"DelaLstMean0818\"] = final.lstMean2018 - final.lstMean2008\n",
    "\n",
    "# NDVI\n",
    "final[\"DelaNDVIMedian9818\"] = final.NDVIMedian2018 - final.NDVIMedian1998\n",
    "final[\"DelaNDVIMean9818\"] = final.NDVIMean2018 - final.NDVIMean1998\n",
    "final[\"DelaNDVIMedian0818\"] = final.NDVIMedian2018 - final.NDVIMedian2008\n",
    "final[\"DelaNDVIMean0818\"] = final.NDVIMean2018 - final.NDVIMean2008"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Save to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "final.to_csv(\"data/finalCleanMerged.csv\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For geopackage, use the following code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "geoframe = gpd.GeoDataFrame(final, geometry=gpd.points_from_xy(final.X, final.Y))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "geoframe.crs = \"EPSG:4326\"\n",
    "geoframe.to_crs(epsg=21781, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "geoframe.to_file(\"data/finalClean.gpkg\", driver=\"GPKG\")"
   ]
  }
 ],
 "metadata": {
  "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.9.16"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "a665b5d41d17b532ea9890333293a1b812fa0b73c9c25c950b3cedf1bebd0438"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}