test2_pure_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "pd.set_option('display.max_columns', 150)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('datas/transactions.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11193677"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# If same order_id products also have some same product_content_id, it means that customer bought different size of same item.\n",
    "# Most probably, customer was planning to try these products and check if it fits or not. So not fitted products are strong candidate for returning back.\n",
    "df['count'] = 1\n",
    "df['same_prod_dif_size_count'] = df.groupby(['order_parent_id','product_content_id'])['count'].transform('count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#last version prepared product dataframe\n",
    "df_prod = pd.read_pickle('df_prods_last_embed_v1.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1457567"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check all products are in product dataframe\n",
    "len(df.groupby(['product_content_id','product_variant_id'])) ,len(df_prod)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11193677"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#merged dataframe has : production,transaction informations\n",
    "df_prod_trans = pd.merge(df,df_prod,on=['product_content_id','product_variant_id'],how='inner')\n",
    "len(df_prod_trans)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For avoid memory problems\n",
    "del df\n",
    "del df_prod"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\doguc\\AppData\\Roaming\\Python\\Python39\\site-packages\\IPython\\core\\interactiveshell.py:3169: DtypeWarning: Columns (1) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  has_raised = await self.run_ast_nodes(code_ast.body, cell_name,\n"
     ]
    }
   ],
   "source": [
    "#prepared user dataframe\n",
    "df_user = pd.read_csv('datas/user_demographics.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11193677"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#merged dataframe has : production,transaction,user informations\n",
    "df_prod_trans_user = pd.merge(df_prod_trans,df_user,on=['user_id'],how='inner')\n",
    "len(df_prod_trans_user)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_prod_trans_user.to_pickle('df_prod_trans_user_v1.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11193677"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_prod_trans_user)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For avoid memory problems\n",
    "del df_prod_trans\n",
    "del df_user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#prepared review dataframe\n",
    "df_rew = pd.read_pickle('df_rew_groupby_prod_content_v3.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_rew = df_rew.drop(columns=['product_content_id']).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "#merged dataframe has : production,transaction,user,review informations\n",
    "df_prod_trans_user_rew = pd.merge(df_prod_trans_user,df_rew,on=['product_content_id'],how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11193677"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_prod_trans_user_rew)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For avoid memory problems\n",
    "del df_prod_trans_user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_prod_trans_user_rew.to_pickle('df_prod_trans_user_rew_v1.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_date</th>\n",
       "      <th>user_id</th>\n",
       "      <th>is_elite_user</th>\n",
       "      <th>supplier_id_x</th>\n",
       "      <th>order_line_item_id</th>\n",
       "      <th>order_parent_id</th>\n",
       "      <th>product_content_id</th>\n",
       "      <th>product_variant_id</th>\n",
       "      <th>original_price</th>\n",
       "      <th>discounted_price</th>\n",
       "      <th>ship_cost</th>\n",
       "      <th>coupon_id</th>\n",
       "      <th>coupon_discount</th>\n",
       "      <th>promotion_name</th>\n",
       "      <th>promotion_award_value</th>\n",
       "      <th>is_wallet_trx</th>\n",
       "      <th>is_saved_card_trx</th>\n",
       "      <th>is_returned</th>\n",
       "      <th>count</th>\n",
       "      <th>same_prod_dif_size_count</th>\n",
       "      <th>product_id</th>\n",
       "      <th>brand_id</th>\n",
       "      <th>gender_id</th>\n",
       "      <th>category_id</th>\n",
       "      <th>color_id</th>\n",
       "      <th>attribute_value</th>\n",
       "      <th>embed_prod_name</th>\n",
       "      <th>embed_brand_name</th>\n",
       "      <th>0_x</th>\n",
       "      <th>1_x</th>\n",
       "      <th>2_x</th>\n",
       "      <th>3_x</th>\n",
       "      <th>4_x</th>\n",
       "      <th>5_x</th>\n",
       "      <th>6_x</th>\n",
       "      <th>7_x</th>\n",
       "      <th>8_x</th>\n",
       "      <th>9_x</th>\n",
       "      <th>10_x</th>\n",
       "      <th>11_x</th>\n",
       "      <th>12_x</th>\n",
       "      <th>13_x</th>\n",
       "      <th>14_x</th>\n",
       "      <th>15_x</th>\n",
       "      <th>16_x</th>\n",
       "      <th>17_x</th>\n",
       "      <th>18_x</th>\n",
       "      <th>19_x</th>\n",
       "      <th>0_x</th>\n",
       "      <th>1_x</th>\n",
       "      <th>2_x</th>\n",
       "      <th>3_x</th>\n",
       "      <th>4_x</th>\n",
       "      <th>5_x</th>\n",
       "      <th>6_x</th>\n",
       "      <th>7_x</th>\n",
       "      <th>8_x</th>\n",
       "      <th>9_x</th>\n",
       "      <th>10_x</th>\n",
       "      <th>11_x</th>\n",
       "      <th>12_x</th>\n",
       "      <th>13_x</th>\n",
       "      <th>14_x</th>\n",
       "      <th>15_x</th>\n",
       "      <th>16_x</th>\n",
       "      <th>17_x</th>\n",
       "      <th>18_x</th>\n",
       "      <th>19_x</th>\n",
       "      <th>birth_date</th>\n",
       "      <th>membership_date</th>\n",
       "      <th>gender</th>\n",
       "      <th>rate</th>\n",
       "      <th>review_like_count</th>\n",
       "      <th>supplier_id_y</th>\n",
       "      <th>0_y</th>\n",
       "      <th>1_y</th>\n",
       "      <th>2_y</th>\n",
       "      <th>3_y</th>\n",
       "      <th>4_y</th>\n",
       "      <th>5_y</th>\n",
       "      <th>6_y</th>\n",
       "      <th>7_y</th>\n",
       "      <th>8_y</th>\n",
       "      <th>9_y</th>\n",
       "      <th>10_y</th>\n",
       "      <th>11_y</th>\n",
       "      <th>12_y</th>\n",
       "      <th>13_y</th>\n",
       "      <th>14_y</th>\n",
       "      <th>15_y</th>\n",
       "      <th>16_y</th>\n",
       "      <th>17_y</th>\n",
       "      <th>18_y</th>\n",
       "      <th>19_y</th>\n",
       "      <th>20</th>\n",
       "      <th>21</th>\n",
       "      <th>22</th>\n",
       "      <th>23</th>\n",
       "      <th>24</th>\n",
       "      <th>25</th>\n",
       "      <th>26</th>\n",
       "      <th>27</th>\n",
       "      <th>28</th>\n",
       "      <th>29</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-05-01 00:00:01.761000+00:00</td>\n",
       "      <td>3941815</td>\n",
       "      <td>0</td>\n",
       "      <td>4662</td>\n",
       "      <td>901280147</td>\n",
       "      <td>595758968</td>\n",
       "      <td>95503167</td>\n",
       "      <td>159147197</td>\n",
       "      <td>79.9</td>\n",
       "      <td>51.94</td>\n",
       "      <td>8.88</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Sepette %35 İndirim</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0</td>\n",
       "      <td>True</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>82687030</td>\n",
       "      <td>148</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>2</td>\n",
       "      <td>1180</td>\n",
       "      <td>[-1.2849286, -1.2541153, -1.782976, -0.1273798...</td>\n",
       "      <td>[-0.023970377, -0.021582747, -0.024002993, -0....</td>\n",
       "      <td>-0.02397</td>\n",
       "      <td>-0.021583</td>\n",
       "      <td>-0.024003</td>\n",
       "      <td>-0.04902</td>\n",
       "      <td>-0.003887</td>\n",
       "      <td>-0.038962</td>\n",
       "      <td>-0.02406</td>\n",
       "      <td>-0.036875</td>\n",
       "      <td>0.049678</td>\n",
       "      <td>-0.006779</td>\n",
       "      <td>-0.038771</td>\n",
       "      <td>0.027583</td>\n",
       "      <td>-0.040332</td>\n",
       "      <td>-0.009561</td>\n",
       "      <td>0.013677</td>\n",
       "      <td>-0.035396</td>\n",
       "      <td>-0.043668</td>\n",
       "      <td>-0.033208</td>\n",
       "      <td>0.037342</td>\n",
       "      <td>0.045288</td>\n",
       "      <td>-1.284929</td>\n",
       "      <td>-1.254115</td>\n",
       "      <td>-1.782976</td>\n",
       "      <td>-0.12738</td>\n",
       "      <td>1.924536</td>\n",
       "      <td>0.055261</td>\n",
       "      <td>1.629931</td>\n",
       "      <td>-2.285416</td>\n",
       "      <td>-1.703261</td>\n",
       "      <td>-0.308678</td>\n",
       "      <td>1.169102</td>\n",
       "      <td>0.600538</td>\n",
       "      <td>-0.844449</td>\n",
       "      <td>-0.319412</td>\n",
       "      <td>-1.381115</td>\n",
       "      <td>-1.230004</td>\n",
       "      <td>-0.166193</td>\n",
       "      <td>0.662455</td>\n",
       "      <td>1.057862</td>\n",
       "      <td>-0.365327</td>\n",
       "      <td>1979-05-24 00:00:00+00:00</td>\n",
       "      <td>2011-04-07 14:55:39.457000+00:00</td>\n",
       "      <td>Bayan</td>\n",
       "      <td>3.25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4662.0</td>\n",
       "      <td>-0.033843</td>\n",
       "      <td>-1.833734</td>\n",
       "      <td>-0.818248</td>\n",
       "      <td>-1.779563</td>\n",
       "      <td>-2.179461</td>\n",
       "      <td>-1.678068</td>\n",
       "      <td>-2.464959</td>\n",
       "      <td>0.605734</td>\n",
       "      <td>1.051787</td>\n",
       "      <td>0.692738</td>\n",
       "      <td>0.821323</td>\n",
       "      <td>-1.415752</td>\n",
       "      <td>1.587257</td>\n",
       "      <td>-0.264496</td>\n",
       "      <td>-1.374641</td>\n",
       "      <td>-0.596889</td>\n",
       "      <td>-0.273802</td>\n",
       "      <td>-2.635564</td>\n",
       "      <td>0.677161</td>\n",
       "      <td>-0.516057</td>\n",
       "      <td>1.706807</td>\n",
       "      <td>-0.245808</td>\n",
       "      <td>-0.395436</td>\n",
       "      <td>2.461663</td>\n",
       "      <td>-1.903484</td>\n",
       "      <td>0.331082</td>\n",
       "      <td>0.646957</td>\n",
       "      <td>-0.395696</td>\n",
       "      <td>-0.52306</td>\n",
       "      <td>-0.745091</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         order_date  user_id  is_elite_user  supplier_id_x  \\\n",
       "0  2021-05-01 00:00:01.761000+00:00  3941815              0           4662   \n",
       "\n",
       "   order_line_item_id  order_parent_id  product_content_id  \\\n",
       "0           901280147        595758968            95503167   \n",
       "\n",
       "   product_variant_id  original_price  discounted_price  ship_cost coupon_id  \\\n",
       "0           159147197            79.9             51.94       8.88       NaN   \n",
       "\n",
       "   coupon_discount       promotion_name  promotion_award_value  is_wallet_trx  \\\n",
       "0              0.0  Sepette %35 İndirim                   35.0              0   \n",
       "\n",
       "   is_saved_card_trx  is_returned  count  same_prod_dif_size_count  \\\n",
       "0               True          0.0      1                         1   \n",
       "\n",
       "   product_id  brand_id  gender_id  category_id  color_id  attribute_value  \\\n",
       "0    82687030       148          0           50         2             1180   \n",
       "\n",
       "                                     embed_prod_name  \\\n",
       "0  [-1.2849286, -1.2541153, -1.782976, -0.1273798...   \n",
       "\n",
       "                                    embed_brand_name      0_x       1_x  \\\n",
       "0  [-0.023970377, -0.021582747, -0.024002993, -0.... -0.02397 -0.021583   \n",
       "\n",
       "        2_x      3_x       4_x       5_x      6_x       7_x       8_x  \\\n",
       "0 -0.024003 -0.04902 -0.003887 -0.038962 -0.02406 -0.036875  0.049678   \n",
       "\n",
       "        9_x      10_x      11_x      12_x      13_x      14_x      15_x  \\\n",
       "0 -0.006779 -0.038771  0.027583 -0.040332 -0.009561  0.013677 -0.035396   \n",
       "\n",
       "       16_x      17_x      18_x      19_x       0_x       1_x       2_x  \\\n",
       "0 -0.043668 -0.033208  0.037342  0.045288 -1.284929 -1.254115 -1.782976   \n",
       "\n",
       "       3_x       4_x       5_x       6_x       7_x       8_x       9_x  \\\n",
       "0 -0.12738  1.924536  0.055261  1.629931 -2.285416 -1.703261 -0.308678   \n",
       "\n",
       "       10_x      11_x      12_x      13_x      14_x      15_x      16_x  \\\n",
       "0  1.169102  0.600538 -0.844449 -0.319412 -1.381115 -1.230004 -0.166193   \n",
       "\n",
       "       17_x      18_x      19_x                 birth_date  \\\n",
       "0  0.662455  1.057862 -0.365327  1979-05-24 00:00:00+00:00   \n",
       "\n",
       "                    membership_date gender  rate  review_like_count  \\\n",
       "0  2011-04-07 14:55:39.457000+00:00  Bayan  3.25                0.0   \n",
       "\n",
       "   supplier_id_y       0_y       1_y       2_y       3_y       4_y       5_y  \\\n",
       "0         4662.0 -0.033843 -1.833734 -0.818248 -1.779563 -2.179461 -1.678068   \n",
       "\n",
       "        6_y       7_y       8_y       9_y      10_y      11_y      12_y  \\\n",
       "0 -2.464959  0.605734  1.051787  0.692738  0.821323 -1.415752  1.587257   \n",
       "\n",
       "       13_y      14_y      15_y      16_y      17_y      18_y      19_y  \\\n",
       "0 -0.264496 -1.374641 -0.596889 -0.273802 -2.635564  0.677161 -0.516057   \n",
       "\n",
       "         20        21        22        23        24        25        26  \\\n",
       "0  1.706807 -0.245808 -0.395436  2.461663 -1.903484  0.331082  0.646957   \n",
       "\n",
       "         27       28        29  \n",
       "0 -0.395696 -0.52306 -0.745091  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_prod_trans_user_rew.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "#boolean column is converted to integer format.\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "encoder = LabelEncoder()\n",
    "df_prod_trans_user_rew['is_saved_card_trx'] = encoder.fit_transform(df_prod_trans_user_rew.is_saved_card_trx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_prod_trans_user_rew.to_pickle('df_prod_trans_user_rew_v2.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For avoid memory problems\n",
    "del df_rew"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "pd.set_option('display.max_columns', 150)\n",
    "#pd.set_option('display.max_rows', 500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_main_concat = pd.read_pickle('df_prod_trans_user_rew_v2.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_date</th>\n",
       "      <th>user_id</th>\n",
       "      <th>is_elite_user</th>\n",
       "      <th>supplier_id_x</th>\n",
       "      <th>order_line_item_id</th>\n",
       "      <th>order_parent_id</th>\n",
       "      <th>product_content_id</th>\n",
       "      <th>product_variant_id</th>\n",
       "      <th>original_price</th>\n",
       "      <th>discounted_price</th>\n",
       "      <th>ship_cost</th>\n",
       "      <th>coupon_id</th>\n",
       "      <th>coupon_discount</th>\n",
       "      <th>promotion_name</th>\n",
       "      <th>promotion_award_value</th>\n",
       "      <th>is_wallet_trx</th>\n",
       "      <th>is_saved_card_trx</th>\n",
       "      <th>is_returned</th>\n",
       "      <th>count</th>\n",
       "      <th>same_prod_dif_size_count</th>\n",
       "      <th>product_id</th>\n",
       "      <th>brand_id</th>\n",
       "      <th>gender_id</th>\n",
       "      <th>category_id</th>\n",
       "      <th>color_id</th>\n",
       "      <th>attribute_value</th>\n",
       "      <th>embed_prod_name</th>\n",
       "      <th>embed_brand_name</th>\n",
       "      <th>0_x</th>\n",
       "      <th>1_x</th>\n",
       "      <th>2_x</th>\n",
       "      <th>3_x</th>\n",
       "      <th>4_x</th>\n",
       "      <th>5_x</th>\n",
       "      <th>6_x</th>\n",
       "      <th>7_x</th>\n",
       "      <th>8_x</th>\n",
       "      <th>9_x</th>\n",
       "      <th>10_x</th>\n",
       "      <th>11_x</th>\n",
       "      <th>12_x</th>\n",
       "      <th>13_x</th>\n",
       "      <th>14_x</th>\n",
       "      <th>15_x</th>\n",
       "      <th>16_x</th>\n",
       "      <th>17_x</th>\n",
       "      <th>18_x</th>\n",
       "      <th>19_x</th>\n",
       "      <th>0_x</th>\n",
       "      <th>1_x</th>\n",
       "      <th>2_x</th>\n",
       "      <th>3_x</th>\n",
       "      <th>4_x</th>\n",
       "      <th>5_x</th>\n",
       "      <th>6_x</th>\n",
       "      <th>7_x</th>\n",
       "      <th>8_x</th>\n",
       "      <th>9_x</th>\n",
       "      <th>10_x</th>\n",
       "      <th>11_x</th>\n",
       "      <th>12_x</th>\n",
       "      <th>13_x</th>\n",
       "      <th>14_x</th>\n",
       "      <th>15_x</th>\n",
       "      <th>16_x</th>\n",
       "      <th>17_x</th>\n",
       "      <th>18_x</th>\n",
       "      <th>19_x</th>\n",
       "      <th>birth_date</th>\n",
       "      <th>membership_date</th>\n",
       "      <th>gender</th>\n",
       "      <th>rate</th>\n",
       "      <th>review_like_count</th>\n",
       "      <th>supplier_id_y</th>\n",
       "      <th>0_y</th>\n",
       "      <th>1_y</th>\n",
       "      <th>2_y</th>\n",
       "      <th>3_y</th>\n",
       "      <th>4_y</th>\n",
       "      <th>5_y</th>\n",
       "      <th>6_y</th>\n",
       "      <th>7_y</th>\n",
       "      <th>8_y</th>\n",
       "      <th>9_y</th>\n",
       "      <th>10_y</th>\n",
       "      <th>11_y</th>\n",
       "      <th>12_y</th>\n",
       "      <th>13_y</th>\n",
       "      <th>14_y</th>\n",
       "      <th>15_y</th>\n",
       "      <th>16_y</th>\n",
       "      <th>17_y</th>\n",
       "      <th>18_y</th>\n",
       "      <th>19_y</th>\n",
       "      <th>20</th>\n",
       "      <th>21</th>\n",
       "      <th>22</th>\n",
       "      <th>23</th>\n",
       "      <th>24</th>\n",
       "      <th>25</th>\n",
       "      <th>26</th>\n",
       "      <th>27</th>\n",
       "      <th>28</th>\n",
       "      <th>29</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-05-01 00:00:01.761000+00:00</td>\n",
       "      <td>3941815</td>\n",
       "      <td>0</td>\n",
       "      <td>4662</td>\n",
       "      <td>901280147</td>\n",
       "      <td>595758968</td>\n",
       "      <td>95503167</td>\n",
       "      <td>159147197</td>\n",
       "      <td>79.9</td>\n",
       "      <td>51.94</td>\n",
       "      <td>8.88</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Sepette %35 İndirim</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>82687030</td>\n",
       "      <td>148</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>2</td>\n",
       "      <td>1180</td>\n",
       "      <td>[-1.2849286, -1.2541153, -1.782976, -0.1273798...</td>\n",
       "      <td>[-0.023970377, -0.021582747, -0.024002993, -0....</td>\n",
       "      <td>-0.02397</td>\n",
       "      <td>-0.021583</td>\n",
       "      <td>-0.024003</td>\n",
       "      <td>-0.04902</td>\n",
       "      <td>-0.003887</td>\n",
       "      <td>-0.038962</td>\n",
       "      <td>-0.02406</td>\n",
       "      <td>-0.036875</td>\n",
       "      <td>0.049678</td>\n",
       "      <td>-0.006779</td>\n",
       "      <td>-0.038771</td>\n",
       "      <td>0.027583</td>\n",
       "      <td>-0.040332</td>\n",
       "      <td>-0.009561</td>\n",
       "      <td>0.013677</td>\n",
       "      <td>-0.035396</td>\n",
       "      <td>-0.043668</td>\n",
       "      <td>-0.033208</td>\n",
       "      <td>0.037342</td>\n",
       "      <td>0.045288</td>\n",
       "      <td>-1.284929</td>\n",
       "      <td>-1.254115</td>\n",
       "      <td>-1.782976</td>\n",
       "      <td>-0.12738</td>\n",
       "      <td>1.924536</td>\n",
       "      <td>0.055261</td>\n",
       "      <td>1.629931</td>\n",
       "      <td>-2.285416</td>\n",
       "      <td>-1.703261</td>\n",
       "      <td>-0.308678</td>\n",
       "      <td>1.169102</td>\n",
       "      <td>0.600538</td>\n",
       "      <td>-0.844449</td>\n",
       "      <td>-0.319412</td>\n",
       "      <td>-1.381115</td>\n",
       "      <td>-1.230004</td>\n",
       "      <td>-0.166193</td>\n",
       "      <td>0.662455</td>\n",
       "      <td>1.057862</td>\n",
       "      <td>-0.365327</td>\n",
       "      <td>1979-05-24 00:00:00+00:00</td>\n",
       "      <td>2011-04-07 14:55:39.457000+00:00</td>\n",
       "      <td>Bayan</td>\n",
       "      <td>3.25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4662.0</td>\n",
       "      <td>-0.033843</td>\n",
       "      <td>-1.833734</td>\n",
       "      <td>-0.818248</td>\n",
       "      <td>-1.779563</td>\n",
       "      <td>-2.179461</td>\n",
       "      <td>-1.678068</td>\n",
       "      <td>-2.464959</td>\n",
       "      <td>0.605734</td>\n",
       "      <td>1.051787</td>\n",
       "      <td>0.692738</td>\n",
       "      <td>0.821323</td>\n",
       "      <td>-1.415752</td>\n",
       "      <td>1.587257</td>\n",
       "      <td>-0.264496</td>\n",
       "      <td>-1.374641</td>\n",
       "      <td>-0.596889</td>\n",
       "      <td>-0.273802</td>\n",
       "      <td>-2.635564</td>\n",
       "      <td>0.677161</td>\n",
       "      <td>-0.516057</td>\n",
       "      <td>1.706807</td>\n",
       "      <td>-0.245808</td>\n",
       "      <td>-0.395436</td>\n",
       "      <td>2.461663</td>\n",
       "      <td>-1.903484</td>\n",
       "      <td>0.331082</td>\n",
       "      <td>0.646957</td>\n",
       "      <td>-0.395696</td>\n",
       "      <td>-0.52306</td>\n",
       "      <td>-0.745091</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         order_date  user_id  is_elite_user  supplier_id_x  \\\n",
       "0  2021-05-01 00:00:01.761000+00:00  3941815              0           4662   \n",
       "\n",
       "   order_line_item_id  order_parent_id  product_content_id  \\\n",
       "0           901280147        595758968            95503167   \n",
       "\n",
       "   product_variant_id  original_price  discounted_price  ship_cost coupon_id  \\\n",
       "0           159147197            79.9             51.94       8.88       NaN   \n",
       "\n",
       "   coupon_discount       promotion_name  promotion_award_value  is_wallet_trx  \\\n",
       "0              0.0  Sepette %35 İndirim                   35.0              0   \n",
       "\n",
       "   is_saved_card_trx  is_returned  count  same_prod_dif_size_count  \\\n",
       "0                  1          0.0      1                         1   \n",
       "\n",
       "   product_id  brand_id  gender_id  category_id  color_id  attribute_value  \\\n",
       "0    82687030       148          0           50         2             1180   \n",
       "\n",
       "                                     embed_prod_name  \\\n",
       "0  [-1.2849286, -1.2541153, -1.782976, -0.1273798...   \n",
       "\n",
       "                                    embed_brand_name      0_x       1_x  \\\n",
       "0  [-0.023970377, -0.021582747, -0.024002993, -0.... -0.02397 -0.021583   \n",
       "\n",
       "        2_x      3_x       4_x       5_x      6_x       7_x       8_x  \\\n",
       "0 -0.024003 -0.04902 -0.003887 -0.038962 -0.02406 -0.036875  0.049678   \n",
       "\n",
       "        9_x      10_x      11_x      12_x      13_x      14_x      15_x  \\\n",
       "0 -0.006779 -0.038771  0.027583 -0.040332 -0.009561  0.013677 -0.035396   \n",
       "\n",
       "       16_x      17_x      18_x      19_x       0_x       1_x       2_x  \\\n",
       "0 -0.043668 -0.033208  0.037342  0.045288 -1.284929 -1.254115 -1.782976   \n",
       "\n",
       "       3_x       4_x       5_x       6_x       7_x       8_x       9_x  \\\n",
       "0 -0.12738  1.924536  0.055261  1.629931 -2.285416 -1.703261 -0.308678   \n",
       "\n",
       "       10_x      11_x      12_x      13_x      14_x      15_x      16_x  \\\n",
       "0  1.169102  0.600538 -0.844449 -0.319412 -1.381115 -1.230004 -0.166193   \n",
       "\n",
       "       17_x      18_x      19_x                 birth_date  \\\n",
       "0  0.662455  1.057862 -0.365327  1979-05-24 00:00:00+00:00   \n",
       "\n",
       "                    membership_date gender  rate  review_like_count  \\\n",
       "0  2011-04-07 14:55:39.457000+00:00  Bayan  3.25                0.0   \n",
       "\n",
       "   supplier_id_y       0_y       1_y       2_y       3_y       4_y       5_y  \\\n",
       "0         4662.0 -0.033843 -1.833734 -0.818248 -1.779563 -2.179461 -1.678068   \n",
       "\n",
       "        6_y       7_y       8_y       9_y      10_y      11_y      12_y  \\\n",
       "0 -2.464959  0.605734  1.051787  0.692738  0.821323 -1.415752  1.587257   \n",
       "\n",
       "       13_y      14_y      15_y      16_y      17_y      18_y      19_y  \\\n",
       "0 -0.264496 -1.374641 -0.596889 -0.273802 -2.635564  0.677161 -0.516057   \n",
       "\n",
       "         20        21        22        23        24        25        26  \\\n",
       "0  1.706807 -0.245808 -0.395436  2.461663 -1.903484  0.331082  0.646957   \n",
       "\n",
       "         27       28        29  \n",
       "0 -0.395696 -0.52306 -0.745091  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_main_concat.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# In this part, supplier rates are added as columns into main dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_main_concat.rename(columns={'supplier_id_x':'supplier_id'},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_main_concat = df_main_concat.drop(columns=['supplier_id_y','gender','order_date','user_id' ,'order_line_item_id' ,'coupon_id' ,'promotion_name' ,'product_id' ,'embed_prod_name'  ,'embed_brand_name','birth_date','membership_date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sup_ret = pd.read_csv('datas/supplier_return.csv')\n",
    "df_sup_def = pd.read_csv('datas/supplier_defective_return.csv')\n",
    "df_sup_dis = pd.read_csv('datas/supplier_disputed_return.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sup_ret.rename(columns={'returnRate':'returnRate_ret'},inplace=True)\n",
    "df_sup_def.rename(columns={'returnRate':'returnRate_def'},inplace=True)\n",
    "df_sup_dis.rename(columns={'returnRate':'returnRate_dis'},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>supplier_id</th>\n",
       "      <th>returnRate_ret</th>\n",
       "      <th>returnRate_def</th>\n",
       "      <th>total_claim</th>\n",
       "      <th>unresolved_claim</th>\n",
       "      <th>unresolved_accepted_claim</th>\n",
       "      <th>unresolvedclaim_percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>247019</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>185253</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>193806</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>113616</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>345124</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   supplier_id  returnRate_ret  returnRate_def  total_claim  unresolved_claim  \\\n",
       "0       247019             0.0             0.0            1                 0   \n",
       "1       185253             0.0             0.0            1                 0   \n",
       "2       193806             0.0             0.0            2                 0   \n",
       "3       113616             0.0             0.0            1                 0   \n",
       "4       345124             0.0             0.0            1                 0   \n",
       "\n",
       "   unresolved_accepted_claim  unresolvedclaim_percentage  \n",
       "0                          0                         0.0  \n",
       "1                          0                         0.0  \n",
       "2                          0                         0.0  \n",
       "3                          0                         0.0  \n",
       "4                          0                         0.0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#All suppliers dataframes are merged.\n",
    "df_ret_def = pd.merge(df_sup_ret,df_sup_def,on='supplier_id',how='inner')\n",
    "df_ret_def_dis = pd.merge(df_ret_def,df_sup_dis,on='supplier_id',how='inner')\n",
    "df_ret_def_dis.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_main_concat = pd.merge(df_main_concat,df_ret_def_dis,on=['supplier_id'],how='inner')"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "ef7cd2e160683df5bc50936fdc15f17e8ae0d292a9877125d3322640160a1ab5"
  },
  "kernelspec": {
   "display_name": "Python 3.9.7 64-bit",
   "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.7"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}