{"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":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\nimport pyarrow as pa\nimport pyarrow.parquet as pq\nimport dask.dataframe as dd\n\nimport datetime  as dt # Conersion of event into date time\nimport matplotlib.pyplot as plt\n\nimport pandas as pd\nimport seaborn as sns\n\n\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\n#import os\n#for dirname, _, filenames in os.walk('/kaggle/input'):\n#    for filename in filenames:\n#        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session\n\n#**************************** Duckdb ***********************#\n# Using duckdb for loading train parquet file in duckdb\n# https://duckdb.org/docs/data/parquet \n# https://www.kaggle.com/code/igjit1/fast-and-less-memory-data-processing-with-duckdb\n# https://www.kaggle.com/code/mauriciy/daily-prices-for-spanish-gas-stations-with-duckdb\n\n!pip install --no-cache-dir duckdb openpyxl\n\nimport duckdb","metadata":{"_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","execution":{"iopub.status.busy":"2023-04-08T04:53:23.014223Z","iopub.execute_input":"2023-04-08T04:53:23.014928Z","iopub.status.idle":"2023-04-08T04:53:36.851965Z","shell.execute_reply.started":"2023-04-08T04:53:23.014834Z","shell.execute_reply":"2023-04-08T04:53:36.850839Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Data Extraction and Data Processing.**","metadata":{}},{"cell_type":"code","source":"#Sensor Location using the file sensory Geometry as reference position\ndf_sl = pd.read_csv(\"/kaggle/input/icecube-neutrinos-in-deep-ice/sensor_geometry.csv\")\ndf_sl.sensor_id = df_sl.sensor_id.astype(\"category\")\ndf_sl\n#df_SensorID = df_sl.filter(items=[2024], axis=0)\ndf_SensorID = df_sl.iloc[1:1500]\ndf_SensorID","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:13:08.017534Z","iopub.execute_input":"2023-03-11T14:13:08.018032Z","iopub.status.idle":"2023-03-11T14:13:08.063733Z","shell.execute_reply.started":"2023-03-11T14:13:08.017985Z","shell.execute_reply":"2023-03-11T14:13:08.062182Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Create View on Train parquet, Test parquet DataSet as well as Train Meta , Test Meta.**","metadata":{}},{"cell_type":"code","source":"# Train\\Test Data and Train\\Test Meta View using duck db to read parquet file\ndbcon = duckdb.connect(database=\":memory:\")\ndbcon.execute(\n    \"CREATE VIEW 't_train' AS SELECT * FROM '/kaggle/input/icecube-neutrinos-in-deep-ice/train/*.parquet';\"\n    \"CREATE VIEW 'ts_test' AS SELECT * FROM '/kaggle/input/icecube-neutrinos-in-deep-ice/test/*.parquet';\"\n    \"CREATE VIEW 't_meta' AS SELECT * FROM '/kaggle/input/icecube-neutrinos-in-deep-ice/train_meta.parquet';\"\n    \"CREATE VIEW 'ts_meta' AS SELECT * FROM '/kaggle/input/icecube-neutrinos-in-deep-ice/test_meta.parquet';\"\n)                            ","metadata":{"execution":{"iopub.status.busy":"2023-04-08T04:54:05.515503Z","iopub.execute_input":"2023-04-08T04:54:05.515931Z","iopub.status.idle":"2023-04-08T04:54:05.695612Z","shell.execute_reply.started":"2023-04-08T04:54:05.515895Z","shell.execute_reply":"2023-04-08T04:54:05.694704Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"****Describe Train Data and Train Meta if necessary****","metadata":{}},{"cell_type":"code","source":"dbcon.execute(\n    \"DESCRIBE SELECT * FROM 't_meta';\").fetchall()","metadata":{"execution":{"iopub.status.busy":"2023-04-08T04:54:12.185846Z","iopub.execute_input":"2023-04-08T04:54:12.186222Z","iopub.status.idle":"2023-04-08T04:54:12.197458Z","shell.execute_reply.started":"2023-04-08T04:54:12.186193Z","shell.execute_reply":"2023-04-08T04:54:12.196431Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"stmt = \"\"\"\n    SELECT\n    distinct(batch_id)\n    FROM 't_meta'\n\"\"\"\ndf_meta = dbcon.execute(stmt).fetchdf()\ndf_meta.describe()","metadata":{"execution":{"iopub.status.busy":"2023-04-08T04:55:50.374164Z","iopub.execute_input":"2023-04-08T04:55:50.374579Z","iopub.status.idle":"2023-04-08T04:55:52.191630Z","shell.execute_reply.started":"2023-04-08T04:55:50.374550Z","shell.execute_reply":"2023-04-08T04:55:52.190574Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"stmt = \"\"\"\n    SELECT\n    batch_id,\n    event_id,\n    to_timestamp(event_id) as EventDate,\n    last_pulse_index,\n    first_pulse_index,\n    azimuth,\n    zenith\n    FROM 't_meta' \n    WHERE event_id in \n    (\n    select distinct(event_id) \n    FROM 't_train' \n    where sensor_id between '1' and '1500'\n    )\n    \n    LIMIT 10000000\n\"\"\"\n#    The conversion of Event ID into Time Stamp.\n#    The datediff('minute',to_timestamp(first_pulse_index) ,to_timestamp(last_pulse_index))*60000 as PD this assumption is not correct.\n#    The [azimuth/zenith] angle in radians of the neutrino, 1 radian approx = 57.3 Degree\n#    date_part('year', to_timestamp(event_id)) as EYear,\n#    date_part('month', to_timestamp(event_id)) as EMonth,\n#    date_part('day', to_timestamp(event_id)) as EDay,    \ndf_meta = dbcon.execute(stmt).fetchdf()\ndf_meta.describe()","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:13:09.188643Z","iopub.execute_input":"2023-03-11T14:13:09.189094Z","iopub.status.idle":"2023-03-11T14:22:06.926918Z","shell.execute_reply.started":"2023-03-11T14:13:09.189054Z","shell.execute_reply":"2023-03-11T14:22:06.923957Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#df_meta1 = pd.to_numeric(df_meta.Degree)\ndf_meta['PulseDuration'] = df_meta['last_pulse_index'] - df_meta['first_pulse_index']\ndf_meta['Degree']= (df_meta['azimuth']/df_meta['zenith'])*57.3\ndf_meta[['batch_id','event_id']]= df_meta[['batch_id','event_id']].astype(\"category\")\ndf_meta['event_dt'] = pd.to_datetime(df_meta['EventDate']).dt.normalize()\n#df_meta.describe()\ndf_meta.head()\n","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:22:06.929987Z","iopub.execute_input":"2023-03-11T14:22:06.932201Z","iopub.status.idle":"2023-03-11T14:22:19.735783Z","shell.execute_reply.started":"2023-03-11T14:22:06.932081Z","shell.execute_reply":"2023-03-11T14:22:19.731173Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Correlation in Meta File Check**","metadata":{}},{"cell_type":"code","source":"# Create a correlation matrix\ncorr = df_meta.corr()\n\n# Create a correlation plot using Seaborn\nsns.set(style=\"white\")\nmask = np.triu(np.ones_like(corr, dtype=bool))\nf, ax = plt.subplots(figsize=(10, 8))\ncmap = sns.diverging_palette(220, 10, as_cmap=True)\nsns.heatmap(corr, mask=mask, cmap=sns.cubehelix_palette(as_cmap=True), vmax=.5, center=0, square=True, linewidths=.5, cbar_kws={\"shrink\": .5},annot=True)\nax.set(xlabel=\"\", ylabel=\"\")\n#ax.xaxis.tick_top()\n# Add a title to the plot\nplt.title('Correlation Plot')\n\n# Display the plot\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:22:19.741605Z","iopub.execute_input":"2023-03-11T14:22:19.743428Z","iopub.status.idle":"2023-03-11T14:22:23.871661Z","shell.execute_reply.started":"2023-03-11T14:22:19.743199Z","shell.execute_reply":"2023-03-11T14:22:23.868736Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Reprocessing of Train Data with additional details for a sensor ID\nstmt = \"\"\"\n    SELECT \n    event_id,\n    sensor_id,\n    time as Time,\n    charge as Charge,\n    (1239.8/charge) as Wavelength,\n    (CASE \n    WHEN charge <= 1.99 THEN 1 \n    ELSE 0 END ) as SinglePhoton,\n    (CASE \n    WHEN auxiliary ='True' THEN 1 \n    WHEN auxiliary ='False' THEN 0  END ) as Auxiliary,\n    FROM 't_train' \n    where sensor_id between '1' and '1500'\n    order by\n    event_id,\n    sensor_id\n    LIMIT 10000000\n    \"\"\"\n#    https://www.kmlabs.com/en/wavelength-to-photon-energy-calculator\ndf_train = dbcon.execute(stmt).fetchdf()\ndf_train.describe","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:22:23.875468Z","iopub.execute_input":"2023-03-11T14:22:23.875931Z","iopub.status.idle":"2023-03-11T14:40:21.660204Z","shell.execute_reply.started":"2023-03-11T14:22:23.875894Z","shell.execute_reply":"2023-03-11T14:40:21.657138Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Conversion milliseconds = nanoseconds ÷ 1,000,000\ndf_train['Time']= pd.to_numeric(df_train['Time'])/1000000\ndf_train['IPT'] = df_train['Charge']/ df_train['Time']\ndf_train['HTZ'] = 1 / df_train['Time']\ndf_train.head()","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:40:21.662793Z","iopub.execute_input":"2023-03-11T14:40:21.664741Z","iopub.status.idle":"2023-03-11T14:40:22.136355Z","shell.execute_reply.started":"2023-03-11T14:40:21.664660Z","shell.execute_reply":"2023-03-11T14:40:22.134458Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create a correlation matrix\ncorr = df_train.corr()\n\n# Create a correlation plot using Seaborn\nsns.set(style=\"white\")\nmask = np.triu(np.ones_like(corr, dtype=bool))\nf, ax = plt.subplots(figsize=(10, 8))\ncmap = sns.diverging_palette(220, 10, as_cmap=True)\nsns.heatmap(corr, mask=mask, cmap=sns.cubehelix_palette(as_cmap=True), vmax=.5, center=0, square=True, linewidths=.5, cbar_kws={\"shrink\": .5},annot=True)\nax.set(xlabel=\"\", ylabel=\"\")\n#ax.xaxis.tick_top()\n# Add a title to the plot\nplt.title('Correlation Plot')\n\n# Display the plot\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:40:22.144076Z","iopub.execute_input":"2023-03-11T14:40:22.145228Z","iopub.status.idle":"2023-03-11T14:40:26.898910Z","shell.execute_reply.started":"2023-03-11T14:40:22.145135Z","shell.execute_reply":"2023-03-11T14:40:26.897016Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_cd = pd.merge(df_train, df_meta, how='inner', on ='event_id')\n#df_cd.describe()\n#df_cd.head()\nprint(df_cd.describe(),df_cd.head())","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:40:26.901314Z","iopub.execute_input":"2023-03-11T14:40:26.902644Z","iopub.status.idle":"2023-03-11T14:40:59.951091Z","shell.execute_reply.started":"2023-03-11T14:40:26.902573Z","shell.execute_reply":"2023-03-11T14:40:59.949392Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"corr_cd = df_cd.corr()\nsns.heatmap(corr_cd, cmap=sns.cubehelix_palette(as_cmap=True),annot=corr_cd.rank(axis=\"columns\"))","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:40:59.953205Z","iopub.execute_input":"2023-03-11T14:40:59.953633Z","iopub.status.idle":"2023-03-11T14:41:08.092467Z","shell.execute_reply.started":"2023-03-11T14:40:59.953595Z","shell.execute_reply":"2023-03-11T14:41:08.091187Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Data visualization","metadata":{"execution":{"iopub.execute_input":"2023-03-02T13:30:57.427597Z","iopub.status.busy":"2023-03-02T13:30:57.427037Z","iopub.status.idle":"2023-03-02T13:30:57.496039Z","shell.execute_reply":"2023-03-02T13:30:57.494565Z","shell.execute_reply.started":"2023-03-02T13:30:57.427555Z"}}},{"cell_type":"code","source":"# Load the data into a Pandas DataFrame\ndf_290 = df_cd[df_cd['sensor_id'] == 290]\ndf_290.set_index('EventDate', inplace=True)\n#df_290['Time_sq'] = np.square(df_290['Time'])\ndf_290['Wavelength_Log'] = np.log10(df_290['Wavelength'])\ndf_290['rolling_mean']  = df_290['Time'].rolling(window=2).mean()\ndf_290 = df_290.drop(['sensor_id','last_pulse_index','first_pulse_index'], axis=1)","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:41:08.094262Z","iopub.execute_input":"2023-03-11T14:41:08.095622Z","iopub.status.idle":"2023-03-11T14:41:08.140113Z","shell.execute_reply.started":"2023-03-11T14:41:08.095560Z","shell.execute_reply":"2023-03-11T14:41:08.138840Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create a correlation matrix\ncorr = df_290.corr()\n\n# Create a correlation plot using Seaborn\nsns.set(style=\"white\")\nmask = np.triu(np.ones_like(corr, dtype=bool))\nf, ax = plt.subplots(figsize=(10, 8))\ncmap = sns.diverging_palette(220, 10, as_cmap=True)\nsns.heatmap(corr, mask=mask, cmap=sns.cubehelix_palette(as_cmap=True), vmax=.5, center=0, square=True, linewidths=.5, cbar_kws={\"shrink\": .5},annot=True)\nax.set(xlabel=\"\", ylabel=\"\")\n#ax.xaxis.tick_top()\n# Add a title to the plot\nplt.title('Correlation Plot')\n\n# Display the plot\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:41:08.141917Z","iopub.execute_input":"2023-03-11T14:41:08.142349Z","iopub.status.idle":"2023-03-11T14:41:08.956924Z","shell.execute_reply.started":"2023-03-11T14:41:08.142310Z","shell.execute_reply":"2023-03-11T14:41:08.955210Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_290.head()","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:41:08.958869Z","iopub.execute_input":"2023-03-11T14:41:08.959351Z","iopub.status.idle":"2023-03-11T14:41:08.985903Z","shell.execute_reply.started":"2023-03-11T14:41:08.959307Z","shell.execute_reply":"2023-03-11T14:41:08.984894Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Set the palette to the \"pastel\" default palette:\ndf_290 = df_cd[df_cd['sensor_id'] == 290]\nsns.set_palette(\"RdPu\",2)\nsns.displot(data=df_290, x=\"Wavelength\", log_scale=True , height=10, aspect=1, facet_kws=None , hue=\"SinglePhoton\", multiple=\"stack\")","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:41:08.987972Z","iopub.execute_input":"2023-03-11T14:41:08.989401Z","iopub.status.idle":"2023-03-11T14:41:11.194282Z","shell.execute_reply.started":"2023-03-11T14:41:08.989347Z","shell.execute_reply":"2023-03-11T14:41:11.192849Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.displot(data=df_290, x=\"Time\", log_scale=True , height=10, aspect=1, facet_kws=None , hue=\"SinglePhoton\", multiple=\"stack\")","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:41:11.196078Z","iopub.execute_input":"2023-03-11T14:41:11.196565Z","iopub.status.idle":"2023-03-11T14:41:12.693212Z","shell.execute_reply.started":"2023-03-11T14:41:11.196522Z","shell.execute_reply":"2023-03-11T14:41:12.691717Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.displot(data=df_290, x=\"PulseDuration\", log_scale=True , height=10, aspect=1, facet_kws=None , hue=\"SinglePhoton\", multiple=\"stack\")","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:41:12.694822Z","iopub.execute_input":"2023-03-11T14:41:12.695263Z","iopub.status.idle":"2023-03-11T14:41:14.368929Z","shell.execute_reply.started":"2023-03-11T14:41:12.695226Z","shell.execute_reply":"2023-03-11T14:41:14.367083Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_1500 = df_cd\n# Set the palette to the \"pastel\" default palette:\nsns.set_palette(\"RdPu\",5)\nsns.scatterplot(data=df_1500, x=\"Wavelength\", y=\"Degree\", hue=\"sensor_id\")","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:41:14.370980Z","iopub.execute_input":"2023-03-11T14:41:14.371462Z","iopub.status.idle":"2023-03-11T14:45:37.586713Z","shell.execute_reply.started":"2023-03-11T14:41:14.371424Z","shell.execute_reply":"2023-03-11T14:45:37.585189Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.scatterplot(data=df_1500, x=\"Wavelength\", y=\"Time\", hue=\"sensor_id\")","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:45:37.588629Z","iopub.execute_input":"2023-03-11T14:45:37.589094Z","iopub.status.idle":"2023-03-11T14:50:03.338345Z","shell.execute_reply.started":"2023-03-11T14:45:37.589053Z","shell.execute_reply":"2023-03-11T14:50:03.336453Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.scatterplot(data=df_1500, x=\"Degree\", y=\"Time\", hue=\"sensor_id\")","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:50:03.340752Z","iopub.execute_input":"2023-03-11T14:50:03.341225Z","iopub.status.idle":"2023-03-11T14:54:28.606276Z","shell.execute_reply.started":"2023-03-11T14:50:03.341182Z","shell.execute_reply":"2023-03-11T14:54:28.604526Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Distribution Plot of all sensor events on a day\ndf_1970 = df_cd[df_cd['event_dt'] == \"1970-01-01\"]\n# Set the palette to the \"pastel\" default palette:\nsns.set_palette(\"RdPu\",2)\nsns.displot(data=df_1970, x=\"Wavelength\", log_scale=True , height=10, aspect=1, facet_kws=None , hue=\"SinglePhoton\", multiple=\"stack\")","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:54:28.608495Z","iopub.execute_input":"2023-03-11T14:54:28.608963Z","iopub.status.idle":"2023-03-11T14:54:32.153883Z","shell.execute_reply.started":"2023-03-11T14:54:28.608920Z","shell.execute_reply":"2023-03-11T14:54:32.152206Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.displot(data=df_1970, x=\"PulseDuration\", log_scale=True , height=10, aspect=1, facet_kws=None , hue=\"SinglePhoton\", multiple=\"stack\")","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:54:32.156154Z","iopub.execute_input":"2023-03-11T14:54:32.156829Z","iopub.status.idle":"2023-03-11T14:54:34.149005Z","shell.execute_reply.started":"2023-03-11T14:54:32.156777Z","shell.execute_reply":"2023-03-11T14:54:34.147856Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.displot(data=df_1970, x=\"Time\", log_scale=True , height=10, aspect=1, facet_kws=None , hue=\"SinglePhoton\", multiple=\"stack\")","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:54:34.150931Z","iopub.execute_input":"2023-03-11T14:54:34.151900Z","iopub.status.idle":"2023-03-11T14:54:37.102859Z","shell.execute_reply.started":"2023-03-11T14:54:34.151853Z","shell.execute_reply":"2023-03-11T14:54:37.101221Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_ASE = df_cd.drop(['batch_id','event_id'], axis=1) # All Sensors and All Events\n# Correlation of time series reference https://www.tensorflow.org/tutorials/structured_data/time_series\n# Refining the Date Format\ndate_time = pd.to_datetime(df_ASE.pop('EventDate'), format='%d.%m.%Y %H:%M:%S')\n\n# Ploting the chart\nplot_cols = ['Time', 'Charge', 'PulseDuration','Wavelength']\nplot_features = df_ASE[plot_cols]\nplot_features.index = date_time\n_ = plot_features.plot(subplots=True)\n\nplot_features = df_ASE[plot_cols][:480]\nplot_features.index = date_time[:480]\n_ = plot_features.plot(subplots=True)","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:54:37.104907Z","iopub.execute_input":"2023-03-11T14:54:37.105747Z","iopub.status.idle":"2023-03-11T14:58:39.954339Z","shell.execute_reply.started":"2023-03-11T14:54:37.105689Z","shell.execute_reply":"2023-03-11T14:58:39.952259Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Distribution fittness / Normalization Check\ndf_KUCD = df_cd\ndf_KUCD = df_KUCD.drop(['batch_id','event_id','event_dt','sensor_id','EventDate'], axis=1)\n#df_KUCD.head()\n#print(df_KUCD['azimuth'].describe() , df_KUCD['zenith'].describe())\n\nfrom scipy.stats import kurtosis\nfrom scipy.stats import zscore\n\n# calculate the kurtosis of each column\nkurt = df_KUCD.kurtosis()\n\n# calculate the z-score of each column\nzscore = df_KUCD.apply(zscore)\n\n# print the results\nprint(kurt, zscore)\n\ndf_KUCD.describe()","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:58:39.956897Z","iopub.execute_input":"2023-03-11T14:58:39.957910Z","iopub.status.idle":"2023-03-11T14:58:57.668681Z","shell.execute_reply.started":"2023-03-11T14:58:39.957855Z","shell.execute_reply":"2023-03-11T14:58:57.667053Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Assign back the dataframe to consolidated data frame of train and meta\ndf_rcd = df_cd\n# Join the dataframes, consolidated data frame of train and meta with Sensor positions\ndf_rcd = pd.merge(df_rcd, df_SensorID, how='inner', on ='sensor_id')\ndf_rcd.head()","metadata":{"execution":{"iopub.execute_input":"2023-03-02T17:55:40.994885Z","iopub.status.busy":"2023-03-02T17:55:40.994405Z","iopub.status.idle":"2023-03-02T17:55:51.303854Z","shell.execute_reply":"2023-03-02T17:55:51.302517Z","shell.execute_reply.started":"2023-03-02T17:55:40.994845Z"}}},{"cell_type":"markdown","source":"df_rcd['X'] = df_rcd.apply(lambda row: np.cos(df_rcd.azimuth) * np.sin(df_rcd.zenith), axis = 1)\ndf_rcd['Y'] = df_rcd.apply(lambda row: np.sin(df_rcd.azimuth) * np.sin(df_rcd.zenith), axis = 1)\ndf_rcd['Z'] = df_rcd.apply(lambda row: np.cos(df_rcd.zenith), axis = 1)\ndf_rcd.head()","metadata":{}},{"cell_type":"markdown","source":"# Assign back the dataframe to consolidated data frame of train and meta\ndf_rcd = df_cd\n# Filter on one sensor ID \ndf_sid = df_rcd[df_rcd['sensor_id'] == 290]\ndf_sid = df_sid.drop(['batch_id','sensor_id','event_id','event_dt','Degree'], axis=1)\ndf_sid.set_index('EventDate', inplace=True)\ndf_sid['rolling_mean']  = df_sid['Time'].rolling(window=2).mean()\ndf_sid['Wavelength'] = np.log10(df_sid['Wavelength'])\ndf_sid[np.isnan(df_sid['rolling_mean'])] = 0\ndf_sid.head()","metadata":{}},{"cell_type":"code","source":"# Assign back the dataframe to consolidated data frame of train and meta\n\n# Filter on one sensor ID \ndf_290 = df_cd[df_cd['sensor_id'] == 290]\ndf_290 = df_290.drop(['batch_id','event_id','event_dt','Degree'], axis=1)\ndf_290['rolling_mean']  = df_290['Time'].rolling(window=2).mean()\ndf_290['Wavelength'] = np.log10(df_290['Wavelength'])\ndf_290[np.isnan(df_290['rolling_mean'])] = 0\ndf_290.head()","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:58:57.670446Z","iopub.execute_input":"2023-03-11T14:58:57.670861Z","iopub.status.idle":"2023-03-11T14:58:57.771397Z","shell.execute_reply.started":"2023-03-11T14:58:57.670825Z","shell.execute_reply":"2023-03-11T14:58:57.770046Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Cluster regression prediction\n#Time Series Dataframe with EventDate as date time stamp , sensor_id as category, Wavelength\tas numerical field, SinglePhoton and Auxiliary as categorical field, Time as numerical , Charge as numerical, PulseDuration as numerical, rolling_mean as numerical, azimuth\tas numerical and zenith as numerical.\n#Python script for cluster regression prediction of azimuth and zenith as output, based on hybrid predictive model with k mean clustering model based on Wavelength , timedur and classification based on SinglePhoton and Auxiliary and input numerical variables as Time, Charge, PulseDuration and rolling_mean for each sensor_id.\n#Perform the model accuracy with split of training data.\n\nimport pandas as pd\nfrom sklearn.cluster import KMeans\nfrom sklearn.preprocessing import LabelEncoder, StandardScaler\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.linear_model import LinearRegression\nfrom sklearn.metrics import r2_score\n\n# Load the time series dataframe\ndf = df_290\n\n# Preprocess the data\nle = LabelEncoder()\ndf[\"SinglePhoton\"] = le.fit_transform(df[\"SinglePhoton\"])\ndf[\"Auxiliary\"] = le.fit_transform(df[\"Auxiliary\"])\nscaler = StandardScaler()\ndf[[\"Time\", \"Charge\", \"PulseDuration\", \"rolling_mean\"]] = scaler.fit_transform(df[[\"Time\", \"Charge\", \"PulseDuration\", \"rolling_mean\"]])\n\n# Cluster the data based on wavelength\nkmeans = KMeans(n_clusters= 5, random_state=42)\ndf[\"WavelengthCluster\"] = kmeans.fit_predict(df[[\"Wavelength\"]])\n\n# Split the data into training and testing sets\nX = df.drop([\"EventDate\", \"azimuth\", \"zenith\"], axis=1)\ny = df[[\"azimuth\", \"zenith\"]]\nX_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)\n\n# Train a linear regression model for each cluster\nmodels = []\nfor i in range(5):\n    cluster = X_train[X_train[\"WavelengthCluster\"] == i]\n    cluster_y = y_train[X_train[\"WavelengthCluster\"] == i]\n    lr = LinearRegression()\n    lr.fit(cluster.drop(\"WavelengthCluster\", axis=1), cluster_y)\n    models.append(lr)\n\n# Make predictions on the test set\ny_pred = []\nfor i in range(len(X_test)):\n    cluster = X_test.iloc[[i]]\n    lr = models[kmeans.predict(cluster[[\"Wavelength\"]])[0]]\n    y_pred.append(lr.predict(cluster.drop(\"WavelengthCluster\", axis=1))[0])\n\n# Evaluate the model's accuracy\nprint(\"R-squared score:\", r2_score(y_test, y_pred))\nprint('Predicted azimuth and zenith:', y_pred[1:20])","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:58:57.778138Z","iopub.execute_input":"2023-03-11T14:58:57.778649Z","iopub.status.idle":"2023-03-11T14:59:10.006131Z","shell.execute_reply.started":"2023-03-11T14:58:57.778608Z","shell.execute_reply":"2023-03-11T14:59:10.004070Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_cd.head()\ndf = df_cd\ndf = df.drop([\"batch_id\", \"event_dt\", \"last_pulse_index\",\"first_pulse_index\"], axis=1)\ndf[['sensor_id','event_id','SinglePhoton','Auxiliary']]= df[['sensor_id','event_id','SinglePhoton','Auxiliary']].astype(\"category\")\n\ndf['Wavelength'] = np.log10(df['Wavelength'])\ndf['PulseDuration'] = np.log10(df['PulseDuration'])\ndf['IPT'] = df_train['Charge']/ df_train['Time']\n\n# Sort the DataFrame by sensor_id and event_id\ndf = df.sort_values([\"sensor_id\", \"event_id\"])\n# Create a new column for the relative time duration in nanoseconds\ndf['time_rt'] = df.groupby(['sensor_id','event_id'])['Time'].diff()\n#df[np.isnan(df['time_rt'])] = 0\n#df['time_rmt'] = df.groupby('sensor_id', 'event_id')['Time'].apply(lambda x: (x - x.mean())/x.mean())\n#df = df.sort_values(\"event_id\")\n\ndf.head()","metadata":{"execution":{"iopub.status.busy":"2023-03-11T14:59:10.008811Z","iopub.execute_input":"2023-03-11T14:59:10.009387Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.displot(data=df, x=\"time_rt\", log_scale=False , height=5, aspect=1, facet_kws=None , hue=\"SinglePhoton\", multiple=\"stack\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Considering distance  ρ as 1\nx = cos(azimuth) * sin(zenith)\ny = sin(azimuth) * sin(zenith)\nz = cos(zenith)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.cluster import KMeans\nfrom sklearn.preprocessing import StandardScaler\nimport tensorflow as tf\n\n# Load data\ndf = df\n\n# Define input and output columns\n# inputs = ['Wavelength', 'Time', 'Charge', 'PulseDuration', 'time_rt', 'SinglePhoton', 'Auxiliary']\ninputs = ['Wavelength','PulseDuration','time_rt','IPT','HTZ','SinglePhoton','Auxiliary']\n\noutputs = ['azimuth', 'zenith']\n\n# Split data into training and testing sets\ntrain_data, test_data = train_test_split(df, test_size=0.2, random_state=42)\n\n# Define function for preprocessing data\ndef preprocess_data(data):\n    # Select input and output columns\n    X = data[inputs]\n    y = data[outputs]\n    \n    # Normalize input data\n    scaler = StandardScaler()\n    X_norm = scaler.fit_transform(X)\n    \n    # Perform clustering based on Wavelength and Time\n    kmeans = KMeans(n_clusters=3, random_state=42)\n    clusters = kmeans.fit_predict(X_norm[:, :3])\n    \n    # One-hot encode categorical variables\n    single_photon = pd.get_dummies(X['SinglePhoton'], prefix='single_photon')\n    auxiliary = pd.get_dummies(X['Auxiliary'], prefix='auxiliary')\n    \n    # Combine numerical and categorical variables\n    X_processed = np.hstack([X_norm, single_photon, auxiliary, clusters.reshape(-1, 1)])\n    \n    return X_processed, y\n\n# Preprocess training data\nX_train, y_train = preprocess_data(train_data)\n\n# Define neural network model\nmodel = tf.keras.Sequential([\n    tf.keras.layers.Dense(64, activation='relu', input_shape=(X_train.shape[1],)),\n    tf.keras.layers.Dense(32, activation='relu'),\n    tf.keras.layers.Dense(2)\n])\n\n# Compile model with mean squared error loss and Adam optimizer\nmodel.compile(loss='mse', optimizer='adam')\n\n# Train model\nmodel.fit(X_train, y_train, epochs=10, batch_size=32)\n\n# Preprocess testing data\nX_test, y_test = preprocess_data(test_data)\n\n# Evaluate model on testing data\ntest_loss = model.evaluate(X_test, y_test)\n\nprint(f'Test loss: {test_loss}')\n","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Describe Test Data and Test Meta\ncon.execute(\n    \"DESCRIBE SELECT * FROM 'ts_test';\").fetchall()","metadata":{}},{"cell_type":"markdown","source":"stmt = \"\"\"\n    SELECT \n*\n    FROM 'ts_test' \n    \n    LIMIT 10000000\n\"\"\"   \ndf_test = con.execute(stmt).fetchdf()\ndf_test.describe()","metadata":{}},{"cell_type":"markdown","source":"# Describe Test Data and Test Meta\ncon.execute(\n    \"DESCRIBE SELECT * FROM 'ts_meta';\").fetchall()","metadata":{}},{"cell_type":"markdown","source":"stmt = \"\"\"\n    SELECT \n*\n    FROM 'ts_meta' \n    \n    LIMIT 10000000\n\"\"\"   \ndf_test = con.execute(stmt).fetchdf()\ndf_test.describe()","metadata":{}}]}