{"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"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Key Findings:\n- 44 series have timestamps affected by daylight saving time (DST)\n- Converting to UTC automatically handles DST when converting from local time","metadata":{}},{"cell_type":"code","source":"import numpy as np \nimport pandas as pd \nimport matplotlib.pyplot as plt\nimport polars as pl","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":true,"execution":{"iopub.status.busy":"2023-09-08T09:40:02.264597Z","iopub.execute_input":"2023-09-08T09:40:02.264974Z","iopub.status.idle":"2023-09-08T09:40:02.862980Z","shell.execute_reply.started":"2023-09-08T09:40:02.264946Z","shell.execute_reply":"2023-09-08T09:40:02.861559Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"DIR=\"/kaggle/input/child-mind-institute-detect-sleep-states\"","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2023-09-08T09:40:02.865193Z","iopub.execute_input":"2023-09-08T09:40:02.865637Z","iopub.status.idle":"2023-09-08T09:40:02.871435Z","shell.execute_reply.started":"2023-09-08T09:40:02.865613Z","shell.execute_reply":"2023-09-08T09:40:02.870308Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n# get timezone counts from timestamps\n(pl.scan_parquet(f\"{DIR}/train_series.parquet\")\n    .select(\n        (pl.col(\"timestamp\")\n        .str.extract(r\"(.{5})$\")\n        .alias(\"timezone\"))\n    )\n    .group_by(\"timezone\")\n    .count()\n    .collect()\n    .to_pandas()\n    .set_index(\"timezone\")\n)","metadata":{"execution":{"iopub.status.busy":"2023-09-08T09:40:02.872991Z","iopub.execute_input":"2023-09-08T09:40:02.873354Z","iopub.status.idle":"2023-09-08T09:40:24.405060Z","shell.execute_reply.started":"2023-09-08T09:40:02.873325Z","shell.execute_reply":"2023-09-08T09:40:24.403973Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#convert timestamps to UTC\ntrain_utc = (pl.scan_parquet(f\"{DIR}/train_series.parquet\")\n                 .with_columns((pl.col(\"timestamp\")\n                                  .str.strptime(pl.Datetime, \n                                                \"%Y-%m-%dT%H:%M:%S%z\") #parse timezone\n                               )\n                 )\n)\n\n#keep local timestamps\ntrain_local = (pl.scan_parquet(f\"{DIR}/train_series.parquet\")\n                .with_columns((pl.col(\"timestamp\")\n                                 .str.strptime(pl.Datetime, \n                                               \"%Y-%m-%dT%H:%M:%S%Z\") #ignore timezone\n                              ) \n                )\n)","metadata":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2023-09-08T09:40:24.407478Z","iopub.execute_input":"2023-09-08T09:40:24.407826Z","iopub.status.idle":"2023-09-08T09:40:24.424411Z","shell.execute_reply.started":"2023-09-08T09:40:24.407804Z","shell.execute_reply":"2023-09-08T09:40:24.423263Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n# get row counts for each series\ndf_counts = (pl.scan_parquet(f\"{DIR}/train_series.parquet\")\n                 .group_by(\"series_id\", maintain_order=True)\n                 .count()\n                 .collect())","metadata":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2023-09-08T09:40:24.425895Z","iopub.execute_input":"2023-09-08T09:40:24.426947Z","iopub.status.idle":"2023-09-08T09:40:27.250794Z","shell.execute_reply.started":"2023-09-08T09:40:24.426917Z","shell.execute_reply":"2023-09-08T09:40:27.249522Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\ndf_max_utc = (train_utc.group_by(\"series_id\", maintain_order=True)\n                        .max()\n                        .collect())\n\ndf_max_local = (train_local.group_by(\"series_id\", maintain_order=True)\n                        .max()\n                        .collect())\n\ndf_min_utc = (train_utc.group_by(\"series_id\", maintain_order=True)\n                    .min()\n                    .collect())\n\ndf_min_local = (train_local.group_by(\"series_id\", maintain_order=True)\n                    .min()\n                    .collect())","metadata":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2023-09-08T09:40:27.252501Z","iopub.execute_input":"2023-09-08T09:40:27.252882Z","iopub.status.idle":"2023-09-08T09:43:03.603692Z","shell.execute_reply.started":"2023-09-08T09:40:27.252855Z","shell.execute_reply":"2023-09-08T09:43:03.602558Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# verify that timezone parsing didn't alter the size of our samples\nassert sum(abs(df_max_utc[\"step\"] - df_max_local[\"step\"])) == 0\n\n# check for missing entries in the \"step\" column\nskipped_steps = df_counts[\"count\"]-df_max_utc[\"step\"] > 1\nprint(f\"{skipped_steps.sum()} series with missing 'step' entries\")","metadata":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2023-09-08T09:43:03.606543Z","iopub.execute_input":"2023-09-08T09:43:03.606812Z","iopub.status.idle":"2023-09-08T09:43:03.628049Z","shell.execute_reply.started":"2023-09-08T09:43:03.606791Z","shell.execute_reply":"2023-09-08T09:43:03.626808Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"If we calculate the duration of each series, we should be able to divide that duration into a number of five second increments equal to the total steps in the series.\n\nThis is true for all series if we convert the timestamps to UTC time zone.  \n\nThis is NOT true for 44 series if we keep the timestamps in their local time zone.","metadata":{}},{"cell_type":"code","source":"# use max and min timesteps to calculate series duration and expected number of steps\ndf_utc = (df_max_utc.to_pandas()\n                   .set_index(\"series_id\")\n                   .assign(duration = df_max_utc[\"timestamp\"] - df_min_utc[\"timestamp\"],\n                           expected_steps = lambda x: (x[\"duration\"].dt.seconds + (x[\"duration\"].dt.days *24*60*60))/5 \n                    )\n)\n\ndf_local = (df_max_local.to_pandas()\n                   .set_index(\"series_id\")\n                   .assign(duration = df_max_local[\"timestamp\"] - df_min_local[\"timestamp\"],\n                           expected_steps = lambda x: (x[\"duration\"].dt.seconds + (x[\"duration\"].dt.days *24*60*60))/5 \n                    )\n)","metadata":{"execution":{"iopub.status.busy":"2023-09-08T09:43:03.629714Z","iopub.execute_input":"2023-09-08T09:43:03.630100Z","iopub.status.idle":"2023-09-08T09:43:03.660295Z","shell.execute_reply.started":"2023-09-08T09:43:03.630078Z","shell.execute_reply":"2023-09-08T09:43:03.659549Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(f\"{df_utc[df_utc.step != df_utc.expected_steps].shape[0]} \\\nseries_ids have a timestep anomaly when we use UTC time zone\")\n\nprint(f\"{df_local[df_local.step != df_local.expected_steps].shape[0]} \\\nseries_ids have a timestep anomaly when we use local time zones\")","metadata":{"execution":{"iopub.status.busy":"2023-09-08T09:43:03.661311Z","iopub.execute_input":"2023-09-08T09:43:03.661930Z","iopub.status.idle":"2023-09-08T09:43:03.672411Z","shell.execute_reply.started":"2023-09-08T09:43:03.661900Z","shell.execute_reply":"2023-09-08T09:43:03.670977Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# DST series summary","metadata":{}},{"cell_type":"code","source":"#Display timestamp anomaly table\n(df_local[df_local.step != df_local.expected_steps]\n     .rename(columns = {\"timestamp\": \"end_timestamp\", \"step\": \"actual_steps\"})\n     .assign(anomaly_steps = lambda x: x[\"actual_steps\"] - x[\"expected_steps\"],\n             anomaly_hours = lambda x: x[\"anomaly_steps\"]/(12*60),\n             month = lambda x: x[\"end_timestamp\"].dt.month)\n     .loc[:,[\"anomaly_hours\", \"end_timestamp\", \"duration\", \"month\", \"actual_steps\", \"expected_steps\", \"anomaly_steps\"]]\n     .sort_values([\"anomaly_hours\",\"month\",\"end_timestamp\"])\n)","metadata":{"execution":{"iopub.status.busy":"2023-09-08T09:43:03.674663Z","iopub.execute_input":"2023-09-08T09:43:03.675199Z","iopub.status.idle":"2023-09-08T09:43:03.717787Z","shell.execute_reply.started":"2023-09-08T09:43:03.675175Z","shell.execute_reply":"2023-09-08T09:43:03.716891Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"All anomalies are equivalent to a 1 hour shift.  Magnitude and timing of the anomalies are consistent with daylight saving time in the United States. ","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"}}