{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"Recently, I wrote notebook [Can we speed up dataloader IO👀](https://www.kaggle.com/code/roger92/can-we-speed-up-dataloader-io) to discuss how slow the dataloader IO will be and it's no way to optimize our model in such a situation. Later, I notice the in [graphnet_example](https://www.kaggle.com/code/rasmusrse/graphnet-example), they use sqlite to do data IO parts, and I find that it's quiet fast! However as a code frame, it's too tightly packaged, so I read their source code and find most relevant parts to create a simple version of dataloader.","metadata":{}},{"cell_type":"markdown","source":"Here is what I did:\n1. Save our data to sqlite, before that we can do \"data process\".\n2. Try not to do anykind of calculation in `__getitem__`(pytorch) or `get`(torch_geometric), especially one using pandas\n\nHere is my thoughts:\n1. Yes we can use  `maker_dataloader` from \"graphnet.training.utils import maker_dataloader\" to do get our data and their code are no doubt more decent. But I think it's also worth to get a \"common\" code that we can use in other competitions as we can use them as part of our pipline\n2. The key function is `attach_index`, if we want to reuse the code, we need to care about that👀\n\n","metadata":{}},{"cell_type":"markdown","source":"Code's performance locally, on batch file(in kaggle environments it's slower😂, but still takes about a minute):\n\n![image.png](attachment:d3a8ae72-4fde-46bd-bc8b-b8925f628c50.png)","metadata":{},"attachments":{"d3a8ae72-4fde-46bd-bc8b-b8925f628c50.png":{"image/png":"iVBORw0KGgoAAAANSUhEUgAAAqAAAABNCAYAAAB5edawAAAgAElEQVR4nO3dd3xUVd748c+dCUkmQwikB9NIgRBAgoKAgICi9LJAaBYQdG3L7rrrgr/ncV1d0QUfWFGUVSwrTUCK1ABSBIkKBJIAoSYkAUJ6JqSXSXJ+f0wS0u+EDAHlvF+7vsicued87ylzzty5RRk4eKigjsK8HEpKSuq+LEmSJEmSJEktprnTAUiSJEmSJEn3FrkAlSRJkiRJklqVXID+ygihwX/4y/zx6b44iXpnT0iSdBvJ8SdJkmQZVpbKSAh3xv6/+Tym+4VP3tpAnFDwHPM6cx3DmL/qtKWKkbDG3tkVV7cO2AFZdzocCQAh2vH4X94mKPJNPj6cd6fDaZCw9ebRadMY0tUFu7IbJEbt5dstEWRUKC3PWzgzYt7/4nPkb6w4Wnbr+QRPZ9F0wZdvrCNWMT8uS5Wv7s6Nv16zljAzRFvrtXPrX6veXyE0uPaZxpzQPriVHmN5nToU9gE8MWk8Azq7Yms0EH9iB+u3nyW3GfUsSZJkKRZbgJoYMeq680DgRuIuyaMDt4OiFBO99i2iTX9YPH8hdPg+0BvXG9FEJNydCymp+YTQ0+/plxlmG863yyPJsvVnWOg0Zg9PZtHu5Dsd3q/G7R5/TdFqFDKOrOCzw+nVr5XklwMghC3+I55n9lA7Yk5ewqFH7W2FaEu/abMZbHOEbz/bQLZ9N8ZPm8m0zAWs+EWOc0mSWp+FF6ClJCbm061XZ5SLF+qlCltvhoZOZnBXN3RGA5ePb2fDznPkKgruI+fxkn4Lb268zuC57zHo6lIWbEtm0Mv/xOvw66w71/SHfdUREN+zmynqMYJujhVknP2eNWuPkFpu2jYw9G1m6X7mUNu+DPOxJe9aBN+t2sLZXEU1PlO6J4MmTWRo8H20FblciQpjw3eRZFVUpXsxJDSUod09sDNmk3B8O9/sOEOOqEy3D2Lc0xPp6+OETikiI+4IG1fv4XKReemBoW/zygAH03tT9rJwYRhpVbFV7/8minuMJLhDOekxYaz+5ijpVfHpOjH8qWk8EuBAccJ+fs4ZxGNs4H/XnauuR0UpIqvMh2ef74ntB59wJMMyXyTU4qs6gu5+8DW+OG6aVP0mvcUL+i3MX3Ua4Tac1192Iy7Bnz5eF9m0t5xRE7tj2PcZn+y/hlBZDJjTv9TaX63/1N5fLT6jXuWVnnF8uvg7EsoU1f6h2j9V+geAsO3Ok/OeJtiwh4+WHSS9ul488PUu58yaHZy6ogDJ7Dr2EP/j54tWXKe8qh/pezFr3gy6Zu9l6Qf7SG3uIqtDH56aN5oejuWkxYSx9pujpFX1Pzt/Hp86gYFd3NCV5XAlahcbtkSRJRRE96d4f86D2FSW98qH/UzbXNnJP/69j1xFUR1/AIpjX2bOb7j/q2nZ+LPlkVf+xaTOtctK2buQRbtTK9um6fY3vaex9gONRsFYaCAzM7OB6Nvj3zGbHUs/5ajDJHrUWYCi8cXf00D452GcTlKAJMKi+vNCYCf4Rf5CJUlS67PwOaBtyDkfTUG3EALr5CyElsDRsxjlkcSuz5exfEMkbfrPZEpfewDSM7PQd3BCiyvOyg1wckGLG46OWWSkmR+BV5AHlzd/yidf/0hB4ARCBznVjrBzEO0iVrNs+XpirPsx43e9sBVCNT4hNASMmsV4zwz2frmMZasOU9RtOk8OcalODxwzh+HtL7F5+b/5aPURikOeZkblhAXQ6bHJPGITw9pli3j/kw2ctxnEpKE+ZqcnhH3IggULWLgzron9dydu86d8svoopcGTmdS/XWV8Cr5PzGC463V2fbactSccCA6ybjCPvNMbWHVcz+hZo/C2suyR7MbiM4udIwXhq/mpoBeP+15i1d4kvAY8hLcZm6r1L7X2r9JY/6nLynck04dYEb5+h2nxaUb/UMtfrX+YMmiLg94anYM9trUSrnM5AfxCumGvCITOm97BTly9HFe9+ATAuh0Oeius27VDZ0a91uXVtSPxWz7lk1XhFAdNJnSgI2AaHz0mzWaI7jxbVyxj2dc/UBg8ndCq9o/bzgeLFrFw0ylK8qNZt3AhCxcuZNGqX8hHffxVlx90H3GbP+PjNccwdmte/2rZ+CshYs27LFiwgAULFvDuisOklOeRfD2nev/Naf/G2k8IjemAq0s/nn99Ae+98wZzp/TGWVvV99LY//VqjqUaG9w3RcTwzT8WsydJqc7PTtcGY0mp2fUjSZJkSRY+AqpBkxnJqbw/8UBnLeG10lzoHNCe2ENbOJFQBiSxJ7I/v+/iC8djKE/PILeDI07aCjoUXiTR1hknjRFHfSaJBvMjSI/+np/icirzf5iXfLyBmxlorx5j2/F4yhSF5IMhDJjgjwdRxKvEBy50CXTk0qGFHI83pW/b4cyYDg4oIgNBR4K76Di/bQdnrihACntOPMxfA/whPAqAtno9ZVlXuXQtjXIlnW3LztSKXS29rMBAZgFY5Tc+aWREH+SnuGwgiQMxg5jhdR9wAehAYIAziT99xC/xeYjLyRwP6c/4BvJQFCOXt3/N3pf/yDOTE1iy7ixFFvq5sfH4zFCeydXYWIzXCvFMPk9CnCuGJ5yxM2dT1f6l1v4mjfefm4SVL2OnD0L8uJxdCWWVP9Wq94+m8k9AvX8AKHlHWfHPS9iVZ5NXo80UpYiIdV/Q/tmZzPtrOgZ7T7RnvuHzA2lAjfdlH+Y/b5/DviwLwy20eUb0fn6uHH+7T/bnRV8f+DEbRakgZvX/8kb1O68Rfn44z/p4wc/nUIpzSU3NRTgWI4TAkJJy8+irooBQG38mWaf381OcAbjGgZiBTG9G/2rJ+FMUQXFuJsWA0HoyamY/bE6sYtOpwma1f2PtBwpaDbj7u3Bi3RccsOnK6ClTCE2+yH/C81AUgRDmt5fGZRCP9iglckWs2dtIkiRZkoUXoKAoGURG3+CPvTrzc61Ti+zQ2xkpKjBSNeEVFBRh49wWrRCUpRnIau+DkytoDBdIc++BSwcjTjnppFdQc44EIDAwkNjY+h+exYUF1f8uLCyijYstWiGqj/IYC/Mpq/x3+eVwNu7WVC5PVeJDXy/dELWVVaadBmGNjbU1Ic8u5v6KqrrQor1kU11+XGQkBbOn8/d5A7iSdI3EM8f56UwKpZXxqKWbo6jwZqUXl5TSRl91lFNPW10FeTm5gIKilJGdU1DdA9we/zOvPuZ+MyNRQRla7PynMPL422yJr32Ur7H6V0tvPL5bpCh1u0bD1PqXSvur9x8Tv7FvsnisBqvSaL7cnUhFddup9w+1/M3tH+UFBho6q6+tT09CHPM4f/Q0ye1gYNc+BDmf41hGRa33GfMyUPvOZ+74s64x/toFj+XJcb3xcbRDq4CisaL8lLkfQWrjz/Sugvzc6i2KipvXvywx/oSwwm/0kwxpc5QVm2JqfHEzr/2hsfar4My2ZSQZk7maVQwksiugLy/4+kJ4/S8iTcZo68eEOaOwj/iKsLiyVj+XVZIkCW7DAhQgPfIUOX/uRaejzfj5tiiNrPIQnLw1kPkjGfb9cfVxpF3WFdIbeHtTix9zKXnxHD9GrQmsZYo5s/lD9tQ8HFaaUz25FJ/fyOKFUXTvEYiPVwCDZw6k554lLD2QZlZ6yyj1FvE1ZR75kvdPtqnxip6g8XMYpdvP4YT63wDU6t8S7WNRzexf5mio/yT98B++ifHmdy89QZ9eemJOFtbYoun+0WT+tKx/CE0gY6ffT8LKd9kcX44QPxEzbC6vTe5PzPJwCpq5CGlu+wptN8Y9NQDtkdUsj86iVID3sJeYdBfdCM4S40/XZQJPDijjwLJtXDbWrdPmtX9NiiLIS42vtTAtyC/A2tEOjRA1vug0TWjcGfrcbB68sZ2PtlygWC4+JUm6Q27Px39mFKeyuxHsXV7jxUIKCtug099c5Oj1OkqK8is/gNPINLTHN9CG3PR80jMVPLp0oDQjzewPVwBbO331v+3sdJQWF5v1Aa8eX1X6zSMqTg9N4bnHA1CEAEopKW2DpjSD1NRUUlNTMRQpWNcoWtFoKTHEcuLQbjavXsZnP2Th06Vz9Tl+auktU0hRsQY7O9MP1kIo6O1unmVWXpyHwWDAYDCQlZWNpttYxvgmsnH1EbKa8dPerSugsAh0+trxFRXkWyh/tf6l1v7mKc3PICXxMFsPZ9NtxAh8q8/RU+8fasztH4qVLdbaOn2mvTf3tTOQlmK6ZY+iCDKSMxAe9+FhfgiqGh1/7T1w1yURdSiGqymppKSkUSDa1M9ANPZdUG38tVxLx5/QdSN0Rm9uhK1i37XyOqnmt39D7SeEHr++Q+nudnMDvV5HSXGB+YtPxYHez7zAE9ojrPgq3CK335IkSbpVt2UBqihZRJ424Otb8wKBdC7F3SBw8EQe7OSJd/DjjHjAjtiLiZXb5JKWaYu/P2SkQ0ZWKf6Bbhgym3f0zzVkGA8HeOLVdRgjHmhH0tWrZm7ZdHyQzoW4bDoPmcxDfp54dnmECSN7Y2fMrrwCO5lzF/Pp8ugkenfyoGNAP6b+4S9M6Fm1oLKmz+z3eOu5YQR5u+Lq2YUQv/YU3siixKx0K/SOzjg7O+Pc1hq0drR3ccHZ2RG9WRcKGbhyNY9OfR+jk16LzmsoffwafqeVz3BmjenAyTXrOZXXOpOUouRx6VIqPg9PoK+/J749xzKsu5HYC0kWyl+tf6m1f3PKEiQf2Em0dX/GPuJc+WrT/UONWv+ofp++N3P++R7vvjYKz5oLpxsJJGb7MmTaowR7ueMZ8BChI0NQriZyvVY5Lgx9fj7zZw3E4RYWdm4PDOfhAFP9jXywHdeuXDElZKeSXuzFQ8N74ePhSdDDoQwOaCD/vFzy9V50C/bB3d0dd4eqL0lq469lWjr+hNDRM3Qq3bN/IOyswMnFBWdnZ5zsqxbZ5rV/o+1HMTZ+Q5kROpLuXh7V/TOusn8KocXexR13d3c8nGzRKLY4enjg7u5YeZFlGwLGvcgU36vs2HKSIgdTfM6VR1AlSZJa2235CR4gM/IU6aPG4Fr5t6JUELvra8KmTGbM83OxK8vm8i8r2Xgsr/onxozMXDp0LyXDAMYMA1aO3qSnFdHkb8d1XD2fjN/EFxnvVEHG2a2sOZJl1vZq8SlKBZd3rWT75ImMmDMXvcjlStQ61v6Yiemcygpid37FnilTGPviX7A1ZnMlci1rf6hKLyVi63o6zRjDM3NHY1NRiOFaBOt2n0UoCgpNpyO8GfWnuQxwqNqXAF56YxBC5BC+7E02XVbfv/P7tnDqxRn88Z1HMWZHcSyxBN867xNCR0Dn9sRvW8XWS0Wten7YtX0r2dJhKsPnzMXOmMXFfSvZGlNisRia6l/m9M/mUErPE7Y/kdeHj6bHLys5U9x0/1DNT6X/VKsoobjUSGlRCTWvh1Yq4tm2YiPaSY8x4w+jsanII+X8PlZsPlb7AjNdAEFB7oj9ceTcwn5fO59C4KQXGe9YQfqZLaw9YgAUlIoYdn5zlBnjQ3m5vyA77hBHYgrx0dfN4GfCIroy8dk/M8RaQ0nkV8xfdVp1/LWUav2qjj8HvDvaY+M+kj++MbI635rxm9X+jbWfUs65rf9l/9SJTH5lMLYlBuKPreLbiLzK7V0YPGc+w9xv5jX99RCEOMe61z7jWJkDAZ3dsWnvwdS/9qx+j8g4yJIF27DM1zxJkiTzKQMHD6339bcwL4eSkpKG3n/XqrrPpPePf+PzY7fzSSi/bkKxol0HPcacG/iELuAp1vD39RfvdFgtJvwm8OZLA6h/050LbJj/BSfkz41mEUFTee8Fbw699z77Mn47dSb7hyRJ0t3lth0BvVPkOfWNE8KKtk7tsVHA1iWYbt46Mo5n3OmwLOPqAT5bcrSBc0pKuVGOJQ6S3RPcfX3QXYvkZDq/rTqT/UOSJOmu8ptbgEpN6cTYP79MP3uFCmM+6ZfCWPeTeaco3O2UsjzSUxt5pKD8VmIWIdrQyduFxKiTt3QP0LuZ7B+SJEl3l9/MT/CSJEmSJEnSr8NddBc+SZIkSZIk6V4gF6CSJEmSJElSq5ILUEmSJEmSJKlVyQWoJEmSJEmS1KrkAlSSJEmSJElqVffEbZiE6M3zS5+keyO3W6lIiiL2vhC6yHSZLtNl+j2WLq7v5t3395Apb0clSVIr+s0tQIVQ8Ok7mk45hzl8sZH7/kmSJEn1/Jo+P+27DOERpyt8/1M8xrtk8Sx6PcPiGT1Mf2Qe4oOFO0m+S2KTpJqaO36EbX9efGciAQCk8v37i1v8tLzf4E/wGjqGPEY/v7oPmZYkSZKa9uv4/BSiHb2Hj6W7Q9Fds/gEQKvFKuckK5csYclXP5J2p+ORflOE0OA//GX++HRfnES9W7g3I59bGD8l0WxcsoQly77nmpUVGgusHi16BFQIDa59pjEntA9upcdY/sY6YmvsnND78fjkCQzs4oau7AaJkbv4dvspsiqfw9xUerseoTw/pTeuSiZRm79kfZQBIezp+9zLBEV8zKrTBZbcFUmSJMlMQlgx8OX/I7RL7cns1Jo/8d8TptcU+y6MmDKefp1dsDXe4ErU92zaepz08srPf5X5oxbHBwnxTSd6QwqgtH75TSnLJzMlhVRFqfWUrZbMf+ZQi7/XrCXMDNHW2ubc+tdYcbTMvPxvd/y3OX/V8u0DeGLSeAZ0dsXWaCD+xA7Wbz9LbmUdtqT+RLuh/OXtcfjU6U+iJJIv560kRlHMzN8ae2dXXN06YAdk1S0neDqLpgu+VOu7dcaPORRRhCG1CGHli9GsLdRZbAEqhC3+I55n9lA7Yk5ewqFH3XRr7v/dswxzjmLj59+SoevCyKlP82ROMh/9kNlk+ocHS+g99AFyD/yHTcahPP/ogzhFfk/JA5MYWv49H5/Kl4/TkyRJumPKObnmXS7aANjiNXgqE/yvExFrShXCiaGzZjNA/MimT09hsPHn0clTeb48i399d5lydE3OH3U5PdATr5Ro1qRSOX+2bvnN1ZL576MfMs3Iv+n5F0CrUcg4soLPDqdXv1aSX36XxH9781cvvy39ps1msM0Rvv1sA9n23Rg/bSbTMhew4hfTqSgtqT+Ta+z9YBURlcfK2j00kz/0yKQqenPyV5Riote+RbTpj+bvaKX64+fOsOAR0Pb4d8xmx9JPOeowiR71BoAnQYEKUd98x8kEASSxObwn/xPUBeuDGZQ0lX4gBo1VOYWZqaQYCynTatHY3c/EJ7QcWB5JgVx8SpIk3TGKIijOy6SowJmHpk/j0TZH+fSDI6QYKz+bHXrQ0y+dQwt2Ep2lAEms2dWJ98b3wve7y1xWnT9uEsKRB0K8uR69lozKz/7WLP/WtGD+O5hBaeV+CtvuPDnvaYINe/ho2UHSq+c+9fg1GgVjoYHMTPMWbLbuPRniX8De8FjEbY/fMvnfMo0v/p4Gwj8P43SSqX+ERfXnhcBO8MvpW6q/WsqzuXb5CtfTM8gsUhDCiZCuHbl+cqXpSLkZ+QeGvs0rAxwAECl7WbgwjLSqeu3+FO/PeRCbyr9f+bCf6fUrO/nHv/dVH8WFhscPgLAPYtzTE+nr44ROKSIj7ggbV+/hctHtW19ZcAGaxv6vVyOEAg4Npduhsy3CUFBB1ZI7v7AIxVaHLVDSRLpOuUFMZApDnv0Xi5QiEsK+xH3SZGwO/YcTeXLxKUmSdFcIfIzxvd2xyunH8MHJbNh3mSJFAVsbbDBiLLn51nKjkQqtVeWFCGrzRw3ODxBy33Wiv06n3uGb1ij/ltz6/GcLlFZl06YtDnprdOX22NbKv+n4hdCYDpi59OP513vTSV9Mypk9rNscQWZ57Tq0db+fwcOHMyhQQ9zuta0Uv4Xyv0WKiOGbf8RU5y2EBjtdG4wlpc2uvwbzL4hm08fR1fnj0ZfeHlf45fMMTKeQqOefEPYhC37QYBUyjfkP1ikgbjsfLNoPASN5dYRgy8d7uAJgzCO/bjCNjJ9Oj03mEZvTfLXsKAatG/3GTWXS0PO8H3a1OVXZLBZbgCqKMHV+Ve0Y8PxrDEz+mg9vmJ+e8sNy3jrhiIOSS8F9k/hTQDifHs2RP71LkiTdLRJ2s+z9n3HwGcDvJs1kXNI7bLhQBpkJJOYNZ+CEPlzYFEWWjR8jh92P9tpWkmjO/AFuvUPoeC2arzKp//NhK5TfMrc2/1VR8o6y4p+XsCvPJq/G3Kcev4JWA+7+LpxY9wUHbLoyesoUQpMv8p9w00/Mtm73M3j4EwwM1BD70/d8vOEUqcXCNMdWX+9ye+K3VP1YisZlEI/2KCVyRWxV5Kr1Zy4hFPwf7o1T3H6O51DZh9XzLyswkFkAVvn1l9tKcS6pqbkIx2KEEBiqzkGGemukxsZPW72esqyrXLqWRrmSzrZlZ5q1X7fiDtyGKZejaxYTbcyFvuanK4qgLD+LTOtApo13JjZGx4sLRmF34zRbvljPqRy5EJUkSWoNgYGBxMbG1ntdKc0lLSWX1OSNfB/0HmODveFCPErFJcLWH+H3T03n9femU5CWhtHFSPR/IyhuxkEEIVx4sGdHEk98haGB7W53+Wr7r+7W5r+aygsMNP8GWRWc2baMJGMyV7OKgUR2BfTlBV9fCD+DEP6MeWkaXqc38vG7kaQVN1Yntzv+ludvjqbaT9j6MWHOKOwjviIsrqxyAdd0/TWLVWf69tJzbmuk6eg8WDb/JjQ1fuIiIymYPZ2/zxvAlaRrJJ45zk9nUlp+ekMT7shtmMqL8igoa3ynGksXworAsRNwPhqFboAbCZtXsd8QzKhHvG9nuJIkSVINdSdvoVijb6enjWI6VKYo5eTkFaHX37ydU96571j85pu8885b/PdiG9omH+L7mGZeT+vem/vdr3DmZO3rf1ut/Eq3tvg0udX5ryUURZCXGl+5uDEpyC/AWmeHRggggwuR12nXZwJTJzxGkFPjx6Zud/ytUT+NLj417gx9bjYP3tjOii0Xqr+cqNef+ax79KWnJoaI6KLq1yyZf5MaGT8Axec3snjh5+yMiCfXyofBM//Cy8PcLVd2A1pxAVpIUbEOvf5mkW3tdFBaUnn+hlo6tPEbze86nuTbI0bsNEnERF0g4lIq+rZtW283JEmSpNqsevH0W39m+H2mP4XQ4GCvo6iw9u3xlLICsghh1MNtiNh1qNZFEObo+OD9uCac5kTdn19bqfxb1/L5r4piZYu1tnmLEiH0+PUdSne3m/ur1+soKS6gQlFQlFxitn/CO/9aSVRZV6b+7U3+PGMYwc5tWil+y+V/q4TiQO9nXuAJ7RFWfBVORo3bO6nVX01avSP21o08dUzoePChbpScPs7Zspqvm59/0ztR42yJBjQ6fgBFo6XEEMuJQ7vZvHoZn/2QhU+XzthacgFch8UWoEJosXdxx93dHQ8nWzSKLY4eHri7O1buQBIXYgW9xvyOXp088Q5+jIkDO3L14sXKbxlNpwuNN6Mm+hD17Q+kl+dTVHEfQd39CfF3ozBf3gNUkiTpjik9z9m4dvQdN4JuXp74953ME8FFnDl1pdbbhGhD91HD8EzYx+4Lxhqvq80fIIQHvXu6knDqRP3zB1uh/JZp2fxXHae+N3P++R7vvjYKzxpxqcdfjI3fUGaEjqS7lwfewY8z4gE74i4m1oqyIjeeI5s+4Z33VhJpDGLK2AfQWmD+VovfYvkLB4IfG8OYgf7Najch2hAw7kWm+F5lx5aTFDm44OzsjLNj1RFI8+pP2Pfn92/+nbf+Nhavhspv14c+XQqIOnYJUasPN52/EFboHZ1NMbW1Bq0d7V1ccHZ2RG9Vo5y8XPL1XnQL9sHd3R13h5uXejU1foSwps/s93jruWEEebvi6tmFEL/2FN7IooTbx4LngLoweM58hrnf3LHpr4cgxDnWvfYZx8tLOf3df3ELncD45/uhJ5crJ9ex+mAaoKAojacLocVnxGT8zqzjw9QK4AKH9g5g1tMv8GD2GbbsvMIdvZmVJEnSPUxRcglfs5L2M8Yxfe4w2hSmELPjK3bEltW6CELj8SijexVz5KOf60yCavMH4N2b+50SOByZS93P+1Ypv0X1c+vzX619rSihuNRIaVFJnZuBq8Vfzrmt/2X/1IlMfmUwtiUG4o+t4tuIvHp1CVCRe5kfN35MuFZrOkLK7Y3fYvVj150hox+j3YFodjbr6LYDAZ3dsWnvwdS/9qx+VWQcZMmCbSQpZtZfWQF5RaUU5+U3eGTWpW8fOmWcZGNCRa1+qajm782oP81lgEPVNgG89MYghMghfNmbbI6vfPnaz4RFdGXis39miLWGksivmL/KdBuppsdPKRFb19NpxhiemTsam4pCDNciWLf7bJ2FsmVZ8Cr4VHYufJWdDaea/lsQz/6v/83+BtKaSleUCq6G/ZulpoJQqCDp8OcsOFw/D0mSJKn1idxz7Pj0HDtqvlj3SEvqXt5/bW+9bc2ZP3x73U+H+MNENbxmuu3lm811KK8tHoyo8yz4W53/av1VdIY1b82vCrpZ8StFCRz8egkHm8i/rorymyvv2xm/pfLX+PvizXX2H79WL60pipLJ3v97lfo942ac5tSfUnSaNW+drrVdTZn7l/CX/Q2nNZW/osSz8R+vsrHh6GuEmk3UusVErav/LtXxkxnFho+i2NBI3jefBa9BSzrxdTO4BXfgKnhJkiRJMp8Q3vTq6Ujcgai7+8EjMVtZ9P5u07/LC8i4s9Hcczz9fLFK/InjDdwi9l5mkfFTEs3GJQlYA1BGngU6929uAaoo5Rxd8SpH73QgkiRJvzJ36+enolxl6zt/vdNhqFKKs0lNrfmCXAW1FiHaEuDTnrgTEbWe/CNZZvxUPQu+xistC4rf4AJUkiRJkqR7i6Lk88Oyv/HDnQ5EMpsycPDQepdqFeblUFJyO699kiRJkiRJku5Vd+RG9JIkSZIkSdK9Sy5AJUmSJEmSpFYlF6CSJEmSJElSq0CL7ccAABMNSURBVJILUEmSJEmSJKlVyQWoJEmSJEmS1KrkbZgkSZKkViF6PcPiGT1Mf9R5UpDqttVPYgFI5fv3F7MvQ97vUZJ+reQCVJKkViOEhoARLzLa9SRrVx0lS94w+t6i1WKVc5IvvzhEZnOfFFT1JBbrHkx59QE08vc7SfpVs9gCVAhnRsz7X0beZ5pQykpyyIg/yc6NOzlrqLBUMb8qgaFvM8dmI6+vibnTodz1hGjH4395m6DIN/n4cN6dDqdBwtabR6dNY0hXF+zKbpAYtZdvt0SQUdHyRVTV+PE58jdWHC279XyCp7NouuDLN9YR24zFnaXKV2eNvbMrrm4dsAOybmNJdfWatYSZIdpar51b/1r1/gqhwbXPNOaE9sGt9BjL69ShsA/giUnjGdDZFVujgfgTO1i//azFnrqiVr5a/C3N39z+LRQ3hr/6V3pd/Jj3d15BNHf/y/LJTEkhVVGa9aSgqiexCCtfjM0rUZKku5DFj4DG7VzI+ugytHbu9Bo7jZlP5bHow4PySEclIXT4PtAb1xvRRCTcnQstqT4h9PR7+mWG2Ybz7fJIsmz9GRY6jdnDk1m0O/lOh/eroSjFRK99i2jTH61atlajkHFkBZ8dTq9+rSS/HAAhbPEf8Tyzh9oRc/ISDj1qbytEW/pNm81gmyN8+9kGsu27MX7aTKZlLmDFLy0fx2rlq8Xf0vyb07/b9R3Pow6nWLnvFhafkiRJlSy+AC3NzyQzswzIZPeWn+k1rxvB+oMcKTQdEZyl+5lDbfsyzMeWvGsRfLdqC2dzTR9iwtaboaGTGdzVDZ3RwOXj29mw81z1EQZh68mgSRMZGnwfbUUuV6LC2PBdJFkVVeleDAkNZWh3D+yM2SQc3843O86QIyrT7YMY9/RE+vo4oVOKyIg7wsbVe7hcZGa6Wv7tgvndMxN5yKstBYn7+eVG/fpRlCKyynx49vme2H7wCUcy6j2IqkFVR6h8z26iuMdIgjuUkx4TxupvjpJeoSCEO2P/33zcD77GF8dNk5LfpLd4Qb+F+atOI9yG8/rLbsQl+NPH6yKb9pYzamJ3DPs+45P911QnEveR83hJv4U3N15n8Nz3GHR1KQu2JTPo5X/idfh11p1TVNtPrf1r768Wn1Gv8krPOD5d/B0JZYpq/av2L5X2NbVxd56c9zTBhj18tOwg6dX14oGvdzln1uzg1BUFSGbXsYf4Hz9ftOI65VV9VN+LWfNm0DV7L0s/2Gc6ytMcHfrw1LzR9HAsJy0mjLXfHCWtqn/b+fP41AkM7OKGriyHK1G72LAliiyhILo/xftzHsSmsrxXPuxn2ubKTv7x733kKorq+AFQHPsyc379/mUOtfoNDH2bVwY4mN6bspeFC8NIq6o3Ycsjr/yLSZ1rl5WydyGLdqdWtk3T7W96T2PtBxqNgrHQQGZmZgPRt8e/YzY7ln7KUYdJ9Ki7ANT44u9pIPzzME4nKUASYVH9eSGwE/xy2qzym6ZSvmr8Lc3fzP5t25VxI/2J3/Mu50stu/g0Z3xKkvTbcXvPojEaKUOLVY1fjdp0DqJdxGqWLV9PjHU/ZvyuF7ZCIISWwNGzGOWRxK7Pl7F8QyRt+s9kSl97oPLcsVGzGO+Zwd4vl7Fs1WGKuk3nySEu1emBY+YwvP0lNi//Nx+tPkJxyNPMqJzwADo9NplHbGJYu2wR73+ygfM2g5g01MesdLX8hbCix7gZDLC7xOZPP2bNzzo6d7ZusFryTm9g1XE9o2eNwtvKvAVoFa8gd+I2f8onq49SGjyZSf3bmb+xnSMF4av5qaAXj/teYtXeJLwGPIS3GZumZ2ah7+CEFleclRvg5IIWNxwds8hIQ7X9qjTW/nVZ+Y5k+hArwtfvMC0+zWhftfzV2t+UQVsc9NboHOyxrZVwncsJ4BfSDXtFIHTe9A524urluOrJGQDrdjjorbBu1w6dGfVal1fXjsRv+ZRPVoVTHDSZ0IGOgKn/9Zg0myG682xdsYxlX/9AYfB0QqvaP247HyxaxMJNpyjJj2bdwoUsXLiQRat+IR/18VNdftB9xG3+jI/XHMPYrXn9S61+E8I+ZMGCBSzcGdfA1iVErHmXBQsWsGDBAt5dcZiU8jySr+dU77857d9Y+wmhMR1wdenH868v4L133mDulN44a6v6Xhr7v17NsdSGf9xVRAzf/GMxe5KU6vzsdG0wlpSaVb66pstXj79l+ZvTv4XQ4PP4OELyDrL9aE6z9s4cZo1PSZJ+M27bRUiKlRO9RvTFLf0UsblA5RytvXqMbcfjKVMUkg+GMGCCPx5EEY8LnQPaE3toCycSyoAk9kT25/ddfOF4DOBCl0BHLh1ayPF4U/q2Hc6M6eCAIjIQdCS4i47z23Zw5ooCpLDnxMP8NcAfwqMAaKvXU5Z1lUvX0ihX0tm27EytmJtOV8vflU7edlw6sIWTCWUIcZ1feg7Et6G6UYxc3v41e1/+I89MTmDJurMUmXmkJCP6ID/FZQNJHIgZxAyv+4AL5jVKeSZXY2MxXivEM/k8CXGuGJ5wxs6cTdMzyO3giJO2gg6FF0m0dcZJY8RRn0miAVBtP5PG2/8mYeXL2OmDED8uZ1dCWeVPtert21T+Cai3P4CSd5QV/7yEXXk2eTXaRFGKiFj3Be2fncm8v6ZjsPdEe+YbPj+QRnXnBpTsw/zn7XPYl2VhuIWfJzOi9/NzXA6QxO6T/XnR1wd+zEZRKohZ/b+8Uf3Oa4SfH86zPl7w8zmU4lxSU3MRjsUIITBUnWNnCh6E2vgxyTq9n5/iDMA1DsQMZHoz+pda/ZYVGMgsAKv80nrbKoqgODeTYkBoPRk1sx82J1ax6VRhs9q/sfYDBa0G3P1dOLHuCw7YdGX0lCmEJl/kP+F5KIpACPPbS+MyiEd7lBK5Irb2fjRaftPUy286/pbmb1b/dn6ECYN0HP/8AKlC4aHnPuDR1CUs3Jlk9n42xZzxKUnSb4fFF6BBof/i/yaBRmtFefY5dq7aS1KND2JjYT5llX+XXw5n424NBgDs0NsZKSowUvWBV1BQhI1zW7RCUIa+XrohaiuroHKCtcbG2pqQZxdzf+U1T4qiRXvJBq0QlCsKcZGRFMyezt/nDeBK0jUSzxznpzMplFbG03R60/mXYYutzc34FEVQWFQMNqb3uj3+Z159zP1mRYkKytBi5z+FkcffZkt87SMZgYGBxMbWntwAigpvTjbFJaW00Td8lNVsioJZ02Sagaz2Pji5gsZwgTT3Hrh0MOKUk056Bai1X9VRlMbb38Rv7JssHqvBqjSaL3cnUlHdd9TbVy1/tfavUl5goKEpva1PT0Ic8zh/9DTJ7WBg1z4EOZ/jWEbti+yMeRm19qkhjbVvcWFB9b8LC4uwdrGt3r92wWN5clxvfBzt0CqgaKwoP2XuEFYbP6Z3FeTnVm9RVNy8/mVu/TZFCCv8Rj/JkDZHWbEppsYXM/PaHxprvwrObFtGkjGZq1nFQCK7Avrygq8vhDdvoSNs/ZgwZxT2EV8RFlf1BemmxvpPTY21f+MsF39jmurfQtjRZ9ww3C9u58tLRtXzd5u/f5bpP5Ik/XpYfAEav2cZm86UIkpzyMgqrLGAqE/Ji+f4MWpNgC1TzJnNH7Kn5uG00pzqyan4/EYWL4yie49AfLwCGDxzID33LGHpgTSz0pvMXyX+zCNf8v7JNjVe0RM0fg6jdPs5nFABdZaBzf3wvu2K0sgqD8HJWwOZP5Jh3x9XH0faZV0hXX3rBjXU/kk//IdvYrz53UtP0KeXnpiThTW2aLp9m8wfc9q3cUITyNjp95Ow8l02x5cjxE/EDJvLa5P7E7M8nIJmTpLNbV+h7ca4pwagPbKa5dFZlArwHvYSk+6iW9G0pH6r6LpM4MkBZRxYto3Lxrp12rz2r0lRBHmp8bUWhgX5BVg72qERosnPqZqExp2hz83mwRvb+WjLBYpvcXHU3Pa3VPyNUevf+fjRNUgQ+fkxs/r6rXx+WaL/SJL062Hx6as0L5nU1FTSDEXN/FAspKCwDTr9zUWaXq+jpCi/coKpSr95RMbpoSk893gAihBAKSWlbdCUZpCamkpqaiqGIgXrGiEoGi0lhlhOHNrN5tXL+OyHLHy6dK4+R7DpdLX8TelV8QuhYKe7eRZYeXEeBoMBg8FAVlY2mm5jGeObyMbVR8hqxk9/jSugsAh0ervq8vV2thQV5Fsgb4A0Mg3t8Q20ITc9n/RMBY8uHSjNSKtsZ7X2M09pfgYpiYfZejibbiNG4Ft9jpt6+6pRa//q91nZYl333Lr23tzXzkBaiumWN4oiyEjOQHjch4f5IaiytdNX/9vOTkdpcbGp/tp74K5LIupQDFdTUklJSaNAtKmfgWjsu5Da+Gk5c+u3MULXjdAZvbkRtop91+pe3W1++zfUfkLo8es7lO5uNzfQ63WUFBeYv/hUHOj9zAs8oT3Ciq/CG739VoP9p4WaE79W74h9cwYGmNW/Fdoy8A8fsnTpUpYuXcqM7pY9MtnS/iNJ0q/LXXT8JJ1LcTcIHDyRBzt54h38OCMesCP2YmJ1+oW4bDoPmcxDfp54dnmECSN7Y2fMrryCO5lzF/Pp8ugkenfyoGNAP6b+4S9M6Fm1ILOmz+z3eOu5YQR5u+Lq2YUQv/YU3siixIx0tfwhlfgrhXSujN83ZCwD/BreUyuf4cwa04GTa9ZzKs8yH+KKkselS6n4PDyBvv6e+PYcy7DuRmIvWOb8LEXJJS3TFn9/yEiHjKxS/APdMGRWHZ1Qa7/mlCVIPrCTaOv+jH3EufJVtfpvmnr7Vr5P35s5/3yPd18bhWfNie9GAonZvgyZ9ijBXu54BjxE6MgQlKuJXK9VjgtDn5/P/FkDcbiFidPtgeE8HGCqv5EPtuPalSumhOxU0ou9eGh4L3w8PAl6OJTBAQ3kn5dLvt6LbsE+uLu74+5Q9SVIbfy0jPr4skLv6IyzszPOba1Ba0d7FxecnR3RWwmE0NEzdCrds38g7KzAycUFZ2dnnOyrFtnmtX+j7UcxNn5DmRE6ku5eHtX9M66yfwqhxd7FHXd3dzycbNEotjh6eODu7lh5kWQbAsa9yBTfq+zYcpIiB1N8zpVHINXLV6u/pstXi786H/v+/P7Nv/PW38biVTMutfxV+3ccO5cuYfHixdX/3xVnuYWhueNTkqTfjrvmSUiKUkHsrq8JmzKZMc/Pxa4sm8u/rGTjsTzTeYpKBZd3rWT75ImMmDMXvcjlStQ61v6Yiemcywpid37FnilTGPviX7A1ZnMlci1rf6hKLyVi63o6zRjDM3NHY1NRiOFaBOt2n0UoCgpq6Wr5l3Fm+zf89MwkJr34AMVXD/FzbDHedeZ2IXQEdG5P/LZVbL1UZNF7IV7bt5ItHaYyfM5c7IxZXNy3kq0xJRYrIyMzlw7dS8kwgDHDgJWjN+lpRVTXfxPt11xK6XnC9ify+vDR9PhlJWeKm65/1fxU2r9aRQnFpUZKi0pq3exaqYhn24qNaCc9xow/jMamIo+U8/tYsflY7QvIdAEEBbkj9seRcwv7fe18CoGTXmS8YwXpZ7aw9ogBUFAqYtj5zVFmjA/l5f6C7LhDHIkpxEdfN4OfCYvoysRn/8wQaw0lkV8xf9Vp1fHTUqr1K7wZ9ae5DHCoKiuAl94YhBA5hC97k02XHfDuaI+N+0j++MbI6nxrxm9W+zfWfko557b+l/1TJzL5lcHYlhiIP7aKbyPyKrd3YfCc+Qxzv5nX9NdDEOIc6177jGNlDgR0dsemvQdT/9qz+j0i4yBLFmwjSaV8dU2Xf7xcLf5KZQXkFZVSnJdPabPyb7p/KxRjSEmqdW5zx+Jm7WCTzB6fkiT9ZigDBw+t9zW2MC+HkhL5vfNeIvwm8OZLA6h/050LbJj/BScs8LSfe4EImsp7L3hz6L33f1PPqZb9Q7IE0ftZls7oTnl5BeKWnwWvQWuVzt6FC9mbJvudJP1ayQWoBICwssfNWd/AORml3EjJuuWLLe41biPmMT84kgVL9t3SbZjuVrJ/SJYgbDvg0b7y1iDlBWSk55p9jrhQdDi5OWA6i7mMvPQMCuQXH0n61bprfoKX7iylLI/01EZuHiMXF2YRog2dvF1IjDr5m1p8guwfkmUoxdmkptZ8oRn3Sq18FnyNVywWlyRJrU8eAZUkSZIkSZJalUYevJAkSZIkSZJak4Y2be90DJIkSZIkSdI9RIO1Ayh30e1AJUmSJEmSpN80jWJlg2Jlc6fjkCRJkiRJku4RGkVrDVpb9XdKkiRJkiRJkgVoTP+Td2OSJEmSJEmSWocGRQGN9k7HIUmSJEmSJN0jNIh6twGVJEmSJEmSpNtGAxVQUX6n45AkSZIkSZLuERpRXgoVZXc6DkmSJEmSJOkeoaG8FMqL73QckiRJkiRJ0j1CIyqMiDL53HdJkiRJkiSpdWgwFoCouNNxSJIkSZIkSfcIDcb8Ox2DJEmSJEmSdA/RyLswSZIkSZIkSa3p/wM1DtITGgeKsAAAAABJRU5ErkJggg=="}}},{"cell_type":"markdown","source":"Like I said before, I'm a newbie in deeplearning. I'd like to share with you😃 what I found. So if you like that, pls upvote🎉🎉🎉","metadata":{}},{"cell_type":"markdown","source":"## Save data to Sqlite\n\nWe can add features before we save them, or we also can merge seval \"batch files\" to a huge one, we don't need to worry about memory limits, the only cost is longer time to save data to sqldatabase, but it's still worthy considering we save huge time in training💪","metadata":{}},{"cell_type":"code","source":"import os\nimport sys\n\nimport sqlalchemy\nfrom typing import Any, Dict, List, Optional\nimport sqlite3\nimport numpy as np\nfrom tqdm import tqdm\nimport pandas as pd\n\njoin = os.path.join\ndirname = os.path.dirname\nabspath = os.path.abspath","metadata":{"execution":{"iopub.status.busy":"2023-04-08T07:58:47.352232Z","iopub.execute_input":"2023-04-08T07:58:47.352661Z","iopub.status.idle":"2023-04-08T07:58:47.364742Z","shell.execute_reply.started":"2023-04-08T07:58:47.352620Z","shell.execute_reply":"2023-04-08T07:58:47.363095Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# no need to modify\ndef create_table(\n    columns: List[str],\n    table_name: str,\n    database_path: str,\n    *,\n    index_column: str = \"event_no\",\n    default_type: str = \"NOT NULL\",\n    integer_primary_key: bool = True,\n) -> None:\n\n    # Prepare column names and types\n    query_columns = []\n    for column in columns:\n        type_ = default_type\n        if column == index_column:\n            if integer_primary_key:\n                type_ = \"INTEGER PRIMARY KEY NOT NULL\"\n            else:\n                type_ = \"NOT NULL\"\n\n        query_columns.append(f\"{column} {type_}\")\n    query_columns_string = \", \".join(query_columns)\n\n    def run_sql_code(database_path: str, code: str) -> None:\n        \"\"\"Execute SQLite code.\n        Args:\n            database_path: Path to databases\n            code: SQLite code\n        \"\"\"\n        conn = sqlite3.connect(database_path)\n        c = conn.cursor()\n        c.executescript(code)\n        c.close()\n\n    def attach_index(\n            database_path: str, table_name: str, index_column: str = \"event_no\"\n    ) -> None:\n        \"\"\"Attach the table (i.e., event) index.\n        Important for query times!\n        \"\"\"\n        code = (\n            \"PRAGMA foreign_keys=off;\\n\"\n            \"BEGIN TRANSACTION;\\n\"\n            f\"CREATE INDEX {index_column}_{table_name} \"\n            f\"ON {table_name} ({index_column});\\n\"\n            \"COMMIT TRANSACTION;\\n\"\n            \"PRAGMA foreign_keys=on;\"\n        )\n        run_sql_code(database_path, code)\n\n    # Run SQL code\n    code = (\n        \"PRAGMA foreign_keys=off;\\n\"\n        f\"CREATE TABLE {table_name} ({query_columns_string});\\n\"\n        \"PRAGMA foreign_keys=on;\"\n    )\n    run_sql_code(\n        database_path,\n        code,\n    )\n\n    # Attaching index to all non-truth-like tables (e.g., pulse maps).\n    if not integer_primary_key:\n        attach_index(database_path, table_name, index_column=index_column)\n\ndef add_to_table(database_path: str,\n                      df: pd.DataFrame,\n                      table_name:  str,\n                      is_primary_key: bool,\n                      index_column=None,\n                      chunksize=200000\n                      ) -> None:\n    \n    try:\n\n        create_table(columns=df.columns,\n                     database_path=database_path,\n                     table_name=table_name,\n                     integer_primary_key=is_primary_key,\n                     index_column=index_column)\n\n    except sqlite3.OperationalError as e:\n        if 'already exists' in str(e):\n            pass\n        else:\n            raise e\n    engine = sqlalchemy.create_engine(\"sqlite:///\" + database_path)\n    df.to_sql(table_name, con=engine, index=False, if_exists=\"append\", chunksize=chunksize)\n    engine.dispose()\n    return","metadata":{"execution":{"iopub.status.busy":"2023-04-08T07:58:47.366453Z","iopub.execute_input":"2023-04-08T07:58:47.366840Z","iopub.status.idle":"2023-04-08T07:58:47.381199Z","shell.execute_reply.started":"2023-04-08T07:58:47.366799Z","shell.execute_reply":"2023-04-08T07:58:47.379483Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def convert_to_sqlite(pulses_batch_df,\n                      meta_data_batch,\n                      database_path: str,\n                      batch_size: int = 200000) -> None:\n\n    if not database_path.endswith('.db'):\n        raise ValueError(\"not contain '.db'!!!\")\n    \n    add_to_table(database_path=database_path,\n                df=meta_data_batch,\n                table_name='meta_table',\n                is_primary_key=True, index_column='event_id')\n    \n    \n    add_to_table(database_path=database_path,\n                df=pulses_batch_df,\n                table_name='pulse_table',\n                is_primary_key=False, index_column='event_id')\n    print(f'Conversion Complete!. Database available at\\n {database_path}')\n","metadata":{"execution":{"iopub.status.busy":"2023-04-08T07:58:47.383495Z","iopub.execute_input":"2023-04-08T07:58:47.383913Z","iopub.status.idle":"2023-04-08T07:58:47.395851Z","shell.execute_reply.started":"2023-04-08T07:58:47.383872Z","shell.execute_reply":"2023-04-08T07:58:47.394859Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"BATH_PATH = '../input/icecube-neutrinos-in-deep-ice/'\n\nOUTPUT_PATH = '/kaggle/working'\n\nBATCHDATA_PATH = join(BATH_PATH, 'train')\n\nBATCH_ID = 1","metadata":{"execution":{"iopub.status.busy":"2023-04-08T07:58:47.397491Z","iopub.execute_input":"2023-04-08T07:58:47.398256Z","iopub.status.idle":"2023-04-08T07:58:47.405970Z","shell.execute_reply.started":"2023-04-08T07:58:47.398214Z","shell.execute_reply":"2023-04-08T07:58:47.405221Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"meta_data = pd.read_parquet(join(BATH_PATH, 'train_meta.parquet'))\n\nmeta_data_1 = meta_data.loc[meta_data['batch_id'] == BATCH_ID]","metadata":{"execution":{"iopub.status.busy":"2023-04-08T07:58:47.408792Z","iopub.execute_input":"2023-04-08T07:58:47.409115Z","iopub.status.idle":"2023-04-08T07:59:28.719016Z","shell.execute_reply.started":"2023-04-08T07:58:47.409074Z","shell.execute_reply":"2023-04-08T07:59:28.717652Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pulses_batch_df = pd.read_parquet(join(BATCHDATA_PATH, 'batch_1.parquet')).reset_index()","metadata":{"execution":{"iopub.status.busy":"2023-04-08T07:59:28.721974Z","iopub.execute_input":"2023-04-08T07:59:28.722296Z","iopub.status.idle":"2023-04-08T07:59:31.219868Z","shell.execute_reply.started":"2023-04-08T07:59:28.722266Z","shell.execute_reply":"2023-04-08T07:59:31.217684Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pulses_batch_df['auxiliary'] = pulses_batch_df['auxiliary'].map({True: 1, False: 0})","metadata":{"execution":{"iopub.status.busy":"2023-04-08T07:59:31.223835Z","iopub.execute_input":"2023-04-08T07:59:31.224233Z","iopub.status.idle":"2023-04-08T07:59:34.131575Z","shell.execute_reply.started":"2023-04-08T07:59:31.224198Z","shell.execute_reply":"2023-04-08T07:59:34.130455Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"database_save_path = join(OUTPUT_PATH, f'batch_{BATCH_ID}.db')\n\nconvert_to_sqlite(pulses_batch_df, meta_data_1, database_save_path)","metadata":{"execution":{"iopub.status.busy":"2023-04-08T07:59:34.132715Z","iopub.execute_input":"2023-04-08T07:59:34.133048Z","iopub.status.idle":"2023-04-08T08:05:38.807341Z","shell.execute_reply.started":"2023-04-08T07:59:34.133019Z","shell.execute_reply":"2023-04-08T08:05:38.805312Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## load in pytorch dataset","metadata":{}},{"cell_type":"code","source":"!pip install torch_geometric","metadata":{"execution":{"iopub.status.busy":"2023-04-08T08:05:38.809063Z","iopub.execute_input":"2023-04-08T08:05:38.809423Z","iopub.status.idle":"2023-04-08T08:06:03.591617Z","shell.execute_reply.started":"2023-04-08T08:05:38.809391Z","shell.execute_reply":"2023-04-08T08:06:03.590029Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from torch_geometric.data import Data, Dataset\nfrom torch_geometric.loader import DataLoader\n\nfrom torch_geometric.nn import knn_graph\n\nimport torch","metadata":{"execution":{"iopub.status.busy":"2023-04-08T08:06:03.593829Z","iopub.execute_input":"2023-04-08T08:06:03.594236Z","iopub.status.idle":"2023-04-08T08:06:06.431088Z","shell.execute_reply.started":"2023-04-08T08:06:03.594190Z","shell.execute_reply":"2023-04-08T08:06:06.429914Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import time\nfrom contextlib import contextmanager\n\n@contextmanager\ndef simple_timer(message):\n    print('Now processing→ ', message)\n    start_time = time.time()\n    yield\n    elapsed_time = time.time() - start_time\n    print(\"Processed→ {}: {:.3f} [s]\".format(message, elapsed_time))","metadata":{"execution":{"iopub.status.busy":"2023-04-08T08:06:06.432580Z","iopub.execute_input":"2023-04-08T08:06:06.433841Z","iopub.status.idle":"2023-04-08T08:06:06.442008Z","shell.execute_reply.started":"2023-04-08T08:06:06.433807Z","shell.execute_reply":"2023-04-08T08:06:06.439472Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\nclass IceCubeSubmissionDataset(Dataset):\n    def __init__(\n            self,\n            input_path_batch,\n            col_y: List[str],\n            mode=\"test\",\n            pulse_select_list: List[int] = None,\n            transform=None,\n            pre_transform=None,\n            pre_filter=None,\n            \n    ):\n        super().__init__(transform, pre_transform, pre_filter)\n        self.mode = mode\n        \n        self._conn = None\n        \n        self._path = input_path_batch\n        \n        if pulse_select_list is None:\n    \n            with sqlite3.connect(self._path) as con:\n                query = 'select event_id from meta_table'\n\n                self.event_ids = con.execute(query).fetchall()\n                self.event_ids = list(map(lambda x: x[0], self.event_ids))\n                \n        else:\n            self.event_ids = pulse_select_list\n\n        self.col_y = col_y\n        \n    def len(self):\n        return len(self.event_ids)\n    \n    def get(self, idx):\n        event_id = self.event_ids[idx]\n        \n        event = torch.tensor(self._query(event_id, 'pulse_table'), dtype=torch.float32)\n\n        x = event[:, [2, 3, 4]]  # just a demo, use your features\n        data = Data(x=x, n_pulses=torch.tensor(x.shape[0], dtype=torch.int32))\n        if self.mode == 'train':\n            y = self._query(event_id, 'meta_table')[0][-2:]\n            y = torch.tensor(y, dtype=torch.float32)\n            data.y = y\n\n#         data.edge_index = knn_graph(\n#             data.x[:, [0, 1, 2]],  # x, y, z\n#             k=8,\n#             batch=None,\n#             loop=False\n#         )\n\n        return data\n\n    def _establish_connection(self):\n        \"\"\"Make sure that a sqlite3 connection is open.\"\"\"\n    \n        assert isinstance(self._path, str)\n        if self._conn is None:\n            self._conn = sqlite3.connect(self._path)\n    \n        return self\n\n    def _query(self, event_id, table):\n\n        assert event_id is not None\n        self._establish_connection()\n        try:\n            assert self._conn\n            result = self._conn.execute(\n                f\"SELECT * FROM {table} WHERE \"\n                f\"event_id=={event_id}\"\n            ).fetchall()\n\n        except sqlite3.OperationalError as e:\n            raise e\n        return result\n    \n    \n    def _query_eventid(self):\n    \n        table = \"meta_table\"\n        self._establish_connection()\n        \n        try:\n            assert self._conn\n            result = self._conn.execute(\n                f\"SELECT event_id FROM {table}\"\n            ).fetchall()\n\n        except sqlite3.OperationalError as e:\n            raise e\n        return result\n    ","metadata":{"execution":{"iopub.status.busy":"2023-04-08T08:06:06.443917Z","iopub.execute_input":"2023-04-08T08:06:06.444266Z","iopub.status.idle":"2023-04-08T08:06:06.462092Z","shell.execute_reply.started":"2023-04-08T08:06:06.444236Z","shell.execute_reply":"2023-04-08T08:06:06.460414Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"DB_BASE = OUTPUT_PATH\n\nbatch1_path = join(DB_BASE, 'batch_1.db')","metadata":{"execution":{"iopub.status.busy":"2023-04-08T08:06:06.463421Z","iopub.execute_input":"2023-04-08T08:06:06.464128Z","iopub.status.idle":"2023-04-08T08:06:06.475989Z","shell.execute_reply.started":"2023-04-08T08:06:06.464095Z","shell.execute_reply":"2023-04-08T08:06:06.475137Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"daset = IceCubeSubmissionDataset(input_path_batch=batch1_path\n                                  , mode='train'\n                                  , col_y=['azimuth', 'zenith'])","metadata":{"execution":{"iopub.status.busy":"2023-04-08T08:06:06.480141Z","iopub.execute_input":"2023-04-08T08:06:06.480460Z","iopub.status.idle":"2023-04-08T08:06:06.794478Z","shell.execute_reply.started":"2023-04-08T08:06:06.480432Z","shell.execute_reply":"2023-04-08T08:06:06.793120Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"num_workers = 0\nbatch_size_tk = 512\n\nwith simple_timer(f'num_workers:{num_workers}; batch_size:{batch_size_tk}'):\n    batch_size_tk = 512\n\n    dataloader_my = DataLoader(daset\n                               , batch_size=batch_size_tk\n                               , shuffle=True\n                               , num_workers=num_workers)\n\n    for tk in tqdm(dataloader_my):\n        pass","metadata":{"execution":{"iopub.status.busy":"2023-04-08T08:06:06.795749Z","iopub.execute_input":"2023-04-08T08:06:06.796137Z","iopub.status.idle":"2023-04-08T08:07:23.876688Z","shell.execute_reply.started":"2023-04-08T08:06:06.796099Z","shell.execute_reply":"2023-04-08T08:07:23.875453Z"},"trusted":true},"execution_count":null,"outputs":[]}]}