Correlation file generator.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "344677ff",
   "metadata": {},
   "source": [
    "<font size=\"3\">Create Correlation Matrix for every every material in xlsx format to directory ./preprocessed dataset/filtered dataset/correlation files </font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "80fae0b0",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\HowitzerPRO\\AppData\\Local\\Temp\\ipykernel_33040\\1350112906.py:36: FutureWarning: The default value of numeric_only in DataFrame.corr is deprecated. In a future version, it will default to False. Select only valid columns or specify the value of numeric_only to silence this warning.\n",
      "  correlation_matrix = filtered_df.corr()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Correlation files created in directory:  ./preprocessed dataset/filtered dataset/correlation files\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "from openpyxl.styles import PatternFill\n",
    "from openpyxl import Workbook\n",
    "\n",
    "# Specify the directory path\n",
    "data_dir = './preprocessed dataset/filtered dataset/full_dataset'\n",
    "\n",
    "# Load and preprocess the data from multiple CSV files\n",
    "df_list = []\n",
    "for file_name in os.listdir(data_dir):\n",
    "    if file_name.endswith('.csv'):\n",
    "        file_path = os.path.join(data_dir, file_name)\n",
    "        df = pd.read_csv(file_path)\n",
    "        df_list.append(df)\n",
    "\n",
    "# Concatenate the dataframes into a single dataframe\n",
    "df = pd.concat(df_list, ignore_index=True)\n",
    "\n",
    "# Directory path where Excel files will be saved\n",
    "output_directory = './preprocessed dataset/filtered dataset/correlation files'\n",
    "# Create the output directory if it doesn't exist\n",
    "\n",
    "if not os.path.exists(output_directory):\n",
    "    os.makedirs(output_directory)\n",
    "\n",
    "# Get the unique values in the 'Material' column\n",
    "material_values = df['Material'].unique()\n",
    "\n",
    "# Calculate the correlation for each unique value in the 'Material' column\n",
    "for material in material_values:\n",
    "    # Filter the DataFrame for the current material\n",
    "    filtered_df = df[df['Material'] == material]\n",
    "    \n",
    "    # Calculate the correlation matrix for the filtered DataFrame\n",
    "    correlation_matrix = filtered_df.corr()\n",
    "    \n",
    "    # Create a new workbook\n",
    "    workbook = Workbook()\n",
    "    \n",
    "    # Create a new worksheet\n",
    "    worksheet = workbook.active\n",
    "    \n",
    "    # Write the header names of columns\n",
    "    for col, column_name in enumerate(correlation_matrix.columns):\n",
    "        cell = worksheet.cell(row=1, column=col+2)  # Start from column 2 to leave space for row names\n",
    "        cell.value = column_name\n",
    "    \n",
    "    # Write the header names of rows\n",
    "    for row, row_name in enumerate(correlation_matrix.index):\n",
    "        cell = worksheet.cell(row=row+2, column=1)  # Start from row 2 to leave space for column names\n",
    "        cell.value = row_name\n",
    "    \n",
    "    # Iterate over the correlation matrix and apply formatting\n",
    "    for row in range(correlation_matrix.shape[0]):\n",
    "        for col in range(correlation_matrix.shape[1]):\n",
    "            cell_value = correlation_matrix.iloc[row, col]\n",
    "            \n",
    "            # Apply green fill to cells with values > 0.7 or < -0.7\n",
    "            if cell_value > 0.7 or cell_value < -0.7:\n",
    "                cell = worksheet.cell(row=row+2, column=col+2)  # Adjust row and column indices for data cells\n",
    "                cell.fill = PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')\n",
    "            \n",
    "            # Write the correlation value to the worksheet\n",
    "            cell = worksheet.cell(row=row+2, column=col+2)  # Adjust row and column indices for data cells\n",
    "            cell.value = cell_value\n",
    "    \n",
    "    # Define the output file path for the current material\n",
    "    output_file = os.path.join(output_directory, f\"correlation_matrix_{material}.xlsx\")\n",
    "    \n",
    "    # Save the workbook as an Excel file\n",
    "    workbook.save(output_file)\n",
    "\n",
    "print(\"Correlation files created in directory: \", output_directory)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "410b12ac",
   "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.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}