[Dev] Japan Flower Animal Data Preprocessing .ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4068244d",
   "metadata": {},
   "source": [
    "I do want to edit it so it would check the prefectures are labelled correctly. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8673fd5",
   "metadata": {},
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2c6aa12c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime\n",
    "\n",
    "import findspark\n",
    "findspark.init()\n",
    "\n",
    "from pyspark.sql import *\n",
    "import pyspark.sql.functions as F\n",
    "from pyspark import SparkContext"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d98ac43",
   "metadata": {},
   "source": [
    "## Data Loading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9c519545",
   "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>ID</th>\n",
       "      <th>Location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>401</td>\n",
       "      <td>Wakkanai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>406</td>\n",
       "      <td>Rumoi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>407</td>\n",
       "      <td>Asahikawa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>409</td>\n",
       "      <td>Abashiri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>412</td>\n",
       "      <td>Sapporo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID     Location\n",
       "0  401     Wakkanai\n",
       "1  406      Rumoi　　\n",
       "2  407  Asahikawa　　\n",
       "3  409     Abashiri\n",
       "4  412      Sapporo"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "location = pd.read_csv('locations.csv')\n",
    "location.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "51924382",
   "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>番号</th>\n",
       "      <th>地点名</th>\n",
       "      <th>1953</th>\n",
       "      <th>rm</th>\n",
       "      <th>1954</th>\n",
       "      <th>rm.1</th>\n",
       "      <th>1955</th>\n",
       "      <th>rm.2</th>\n",
       "      <th>1956</th>\n",
       "      <th>rm.3</th>\n",
       "      <th>...</th>\n",
       "      <th>2032</th>\n",
       "      <th>rm.79</th>\n",
       "      <th>平年値</th>\n",
       "      <th>rm.80</th>\n",
       "      <th>最早値</th>\n",
       "      <th>rm.81</th>\n",
       "      <th>最早年</th>\n",
       "      <th>最晩値</th>\n",
       "      <th>rm.82</th>\n",
       "      <th>最晩年</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>401</td>\n",
       "      <td>稚内</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>406</td>\n",
       "      <td>留萌</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>407</td>\n",
       "      <td>旭川</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>409</td>\n",
       "      <td>網走</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>412</td>\n",
       "      <td>札幌</td>\n",
       "      <td>529</td>\n",
       "      <td>8</td>\n",
       "      <td>603</td>\n",
       "      <td>8</td>\n",
       "      <td>606</td>\n",
       "      <td>8</td>\n",
       "      <td>523</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>503</td>\n",
       "      <td>8</td>\n",
       "      <td>1966</td>\n",
       "      <td>606</td>\n",
       "      <td>8</td>\n",
       "      <td>1955</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 170 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    番号   地点名  1953  rm  1954  rm.1  1955  rm.2  1956  rm.3  ...  2032  rm.79  \\\n",
       "0  401  稚内　　     0   0     0     0     0     0     0     0  ...     0      0   \n",
       "1  406  留萌　　     0   0     0     0     0     0     0     0  ...     0      0   \n",
       "2  407  旭川　　     0   0     0     0     0     0     0     0  ...     0      0   \n",
       "3  409  網走　　     0   0     0     0     0     0     0     0  ...     0      0   \n",
       "4  412  札幌　　   529   8   603     8   606     8   523     8  ...     0      0   \n",
       "\n",
       "   平年値  rm.80  最早値  rm.81   最早年  最晩値  rm.82   最晩年  \n",
       "0    0      0    0      0     0    0      0     0  \n",
       "1    0      0    0      0     0    0      0     0  \n",
       "2    0      0    0      0     0    0      0     0  \n",
       "3    0      0    0      0     0    0      0     0  \n",
       "4    0      0  503      8  1966  606      8  1955  \n",
       "\n",
       "[5 rows x 170 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_jp = pd.read_csv(\"../nodafuji_full_bloom.csv\", encoding='cp932')\n",
    "df_jp.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60302a55",
   "metadata": {},
   "source": [
    "## Organize to one chunk "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "9f623ee2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_jp['番号'].equals(location['ID'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "cc838e04",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "convert_to_datetime\n",
    "\n",
    "entry: Integer of the month-date, written in format mmdd.\n",
    "year: Integer of the year, extracted by the column name. \n",
    "\"\"\"\n",
    "def convert_to_datetime(entry, year):\n",
    "    date_str = str(int(entry)).zfill(4)\n",
    "    month = int(date_str[:2])\n",
    "    day = int(date_str[2:])\n",
    "    return datetime(int(year), month, day)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "95cef868",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_jp['Site Name'] = location['Location']\n",
    "\n",
    "# could add a sanity check to make sure the location ids are matching...?\n",
    "\n",
    "df_eng = df_jp.drop(['地点名', '番号'], axis=1)\n",
    "cols = df_eng.columns.tolist()\n",
    "cols = cols[-1:] + cols[:-1]\n",
    "df_eng = df_eng[cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "dc4bc68b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Partition the data to two tables\n",
    "df_date = df_eng[df_eng.columns[1:][::2][:-4].insert(0, 'Site Name')]\n",
    "df_rmk = df_eng[df_eng.columns[::2][:-2]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "13a597f5",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/0b/9sy9_0p51fz4vh_tvn2zmg180000gq/T/ipykernel_94589/993325827.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_date[year] = df_date[year].apply(lambda x: convert_to_datetime(x, year) if x != 0 else 0)\n"
     ]
    }
   ],
   "source": [
    "# Convert the times to datetime objects\n",
    "years = df_date.columns[1:]\n",
    "for year in years:\n",
    "    df_date[year] = df_date[year].apply(lambda x: convert_to_datetime(x, year) if x != 0 else 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "dd6a48d6",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/0b/9sy9_0p51fz4vh_tvn2zmg180000gq/T/ipykernel_94589/2585051401.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_date['Currently Being Observed'] = prev_yr_dates.apply(lambda x: True if x != 0 else False)\n"
     ]
    }
   ],
   "source": [
    "# Check if the observations are still made or not. \n",
    "prev_yr = datetime.today().year -1\n",
    "\n",
    "# Get the column for the previous year and if the value is 0, then \n",
    "prev_yr_dates = df_date[str(prev_yr)]\n",
    "\n",
    "df_date['Currently Being Observed'] = prev_yr_dates.apply(lambda x: True if x != 0 else False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "f161bcbb",
   "metadata": {},
   "outputs": [],
   "source": [
    "reorder = ['Site Name', 'Currently Being Observed'] + list(years)\n",
    "df_date = df_date[reorder]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bda8717d",
   "metadata": {},
   "source": [
    "## Create a function that will return the cleaned dataframe "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35b1cbfe",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "clean_data\n",
    "\n",
    "Cleans the csv file in the format to a data analysis ready dataframe. \n",
    "\n",
    "Currently only creates a dataframe for the observed dates. \n",
    "No table for the remarks. No sanity check for checking if locations are matched correctly. \n",
    "\n",
    "file_path: String for the file path. \n",
    "\"\"\"\n",
    "\n",
    "def clean_data(file_path):\n",
    "    df_jp = pd.read_csv(file_path, encoding='cp932') # Encoding to correctly read Japanese characters\n",
    "    df_jp['Site Name'] = location['Location']\n",
    "\n",
    "    # could add a sanity check to make sure the location ids are matching...?\n",
    "\n",
    "    df_eng = df_jp.drop(['地点名', '番号'], axis=1)\n",
    "    cols = df_eng.columns.tolist()\n",
    "    cols = cols[-1:] + cols[:-1]\n",
    "    df_eng = df_eng[cols]\n",
    "    \n",
    "    # Partition the data to two tables\n",
    "    df_date = df_eng[df_eng.columns[1:][::2][:-4].insert(0, 'Site Name')]\n",
    "    df_rmk = df_eng[df_eng.columns[::2][:-2]]\n",
    "    \n",
    "    # Convert the times to datetime objects\n",
    "    years = df_date.columns[1:]\n",
    "    for year in years:\n",
    "        df_date[year] = df_date[year].apply(lambda x: convert_to_datetime(x, year) if x != 0 else 0)\n",
    "        \n",
    "    # Check if the observations are still made or not. \n",
    "    prev_yr = datetime.today().year -1\n",
    "\n",
    "    # Get the column for the previous year and if the value is 0, then \n",
    "    prev_yr_dates = df_date[str(prev_yr)]\n",
    "\n",
    "    df_date['Currently Being Observed'] = prev_yr_dates.apply(lambda x: True if x != 0 else False)\n",
    "    reorder = ['Site Name', 'Currently Being Observed'] + list(years)\n",
    "    return df_date[reorder]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a566754",
   "metadata": {},
   "source": [
    "# Development Process"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "436d53d2",
   "metadata": {},
   "source": [
    "## Add Site Name in English"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2c753c13",
   "metadata": {},
   "outputs": [],
   "source": [
    "wisteria_jp['Site Name'] = location['Location']\n",
    "wisteria_eng = wisteria_jp.drop(['地点名', '番号'], axis=1)\n",
    "wisteria_eng = wisteria_eng.rename(columns={'平年値': 'Avg', '最早値': 'Min Date', '最早年': 'Min Year', '最晩値': 'Max Date', '最晩年': 'Max Year'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8d5649b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reorder Columns \n",
    "cols = wisteria_eng.columns.tolist() \n",
    "cols = cols[-1:] + cols[:-1]\n",
    "wisteria_eng = wisteria_eng[cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "8fa44f96",
   "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>Site Name</th>\n",
       "      <th>1953</th>\n",
       "      <th>rm</th>\n",
       "      <th>1954</th>\n",
       "      <th>rm.1</th>\n",
       "      <th>1955</th>\n",
       "      <th>rm.2</th>\n",
       "      <th>1956</th>\n",
       "      <th>rm.3</th>\n",
       "      <th>1957</th>\n",
       "      <th>...</th>\n",
       "      <th>2032</th>\n",
       "      <th>rm.79</th>\n",
       "      <th>Avg</th>\n",
       "      <th>rm.80</th>\n",
       "      <th>Min Date</th>\n",
       "      <th>rm.81</th>\n",
       "      <th>Min Year</th>\n",
       "      <th>Max Date</th>\n",
       "      <th>rm.82</th>\n",
       "      <th>Max Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Wakkanai</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rumoi</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Asahikawa</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Abashiri</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Sapporo</td>\n",
       "      <td>529</td>\n",
       "      <td>8</td>\n",
       "      <td>603</td>\n",
       "      <td>8</td>\n",
       "      <td>606</td>\n",
       "      <td>8</td>\n",
       "      <td>523</td>\n",
       "      <td>8</td>\n",
       "      <td>524</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>503</td>\n",
       "      <td>8</td>\n",
       "      <td>1966</td>\n",
       "      <td>606</td>\n",
       "      <td>8</td>\n",
       "      <td>1955</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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>97</th>\n",
       "      <td>Miyako-jima</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Kume-jima</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Naha</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>Nago</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>Minamidaito-jima</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>102 rows × 169 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Site Name  1953  rm  1954  rm.1  1955  rm.2  1956  rm.3  1957  \\\n",
       "0            Wakkanai     0   0     0     0     0     0     0     0     0   \n",
       "1             Rumoi　　     0   0     0     0     0     0     0     0     0   \n",
       "2         Asahikawa　　     0   0     0     0     0     0     0     0     0   \n",
       "3            Abashiri     0   0     0     0     0     0     0     0     0   \n",
       "4             Sapporo   529   8   603     8   606     8   523     8   524   \n",
       "..                ...   ...  ..   ...   ...   ...   ...   ...   ...   ...   \n",
       "97        Miyako-jima     0   0     0     0     0     0     0     0     0   \n",
       "98          Kume-jima     0   0     0     0     0     0     0     0     0   \n",
       "99               Naha     0   0     0     0     0     0     0     0     0   \n",
       "100              Nago     0   0     0     0     0     0     0     0     0   \n",
       "101  Minamidaito-jima     0   0     0     0     0     0     0     0     0   \n",
       "\n",
       "     ...  2032  rm.79  Avg  rm.80  Min Date  rm.81  Min Year  Max Date  rm.82  \\\n",
       "0    ...     0      0    0      0         0      0         0         0      0   \n",
       "1    ...     0      0    0      0         0      0         0         0      0   \n",
       "2    ...     0      0    0      0         0      0         0         0      0   \n",
       "3    ...     0      0    0      0         0      0         0         0      0   \n",
       "4    ...     0      0    0      0       503      8      1966       606      8   \n",
       "..   ...   ...    ...  ...    ...       ...    ...       ...       ...    ...   \n",
       "97   ...     0      0    0      0         0      0         0         0      0   \n",
       "98   ...     0      0    0      0         0      0         0         0      0   \n",
       "99   ...     0      0    0      0         0      0         0         0      0   \n",
       "100  ...     0      0    0      0         0      0         0         0      0   \n",
       "101  ...     0      0    0      0         0      0         0         0      0   \n",
       "\n",
       "     Max Year  \n",
       "0           0  \n",
       "1           0  \n",
       "2           0  \n",
       "3           0  \n",
       "4        1955  \n",
       "..        ...  \n",
       "97          0  \n",
       "98          0  \n",
       "99          0  \n",
       "100         0  \n",
       "101         0  \n",
       "\n",
       "[102 rows x 169 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wisteria_eng"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb26c46a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#wisteria_eng.replace(0, np.nan, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ae3f356",
   "metadata": {},
   "outputs": [],
   "source": [
    "wisteria_eng"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bed7a1c6",
   "metadata": {},
   "source": [
    "Since we know the last columns, we can replace the rm.# by more informative names coutning from there. To make the code more general. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b255b4ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Partition the data to two tables\n",
    "wisteria_date = wisteria_eng[wisteria_eng.columns[1:][::2][:-4].insert(0, 'Site Name')]\n",
    "wisteria_rmk = wisteria_eng[wisteria_eng.columns[::2][:-2]]\n",
    "# For the remark, need to remove the columns that are associate with the stats "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "f73cc517",
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_to_datetime(entry, year):\n",
    "    date_str = str(int(entry)).zfill(4)\n",
    "    month = int(date_str[:2])\n",
    "    day = int(date_str[2:])\n",
    "    return datetime(int(year), month, day)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "cb5f20d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "years = wisteria_date.columns[1:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "3bde051a",
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "int() argument must be a string, a bytes-like object or a number, not 'datetime.datetime'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[57], line 2\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[38;5;28;01mfor\u001b[39;00m year \u001b[38;5;129;01min\u001b[39;00m years:\n\u001b[0;32m----> 2\u001b[0m     wisteria_date[year] \u001b[38;5;241m=\u001b[39m \u001b[43mwisteria_date\u001b[49m\u001b[43m[\u001b[49m\u001b[43myear\u001b[49m\u001b[43m]\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mapply\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;28;43;01mlambda\u001b[39;49;00m\u001b[43m \u001b[49m\u001b[43mx\u001b[49m\u001b[43m:\u001b[49m\u001b[43m \u001b[49m\u001b[43mconvert_to_datetime\u001b[49m\u001b[43m(\u001b[49m\u001b[43mx\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43myear\u001b[49m\u001b[43m)\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43;01mif\u001b[39;49;00m\u001b[43m \u001b[49m\u001b[43mx\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m!=\u001b[39;49m\u001b[43m \u001b[49m\u001b[38;5;241;43m0\u001b[39;49m\u001b[43m \u001b[49m\u001b[38;5;28;43;01melse\u001b[39;49;00m\u001b[43m \u001b[49m\u001b[38;5;241;43m0\u001b[39;49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pandas/core/series.py:4200\u001b[0m, in \u001b[0;36mSeries.apply\u001b[0;34m(self, func, convert_dtype, args, **kwds)\u001b[0m\n\u001b[1;32m   4198\u001b[0m     \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[1;32m   4199\u001b[0m         values \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mastype(\u001b[38;5;28mobject\u001b[39m)\u001b[38;5;241m.\u001b[39m_values\n\u001b[0;32m-> 4200\u001b[0m         mapped \u001b[38;5;241m=\u001b[39m \u001b[43mlib\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mmap_infer\u001b[49m\u001b[43m(\u001b[49m\u001b[43mvalues\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mf\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mconvert\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mconvert_dtype\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   4202\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mlen\u001b[39m(mapped) \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(mapped[\u001b[38;5;241m0\u001b[39m], Series):\n\u001b[1;32m   4203\u001b[0m     \u001b[38;5;66;03m# GH 25959 use pd.array instead of tolist\u001b[39;00m\n\u001b[1;32m   4204\u001b[0m     \u001b[38;5;66;03m# so extension arrays can be used\u001b[39;00m\n\u001b[1;32m   4205\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_constructor_expanddim(pd\u001b[38;5;241m.\u001b[39marray(mapped), index\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mindex)\n",
      "File \u001b[0;32mpandas/_libs/lib.pyx:2402\u001b[0m, in \u001b[0;36mpandas._libs.lib.map_infer\u001b[0;34m()\u001b[0m\n",
      "Cell \u001b[0;32mIn[57], line 2\u001b[0m, in \u001b[0;36m<lambda>\u001b[0;34m(x)\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[38;5;28;01mfor\u001b[39;00m year \u001b[38;5;129;01min\u001b[39;00m years:\n\u001b[0;32m----> 2\u001b[0m     wisteria_date[year] \u001b[38;5;241m=\u001b[39m wisteria_date[year]\u001b[38;5;241m.\u001b[39mapply(\u001b[38;5;28;01mlambda\u001b[39;00m x: \u001b[43mconvert_to_datetime\u001b[49m\u001b[43m(\u001b[49m\u001b[43mx\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43myear\u001b[49m\u001b[43m)\u001b[49m \u001b[38;5;28;01mif\u001b[39;00m x \u001b[38;5;241m!=\u001b[39m \u001b[38;5;241m0\u001b[39m \u001b[38;5;28;01melse\u001b[39;00m \u001b[38;5;241m0\u001b[39m)\n",
      "Cell \u001b[0;32mIn[55], line 2\u001b[0m, in \u001b[0;36mconvert_to_datetime\u001b[0;34m(entry, year)\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mconvert_to_datetime\u001b[39m(entry, year):\n\u001b[0;32m----> 2\u001b[0m     date_str \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mstr\u001b[39m(\u001b[38;5;28;43mint\u001b[39;49m\u001b[43m(\u001b[49m\u001b[43mentry\u001b[49m\u001b[43m)\u001b[49m)\u001b[38;5;241m.\u001b[39mzfill(\u001b[38;5;241m4\u001b[39m)\n\u001b[1;32m      3\u001b[0m     month \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mint\u001b[39m(date_str[:\u001b[38;5;241m2\u001b[39m])\n\u001b[1;32m      4\u001b[0m     day \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mint\u001b[39m(date_str[\u001b[38;5;241m2\u001b[39m:])\n",
      "\u001b[0;31mTypeError\u001b[0m: int() argument must be a string, a bytes-like object or a number, not 'datetime.datetime'"
     ]
    }
   ],
   "source": [
    "for year in years:\n",
    "    wisteria_date[year] = wisteria_date[year].apply(lambda x: convert_to_datetime(x, year) if x != 0 else 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "388bcadd",
   "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>Site Name</th>\n",
       "      <th>1953</th>\n",
       "      <th>1954</th>\n",
       "      <th>1955</th>\n",
       "      <th>1956</th>\n",
       "      <th>1957</th>\n",
       "      <th>1958</th>\n",
       "      <th>1959</th>\n",
       "      <th>1960</th>\n",
       "      <th>1961</th>\n",
       "      <th>...</th>\n",
       "      <th>2024</th>\n",
       "      <th>2025</th>\n",
       "      <th>2026</th>\n",
       "      <th>2027</th>\n",
       "      <th>2028</th>\n",
       "      <th>2029</th>\n",
       "      <th>2030</th>\n",
       "      <th>2031</th>\n",
       "      <th>2032</th>\n",
       "      <th>Currently Being Observed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Wakkanai</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rumoi</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Asahikawa</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Abashiri</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Sapporo</td>\n",
       "      <td>1953-05-29 00:00:00</td>\n",
       "      <td>1954-06-03 00:00:00</td>\n",
       "      <td>1955-06-06 00:00:00</td>\n",
       "      <td>1956-05-23 00:00:00</td>\n",
       "      <td>1957-05-24 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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>97</th>\n",
       "      <td>Miyako-jima</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Kume-jima</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Naha</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>Nago</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>Minamidaito-jima</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>102 rows × 82 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Site Name                 1953                 1954  \\\n",
       "0            Wakkanai                    0                    0   \n",
       "1             Rumoi　　                    0                    0   \n",
       "2         Asahikawa　　                    0                    0   \n",
       "3            Abashiri                    0                    0   \n",
       "4             Sapporo  1953-05-29 00:00:00  1954-06-03 00:00:00   \n",
       "..                ...                  ...                  ...   \n",
       "97        Miyako-jima                    0                    0   \n",
       "98          Kume-jima                    0                    0   \n",
       "99               Naha                    0                    0   \n",
       "100              Nago                    0                    0   \n",
       "101  Minamidaito-jima                    0                    0   \n",
       "\n",
       "                    1955                 1956                 1957 1958 1959  \\\n",
       "0                      0                    0                    0    0    0   \n",
       "1                      0                    0                    0    0    0   \n",
       "2                      0                    0                    0    0    0   \n",
       "3                      0                    0                    0    0    0   \n",
       "4    1955-06-06 00:00:00  1956-05-23 00:00:00  1957-05-24 00:00:00    0    0   \n",
       "..                   ...                  ...                  ...  ...  ...   \n",
       "97                     0                    0                    0    0    0   \n",
       "98                     0                    0                    0    0    0   \n",
       "99                     0                    0                    0    0    0   \n",
       "100                    0                    0                    0    0    0   \n",
       "101                    0                    0                    0    0    0   \n",
       "\n",
       "    1960 1961  ... 2024 2025 2026 2027 2028 2029 2030 2031 2032  \\\n",
       "0      0    0  ...    0    0    0    0    0    0    0    0    0   \n",
       "1      0    0  ...    0    0    0    0    0    0    0    0    0   \n",
       "2      0    0  ...    0    0    0    0    0    0    0    0    0   \n",
       "3      0    0  ...    0    0    0    0    0    0    0    0    0   \n",
       "4      0    0  ...    0    0    0    0    0    0    0    0    0   \n",
       "..   ...  ...  ...  ...  ...  ...  ...  ...  ...  ...  ...  ...   \n",
       "97     0    0  ...    0    0    0    0    0    0    0    0    0   \n",
       "98     0    0  ...    0    0    0    0    0    0    0    0    0   \n",
       "99     0    0  ...    0    0    0    0    0    0    0    0    0   \n",
       "100    0    0  ...    0    0    0    0    0    0    0    0    0   \n",
       "101    0    0  ...    0    0    0    0    0    0    0    0    0   \n",
       "\n",
       "    Currently Being Observed  \n",
       "0                      False  \n",
       "1                      False  \n",
       "2                      False  \n",
       "3                      False  \n",
       "4                      False  \n",
       "..                       ...  \n",
       "97                     False  \n",
       "98                     False  \n",
       "99                     False  \n",
       "100                    False  \n",
       "101                    False  \n",
       "\n",
       "[102 rows x 82 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wisteria_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "47497090",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/0b/9sy9_0p51fz4vh_tvn2zmg180000gq/T/ipykernel_36800/116196685.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  wisteria_date['Currently Being Observed'] = prev_yr_dates.apply(lambda x: True if x != 0 else False)\n"
     ]
    }
   ],
   "source": [
    "# Check if the observations are still made or not. \n",
    "prev_yr = datetime.today().year -1\n",
    "\n",
    "# Get the column for the previous year and if the value is 0, then \n",
    "prev_yr_dates = wisteria_date[str(prev_yr)]\n",
    "\n",
    "wisteria_date['Currently Being Observed'] = prev_yr_dates.apply(lambda x: True if x != 0 else False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "2249c5fa",
   "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>Site Name</th>\n",
       "      <th>Currently Being Observed</th>\n",
       "      <th>1953</th>\n",
       "      <th>1954</th>\n",
       "      <th>1955</th>\n",
       "      <th>1956</th>\n",
       "      <th>1957</th>\n",
       "      <th>1958</th>\n",
       "      <th>1959</th>\n",
       "      <th>1960</th>\n",
       "      <th>...</th>\n",
       "      <th>2024</th>\n",
       "      <th>2025</th>\n",
       "      <th>2026</th>\n",
       "      <th>2027</th>\n",
       "      <th>2028</th>\n",
       "      <th>2029</th>\n",
       "      <th>2030</th>\n",
       "      <th>2031</th>\n",
       "      <th>2032</th>\n",
       "      <th>Currently Being Observed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Wakkanai</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rumoi</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Asahikawa</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Abashiri</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Sapporo</td>\n",
       "      <td>False</td>\n",
       "      <td>1953-05-29 00:00:00</td>\n",
       "      <td>1954-06-03 00:00:00</td>\n",
       "      <td>1955-06-06 00:00:00</td>\n",
       "      <td>1956-05-23 00:00:00</td>\n",
       "      <td>1957-05-24 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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>97</th>\n",
       "      <td>Miyako-jima</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Kume-jima</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Naha</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>Nago</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>Minamidaito-jima</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>102 rows × 83 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Site Name Currently Being Observed                 1953  \\\n",
       "0            Wakkanai                    False                    0   \n",
       "1             Rumoi　　                    False                    0   \n",
       "2         Asahikawa　　                    False                    0   \n",
       "3            Abashiri                    False                    0   \n",
       "4             Sapporo                    False  1953-05-29 00:00:00   \n",
       "..                ...                      ...                  ...   \n",
       "97        Miyako-jima                    False                    0   \n",
       "98          Kume-jima                    False                    0   \n",
       "99               Naha                    False                    0   \n",
       "100              Nago                    False                    0   \n",
       "101  Minamidaito-jima                    False                    0   \n",
       "\n",
       "                    1954                 1955                 1956  \\\n",
       "0                      0                    0                    0   \n",
       "1                      0                    0                    0   \n",
       "2                      0                    0                    0   \n",
       "3                      0                    0                    0   \n",
       "4    1954-06-03 00:00:00  1955-06-06 00:00:00  1956-05-23 00:00:00   \n",
       "..                   ...                  ...                  ...   \n",
       "97                     0                    0                    0   \n",
       "98                     0                    0                    0   \n",
       "99                     0                    0                    0   \n",
       "100                    0                    0                    0   \n",
       "101                    0                    0                    0   \n",
       "\n",
       "                    1957 1958 1959 1960  ... 2024 2025 2026 2027 2028 2029  \\\n",
       "0                      0    0    0    0  ...    0    0    0    0    0    0   \n",
       "1                      0    0    0    0  ...    0    0    0    0    0    0   \n",
       "2                      0    0    0    0  ...    0    0    0    0    0    0   \n",
       "3                      0    0    0    0  ...    0    0    0    0    0    0   \n",
       "4    1957-05-24 00:00:00    0    0    0  ...    0    0    0    0    0    0   \n",
       "..                   ...  ...  ...  ...  ...  ...  ...  ...  ...  ...  ...   \n",
       "97                     0    0    0    0  ...    0    0    0    0    0    0   \n",
       "98                     0    0    0    0  ...    0    0    0    0    0    0   \n",
       "99                     0    0    0    0  ...    0    0    0    0    0    0   \n",
       "100                    0    0    0    0  ...    0    0    0    0    0    0   \n",
       "101                    0    0    0    0  ...    0    0    0    0    0    0   \n",
       "\n",
       "    2030 2031 2032 Currently Being Observed  \n",
       "0      0    0    0                    False  \n",
       "1      0    0    0                    False  \n",
       "2      0    0    0                    False  \n",
       "3      0    0    0                    False  \n",
       "4      0    0    0                    False  \n",
       "..   ...  ...  ...                      ...  \n",
       "97     0    0    0                    False  \n",
       "98     0    0    0                    False  \n",
       "99     0    0    0                    False  \n",
       "100    0    0    0                    False  \n",
       "101    0    0    0                    False  \n",
       "\n",
       "[102 rows x 83 columns]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reorder = ['Site Name', 'Currently Being Observed'] + list(years)\n",
    "wisteria_date[reorder]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "6f4b90d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write this to csv. \n",
    "# Finished building basic code for converting the data, now want to make this more general.\n",
    "\n",
    "# Then use the data for EDA and building models. \n",
    "# the Cherry blosom data has the type of the cherry blossom. Does this slightly differ between tables? "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89e8e15d",
   "metadata": {},
   "source": [
    "## Todo \n",
    "\n",
    "- Reassign IDs\n",
    "- Currently Being Observed (<- influence from other person)\n",
    "    - if the column for the previous year (from today) is filled, still observed. Otherwise, not observed. \n",
    "- Remove the columns with the stats\n",
    "- Create two tables: One for dates and one for the remarks. \n",
    "\n",
    "\n",
    "## Steps\n",
    "1. Convert the cities to English using the locations.csv\n",
    "2. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2efee8b",
   "metadata": {},
   "source": [
    "## Write a fn that takes the file and creates a clean data\n",
    "\n",
    "Ref for data format: https://www.kaggle.com/datasets/ryanglasnapp/japanese-cherry-blossom-data/data\n",
    "\n",
    "Cols: Site Name, Currently Being Observed (For future data parsing check current year and check previous year values), [Col for years], Avg, Median, Max, Min\n",
    "\n",
    "If value is 0, set it to none. \n",
    "For non-zero entries, get the corresponding column years. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25cce011",
   "metadata": {},
   "outputs": [],
   "source": [
    "def "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "491b63bb",
   "metadata": {},
   "source": [
    "## Create csv file for location\n",
    "\n",
    "Should I create a separate file for each location? -> no"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ec2ebeb",
   "metadata": {},
   "source": [
    "I want a parser for all of the data. I should be all in the same format, so if I can write code for that it'll be great. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30c36ab9",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Have the column names from the data frame. \n",
    "It's not too big, can probabably parse just using a for loop\n",
    "\n",
    "\"\"\"\n",
    "def parser():\n",
    "    "
   ]
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}