Untitled.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "coupled-capitol",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "09_to_lbe.pkl.bz2  11_to_lbe.pkl.bz2  doc_lbe.pkl.bz2\r\n",
      "10_to_lbe.pkl.bz2  card_lbe.pkl.bz2\r\n"
     ]
    }
   ],
   "source": [
    "from pathlib import Path\n",
    "import pandas as pd\n",
    "import gc\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "import pickle\n",
    "import bz2\n",
    "\n",
    "na_int_val = -9999\n",
    "\n",
    "data_path = Path('./data/').resolve()\n",
    "lbe_path = data_path / 'lbe'\n",
    "!ls {lbe_path}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "indie-split",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3,8G\t/data/Projects/inno_stats/data/09.csv\n",
      "4,1G\t/data/Projects/inno_stats/data/10.csv\n",
      "4,2G\t/data/Projects/inno_stats/data/11.csv\n",
      "172M\t/data/Projects/inno_stats/data/lbe/doc_lbe.pkl.bz2\n",
      "12M\t/data/Projects/inno_stats/data/lbe/card_lbe.pkl.bz2\n"
     ]
    }
   ],
   "source": [
    "!du -sh {data_path / '09.csv'}\n",
    "!du -sh {data_path / '10.csv'}\n",
    "!du -sh {data_path / '11.csv'}\n",
    "\n",
    "!du -sh {lbe_path / 'doc_lbe.pkl.bz2'}\n",
    "!du -sh {lbe_path / 'card_lbe.pkl.bz2'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "objective-inflation",
   "metadata": {},
   "outputs": [],
   "source": [
    "with bz2.open(lbe_path / 'doc_lbe.pkl.bz2', 'rb') as f:\n",
    "    doc_lbe = pickle.load(f)\n",
    "with bz2.open(lbe_path / 'card_lbe.pkl.bz2', 'rb') as f:\n",
    "    card_lbe = pickle.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "adapted-december",
   "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>id_card</th>\n",
       "      <th>id_card_int</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8594499</td>\n",
       "      <td>1538855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1452388</td>\n",
       "      <td>267307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3493538</td>\n",
       "      <td>610220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2491281</td>\n",
       "      <td>441497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5732396</td>\n",
       "      <td>1065358</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_card  id_card_int\n",
       "0  8594499      1538855\n",
       "1  1452388       267307\n",
       "2  3493538       610220\n",
       "3  2491281       441497\n",
       "4  5732396      1065358"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "card_lbe.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "signed-thesaurus",
   "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>id_card_int</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id_card</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8594499</th>\n",
       "      <td>1538855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1452388</th>\n",
       "      <td>267307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3493538</th>\n",
       "      <td>610220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2491281</th>\n",
       "      <td>441497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5732396</th>\n",
       "      <td>1065358</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id_card_int\n",
       "id_card             \n",
       "8594499      1538855\n",
       "1452388       267307\n",
       "3493538       610220\n",
       "2491281       441497\n",
       "5732396      1065358"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "card_lbe.set_index('id_card', inplace=True)\n",
    "card_lbe.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "reverse-improvement",
   "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>id_doc</th>\n",
       "      <th>id_doc_int</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0E05D308-5CEC-EA11-B444-005056A7539A</td>\n",
       "      <td>1283228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>610205A1-61EC-EA11-B444-005056A7539A</td>\n",
       "      <td>8873113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8AF19602-70EC-EA11-B444-005056A7539A</td>\n",
       "      <td>12712899</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EB6C71A3-84EC-EA11-B444-005056A7539A</td>\n",
       "      <td>21535283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0706023F-6BEC-EA11-B444-005056A7539A</td>\n",
       "      <td>642341</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 id_doc  id_doc_int\n",
       "0  0E05D308-5CEC-EA11-B444-005056A7539A     1283228\n",
       "1  610205A1-61EC-EA11-B444-005056A7539A     8873113\n",
       "2  8AF19602-70EC-EA11-B444-005056A7539A    12712899\n",
       "3  EB6C71A3-84EC-EA11-B444-005056A7539A    21535283\n",
       "4  0706023F-6BEC-EA11-B444-005056A7539A      642341"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "doc_lbe.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "steady-springfield",
   "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>id_doc_int</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id_doc</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0E05D308-5CEC-EA11-B444-005056A7539A</th>\n",
       "      <td>1283228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>610205A1-61EC-EA11-B444-005056A7539A</th>\n",
       "      <td>8873113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8AF19602-70EC-EA11-B444-005056A7539A</th>\n",
       "      <td>12712899</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EB6C71A3-84EC-EA11-B444-005056A7539A</th>\n",
       "      <td>21535283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0706023F-6BEC-EA11-B444-005056A7539A</th>\n",
       "      <td>642341</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      id_doc_int\n",
       "id_doc                                          \n",
       "0E05D308-5CEC-EA11-B444-005056A7539A     1283228\n",
       "610205A1-61EC-EA11-B444-005056A7539A     8873113\n",
       "8AF19602-70EC-EA11-B444-005056A7539A    12712899\n",
       "EB6C71A3-84EC-EA11-B444-005056A7539A    21535283\n",
       "0706023F-6BEC-EA11-B444-005056A7539A      642341"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "doc_lbe.set_index('id_doc', inplace=True)\n",
    "doc_lbe.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "alien-aquatic",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1283228"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "doc_lbe.loc['0E05D308-5CEC-EA11-B444-005056A7539A']['id_doc_int']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "younger-plate",
   "metadata": {},
   "outputs": [],
   "source": [
    "one = '09'\n",
    "two = '10'\n",
    "three = '11'\n",
    "file_path_in = data_path/(two + '.csv')\n",
    "file_path_out = data_path / (two + '_prepared.pkl.bz2')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "actual-ridge",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'file_path' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-10-ae3ad02aa1d4>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m df = pd.read_csv(file_path, sep=';',\n\u001b[0m\u001b[1;32m      2\u001b[0m                  \u001b[0mparse_dates\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'date'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m                  \u001b[0minfer_datetime_format\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m                  \u001b[0mdtype\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m{\u001b[0m\u001b[0;34m'id_doc'\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m'category'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'id_card'\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m'category'\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m                 )\n",
      "\u001b[0;31mNameError\u001b[0m: name 'file_path' is not defined"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv(file_path_in, sep=';',\n",
    "                 parse_dates=['date'], \n",
    "                 infer_datetime_format=True, \n",
    "                 dtype={'id_doc':'category', 'id_card':'category'}\n",
    "                )\n",
    "\n",
    "df['quantity'] = df['quantity'].str.replace(',', '.').astype(float)\n",
    "df['sum'] = df['sum'].str.replace(',', '.').astype(float)\n",
    "df['is_green'] = df['is_green'].astype('bool')\n",
    "df['id_kontr'] = df['id_kontr'].fillna(na_int_val).astype(int)\n",
    "df['id_card'] = df['id_card'].apply(lambda x: card_lbe.loc[x.strip()]['id_card_int']) \n",
    "df['id_doc'] = df['id_doc'].apply(lambda x: doc_lbe.loc[x.strip()]['id_doc_int'])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "distributed-alignment",
   "metadata": {},
   "outputs": [],
   "source": [
    "with bz2.open(file_path_out, 'wb') as f:\n",
    "    pickle.dump(df, f, protocol=4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "addressed-notification",
   "metadata": {},
   "outputs": [],
   "source": [
    "del df\n",
    "gc.collect()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}