process.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import settings \n",
    "Link = settings.EXTRACTED_DIR # path to extracted files\n",
    "Link2 = settings.PROCESSED_DIR # path to save modified files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DATASET-1\n",
    "dataset1 = pd.read_csv(Link + \"\\\\\" + 'Demand_2014_2020.csv')\n",
    "dataset1.head(5)\n",
    "dataset1_0 = dataset1[[\"REGION\", \"SETTLEMENTDATE\", \"TOTALDEMAND\"]]\n",
    "\n",
    "#Change columnnames \n",
    "dataset1_1 = dataset1_0.rename(columns={'REGION':'STATE', 'SETTLEMENTDATE':'DATE_OBJECT', 'TOTALDEMAND':'DEMAND'})\n",
    "\n",
    "#Change Statenames\n",
    "dataset1_1[\"STATE\"].value_counts() # Check State names\n",
    "dataset1_1[\"STATE\"].replace([\"VIC1\", \"TAS1\", \"NSW1\",\"SA1\", \"QLD1\"], [\"VIC\", \"TAS\", \"NSW\", \"SA\", \"QLD\"], inplace= True)\n",
    "dataset1_1[\"STATE\"].value_counts() # Check if state names are modified correctly\n",
    "\n",
    "#Get Date objects - Time, day, date, year\n",
    "dataset1_1[\"DATE_OBJECT\"].value_counts()\n",
    "dataset1_1['DATE'] = pd.to_datetime(dataset1_1[\"DATE_OBJECT\"]).dt.date\n",
    "dataset1_1['YEAR'] = pd.to_datetime(dataset1_1['DATE']).dt.year\n",
    "dataset1_1['DAY'] = pd.to_datetime(dataset1_1['DATE_OBJECT']).dt.day\n",
    "dataset1_1['MONTH'] = pd.to_datetime(dataset1_1['DATE']).dt.month\n",
    "dataset1_1['HOUR'] = pd.to_datetime(dataset1_1['DATE_OBJECT']).dt.hour\n",
    "dataset1_1 ['MINUTES']= pd.to_datetime(dataset1_1['DATE_OBJECT']).dt.minute\n",
    "\n",
    "dataset1_1.head(5) # Chcek if all columns are set properly\n",
    "\n",
    "# Treating missing values of Demand\n",
    "# No missing values are there for Demand variable in dataset\n",
    "\n",
    "# Dataset at half  hour level\n",
    "dataset1_1.drop(['DATE_OBJECT'], axis='columns', inplace = True)\n",
    "file_name = \"Demand_halfhourly.csv\"\n",
    "dataset1_1.to_csv(Link2 + \"\\\\\" + file_name, index=False)\n",
    "\n",
    "# Dataset at hour level\n",
    "dataset1_1.drop(['MINUTES'], axis='columns', inplace = True)\n",
    "df1 = dataset1_1.groupby(by=[\"HOUR\",\"DAY\",\"DATE\",\"MONTH\",\"YEAR\",\"STATE\"], as_index=False)[\"DEMAND\"].sum()\n",
    "file_name = \"Demand_hourly.csv\"\n",
    "df1.to_csv(Link2 + \"\\\\\" + file_name, index=False)\n",
    "\n",
    "# Aggregate data for a day, as we have temperature data at day level \n",
    "#( We can extract data for temperature at hour level, but to keep analysis simple and data size easy to work on, \n",
    "# analysis will be conducted at day level)\n",
    "dataset1_1.drop(['HOUR'], axis='columns', inplace = True)\n",
    "df1 = dataset1_1.groupby(by=[\"DAY\",\"DATE\", \"MONTH\",\"YEAR\",\"STATE\"],as_index=False)[\"DEMAND\"].sum()\n",
    "file_name = \"Demand_daily.csv\"\n",
    "df1.to_csv(Link2 + \"\\\\\" + file_name, index=False)\n",
    "\n",
    "# Aggregate the data at month and State level\n",
    "dataset1_1.drop(['DAY', 'DATE'], axis='columns', inplace = True)\n",
    "df1 = dataset1_1.groupby(by=[\"MONTH\",\"YEAR\",\"STATE\"], as_index=False)[\"DEMAND\"].sum()\n",
    "file_name = \"Demand_monthly.csv\"\n",
    "df1.to_csv(Link2 + \"\\\\\" + file_name, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# DATASET-2- PART 1\n",
    "dataset2 = pd.read_csv(Link + \"\\\\\" + 'Temp_State_Daily_Max_2014_2020.csv')\n",
    "dataset2.head(5)\n",
    "# Keep Desried columns\n",
    "dataset2_0 = dataset2[[\"Year\", \"Month\", \"Day\", \"Maximum temperature (Degree C)\", \n",
    "                       \"File Identifier\"]]\n",
    "\n",
    "# Getting State info from file identifier and dropping the column File identifier\n",
    "dataset2_1 = dataset2_0.assign(STATE = lambda x: (x['File Identifier'].str)[:3])\n",
    "dataset2_1.drop(['File Identifier'], axis='columns', inplace = True)\n",
    "dataset2_1.head(5)\n",
    "\n",
    "#Change columnnames \n",
    "dataset2_2 = dataset2_1.rename(columns={'Year':'YEAR', 'Month':'MONTH', 'Day':'DAY', \n",
    "                                        \"Maximum temperature (Degree C)\":'MAX_TEMP'})\n",
    "\n",
    "#Change Statenames\n",
    "dataset2_2[\"STATE\"].value_counts() # Check State names\n",
    "dataset2_2[\"STATE\"].replace([\"SA_\", \"NT_\", \"Que\", \"WA_\"], [\"SA\", \"NT\", \"QLD\", \"WA\"], inplace= True)\n",
    "dataset2_2[\"STATE\"].value_counts() # Check if state names are modified correctly\n",
    "\n",
    "\n",
    "# Keeping dataset consist across all sources . We select only these states for which Demand data is avaliable\n",
    "#[\"VIC\", \"TAS\", \"NSW\", \"SA\", \"QLD\"]\n",
    "dataset2_3 = dataset2_2.loc[~dataset2_2[\"STATE\"].isin([\"WA\", \"NT\", \"ACT\"])]\n",
    "dataset2_3[\"STATE\"].value_counts() # Checking if other states are dropped\n",
    "\n",
    "#Treating missing values of Max temperature and Min Temperature\n",
    "# As we have high resolution data, we can interpolate missing values based on time, State\n",
    "dataset2_3.loc[dataset2_3.MAX_TEMP.isnull(), \"MAX_TEMP\"] = dataset2_3.groupby([\"MONTH\",\"STATE\"]).MAX_TEMP.transform('mean')\n",
    "\n",
    "# Saving modified file daily\n",
    "file_name = \"Temp_daily_Max.csv\"\n",
    "dataset2_3.to_csv(Link2 + \"\\\\\" + file_name, index=False)\n",
    "\n",
    "# # # Making Monthly file\n",
    "dataset2_3.drop(['DAY'], axis='columns', inplace = True)\n",
    "\n",
    "file_name = \"Temp_monthly_Max.csv\"\n",
    "df = dataset2_3.groupby(by=[\"MONTH\",\"YEAR\",\"STATE\"], as_index=False)[\"MAX_TEMP\"].mean()\n",
    "df.to_csv(Link2 + \"\\\\\" + file_name, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DATASET-2- PART 1\n",
    "dataset2 = pd.read_csv(Link + \"\\\\\" + 'Temp_State_Daily_Min_2014_2020.csv')\n",
    "dataset2.head(5)\n",
    "# Keep Desried columns\n",
    "dataset2_0 = dataset2[[\"Year\", \"Month\", \"Day\", \"Minimum temperature (Degree C)\", \n",
    "                       \"File Identifier\"]]\n",
    "\n",
    "# Getting State info from file identifier and dropping the column File identifier\n",
    "dataset2_1 = dataset2_0.assign(STATE = lambda x: (x['File Identifier'].str)[:3])\n",
    "dataset2_1.drop(['File Identifier'], axis='columns', inplace = True)\n",
    "dataset2_1.head(5)\n",
    "\n",
    "#Change columnnames \n",
    "dataset2_2 = dataset2_1.rename(columns={'Year':'YEAR', 'Month':'MONTH', 'Day':'DAY', \n",
    "                                        \"Minimum temperature (Degree C)\":'MIN_TEMP'})\n",
    "\n",
    "#Change Statenames\n",
    "dataset2_2[\"STATE\"].value_counts() # Check State names\n",
    "dataset2_2[\"STATE\"].replace([\"SA_\", \"NT_\", \"Que\", \"WA_\"], [\"SA\", \"NT\", \"QLD\", \"WA\"], inplace= True)\n",
    "dataset2_2[\"STATE\"].value_counts() # Check if state names are modified correctly\n",
    "\n",
    "\n",
    "# Keeping dataset consist across all sources . We select only these states for which Demand data is avaliable\n",
    "#[\"VIC\", \"TAS\", \"NSW\", \"SA\", \"QLD\"]\n",
    "dataset2_3 = dataset2_2[~dataset2_2[\"STATE\"].isin([\"WA\", \"NT\", \"ACT\"])]\n",
    "dataset2_3[\"STATE\"].value_counts() # Checking if other states are dropped\n",
    "\n",
    "#Treating missing values of Max temperature and Min Temperature\n",
    "# As we have high resolution data, we can interpolate missing values based on time, State\n",
    "dataset2_3.loc[dataset2_3.MIN_TEMP.isnull(), \"MIN_TEMP\"] = dataset2_3.groupby([\"MONTH\",\"STATE\"]).MIN_TEMP.transform('mean')\n",
    "\n",
    "# Saving modified file daily\n",
    "file_name = \"Temp_daily_Min.csv\"\n",
    "dataset2_3.to_csv(Link2 + \"\\\\\" + file_name, index=False)\n",
    "\n",
    "# # # Making Monthly file\n",
    "dataset2_3.drop(['DAY'], axis='columns', inplace = True)\n",
    "\n",
    "file_name = \"Temp_monthly_Min.csv\"\n",
    "df = dataset2_3.groupby(by=[\"MONTH\",\"YEAR\",\"STATE\"], as_index=False)[\"MIN_TEMP\"].mean()\n",
    "df.to_csv(Link2 + \"\\\\\" + file_name, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DATASET-3 PART-1\n",
    "dataset3 = pd.read_excel(Link + \"\\\\\" + 'Employment_Unemployment_state_month_2014_2020.xls')\n",
    "\n",
    "# Getting desired columns using melt function\n",
    "dataset3_0 = dataset3.melt(id_vars = ['Month'],\n",
    "             var_name = \"State\",\n",
    "             value_name = \"EPR\")\n",
    "\n",
    "#Change Statenames\n",
    "dataset3_1 = dataset3_0.assign(STATE = lambda x:(x['State'].str)[47:])\n",
    "dataset3_1[\"STATE\"].replace([\"South Australia ;\", \"Western Australia ;\", \"Queensland ;\", \"Northern Territory ;\", \n",
    "                             \"Australian Capital Territory ;\",  \"Victoria ;\", \"New South Wales ;\", \"Tasmania ;\" ], \n",
    "                            [\"SA\", \"WA\", \"QLD\", \"NT\", \"ACT\", \"VIC\", \"NSW\", \"TAS\"], inplace= True)\n",
    "#dataset3_1[\"STATE\"].value_counts() \n",
    "\n",
    "# Keeping dataset consist across all sources . We select only these states for which Demand data is avaliable\n",
    "#[\"VIC\", \"TAS\", \"NSW\", \"SA\", \"QLD\"]\n",
    "dataset3_2 = dataset3_1.loc[~dataset3_1[\"STATE\"].isin([\"WA\", \"NT\", \"ACT\"])]\n",
    "#dataset3_2[\"STATE\"].value_counts() # Checking if other states are dropped\n",
    "\n",
    "# Changing Date element\n",
    "dataset3_2['DATE'] = pd.to_datetime(dataset3_2[\"Month\"]).dt.date\n",
    "dataset3_2['YEAR'] = pd.to_datetime(dataset3_2['Month']).dt.year\n",
    "dataset3_2['MONTH'] = pd.to_datetime(dataset3_2['Month']).dt.month\n",
    "dataset3_2.drop(['Month', 'State'], axis='columns', inplace = True)\n",
    "\n",
    "# Save the modified file\n",
    "file_name = \"EmploymentStatistics_Monthly.csv\"\n",
    "dataset3_2.to_csv(Link2 + \"\\\\\" + file_name, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DATASET-3 PART-2\n",
    "dataset3 = pd.read_excel(Link + \"\\\\\" + 'Employment_Unemployment_state_month_2014_2020.xls', sheet_name = \"Unemployment Rate\")\n",
    "# Getting desired columns using melt function\n",
    "dataset3_0 = dataset3.melt(id_vars = ['Month'],\n",
    "             var_name = \"State\",\n",
    "             value_name = \"Unemploy_Rate\")\n",
    "\n",
    "#Change Statenames\n",
    "dataset3_1 = dataset3_0.assign(STATE = lambda x:(x['State'].str)[34:])\n",
    "dataset3_1[\"STATE\"].replace([\"South Australia ;\", \"Western Australia ;\", \"Queensland ;\", \"Northern Territory ;\", \n",
    "                              \"Australian Capital Territory ;\",  \"Victoria ;\", \"New South Wales ;\", \"Tasmania ;\" ], \n",
    "                             [\"SA\", \"WA\", \"QLD\", \"NT\", \"ACT\", \"VIC\", \"NSW\", \"TAS\"], inplace= True)\n",
    "dataset3_1[\"STATE\"].value_counts() \n",
    "\n",
    "# Keeping dataset consist across all sources . We select only these states for which Demand data is avaliable\n",
    "# [\"VIC\", \"TAS\", \"NSW\", \"SA\", \"QLD\"]\n",
    "dataset3_2 = dataset3_1.loc[~dataset3_1[\"STATE\"].isin([\"WA\", \"NT\", \"ACT\"])]\n",
    "#dataset3_2[\"STATE\"].value_counts() # Checking if other states are dropped\n",
    "\n",
    "# # Changing Date element\n",
    "dataset3_2['DATE'] = pd.to_datetime(dataset3_2[\"Month\"]).dt.date\n",
    "dataset3_2['YEAR'] = pd.to_datetime(dataset3_2['Month']).dt.year\n",
    "dataset3_2['MONTH'] = pd.to_datetime(dataset3_2['Month']).dt.month\n",
    "dataset3_2.drop(['Month', 'State'], axis='columns', inplace = True)\n",
    "# dataset3_2.head(3)\n",
    "\n",
    "# # Save the modified file\n",
    "file_name = \"UnemploymentStatistics_Monthly.csv\"\n",
    "dataset3_2.to_csv(Link2 + \"\\\\\" + file_name, index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "#DATASET -3 PART 3\n",
    "dataset3 = pd.read_excel(Link + \"\\\\\" + 'Employment_Unemployment_state_month_2014_2020.xls', sheet_name = \"Population\")\n",
    "# Getting desired columns using melt function\n",
    "dataset3_0 = dataset3.melt(id_vars = ['Month'],\n",
    "             var_name = \"State\",\n",
    "             value_name = \"Population\")\n",
    "\n",
    "#Change Statenames\n",
    "dataset3_1 = dataset3_0.assign(STATE = lambda x:(x['State'].str)[22:])\n",
    "dataset3_1[\"STATE\"].replace([\"South Australia ;\", \"Western Australia ;\", \"Queensland ;\", \"Northern Territory ;\", \n",
    "                              \"Australian Capital Territory ;\",  \"Victoria ;\", \"New South Wales ;\", \"Tasmania ;\" ], \n",
    "                            [\"SA\", \"WA\", \"QLD\", \"NT\", \"ACT\", \"VIC\", \"NSW\", \"TAS\"], inplace= True)\n",
    "\n",
    "# Keeping dataset consist across all sources . We select only these states for which Demand data is avaliable\n",
    "# [\"VIC\", \"TAS\", \"NSW\", \"SA\", \"QLD\"]\n",
    "dataset3_2 = dataset3_1.loc[~dataset3_1[\"STATE\"].isin([\"WA\", \"NT\", \"ACT\"])]\n",
    "#dataset3_2[\"STATE\"].value_counts() # Checking if other states are dropped\n",
    "\n",
    "# # # Changing Date element\n",
    "dataset3_2['DATE'] = pd.to_datetime(dataset3_2.loc[:, 'Month']).dt.date\n",
    "dataset3_2['YEAR'] = pd.to_datetime(dataset3_2.loc[:, 'Month']).dt.year\n",
    "dataset3_2['MONTH'] = pd.to_datetime(dataset3_2.loc[:, 'Month']).dt.month\n",
    "dataset3_2.drop(['Month', 'State'], axis='columns', inplace = True)\n",
    "\n",
    "# # # Save the modified file\n",
    "file_name = \"Population_Monthly.csv\"\n",
    "dataset3_2.to_csv(Link2 + \"\\\\\" + file_name, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DATASET-4\n",
    "dataset4 = pd.read_csv(Link + \"\\\\\" + 'Australian_public_holidays_2014_2020.csv')\n",
    "\n",
    "# Keeping desired columns\n",
    "dataset4_1 = dataset4[['Date', 'Applicable To', 'Jurisdiction']]\n",
    "\n",
    "# Breaking Applicable to : i.e. NSW|SA|WA into different states\n",
    "dataset4_1[[\"S1\", \"S2\", \"S3\", \"S4\", \"S5\", \"S6\", \"S7\"]] = pd.DataFrame(dataset4_1['Applicable To'].str.split(\"|\", expand = True))\n",
    "\n",
    "#Breaking NAT ( National Holiday) into different states\n",
    "dataset4_1.loc[dataset4_1['Applicable To'] == 'NAT', ['S1', 'S2', 'S3', 'S4', 'S5', 'S6', 'S7']] = ['NSW', 'SA', 'WA', 'QLD', 'TAS', 'NT', 'ACT']\n",
    "\n",
    "# for year 2019 & 2020, state is recorded under column jurisdiction\n",
    "dataset4_1['S8'] = None \n",
    "dataset4_1.loc[dataset4_1['Jurisdiction'] != None, 'S8']= dataset4_1['Jurisdiction'].str.upper()\n",
    "dataset4_1.loc[dataset4_1['Applicable To'] == 'NAT', 'S8']= 'VIC'\n",
    "\n",
    "# Getting Desired columns \n",
    "dataset4_2 = dataset4_1[['Date', 'S1', 'S2', 'S3', 'S4', 'S5', 'S6', 'S7', 'S8']]\n",
    "\n",
    "# Getting desired columns using melt function\n",
    "dataset4_3 = dataset4_2.melt(id_vars = ['Date'],\n",
    "             var_name = \"state\",\n",
    "             value_name = \"STATE\")\n",
    "\n",
    "# Deleting NaN Values and other states\n",
    "\n",
    "dataset4_4 = dataset4_3.loc[~dataset4_3[\"STATE\"].isin([\"WA\", \"NT\", \"ACT\", None])]\n",
    "dataset4_5 = dataset4_4[['Date', 'STATE']]\n",
    "\n",
    "# Adding Holiday Flag\n",
    "dataset4_5['Holiday_Flag'] = 1\n",
    "\n",
    "# Date objects\n",
    "dataset4_5['DATE'] = pd.to_datetime(dataset4_5[\"Date\"], format='%Y%m%d').dt.date\n",
    "dataset4_5['DAY'] = pd.to_datetime(dataset4_5[\"DATE\"]).dt.day\n",
    "dataset4_5['YEAR'] = pd.to_datetime(dataset4_5['DATE']).dt.year\n",
    "dataset4_5['MONTH'] = pd.to_datetime(dataset4_5['DATE']).dt.month\n",
    "\n",
    "dataset4_5.drop(['Date'], axis='columns', inplace = True)\n",
    "\n",
    "# Saving File\n",
    "# Save the modified file\n",
    "file_name = \"Hoilidays.csv\"\n",
    "dataset4_5.to_csv(Link2 + \"\\\\\" + file_name, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}