{"cells":[{"metadata":{"_uuid":"a3dd130dc616e25068f99dccfae7d294c22b0e61"},"cell_type":"markdown","source":"Loading this data from csv is pain when you have slow machine with limited memory (like I do).\n\nSo, by several steps we gonna reduce both memory size and loading time:\n\n1. Label encode Card ID and Merchant ID labels in transaction histories and train/test sets\n2. Merge historical and \"new\" transaction logs into one (also add distinguishing column)\n3. Encode date field\n4. Encode text categorical fields as numeric\n5. Compress all numeric fields if possible with reduce_mem_usage*\n6. At last: save all the data in parquete format \n\n\\* reduce_mem_usage is a slightly simplified version of [this kernel](https://www.kaggle.com/arjanso/reducing-dataframe-memory-size-by-65) by [arjanso](https://www.kaggle.com/arjanso)\n\n\\** Saving data in parquete format (by using [Pyarrow](https://arrow.apache.org/docs/python/parquet.html)) gets us great loading speed due to compression, muilthreaded loading, etc.)\n\nOn my home machine (cpu: 4 cores, mem: 16GB) loading time is reduced down to **2.7 sec.**\n\nIn this notebook it's around **6 sec.**\n"},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"import time\nimport numpy as np\nimport pandas as pd\nimport pyarrow as pa\nimport pyarrow.parquet as pq\nfrom sklearn.preprocessing import LabelEncoder","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1b14271f4a6192c7c8947f037d9b8db3023d239e"},"cell_type":"markdown","source":"First load all the data:"},{"metadata":{"trusted":true,"_uuid":"adf57393bc5dfe09a01b96c49d34874b302e5fa2"},"cell_type":"code","source":"train = pd.read_csv(\"../input/train.csv\")\ntest = pd.read_csv(\"../input/test.csv\")\nmerchants = pd.read_csv(\"../input/merchants.csv\")\nhistorical_transactions = pd.read_csv(\"../input/historical_transactions.csv\")\nnew_merchant_transactions = pd.read_csv(\"../input/new_merchant_transactions.csv\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7de8ed1b4d96ed9e66c9e331254ee38a84c5288d"},"cell_type":"markdown","source":"Let's see how much memory it occupies in the beginning:"},{"metadata":{"trusted":true,"_uuid":"cf29965dd3ca27455d3af7a1b1659f63a9222752"},"cell_type":"code","source":"def mem_usage_mb(dataframes):\n    return int(sum([df.memory_usage().sum() for df in dataframes]) / 1024**2)\n \nmem_usg_0 = mem_usage_mb([train, test, merchants, historical_transactions, new_merchant_transactions])\nprint(\"Memory usage: {} MB\".format(mem_usg_0))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d763d5616ecd8e98b1ad7e7e497a595e42b77ff0"},"cell_type":"markdown","source":"Label encoding for Card ID and Merchant ID:"},{"metadata":{"trusted":true,"_uuid":"0fa17cac8aa75a1ecfbb9c08dcf504dba9d25f57"},"cell_type":"code","source":"card_ids = np.hstack((train[\"card_id\"].values, test[\"card_id\"].values))\nencoder = LabelEncoder().fit(card_ids)\n\nfor df in (test, train, historical_transactions, new_merchant_transactions):\n    df[\"card_id\"] = encoder.transform(df[\"card_id\"])\n    \nencoder = LabelEncoder().fit(merchants[\"merchant_id\"])\nfor df in (merchants, historical_transactions, new_merchant_transactions):\n    # rows with non-null merchant_id\n    df.loc[~df[\"merchant_id\"].isnull(), \"merchant_id\"] \\\n        = encoder.transform(df.loc[~df[\"merchant_id\"].isnull(), \"merchant_id\"])\n    # rows with null merchant_id\n    df.loc[df[\"merchant_id\"].isnull(), \"merchant_id\"] = -1","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f28067537776f6b6492e231853adcbf214bd7e11"},"cell_type":"markdown","source":"Merge old and new transactions in one table:"},{"metadata":{"trusted":true,"_uuid":"c523bdf2412840d1041f0429a1cc24f2f04e9e3f"},"cell_type":"code","source":"historical_transactions[\"historical\"] = True\nnew_merchant_transactions[\"historical\"] = False\ntransactions = pd.concat((historical_transactions, new_merchant_transactions), axis=0).reset_index(drop=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1585ed81d2094ea0c8f278410ab3ff8cac7903b3"},"cell_type":"markdown","source":"Encode rest of the text columns:"},{"metadata":{"trusted":true,"_uuid":"30e522ca02b74b5f5bbdf553e584d8eab8748eb3"},"cell_type":"code","source":"transactions[\"authorized_flag\"] = transactions[\"authorized_flag\"] == \"Y\"\n  \ntransactions[\"category_1\"] = transactions[\"category_1\"] == \"Y\"\n\ntransactions[\"category_3\"] = transactions[\"category_3\"].fillna(\"\")\ntransactions[\"category_3\"] = LabelEncoder().fit(transactions[\"category_3\"]).transform(transactions[\"category_3\"])\n\ntransactions[\"purchase_date\"] = pd.to_datetime(transactions[\"purchase_date\"])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6e9074445862d49db3f1ac909717ed414bede081"},"cell_type":"markdown","source":"Simplified version of reduce_mem_usage:"},{"metadata":{"trusted":true,"_uuid":"9b9118cb1229a4e320eb7caf3c03dc144b2ad907"},"cell_type":"code","source":"def reduce_mem_usage(dataframe, skip=[]):\n    \n    for col in dataframe.columns:\n        \n        if col in skip:\n            continue\n        \n        col_type = str(dataframe[col].dtype)\n        \n        if col_type.startswith(\"int\"):\n            \n            mx = dataframe[col].max()\n            mn = dataframe[col].min()\n            \n            if mn >= 0:\n                if mx < 255:\n                    dataframe[col] = dataframe[col].astype(np.uint8)\n                elif mx < 65535:\n                    dataframe[col] = dataframe[col].astype(np.uint16)\n                elif mx < 4294967295:\n                    dataframe[col] = dataframe[col].astype(np.uint32)\n                else:\n                    dataframe[col] = dataframe[col].astype(np.uint64)\n            else:\n                if mn > np.iinfo(np.int8).min and mx < np.iinfo(np.int8).max:\n                    dataframe[col] = dataframe[col].astype(np.int8)\n                elif mn > np.iinfo(np.int16).min and mx < np.iinfo(np.int16).max:\n                    dataframe[col] = dataframe[col].astype(np.int16)\n                elif mn > np.iinfo(np.int32).min and mx < np.iinfo(np.int32).max:\n                    dataframe[col] = dataframe[col].astype(np.int32)\n                elif mn > np.iinfo(np.int64).min and mx < np.iinfo(np.int64).max:\n                    dataframe[col] = dataframe[col].astype(np.int64)\n                    \n        elif col_type.startswith(\"float\"):\n            \n            mx = dataframe[col].max()\n            mn = dataframe[col].min()\n    \n            if mn > np.finfo(np.float32).min and mx < np.finfo(np.float32).max:\n                dataframe[col] = dataframe[col].astype(np.float32)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"958c4c09610d525d564bf3172a3f4da580c7cf53"},"cell_type":"markdown","source":"Compress the old and new numeric fields is possible:"},{"metadata":{"trusted":true,"_uuid":"1e973b440606409a17e46e0313a38724b0456781"},"cell_type":"code","source":"    reduce_mem_usage(transactions)\n    reduce_mem_usage(train)\n    reduce_mem_usage(test)\n    \n    reduce_mem_usage(merchants, skip=[\"avg_purchases_lag3\", \"avg_purchases_lag6\", \"avg_purchases_lag12\"])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f6b33fd8b63d7a65936445df5c622170960a8391"},"cell_type":"markdown","source":"Let's see how much memory we've saved:"},{"metadata":{"trusted":true,"_uuid":"88173a89adb01e381a90474c1462527f9fdb8cf0"},"cell_type":"code","source":"mem_usg_1 = mem_usage_mb([train, test, merchants, transactions])\nprint(\"Memory usage: {} MB\".format(mem_usg_1))\nprint(mem_usg_1 / mem_usg_0 * 100, \"% of initial size\")\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"020858a5354b0e7c34e9156c2aaa55f904ff8189"},"cell_type":"markdown","source":"Save to parquet format:"},{"metadata":{"trusted":true,"_uuid":"0f678112050422dd805c8c4924095651a15f72b9"},"cell_type":"code","source":"def save_as_parquet(df, path):\n    table = pa.Table.from_pandas(df)\n    pq.write_table(table, path, use_dictionary=True, compression='snappy')\n\nsave_as_parquet(train, \"train.parquet\")\nsave_as_parquet(test, \"test.parquet\")\nsave_as_parquet(merchants, \"merchants.parquet\")\nsave_as_parquet(historical_transactions, \"historical_transactions.parquet\")\nsave_as_parquet(new_merchant_transactions, \"new_merchant_transactions.parquet\")\nsave_as_parquet(transactions, \"transactions.parquet\")\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"615757af415759472d6ff85705ce315f1330764a"},"cell_type":"markdown","source":"How fast is the loading now:"},{"metadata":{"trusted":true,"_uuid":"10d45354a6b0a9b6c11fa046b178f36501696e91"},"cell_type":"code","source":"def load_parquet(path):\n    table = pq.read_table(path, nthreads=4)\n    return table.to_pandas()\n\np0 = time.time()\nload_parquet(\"train.parquet\")\nload_parquet(\"test.parquet\")\nload_parquet(\"merchants.parquet\")\nload_parquet(\"transactions.parquet\")\np1 = time.time()\np1 - p0\n    ","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}