{"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":"### ASL-Sign dataset - DuckDB notebook 🦆 🚀 🪶\n\nI have created a [DuckDB](https://duckdb.org) database from the [ASL-Sign dataset](https://www.kaggle.com/competitions/asl-signs/data). The database used in this notebook is available for download from [here](https://www.kaggle.com/datasets/habedi/asl-sign-training-dataset-duckdb-format). I made the database because I think it is a good idea to use DuckDB (which is a serverless in-memory OLAP database) to store the data because it is much easier and even faster to query the data from an OLAP database than from parquet files using Python. DuckDB allows us to use SQL (more precisely, a subset of PostgreSQL's dialect) to query the data. Furthermore, I have created this notebook to demonstrate how to connect to the database and query the data.\n\nAnyhow, the database has two tables: `train_data` and `train_labels`. The `train_data` table contains the sequences of landmarks (lm), and the `train_labels` table contains the labels for those sequences.\n\nThe `train_data` table has the following columns:\n  * `user_id`: the id of the user who recorded the sequence of landmarks (this is the same as the `participant_id` in the original dataset)\n  * `sequence_id`: the id of the sequence of landmarks (this is the same as the `sequence_id` in the original dataset)\n  * `frame`: the frame number (this is the same as the `frame` in the original dataset)\n  * `lm_type`: the type of landmark (this is the same as the `landmark_type` in the original dataset)\n  * `lm_index`: the index of the landmark (this is the same as the `landmark_index` in the original dataset)\n  * `x`: the x coordinate of the landmark\n  * `y`: the y coordinate of the landmark\n  * `z`: the z coordinate of the landmark\n\nThe `train_labels` table has the following columns:\n   * `user_id`: the id of the user who recorded the sequence of landmarks (this is the same as the `participant_id` in the original dataset)\n   * `sequence_id`: the id of the sequence of landmarks (this is the same as the `sequence_id` in the original dataset)\n   * `sign`: the name of the sign that the user is making (this is the same as the `sign` in the original dataset)\n   * `label`: the numerical label for the sequence of landmarks (this comes from the `sign_to_prediction_index_map.json` file in the original dataset)\n\nI hope you find this notebook useful. If you have any questions, please let me know in the comments section below.\n\nIt's a duck! 🦆","metadata":{}},{"cell_type":"code","source":"!pip install -U duckdb","metadata":{"collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2023-03-12T07:13:17.362103Z","iopub.execute_input":"2023-03-12T07:13:17.362523Z","iopub.status.idle":"2023-03-12T07:13:32.166432Z","shell.execute_reply.started":"2023-03-12T07:13:17.362488Z","shell.execute_reply":"2023-03-12T07:13:32.165272Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from pathlib import Path\n\nimport duckdb","metadata":{"execution":{"iopub.status.busy":"2023-03-12T07:13:32.169052Z","iopub.execute_input":"2023-03-12T07:13:32.169481Z","iopub.status.idle":"2023-03-12T07:13:32.212784Z","shell.execute_reply.started":"2023-03-12T07:13:32.169436Z","shell.execute_reply":"2023-03-12T07:13:32.211427Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# DuckDB version\nduckdb.__version__","metadata":{"collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2023-03-12T07:13:32.214598Z","iopub.execute_input":"2023-03-12T07:13:32.214955Z","iopub.status.idle":"2023-03-12T07:13:32.223808Z","shell.execute_reply.started":"2023-03-12T07:13:32.214922Z","shell.execute_reply":"2023-03-12T07:13:32.222525Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Data directory\ndata_dir = Path('/kaggle/input/asl-sign-training-dataset-duckdb-format')","metadata":{"collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2023-03-12T07:13:32.226369Z","iopub.execute_input":"2023-03-12T07:13:32.227011Z","iopub.status.idle":"2023-03-12T07:13:32.234572Z","shell.execute_reply.started":"2023-03-12T07:13:32.226970Z","shell.execute_reply":"2023-03-12T07:13:32.233167Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Connect to DuckDB database\ndb_path = str((data_dir / 'train_data.db').resolve())\ncon = duckdb.connect(database=db_path, read_only=True)","metadata":{"collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2023-03-12T07:13:32.237099Z","iopub.execute_input":"2023-03-12T07:13:32.238276Z","iopub.status.idle":"2023-03-12T07:13:33.401327Z","shell.execute_reply.started":"2023-03-12T07:13:32.238221Z","shell.execute_reply":"2023-03-12T07:13:33.399584Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Query the database and fetch the results as a pandas DataFrame\nquery = \"\"\"\nSELECT\n    *\nFROM\n    train_data\nLIMIT 100\n\"\"\"\n\n# Query the database and fetch the results as a pandas DataFrame\nsample_landmarks_df = con.execute(query).fetchdf()","metadata":{"collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2023-03-12T07:13:33.403539Z","iopub.execute_input":"2023-03-12T07:13:33.404251Z","iopub.status.idle":"2023-03-12T07:13:33.453858Z","shell.execute_reply.started":"2023-03-12T07:13:33.404209Z","shell.execute_reply":"2023-03-12T07:13:33.452637Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Show the first 5 rows of the DataFrame\nsample_landmarks_df.head(n=5)","metadata":{"collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2023-03-12T07:13:33.455420Z","iopub.execute_input":"2023-03-12T07:13:33.455769Z","iopub.status.idle":"2023-03-12T07:13:33.490265Z","shell.execute_reply.started":"2023-03-12T07:13:33.455735Z","shell.execute_reply":"2023-03-12T07:13:33.488168Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Query the database and fetch the results as a pandas DataFrame\nquery = \"\"\"\nSELECT\n    *\nFROM\n    train_labels\nLIMIT 100\n\"\"\"\n\n# Query the database and fetch the results as a pandas DataFrame\nsample_labels_df = con.execute(query).fetchdf()","metadata":{"collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2023-03-12T07:13:33.491952Z","iopub.execute_input":"2023-03-12T07:13:33.492319Z","iopub.status.idle":"2023-03-12T07:13:33.513086Z","shell.execute_reply.started":"2023-03-12T07:13:33.492284Z","shell.execute_reply":"2023-03-12T07:13:33.512146Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Show the last 5 rows of the DataFrame\nsample_labels_df.tail(n=5)","metadata":{"collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2023-03-12T07:13:33.514402Z","iopub.execute_input":"2023-03-12T07:13:33.514965Z","iopub.status.idle":"2023-03-12T07:13:33.525521Z","shell.execute_reply.started":"2023-03-12T07:13:33.514929Z","shell.execute_reply":"2023-03-12T07:13:33.524303Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Query the database to find the number of landmarks for each training sample\nA training sample is frame from a sequence of landmarks. The number of landmarks in a frame is 543. However, there is one sequence with 436 landmarks instead of 543 landmarks. This is the sequence with sequence_id = 999962374 and frame = 128. The following query finds the number of landmarks for each sequence and frame.","metadata":{}},{"cell_type":"code","source":"query_read_frames = \"\"\"\nWITH train_data AS (\n    SELECT\n        sequence_id, frame, COUNT(*) AS num_lm\n    FROM\n        train_data\n    GROUP BY sequence_id, frame\n)\nSELECT\n    sequence_id, frame, num_lm\nFROM\n    train_data\nWHERE\n    num_lm <> 543\nLIMIT 100\n\"\"\"\n\n# Query the database and fetch the results as a pandas DataFrame\ntraining_samples_df = con.execute(query_read_frames).fetchdf()","metadata":{"collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2023-03-12T07:13:33.530595Z","iopub.execute_input":"2023-03-12T07:13:33.530993Z","iopub.status.idle":"2023-03-12T07:15:33.939439Z","shell.execute_reply.started":"2023-03-12T07:13:33.530954Z","shell.execute_reply":"2023-03-12T07:15:33.938052Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"training_samples_df.head(n=5)","metadata":{"collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2023-03-12T07:15:33.940999Z","iopub.execute_input":"2023-03-12T07:15:33.941513Z","iopub.status.idle":"2023-03-12T07:15:33.953428Z","shell.execute_reply.started":"2023-03-12T07:15:33.941460Z","shell.execute_reply":"2023-03-12T07:15:33.952013Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Get the sign and label for each training sample\nquery_read_train_samples = \"\"\"\nWITH train_data_samples AS (\n    SELECT\n        sequence_id, frame, COUNT(*) AS num_lm\n    FROM\n        train_data\n    GROUP BY sequence_id, frame\n),\ntrain_data_samples_with_labels AS (\n    SELECT\n        train_labels.user_id,\n        train_data_samples.sequence_id,\n        train_data_samples.frame,\n        train_data_samples.num_lm,\n        train_labels.sign,\n        train_labels.label\n    FROM\n        train_data_samples\n    INNER JOIN\n        train_labels\n        USING(sequence_id)\n    WHERE\n        num_lm = 543\n)\nSELECT\n    *\nFROM\n    train_data_samples_with_labels\n-- LIMIT 100\n\"\"\"\n\n# Query the database and fetch the results as a pandas DataFrame\nlabeled_training_samples_df = con.execute(query_read_train_samples).fetchdf()","metadata":{"collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2023-03-12T07:15:33.954631Z","iopub.execute_input":"2023-03-12T07:15:33.955034Z","iopub.status.idle":"2023-03-12T07:17:40.472292Z","shell.execute_reply.started":"2023-03-12T07:15:33.954996Z","shell.execute_reply":"2023-03-12T07:17:40.470620Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Show the first 5 rows of the DataFrame\nlabeled_training_samples_df.head(n=5)","metadata":{"collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2023-03-12T07:17:40.473868Z","iopub.execute_input":"2023-03-12T07:17:40.474344Z","iopub.status.idle":"2023-03-12T07:17:40.487475Z","shell.execute_reply.started":"2023-03-12T07:17:40.474306Z","shell.execute_reply":"2023-03-12T07:17:40.486210Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Get the number of training samples in the dataset; each training sample consists of 543 recorded landmarks\nlabeled_training_samples_df.shape","metadata":{"collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2023-03-12T07:17:40.488881Z","iopub.execute_input":"2023-03-12T07:17:40.489627Z","iopub.status.idle":"2023-03-12T07:17:40.501917Z","shell.execute_reply.started":"2023-03-12T07:17:40.489588Z","shell.execute_reply":"2023-03-12T07:17:40.500867Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Close the connection to the database\n#con.close()","metadata":{"collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2023-03-12T07:17:40.503233Z","iopub.execute_input":"2023-03-12T07:17:40.503669Z","iopub.status.idle":"2023-03-12T07:17:40.512640Z","shell.execute_reply.started":"2023-03-12T07:17:40.503622Z","shell.execute_reply":"2023-03-12T07:17:40.511556Z"},"trusted":true},"execution_count":null,"outputs":[]}]}