Coldwell Data Transformation.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "af662bc1",
      "metadata": {
        "id": "af662bc1"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "import re\n",
        "import os"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "96e8f143",
      "metadata": {
        "id": "96e8f143"
      },
      "outputs": [],
      "source": [
        "# Inpust section\n",
        "\n",
        "df = pd.read_excel(r\"D:\\coldwell\\Commercial Website sheet.xlsx\")\n",
        "projct_table = pd.read_excel(r\"D:\\coldwell\\ProjectTable_20231113173024.xlsx\")\n",
        "\n",
        "#export_at = r\"D:\\coldwell\\pending\"\n",
        "#status__ = \"pending\"\n",
        "\n",
        "export_at = r\"D:\\coldwell\\Publish\"\n",
        "status__ = \"published\"\n",
        "\n",
        "project_type = \"commertial\"\n",
        "#project_type = \"Resedetial\"\n",
        "#project_type = \"Costal\"\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "111b01f8",
      "metadata": {
        "id": "111b01f8",
        "outputId": "4f6d8d82-b414-476e-98ef-9ab3e078a0c8"
      },
      "outputs": [],
      "source": [
        "                              ### ======================= MAPS ======================= ###\n",
        "\n",
        "# Replaced all real values with dummy ones for confidentiality purposes\n",
        "\n",
        "translation = {'English word 1': 'الاسم العربي 1', 'ُEnglish word 2': 'الاسم العربي 2' }\n",
        "category_map = {'wrongcat.writingstyle': 'Right Cat. Writing Style', 'cat1sub2': 'Cat1', }\n",
        "finishing_map = {'English word 1': 'الاسم العربي 1', 'ُEnglish word 2': 'الاسم العربي 2'}\n",
        "Deliver_Status_map = {\"inputsyle\": \"Unified Out Put Style\", \"inputsyle2\": \"Unified Out Put Style2\" }\n",
        "comparing_new_and_old_projects = {}\n",
        "\n",
        "project_type_maps = {\"commertial\": {\"dummyprojectname1\": \"Dummy Old Project Name 1.0\", \"dummyprojectnamephase5\": \"Dummy Project Name | Phase 5\"},\n",
        "                     \"Resedetial\": {\"dummyresedetialprojectname1\": \"Dummy Resedetial Project Name 1.0\", \"dummyprojectnamephase5\": \"Dummy Old Project Name | Phase 5\"},\n",
        "                     \"Costal\":{\"dummycostalprojectname1\": \"Dummy Costal Project Name 1.0\", \"dummyprojectcostalnamephase5\": \"Dummy Project Costal Name | Phase 5\"}}\n",
        "\n",
        "# Used to compare new naming styles with names already found on the system to be able to update them without considering them new\n",
        "old_projects_naming_map =  project_type_maps[project_type]\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "                          ### ======================= Output Lists ======================= ###\n",
        "\n",
        "Name,Starting_Price,Concept_Name,Area_From,Area_To,Finishing,Description,Is_Sold,Price_Per_Meter,Category,Project,Status,Import_Type,Name_AR,Description_AR,Finishing_AR,show_in_price_indicator= [],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[]\n",
        "Projects_list, Deliver_Statuses, Delivery_Dates, Down_Payment_list, Starting_Price_list, Installments_Years_list, Status_list = [], [], [], [], [], [], []\n",
        "ucn_has_error = {}\n",
        "\n",
        "\n",
        "                     ### ======================= Removing trash rows ======================= ###\n",
        "\n",
        "\n",
        "no_ucn_df = df[df[\"Unit Concept Name\"].isna()]\n",
        "no_ucn_df[\"error\"] = \"NO UCN\"\n",
        "df = df[~df[\"Unit Concept Name\"].isna()]\n",
        "\n",
        "no_project_name_df = df[df[\"Project Name  ↑\"].isna()]\n",
        "no_project_name_df[\"error\"] = \"NO Project Name\"\n",
        "df = df[~df[\"Project Name  ↑\"].isna()]\n",
        "\n",
        "no_unit_type_df = df[df[\"Unit Type\"].isna()]\n",
        "no_unit_type_df[\"error\"] = \"No Unit Type\"\n",
        "df = df[~df[\"Unit Type\"].isna()]\n",
        "\n",
        "no_delivery_date_df = df[df[\"Delivery Date\"].isna()]\n",
        "no_delivery_date_df[\"error\"] = \"No Delivery Date\"\n",
        "df = df[~df[\"Delivery Date\"].isna()]\n",
        "\n",
        "catastrophic_errors = pd.concat([no_ucn_df, no_project_name_df,no_delivery_date_df,  no_unit_type_df], ignore_index=True, sort=False)\n",
        "catastrophic_errors.to_excel(r\"{}\\{}\".format(export_at,\"catastrophic_errors.xlsx\"), index=False)\n",
        "\n",
        "                     ### ======================= Projects_Units_Sheet ======================= ###\n",
        "for ucn in df[\"Unit Concept Name\"].unique():\n",
        "\n",
        "    error_note = \"\"\n",
        "\n",
        "    # ----------- Name ----------- #\n",
        "\n",
        "    try:\n",
        "        Name.append(list(df[df[\"Unit Concept Name\"] == ucn][\"Unit Type\"])[0])\n",
        "\n",
        "        if list(df[df[\"Unit Concept Name\"] == ucn][\"Unit Type\"])[0] is None:\n",
        "            print(ucn, f\"Name (Unit Type) is not found\")\n",
        "            error_note += f\" |Name (Unit Type) is not found\"\n",
        "            Name.append(np.nan)\n",
        "\n",
        "\n",
        "    except Exception as e:\n",
        "        print(ucn, f\": Name (Unit Type) has error {e}\")\n",
        "        error_note += f\" |Name (Unit Type) has error {e}\"\n",
        "        Name.append(np.nan)\n",
        "\n",
        "    # ----------- Project ----------- #\n",
        "\n",
        "    try:\n",
        "        Project.append(str(list(df[df[\"Unit Concept Name\"] == ucn][\"Project Name  ↑\"])[0]) + \" | \" + str(list(df[df[\"Unit Concept Name\"] == ucn][\"Phase Name  ↑\"])[0]))\n",
        "\n",
        "        if list(df[df[\"Unit Concept Name\"] == ucn][\"Project Name  ↑\"])[0] is None:\n",
        "            print(ucn, f\"Project Name not found\")\n",
        "            error_note += f\" |Project Name not found\"\n",
        "            Project.append(np.nan)\n",
        "\n",
        "        if list(df[df[\"Unit Concept Name\"] == ucn][\"Phase Name  ↑\"])[0] is None:\n",
        "            print(ucn, f\"Phase Name not found\")\n",
        "            error_note += f\" |Phase Name not found\"\n",
        "            Project.append(np.nan)\n",
        "\n",
        "    except Exception as e:\n",
        "        print(ucn, f\": Project name or phase name has error {e}\")\n",
        "        error_note += f\" |Project name or phase name has error {e}\"\n",
        "        Project.append(np.nan)\n",
        "\n",
        "    # ----------- Starting_Price ----------- #\n",
        "\n",
        "    try:\n",
        "        Starting_Price.append(list(df[df[\"Unit Concept Name\"] == ucn][\"Price From\"])[0])\n",
        "\n",
        "        if list(df[df[\"Unit Concept Name\"] == ucn][\"Price From\"])[0] is None:\n",
        "            print(ucn, f\": Price From is not found\")\n",
        "            error_note += f\" |Price From is not found\"\n",
        "            Starting_Price.append(np.nan)\n",
        "\n",
        "    except Exception as e:\n",
        "        print(ucn, f\"Price From has error {e}\")\n",
        "        error_note += f\" |Price has error {e}\"\n",
        "        Starting_Price.append(np.nan)\n",
        "\n",
        "\n",
        "    # ----------- Concept_Name (UCN) ----------- #\n",
        "\n",
        "    Concept_Name.append(ucn)\n",
        "    if ucn is None:\n",
        "        print(ucn, f\"Concept_Name (UCN) is not found\")\n",
        "        error_note += f\" |Concept_Name (UCN) is not found\"\n",
        "        Concept_Name.append(np.nan)\n",
        "\n",
        "\n",
        "    # ----------- Finishing ----------- #\n",
        "    finishing_has_error = False\n",
        "    try:\n",
        "        Finishing.append(list(df[df[\"Unit Concept Name\"] == ucn][\"Finishing Type\"])[0])\n",
        "\n",
        "    except Exception as e:\n",
        "        print(ucn, f\"Finishing has error {e}\")\n",
        "        error_note += f\" |Finishing has error {e}\"\n",
        "        Finishing.append(np.nan)\n",
        "        finishing_has_error = True\n",
        "\n",
        "    # ----------- Status & Import_Type ----------- #\n",
        "\n",
        "    Status.append(status__)\n",
        "    Import_Type.append(\"units\")\n",
        "\n",
        "    # ----------- Area_From ----------- #\n",
        "    #check if the area has decimals or not\n",
        "    try:\n",
        "        if int(list(df[df[\"Unit Concept Name\"] == ucn][\"Build Up Area From\"])[0]) == list(df[df[\"Unit Concept Name\"] == ucn][\"Build Up Area From\"])[0]:\n",
        "            Area_From.append(int(list(df[df[\"Unit Concept Name\"] == ucn][\"Build Up Area From\"])[0]))\n",
        "        else:\n",
        "            Area_From.append(list(df[df[\"Unit Concept Name\"] == ucn][\"Build Up Area From\"])[0])\n",
        "    except:\n",
        "        print(ucn, f\":Area From is not found\")\n",
        "        error_note += f\" |Area From is not found\"\n",
        "        Area_From.append(np.nan)\n",
        "\n",
        "\n",
        "    # ----------- Area_To ----------- #\n",
        "    #check if the area has decimals or not\n",
        "    try:\n",
        "        if int(list(df[df[\"Unit Concept Name\"] == ucn][\"Build Up Area To\"])[0]) == list(df[df[\"Unit Concept Name\"] == ucn][\"Build Up Area To\"])[0]:\n",
        "            Area_To.append(int(list(df[df[\"Unit Concept Name\"] == ucn][\"Build Up Area To\"])[0]))\n",
        "        else:\n",
        "            Area_To.append(list(df[df[\"Unit Concept Name\"] == ucn][\"Build Up Area To\"])[0])\n",
        "    except:\n",
        "        Area_To.append(np.nan)\n",
        "\n",
        "\n",
        "    # ----------- Category ----------- #\n",
        "    try:\n",
        "        Category.append(category_map[str(list(df[df[\"Unit Concept Name\"] == ucn][\"Unit Type\"])[0]).lower().replace(\" \",\"\")])\n",
        "    except:\n",
        "        print(ucn, f\"Category not found\")\n",
        "        error_note += f\" |Category not found\"\n",
        "        Category.append(np.nan)\n",
        "\n",
        "\n",
        "    # ----------- Finishing_AR ----------- #\n",
        "    if not finishing_has_error:\n",
        "        try:\n",
        "            Finishing_AR.append(finishing_map[list(df[df[\"Unit Concept Name\"] == ucn][\"Finishing Type\"])[0]])\n",
        "        except:\n",
        "            print(ucn, f\":{list(df[df['Unit Concept Name'] == ucn]['Finishing Type'])[0]} Finishing transation not found\")\n",
        "            error_note += f\" |{list(df[df['Unit Concept Name'] == ucn]['Finishing Type'])[0]} Finishing transation not found\"\n",
        "            Finishing_AR.append(np.nan)\n",
        "\n",
        "\n",
        "    # ----------- Name_AR ----------- #\n",
        "    try:\n",
        "        Name_AR.append(translation[list(df[df[\"Unit Concept Name\"] == ucn][\"Unit Type\"])[0]])\n",
        "    except:\n",
        "        print(ucn, f\"Name transation not found\")\n",
        "        error_note += f\" |Name transation not found\"\n",
        "        Name_AR.append(np.nan)\n",
        "\n",
        "    # ----------- Append ----------- #\n",
        "    if error_note != \"\":\n",
        "        ucn_has_error[ucn] =error_note\n",
        "\n",
        "    Description.append(np.nan)\n",
        "    Is_Sold.append(0)\n",
        "    Price_Per_Meter.append(0)\n",
        "    Description_AR.append(np.nan)\n",
        "    show_in_price_indicator = 1\n",
        "\n",
        "\n",
        "\n",
        "                          ### ======================= Starting_price_Sheet ======================= ###\n",
        "\n",
        "for project_name in df[\"Project Name  ↑\"].unique():\n",
        "    for phase in df[df[\"Project Name  ↑\"] == project_name][\"Phase Name  ↑\"].unique():\n",
        "        Projects_list.append(project_name+ \" | \"+ phase)\n",
        "        mapped_status = Deliver_Status_map.get(re.sub(r'[^a-zA-Z0-9]', '', str(df[((df[\"Project Name  ↑\"] == project_name) & (df[\"Phase Name  ↑\"] == phase))][\"Delivery Status\"].iloc[0]).lower().replace(\" \",\"\")), np.nan)\n",
        "        Deliver_Statuses.append(mapped_status)\n",
        "        Delivery_Dates.append(f'1/1/{int(list(df[((df[\"Project Name  ↑\"] == project_name) & (df[\"Phase Name  ↑\"] == phase))][\"Delivery Date\"])[0])}')\n",
        "        Down_Payment_list.append(min(list(df[((df[\"Project Name  ↑\"] == project_name) & (df[\"Phase Name  ↑\"] == phase))][\"Down Payment\"]))*100)\n",
        "        Starting_Price_list.append(min(list(df[((df[\"Project Name  ↑\"] == project_name) & (df[\"Phase Name  ↑\"] == phase))][\"Price From\"])))\n",
        "        Installments_Years_list.append(max(list(df[((df[\"Project Name  ↑\"] == project_name) & (df[\"Phase Name  ↑\"] == phase))][\"Years of Installments\"])))\n",
        "        Status_list.append(status__)\n",
        "\n",
        "                           ### ======================= Down_payments_Sheet ======================= ###\n",
        "\n",
        "Down_payments = df[['Unit Concept Name', 'Down Payment', 'Years of Installments']].copy()\n",
        "Down_payments = Down_payments.rename(columns = {'Unit Concept Name':'Concept Name', 'Down Payment': 'Downpayment', 'Years of Installments': 'Installments year'})\n",
        "Down_payments.insert(2,\"Sec Downpayment\",np.nan)\n",
        "Down_payments[\"Status\"] = status__\n",
        "Down_payments[\"Downpayment\"] = Down_payments[\"Downpayment\"].apply(lambda x: x*100)\n",
        "\n",
        "# ----------- Check if the ucn has an error or not  ----------- #\n",
        "if Down_payments[\"Installments year\"].isnull().sum() >0 :\n",
        "\n",
        "    for ucn in Down_payments[Down_payments[\"Installments year\"].isnull()][\"Concept Name\"]:\n",
        "        try:\n",
        "            ucn_has_error[ucn] += \" | Installments year is not found\"\n",
        "        except KeyError:\n",
        "            ucn_has_error[ucn] = \" | Installments year is not found\"\n",
        "\n",
        "if Down_payments[\"Downpayment\"].isnull().sum() >0 :\n",
        "\n",
        "    for ucn in Down_payments[Down_payments[\"Downpayment\"].isnull()][\"Concept Name\"]:\n",
        "        try:\n",
        "            ucn_has_error[ucn] += \" | Downpayment is not found\"\n",
        "        except KeyError:\n",
        "            ucn_has_error[ucn] = \" | Downpayment is not found\"\n",
        "\n",
        "Down_payments = Down_payments[Down_payments[\"Concept Name\"].isin(ucn_has_error.keys()) == False]\n",
        "\n",
        "                          ### ======================= Ceating data frames ======================= ###\n",
        "\n",
        "output_df = pd.DataFrame({\"Name\": Name,\"Starting Price\": Starting_Price,\"Concept Name\": Concept_Name, \"Area From\": Area_From, \"Area To\": Area_To, \"Finishing\":Finishing ,\"Description\": Description,\"Is Sold\": Is_Sold, \"Price Per Meter\": Price_Per_Meter,\"Category\": Category,\"Project\": Project, \"Status\": Status,\"Import Type\": Import_Type ,\"Name AR\": Name_AR, \"Description AR\": Description_AR,\"Finishing AR\": Finishing_AR, \"Show in price indicator\": show_in_price_indicator})\n",
        "has_errors_df = pd.DataFrame({\"Concept Name\": ucn_has_error.keys(), \"Error\\s\":ucn_has_error.values()})\n",
        "starting_price_df = pd.DataFrame({\"Project\": Projects_list, \"Delivery Status\": Deliver_Statuses,\"Delivery Date\": Delivery_Dates, \"Down Payment\": Down_Payment_list, \"Starting Price\": Starting_Price_list, \"Installments Years\":Installments_Years_list ,\"Status\": Status_list})\n",
        "\n",
        "total = output_df.shape[0]  #<==== to check if the exported sheets are not missing any data\n",
        "\n",
        "\n",
        "                ### ======================= Comparing New projects With old Projects ======================= ###\n",
        "\n",
        "for index, row in projct_table.iterrows():\n",
        "    old_projects_naming_map[re.sub(r'[^a-zA-Z0-9]', '', str(row['Name']).lower().replace(\" \",\"\"))] = row['Name']\n",
        "\n",
        "output_df['Project'] = output_df['Project'].apply(lambda x: comparing_new_and_old_projects[re.sub(r'[^a-zA-Z0-9]', '', str(x).lower().replace(\" \",\"\"))] if re.sub(r'[^a-zA-Z0-9]', '', str(x).lower().replace(\" \",\"\")) in comparing_new_and_old_projects else x)\n",
        "output_df['Project'] = output_df['Project'].apply(lambda x: old_projects_naming_map[re.sub(r'[^a-zA-Z0-9]', '', str(x).lower().replace(\" \",\"\"))] if re.sub(r'[^a-zA-Z0-9]', '', str(x).lower().replace(\" \",\"\")) in old_projects_naming_map else x)\n",
        "\n",
        "starting_price_df['Project'] = starting_price_df['Project'].apply(lambda x: comparing_new_and_old_projects[re.sub(r'[^a-zA-Z0-9]', '', str(x).lower().replace(\" \",\"\"))] if re.sub(r'[^a-zA-Z0-9]', '', str(x).lower().replace(\" \",\"\")) in comparing_new_and_old_projects else x)\n",
        "starting_price_df['Project'] = starting_price_df['Project'].apply(lambda x: old_projects_naming_map[re.sub(r'[^a-zA-Z0-9]', '', str(x).lower().replace(\" \",\"\"))] if re.sub(r'[^a-zA-Z0-9]', '', str(x).lower().replace(\" \",\"\")) in old_projects_naming_map else x)\n",
        "\n",
        "\n",
        "                    ### ======================= Fltring and splitting data frames ======================= ###\n",
        "\n",
        "# ----------- Check if the ucn has an error or not  ----------- #\n",
        "has_errors_df = has_errors_df[has_errors_df[\"Concept Name\"].isin(ucn_has_error.keys()) == True]\n",
        "output_df = output_df[output_df[\"Concept Name\"].isin(ucn_has_error.keys()) == False]\n",
        "\n",
        "\n",
        "# ----------- Check if the project is already on the system or not  ----------- #\n",
        "needs_to_be_added = output_df[~output_df.Project.isin(projct_table[\"Name\"])]\n",
        "output_df = output_df[output_df.Project.isin(projct_table[\"Name\"])]\n",
        "\n",
        "\n",
        "# ----------- Split down payment sheet depending on if the project is already on the system or not  ----------- #\n",
        "Down_payments_to_upload = Down_payments[Down_payments[\"Concept Name\"].isin(output_df[\"Concept Name\"]) == True]\n",
        "Down_payments_to_be_added = Down_payments[Down_payments[\"Concept Name\"].isin(output_df[\"Concept Name\"]) == False]\n",
        "\n",
        "# ----------- Split starting price sheet depending on if the project is already on the system or not  ----------- #\n",
        "starting_price_df_to_upload = starting_price_df[starting_price_df.Project.isin(output_df[\"Project\"])]\n",
        "starting_price_df_to_be_added = starting_price_df[~starting_price_df.Project.isin(output_df[\"Project\"])]\n",
        "\n",
        "\n",
        "                    ### ======================= Exporting Excel sheets ======================= ###\n",
        "\n",
        "# Remove headers format from excel sheet\n",
        "output_df.to_excel(r\"{}\\{}\".format(export_at,\"Coldwell ready_to_upload (units).xlsx\"), index=False)\n",
        "pd.io.formats.excel.ExcelFormatter.header_style = None  #<====== remove headers format from excel sheet\n",
        "\n",
        "\n",
        "# ----------- ready_to_upload | needs_to_be_added | has_errors  ----------- #\n",
        "output_df.to_excel(r\"{}\\{}\".format(export_at,\"Coldwell ready_to_upload (units).xlsx\"), index=False)\n",
        "has_errors_df.to_excel(r\"{}\\{}\".format(export_at,\"Coldwell has_errors.xlsx\"), index=False)\n",
        "needs_to_be_added.to_excel(r\"{}\\{}\".format(export_at,\"Coldwell needs_to_be_added (units).xlsx\"), index=False)\n",
        "\n",
        "\n",
        "# ----------- Down Payments|Starting Price + Add percentage format to the \"Downpayment\" columns  ----------- #\n",
        "df_to_sheet = {\n",
        "    \"Down_payments_to_upload\": [Down_payments_to_upload, \"Coldwell ready_to_upload (Down Payments).xlsx\", \"B\"],\n",
        "    \"Down_payments_to_be_added\": [Down_payments_to_be_added, \"Coldwell needs_to_be_added (Down Payments).xlsx\", \"B\"],\n",
        "    \"starting_price_df_to_upload\": [starting_price_df_to_upload, \"Coldwell ready_to_upload (Starting Prices).xlsx\", \"D\"],\n",
        "    \"starting_price_df_to_be_added\": [starting_price_df_to_be_added, \"Coldwell needs_to_be_added (Starting Prices).xlsx\", \"D\"]\n",
        "}\n",
        "\n",
        "# Process and export dataframes\n",
        "for key, value in df_to_sheet.items():\n",
        "    # Drop duplicates in the dataframe (if not already dropped)\n",
        "    value[0] = value[0].drop_duplicates()\n",
        "\n",
        "    # Create the Excel path\n",
        "    excel_path = export_at + \"\\\\\" + value[1]\n",
        "\n",
        "    # Export the dataframe to Excel\n",
        "    value[0].to_excel(excel_path, index=False)\n",
        "\n",
        "\n",
        "total == needs_to_be_added.shape[0] + has_errors_df.shape[0] + output_df.shape[0]"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "provenance": []
    },
    "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.11.4"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}