{"cells":[{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"%%time\n\npip install pyspark -q","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"from pyspark.sql import SparkSession, DataFrame, Window, functions as F, types as T\n#from pyspark.ml.feature import Bucketizer\nspark = SparkSession.builder.appName('_').getOrCreate()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import numpy as np","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\n\ndef train_to_tmp():\n\n    train_schema = T.StructType([\n        T.StructField('row_id',                          T.LongType(),    False),\n        T.StructField('timestamp',                       T.LongType(),    False),\n        T.StructField('user_id',                         T.IntegerType(), False),\n        T.StructField('content_id',                      T.ShortType(),   False),\n        T.StructField('content_type_id',                 T.ByteType(),    False),\n        T.StructField('task_container_id',               T.ShortType(),   False),\n        T.StructField('user_answer',                     T.ByteType(),    False),\n        T.StructField('answered_correctly',              T.ByteType(),    False),\n        T.StructField('prior_question_elapsed_time',     T.FloatType(),   False),\n        T.StructField('prior_question_had_explanation',  T.BooleanType(), False),\n    ])\n\n    train = spark\\\n    .read.csv('../input/riiid-test-answer-prediction/train.csv',\n              schema = train_schema,\n              enforceSchema = True,\n              header = True)\\\n    .withColumn('prior_question_had_explanation', F.col('prior_question_had_explanation').astype(T.ByteType()))\n    #.na.fill(-1)\n\n    train.write.parquet('tmp', mode = 'overwrite')\n\n\ntrain_to_tmp()\ntmp = spark.read.parquet('./tmp')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\n\ntmp\\\n.filter(F.col('content_type_id')==0)\\\n.select(['user_id','timestamp','task_container_id']).distinct()\\\n.withColumn('new_order', \n            F.row_number().over(\n                Window.partitionBy('user_id').orderBy('timestamp')\n            )-1)\\\n.write.parquet('utc', mode = 'overwrite')\nutc = spark.read.parquet('./utc')\n\n\ntmp\\\n.join(utc,on=['user_id','timestamp','task_container_id'], how = 'inner')\\\n.write.parquet('tmp_new', mode = 'overwrite')\n\n!rm -rf  ./tmp\n!rm -rf  ./utc\n\ndf = spark.read.parquet('./tmp_new')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\n\nuo_tm = df.groupBy(['user_id','new_order']).agg(F.mean('prior_question_elapsed_time').alias('question_elapsed_time'))\nuo_tm = uo_tm.withColumn('new_order', F.col('new_order')-1)\ndf = df.join(uo_tm, on = ['user_id','new_order'], how = 'left')\ndf.write.parquet('df_new', mode = 'overwrite')\ndf = spark.read.parquet('./df_new')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\n\ndfp = df\\\n.filter(F.col('content_type_id')==0)\\\n.groupBy('content_id')\\\n.agg(F.mean('question_elapsed_time').alias('mean_question_elapsed_time')).toPandas()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"qm = dfp\\\n.sort_values(by = 'content_id')\\\n.mean_question_elapsed_time\\\n.to_numpy(dtype = np.float32)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"np.save('question_mean_time', qm)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"!rm -rf  ./tmp_new\n!rm -rf  ./df_new","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}