preprocessing.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preprocessing\n",
    "- Drop redundancies\n",
    "- Handing missing values\n",
    "- Encoding"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "dataset = pd.read_excel('pharma.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dataset.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = dataset.columns\n",
    "columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for column in columns:\n",
    "    print(f'{column}: {dataset[column].nunique()}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "top = dataset['Tỉnh'].value_counts()\n",
    "top.head(20)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Drop redundancies\n",
    "- Drop columns with vast categories (numbers of unique)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Case 1\n",
    "- mã đơn hàng, mã kh, tên kh, tên nhân viên: drop vì\n",
    "    - nunique lớn (nhiều thể loại)\n",
    "    - \n",
    "- Dơn giá không liên quan vì = số lượng x giá (giá có thể thay đổi nhưng drop để đơn giản trong TH này)\n",
    "- Tên thuốc dễ nhập sai -> giữ mã thuốc, bỏ tên thuốc, đơn vị\n",
    "- địa điểm: chi nhánh, Tỉnh, quận/huyện, xã/phường -> chọn tỉnh (drop còn lại)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "drop_list = ['Mã đơn hàng', 'Mã KH', 'Tên KH', 'Xã/phường', 'Quận/huyện', 'Tên nhân viên', 'Tên thuốc', 'Đơn vị', 'Đơn giá', 'Chi nhánh' ]\n",
    "df1 = dataset.drop(drop_list, axis=1)\n",
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Choose top 7 popular city:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "value_counts = df1['Tỉnh'].value_counts()\n",
    "value_counts.head(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the top 7 values\n",
    "top_values = value_counts.nlargest(7).index.tolist()\n",
    "\n",
    "# Create a boolean mask\n",
    "mask = df1['Tỉnh'].isin(top_values)\n",
    "\n",
    "# Select the rows that contain the top 7 values\n",
    "df_city = df1.loc[mask]\n",
    "df_city"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_city.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Select top popular drugs (8 in temporatory)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "drug_50 = df_city['Mã thuốc'].value_counts()\n",
    "top_50_drug = drug_50.head(50)\n",
    "# top_50_drug = drug_50\n",
    "\n",
    "for code, value in zip(list(top_50_drug.index), list(top_50_drug.values)):\n",
    "    entry = dataset.loc[dataset['Mã thuốc'] == code].iloc[0]\n",
    "    drug_name = entry['Tên thuốc']\n",
    "    print(f'Drug code: {code}, Name drug: {drug_name}, Count: {value}')\n",
    "    # print(f'{code},{drug_name},{value}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# in future will replace by 8 actual drugs\n",
    "drug_columns = list(top_50_drug.head(8).index)\n",
    "drug_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Keep drup in drug_columns and drop others\n",
    "\n",
    "# Create a boolean mask\n",
    "mask = df_city['Mã thuốc'].isin(drug_columns)\n",
    "\n",
    "# Dropping\n",
    "df_city = df_city.loc[mask]\n",
    "df_city"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for column in df_city.columns:\n",
    "    print(f'{column}: {df_city[column].nunique()}')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Visualize amount of drug**\n",
    "-  To reduce the vast number of few numbers in or to reduce\n",
    "over-fitting, data were processed before model training using log transformation methods.\n",
    "Quantity of the amount utilized were changed to float type. The modified numbers were\n",
    "returned to normal using exponential power, according to the prediction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "\n",
    "# get the 'Số lượng' column\n",
    "amount = df_city['Số lượng']\n",
    "\n",
    "# Add a small constant to avoid log(0)\n",
    "amount = amount + 1e-10\n",
    "\n",
    "# Filter out negative values\n",
    "amount = amount[amount > 0]\n",
    "\n",
    "log_amount = np.log(amount)\n",
    "log_amount.plot(kind='hist', bins=100, figsize = (8,6))\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter out any negative values (outliers)\n",
    "log_amount = log_amount[log_amount > 0.1]\n",
    "log_amount.plot(kind='hist', bins=100, figsize = (8,6))\n",
    "plt.show()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Drop outliers: frequency < 10^0.1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Log transform the 'income' column\n",
    "df_city['Số lượng'] = np.log(df_city['Số lượng'])\n",
    "\n",
    "# Define a threshold value\n",
    "threshold = 0.1\n",
    "\n",
    "# Drop any rows where the log-transformed 'Số lượng' value is less than the threshold\n",
    "df_city.drop(df_city[df_city['Số lượng'] < threshold].index, inplace=True)\n",
    "df_city.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save as csv for easy manipulate\n",
    "df_city.to_csv('pre1_pharma.csv', index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Choosing model"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Encoding\n",
    "- Time-feature\n",
    "- One hot: Tỉnh, Mã"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import data after log and preprocessing 1\n",
    "data_for_encode = pd.read_csv('pre1_pharma.csv', index_col='Ngày')\n",
    "data_for_encode"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test-train splitting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "# Separate target from predictors\n",
    "y = data_for_encode['Số lượng']\n",
    "X = data_for_encode.drop(['Số lượng'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Divide data into training and validation subsets\n",
    "X_train_full, X_valid_full, y_train, y_valid = train_test_split(X, y, train_size=0.8, test_size=0.2, random_state=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Linear regression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Random Forest"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ANN"
   ]
  },
  {
   "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.9.16"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}