Data preparation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Preparing data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5"
   },
   "outputs": [],
   "source": [
    "import sys\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import scipy.sparse as sparse\n",
    "from scipy.sparse.linalg import spsolve\n",
    "import random\n",
    "import os\n",
    "import scipy.stats as ss\n",
    "import scipy\n",
    "from sklearn.preprocessing import MinMaxScaler\n",
    "\n",
    "import implicit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
    "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a"
   },
   "outputs": [],
   "source": [
    "df_train = pd.read_csv(\"../input/train.csv\", low_memory=None, header=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train.columns = df_train.iloc[2713723]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train.drop(df_train.index[2713723], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train.drop(['deleted', 'deleter_fk'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = df_train[~df_train['receipt_mfc'].str.match('^(?![0123456789])')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = df_train[~df_train['order_number'].str.match('^(?![0123456789])')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = df_train[~df_train['mfc'].str.match('^(?![0123456789])')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train['service'] = df_train['service'].astype('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train['cpgu_user'] = df_train['cpgu_user'].astype('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train['mfc'] = df_train['mfc'].astype('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train.drop(['receipt_mfc', 'creation_mfc', 'issue_date', 'close_date', 'id_x', 'id_y', 'id', 'cpgu_user'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_subdep(x):\n",
    "    if x['sub_department'] is not np.nan:\n",
    "        return 1\n",
    "    return 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train['is_subdep'] = df_train.apply(get_subdep, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train.drop(['sub_department'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_csid(x):\n",
    "    if x['custom_service_id'] is not np.nan:\n",
    "        return 1\n",
    "    return 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train['is_csid'] = df_train.apply(get_csid, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train['custom_service_id'].fillna(-1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train['win_count'].fillna(df_train['win_count'].mean(), inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train['order_date'] = df_train['order_date'].apply(pd.to_datetime)\n",
    "df_train['change_timestamp'] = df_train['change_timestamp'].apply(pd.to_datetime)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train['proc_time'] = (df_train['change_timestamp']-df_train['order_date'])/np.timedelta64(1,'h')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_time(x):\n",
    "    if x['proc_time'] < 0:\n",
    "        return 0\n",
    "    return x['proc_time']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train['proc_time'] = df_train.apply(get_time, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train['dayofweek'] = df_train['order_date'].dt.dayofweek"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_hour(x):\n",
    "    h = x['order_date'].hour\n",
    "    if h>=5 and h<11:\n",
    "        return 'morning'\n",
    "    elif h>=11 and h<17:\n",
    "        return 'lunch'\n",
    "    elif h>=17 and h<23:\n",
    "        return 'evening'\n",
    "    return 'night'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train['day_part'] = df_train.apply(get_hour, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train.to_csv('clean_train.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "service = pd.read_csv(\"../input/cpgu_service.csv\", sep=';', encoding='latin-1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = pd.merge(df_train, service[['id', 'person', 'sole', 'legal']], how='left', left_on = 'service', right_on = 'id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "cpgu_user = pd.read_csv(\"../input/cpgu_user.csv\", sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = pd.merge(df_train, cpgu_user, how='left', left_on = 'cpgu_user', right_on = 'id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "cpgu_mfc = pd.read_csv(\"../input/cpgu_mfc.csv\", sep=';', encoding='latin-1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = pd.merge(df_train, cpgu_mfc[['id', 'win_count']], how='left', left_on = 'mfc', right_on = 'id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train.to_csv('clean_train.csv', index=False)"
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}