{"cells":[{"metadata":{"_uuid":"9b334dcbad72a56fe63315c0fd9e6be6e3c4991d"},"cell_type":"markdown","source":"## <span style=\"color;brown\">SQLite</span>\n\"Use something old\""},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport os\nimport gc\nimport sqlite3\nimport json\nfrom pandas.io.json import json_normalize\n\nprint(os.listdir(\"../input\"))\n\n# Any results you write to the current directory are saved as output.\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2a40c5fcd8186e7dce5f47d98fd33047626a6ef7"},"cell_type":"code","source":"# # Number of rows, train\n# n = 0\n# with open(\"../input/train_v2.csv\", \"r\") as f:\n#     for l in f:\n#         if n==0:\n#             print(l)\n#             break\n#         n += 1\n#         if not(n % 100000):\n#             print(n,\" :: date:\",l.strip().split(\",\")[2])\n# print(\"TRAIN CONTAINS %d-1 rows\"%n)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"N_ROWS = 10\n# 'customDimensions','hits' may are multi dict into a list (w'll stock them as STR)\nJSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\n\ndef json_normalize_data(df):\n    for column in JSON_COLUMNS: \n        column_as_df = json_normalize(df[column]) \n        column_as_df.columns = [f\"{column}_{subcolumn}\".replace('.','_') for subcolumn in column_as_df.columns] \n        df = df.drop(column, axis=1).merge(column_as_df, right_index=True, left_index=True)\n    return df\n\ntrain = pd.read_csv(\"../input/train_v2.csv\",  nrows=N_ROWS, \n                    converters={column: json.loads for column in JSON_COLUMNS}, \n                    dtype={\"fullVisitorId\": \"str\"})\ntest = pd.read_csv(\"../input/test_v2.csv\", nrows=N_ROWS, \n                    converters={column: json.loads for column in JSON_COLUMNS}, \n                    dtype={'fullVisitorId': 'str'})\n\ntrain = json_normalize_data(train)\ntest = json_normalize_data(test)\n\ntrain.shape,test.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"aef615aa0f587745fb71c4317839d553f617b396"},"cell_type":"code","source":"\n\ncolumns_types = {'date': 'TEXT', }\nfor c in set(train.columns) - set(['date']):\n    columns_types[c] = 'TEXT' # YOU CAN CHANGE THE TYPES ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b5c07d1924474e32f8178b238a281d87d6bf9675"},"cell_type":"code","source":"cols = [c for c in train.columns]\n#cols\ndel train\ndel test\ngc.collect()\ndel gc.garbage[:]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2404a95f0185727f2c69cb0cba5b2a2273b20095"},"cell_type":"code","source":"# -*- coding:utf8 -*-\nimport sqlite3\n\ncolumns_types.update(ID='INTEGER', ISTRAIN='INTEGER')\n\nclass DataBase:\n    \"\"\"\"\"\"\n    \n    fileCsvTrain = \"../input/train_v2.csv\"\n    fileCsvTest = \"../input/test_v2.csv\"\n    fileTrainDb = \"data.sq3\"\n    columns = ['ID',] + cols + ['ISTRAIN',]\n    \n    #=======================================================#\n    def __init__(self, data=\"train\"):\n        \"\"\"init\"\"\"\n        self.dataset=data\n        self.bdd_file=DataBase.fileTrainDb\n        self.table = \"train_test_data\"\n        self.nb_rows = 0\n        #create file\n#         with open(self.bdd_file, 'w') as f:\n#             pass\n        self.connexion = sqlite3.connect(self.bdd_file)\n        self.cursor = self.connexion.cursor()\n        \n    #=======================================================#   \n    def create_table(self):\n        \"\"\"\"\"\"\n        req = \"CREATE TABLE IF NOT EXISTS \"+self.table+\\\n                            \" (\"+ ','.join(['%s %s'%(c,columns_types[c])\\\n                                            for c in DataBase.columns])+\")\"\n        #print(req)\n        self.cursor.execute(req)\n        self.connexion.commit()\n        \n    #=======================================================#\n    def file_csv_to_SQLite(self):\n        \"\"\"\"\"\"\n        nb_rows = 0\n        #train_v2\n        print('train v2 ...')\n        for dt in pd.read_csv(DataBase.fileCsvTrain,chunksize=5*10**4, iterator=True,\n                               converters={column: json.loads for column in JSON_COLUMNS}, \n                    dtype={\"fullVisitorId\": \"str\"}):\n            data = json_normalize_data(dt.copy())\n            print(\"chunk, \",data.shape)\n            for index, row in data.iterrows():\n                nb_rows += 1\n                row_values = [nb_rows] + [row[c] for c in DataBase.columns[1:-1]] + [1]\n                self.cursor.execute(\"INSERT INTO \" + self.table +\\\n                                    \"(%s) VALUES(%s)\"%(\n                        ','.join([str(c) for c in DataBase.columns]),\n                    ','.join(['?' for c in DataBase.columns]),\n                ), tuple([str(v) for v in row_values]))\n            del data\n            gc.collect()\n            del gc.garbage[:]\n                \n                \n                \n        #test_v2\n        print(\"test v2 ...\")\n        for dt in pd.read_csv(DataBase.fileCsvTest,chunksize=5*10**4, iterator=True,\n                               converters={column: json.loads for column in JSON_COLUMNS}, \n                    dtype={\"fullVisitorId\": \"str\"}):\n            data = json_normalize_data(dt.copy())\n            print(\"chunk, \",data.shape)\n            for index, row in data.iterrows():\n                nb_rows += 1\n                row_values = [nb_rows] + [row[c] for c in DataBase.columns[1:-1]] + [0]\n                self.cursor.execute(\"INSERT INTO \" + self.table +\\\n                                    \"(%s) VALUES(%s)\"%(\n                        ','.join([str(c) for c in DataBase.columns]),\n                    ','.join(['?' for c in DataBase.columns]),\n                ), tuple([str(v) for v in row_values]))\n            del data\n            gc.collect()\n            del gc.garbage[:]\n            \n        print(\"Done storing data with %d rows\" % nb_rows)\n        self.nb_rows = nb_rows\n        self.connexion.commit()\n        \n        \n        \n    #=======================================================#    \n    def close_connexion_cursor(self):\n        \"\"\"\"\"\"\n        self.cursor.close()\n        self.connexion.close()\n        print(\"Connexion and Cursor closed\")\n        \n    #=======================================================#\n    def select_all_rows(self):\n        \"\"\"\"\"\"\n        self.cursor.execute(\"SELECT * FROM \" + self.table + \"\")\n        for l in self.cursor:\n            yield l\n\n    #=======================================================#\n    def get_line_by_index(self,index):\n        \"\"\"\"\"\"\n        self.cursor.execute(\"SELECT * FROM \" + self.table + \" where id=?\", (str(index),))\n        for line in self.cursor:\n            return line\n        return None\n    \n    #=======================================================#\n    def get_lines_between(self, start_id, end_id):\n        \"\"\"\"\"\"\n        self.cursor.execute(\"SELECT * FROM \" + self.table + \" where id>=? and id<=?\",(str(start_id),str(end_id)))\n        return self.cursor.fetchall()\n\n    #=======================================================#\n    def create_index_column(self, column='id'):\n        \"\"\"C\"\"\"\n        self.cursor.execute(\"CREATE INDEX id_index on \" + self.table + \" (%s)\"%column)\n        self.connexion.commit()\n        print(\"% indexed\" + str(column))\n        \n    #=======================================================#\n    def clear_table(self):\n        \"\"\"Vider une table\"\"\"\n        self.cursor.execute(\"DELETE FROM \" + self.table + \"\")\n        self.connexion.commit()\n\n    #=======================================================#   \n    def get_features_names(self):\n        \"\"\"\"\"\"\n        return DataBase.columns[1:-1]\n    \n    def generate_init_sub(self):\n        self.cursor.execute(\"SELECT DISTINCT fullVisitorId FROM \" + self.table)\n        sub = pd.DataFrame.from_dict({'fullVisitorId':[l[0] for l in self.cursor.fetchall()]})\n        sub['PredictedLogRevenue'] = 0\n        return sub","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3735d5cb22e4e48b704a1265a14f86fd9ccb60fe"},"cell_type":"code","source":"db = DataBase()\ndb.create_table()\ndb.file_csv_to_SQLite()\ndb.create_index_column()\nsubmission = db.generate_init_sub()\ndb.close_connexion_cursor()\n\ndel db\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"58601d73b2edef9b6adbf9675c6550bfc1eef800"},"cell_type":"code","source":"submission.to_csv(\"to_submit.csv\", index=False)","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}