parse_train_set.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "63e4728a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "import time\n",
    "import json\n",
    "from math import radians, cos, sin, asin, sqrt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1c824f71",
   "metadata": {},
   "outputs": [],
   "source": [
    "def lldis(lat1, lat2, lon1, lon2):\n",
    "     \n",
    "    # The math module contains a function named\n",
    "    # radians which converts from degrees to radians.\n",
    "    lon1 = radians(lon1)\n",
    "    lon2 = radians(lon2)\n",
    "    lat1 = radians(lat1)\n",
    "    lat2 = radians(lat2)\n",
    "      \n",
    "    # Haversine formula\n",
    "    dlon = lon2 - lon1\n",
    "    dlat = lat2 - lat1\n",
    "    a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlon / 2)**2\n",
    " \n",
    "    c = 2 * asin(sqrt(a))\n",
    "    \n",
    "    # Radius of earth in kilometers. Use 3956 for miles\n",
    "    r = 6371\n",
    "      \n",
    "    # calculate the result\n",
    "    return(c * r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f02f3521",
   "metadata": {},
   "outputs": [],
   "source": [
    "raw = pd.read_csv('data/train.tsv', sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "20eed045",
   "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>b2c_c2c</th>\n",
       "      <th>seller_id</th>\n",
       "      <th>declared_handling_days</th>\n",
       "      <th>acceptance_scan_timestamp</th>\n",
       "      <th>shipment_method_id</th>\n",
       "      <th>shipping_fee</th>\n",
       "      <th>carrier_min_estimate</th>\n",
       "      <th>carrier_max_estimate</th>\n",
       "      <th>item_zip</th>\n",
       "      <th>buyer_zip</th>\n",
       "      <th>category_id</th>\n",
       "      <th>item_price</th>\n",
       "      <th>quantity</th>\n",
       "      <th>payment_datetime</th>\n",
       "      <th>delivery_date</th>\n",
       "      <th>weight</th>\n",
       "      <th>weight_units</th>\n",
       "      <th>package_size</th>\n",
       "      <th>record_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B2C</td>\n",
       "      <td>25454</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2019-03-26 15:11:00.000-07:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>97219</td>\n",
       "      <td>49040</td>\n",
       "      <td>13</td>\n",
       "      <td>27.95</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-03-24 03:56:49.000-07:00</td>\n",
       "      <td>2019-03-29</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>LETTER</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>C2C</td>\n",
       "      <td>6727381</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2018-06-02 12:53:00.000-07:00</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>11415-3528</td>\n",
       "      <td>62521</td>\n",
       "      <td>0</td>\n",
       "      <td>20.50</td>\n",
       "      <td>1</td>\n",
       "      <td>2018-06-01 13:43:54.000-07:00</td>\n",
       "      <td>2018-06-05</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>PACKAGE_THICK_ENVELOPE</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B2C</td>\n",
       "      <td>18507</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2019-01-07 16:22:00.000-05:00</td>\n",
       "      <td>0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>27292</td>\n",
       "      <td>53010</td>\n",
       "      <td>1</td>\n",
       "      <td>19.90</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-06 00:02:00.000-05:00</td>\n",
       "      <td>2019-01-10</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>PACKAGE_THICK_ENVELOPE</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B2C</td>\n",
       "      <td>4677</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2018-12-17 16:56:00.000-08:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>90703</td>\n",
       "      <td>80022</td>\n",
       "      <td>1</td>\n",
       "      <td>35.50</td>\n",
       "      <td>1</td>\n",
       "      <td>2018-12-16 10:28:28.000-08:00</td>\n",
       "      <td>2018-12-21</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>PACKAGE_THICK_ENVELOPE</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B2C</td>\n",
       "      <td>4677</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2018-07-27 16:48:00.000-07:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>90703</td>\n",
       "      <td>55070</td>\n",
       "      <td>1</td>\n",
       "      <td>25.00</td>\n",
       "      <td>1</td>\n",
       "      <td>2018-07-26 18:20:02.000-07:00</td>\n",
       "      <td>2018-07-30</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>PACKAGE_THICK_ENVELOPE</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  b2c_c2c  seller_id  declared_handling_days      acceptance_scan_timestamp  \\\n",
       "0     B2C      25454                     3.0  2019-03-26 15:11:00.000-07:00   \n",
       "1     C2C    6727381                     2.0  2018-06-02 12:53:00.000-07:00   \n",
       "2     B2C      18507                     1.0  2019-01-07 16:22:00.000-05:00   \n",
       "3     B2C       4677                     1.0  2018-12-17 16:56:00.000-08:00   \n",
       "4     B2C       4677                     1.0  2018-07-27 16:48:00.000-07:00   \n",
       "\n",
       "   shipment_method_id  shipping_fee  carrier_min_estimate  \\\n",
       "0                   0           0.0                     3   \n",
       "1                   0           3.0                     3   \n",
       "2                   0           4.5                     3   \n",
       "3                   0           0.0                     3   \n",
       "4                   0           0.0                     3   \n",
       "\n",
       "   carrier_max_estimate    item_zip buyer_zip  category_id  item_price  \\\n",
       "0                     5       97219     49040           13       27.95   \n",
       "1                     5  11415-3528     62521            0       20.50   \n",
       "2                     5       27292     53010            1       19.90   \n",
       "3                     5       90703     80022            1       35.50   \n",
       "4                     5       90703     55070            1       25.00   \n",
       "\n",
       "   quantity               payment_datetime delivery_date  weight  \\\n",
       "0         1  2019-03-24 03:56:49.000-07:00    2019-03-29       5   \n",
       "1         1  2018-06-01 13:43:54.000-07:00    2018-06-05       0   \n",
       "2         1  2019-01-06 00:02:00.000-05:00    2019-01-10       9   \n",
       "3         1  2018-12-16 10:28:28.000-08:00    2018-12-21       8   \n",
       "4         1  2018-07-26 18:20:02.000-07:00    2018-07-30       3   \n",
       "\n",
       "   weight_units            package_size  record_number  \n",
       "0             1                  LETTER              1  \n",
       "1             1  PACKAGE_THICK_ENVELOPE              2  \n",
       "2             1  PACKAGE_THICK_ENVELOPE              3  \n",
       "3             1  PACKAGE_THICK_ENVELOPE              4  \n",
       "4             1  PACKAGE_THICK_ENVELOPE              5  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f48414e6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     2544488\n",
       "1     1293227\n",
       "2     1281052\n",
       "3     1233523\n",
       "4      931638\n",
       "5      787817\n",
       "6      772039\n",
       "7      720987\n",
       "8      569545\n",
       "10     543662\n",
       "9      538951\n",
       "11     445998\n",
       "12     434892\n",
       "13     429443\n",
       "14     366025\n",
       "15     320721\n",
       "16     303283\n",
       "17     257026\n",
       "19     239080\n",
       "18     215859\n",
       "20     125426\n",
       "22     102039\n",
       "21      97935\n",
       "23      94685\n",
       "24      69860\n",
       "25      65579\n",
       "26      64628\n",
       "27      39559\n",
       "28      38358\n",
       "29      32863\n",
       "30      28701\n",
       "31       9216\n",
       "32       1895\n",
       "Name: category_id, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw['category_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "73d354e2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1      14216142\n",
       "2        522973\n",
       "3        116993\n",
       "4         61183\n",
       "5         29698\n",
       "         ...   \n",
       "305           1\n",
       "109           1\n",
       "148           1\n",
       "79            1\n",
       "175           1\n",
       "Name: quantity, Length: 147, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw['quantity'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "74257a8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "parsed = raw[['record_number', 'shipment_method_id', 'shipping_fee', 'carrier_min_estimate', 'carrier_max_estimate', 'category_id', 'item_price', 'quantity']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f628085c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(15000000, 8)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parsed.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "72ffff28",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_5123/1629042217.py:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  parsed['bt'] = raw['b2c_c2c'].map(dict1)\n"
     ]
    },
    {
     "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>record_number</th>\n",
       "      <th>shipment_method_id</th>\n",
       "      <th>shipping_fee</th>\n",
       "      <th>carrier_min_estimate</th>\n",
       "      <th>carrier_max_estimate</th>\n",
       "      <th>category_id</th>\n",
       "      <th>item_price</th>\n",
       "      <th>quantity</th>\n",
       "      <th>bt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "      <td>27.95</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>20.50</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>4.50</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>19.90</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>35.50</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>25.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>10.39</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>5.70</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>2.95</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>6.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>18</td>\n",
       "      <td>5.55</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "      <td>59.98</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   record_number  shipment_method_id  shipping_fee  carrier_min_estimate  \\\n",
       "0              1                   0          0.00                     3   \n",
       "1              2                   0          3.00                     3   \n",
       "2              3                   0          4.50                     3   \n",
       "3              4                   0          0.00                     3   \n",
       "4              5                   0          0.00                     3   \n",
       "5              6                   0          0.00                     3   \n",
       "6              7                   0          0.00                     3   \n",
       "7              8                   0          2.95                     3   \n",
       "8              9                   3          0.00                     2   \n",
       "9             10                   1          0.00                     2   \n",
       "\n",
       "   carrier_max_estimate  category_id  item_price  quantity  bt  \n",
       "0                     5           13       27.95         1   0  \n",
       "1                     5            0       20.50         1   1  \n",
       "2                     5            1       19.90         1   0  \n",
       "3                     5            1       35.50         1   0  \n",
       "4                     5            1       25.00         1   0  \n",
       "5                     5            3       10.39         1   0  \n",
       "6                     5           11        5.70         1   0  \n",
       "7                     5            1        6.00         1   0  \n",
       "8                     8           18        5.55         1   0  \n",
       "9                     5           13       59.98         1   0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict1 = {\n",
    "    'B2C': 0,\n",
    "    'C2C': 1\n",
    "}\n",
    "\n",
    "parsed['bt'] = raw['b2c_c2c'].map(dict1)\n",
    "parsed[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1348d383",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(15000000, 9)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parsed.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8aeddf14",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'15'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'2019-03-26 15:11:00.000-07:00'.split()[1][:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "376cd892",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-7"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "int('2019-03-26 15:11:00.000-07:00'.split()[1][-6:-3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "8772e289",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdate = datetime.strptime('2019-03-26', \"%Y-%m-%d\")\n",
    "cdate.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e73d25df",
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_func(row):\n",
    "    acct = row['acceptance_scan_timestamp']\n",
    "    dd = row['delivery_date']\n",
    "    arr = acct.split()\n",
    "    cdate = datetime.strptime(arr[0], \"%Y-%m-%d\")\n",
    "    ddate = datetime.strptime(dd, \"%Y-%m-%d\")\n",
    "    delta = ddate - cdate\n",
    "    \n",
    "    acc_hour = arr[1][:2]\n",
    "    tz = int(arr[1][-6:-3])\n",
    "    if cdate.month >= 3 and cdate.month <= 11:\n",
    "        isdst = 0\n",
    "    else:\n",
    "        isdst = 1\n",
    "        \n",
    "    return delta.days, acc_hour, tz, isdst"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "4a89c6c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "fattr = raw.apply(add_func, axis=1, result_type='expand')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "e543b466",
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>15</td>\n",
       "      <td>-7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>-7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>-5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>-8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>-7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14999995</th>\n",
       "      <td>3</td>\n",
       "      <td>15</td>\n",
       "      <td>-5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14999996</th>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "      <td>-4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14999997</th>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>-7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14999998</th>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>-5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14999999</th>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>-4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15000000 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          0   1  2  3\n",
       "0         3  15 -7  0\n",
       "1         3  12 -7  0\n",
       "2         3  16 -5  1\n",
       "3         4  16 -8  1\n",
       "4         3  16 -7  0\n",
       "...      ..  .. .. ..\n",
       "14999995  3  15 -5  0\n",
       "14999996  6  14 -4  0\n",
       "14999997  5  15 -7  0\n",
       "14999998  3  10 -5  1\n",
       "14999999  4  12 -4  0\n",
       "\n",
       "[15000000 rows x 4 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fattr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "56473c0d",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_5123/946312585.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  parsed['target'] = fattr[0]\n",
      "/tmp/ipykernel_5123/946312585.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  parsed['acc_hour'] = fattr[1]\n",
      "/tmp/ipykernel_5123/946312585.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  parsed['sender_tz'] = fattr[2]\n",
      "/tmp/ipykernel_5123/946312585.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  parsed['isdst'] = fattr[3]\n"
     ]
    }
   ],
   "source": [
    "parsed['target'] = fattr[0]\n",
    "parsed['acc_hour'] = fattr[1]\n",
    "parsed['sender_tz'] = fattr[2]\n",
    "parsed['isdst'] = fattr[3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "83d1b07c",
   "metadata": {},
   "outputs": [],
   "source": [
    "raw['sender_tz'] = fattr[2]\n",
    "raw['isdst'] = fattr[3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "909c1958",
   "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>record_number</th>\n",
       "      <th>shipment_method_id</th>\n",
       "      <th>shipping_fee</th>\n",
       "      <th>carrier_min_estimate</th>\n",
       "      <th>carrier_max_estimate</th>\n",
       "      <th>category_id</th>\n",
       "      <th>item_price</th>\n",
       "      <th>quantity</th>\n",
       "      <th>bt</th>\n",
       "      <th>target</th>\n",
       "      <th>acc_hour</th>\n",
       "      <th>sender_tz</th>\n",
       "      <th>isdst</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "      <td>27.95</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>15</td>\n",
       "      <td>-7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>20.50</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>-7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>19.90</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>-5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>35.50</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>-8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>25.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>-7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   record_number  shipment_method_id  shipping_fee  carrier_min_estimate  \\\n",
       "0              1                   0           0.0                     3   \n",
       "1              2                   0           3.0                     3   \n",
       "2              3                   0           4.5                     3   \n",
       "3              4                   0           0.0                     3   \n",
       "4              5                   0           0.0                     3   \n",
       "\n",
       "   carrier_max_estimate  category_id  item_price  quantity  bt  target  \\\n",
       "0                     5           13       27.95         1   0       3   \n",
       "1                     5            0       20.50         1   1       3   \n",
       "2                     5            1       19.90         1   0       3   \n",
       "3                     5            1       35.50         1   0       4   \n",
       "4                     5            1       25.00         1   0       3   \n",
       "\n",
       "  acc_hour  sender_tz  isdst  \n",
       "0       15         -7      0  \n",
       "1       12         -7      0  \n",
       "2       16         -5      1  \n",
       "3       16         -8      1  \n",
       "4       16         -7      0  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parsed[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "f5aed8c4",
   "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>record_number</th>\n",
       "      <th>shipment_method_id</th>\n",
       "      <th>shipping_fee</th>\n",
       "      <th>carrier_min_estimate</th>\n",
       "      <th>carrier_max_estimate</th>\n",
       "      <th>category_id</th>\n",
       "      <th>item_price</th>\n",
       "      <th>quantity</th>\n",
       "      <th>bt</th>\n",
       "      <th>target</th>\n",
       "      <th>acc_hour</th>\n",
       "      <th>sender_tz</th>\n",
       "      <th>isdst</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1558</th>\n",
       "      <td>1559</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "      <td>3.76</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-2</td>\n",
       "      <td>18</td>\n",
       "      <td>-6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1777</th>\n",
       "      <td>1778</td>\n",
       "      <td>3</td>\n",
       "      <td>3.79</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>6.95</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-6</td>\n",
       "      <td>17</td>\n",
       "      <td>-4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3928</th>\n",
       "      <td>3929</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>10.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-43</td>\n",
       "      <td>20</td>\n",
       "      <td>-7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7233</th>\n",
       "      <td>7234</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>14.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>21</td>\n",
       "      <td>-4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7642</th>\n",
       "      <td>7643</td>\n",
       "      <td>0</td>\n",
       "      <td>3.50</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>32.00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-10</td>\n",
       "      <td>09</td>\n",
       "      <td>-7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14994475</th>\n",
       "      <td>14994476</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>22.99</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-2</td>\n",
       "      <td>19</td>\n",
       "      <td>-4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14996746</th>\n",
       "      <td>14996747</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>14.88</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-21</td>\n",
       "      <td>17</td>\n",
       "      <td>-7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14997239</th>\n",
       "      <td>14997240</td>\n",
       "      <td>2</td>\n",
       "      <td>9.26</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>18.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-3</td>\n",
       "      <td>20</td>\n",
       "      <td>-5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14998005</th>\n",
       "      <td>14998006</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>340.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-16</td>\n",
       "      <td>17</td>\n",
       "      <td>-5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14998072</th>\n",
       "      <td>14998073</td>\n",
       "      <td>0</td>\n",
       "      <td>10.15</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>24.95</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-3</td>\n",
       "      <td>11</td>\n",
       "      <td>-8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14866 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          record_number  shipment_method_id  shipping_fee  \\\n",
       "1558               1559                   0          0.00   \n",
       "1777               1778                   3          3.79   \n",
       "3928               3929                   0          0.00   \n",
       "7233               7234                   0          0.00   \n",
       "7642               7643                   0          3.50   \n",
       "...                 ...                 ...           ...   \n",
       "14994475       14994476                   0          0.00   \n",
       "14996746       14996747                   0          0.00   \n",
       "14997239       14997240                   2          9.26   \n",
       "14998005       14998006                   1          0.00   \n",
       "14998072       14998073                   0         10.15   \n",
       "\n",
       "          carrier_min_estimate  carrier_max_estimate  category_id  item_price  \\\n",
       "1558                         3                     5           13        3.76   \n",
       "1777                         2                     8            3        6.95   \n",
       "3928                         3                     5            3       10.00   \n",
       "7233                         3                     5            5       14.00   \n",
       "7642                         3                     5            2       32.00   \n",
       "...                        ...                   ...          ...         ...   \n",
       "14994475                     3                     5            2       22.99   \n",
       "14996746                     3                     5            1       14.88   \n",
       "14997239                     2                     9            0       18.00   \n",
       "14998005                     2                     5            1      340.00   \n",
       "14998072                     3                     5            1       24.95   \n",
       "\n",
       "          quantity  bt  target acc_hour  sender_tz  isdst  \n",
       "1558             1   0      -2       18         -6      1  \n",
       "1777             1   0      -6       17         -4      0  \n",
       "3928             1   0     -43       20         -7      0  \n",
       "7233             1   0      -1       21         -4      0  \n",
       "7642             1   1     -10       09         -7      0  \n",
       "...            ...  ..     ...      ...        ...    ...  \n",
       "14994475         1   0      -2       19         -4      0  \n",
       "14996746         1   0     -21       17         -7      0  \n",
       "14997239         1   0      -3       20         -5      1  \n",
       "14998005         1   0     -16       17         -5      0  \n",
       "14998072         1   0      -3       11         -8      1  \n",
       "\n",
       "[14866 rows x 13 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parsed[parsed['target'] < 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "bee95d9f",
   "metadata": {},
   "outputs": [],
   "source": [
    "zip_info = json.load(open('data/zipcode_dict.json', 'r'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "60719b5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_func(row):\n",
    "    sender_tz = row['sender_tz']\n",
    "    sender_zip = str(row['item_zip'])\n",
    "    re_zip = str(row['buyer_zip'])\n",
    "    \n",
    "    sender_info = zip_info[sender_zip]\n",
    "    re_info = zip_info[re_zip]\n",
    "    \n",
    "    if re_info['tz'] != False:\n",
    "        tz_dis = re_info['tz'] - sender_tz - int(row['isdst'])\n",
    "    else:\n",
    "        tz_dis = 0\n",
    "    \n",
    "    if sender_info['lat'] != False and re_info['lat'] != False:\n",
    "        dis = int(lldis(sender_info['lat'], re_info['lat'], sender_info['lng'], re_info['lng']) / 1000)\n",
    "    else:\n",
    "        dis = -1\n",
    "    \n",
    "    if sender_info['city'] != False and re_info['city'] != False:\n",
    "        if sender_info['city'] == re_info['city']:\n",
    "            cross_city = 0\n",
    "        else:\n",
    "            cross_city = 1\n",
    "    else:\n",
    "        cross_city = -1\n",
    "    \n",
    "    if sender_info['state'] != False and re_info['state'] != False:\n",
    "        if sender_info['state'] == re_info['state']:\n",
    "            cross_state = 0\n",
    "        else:\n",
    "            cross_state = 1\n",
    "    else:\n",
    "        cross_state = -1\n",
    "    \n",
    "    return tz_dis, dis, cross_city, cross_state"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "d51b8421",
   "metadata": {},
   "outputs": [],
   "source": [
    "dis_attr = raw.apply(add_func, axis=1, result_type='expand')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "ecd67adf",
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14999995</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14999996</th>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14999997</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14999998</th>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14999999</th>\n",
       "      <td>-2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15000000 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          0  1  2  3\n",
       "0         2  3  1  1\n",
       "1         1  1  1  1\n",
       "2        -2  1  1  1\n",
       "3         0  1  1  1\n",
       "4         1  2  1  1\n",
       "...      .. .. .. ..\n",
       "14999995  0  1  1  1\n",
       "14999996 -1  0  1  1\n",
       "14999997  1  2  1  1\n",
       "14999998  0 -1 -1 -1\n",
       "14999999 -2  1  1  1\n",
       "\n",
       "[15000000 rows x 4 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dis_attr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "1c5e29e8",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_5123/3186594668.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  parsed['tz_dis'] = dis_attr[0]\n",
      "/tmp/ipykernel_5123/3186594668.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  parsed['dis'] = dis_attr[1]\n",
      "/tmp/ipykernel_5123/3186594668.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  parsed['cross_city'] = dis_attr[2]\n",
      "/tmp/ipykernel_5123/3186594668.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  parsed['cross_state'] = dis_attr[3]\n"
     ]
    }
   ],
   "source": [
    "parsed['tz_dis'] = dis_attr[0]\n",
    "parsed['dis'] = dis_attr[1]\n",
    "parsed['cross_city'] = dis_attr[2]\n",
    "parsed['cross_state'] = dis_attr[3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "936cf491",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-1     5199652\n",
       " 0     2253792\n",
       "-2     2128312\n",
       " 2     1813210\n",
       " 1     1371500\n",
       "-4     1111304\n",
       "-3      998387\n",
       " 3      122879\n",
       " 5         350\n",
       " 4         332\n",
       "-5          80\n",
       " 13         57\n",
       "-6          40\n",
       " 14         36\n",
       " 16         30\n",
       " 15         20\n",
       " 12          9\n",
       " 17          7\n",
       "-7           2\n",
       " 9           1\n",
       "Name: 0, dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dis_attr[0].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "d70b6a0c",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_5123/645992141.py:10: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  parsed['package_size'] = raw['package_size'].map(dict2)\n"
     ]
    }
   ],
   "source": [
    "dict2 = {\n",
    "    'PACKAGE_THICK_ENVELOPE': 0,\n",
    "    'NONE': 1,\n",
    "    'LETTER': 2,\n",
    "    'LARGE_ENVELOPE': 3,\n",
    "    'LARGE_PACKAGE': 4,\n",
    "    'EXTRA_LARGE_PACKAGE': 5,\n",
    "    'VERY_LARGE_PACKAGE': 6\n",
    "}\n",
    "parsed['package_size'] = raw['package_size'].map(dict2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "dd13dfbd",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_5123/1896518701.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  parsed['weight'] = raw['weight_units'] * raw['weight']\n"
     ]
    }
   ],
   "source": [
    "parsed['weight'] = raw['weight_units'] * raw['weight']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "4de68b67",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        4792627\n",
       "32       1229837\n",
       "4        1183041\n",
       "3        1140367\n",
       "16        853087\n",
       "          ...   \n",
       "707            1\n",
       "980            1\n",
       "10960          1\n",
       "5920           1\n",
       "505            1\n",
       "Name: weight, Length: 1298, dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parsed['weight'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "0524e17e",
   "metadata": {},
   "outputs": [],
   "source": [
    "parsed.to_csv('data/parsed_train.tsv', index=None, sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "6a9c8a52",
   "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>record_number</th>\n",
       "      <th>shipment_method_id</th>\n",
       "      <th>shipping_fee</th>\n",
       "      <th>carrier_min_estimate</th>\n",
       "      <th>carrier_max_estimate</th>\n",
       "      <th>category_id</th>\n",
       "      <th>item_price</th>\n",
       "      <th>quantity</th>\n",
       "      <th>bt</th>\n",
       "      <th>target</th>\n",
       "      <th>acc_hour</th>\n",
       "      <th>sender_tz</th>\n",
       "      <th>isdst</th>\n",
       "      <th>tz_dis</th>\n",
       "      <th>dis</th>\n",
       "      <th>cross_city</th>\n",
       "      <th>cross_state</th>\n",
       "      <th>package_size</th>\n",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "      <td>27.95</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>15</td>\n",
       "      <td>-7</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>20.50</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>-7</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>4.50</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>19.90</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>-5</td>\n",
       "      <td>1</td>\n",
       "      <td>-2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>35.50</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>-8</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>25.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>-7</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14999995</th>\n",
       "      <td>14999996</td>\n",
       "      <td>1</td>\n",
       "      <td>9.85</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>7.35</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>15</td>\n",
       "      <td>-5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14999996</th>\n",
       "      <td>14999997</td>\n",
       "      <td>2</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>36.00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "      <td>-4</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14999997</th>\n",
       "      <td>14999998</td>\n",
       "      <td>2</td>\n",
       "      <td>2.99</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>7.99</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>-7</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14999998</th>\n",
       "      <td>14999999</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>34.99</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>-5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14999999</th>\n",
       "      <td>15000000</td>\n",
       "      <td>1</td>\n",
       "      <td>7.45</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>205.50</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>-4</td>\n",
       "      <td>0</td>\n",
       "      <td>-2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15000000 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          record_number  shipment_method_id  shipping_fee  \\\n",
       "0                     1                   0          0.00   \n",
       "1                     2                   0          3.00   \n",
       "2                     3                   0          4.50   \n",
       "3                     4                   0          0.00   \n",
       "4                     5                   0          0.00   \n",
       "...                 ...                 ...           ...   \n",
       "14999995       14999996                   1          9.85   \n",
       "14999996       14999997                   2          0.00   \n",
       "14999997       14999998                   2          2.99   \n",
       "14999998       14999999                   0          0.00   \n",
       "14999999       15000000                   1          7.45   \n",
       "\n",
       "          carrier_min_estimate  carrier_max_estimate  category_id  item_price  \\\n",
       "0                            3                     5           13       27.95   \n",
       "1                            3                     5            0       20.50   \n",
       "2                            3                     5            1       19.90   \n",
       "3                            3                     5            1       35.50   \n",
       "4                            3                     5            1       25.00   \n",
       "...                        ...                   ...          ...         ...   \n",
       "14999995                     2                     5            3        7.35   \n",
       "14999996                     2                     9            1       36.00   \n",
       "14999997                     2                     9            0        7.99   \n",
       "14999998                     3                     5            1       34.99   \n",
       "14999999                     2                     5            3      205.50   \n",
       "\n",
       "          quantity  bt  target acc_hour  sender_tz  isdst  tz_dis  dis  \\\n",
       "0                1   0       3       15         -7      0       2    3   \n",
       "1                1   1       3       12         -7      0       1    1   \n",
       "2                1   0       3       16         -5      1      -2    1   \n",
       "3                1   0       4       16         -8      1       0    1   \n",
       "4                1   0       3       16         -7      0       1    2   \n",
       "...            ...  ..     ...      ...        ...    ...     ...  ...   \n",
       "14999995         1   0       3       15         -5      0       0    1   \n",
       "14999996         1   1       6       14         -4      0      -1    0   \n",
       "14999997         1   0       5       15         -7      0       1    2   \n",
       "14999998         1   0       3       10         -5      1       0   -1   \n",
       "14999999         1   1       4       12         -4      0      -2    1   \n",
       "\n",
       "          cross_city  cross_state  package_size  weight  \n",
       "0                  1            1             2       5  \n",
       "1                  1            1             0       0  \n",
       "2                  1            1             0       9  \n",
       "3                  1            1             0       8  \n",
       "4                  1            1             0       3  \n",
       "...              ...          ...           ...     ...  \n",
       "14999995           1            1             0      32  \n",
       "14999996           1            1             0      64  \n",
       "14999997           1            1             1       0  \n",
       "14999998          -1           -1             0      13  \n",
       "14999999           1            1             0      15  \n",
       "\n",
       "[15000000 rows x 19 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parsed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "acae2285",
   "metadata": {},
   "outputs": [],
   "source": [
    "parsed = parsed[parsed['target'] >= 0]"
   ]
  }
 ],
 "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.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}