{"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_minor":4,"nbformat":4,"cells":[{"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\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\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 20GB 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","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2022-12-24T05:32:49.864852Z","iopub.execute_input":"2022-12-24T05:32:49.865725Z","iopub.status.idle":"2022-12-24T05:32:49.893244Z","shell.execute_reply.started":"2022-12-24T05:32:49.865630Z","shell.execute_reply":"2022-12-24T05:32:49.892080Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Notebook is for exploring the Pyspark Library On JSONL file\nTrain data for this competition contains, three simple features with lot of information held inside it. Article ID (aid), Time Stamp(of the event), event (the type of event). \n\nThe JSONL format has nested structure, which when loaded through the pandas reader requires additional parsing using hand written function. This format is common in the web site scraping, and mostly event logging. There is a very good [medium article](https://medium.com/hackernoon/json-lines-format-76353b4e588d) that explains in-detail about this format. \n\nThe challenge is how to read this 11+ GB file into the execution space? After looking around, I came across the Pyspark's \"explode\" function. Using the explode function, which returns a new row in a dataframe for each array or map element in the JSON/ JSONL input file.\n\nI am planning to make some Data Analysis out of this data, but this notebook will be about processing the input Train Data and writing out the parquet files for later usage. ","metadata":{}},{"cell_type":"code","source":"#Pyspark can be installed on Linux containers. I have written about [my experience here](https://medium.com/p/ee1837c9b366)\n! pip install pyspark","metadata":{"_kg_hide-output":true,"_kg_hide-input":false,"execution":{"iopub.status.busy":"2022-12-24T05:32:59.243905Z","iopub.execute_input":"2022-12-24T05:32:59.244304Z","iopub.status.idle":"2022-12-24T05:33:49.856376Z","shell.execute_reply.started":"2022-12-24T05:32:59.244273Z","shell.execute_reply":"2022-12-24T05:33:49.855113Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import pyspark\nfrom pyspark import SparkContext, SparkConf\nfrom pyspark.sql import SparkSession\nfrom pyspark.sql.functions import *\nfrom pyspark.sql.window import *","metadata":{"execution":{"iopub.status.busy":"2022-12-24T05:33:49.859187Z","iopub.execute_input":"2022-12-24T05:33:49.859615Z","iopub.status.idle":"2022-12-24T05:33:49.940841Z","shell.execute_reply.started":"2022-12-24T05:33:49.859580Z","shell.execute_reply":"2022-12-24T05:33:49.939361Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"help(explode)","metadata":{"execution":{"iopub.status.busy":"2022-12-24T00:29:11.123868Z","iopub.execute_input":"2022-12-24T00:29:11.124213Z","iopub.status.idle":"2022-12-24T00:29:11.130800Z","shell.execute_reply.started":"2022-12-24T00:29:11.124180Z","shell.execute_reply":"2022-12-24T00:29:11.129592Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"spark = SparkSession.builder.appName(\"ottoDB\").getOrCreate()","metadata":{"execution":{"iopub.status.busy":"2022-12-24T05:33:49.942581Z","iopub.execute_input":"2022-12-24T05:33:49.942990Z","iopub.status.idle":"2022-12-24T05:33:55.812535Z","shell.execute_reply.started":"2022-12-24T05:33:49.942955Z","shell.execute_reply":"2022-12-24T05:33:55.811464Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ottodata = spark.read.json(\"/kaggle/input/otto-recommender-system/train.jsonl\",lineSep='\\n')","metadata":{"execution":{"iopub.status.busy":"2022-12-24T05:33:55.815405Z","iopub.execute_input":"2022-12-24T05:33:55.816135Z","iopub.status.idle":"2022-12-24T05:35:27.905838Z","shell.execute_reply.started":"2022-12-24T05:33:55.816090Z","shell.execute_reply":"2022-12-24T05:35:27.904829Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ottodata.printSchema()","metadata":{"execution":{"iopub.status.busy":"2022-12-24T05:47:17.126503Z","iopub.execute_input":"2022-12-24T05:47:17.128841Z","iopub.status.idle":"2022-12-24T05:47:17.142199Z","shell.execute_reply.started":"2022-12-24T05:47:17.128767Z","shell.execute_reply":"2022-12-24T05:47:17.140761Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_data = spark.read.json(\"/kaggle/input/otto-recommender-system/test.jsonl\",lineSep='\\n')","metadata":{"execution":{"iopub.status.busy":"2022-12-24T05:35:27.944135Z","iopub.execute_input":"2022-12-24T05:35:27.944629Z","iopub.status.idle":"2022-12-24T05:35:31.881448Z","shell.execute_reply.started":"2022-12-24T05:35:27.944580Z","shell.execute_reply":"2022-12-24T05:35:31.880009Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from pyspark.sql.functions import explode, explode_outer\nottodata.show(2)","metadata":{"execution":{"iopub.status.busy":"2022-12-24T00:46:01.410716Z","iopub.execute_input":"2022-12-24T00:46:01.412404Z","iopub.status.idle":"2022-12-24T00:46:01.653730Z","shell.execute_reply.started":"2022-12-24T00:46:01.412340Z","shell.execute_reply":"2022-12-24T00:46:01.652321Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ottodata.printSchema()","metadata":{"execution":{"iopub.status.busy":"2022-12-24T00:46:49.672714Z","iopub.execute_input":"2022-12-24T00:46:49.673283Z","iopub.status.idle":"2022-12-24T00:46:49.681393Z","shell.execute_reply.started":"2022-12-24T00:46:49.673217Z","shell.execute_reply":"2022-12-24T00:46:49.679761Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ottodata.select(\"session\", explode('events').alias('events')).printSchema()","metadata":{"execution":{"iopub.status.busy":"2022-12-24T00:46:25.659487Z","iopub.execute_input":"2022-12-24T00:46:25.660007Z","iopub.status.idle":"2022-12-24T00:46:25.703844Z","shell.execute_reply.started":"2022-12-24T00:46:25.659971Z","shell.execute_reply":"2022-12-24T00:46:25.702420Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp_1 = ottodata.select(\"session\", explode('events').alias('events')) \\\n        .select(\"session\", \"events.aid\", \"events.ts\")","metadata":{"execution":{"iopub.status.busy":"2022-12-24T05:48:12.926778Z","iopub.execute_input":"2022-12-24T05:48:12.927350Z","iopub.status.idle":"2022-12-24T05:48:12.970921Z","shell.execute_reply.started":"2022-12-24T05:48:12.927308Z","shell.execute_reply":"2022-12-24T05:48:12.969748Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp_2 = ottodata.select(\"session\", explode('events').alias('events')) \\\n        .select(\"events.ts\", \"events.type\")","metadata":{"execution":{"iopub.status.busy":"2022-12-24T05:48:54.322628Z","iopub.execute_input":"2022-12-24T05:48:54.323088Z","iopub.status.idle":"2022-12-24T05:48:54.364375Z","shell.execute_reply.started":"2022-12-24T05:48:54.323055Z","shell.execute_reply":"2022-12-24T05:48:54.363059Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp_2.printSchema()","metadata":{"execution":{"iopub.status.busy":"2022-12-24T05:49:17.619790Z","iopub.execute_input":"2022-12-24T05:49:17.620234Z","iopub.status.idle":"2022-12-24T05:49:17.626732Z","shell.execute_reply.started":"2022-12-24T05:49:17.620200Z","shell.execute_reply":"2022-12-24T05:49:17.625522Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp_1.printSchema()","metadata":{"execution":{"iopub.status.busy":"2022-12-24T05:49:27.340814Z","iopub.execute_input":"2022-12-24T05:49:27.341255Z","iopub.status.idle":"2022-12-24T05:49:27.348463Z","shell.execute_reply.started":"2022-12-24T05:49:27.341221Z","shell.execute_reply":"2022-12-24T05:49:27.347218Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp_3 = temp_1.join(temp_2, on=['ts'])","metadata":{"execution":{"iopub.status.busy":"2022-12-24T05:51:23.452941Z","iopub.execute_input":"2022-12-24T05:51:23.453355Z","iopub.status.idle":"2022-12-24T05:51:23.485884Z","shell.execute_reply.started":"2022-12-24T05:51:23.453321Z","shell.execute_reply":"2022-12-24T05:51:23.484981Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp_3.printSchema()","metadata":{"execution":{"iopub.status.busy":"2022-12-24T05:51:27.592793Z","iopub.execute_input":"2022-12-24T05:51:27.593344Z","iopub.status.idle":"2022-12-24T05:51:27.600042Z","shell.execute_reply.started":"2022-12-24T05:51:27.593298Z","shell.execute_reply":"2022-12-24T05:51:27.598790Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ottodata.select(\"session\",\"events.element.aid\").printSchema()","metadata":{"execution":{"iopub.status.busy":"2022-12-24T00:49:18.240447Z","iopub.execute_input":"2022-12-24T00:49:18.240945Z","iopub.status.idle":"2022-12-24T00:49:18.339986Z","shell.execute_reply.started":"2022-12-24T00:49:18.240895Z","shell.execute_reply":"2022-12-24T00:49:18.338470Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ottoData_timed = ottodata.select(\"session\", explode(\"events\").alias(\"elements\")) \\\n    .select(\"session\", \"elements.aid\",date_format(to_timestamp(\"elements.ts\"),'dd-MM-yy HH:mm'). \\\n            alias(\"TimeStamp\"),\"elements.type\") ","metadata":{"execution":{"iopub.status.busy":"2022-11-26T01:33:54.197667Z","iopub.status.idle":"2022-11-26T01:33:54.197972Z","shell.execute_reply.started":"2022-11-26T01:33:54.197821Z","shell.execute_reply":"2022-11-26T01:33:54.197835Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ottoData_timed.show(2)","metadata":{"execution":{"iopub.status.busy":"2022-11-26T01:33:54.199426Z","iopub.status.idle":"2022-11-26T01:33:54.199740Z","shell.execute_reply.started":"2022-11-26T01:33:54.199600Z","shell.execute_reply":"2022-11-26T01:33:54.199614Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"testData_timed = test_data.select(\"session\", explode(\"events\").alias(\"elements\")) \\\n    .select(\"session\", \"elements.aid\",col(\"elements.ts\").cast(\"STRING\"). \\\n            alias(\"TimeStamp\"),\"elements.type\") ","metadata":{"execution":{"iopub.status.busy":"2022-11-26T01:33:54.201633Z","iopub.status.idle":"2022-11-26T01:33:54.202090Z","shell.execute_reply.started":"2022-11-26T01:33:54.201891Z","shell.execute_reply":"2022-11-26T01:33:54.201908Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"testData_timed.select(\"session\", \"aid\",to_utc_timestamp(\"TimeStamp\",'GMT') \\\n                ,\"type\").show(2) ","metadata":{"execution":{"iopub.status.busy":"2022-11-26T01:33:54.203306Z","iopub.status.idle":"2022-11-26T01:33:54.203644Z","shell.execute_reply.started":"2022-11-26T01:33:54.203491Z","shell.execute_reply":"2022-11-26T01:33:54.203507Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"testData_timed.show(5,truncate=False)","metadata":{"execution":{"iopub.status.busy":"2022-11-26T01:33:54.205426Z","iopub.status.idle":"2022-11-26T01:33:54.206033Z","shell.execute_reply.started":"2022-11-26T01:33:54.205744Z","shell.execute_reply":"2022-11-26T01:33:54.205770Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"testData_timed.printSchema()","metadata":{"execution":{"iopub.status.busy":"2022-11-26T01:33:54.208680Z","iopub.status.idle":"2022-11-26T01:33:54.209138Z","shell.execute_reply.started":"2022-11-26T01:33:54.208890Z","shell.execute_reply":"2022-11-26T01:33:54.208911Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"root\n |-- association_info: struct (nullable = true)\n |    |-- ancestry: array (nullable = true)\n |    |    |-- element: string (containsNull = true)\n |    |-- doi: string (nullable = true)\n |    |-- gwas_catalog_id: string (nullable = true)\n |    |-- neg_log_pval: double (nullable = true)\n |    |-- study_id: string (nullable = true)\n |    |-- pubmed_id: string (nullable = true)\n |    |-- url: string (nullable = true)\n |-- gold_standard_info: struct (nullable = true)\n |    |-- evidence: array (nullable = true)\n |    |    |-- element: struct (containsNull = true)\n |    |    |    |-- class: string (nullable = true)\n |    |    |    |-- confidence: string (nullable = true)\n |    |    |    |-- curated_by: string (nullable = true)\n |    |    |    |-- description: string (nullable = true)\n |    |    |    |-- pubmed_id: string (nullable = true)\n |    |    |    |-- source: string (nullable = true)\n |    |-- gene_id: string (nullable = true)\n |    |-- highest_confidence: string (nullable = true)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df1.select('association_info.study_id', \n           'gold_standard_info.evidence.element.description',\n          'gold_standard_info.gene_id')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"testData_timed = test_data.select(\"session\", explode(\"events\").alias(\"elements\")) \\\n    .select(\"session\", \"elements.aid\",col(\"elements.ts\").cast(\"STRING\"). \\\n            alias(\"TimeStamp\"),\"elements.type\") ","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Questions to Answer","metadata":{"execution":{"iopub.status.busy":"2022-11-24T17:19:10.701370Z","iopub.execute_input":"2022-11-24T17:19:10.701859Z","iopub.status.idle":"2022-11-24T17:21:33.562510Z","shell.execute_reply.started":"2022-11-24T17:19:10.701819Z","shell.execute_reply":"2022-11-24T17:21:33.561684Z"}}},{"cell_type":"markdown","source":"- How many unique sessions are there?\n\n- How long each session lasts? \n\n- How many events each session contains?\n\n- What the articles_IDs available in the said time line\n\n- How is the activity depending on each timeline\n","metadata":{}},{"cell_type":"code","source":"# What are the individual session_ids available\nottoData_timed.select(col(\"session\")).distinct().orderBy(\"session\").show()","metadata":{"execution":{"iopub.status.busy":"2022-11-26T01:33:54.211505Z","iopub.status.idle":"2022-11-26T01:33:54.211975Z","shell.execute_reply.started":"2022-11-26T01:33:54.211740Z","shell.execute_reply":"2022-11-26T01:33:54.211763Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# How many sessions are there? 12,899,779 sessions\nottoData_timed.select(col(\"session\")).distinct().count()","metadata":{"execution":{"iopub.status.busy":"2022-11-26T01:33:54.212976Z","iopub.status.idle":"2022-11-26T01:33:54.213432Z","shell.execute_reply.started":"2022-11-26T01:33:54.213214Z","shell.execute_reply":"2022-11-26T01:33:54.213235Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# How many clicks, carts and orders each sessions have\nottoData_timed.createOrReplaceTempView(\"ottoData\")\nspark.sql(\"\"\"SELECT type, COUNT(type) AS event_type\n                FROM ottoData\n                GROUP BY type\"\"\").show()","metadata":{"execution":{"iopub.status.busy":"2022-11-26T01:33:54.214719Z","iopub.status.idle":"2022-11-26T01:33:54.215170Z","shell.execute_reply.started":"2022-11-26T01:33:54.214926Z","shell.execute_reply":"2022-11-26T01:33:54.214947Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# How many events each session has \nspark.sql(\"\"\"SELECT session, type, COUNT(type) AS event_counts \n                FROM ottoData\n                GROUP BY session, type\n                ORDER BY session, COUNT(type) DESC\"\"\").show()","metadata":{"execution":{"iopub.status.busy":"2022-11-26T01:33:54.216359Z","iopub.status.idle":"2022-11-26T01:33:54.216798Z","shell.execute_reply.started":"2022-11-26T01:33:54.216583Z","shell.execute_reply":"2022-11-26T01:33:54.216604Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"spark.sql(\"\"\"SELECT aid, type, COUNT(type) AS event_counts \n                FROM ottoData\n                GROUP BY aid, type\n                ORDER BY aid, COUNT(type) DESC\"\"\").show()","metadata":{"execution":{"iopub.status.busy":"2022-11-26T01:33:54.218199Z","iopub.status.idle":"2022-11-26T01:33:54.218628Z","shell.execute_reply.started":"2022-11-26T01:33:54.218403Z","shell.execute_reply":"2022-11-26T01:33:54.218424Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ottoData_events = spark.sql(\"\"\"SELECT aid, type, COUNT(type) AS event_counts \n                FROM ottoData\n                WHERE type='clicks'\n                GROUP BY aid, type\n                ORDER BY aid, COUNT(type) DESC\"\"\")","metadata":{"execution":{"iopub.status.busy":"2022-11-26T01:33:54.219598Z","iopub.status.idle":"2022-11-26T01:33:54.220618Z","shell.execute_reply.started":"2022-11-26T01:33:54.220374Z","shell.execute_reply":"2022-11-26T01:33:54.220397Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Which days there has been more events\nottodata_dailyData = spark.sql(\"\"\"SELECT session, SUBSTRING(TimeStamp,1,8) AS day,\n            SUBSTRING(TimeStamp,9,6) AS timeofDay,\n            SUBSTRING(TimeStamp,9,3) AS hourofDay,\n            type\n            FROM ottoData\"\"\")","metadata":{"execution":{"iopub.status.busy":"2022-11-26T01:33:54.222428Z","iopub.status.idle":"2022-11-26T01:33:54.222868Z","shell.execute_reply.started":"2022-11-26T01:33:54.222656Z","shell.execute_reply":"2022-11-26T01:33:54.222677Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ottodata_dailyData.printSchema()","metadata":{"execution":{"iopub.status.busy":"2022-11-26T01:33:54.224300Z","iopub.status.idle":"2022-11-26T01:33:54.224738Z","shell.execute_reply.started":"2022-11-26T01:33:54.224517Z","shell.execute_reply":"2022-11-26T01:33:54.224539Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"type(ottoData_events)","metadata":{"execution":{"iopub.status.busy":"2022-11-26T01:33:54.225904Z","iopub.status.idle":"2022-11-26T01:33:54.226335Z","shell.execute_reply.started":"2022-11-26T01:33:54.226122Z","shell.execute_reply":"2022-11-26T01:33:54.226142Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ottoData_events","metadata":{"execution":{"iopub.status.busy":"2022-11-26T01:33:54.227912Z","iopub.status.idle":"2022-11-26T01:33:54.228422Z","shell.execute_reply.started":"2022-11-26T01:33:54.228207Z","shell.execute_reply":"2022-11-26T01:33:54.228229Z"},"trusted":true},"execution_count":null,"outputs":[]}]}