suzlon_complete_list.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime as dt\n",
    "from dateutil.relativedelta import relativedelta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing and defining a variable for each spreadsheet \n",
    "\n",
    "\n",
    "###################################################################\n",
    "folder_name = '2021_09_set'  # <<< ALTERE O NOME DA PASTA AO LADO #\n",
    "###################################################################\n",
    "\n",
    "\n",
    "df1 = pd.read_excel(folder_name + \"\\Suzlon_Set (1).xlsx\")\n",
    "df2 = pd.read_excel(folder_name + \"\\Suzlon_Set (2).xlsx\")\n",
    "df3 = pd.read_excel(folder_name + \"\\Suzlon_Set (3).xlsx\")\n",
    "df4 = pd.read_excel(folder_name + \"\\Suzlon_Set (4).xlsx\")\n",
    "df5 = pd.read_excel(folder_name + \"\\Suzlon_Set (5).xlsx\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1736 is the total number of lines in this spreadsheet\n"
     ]
    }
   ],
   "source": [
    "# Appending all spreadsheets into one and counting the number of rows  \n",
    "\n",
    "df = df1.append(df2, ignore_index = True)\n",
    "df = df.append(df3, ignore_index = True)\n",
    "df = df.append(df4, ignore_index = True)\n",
    "df = df.append(df5, ignore_index = True)\n",
    "\n",
    "print('%s is the total number of lines in this spreadsheet' %len(df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "##Changing columns names.\n",
    "\n",
    "df.rename(columns = {\"respDescription\" : \"Responsability\",\n",
    "                     \"Power Plant\" : \"Power_plant\", \n",
    "                     \"Element\" : \"WTG\",\n",
    "                     \"Downtime\" : \"Downtime_type\",\n",
    "                     \"Time from\" : \"Time_from\",\n",
    "                     \"Time to\" : \"Time_to\"\n",
    "                    }, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Adjusting the values in each column for the right data type.\n",
    "\n",
    "df[\"Responsability\"] = df[\"Responsability\"].astype(\"category\")\n",
    "df[\"Power_plant\"] = df[\"Power_plant\"].astype(\"category\")\n",
    "df[\"WTG\"] = df[\"WTG\"].astype(\"category\")\n",
    "df[\"Downtime_type\"] = df[\"Downtime_type\"].astype(\"category\")\n",
    "df[\"Time_from\"] = pd.to_datetime(df[\"Time_from\"])\n",
    "df[\"Time_to\"] = pd.to_datetime(df[\"Time_to\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "## changing time before the working month to the first day/hour/second of \n",
    "## the month to study only the analysed month.\n",
    "\n",
    "time = dt.datetime.now()\n",
    "time_to = time.replace( day=1, hour=0, minute=0, second=0, microsecond=0)\n",
    "time_from = time_to - relativedelta(months=1)\n",
    "# print('time from: %s' %time_from)\n",
    "# print('time to  : %s' %time_to)\n",
    "\n",
    "\n",
    "mask = df[\"Time_from\"]  <= time_from\n",
    "df.loc[mask, \"Time_from\"] = time_from\n",
    "# df[\"Time_from\"].min()\n",
    "\n",
    "\n",
    "## changing time before the working month to the first day/hour/second of \n",
    "## the month to study only the analysed month.\n",
    "\n",
    "mask2 = df[\"Time_to\"]  >= time_to\n",
    "df.loc[mask2, \"Time_to\"] = time_to\n",
    "# df[\"Time_to\"].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Converting timedelta \"Duration\" column to a float number.\n",
    "\n",
    "df[\"Duration\"] = df[\"Time_to\"] - df[\"Time_from\"]\n",
    "mask3 = df[\"Duration\"]\n",
    "df[\"Duration\"] = mask3.dt.total_seconds()/3600"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "## separating the alarm code column into categories: alarm number, code and description.\n",
    "\n",
    "df[[\"Alarm_number\", \"Alarm_code\",\"Alarm_descrition\"]] = df.Alarm.str.split(\" - \", expand = True)\n",
    "df.drop(columns = \"Alarm\", inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "## filling na with corrective maintenance code and setting it as integer.\n",
    "\n",
    "df[\"Alarm_number\"] = df[\"Alarm_number\"].fillna(\"2\")\n",
    "df[\"Alarm_number\"] = df[\"Alarm_number\"].astype(\"int\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "## opening up the suzlon codes spreadsheet as reference for system and alarm correlation.\n",
    "\n",
    "suzlon_codes = pd.read_excel(\"suzlon_codes.xlsx\")\n",
    "# suzlon_codes.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Joining system by alarms in the df.\n",
    "\n",
    "df = df.merge(suzlon_codes, how = \"inner\",  left_on = \"Alarm_number\", right_on = \"number\" )\n",
    "df.drop(columns = \"number\", inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Joining System stops by alarms to the table\n",
    "\n",
    "suzlon_powerplant = pd.read_excel(\"suzlon_powerplant.xlsx\")\n",
    "# suzlon_powerplant.info()\n",
    "\n",
    "df = df.merge(suzlon_powerplant, how = \"left\",  left_on = \"Power_plant\", right_on = \"Power_plants\")\n",
    "df.drop(columns = \"Power_plants\", inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "## adjusting the data types of some columns.\n",
    "\n",
    "df[\"Power_plant\"] = df[\"Power_plant\"].astype(\"category\")\n",
    "df[\"system\"] = df[\"system\"].astype(\"category\")\n",
    "df[\"Complex\"] = df[\"Complex\"].astype(\"category\")\n",
    "df[\"Status\"] = df[\"Status\"].astype(\"category\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "## organizing the df to the new index.\n",
    "\n",
    "df.sort_values([\"Power_plant\",\"WTG\",\"Time_from\"], ascending=[True, True, True], inplace=True)\n",
    "df.reset_index(drop = True, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "## creating the output file with the new dataframe.\n",
    "\n",
    "file_name = 'complete_list_suzlon_' + time_from.strftime(\"%m\") + '.xlsx'\n",
    "excel_file = pd.ExcelWriter(file_name)\n",
    "df.to_excel(excel_file, sheet_name = \"Suzlon\", index=False, freeze_panes = (1,0))\n",
    "excel_file.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}