{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\n \n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 5GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-output":true,"_kg_hide-input":true},"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport gc\nfrom sklearn.preprocessing import LabelEncoder\nfrom sklearn.preprocessing import StandardScaler\nimport lightgbm as lgb\nimport dill\nimport time\nimport json\nimport multiprocessing as mp\nimport threading\nfrom queue import Queue","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"#From https://www.kaggle.com/rohanrao/ashrae-half-and-half\n\nfrom pandas.api.types import is_datetime64_any_dtype as is_datetime\nfrom pandas.api.types import is_categorical_dtype\n\ndef reduce_mem_usage(df, use_float16=False,verbose=True):\n    \"\"\"\n    Iterate through all the columns of a dataframe and modify the data type to reduce memory usage.        \n    \"\"\"\n    \n    start_mem = df.memory_usage().sum() / 1024**2\n    if verbose :print(\"Memory usage of dataframe is {:.2f} MB\".format(start_mem))\n    \n    for col in df.columns:\n        if is_datetime(df[col]) or is_categorical_dtype(df[col]):\n            continue\n        col_type = df[col].dtype\n        \n        if col_type != object:\n            c_min = df[col].min()\n            c_max = df[col].max()\n            if str(col_type)[:3] == \"int\":\n                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n                    df[col] = df[col].astype(np.int8)\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df[col] = df[col].astype(np.int16)\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df[col] = df[col].astype(np.int32)\n                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n                    df[col] = df[col].astype(np.int64)  \n            else:\n                if use_float16 and c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n                    df[col] = df[col].astype(np.float16)\n                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df[col] = df[col].astype(np.float32)\n                else:\n                    df[col] = df[col].astype(np.float64)\n        else:\n            df[col] = df[col].astype(\"category\")\n\n    end_mem = df.memory_usage().sum() / 1024**2\n    if verbose:print(\"Memory usage after optimization is: {:.2f} MB\".format(end_mem))\n    if verbose:print(\"Decreased by {:.1f}%\".format(100 * (start_mem - end_mem) / start_mem))\n    \n    return df","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# SQLAlchemy :\nSQLAlchemy is a popular SQL toolkit and Object Relational Mapper. It gives full power and flexibility of SQL. By default, SQLAlchemy is installed in the Kaggle environment, so, no need to install anything\n\n### Recall: \nmetadata contains the latest state of each user and will be used to produce the final submission. I opted for SQL because the handling of Datafrme is very slow.\nWe extracted the metadata from this notebook: https://www.kaggle.com/tchaye59/riiid-preprocess-and-balance-the-dataset\nand use it in this notebook: https://www.kaggle.com/tchaye59/riiid-work-with-the-full-state-using-sqlalchemy"},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\nmetadata = dill.load(open('/kaggle/input/riiid-preprocess-and-balance-the-dataset/metadata.dill','rb'))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The exportation from Dataframe to SQLite files doesn't support multi-index,so it is important to reset the index"},{"metadata":{"trusted":true},"cell_type":"code","source":"for key in list(metadata.keys()):\n    if key in metadata[key].columns:\n        metadata[key].set_index(key,inplace=True)\n    print(key,metadata[key].shape)\n    metadata[key].reset_index(inplace=True)\n    metadata[key] = reduce_mem_usage(metadata[key])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Remove rows with all zeros"},{"metadata":{"trusted":true},"cell_type":"code","source":"s = 0\nfor key in metadata.keys():\n    metadata[key].set_index(list(key) if type(key) != str else key,inplace=True)\n    d = metadata[key]\n    metadata[key] = d[~(d == 0).all(axis=1)]\n    s += metadata[key].shape[0]\n    metadata[key].reset_index(inplace=True)\ns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"from sqlalchemy import create_engine","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Merge multiple keys into a single key\ndef build_index(x):\n    return '_'.join(map(lambda x:str(int(x)),x)) if type(x) not in [int,float] else str(int(x))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Export each key in metadata to an SQLite file"},{"metadata":{"trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"%%time\nn = 2000000 # Chunk size\nmetadata_info = {} # columns info\n\nfor i,key in enumerate(list(metadata.keys())):\n    df = metadata[key]\n    # make sure the key is a tuple\n    if type(key) == str:\n        key = key,\n        \n    # Connection to the DB file\n    name = f\"db.{'_'.join(key)}.sqlite\"\n    engine = create_engine(f'sqlite:///{name}', echo=False)\n    sqlite_connection = engine.connect()   \n    \n    # Export chunk by chunk\n    for i in range(0,df.shape[0],n):\n        tmp = df.iloc[i:min(i+n,df.shape[0])]\n        print(f'{key} | {i}/{df.shape[0]} | Build Index')\n        # multi-index is not supported so we will merge the keys columns\n        tmp.index = tmp[list(key)].apply(build_index,axis=1).values\n        tmp.drop(columns=[*key,], inplace=True)   \n        if key not in metadata_info : metadata_info[key] = [i,list(tmp.columns)]\n        print(f'{key} | {i}/{df.shape[0]} | Update DB')\n        tmp.to_sql('_'.join(key), sqlite_connection, if_exists='append')\n    sqlite_connection.close()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"dill.dump(metadata_info,open('metadata_info.dill','wb'))","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}