{"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":"markdown","source":"

Welcome!

\n\n
This notebook contains a mixture of Pandas and PySpark code to perform data analysis and anomaly detection on the given dataset.
\n
\n\n## **Features**\n___\n- Uses both Pandas and PySpark for data processing and analysis.\n- Uses caching to take advantage of the limited memory situation.\n- [Analyzes missing data in all the columns.](#1)\n- [Which columns containig object data type can potentially be used as categorical columns.](#2)\n- [Only event `notebook_click` has `page` info, checks if that is true](#3) \n- [Shows which events are more likely to occur in different levels of the game](#4)\n- [Provides anomaly detection for the given dataset in the `text` column.](#5)\n- [Provides pattern detection for the `text` column](#6)\n\n## **Data Analysis**\n___\nData analysis is performed using Pandas and PySpark. PySpark is used to perform distributed computing on large datasets, while Pandas is used to perform data manipulation and exploration.\n\n## **Anomaly Detection & Pattern Analysis of Text data.**\n\n* Anomaly detection is performed by identifying `text` anomalies in the dataset. \n* Rows containing text containing 7, 8, or 10 splits are flagged as anomalies.\n\n
\n\n

Quick look at the columns (paraphasing the general understandings only)

\n\n___\n\n* **session_id:** A unique identifier for each gameplay session\n\n* **index:** A number indicating the order in which each event occurred within a session\n\n* **elapsed_time:** How much time has passed in the session (in milliseconds) when each event was recorded\n\n* **event_name:** A description of the type of event that occurred (e.g. click, hover, etc.)\n\n* **name:** A more specific description of the event that occurred (e.g. which button was clicked)\n\n* **level:** The level of the game where the event occurred (ranging from 0 to 22)\n\n* **page:** The page number of the event (only for notebook-related events)\n\n* **room_coor_x, room_coor_y:** The coordinates of the player's click within the in-game room (only for click events)\n\n* **screen_coor_x, screen_coor_y:** The coordinates of the player's click within the player's screen (only for click events)\n\n* **hover_duration:** How long the player hovered over an object (in milliseconds)\n\n* **text:** The text that the player saw during the event\n\n* **fqid:** A unique identifier for each event\n\n* **room_fqid:** A unique identifier for the room where the event occurred\n\n* **text_fqid:** A unique identifier for the text that the player saw during the event\n\n* **fullscreen:** Whether the player was in fullscreen mode\n \n* **hq:** Whether the game was in high-quality\n \n* **music:** Whether the game music was on or off\n \n* **level_group:** Which group of levels the event belongs to (0-4, 5-12, or 13-22)\n\n\n\n\n*This notebook showcases the use of both Pandas and PySpark for data analysis and anomaly detection. The code is written in a clear and concise manner, making it easy to follow along and understand the steps involved in the analysis.*\n___","metadata":{}},{"cell_type":"markdown","source":"### **Import Pandas**","metadata":{}},{"cell_type":"code","source":"import pandas as pd","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":false,"execution":{"iopub.status.busy":"2023-04-12T13:34:15.776863Z","iopub.execute_input":"2023-04-12T13:34:15.777571Z","iopub.status.idle":"2023-04-12T13:34:15.814109Z","shell.execute_reply.started":"2023-04-12T13:34:15.777528Z","shell.execute_reply":"2023-04-12T13:34:15.812892Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### **Installing PySpark**","metadata":{}},{"cell_type":"code","source":"!pip install pyspark","metadata":{"_kg_hide-output":true,"execution":{"iopub.status.busy":"2023-04-12T13:34:15.815942Z","iopub.execute_input":"2023-04-12T13:34:15.816570Z","iopub.status.idle":"2023-04-12T13:34:56.399441Z","shell.execute_reply.started":"2023-04-12T13:34:15.816525Z","shell.execute_reply":"2023-04-12T13:34:56.398153Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Loading the dataset","metadata":{}},{"cell_type":"code","source":"from pyspark.sql import SparkSession\n# create a SparkSession object\nspark = SparkSession.builder.appName('predict-student-performance').getOrCreate()","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:34:56.400774Z","iopub.execute_input":"2023-04-12T13:34:56.401068Z","iopub.status.idle":"2023-04-12T13:35:01.854053Z","shell.execute_reply.started":"2023-04-12T13:34:56.401035Z","shell.execute_reply":"2023-04-12T13:35:01.853149Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# reading the data & caching it using `persist()`\ntrain = spark.read.csv('/kaggle/input/predict-student-performance-from-game-play/train.csv',\n header=True, \n inferSchema=True)","metadata":{"_kg_hide-output":true,"execution":{"iopub.status.busy":"2023-04-12T13:35:01.856005Z","iopub.execute_input":"2023-04-12T13:35:01.856294Z","iopub.status.idle":"2023-04-12T13:36:08.255905Z","shell.execute_reply.started":"2023-04-12T13:35:01.856262Z","shell.execute_reply":"2023-04-12T13:36:08.255091Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# inspect infered schema\ntrain.printSchema()","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:36:08.256748Z","iopub.execute_input":"2023-04-12T13:36:08.257004Z","iopub.status.idle":"2023-04-12T13:36:08.273044Z","shell.execute_reply.started":"2023-04-12T13:36:08.256976Z","shell.execute_reply":"2023-04-12T13:36:08.272261Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Check first five rows of the training data\n___","metadata":{}},{"cell_type":"code","source":"train_head=train.limit(5).toPandas()\ntrain_head","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:36:08.274052Z","iopub.execute_input":"2023-04-12T13:36:08.275060Z","iopub.status.idle":"2023-04-12T13:36:08.621058Z","shell.execute_reply.started":"2023-04-12T13:36:08.275028Z","shell.execute_reply":"2023-04-12T13:36:08.620156Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### **The Dataset contains following Columns and Datatypes**\n___","metadata":{}},{"cell_type":"code","source":"train_head.dtypes.to_frame(name=\"Data Types\")","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:36:08.624832Z","iopub.execute_input":"2023-04-12T13:36:08.626886Z","iopub.status.idle":"2023-04-12T13:36:08.642509Z","shell.execute_reply.started":"2023-04-12T13:36:08.626841Z","shell.execute_reply":"2023-04-12T13:36:08.641022Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"

Missing Data Inspection

\n\n___","metadata":{}},{"cell_type":"code","source":"from pyspark.sql.functions import col, sum\n\n# Count the number of missing values in each column \nmissing_value_counts = train.agg(*[sum(col(c).isNull().cast(\"int\")).alias(c) for c in train.columns])\nmissing_value_counts=missing_value_counts.toPandas()\nmissing_value_counts","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:36:08.647915Z","iopub.execute_input":"2023-04-12T13:36:08.649993Z","iopub.status.idle":"2023-04-12T13:36:57.800565Z","shell.execute_reply.started":"2023-04-12T13:36:08.649949Z","shell.execute_reply":"2023-04-12T13:36:57.799677Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Total Rows of Data","metadata":{}},{"cell_type":"code","source":"total_rows=train.select(\"session_id\").count() # for faster processing we are usning one column\nprint(f\"Total rows:{total_rows}\")","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:36:57.804384Z","iopub.execute_input":"2023-04-12T13:36:57.806010Z","iopub.status.idle":"2023-04-12T13:37:03.925487Z","shell.execute_reply.started":"2023-04-12T13:36:57.805943Z","shell.execute_reply":"2023-04-12T13:37:03.924798Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#In this list comprehension we store data \ncolumn_with_missing_vals,data_type, missing_value_percentage = zip(*[(col, \n train_head[col].dtype, \n round(((missing_value_counts[col][0])/total_rows)*100,2)) \n for col in train.columns if missing_value_counts[col][0]>0])","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:37:03.931071Z","iopub.execute_input":"2023-04-12T13:37:03.931393Z","iopub.status.idle":"2023-04-12T13:37:03.944269Z","shell.execute_reply.started":"2023-04-12T13:37:03.931366Z","shell.execute_reply":"2023-04-12T13:37:03.943046Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(f\"{len(column_with_missing_vals)} Features contain missing values\")","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:37:03.947672Z","iopub.execute_input":"2023-04-12T13:37:03.949938Z","iopub.status.idle":"2023-04-12T13:37:03.955617Z","shell.execute_reply.started":"2023-04-12T13:37:03.949875Z","shell.execute_reply":"2023-04-12T13:37:03.954367Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Plotting the Missing value percentage in each column","metadata":{}},{"cell_type":"code","source":"import matplotlib.pyplot as plt\nnum_rows = 2\nnum_cols = 7\n\n# Create subplots with the specified number of rows and columns\nfig, axes = plt.subplots(num_rows, num_cols, figsize=(20,10))\n\n# Flatten the axes array for easy iteration\naxes = axes.flatten()\n\n# Plot each column with missing values as a pie chart\nfor i, col in enumerate(column_with_missing_vals):\n # Calculate the percentage of missing values\n missing_percent = missing_value_percentage[i]\n present_percent = 100 - missing_percent\n \n # Create a pie chart with missing and present values\n axes[i].pie([missing_percent, present_percent],\n autopct='%1.1f%%', startangle=90, colors=['#CE3E3E', '#7CB342'])\n axes[i].set_title(f\"{col}[{data_type[i]}]\")\n\n# Remove unused subplots and adjust spacing\n# for i in range(len(column_with_missing_vals), num_rows*num_cols):\n# fig.delaxes(axes[i])\nfig.tight_layout()\nfig.legend(['Missing', 'Present'], loc='upper right')\n\n# Set the plot title\nfig.suptitle('Percentage of Missing Values in Columns',fontweight='bold',fontsize=16)\n\n# Show the plot\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:37:03.956810Z","iopub.execute_input":"2023-04-12T13:37:03.958012Z","iopub.status.idle":"2023-04-12T13:37:05.154176Z","shell.execute_reply.started":"2023-04-12T13:37:03.957975Z","shell.execute_reply":"2023-04-12T13:37:05.152764Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"

Which of the Object type columns could be categorical? 🤔

\n\n___","metadata":{}},{"cell_type":"code","source":"#from pyspark_dist_explore import hist\nfrom pyspark.sql.types import StringType\n# here we are going to make use of the 'train_head' to extract the datatypes easily 😜\ncols = train_head.select_dtypes(include='object').columns.tolist()\nvals=[train.select(col).distinct().count() for col in cols]","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:37:05.155132Z","iopub.execute_input":"2023-04-12T13:37:05.155410Z","iopub.status.idle":"2023-04-12T13:39:55.651418Z","shell.execute_reply.started":"2023-04-12T13:37:05.155380Z","shell.execute_reply":"2023-04-12T13:39:55.650555Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"unique_vals = pd.DataFrame({'cols': cols, 'unique_vals': vals})\n# Sort DataFrame based on unique_vals column\nunique_vals = unique_vals.sort_values(by='unique_vals', ascending=True).reset_index(drop=True)\nplt.bar('cols','unique_vals',data=unique_vals)\nplt.xticks(rotation=90)\nplt.title('Unique value count for object Data Types')\nplt.xlabel('Column Names')\nplt.ylabel('Counts')\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:39:55.652249Z","iopub.execute_input":"2023-04-12T13:39:55.652514Z","iopub.status.idle":"2023-04-12T13:39:55.839393Z","shell.execute_reply.started":"2023-04-12T13:39:55.652484Z","shell.execute_reply":"2023-04-12T13:39:55.838226Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"`level_group`,`event_name` and `name` should be categorized","metadata":{}},{"cell_type":"markdown","source":"

Only event 'notebook_click' has 'page' info. Let's check if there are any outliers.

\n\n___\n\n\n

event_name` vs page

","metadata":{}},{"cell_type":"code","source":"from pyspark.sql.functions import col\nunique_event_names = train.select(col(\"event_name\")).distinct().rdd.flatMap(lambda x: x).collect()\n","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:39:55.841841Z","iopub.execute_input":"2023-04-12T13:39:55.842181Z","iopub.status.idle":"2023-04-12T13:40:20.760119Z","shell.execute_reply.started":"2023-04-12T13:39:55.842154Z","shell.execute_reply":"2023-04-12T13:40:20.759293Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"\\n\".join(unique_event_names))","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:40:20.760914Z","iopub.execute_input":"2023-04-12T13:40:20.761177Z","iopub.status.idle":"2023-04-12T13:40:20.767914Z","shell.execute_reply.started":"2023-04-12T13:40:20.761151Z","shell.execute_reply":"2023-04-12T13:40:20.766499Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from pyspark.sql.functions import col, isnull\n\nevent_vs_page = train.select(\"event_name\", \"page\").filter(col(\"event_name\") == \"notebook_click\")\nevent_vs_page.cache() # caching the dataframe ","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:40:20.769266Z","iopub.execute_input":"2023-04-12T13:40:20.769529Z","iopub.status.idle":"2023-04-12T13:40:20.862286Z","shell.execute_reply.started":"2023-04-12T13:40:20.769494Z","shell.execute_reply":"2023-04-12T13:40:20.861533Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from pyspark.sql.functions import min, max\n\n# Find the maximum value in the \"page\" column\nmax_value = event_vs_page.agg(max(\"page\")).take(1)[0][0]\nmin_value = event_vs_page.agg(min(\"page\")).take(1)[0][0]\n\n# Print the maximum value\nprint(\"Max value in page: \", max_value)\nprint(\"Min value in page: \", min_value)\n","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:40:20.863129Z","iopub.execute_input":"2023-04-12T13:40:20.863387Z","iopub.status.idle":"2023-04-12T13:40:42.718325Z","shell.execute_reply.started":"2023-04-12T13:40:20.863360Z","shell.execute_reply":"2023-04-12T13:40:42.717446Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"event_vs_page.count()==(total_rows-missing_value_counts['page'][0])","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:40:42.719203Z","iopub.execute_input":"2023-04-12T13:40:42.719484Z","iopub.status.idle":"2023-04-12T13:40:43.024740Z","shell.execute_reply.started":"2023-04-12T13:40:42.719452Z","shell.execute_reply":"2023-04-12T13:40:43.023876Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"event_vs_page.unpersist() #remove it from cache","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:40:43.025652Z","iopub.execute_input":"2023-04-12T13:40:43.025926Z","iopub.status.idle":"2023-04-12T13:40:43.046317Z","shell.execute_reply.started":"2023-04-12T13:40:43.025895Z","shell.execute_reply":"2023-04-12T13:40:43.045627Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Conclusion:**\n___\n* `page` column only contains values for `notebook_click` events","metadata":{}},{"cell_type":"markdown","source":"

Which events are more likely to occur in different levels of the game?

\n\n___","metadata":{}},{"cell_type":"code","source":"level_vs_event = train.groupBy(\"event_name\").pivot(\"level\").agg({\"event_name\": \"count\"})\nlevel_vs_event.cache()","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:40:43.047073Z","iopub.execute_input":"2023-04-12T13:40:43.047314Z","iopub.status.idle":"2023-04-12T13:41:06.508329Z","shell.execute_reply.started":"2023-04-12T13:40:43.047287Z","shell.execute_reply":"2023-04-12T13:41:06.507581Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"level_vs_event=level_vs_event.toPandas()","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:41:06.509108Z","iopub.execute_input":"2023-04-12T13:41:06.509352Z","iopub.status.idle":"2023-04-12T13:41:36.669245Z","shell.execute_reply.started":"2023-04-12T13:41:06.509326Z","shell.execute_reply":"2023-04-12T13:41:36.667963Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"level_vs_event","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:41:36.673540Z","iopub.execute_input":"2023-04-12T13:41:36.675706Z","iopub.status.idle":"2023-04-12T13:41:36.718163Z","shell.execute_reply.started":"2023-04-12T13:41:36.675661Z","shell.execute_reply":"2023-04-12T13:41:36.716859Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import matplotlib.pyplot as plt\n\n# Generate the subplots with 4 rows and 6 columns\nfig, axs = plt.subplots(nrows=4, ncols=6, figsize=(40, 20))\n\n# Flatten the axs array so that we can iterate over it more easily\naxs = axs.flatten()\n# Loop over each column in the level_vs_event DataFrame and create a boxplot\nfor i, col in enumerate(level_vs_event.columns[1:]):\n if i>22:\n break\n else:\n ax = axs[i]\n # Create the plot for the current column\n level_vs_event.plot(x='event_name', y=col, kind='bar', ax=ax,legend=False)\n\n # Set the title for the current axis\n ax.set_title(f\"Level: {col}\")\n \n \n \n# Adjust the layout of the subplots\nplt.suptitle(\"Which events are more likely to occur in different levels?\\n\",fontsize=24,fontweight=\"bold\")\nplt.subplots_adjust(wspace=0.3, hspace=0.5)\nplt.tight_layout()\n\n# Show the figure\nplt.show()\n","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:41:36.722581Z","iopub.execute_input":"2023-04-12T13:41:36.724709Z","iopub.status.idle":"2023-04-12T13:41:40.849655Z","shell.execute_reply.started":"2023-04-12T13:41:36.724664Z","shell.execute_reply":"2023-04-12T13:41:40.848171Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"

Are there any Inconsistent Data in the text column?

\n\n___\n","metadata":{}},{"cell_type":"code","source":"from pyspark.sql.functions import monotonically_increasing_id\n# Selecting the columns we need\nsess_idx_txt=train.select(\"session_id\", \"index\",\"text\")\n\n# Adding a row number to track anomalies\nsess_idx_txt=sess_idx_txt.withColumn('row_index', monotonically_increasing_id())\n\n# Let's put the row_index column at front\nsess_idx_txt = sess_idx_txt.select('row_index', *sess_idx_txt.columns[:-1])","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:41:40.851132Z","iopub.execute_input":"2023-04-12T13:41:40.851466Z","iopub.status.idle":"2023-04-12T13:41:40.896453Z","shell.execute_reply.started":"2023-04-12T13:41:40.851433Z","shell.execute_reply":"2023-04-12T13:41:40.895576Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sess_idx_txt.cache()","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:41:40.897341Z","iopub.execute_input":"2023-04-12T13:41:40.897605Z","iopub.status.idle":"2023-04-12T13:41:40.921155Z","shell.execute_reply.started":"2023-04-12T13:41:40.897559Z","shell.execute_reply":"2023-04-12T13:41:40.919785Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sess_idx_txt.orderBy(\"session_id\",\"index\").show(5)","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:41:40.922713Z","iopub.execute_input":"2023-04-12T13:41:40.923296Z","iopub.status.idle":"2023-04-12T13:42:48.189285Z","shell.execute_reply.started":"2023-04-12T13:41:40.923259Z","shell.execute_reply":"2023-04-12T13:42:48.188462Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Let's try and split the texts by '.' and see if we can detect some anomaly.","metadata":{}},{"cell_type":"code","source":"from pyspark.sql.functions import length,size,split,monotonically_increasing_id\n\n#Let's just split the texts with '.' and see what pops up, going on a blind intuition here.\nleng=sess_idx_txt.withColumn(\"text_splits\", size(split(sess_idx_txt['text'], '\\.')))\n","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:42:48.195325Z","iopub.execute_input":"2023-04-12T13:42:48.196056Z","iopub.status.idle":"2023-04-12T13:42:48.219709Z","shell.execute_reply.started":"2023-04-12T13:42:48.196011Z","shell.execute_reply":"2023-04-12T13:42:48.218398Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sess_idx_txt.unpersist()","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:42:48.220814Z","iopub.execute_input":"2023-04-12T13:42:48.221285Z","iopub.status.idle":"2023-04-12T13:42:48.301316Z","shell.execute_reply.started":"2023-04-12T13:42:48.221250Z","shell.execute_reply":"2023-04-12T13:42:48.299890Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"leng.show(5)","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:42:48.305838Z","iopub.execute_input":"2023-04-12T13:42:48.306213Z","iopub.status.idle":"2023-04-12T13:42:48.432327Z","shell.execute_reply.started":"2023-04-12T13:42:48.306175Z","shell.execute_reply":"2023-04-12T13:42:48.431583Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Let's check the min and max values in the 'text_splits' column, because... erm... I have no idea what awaits. \nmax_value = leng.agg(max(\"text_splits\")).take(1)[0][0]\nmin_value = leng.agg(min(\"text_splits\")).take(1)[0][0]\n\n# Print the minimum and maximum values\nprint(\"Max value in page: \", max_value)\nprint(\"Min value in page: \", min_value)","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:42:48.433115Z","iopub.execute_input":"2023-04-12T13:42:48.433341Z","iopub.status.idle":"2023-04-12T13:43:38.290483Z","shell.execute_reply.started":"2023-04-12T13:42:48.433316Z","shell.execute_reply":"2023-04-12T13:43:38.289729Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from tabulate import tabulate\n\n# Assuming that leng is a dataframe with a 'text_splits' column\nunique_values = leng.select('text_splits').distinct().collect()\n\n# Convert the list of Row objects to a list of values\nunique_values = [row[0] for row in unique_values]","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:43:38.291314Z","iopub.execute_input":"2023-04-12T13:43:38.291580Z","iopub.status.idle":"2023-04-12T13:44:02.951450Z","shell.execute_reply.started":"2023-04-12T13:43:38.291544Z","shell.execute_reply":"2023-04-12T13:44:02.950361Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# let's sort out the unique_values\nunique_values = sorted(unique_values)\n#just being fancy here\nprint(\"Number of text splits:\")\nprint(tabulate([unique_values],tablefmt='psql', stralign='center'))","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:44:02.952327Z","iopub.execute_input":"2023-04-12T13:44:02.952582Z","iopub.status.idle":"2023-04-12T13:44:02.962894Z","shell.execute_reply.started":"2023-04-12T13:44:02.952558Z","shell.execute_reply":"2023-04-12T13:44:02.961860Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"##### Interesting! the split was successful I'd say. But we aren't finished here\n##### Let's check out some of the sample texts that has the available split by '.'","metadata":{}},{"cell_type":"code","source":"for j in unique_values:\n print(f\"\\033[34;4mFor \\033[0m{j} \\033[34;4m text split(s)\\033[0m\".center(100))\n for i in leng.select(leng.row_index,leng.text).filter(leng.text_splits == j).head(5):\n #Printing the row where the anomaly exists\n print(\"\\n\\033[31mRow Number:\\033[0m \",i.row_index) # Those numbers are ANSI escape codes for fancy outputs in color. \n #printing the text containing anomaly\n print(\"\\n\\033[32mText:\\033[35m \",i.text)\n #printing the data type\n print(\"\\033[33m\\nText Data Type: \",type(i.text))\n print(\"\\033[32m------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\033[0m\")","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:44:02.965139Z","iopub.execute_input":"2023-04-12T13:44:02.968075Z","iopub.status.idle":"2023-04-12T13:44:03.929160Z","shell.execute_reply.started":"2023-04-12T13:44:02.968039Z","shell.execute_reply":"2023-04-12T13:44:03.925743Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Looks like unique values with 7, 8, 10 length has some unrecognizable values ","metadata":{}},{"cell_type":"markdown","source":"Checking the heads","metadata":{}},{"cell_type":"code","source":"for j in [7,8,10]:\n print(f\"\\033[34;4mFor \\033[0m{j} \\033[34;4m text split(s)\\033[0m\".center(100))\n for i in leng.select(leng.row_index,leng.text).filter(leng.text_splits == j).head(5):\n #Printing the row where the anomaly exists\n print(\"\\033[31mAnomaly Detected! at row:\\033[0m \",i.row_index) # Those numbers are ANSI escape codes for fancy outputs in color. \n #printing the text containing anomaly\n print(\"\\n\\033[32mText:\\033[35m \",i.text)\n #printing the data type\n print(\"\\033[33m\\nText Data Type: \",type(i.text))\n print(\"\\033[32m------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\033[0m\")","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:44:03.933683Z","iopub.execute_input":"2023-04-12T13:44:03.934090Z","iopub.status.idle":"2023-04-12T13:44:04.188407Z","shell.execute_reply.started":"2023-04-12T13:44:03.934054Z","shell.execute_reply":"2023-04-12T13:44:04.187653Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Checking the tails","metadata":{}},{"cell_type":"code","source":"for j in [7,8,10]:\n print(f\"\\033[34;4mFor \\033[0m{j} \\033[34;4m text split(s)\\033[0m\".center(100))\n for i in leng.select(leng.row_index,leng.text).filter(leng.text_splits == j).tail(5):\n #Printing the row where the anomaly exists\n print(\"\\033[31mAnomaly Detected! at row:\\033[0m \",i.row_index) # Those numbers are ANSI escape codes for fancy outputs in color. \n #printing the text containing anomaly\n print(\"\\n\\033[32mText:\\033[35m \",i.text)\n #printing the data type\n print(\"\\033[33m\\nText Data Type: \",type(i.text))\n print(\"\\033[32m------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\033[0m\")","metadata":{"execution":{"iopub.status.busy":"2023-04-12T13:44:04.189196Z","iopub.execute_input":"2023-04-12T13:44:04.189444Z","iopub.status.idle":"2023-04-12T13:44:05.229410Z","shell.execute_reply.started":"2023-04-12T13:44:04.189415Z","shell.execute_reply":"2023-04-12T13:44:05.228172Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"rows_with_text_anomaly = leng.filter(leng.text_splits.isin([7, 8, 10]))\nrows_with_text_anomaly.show()","metadata":{"execution":{"iopub.status.busy":"2023-04-12T14:31:22.278147Z","iopub.execute_input":"2023-04-12T14:31:22.278805Z","iopub.status.idle":"2023-04-12T14:31:22.679402Z","shell.execute_reply.started":"2023-04-12T14:31:22.278748Z","shell.execute_reply":"2023-04-12T14:31:22.677547Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"

Conclusion

\n\n___ \n\n* It can be said that the `text` column doesn't contain all plain texts. \n* It looks like the `text_splits` column with 7,8,10 values contain visible anomalies \n* The above frame `rows_with_text_anomaly` gives us the row number, as well as `session_id` & `index` for us to use, in order to clean the texts (`text_leng` column can be used to detect different patterns in the text)\n","metadata":{}}]}