{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.12","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":53666,"databundleVersionId":6589269,"sourceType":"competition"}],"dockerImageVersionId":30558,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"from IPython.display import HTML\nimport time\n\nhandle = display(HTML(\"\"\"<marquee>👌</marquee>\"\"\"), display_id='html_marquee1')\ntime.sleep(2)\nhandle = display(HTML(\"\"\"<marquee>~ \"There is a time for many words, and there is also a time for sleep.\" – Homer, The Odyssey</marquee>\"\"\"), display_id='html_marquee1', update=True)","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:07:59.562083Z","iopub.execute_input":"2024-04-10T08:07:59.562698Z","iopub.status.idle":"2024-04-10T08:08:01.602255Z","shell.execute_reply.started":"2024-04-10T08:07:59.562666Z","shell.execute_reply":"2024-04-10T08:08:01.601346Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import gc\nimport pandas as pd\nimport numpy as np\nimport datetime as dt\n\nimport matplotlib.pyplot as plt\nimport matplotlib.cm as cm\nimport seaborn as sns\n\nimport plotly.graph_objects as go\nfrom plotly.subplots import make_subplots\nimport plotly.express as px\nimport plotly.offline\n\nfrom colorama import Fore, Style, init\nfrom pprint import pprint\n\nimport warnings\nwarnings.filterwarnings('ignore')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:08:01.603853Z","iopub.execute_input":"2024-04-10T08:08:01.604970Z","iopub.status.idle":"2024-04-10T08:08:04.978439Z","shell.execute_reply.started":"2024-04-10T08:08:01.604935Z","shell.execute_reply":"2024-04-10T08:08:04.977132Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Color printing\n# inspired by https://www.kaggle.com/code/ravi20076/sleepstate-eda-baseline\ndef PrintColor(text:str, color = Fore.BLUE, style = Style.BRIGHT):\n    \"Prints color outputs using colorama using a text F-string\";\n    print(style + color + text + Style.RESET_ALL);","metadata":{"_kg_hide-output":true,"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:08:04.980067Z","iopub.execute_input":"2024-04-10T08:08:04.980931Z","iopub.status.idle":"2024-04-10T08:08:04.987435Z","shell.execute_reply.started":"2024-04-10T08:08:04.980883Z","shell.execute_reply":"2024-04-10T08:08:04.986218Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n# inspired by https://www.kaggle.com/code/enricomanosperti/detect-sleep-states-first-preprocessing-and-eda\nimport polars as pl\ntrain_series = (pl.scan_parquet('/kaggle/input/child-mind-institute-detect-sleep-states/train_series.parquet')\n                .with_columns(\n                    (\n                        (pl.col(\"timestamp\").str.strptime(pl.Datetime, \"%Y-%m-%dT%H:%M:%S%Z\")),\n                        (pl.col(\"timestamp\").str.strptime(pl.Datetime, \"%Y-%m-%dT%H:%M:%S%Z\").dt.year().alias(\"year\")),\n                        (pl.col(\"timestamp\").str.strptime(pl.Datetime, \"%Y-%m-%dT%H:%M:%S%Z\").dt.month().alias(\"month\")),\n                        (pl.col(\"timestamp\").str.strptime(pl.Datetime, \"%Y-%m-%dT%H:%M:%S%Z\").dt.day().alias(\"day\")),\n                        (pl.col(\"timestamp\").str.strptime(pl.Datetime, \"%Y-%m-%dT%H:%M:%S%Z\").dt.hour().alias(\"hour\")),\n                    )\n                )\n                .collect()\n                .to_pandas()\n               )","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:08:04.991889Z","iopub.execute_input":"2024-04-10T08:08:04.992659Z","iopub.status.idle":"2024-04-10T08:09:26.421441Z","shell.execute_reply.started":"2024-04-10T08:08:04.992616Z","shell.execute_reply":"2024-04-10T08:09:26.420200Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_series.head().style.set_caption(\"Sample of data in the train series data file\"). \\\nset_properties(**{'border': '1.3px solid blue',\n                          'color': 'grey'})","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:26.422677Z","iopub.execute_input":"2024-04-10T08:09:26.423703Z","iopub.status.idle":"2024-04-10T08:09:26.498220Z","shell.execute_reply.started":"2024-04-10T08:09:26.423667Z","shell.execute_reply":"2024-04-10T08:09:26.497315Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_series.info()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:26.499394Z","iopub.execute_input":"2024-04-10T08:09:26.500260Z","iopub.status.idle":"2024-04-10T08:09:26.521458Z","shell.execute_reply.started":"2024-04-10T08:09:26.500227Z","shell.execute_reply":"2024-04-10T08:09:26.520031Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\ntrain_events = (pl.scan_csv('/kaggle/input/child-mind-institute-detect-sleep-states/train_events.csv')\n                .with_columns(\n                    (\n                        (pl.col(\"timestamp\").str.strptime(pl.Datetime, \"%Y-%m-%dT%H:%M:%S%Z\")),\n                        (pl.col(\"timestamp\").str.strptime(pl.Datetime, \"%Y-%m-%dT%H:%M:%S%Z\").dt.year().alias(\"year\")),\n                        (pl.col(\"timestamp\").str.strptime(pl.Datetime, \"%Y-%m-%dT%H:%M:%S%Z\").dt.month().alias(\"month\")),\n                        (pl.col(\"timestamp\").str.strptime(pl.Datetime, \"%Y-%m-%dT%H:%M:%S%Z\").dt.day().alias(\"day\")),\n                        (pl.col(\"timestamp\").str.strptime(pl.Datetime, \"%Y-%m-%dT%H:%M:%S%Z\").dt.hour().alias(\"hour\")),\n                    )\n                )\n                .collect()\n                .to_pandas()\n               )","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:26.523167Z","iopub.execute_input":"2024-04-10T08:09:26.523622Z","iopub.status.idle":"2024-04-10T08:09:26.584084Z","shell.execute_reply.started":"2024-04-10T08:09:26.523577Z","shell.execute_reply":"2024-04-10T08:09:26.582783Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_events.head().style.set_caption(\"Sample of data in the train events data file\"). \\\nset_properties(**{'border': '1.3px solid blue',\n                          'color': 'grey'})","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:26.586020Z","iopub.execute_input":"2024-04-10T08:09:26.586515Z","iopub.status.idle":"2024-04-10T08:09:26.605156Z","shell.execute_reply.started":"2024-04-10T08:09:26.586468Z","shell.execute_reply":"2024-04-10T08:09:26.603829Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_events.info()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:26.606367Z","iopub.execute_input":"2024-04-10T08:09:26.606694Z","iopub.status.idle":"2024-04-10T08:09:26.628568Z","shell.execute_reply.started":"2024-04-10T08:09:26.606667Z","shell.execute_reply":"2024-04-10T08:09:26.627228Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\ntest_series = (pl.scan_parquet('/kaggle/input/child-mind-institute-detect-sleep-states/test_series.parquet')\n                .with_columns(\n                    (\n                        (pl.col(\"timestamp\").str.strptime(pl.Datetime, \"%Y-%m-%dT%H:%M:%S%Z\")),\n                        (pl.col(\"timestamp\").str.strptime(pl.Datetime, \"%Y-%m-%dT%H:%M:%S%Z\").dt.year().alias(\"year\")),\n                        (pl.col(\"timestamp\").str.strptime(pl.Datetime, \"%Y-%m-%dT%H:%M:%S%Z\").dt.month().alias(\"month\")),\n                        (pl.col(\"timestamp\").str.strptime(pl.Datetime, \"%Y-%m-%dT%H:%M:%S%Z\").dt.day().alias(\"day\")),\n                        (pl.col(\"timestamp\").str.strptime(pl.Datetime, \"%Y-%m-%dT%H:%M:%S%Z\").dt.hour().alias(\"hour\")),\n                    )\n                )\n                .collect()\n                .to_pandas()\n               )","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:26.632935Z","iopub.execute_input":"2024-04-10T08:09:26.633291Z","iopub.status.idle":"2024-04-10T08:09:26.655869Z","shell.execute_reply.started":"2024-04-10T08:09:26.633261Z","shell.execute_reply":"2024-04-10T08:09:26.654724Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_series.info()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:26.658031Z","iopub.execute_input":"2024-04-10T08:09:26.658757Z","iopub.status.idle":"2024-04-10T08:09:26.675126Z","shell.execute_reply.started":"2024-04-10T08:09:26.658716Z","shell.execute_reply":"2024-04-10T08:09:26.673927Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n# inspired by https://www.kaggle.com/code/renatoreggiani/reduce-memory-usage-zzzs-cmi\n# with tweaks determined by the selected polars loading strategy\n# tweaks inspired by https://github.com/softhints/Pandas-Tutorials/blob/master/column/3.check-dtype-column-columns-pandas-dataframe.ipynb\nfrom pandas.api.types import is_datetime64_ns_dtype\nfrom pandas.api.types import is_datetime64_any_dtype\ndef reduce_mem_usage(df):\n    \"\"\" iterate through all numeric columns of a dataframe and modify the data type\n        to reduce memory usage.        \n    \"\"\"\n    start_mem = df.memory_usage().sum() / 1024**2\n    print(f'Memory usage of dataframe is {start_mem:.2f} MB')\n    \n    for col in df.columns:\n        col_type = df[col].dtype\n\n        # is_datetime64_any_dtype is more generic then is_datetime64_ns_dtype\n        if col_type != object and not is_datetime64_any_dtype(df[col]):\n            c_min = df[col].min()\n            c_max = df[col].max()\n            if str(col_type)[:3] == 'int':\n                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n                    df[col] = df[col].astype(np.int8)\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df[col] = df[col].astype(np.int16)\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df[col] = df[col].astype(np.int32)\n                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n                    df[col] = df[col].astype(np.int64)  \n            else:\n                if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n                    df[col] = df[col].astype(np.float16)\n                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df[col] = df[col].astype(np.float32)\n                else:\n                    df[col] = df[col].astype(np.float16)\n\n    end_mem = df.memory_usage().sum() / 1024**2\n    print(f'Memory usage after optimization is: {end_mem:.2f} MB')\n    decrease = 100 * (start_mem - end_mem) / start_mem\n    print(f'Decreased by {decrease:.2f}%')\n    \n    return df\n\ntrain_series = reduce_mem_usage(train_series)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:26.678665Z","iopub.execute_input":"2024-04-10T08:09:26.679032Z","iopub.status.idle":"2024-04-10T08:09:33.166078Z","shell.execute_reply.started":"2024-04-10T08:09:26.679001Z","shell.execute_reply":"2024-04-10T08:09:33.164864Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\ntest_series = reduce_mem_usage(test_series)","metadata":{"_kg_hide-input":true,"_kg_hide-output":false,"execution":{"iopub.status.busy":"2024-04-10T08:09:33.167826Z","iopub.execute_input":"2024-04-10T08:09:33.168310Z","iopub.status.idle":"2024-04-10T08:09:33.183664Z","shell.execute_reply.started":"2024-04-10T08:09:33.168267Z","shell.execute_reply":"2024-04-10T08:09:33.182422Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for col in train_events.columns:\n    msg = 'column: {:>10}\\t Percent of NaN value: {:.2f}%'.format(col,100*(train_events[col].isnull().sum()/train_events[col].shape[0]))\n    PrintColor(f\"\\n---> {msg}\");\n    #print(msg)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:33.185493Z","iopub.execute_input":"2024-04-10T08:09:33.186263Z","iopub.status.idle":"2024-04-10T08:09:33.203189Z","shell.execute_reply.started":"2024-04-10T08:09:33.186219Z","shell.execute_reply":"2024-04-10T08:09:33.201728Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import missingno as msno\n\nmsno.bar(train_events, color=(0.3,0.3,0.5))","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:33.205035Z","iopub.execute_input":"2024-04-10T08:09:33.205948Z","iopub.status.idle":"2024-04-10T08:09:34.950417Z","shell.execute_reply.started":"2024-04-10T08:09:33.205900Z","shell.execute_reply":"2024-04-10T08:09:34.949109Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now we are going to look at the actual records inn the train events data affected in the matrix view","metadata":{}},{"cell_type":"code","source":"msno.matrix(train_events, color=(0.3,0.3,0.5))","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:34.951923Z","iopub.execute_input":"2024-04-10T08:09:34.953052Z","iopub.status.idle":"2024-04-10T08:09:35.905249Z","shell.execute_reply.started":"2024-04-10T08:09:34.953006Z","shell.execute_reply":"2024-04-10T08:09:35.903910Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for col in train_series.columns:\n    msg = 'column: {:>10}\\t Percent of NaN value: {:.2f}%'.format(col,100*(train_series[col].isnull().sum()/train_series[col].shape[0]))\n    PrintColor(f\"\\n---> {msg}\");\n    #print(msg)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:35.906812Z","iopub.execute_input":"2024-04-10T08:09:35.907241Z","iopub.status.idle":"2024-04-10T08:09:51.734663Z","shell.execute_reply.started":"2024-04-10T08:09:35.907206Z","shell.execute_reply":"2024-04-10T08:09:51.733398Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# base statistics\ntrain_events.describe(include='all').style.set_caption(\"Summary statistics for training events\"). \\\nset_properties(**{'border': '1.3px solid blue',\n                          'color': 'grey'})","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:51.736399Z","iopub.execute_input":"2024-04-10T08:09:51.736963Z","iopub.status.idle":"2024-04-10T08:09:51.797439Z","shell.execute_reply.started":"2024-04-10T08:09:51.736861Z","shell.execute_reply":"2024-04-10T08:09:51.796244Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"series_steps = train_events.groupby(['series_id'])['step'].apply(lambda x: x.isnull().any())\n\nframe = {'series_id': series_steps.index,\n         'has_missing_labels': series_steps}\n \n# Creating DataFrame by passing Dictionary\nagg_data = pd.DataFrame(frame)\n\ndfg = agg_data['has_missing_labels'].value_counts().reset_index()\ndfg.columns = ['has_missing_labels', 'number_of_series']\nfig = px.bar(dfg, x='has_missing_labels', y='number_of_series', \n             title='Series with and without missing event labels',\n            color_discrete_sequence=px.colors.qualitative.Prism) \nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:51.799350Z","iopub.execute_input":"2024-04-10T08:09:51.799703Z","iopub.status.idle":"2024-04-10T08:09:53.786891Z","shell.execute_reply.started":"2024-04-10T08:09:51.799672Z","shell.execute_reply":"2024-04-10T08:09:53.785544Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pivot = pd.pivot_table(train_events, index='series_id', columns='event', values='timestamp', aggfunc=['min', 'max'])\npivot.columns = [f'{level}_{col}_ts' for level, col in pivot.columns]\n\nevents_summary = train_events \\\n    .groupby('series_id').agg({'series_id': 'count', 'step': 'count'}) \\\n    .rename({'series_id': 'count_events', 'step': 'count_non_null_events'}, axis=1) \\\n    .join(pivot, how='left')\n\nevents_summary.insert(\n    2, 'pct_non_null_events', events_summary.count_non_null_events / events_summary.count_events\n)\n\nevents_summary = events_summary.sort_values(['pct_non_null_events', 'count_non_null_events'], ascending=[False, True])\n\ncomplete_series = events_summary.query('pct_non_null_events == 1.0')\nempty_series    = events_summary.query('pct_non_null_events == 0.0')\nincomplete_series = events_summary.query('(pct_non_null_events > 0.0) & (pct_non_null_events < 1.0)')\n\nsummary_evt = {\n    'event_coverage': ['Full', 'Partial', 'Missing'],\n    'series_count': [complete_series.shape[0], incomplete_series.shape[0], empty_series.shape[0]]\n}\n\nagg_data = pd.DataFrame(summary_evt)\nfig = px.bar(agg_data, x='event_coverage', y='series_count', \n             title='Number of series with full, partial, and missing event label coverage',\n            color_discrete_sequence=px.colors.qualitative.Prism) \nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:53.789534Z","iopub.execute_input":"2024-04-10T08:09:53.790670Z","iopub.status.idle":"2024-04-10T08:09:53.938606Z","shell.execute_reply.started":"2024-04-10T08:09:53.790623Z","shell.execute_reply":"2024-04-10T08:09:53.937066Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = px.histogram(events_summary, x=\"pct_non_null_events\", marginal=\"rug\",\n                   title='Distribution of Training Series by % events labelled',\n                   color_discrete_sequence=px.colors.qualitative.Prism)\nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:53.940425Z","iopub.execute_input":"2024-04-10T08:09:53.940952Z","iopub.status.idle":"2024-04-10T08:09:54.132829Z","shell.execute_reply.started":"2024-04-10T08:09:53.940906Z","shell.execute_reply":"2024-04-10T08:09:54.131604Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Finally, let's display the ids of series with fully labelled events.","metadata":{}},{"cell_type":"code","source":"no_NaN_series = series_steps[~series_steps].index.tolist()\n\nPrintColor(f\"\\n---> Displaing the ids of series with fully labelled events:\\n\");\npprint(no_NaN_series, indent = 10, width = 50);\n\n# print(no_NaN_series)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:54.134650Z","iopub.execute_input":"2024-04-10T08:09:54.135048Z","iopub.status.idle":"2024-04-10T08:09:54.142144Z","shell.execute_reply.started":"2024-04-10T08:09:54.135015Z","shell.execute_reply":"2024-04-10T08:09:54.141070Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\ndef summarize_dataframe(df):\n    summary_df = pd.DataFrame(df.dtypes, columns=['dtypes'])\n    summary_df['missing#'] = df.isna().sum().values*100\n    summary_df['missing%'] = (df.isna().sum().values*100)/len(df)\n    summary_df['uniques'] = df.nunique().values\n    summary_df['count'] = df.count().values\n    #sum['skew'] = df.skew().values\n    desc = pd.DataFrame(df.describe().T)\n    summary_df['min'] = desc['min']\n    summary_df['max'] = desc['max']\n    summary_df['mean'] = desc['mean']\n    return summary_df\n\nsummarize_dataframe(train_events).style.background_gradient(cmap='Purples')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:54.143551Z","iopub.execute_input":"2024-04-10T08:09:54.144518Z","iopub.status.idle":"2024-04-10T08:09:54.235239Z","shell.execute_reply.started":"2024-04-10T08:09:54.144481Z","shell.execute_reply":"2024-04-10T08:09:54.234220Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Pivot the DataFrame to create df_availability\ntr_ev = train_events.copy()\ntr_ev['dates'] = tr_ev['timestamp'].dt.date\ndf_availability = tr_ev.pivot_table(columns='dates', index='series_id', values='step', aggfunc='count')\n\n# Fill NA values with 0\ndf_availability = (~df_availability.isna()).fillna(0).astype('int')\n\n# Plotting the clustered heatmap\nsns.clustermap(df_availability, col_cluster=False, figsize=(12, 8))\n\n# TODO: re-implement for plotly per\n# https://stackoverflow.com/questions/66547583/plotly-clustered-heatmap-with-dendrogram-python\n# https://plotly.com/python/clustergram/#default-clustergram\n# https://plotly.com/python/dendrogram/","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:54.236855Z","iopub.execute_input":"2024-04-10T08:09:54.238204Z","iopub.status.idle":"2024-04-10T08:09:56.184081Z","shell.execute_reply.started":"2024-04-10T08:09:54.238164Z","shell.execute_reply":"2024-04-10T08:09:56.182751Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del tr_ev, df_availability\n\n# Returns the number of\n# objects it has collected\n# and deallocated\ncollected = gc.collect()\n \n# Prints Garbage collector\n# as 0 object\nprint(\"Garbage collector: collected\",\n          \"%d objects.\" % collected)# Importing gc module","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:56.185949Z","iopub.execute_input":"2024-04-10T08:09:56.186311Z","iopub.status.idle":"2024-04-10T08:09:56.364728Z","shell.execute_reply.started":"2024-04-10T08:09:56.186280Z","shell.execute_reply":"2024-04-10T08:09:56.363459Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_onset = train_events[train_events['event'] == 'onset']\ndf_wakeup = train_events[train_events['event'] == 'wakeup']\n\nfig = make_subplots(rows=1, cols=2, subplot_titles=('<b>Hourly Distr. of Onset Events</b>',\n                                                    '<b>Hourly Distr. of Wakeup Events</b>',\n                                                   ))\n\nfig.add_trace(go.Histogram(x=df_onset['hour'].dropna(), nbinsx=24), row=1, col=1)\nfig.add_trace(go.Histogram(x=df_wakeup['hour'].dropna(), nbinsx=24), row=1, col=2)\n                                \n# Update visual layout\nfig.update_layout(\n    showlegend=False,\n    width=600,\n    height=400,\n    autosize=False,\n    margin=dict(t=15, b=0, l=5, r=5),\n    template=\"plotly_white\",\n    colorway=px.colors.qualitative.Prism ,\n)\n\n# update font size at the axes\nfig.update_coloraxes(colorbar_tickfont_size=10)\n# Update font in the titles: Apparently subplot titles are annotations (Subplot font size is hardcoded to 16pt · Issue #985)\nfig.update_annotations(font_size=12)\n# Reduce opacity\nfig.update_traces(opacity=0.75)\n\nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:56.366399Z","iopub.execute_input":"2024-04-10T08:09:56.366862Z","iopub.status.idle":"2024-04-10T08:09:56.500200Z","shell.execute_reply.started":"2024-04-10T08:09:56.366805Z","shell.execute_reply":"2024-04-10T08:09:56.499102Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"agg_data = train_events[[\"event\", \"hour\"]].groupby([\"event\", \"hour\"]).size().reset_index(name=\"count\")\nagg_data[\"hour\"] = agg_data[\"hour\"].astype(int)\n# define figure element\nfig = px.sunburst(\n    agg_data,\n    values='count',\n    path=[\"event\", \"hour\"],\n    title=\"Events by hours in a day\",\n    color=\"event\",\n    height=600,\n    color_discrete_sequence=px.colors.qualitative.Prism,\n    #color_continuous_scale=px.colors.sequential.Viridis\n)\n# display the figure\nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:56.501827Z","iopub.execute_input":"2024-04-10T08:09:56.502504Z","iopub.status.idle":"2024-04-10T08:09:56.656731Z","shell.execute_reply.started":"2024-04-10T08:09:56.502468Z","shell.execute_reply":"2024-04-10T08:09:56.655458Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"agg_data = train_events[[\"series_id\", \"event\"]].groupby([\"series_id\", \"event\"]).size().reset_index(name=\"count\")\n\ndf_wide=pd.pivot(agg_data, index=['series_id'], columns = ['event'],values = 'count') #Reshape from long to wide\n\n#Re-arange the new columns in the correct order\ncols = agg_data['event'].unique()\ndf_wide=df_wide[cols]\ndf_wide['diff'] = df_wide['wakeup'] - df_wide['onset']\ndf_wide.reset_index(inplace=True)\ndf_wide = df_wide.rename(columns = {'index':'series_id'})\n\ndf_unbalanced = df_wide[df_wide['diff'] !=0 ]\n\nunbalanced_series = df_unbalanced['series_id'].unique()\n\nif len(unbalanced_series) > 0:\n    PrintColor(f\"\\n---> Displaing the ids of series where wakeups to do not equal to onset events:\\n\")\n    pprint(unbalanced_series, indent = 10, width = 50)\nelse:\n    PrintColor(f\"\\n---> No series with the unbalanced events detected\")\n\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:56.666216Z","iopub.execute_input":"2024-04-10T08:09:56.666628Z","iopub.status.idle":"2024-04-10T08:09:56.694954Z","shell.execute_reply.started":"2024-04-10T08:09:56.666597Z","shell.execute_reply":"2024-04-10T08:09:56.693583Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"mn_train_events = pd.read_csv('/kaggle/input/child-mind-institute-detect-sleep-states/train_events.csv')\nmn_train_events = mn_train_events.loc[~mn_train_events['timestamp'].isna()] # drop NA\n\ndf_onset_mn = mn_train_events[mn_train_events['event'] == 'onset']\ndf_wakeup_mn = mn_train_events[mn_train_events['event'] == 'wakeup']\n\nmin15_onset = df_onset_mn['timestamp'].str[-10:-8].astype('int32')%15 # minute of event, in 15 min blocks\nmin15_wakeup = df_wakeup_mn['timestamp'].str[-10:-8].astype('int32')%15 # minute of event, in 15 min blocks\n\nframe1 = {'interval': min15_onset.index,\n         'onset_minute': min15_onset}\n \n# Creating DataFrame by passing Dictionary\nagg_data_onsets = pd.DataFrame(frame1)\n\nframe2 = {'interval': min15_wakeup.index,\n         'wakeup_minute': min15_wakeup}\n \n# Creating DataFrame by passing Dictionary\nagg_data_wakeups = pd.DataFrame(frame2)\n\nfig = make_subplots(rows=1, cols=2, subplot_titles=('<b>Distr. of Onset Events in 15-min intervals</b>',\n                                                    '<b>Distr. of Wakeup Events in 15-min intervals</b>',\n                                                   ))\n\nfig.add_trace(go.Histogram(x=agg_data_onsets['onset_minute'].dropna(), nbinsx=24), row=1, col=1)\nfig.add_trace(go.Histogram(x=agg_data_wakeups['wakeup_minute'].dropna(), nbinsx=24), row=1, col=2)\n                                \n# Update visual layout\nfig.update_layout(\n    showlegend=False,\n    width=700,\n    height=400,\n    autosize=False,\n    margin=dict(t=15, b=0, l=5, r=5),\n    template=\"plotly_white\",\n    colorway=px.colors.qualitative.Prism ,\n)\n\n# update font size at the axes\nfig.update_coloraxes(colorbar_tickfont_size=10)\n# Update font in the titles: Apparently subplot titles are annotations (Subplot font size is hardcoded to 16pt · Issue #985)\nfig.update_annotations(font_size=12)\n# Reduce opacity\nfig.update_traces(opacity=0.75)\n\nfig.show()\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:56.697211Z","iopub.execute_input":"2024-04-10T08:09:56.697688Z","iopub.status.idle":"2024-04-10T08:09:56.824457Z","shell.execute_reply.started":"2024-04-10T08:09:56.697646Z","shell.execute_reply":"2024-04-10T08:09:56.823270Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def is_in_frequent_event_minute_slot(x):\n    value = 0\n    if x in [0,3,7,11]:\n        value = 1\n    return value\n\ndef is_in_other_event_minute_slot(x):\n    value = 0\n    if x not in [0,3,7,11]:\n        value = 1\n    return value\n\ndf_onset_mn['timestamp'] = pd.to_datetime(df_onset_mn['timestamp']).apply(lambda t: t.tz_localize(None))\ndf_onset_mn['hour'] = df_onset_mn[\"timestamp\"].dt.hour\ndf_onset_mn['minute'] = df_onset_mn[\"timestamp\"].dt.minute\ndf_onset_mn['15min'] = df_onset_mn['minute'] % 15\n\ndf_onset_mn['in_0_3_7_11_slot'] = df_onset_mn['15min'].apply(lambda x: is_in_frequent_event_minute_slot(x))\ndf_onset_mn['in_other_slot'] = df_onset_mn['15min'].apply(lambda x: is_in_other_event_minute_slot(x))\n\ndf_series_agg = df_onset_mn.groupby(['series_id'])[['in_0_3_7_11_slot', 'in_other_slot']].sum().reset_index()\n\nfig = px.scatter(df_series_agg, x=\"in_0_3_7_11_slot\", y=\"in_other_slot\",\n                 size='in_0_3_7_11_slot', \n                 title='Onset events in minute buckets: [0,3,7,11] vs. others',\n                 color_discrete_sequence=px.colors.qualitative.Prism,\n                 hover_data=['series_id'])\nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:56.826332Z","iopub.execute_input":"2024-04-10T08:09:56.826789Z","iopub.status.idle":"2024-04-10T08:09:57.092518Z","shell.execute_reply.started":"2024-04-10T08:09:56.826747Z","shell.execute_reply":"2024-04-10T08:09:57.091355Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_series_agg['ratio_0_3_7_11'] = df_series_agg['in_0_3_7_11_slot'] / (df_series_agg['in_0_3_7_11_slot'] + df_series_agg['in_other_slot'])\ndf_series_agg['ratio_other'] = 1 - df_series_agg['ratio_0_3_7_11']\n\nfig = make_subplots(rows=1, cols=2, subplot_titles=('<b>Distr. of Onsets ratio in [0,3,7,11] minute bucket</b>',\n                                                    '<b>Distr. of Onsets ratio in other minute bucket</b>',\n                                                   ))\n\nfig.add_trace(go.Histogram(x=df_series_agg['ratio_0_3_7_11'].dropna(), nbinsx=24), row=1, col=1)\nfig.add_trace(go.Histogram(x=df_series_agg['ratio_other'].dropna(), nbinsx=24), row=1, col=2)\n                                \n# Update visual layout\nfig.update_layout(\n    showlegend=False,\n    width=800,\n    height=400,\n    autosize=False,\n    margin=dict(t=15, b=0, l=5, r=5),\n    template=\"plotly_white\",\n    colorway=px.colors.qualitative.Prism ,\n)\n\n# update font size at the axes\nfig.update_coloraxes(colorbar_tickfont_size=10)\n# Update font in the titles: Apparently subplot titles are annotations (Subplot font size is hardcoded to 16pt · Issue #985)\nfig.update_annotations(font_size=12)\n# Reduce opacity\nfig.update_traces(opacity=0.75)\n\nfig.show()\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:57.094703Z","iopub.execute_input":"2024-04-10T08:09:57.095203Z","iopub.status.idle":"2024-04-10T08:09:57.182600Z","shell.execute_reply.started":"2024-04-10T08:09:57.095159Z","shell.execute_reply":"2024-04-10T08:09:57.181419Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Wakeup Event Patterns\n\nLet's look at *Wakeup* event patterns now. Initially, we are going to plot the chart to visualize relation between the number of wakeup events in [0,3,7,11] minute bin vs. the events in other bin.","metadata":{}},{"cell_type":"code","source":"df_wakeup_mn['timestamp'] = pd.to_datetime(df_wakeup_mn['timestamp']).apply(lambda t: t.tz_localize(None))\ndf_wakeup_mn['hour'] = df_wakeup_mn[\"timestamp\"].dt.hour\ndf_wakeup_mn['minute'] = df_wakeup_mn[\"timestamp\"].dt.minute\ndf_wakeup_mn['15min'] = df_wakeup_mn['minute'] % 15\n\ndf_wakeup_mn['in_0_3_7_11_slot'] = df_wakeup_mn['15min'].apply(lambda x: is_in_frequent_event_minute_slot(x))\ndf_wakeup_mn['in_other_slot'] = df_wakeup_mn['15min'].apply(lambda x: is_in_other_event_minute_slot(x))\n\ndf_series_agg = df_wakeup_mn.groupby(['series_id'])[['in_0_3_7_11_slot', 'in_other_slot']].sum().reset_index()\n\nfig = px.scatter(df_series_agg, x=\"in_0_3_7_11_slot\", y=\"in_other_slot\",\n                 size='in_0_3_7_11_slot', \n                 title='Wakeup events in minute buckets: [0,3,7,11] vs. others',\n                 color_discrete_sequence=px.colors.qualitative.Prism,\n                 hover_data=['series_id'])\nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:57.183892Z","iopub.execute_input":"2024-04-10T08:09:57.184270Z","iopub.status.idle":"2024-04-10T08:09:57.408925Z","shell.execute_reply.started":"2024-04-10T08:09:57.184235Z","shell.execute_reply":"2024-04-10T08:09:57.407515Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We find that \n- there is a clear separation between the series in terms of the event distribution (some series are \"0,3,7,11-quantized\" and some are evenly-distributed)\n- the majority of series tend to have more wakeup events recorded in [0,3,7,11] minute bin.\n\nLet's now calculate the ratios of events in [0,3,7,11] minute bin and in other bin for each series as well as plot the histograms of them.","metadata":{}},{"cell_type":"code","source":"df_series_agg['ratio_0_3_7_11'] = df_series_agg['in_0_3_7_11_slot'] / (df_series_agg['in_0_3_7_11_slot'] + df_series_agg['in_other_slot'])\ndf_series_agg['ratio_other'] = 1 - df_series_agg['ratio_0_3_7_11']\n\nfig = make_subplots(rows=1, cols=2, subplot_titles=('<b>Distr. of Wakeups ratio in [0,3,7,11] minute bucket</b>',\n                                                    '<b>Distr. of Wakeups ratio in other minute bucket</b>',\n                                                   ))\n\nfig.add_trace(go.Histogram(x=df_series_agg['ratio_0_3_7_11'].dropna(), nbinsx=24), row=1, col=1)\nfig.add_trace(go.Histogram(x=df_series_agg['ratio_other'].dropna(), nbinsx=24), row=1, col=2)\n                                \n# Update visual layout\nfig.update_layout(\n    showlegend=False,\n    width=800,\n    height=400,\n    autosize=False,\n    margin=dict(t=15, b=0, l=5, r=5),\n    template=\"plotly_white\",\n    colorway=px.colors.qualitative.Prism ,\n)\n\n# update font size at the axes\nfig.update_coloraxes(colorbar_tickfont_size=10)\n# Update font in the titles: Apparently subplot titles are annotations (Subplot font size is hardcoded to 16pt · Issue #985)\nfig.update_annotations(font_size=12)\n# Reduce opacity\nfig.update_traces(opacity=0.75)\n\nfig.show()\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:57.410498Z","iopub.execute_input":"2024-04-10T08:09:57.410968Z","iopub.status.idle":"2024-04-10T08:09:57.496588Z","shell.execute_reply.started":"2024-04-10T08:09:57.410937Z","shell.execute_reply":"2024-04-10T08:09:57.495346Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del mn_train_events, df_onset_mn, df_wakeup_mn, agg_data_onsets, agg_data_wakeups, df_series_agg, frame1, frame2\n\n# Returns the number of\n# objects it has collected\n# and deallocated\ncollected = gc.collect()\n \n# Prints Garbage collector\n# as 0 object\nprint(\"Garbage collector: collected\",\n          \"%d objects.\" % collected)# Importing gc module","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:57.497992Z","iopub.execute_input":"2024-04-10T08:09:57.498373Z","iopub.status.idle":"2024-04-10T08:09:57.680182Z","shell.execute_reply.started":"2024-04-10T08:09:57.498341Z","shell.execute_reply":"2024-04-10T08:09:57.678816Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## <div style=\"font-size:20px;text-align:center;color:black;border-bottom:5px #0026d6 solid;padding-bottom:3%\">Sleep Duration</div>\n\nLet's review how many hours the people observed in the training set sleep in average.","metadata":{}},{"cell_type":"code","source":"# inspired by https://www.kaggle.com/code/alinasri/sleep-data-analysis-some-eda\n\n# Lets focus on non null rows of the training events only\nmask_nonull= (~train_events['step'].isnull()) & (~train_events['timestamp'].isnull()) \ntrain_events_nonull = train_events[mask_nonull]\n\n# Group by 'series_id' and calculate sleep duration for each night\nsleep_duration_df = train_events_nonull.groupby([train_events_nonull['series_id'], train_events_nonull['night']])['timestamp'].agg(['min', 'max']).reset_index()\nsleep_duration_df = sleep_duration_df.rename(columns={'min': 'onset', 'max': 'wakeup'})\nsleep_duration_df['sleep_duration'] = ((sleep_duration_df['wakeup'] - sleep_duration_df['onset']).dt.seconds / 3600).round(0)\n\ndfg = sleep_duration_df['sleep_duration'].value_counts().reset_index()\ndfg.columns = ['sleep_duration', 'number_of_observations']\nfig = px.bar(dfg, x='sleep_duration', y='number_of_observations', \n             title='Sleep Duration, Training seria (hours)',\n            color_discrete_sequence=px.colors.qualitative.Prism) \nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:57.682603Z","iopub.execute_input":"2024-04-10T08:09:57.683077Z","iopub.status.idle":"2024-04-10T08:09:57.780255Z","shell.execute_reply.started":"2024-04-10T08:09:57.683041Z","shell.execute_reply":"2024-04-10T08:09:57.778918Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We can see that the most of the training observations (within the series-to-nights dimensions) demonstrated the sleep duration to be between 8 and 10 h per night (day), with the sleeping time of 9 h per day to be the most frequent value. ","metadata":{}},{"cell_type":"markdown","source":"## <div style=\"font-size:20px;text-align:center;color:black;border-bottom:5px #0026d6 solid;padding-bottom:3%\">Nights Data Insights</div>","metadata":{}},{"cell_type":"code","source":"nights_in_series = train_events.groupby('series_id')['night'].max()\n\nframe = {'series_id': nights_in_series.index,\n         'number_of_nights_in_seria': nights_in_series}\n \n# Creating DataFrame by passing Dictionary\nagg_data = pd.DataFrame(frame)\n\nfig = px.histogram(agg_data, x=\"number_of_nights_in_seria\", marginal=\"rug\",\n                   title='Distribution of Nights per Series in Training Events',\n                   color_discrete_sequence=px.colors.qualitative.Prism)\nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:57.782032Z","iopub.execute_input":"2024-04-10T08:09:57.782444Z","iopub.status.idle":"2024-04-10T08:09:57.888355Z","shell.execute_reply.started":"2024-04-10T08:09:57.782413Z","shell.execute_reply":"2024-04-10T08:09:57.887086Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We find that\n\n- the majority of the series has *20-29* nights in it\n- the second 'cluster' contains the series with *30-39* nights in it\n- the tails of the distribution are minor; \n- we can indicate there are exceptionally long ('outlier') series with *84* and *62* nights, respectively","metadata":{}},{"cell_type":"markdown","source":"## <div style=\"font-size:20px;text-align:center;color:black;border-bottom:5px #0026d6 solid;padding-bottom:3%\">Steps Data Insights</div>","metadata":{}},{"cell_type":"code","source":"min_steps_in_series = train_series.groupby('series_id')['step'].min()\nmax_steps_in_series = train_series.groupby('series_id')['step'].max()\n\nframe1 = {'series_id': min_steps_in_series.index,\n         'min_number_of_steps_in_seria': min_steps_in_series}\n\nframe2 = {'series_id': max_steps_in_series.index,\n         'max_number_of_steps_in_seria': max_steps_in_series}\n \n# Creating DataFrames by passing Dictionary\nmin_steps_df = pd.DataFrame(frame1)\nmax_steps_df = pd.DataFrame(frame2)\n\nmin_steps_df.reset_index(drop = True, inplace = True)\nmax_steps_df.reset_index(drop = True, inplace = True)\n\nagg_data = pd.merge(min_steps_df, max_steps_df, how='inner', on=['series_id'])\nagg_data['steps_number'] = agg_data['max_number_of_steps_in_seria'] - agg_data['min_number_of_steps_in_seria']\n\nfig = px.histogram(agg_data, x=\"steps_number\", marginal=\"rug\",\n                   title='Distribution of Steps per Series in Training Series',\n                   color_discrete_sequence=px.colors.qualitative.Prism)\nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:09:57.889730Z","iopub.execute_input":"2024-04-10T08:09:57.890144Z","iopub.status.idle":"2024-04-10T08:10:28.408461Z","shell.execute_reply.started":"2024-04-10T08:09:57.890111Z","shell.execute_reply":"2024-04-10T08:10:28.407213Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We can see that the distribution of steps follows the general distributions by nights, more or less. Every step corresponds to one instance of the accelerometer measurements that are recorded every 5 s (if no errors/breaks happen).","metadata":{}},{"cell_type":"code","source":"ground_truth = pd.read_csv(\"/kaggle/input/child-mind-institute-detect-sleep-states/train_events.csv\")\n\n# get event counts in UTC\ndf = ground_truth.copy()\ndf[\"timestamp\"] = pd.to_datetime(df[\"timestamp\"],utc=True)\ndf[\"hour\"] = df[\"timestamp\"].dt.hour\n# filter for mornings and afternons\ndf_morning   = df.loc[df[\"hour\"] <= 12].copy()\ndf_afternoon = df.loc[df[\"hour\"]  > 12].copy()\n\nutc_stat = {\n    \"label\": \"UTC\",\n    \"morning_wakeups\": df_morning.loc[df_morning[\"event\"] == \"wakeup\"].shape[0],\n    \"morning_onsets\": df_morning.loc[df_morning[\"event\"] == \"onset\"].shape[0],\n    \"afternoon_onsets\": df_afternoon.loc[df_afternoon[\"event\"] == \"onset\"].shape[0],\n    \"afternoon_wakeups\": df_afternoon.loc[df_afternoon[\"event\"] == \"wakeup\"].shape[0]\n}\n\n# get event counts in the local time zone time\ndf = ground_truth.copy()\ndf['timestamp'] = pd.to_datetime(df['timestamp']).apply(lambda t: t.tz_localize(None))\ndf[\"hour\"] = df[\"timestamp\"].dt.hour\n# filter for mornings and afternons\ndf_morning   = df.loc[df[\"hour\"] <= 12].copy()\ndf_afternoon = df.loc[df[\"hour\"]  > 12].copy()\n\nlocal_tz_stat = {\n    \"label\": \"Local time\",\n    \"morning_wakeups\": df_morning.loc[df_morning[\"event\"] == \"wakeup\"].shape[0],\n    \"morning_onsets\": df_morning.loc[df_morning[\"event\"] == \"onset\"].shape[0],\n    \"afternoon_onsets\": df_afternoon.loc[df_afternoon[\"event\"] == \"onset\"].shape[0],\n    \"afternoon_wakeups\": df_afternoon.loc[df_afternoon[\"event\"] == \"wakeup\"].shape[0]\n}\n\ndata = [utc_stat, local_tz_stat]\ndf_stat = pd.DataFrame.from_dict(data)\n\nfig = make_subplots(rows=2, cols=2, subplot_titles=('<b>Morning Wakeups: UTC vs. Local time</b>',\n                                                    '<b>Morning Onsets: UTC vs. Local time</b>',\n                                                    '<b>Afternoon Onsets: UTC vs. Local time</b>',\n                                                    '<b>Afternoon Wakeups: UTC vs. Local time</b>'\n                                                   ))\n\nfig.add_trace(go.Bar(x=df_stat['label'], y=df_stat['morning_wakeups']), row=1, col=1)\nfig.add_trace(go.Bar(x=df_stat['label'], y=df_stat['morning_onsets']), row=1, col=2)\nfig.add_trace(go.Bar(x=df_stat['label'], y=df_stat['afternoon_onsets']), row=2, col=1)\nfig.add_trace(go.Bar(x=df_stat['label'], y=df_stat['afternoon_wakeups']), row=2, col=2)\n                                \n# Update visual layout\nfig.update_layout(\n    showlegend=False,\n    width=600,\n    height=400,\n    autosize=False,\n    margin=dict(t=15, b=0, l=5, r=5),\n    template=\"plotly_white\",\n    colorway=px.colors.qualitative.Prism ,\n)\n\n# update font size at the axes\nfig.update_coloraxes(colorbar_tickfont_size=10)\n# Update font in the titles: Apparently subplot titles are annotations (Subplot font size is hardcoded to 16pt · Issue #985)\nfig.update_annotations(font_size=12)\n# Reduce opacity\nfig.update_traces(opacity=0.75)\n\nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:10:28.410118Z","iopub.execute_input":"2024-04-10T08:10:28.410443Z","iopub.status.idle":"2024-04-10T08:10:28.883542Z","shell.execute_reply.started":"2024-04-10T08:10:28.410416Z","shell.execute_reply":"2024-04-10T08:10:28.882369Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del ground_truth, df, df_morning, df_afternoon, df_stat\n\n\n# Returns the number of\n# objects it has collected\n# and deallocated\ncollected = gc.collect()\n \n# Prints Garbage collector\n# as 0 object\nprint(\"Garbage collector: collected\",\n          \"%d objects.\" % collected)# Importing gc module","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2024-04-10T08:10:28.886857Z","iopub.execute_input":"2024-04-10T08:10:28.887519Z","iopub.status.idle":"2024-04-10T08:10:29.063484Z","shell.execute_reply.started":"2024-04-10T08:10:28.887485Z","shell.execute_reply":"2024-04-10T08:10:29.062203Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\ntrain_series = train_series[train_series['series_id'].isin(no_NaN_series)]","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:10:29.065029Z","iopub.execute_input":"2024-04-10T08:10:29.065518Z","iopub.status.idle":"2024-04-10T08:10:42.328706Z","shell.execute_reply.started":"2024-04-10T08:10:29.065485Z","shell.execute_reply":"2024-04-10T08:10:42.327463Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"summarize_dataframe(train_series).style.background_gradient(cmap='Purples')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2023-10-23T20:29:33.583423Z","iopub.execute_input":"2023-10-23T20:29:33.584237Z","iopub.status.idle":"2023-10-23T20:29:45.070639Z","shell.execute_reply.started":"2023-10-23T20:29:33.584214Z","shell.execute_reply":"2023-10-23T20:29:45.069435Z"}}},{"cell_type":"code","source":"summarize_dataframe(test_series).style.background_gradient(cmap='Purples')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:10:42.329925Z","iopub.execute_input":"2024-04-10T08:10:42.330248Z","iopub.status.idle":"2024-04-10T08:10:42.390952Z","shell.execute_reply.started":"2024-04-10T08:10:42.330220Z","shell.execute_reply":"2024-04-10T08:10:42.389675Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"!pip install duckdb --quiet","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2024-04-10T08:10:42.392223Z","iopub.execute_input":"2024-04-10T08:10:42.392546Z","iopub.status.idle":"2024-04-10T08:10:59.847858Z","shell.execute_reply.started":"2024-04-10T08:10:42.392519Z","shell.execute_reply":"2024-04-10T08:10:59.846338Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import duckdb\n\nparquet_table = '/kaggle/input/child-mind-institute-detect-sleep-states/train_series.parquet'\ntesting_parquet_table = '/kaggle/input/child-mind-institute-detect-sleep-states/test_series.parquet'\ntraining_events_table = '/kaggle/input/child-mind-institute-detect-sleep-states/train_events.csv'\n\ncon = duckdb.connect()","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2024-04-10T08:10:59.849880Z","iopub.execute_input":"2024-04-10T08:10:59.850300Z","iopub.status.idle":"2024-04-10T08:10:59.918774Z","shell.execute_reply.started":"2024-04-10T08:10:59.850265Z","shell.execute_reply":"2024-04-10T08:10:59.917671Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.execute(f\"\"\"\n    CREATE TABLE train_series AS\n    SELECT * \n    FROM read_parquet('{parquet_table}');\n\"\"\")","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2024-04-10T08:10:59.920652Z","iopub.execute_input":"2024-04-10T08:10:59.921356Z","iopub.status.idle":"2024-04-10T08:11:24.926265Z","shell.execute_reply.started":"2024-04-10T08:10:59.921322Z","shell.execute_reply":"2024-04-10T08:11:24.924942Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.execute(f\"\"\"\n    CREATE TABLE test_series AS\n    SELECT * \n    FROM read_parquet('{testing_parquet_table}');\n\"\"\")","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2024-04-10T08:11:24.928017Z","iopub.execute_input":"2024-04-10T08:11:24.928350Z","iopub.status.idle":"2024-04-10T08:11:24.944008Z","shell.execute_reply.started":"2024-04-10T08:11:24.928322Z","shell.execute_reply":"2024-04-10T08:11:24.942919Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# we intentionally load every col as VARCHAR; we need to load timestamp as a varchar, to prevent the UTC time casting at load\ncon.execute(f\"\"\"\n    CREATE TABLE train_events AS\n    SELECT * \n    FROM read_csv('{training_events_table}', ALL_VARCHAR=1, AUTO_DETECT=TRUE);\n\"\"\")","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2024-04-10T08:11:24.945395Z","iopub.execute_input":"2024-04-10T08:11:24.945718Z","iopub.status.idle":"2024-04-10T08:11:25.025362Z","shell.execute_reply.started":"2024-04-10T08:11:24.945691Z","shell.execute_reply":"2024-04-10T08:11:25.024147Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# inspired by \n# - https://www.kaggle.com/code/mitsuyasuhoshino/cmi-detectsleepstates-eda-visualization\n# - https://www.kaggle.com/code/carlmcbrideellis/zzzs-eda-utc-or-not-utc\ndef get_series_data_with_events(series_id):\n    \n    train_series_sql = f\"\"\"\n        SELECT\n        series_id,\n        CAST (step AS INTEGER) AS step,\n        timestamp,\n        CAST (anglez AS FLOAT) AS anglez,\n        CAST (enmo AS FLOAT) AS enmo\n        FROM train_series WHERE series_id = '{series_id}'\n    \"\"\"\n    df = con.execute(train_series_sql).fetchdf()\n    \n    #df['date_time'] = pd.to_datetime(df['timestamp'], utc=True)\n    df['date_time'] = pd.to_datetime(df['timestamp']).apply(lambda t: t.tz_localize(None))\n    df = df.drop('timestamp', axis=1)\n    \n    train_events_sql = f\"\"\"\n        SELECT \n            series_id,\n            CAST (night as INTEGER) as night,\n            event,\n            CAST (step as INTEGER) as step,\n            timestamp\n        FROM train_events \n        WHERE series_id = '{series_id}'\n    \"\"\"\n    \n    ev = con.execute(train_events_sql).fetchdf()\n    #ev['date_time'] = pd.to_datetime(ev['timestamp'], utc=True)\n    ev['date_time'] = pd.to_datetime(ev['timestamp']).apply(lambda t: t.tz_localize(None))\n    ev = ev.drop('timestamp', axis=1)\n    \n    df2 = pd.merge(df, ev, on='date_time', how='outer')\n    df2 = df2.drop(['series_id_x','series_id_y','step_x','step_y'], axis=1)\n    df2['event'] = df2['event'].fillna(0)\n    \n    # fill values for night feature\n    df2['night'][0] = 1\n    df2['night'] = df2['night'].interpolate()\n    df2['night'] = round(df2['night'])\n    # map numeric values to event feature\n    df2.loc[df2['event']=='onset','event'] = -1\n    df2.loc[df2['event']=='wakeup','event'] = 1\n    \n    return df2\n\ndef get_train_series_data(series_id):\n    train_series_sql = f\"\"\"\n        SELECT\n        series_id,\n        CAST (step AS INTEGER) AS step,\n        timestamp,\n        CAST (anglez AS FLOAT) AS anglez,\n        CAST (enmo AS FLOAT) AS enmo\n        FROM train_series WHERE series_id = '{series_id}'\n    \"\"\"\n    train_df = con.execute(train_series_sql).fetchdf()\n    return train_df\n\ndef get_test_series_data(series_id):\n    test_series_sql = f\"\"\"\n        SELECT\n        series_id,\n        CAST (step AS INTEGER) AS step,\n        timestamp,\n        CAST (anglez AS FLOAT) AS anglez,\n        CAST (enmo AS FLOAT) AS enmo\n        FROM test_series WHERE series_id = '{series_id}'\n    \"\"\"\n    df = con.execute(test_series_sql).fetchdf()\n    \n    return df\n","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2024-04-10T08:11:25.027208Z","iopub.execute_input":"2024-04-10T08:11:25.028403Z","iopub.status.idle":"2024-04-10T08:11:25.044573Z","shell.execute_reply.started":"2024-04-10T08:11:25.028357Z","shell.execute_reply":"2024-04-10T08:11:25.043461Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Checking Training Series Boundaries\n\nSince the training series data spreads on 2017-2019 sparsly, we need to check which series overlap with the dailight saving events.","metadata":{}},{"cell_type":"code","source":"def get_train_series_date_boundaries():\n    sql = f\"\"\"\n        WITH start_end AS (\n            SELECT\n            series_id,\n            MIN (timestamp) as series_start,\n            MAX (timestamp) as series_end\n            FROM train_series\n        GROUP BY 1\n        ),\n        over_dst_start AS (\n            SELECT DISTINCT\n                series_id,\n                '1' AS is_over_dst_start\n                FROM train_series\n                WHERE \n                timestamp LIKE '2017-03-12%' OR\n                timestamp LIKE '2018-03-11%' OR\n                timestamp LIKE '2019-03-10%'\n        ),\n        over_dst_end AS (\n            SELECT DISTINCT\n                series_id,\n                '1' AS is_over_dst_end\n                FROM train_series\n                WHERE \n                timestamp LIKE '2017-11-05%' OR\n                timestamp LIKE '2018-11-04%' OR\n                timestamp LIKE '2019-11-03%'\n        )\n        SELECT\n            start_end.series_id,\n            start_end.series_start,\n            start_end.series_end,\n            over_dst_start.is_over_dst_start,\n            over_dst_end.is_over_dst_end\n            FROM start_end\n            LEFT JOIN over_dst_start ON\n                (over_dst_start.series_id = start_end.series_id)\n            LEFT JOIN over_dst_end ON\n                (over_dst_end.series_id = start_end.series_id);\n        \n    \"\"\"\n    df = con.execute(sql).fetchdf()\n    \n    return df","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:11:25.046079Z","iopub.execute_input":"2024-04-10T08:11:25.046611Z","iopub.status.idle":"2024-04-10T08:11:25.063142Z","shell.execute_reply.started":"2024-04-10T08:11:25.046580Z","shell.execute_reply":"2024-04-10T08:11:25.061991Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_series_data = get_train_series_date_boundaries()\ntrain_series_data = train_series_data.fillna('0')\ntrain_series_data['is_over_dst_start'] = train_series_data['is_over_dst_start'].astype(int)\ntrain_series_data['is_over_dst_end'] = train_series_data['is_over_dst_end'].astype(int)\n\ndef sum_row(x):\n    return x['is_over_dst_start'] + x['is_over_dst_end']\n\ntrain_series_data['affected_by_dst'] = train_series_data.apply(sum_row, axis=1)\ntrain_series_data.head(10)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:11:25.064605Z","iopub.execute_input":"2024-04-10T08:11:25.065176Z","iopub.status.idle":"2024-04-10T08:11:39.490547Z","shell.execute_reply.started":"2024-04-10T08:11:25.065144Z","shell.execute_reply":"2024-04-10T08:11:39.489208Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### DST Change Effects on Training Series Data\n\nLet's check if any series goes through either the beginning and/or the end of DST period.","metadata":{}},{"cell_type":"code","source":"dst_df = train_series_data[ train_series_data['affected_by_dst'] > 1]\n\nmsg = f\"Number of training series going through both the DST start and DST end dates: {dst_df.shape[0]}\"\nPrintColor(f\"\\n---> {msg}\")\nif dst_df.shape[0] == 0:\n    PrintColor(f\"\\n---> As we can see, none of the training series is long enough to go through both thee DST start and DST end dates of the same year.\")","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:11:39.492318Z","iopub.execute_input":"2024-04-10T08:11:39.492793Z","iopub.status.idle":"2024-04-10T08:11:39.502173Z","shell.execute_reply.started":"2024-04-10T08:11:39.492751Z","shell.execute_reply":"2024-04-10T08:11:39.500774Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now, let's visualize the distribution of series that are\n\n- affeced by DTS change start\n- affected by DTS change end\n- not affected by DTS change","metadata":{}},{"cell_type":"code","source":"summmary_stat = {\n    'series_with': [\"DST start date\", \"DST end date\", \"None of DST dates\"],\n    'series_count': [\n        train_series_data['is_over_dst_start'].sum(),\n        train_series_data['is_over_dst_end'].sum(),\n        train_series_data.shape[0] - train_series_data['is_over_dst_start'].sum() - train_series_data['is_over_dst_end'].sum()\n    ]\n}\nsummary_df = pd.DataFrame(summmary_stat)\nfig = px.bar(summary_df, x='series_with', y='series_count', \n             title='Number of series overlapping with any of DST dates',\n            color_discrete_sequence=px.colors.qualitative.Prism) \nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:11:39.504067Z","iopub.execute_input":"2024-04-10T08:11:39.504539Z","iopub.status.idle":"2024-04-10T08:11:39.598391Z","shell.execute_reply.started":"2024-04-10T08:11:39.504496Z","shell.execute_reply":"2024-04-10T08:11:39.597211Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We can see that\n\n- the majority of the series in the training dataset (`234`) are not affected by DST change\n- `22` series overlap with DST change start dates\n- `21` series overlap with DST change end dates\n- none of the training series lasts through both DST change start and end dates","metadata":{}},{"cell_type":"markdown","source":"## <div style=\"font-size:20px;text-align:center;color:black;border-bottom:5px #0026d6 solid;padding-bottom:3%\">Series Start and End: Minute patterns</div>\n\nLet's look at the series start minutes.","metadata":{}},{"cell_type":"code","source":"def get_train_series_date_intervals():\n    sql = f\"\"\"\n        SELECT\n            series_id,\n            MIN (timestamp) as series_start,\n            MAX (timestamp) as series_end\n            FROM train_series\n        GROUP BY 1\n    \"\"\"\n    df = con.execute(sql).fetchdf()\n    \n    return df\n\ndf_train_series = get_train_series_date_intervals()\n\ndf_train_series['series_start'] = pd.to_datetime(df_train_series['series_start']).apply(lambda t: t.tz_localize(None))\ndf_train_series['series_end'] = pd.to_datetime(df_train_series['series_end']).apply(lambda t: t.tz_localize(None))\ndf_train_series['start_minute'] = df_train_series[\"series_start\"].dt.minute\ndf_train_series['end_minute'] = df_train_series[\"series_end\"].dt.minute","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:11:39.599969Z","iopub.execute_input":"2024-04-10T08:11:39.600380Z","iopub.status.idle":"2024-04-10T08:11:47.739388Z","shell.execute_reply.started":"2024-04-10T08:11:39.600350Z","shell.execute_reply":"2024-04-10T08:11:47.738476Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dfg = df_train_series['start_minute'].value_counts().reset_index()\ndfg.columns = ['start_minute', 'number_of_series']\nfig = px.bar(dfg, x='start_minute', y='number_of_series', \n             title='Training Series: Start minutes',\n            color_discrete_sequence=px.colors.qualitative.Prism) \nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:11:47.741229Z","iopub.execute_input":"2024-04-10T08:11:47.742141Z","iopub.status.idle":"2024-04-10T08:11:47.821710Z","shell.execute_reply.started":"2024-04-10T08:11:47.742097Z","shell.execute_reply":"2024-04-10T08:11:47.820772Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now, let's look at the series end minutes.","metadata":{}},{"cell_type":"code","source":"dfg = df_train_series['end_minute'].value_counts().reset_index()\ndfg.columns = ['end_minute', 'number_of_series']\nfig = px.bar(dfg, x='end_minute', y='number_of_series', \n             title='Training Series: End minutes',\n            color_discrete_sequence=px.colors.qualitative.Prism) \nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:11:47.823186Z","iopub.execute_input":"2024-04-10T08:11:47.823538Z","iopub.status.idle":"2024-04-10T08:11:47.903669Z","shell.execute_reply.started":"2024-04-10T08:11:47.823508Z","shell.execute_reply":"2024-04-10T08:11:47.902158Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## <div style=\"font-size:20px;text-align:center;color:black;border-bottom:5px #0026d6 solid;padding-bottom:3%\">Data Analytics: enmo (training series)</div>","metadata":{}},{"cell_type":"code","source":"def get_all_series_boxplot_data(table, col_name, where_clause = None):\n    min_max_sql = \"\"\n    quantile_sql = \"\"\n    if where_clause:\n        min_max_sql = f\"\"\"\n            SELECT series_id, \n            MIN({col_name}) AS min_value, \n            AVG({col_name}) AS mean_value, \n            MAX({col_name}) AS max_value,\n            stddev_pop({col_name}) AS stddev\n            FROM {table}\n            GROUP BY series_id\n            WHERE {where_clause}\n        \"\"\"\n        quantile_sql = f\"\"\"\n            WITH stats as (\n              SELECT\n                series_id,\n                percentile_disc([0.25, 0.50, 0.75]) WITHIN GROUP \n                  (ORDER BY {col_name}) AS percentiles\n              FROM {table}\n              GROUP BY series_id\n              WHERE {where_clause}\n            )\n            SELECT\n              series_id,\n              percentiles[1] as q1,\n              percentiles[2] as median,\n              percentiles[3] as q3\n            FROM stats;\n        \"\"\"\n    else:\n        min_max_sql = f\"\"\"\n            SELECT series_id, \n            MIN({col_name}) AS min_value, \n            AVG({col_name}) AS mean_value, \n            MAX({col_name}) AS max_value,\n            stddev_pop({col_name}) AS stddev\n            FROM {table}\n            GROUP BY series_id\n        \"\"\"\n        quantile_sql = f\"\"\"\n            WITH stats as (\n              SELECT\n                series_id,\n                percentile_disc([0.25, 0.50, 0.75]) WITHIN GROUP \n                  (ORDER BY {col_name}) AS percentiles\n              FROM {table}\n              GROUP BY series_id\n            )\n            SELECT\n              series_id,\n              percentiles[1] as q1,\n              percentiles[2] as median,\n              percentiles[3] as q3\n            FROM stats;\n        \"\"\"\n    df_mean_max = con.execute(min_max_sql).fetchdf()\n    df_quantile = con.execute(quantile_sql).fetchdf()\n    \n    df = pd.merge(\n        df_mean_max,\n        df_quantile,\n        how=\"inner\",\n        on='series_id')\n    \n    #rearrange the order of cols to make it a canonical boxplot style\n    df = df[['series_id', 'min_value', 'q1', 'median', 'q3', 'max_value', 'mean_value', 'stddev']]\n    \n    return df\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:11:47.905580Z","iopub.execute_input":"2024-04-10T08:11:47.906105Z","iopub.status.idle":"2024-04-10T08:11:47.917138Z","shell.execute_reply.started":"2024-04-10T08:11:47.906064Z","shell.execute_reply":"2024-04-10T08:11:47.915630Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def get_boxplot_data(table, col_name, where_clause = None):\n    min_max_sql = \"\"\n    quantile_sql = \"\"\n    if where_clause:\n        min_max_sql = f\"\"\"\n            SELECT '{col_name}' AS attribute, \n            MIN({col_name}) AS min_value, \n            AVG({col_name}) AS mean_value, \n            MAX({col_name}) AS max_value,\n            stddev_pop({col_name}) AS stddev\n            FROM {table}\n            WHERE {where_clause}\n        \"\"\"\n        quantile_sql = f\"\"\"\n            WITH stats as (\n              SELECT\n                percentile_disc([0.25, 0.50, 0.75]) WITHIN GROUP \n                  (ORDER BY {col_name}) AS percentiles\n              FROM {table}\n              WHERE {where_clause}\n            )\n            SELECT\n              '{col_name}' AS attribute,\n              percentiles[1] as q1,\n              percentiles[2] as median,\n              percentiles[3] as q3\n            FROM stats;\n        \"\"\"\n    else:\n        min_max_sql = f\"\"\"\n            SELECT '{col_name}' AS attribute, \n            MIN({col_name}) AS min_value, \n            AVG({col_name}) AS mean_value, \n            MAX({col_name}) AS max_value,\n            stddev_pop({col_name}) AS stddev\n            FROM {table}\n        \"\"\"\n        quantile_sql = f\"\"\"\n            WITH stats as (\n              SELECT\n                percentile_disc([0.25, 0.50, 0.75]) WITHIN GROUP \n                  (ORDER BY {col_name}) AS percentiles\n              FROM {table}\n            )\n            SELECT\n              '{col_name}' AS attribute,\n              percentiles[1] as q1,\n              percentiles[2] as median,\n              percentiles[3] as q3\n            FROM stats;\n        \"\"\"\n    df_mean_max = con.execute(min_max_sql).fetchdf()\n    df_quantile = con.execute(quantile_sql).fetchdf()\n    \n    df = pd.merge(\n        df_mean_max,\n        df_quantile,\n        how=\"inner\",\n        on='attribute')\n    \n    #rearrange the order of cols to make it a canonical boxplot style\n    df = df[['attribute', 'min_value', 'q1', 'median', 'q3', 'max_value', 'mean_value', 'stddev']]\n    \n    return df\n\ndef build_boxplot(table, col_name, where_clause = None):\n    df = get_boxplot_data(table, col_name, where_clause)\n    fig_title = \"\"\n    \n    bar_name = \"\".join([table, \".\", col_name])\n    \n    if where_clause:\n        fig_title = \"\".join([table, \".\", col_name, \": \", where_clause])\n    else:\n        fig_title = \"\".join([table, \".\", col_name, \": Entire table population\"])\n    \n    fig = go.Figure()\n\n    fig.add_trace(go.Box(\n        y= [ df['min_value'].iloc[0], df['max_value'].iloc[0] ],\n        name=bar_name, boxpoints=False,)\n      )\n\n    fig.update_traces(q1=[ df['q1'].iloc[0] ], median=[ df['median'].iloc[0] ],\n                  q3=[ df['q3'].iloc[0] ], lowerfence=[df['min_value'].iloc[0]],\n                  upperfence=[df['max_value'].iloc[0]], mean=[ df['mean_value'].iloc[0] ],\n                  sd=[ df['stddev'].iloc[0]]\n                 )\n    # Update visual layout\n    fig.update_layout(\n        showlegend=False,\n        width=600,\n        height=400,\n        autosize=False,\n        margin=dict(t=15, b=0, l=5, r=5),\n        title=dict(text=fig_title, font=dict(size=16), automargin=True, yref='paper'),\n        title_x=0.5, # center the title\n        template=\"plotly_white\",\n        colorway=px.colors.qualitative.Prism ,\n    )\n    \n    # update font size at the axes\n    fig.update_coloraxes(colorbar_tickfont_size=10)\n    # Update font in the titles: Apparently subplot titles are annotations (Subplot font size is hardcoded to 16pt · Issue #985)\n    fig.update_annotations(font_size=12)\n    # Reduce opacity\n    fig.update_traces(opacity=0.75)\n    \n    return fig\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:11:47.918667Z","iopub.execute_input":"2024-04-10T08:11:47.919566Z","iopub.status.idle":"2024-04-10T08:11:47.939722Z","shell.execute_reply.started":"2024-04-10T08:11:47.919531Z","shell.execute_reply":"2024-04-10T08:11:47.938441Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# https://duckdb.org/docs/sql/aggregates\ndef get_histogram_data(table, col_name, where_clause = None):\n    query = \"\"\n    if where_clause:\n        query = f\"\"\"\n            SELECT histogram({col_name}) FROM {table}\n            WHERE {where_clause};\n        \"\"\"\n    else:\n        query = f\"\"\"\n            SELECT histogram({col_name}) FROM {table};\n        \"\"\"\n    df = con.execute(query).fetchdf()\n    \n    hist_col_name = f\"histogram({col_name})\"\n\n    histogram_dict = df[hist_col_name].iloc[0] # dictionary: dict_keys(['key', 'value'])\n\n    frame = {\n        col_name: histogram_dict.get('key'),\n        'record_count': histogram_dict.get('value')}\n \n    # Creating DataFrame by passing Dictionary\n    agg_data = pd.DataFrame(frame)\n    return agg_data\n    \ndef build_histogram(table, col_name, where_clause = None):\n    figure_title = \"\"\n    if where_clause:\n        figure_title = f\"Distribution of {col_name} in {table}; filter: {where_clause}\"\n    else:\n        figure_title = f\"Distribution of {col_name} in {table}\"\n    \n    agg_data = get_histogram_data(table, col_name, where_clause)\n    \n    fig = px.histogram(agg_data, x=col_name, marginal=\"rug\",\n                   title=figure_title,\n                   color_discrete_sequence=px.colors.qualitative.Prism)\n    return fig\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:11:47.940906Z","iopub.execute_input":"2024-04-10T08:11:47.941704Z","iopub.status.idle":"2024-04-10T08:11:47.957051Z","shell.execute_reply.started":"2024-04-10T08:11:47.941670Z","shell.execute_reply":"2024-04-10T08:11:47.955629Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def get_histogram_data_2(table, col_name, where_clause = None):\n    query = \"\"\n    if where_clause:\n        query = f\"\"\"\n        SELECT FLOOR({col_name}/1.00)*1 AS {col_name}, \n           COUNT(*) AS record_count\n        FROM {table}\n        WHERE {where_clause}\n        GROUP BY FLOOR({col_name}/1.00)*1\n        ORDER BY 1\n        \"\"\"\n    else:\n        query = f\"\"\"\n        SELECT FLOOR({col_name}/1.00)*1 AS {col_name}, \n           COUNT(*) AS record_count\n        FROM {table}\n        GROUP BY FLOOR({col_name}/1.00)*1\n        ORDER BY 1\n    \"\"\"\n    df_agg = con.execute(query).fetchdf()\n    return df_agg\n\ndef build_histogram_2(table, col_name, where_clause = None):\n    fig_title = \"\"\n    if where_clause:\n        fig_title = f\"Distribution of {col_name}: {table}; filtered: {where_clause}\"\n    else:\n        fig_title = f\"Distribution of {col_name}: {table}\"\n    df_agg = get_histogram_data_2(table, col_name, where_clause)\n    fig = px.bar(df_agg, x=col_name, y=\"record_count\",\n                   title=\"Distribution of anglez: training series\",\n                   color_discrete_sequence=px.colors.qualitative.Prism)\n    return fig\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:11:47.958534Z","iopub.execute_input":"2024-04-10T08:11:47.959565Z","iopub.status.idle":"2024-04-10T08:11:47.973939Z","shell.execute_reply.started":"2024-04-10T08:11:47.959519Z","shell.execute_reply":"2024-04-10T08:11:47.972688Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def get_hourly_averages(table, col_name, where_clause = None):\n    query = \"\"\n    if where_clause:\n        query = f\"\"\"\n            WITH hours_of_day AS (\n                SELECT extract('hour' FROM \n                    strptime(substring(replace(timestamp, 'T', ' '), 1, length(timestamp)-5), '%Y-%m-%d %H:%M:%S')) AS hour_of_day,\n                    {col_name}\n                FROM {table}\n                WHERE {where_clause}\n            )\n            SELECT\n                hour_of_day, \n                AVG({col_name}) AS avg_{col_name}\n            FROM hours_of_day\n            GROUP BY 1\n            ORDER BY 1\n        \"\"\"\n    else:\n        query = f\"\"\"\n            WITH hours_of_day AS (\n                SELECT extract('hour' FROM \n                    strptime(substring(replace(timestamp, 'T', ' '), 1, length(timestamp)-5), '%Y-%m-%d %H:%M:%S')) AS hour_of_day,\n                    {col_name}\n                FROM {table}\n            )\n            SELECT\n                hour_of_day, \n                AVG({col_name}) AS avg_{col_name}\n            FROM hours_of_day\n            GROUP BY 1\n            ORDER BY 1\n        \"\"\"\n    df_agg = con.execute(query).fetchdf()\n    return df_agg\n\n\ndef build_hourly_averages_bar(table, col_name, where_clause = None):\n    y_variable = f\"avg_{col_name}\"\n    fig_title = \"\"\n    if where_clause:\n        fig_title = f\"Average {col_name} by hours in a day (local time); filtered: {where_clause}\"\n    else:\n        fig_title = f\"Average {col_name} by hours in a day (local time)\"\n    df_agg = get_hourly_averages(table, col_name, where_clause)\n    fig = px.bar(df_agg, x='hour_of_day', y=y_variable,\n                   title=fig_title,\n                   color_discrete_sequence=px.colors.qualitative.Prism)\n    return fig\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:11:47.975853Z","iopub.execute_input":"2024-04-10T08:11:47.976244Z","iopub.status.idle":"2024-04-10T08:11:47.991502Z","shell.execute_reply.started":"2024-04-10T08:11:47.976209Z","shell.execute_reply":"2024-04-10T08:11:47.990278Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's look at the summary statistics on *enmo* values in the training series","metadata":{}},{"cell_type":"code","source":"fig = build_boxplot('train_series', 'enmo', where_clause = None)\nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:11:47.993204Z","iopub.execute_input":"2024-04-10T08:11:47.993607Z","iopub.status.idle":"2024-04-10T08:11:53.904228Z","shell.execute_reply.started":"2024-04-10T08:11:47.993575Z","shell.execute_reply":"2024-04-10T08:11:53.902920Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's now look at the distribution of the *enmo* values in the training series","metadata":{}},{"cell_type":"code","source":"fig = build_histogram(table='train_series', col_name='enmo')\nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-10T08:11:53.905875Z","iopub.execute_input":"2024-04-10T08:11:53.906316Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = build_hourly_averages_bar('train_series', 'enmo')\nfig.show()","metadata":{"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## <div style=\"font-size:20px;text-align:center;color:black;border-bottom:5px #0026d6 solid;padding-bottom:3%\">Data Analytics: enmo (testing series)</div>\n\nLet's look at the summary statistisc on *enmo* on the testing series data.","metadata":{}},{"cell_type":"code","source":"df_test_enmo_summary = get_boxplot_data(\"test_series\", 'enmo')\n\ndf_test_enmo_summary.style.set_caption(\"Summary statistics on enmo: test series\"). \\\nset_properties(**{'border': '1.3px solid blue',\n                          'color': 'grey'})","metadata":{"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = build_boxplot('test_series', 'enmo', where_clause = None)\nfig.show()","metadata":{"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now, let's look at the distribution of *enmo* on the testing series data.","metadata":{}},{"cell_type":"code","source":"fig = build_histogram(table='test_series', col_name='enmo')\nfig.show()","metadata":{"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## <div style=\"font-size:20px;text-align:center;color:black;border-bottom:5px #0026d6 solid;padding-bottom:3%\">Data Analytics: anglez (training series)</div>\n\nLet's look at the summary statistics on *anglez* values in the training series","metadata":{}},{"cell_type":"code","source":"df_train_anglez_summary = get_boxplot_data(\"train_series\", 'anglez')\n\ndf_test_enmo_summary.style.set_caption(\"Summary statistics on anglez: train series\"). \\\nset_properties(**{'border': '1.3px solid blue',\n                          'color': 'grey'})","metadata":{"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = build_boxplot('train_series', 'anglez', where_clause = None)\nfig.show()","metadata":{"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now, let's look at the histogram of *anglez* to review its distribution on the train series data.","metadata":{}},{"cell_type":"code","source":"fig = build_histogram_2('train_series', 'anglez')\nfig.show()","metadata":{"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now, let's review the distribution of hourly averages of *anglez* acorss the hours in a day.","metadata":{}},{"cell_type":"code","source":"fig = build_hourly_averages_bar('train_series', 'anglez')\nfig.show()","metadata":{"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## <div style=\"font-size:20px;text-align:center;color:black;border-bottom:5px #0026d6 solid;padding-bottom:3%\">Data Analytics: anglez (testing series)</div>\n\nLet's look at the summary statistisc on *anglez* on the testing series data.","metadata":{}},{"cell_type":"code","source":"df_test_anglez_summary = get_boxplot_data(\"test_series\", 'anglez')\n\ndf_test_anglez_summary.style.set_caption(\"Summary statistics on anglez: test series\"). \\\nset_properties(**{'border': '1.3px solid blue',\n                          'color': 'grey'})","metadata":{"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = build_boxplot('test_series', 'anglez', where_clause = None)\nfig.show()","metadata":{"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now, let's look at the distribution of *anglez* on the testing series data.","metadata":{}},{"cell_type":"code","source":"fig = build_histogram(table='test_series', col_name='anglez')\nfig.show()","metadata":{"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]}]}