01-time-series-analysis/time-series-analysis.ipynb

{"cells":[{"cell_type":"markdown","metadata":{"id":"lRXdUlo8PJ-L"},"source":["# Time Series Analysis with Polars\n","\n","This notebook accompanies my blog post available here:  \n","https://dev.to/gaborschulz/time-series-analysis-with-polars-3dfg\n","\n","The purpose is to demonstrate how you can use Polars to clean, prepare and analyze a temporal dataset. This is fairly easy with `pandas` as there are many time-related functions, and I'm curious to see if it would be similar in `polars`."]},{"cell_type":"markdown","metadata":{"id":"3XYW7yp4Pi_d"},"source":["## Setup\n","\n","Let's start by installing polars and configuring some of the variables required for reading the dataset."]},{"cell_type":"code","execution_count":9,"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"elapsed":8885,"status":"ok","timestamp":1702213231639,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"AHjMnEYBPI4k","outputId":"e2d8a4fe-8948-45d1-ac31-b53408ae2efa"},"outputs":[{"name":"stdout","output_type":"stream","text":["Requirement already satisfied: polars in /usr/local/lib/python3.10/dist-packages (0.19.19)\n"]}],"source":["!pip install --upgrade polars"]},{"cell_type":"code","execution_count":10,"metadata":{"executionInfo":{"elapsed":8,"status":"ok","timestamp":1702213231639,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"c9G4dpgPP-JV"},"outputs":[],"source":["from pathlib import Path\n","\n","import polars as pl"]},{"cell_type":"code","execution_count":11,"metadata":{"executionInfo":{"elapsed":6,"status":"ok","timestamp":1702213231639,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"3lCHLN2ePfFu"},"outputs":[],"source":["# Some folder and file paths that we'll need later\n","BASE_PATH = Path('/content') / 'drive' / 'MyDrive' / 'time-series-polars' / 'data' # The root directory of your data files, change as needed\n","SOURCE_FILE =  BASE_PATH / 'nrg_105m_tabular.tsv'\n","INDICATORS_FILE = BASE_PATH / 'indic_nrg.csv'\n","\n","# The number of lines to show when viewing a dataframe\n","N = 15"]},{"cell_type":"code","execution_count":12,"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"elapsed":6,"status":"ok","timestamp":1702213231639,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"LT3l3QTBw6Mj","outputId":"fa30ec4b-b26b-4ab0-d6fb-f3e2009e4c6d"},"outputs":[{"data":{"text/plain":["polars.config.Config"]},"execution_count":12,"metadata":{},"output_type":"execute_result"}],"source":["# Set the number of characters to display when showing a string in a dataframe\n","pl.Config.set_fmt_str_lengths(50)"]},{"cell_type":"markdown","metadata":{"id":"QhLsQUEMQQJ_"},"source":["## Loading the data\n","\n","The dataset is a TSV file, so we'll have to make sure that Polars knows that it should split columns at Tabs, not at commas."]},{"cell_type":"code","execution_count":13,"metadata":{"executionInfo":{"elapsed":5,"status":"ok","timestamp":1702213231639,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"c004IhwKQMNu"},"outputs":[],"source":["dataframe = pl.read_csv(SOURCE_FILE, separator='\\t')"]},{"cell_type":"code","execution_count":14,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":701},"executionInfo":{"elapsed":5,"status":"ok","timestamp":1702213231639,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"2ZvwdyWEQgZg","outputId":"c7e77c65-7d65-45ee-9778-52d7a8e146e5"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (15, 145)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>freq,unit,product,indic_nrg,geo\\TIME_PERIOD</th><th>2008-01 </th><th>2008-02 </th><th>2008-03 </th><th>2008-04 </th><th>2008-05 </th><th>2008-06 </th><th>2008-07 </th><th>2008-08 </th><th>2008-09 </th><th>2008-10 </th><th>2008-11 </th><th>2008-12 </th><th>2009-01 </th><th>2009-02 </th><th>2009-03 </th><th>2009-04 </th><th>2009-05 </th><th>2009-06 </th><th>2009-07 </th><th>2009-08 </th><th>2009-09 </th><th>2009-10 </th><th>2009-11 </th><th>2009-12 </th><th>2010-01 </th><th>2010-02 </th><th>2010-03 </th><th>2010-04 </th><th>2010-05 </th><th>2010-06 </th><th>2010-07 </th><th>2010-08 </th><th>2010-09 </th><th>2010-10 </th><th>2010-11 </th><th>2010-12 </th><th>&hellip;</th><th>2016-12 </th><th>2017-01 </th><th>2017-02 </th><th>2017-03 </th><th>2017-04 </th><th>2017-05 </th><th>2017-06 </th><th>2017-07 </th><th>2017-08 </th><th>2017-09 </th><th>2017-10 </th><th>2017-11 </th><th>2017-12 </th><th>2018-01 </th><th>2018-02 </th><th>2018-03 </th><th>2018-04 </th><th>2018-05 </th><th>2018-06 </th><th>2018-07 </th><th>2018-08 </th><th>2018-09 </th><th>2018-10 </th><th>2018-11 </th><th>2018-12 </th><th>2019-01 </th><th>2019-02 </th><th>2019-03 </th><th>2019-04 </th><th>2019-05 </th><th>2019-06 </th><th>2019-07 </th><th>2019-08 </th><th>2019-09 </th><th>2019-10 </th><th>2019-11 </th><th>2019-12 </th></tr><tr><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>&hellip;</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td></tr></thead><tbody><tr><td>&quot;M,GWH,6000,15_107000,AT&quot;</td><td>&quot;5529 &quot;</td><td>&quot;5098 &quot;</td><td>&quot;5561 &quot;</td><td>&quot;5320 &quot;</td><td>&quot;5677 &quot;</td><td>&quot;5822 &quot;</td><td>&quot;6230 &quot;</td><td>&quot;5993 &quot;</td><td>&quot;5366 &quot;</td><td>&quot;5411 &quot;</td><td>&quot;5131 &quot;</td><td>&quot;5593 &quot;</td><td>&quot;5346 &quot;</td><td>&quot;4933 &quot;</td><td>&quot;5736 &quot;</td><td>&quot;5959 &quot;</td><td>&quot;6463 &quot;</td><td>&quot;5933 &quot;</td><td>&quot;6139 &quot;</td><td>&quot;5606 &quot;</td><td>&quot;5619 &quot;</td><td>&quot;6175 &quot;</td><td>&quot;5178 &quot;</td><td>&quot;5764 &quot;</td><td>&quot;6036 &quot;</td><td>&quot;5434 &quot;</td><td>&quot;5724 &quot;</td><td>&quot;4630 &quot;</td><td>&quot;5914 &quot;</td><td>&quot;6461 &quot;</td><td>&quot;6101 &quot;</td><td>&quot;6124 &quot;</td><td>&quot;6042 &quot;</td><td>&quot;5913 &quot;</td><td>&quot;5876 &quot;</td><td>&quot;6572 &quot;</td><td>&hellip;</td><td>&quot;6018 &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,BE&quot;</td><td>&quot;7711 &quot;</td><td>&quot;7126 &quot;</td><td>&quot;6718 &quot;</td><td>&quot;6334 &quot;</td><td>&quot;6487 &quot;</td><td>&quot;6707 &quot;</td><td>&quot;7052 &quot;</td><td>&quot;6810 &quot;</td><td>&quot;6745 &quot;</td><td>&quot;7504 &quot;</td><td>&quot;7185 &quot;</td><td>&quot;7765 &quot;</td><td>&quot;8043 &quot;</td><td>&quot;7169 &quot;</td><td>&quot;7544 &quot;</td><td>&quot;6815 &quot;</td><td>&quot;7164 &quot;</td><td>&quot;7093 &quot;</td><td>&quot;7509 &quot;</td><td>&quot;7841 &quot;</td><td>&quot;7911 &quot;</td><td>&quot;7892 &quot;</td><td>&quot;7466 &quot;</td><td>&quot;8336 &quot;</td><td>&quot;8562 &quot;</td><td>&quot;7948 &quot;</td><td>&quot;8321 &quot;</td><td>&quot;7543 &quot;</td><td>&quot;7837 &quot;</td><td>&quot;7246 &quot;</td><td>&quot;7435 &quot;</td><td>&quot;7702 &quot;</td><td>&quot;7351 &quot;</td><td>&quot;7655 &quot;</td><td>&quot;7772 &quot;</td><td>&quot;7761 &quot;</td><td>&hellip;</td><td>&quot;7685 &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,BG&quot;</td><td>&quot;4435 &quot;</td><td>&quot;3678 &quot;</td><td>&quot;3780 &quot;</td><td>&quot;3559 &quot;</td><td>&quot;3381 &quot;</td><td>&quot;3268 &quot;</td><td>&quot;3508 &quot;</td><td>&quot;3483 &quot;</td><td>&quot;3336 &quot;</td><td>&quot;3608 &quot;</td><td>&quot;4122 &quot;</td><td>&quot;4265 &quot;</td><td>&quot;4519 &quot;</td><td>&quot;3892 &quot;</td><td>&quot;3891 &quot;</td><td>&quot;2973 &quot;</td><td>&quot;2893 &quot;</td><td>&quot;3082 &quot;</td><td>&quot;3449 &quot;</td><td>&quot;3221 &quot;</td><td>&quot;3211 &quot;</td><td>&quot;3523 &quot;</td><td>&quot;3993 &quot;</td><td>&quot;4042 &quot;</td><td>&quot;4301 &quot;</td><td>&quot;4036 &quot;</td><td>&quot;3981 &quot;</td><td>&quot;3189 &quot;</td><td>&quot;2999 &quot;</td><td>&quot;3576 &quot;</td><td>&quot;3976 &quot;</td><td>&quot;3992 &quot;</td><td>&quot;3494 &quot;</td><td>&quot;3889 &quot;</td><td>&quot;3918 &quot;</td><td>&quot;4649 &quot;</td><td>&hellip;</td><td>&quot;4551 &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,CY&quot;</td><td>&quot;461 &quot;</td><td>&quot;415 &quot;</td><td>&quot;340 &quot;</td><td>&quot;323 &quot;</td><td>&quot;358 &quot;</td><td>&quot;443 &quot;</td><td>&quot;549 &quot;</td><td>&quot;539 &quot;</td><td>&quot;456 &quot;</td><td>&quot;372 &quot;</td><td>&quot;334 &quot;</td><td>&quot;401 &quot;</td><td>&quot;432 &quot;</td><td>&quot;382 &quot;</td><td>&quot;390 &quot;</td><td>&quot;321 &quot;</td><td>&quot;368 &quot;</td><td>&quot;470 &quot;</td><td>&quot;571 &quot;</td><td>&quot;546 &quot;</td><td>&quot;454 &quot;</td><td>&quot;412 &quot;</td><td>&quot;362 &quot;</td><td>&quot;424 &quot;</td><td>&quot;438 &quot;</td><td>&quot;393 &quot;</td><td>&quot;380 &quot;</td><td>&quot;342 &quot;</td><td>&quot;384 &quot;</td><td>&quot;448 &quot;</td><td>&quot;549 &quot;</td><td>&quot;617 &quot;</td><td>&quot;501 &quot;</td><td>&quot;402 &quot;</td><td>&quot;362 &quot;</td><td>&quot;391 &quot;</td><td>&hellip;</td><td>&quot;430 &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,CZ&quot;</td><td>&quot;8497 &quot;</td><td>&quot;7767 &quot;</td><td>&quot;7829 &quot;</td><td>&quot;7390 &quot;</td><td>&quot;6406 &quot;</td><td>&quot;6176 &quot;</td><td>&quot;6351 &quot;</td><td>&quot;6150 &quot;</td><td>&quot;6129 &quot;</td><td>&quot;6678 &quot;</td><td>&quot;6796 &quot;</td><td>&quot;7354 &quot;</td><td>&quot;7935 &quot;</td><td>&quot;7414 &quot;</td><td>&quot;7825 &quot;</td><td>&quot;6548 &quot;</td><td>&quot;5871 &quot;</td><td>&quot;6058 &quot;</td><td>&quot;6235 &quot;</td><td>&quot;5929 &quot;</td><td>&quot;6109 &quot;</td><td>&quot;7284 &quot;</td><td>&quot;7191 &quot;</td><td>&quot;7841 &quot;</td><td>&quot;8481 &quot;</td><td>&quot;7578 &quot;</td><td>&quot;8056 &quot;</td><td>&quot;7065 &quot;</td><td>&quot;6206 &quot;</td><td>&quot;6098 &quot;</td><td>&quot;6881 &quot;</td><td>&quot;6162 &quot;</td><td>&quot;6736 &quot;</td><td>&quot;7543 &quot;</td><td>&quot;7235 &quot;</td><td>&quot;7846 &quot;</td><td>&hellip;</td><td>&quot;7665 &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,DE&quot;</td><td>&quot;52597 &quot;</td><td>&quot;50957 &quot;</td><td>&quot;50607 &quot;</td><td>&quot;50457 &quot;</td><td>&quot;45959 &quot;</td><td>&quot;43449 &quot;</td><td>&quot;44251 &quot;</td><td>&quot;43939 &quot;</td><td>&quot;46286 &quot;</td><td>&quot;50136 &quot;</td><td>&quot;48871 &quot;</td><td>&quot;49869 &quot;</td><td>&quot;52248 &quot;</td><td>&quot;46269 &quot;</td><td>&quot;46477 &quot;</td><td>&quot;40951 &quot;</td><td>&quot;39071 &quot;</td><td>&quot;40564 &quot;</td><td>&quot;41361 &quot;</td><td>&quot;40833 &quot;</td><td>&quot;42592 &quot;</td><td>&quot;46213 &quot;</td><td>&quot;46702 &quot;</td><td>&quot;49959 &quot;</td><td>&quot;48775 &quot;</td><td>&quot;43547 &quot;</td><td>&quot;44394 &quot;</td><td>&quot;39984 &quot;</td><td>&quot;39295 &quot;</td><td>&quot;35987 &quot;</td><td>&quot;38838 &quot;</td><td>&quot;36875 &quot;</td><td>&quot;38524 &quot;</td><td>&quot;42027 &quot;</td><td>&quot;44078 &quot;</td><td>&quot;49007 &quot;</td><td>&hellip;</td><td>&quot;49237 &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,DK&quot;</td><td>&quot;4027 &quot;</td><td>&quot;3494 &quot;</td><td>&quot;3225 &quot;</td><td>&quot;2601 &quot;</td><td>&quot;2337 &quot;</td><td>&quot;2499 &quot;</td><td>&quot;1989 &quot;</td><td>&quot;2039 &quot;</td><td>&quot;2573 &quot;</td><td>&quot;3537 &quot;</td><td>&quot;4118 &quot;</td><td>&quot;3973 &quot;</td><td>&quot;4121 &quot;</td><td>&quot;3723 &quot;</td><td>&quot;3737 &quot;</td><td>&quot;2560 &quot;</td><td>&quot;2303 &quot;</td><td>&quot;2385 &quot;</td><td>&quot;2159 &quot;</td><td>&quot;2157 &quot;</td><td>&quot;2553 &quot;</td><td>&quot;3265 &quot;</td><td>&quot;3457 &quot;</td><td>&quot;3785 &quot;</td><td>&quot;4245 &quot;</td><td>&quot;3964 &quot;</td><td>&quot;4001 &quot;</td><td>&quot;3189 &quot;</td><td>&quot;2696 &quot;</td><td>&quot;1999 &quot;</td><td>&quot;1993 &quot;</td><td>&quot;2104 &quot;</td><td>&quot;2801 &quot;</td><td>&quot;3484 &quot;</td><td>&quot;3725 &quot;</td><td>&quot;4364 &quot;</td><td>&hellip;</td><td>&quot;3332 &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EA&quot;</td><td>&quot;212276 &quot;</td><td>&quot;198528 &quot;</td><td>&quot;197445 &quot;</td><td>&quot;187055 &quot;</td><td>&quot;177805 &quot;</td><td>&quot;175920 &quot;</td><td>&quot;183886 &quot;</td><td>&quot;175958 &quot;</td><td>&quot;181217 &quot;</td><td>&quot;190227 &quot;</td><td>&quot;193189 &quot;</td><td>&quot;203960 &quot;</td><td>&quot;212439 &quot;</td><td>&quot;187857 &quot;</td><td>&quot;188375 &quot;</td><td>&quot;166717 &quot;</td><td>&quot;166606 &quot;</td><td>&quot;165414 &quot;</td><td>&quot;174664 &quot;</td><td>&quot;167269 &quot;</td><td>&quot;170188 &quot;</td><td>&quot;181378 &quot;</td><td>&quot;183703 &quot;</td><td>&quot;203205 &quot;</td><td>&quot;211129 &quot;</td><td>&quot;189137 &quot;</td><td>&quot;195649 &quot;</td><td>&quot;168221 &quot;</td><td>&quot;168439 &quot;</td><td>&quot;164873 &quot;</td><td>&quot;176777 &quot;</td><td>&quot;166473 &quot;</td><td>&quot;168905 &quot;</td><td>&quot;179352 &quot;</td><td>&quot;190686 &quot;</td><td>&quot;213353 &quot;</td><td>&hellip;</td><td>&quot;202762 &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EE&quot;</td><td>&quot;1125 &quot;</td><td>&quot;996 &quot;</td><td>&quot;765 &quot;</td><td>&quot;749 &quot;</td><td>&quot;675 &quot;</td><td>&quot;599 &quot;</td><td>&quot;658 &quot;</td><td>&quot;970 &quot;</td><td>&quot;1042 &quot;</td><td>&quot;1011 &quot;</td><td>&quot;956 &quot;</td><td>&quot;981 &quot;</td><td>&quot;1036 &quot;</td><td>&quot;948 &quot;</td><td>&quot;900 &quot;</td><td>&quot;602 &quot;</td><td>&quot;577 &quot;</td><td>&quot;724 &quot;</td><td>&quot;554 &quot;</td><td>&quot;594 &quot;</td><td>&quot;671 &quot;</td><td>&quot;673 &quot;</td><td>&quot;649 &quot;</td><td>&quot;808 &quot;</td><td>&quot;1233 &quot;</td><td>&quot;1132 &quot;</td><td>&quot;1183 &quot;</td><td>&quot;854 &quot;</td><td>&quot;948 &quot;</td><td>&quot;945 &quot;</td><td>&quot;959 &quot;</td><td>&quot;907 &quot;</td><td>&quot;943 &quot;</td><td>&quot;1109 &quot;</td><td>&quot;1160 &quot;</td><td>&quot;1375 &quot;</td><td>&hellip;</td><td>&quot;1096 &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EL&quot;</td><td>&quot;5119 &quot;</td><td>&quot;4477 &quot;</td><td>&quot;3809 &quot;</td><td>&quot;4128 &quot;</td><td>&quot;4504 &quot;</td><td>&quot;4994 &quot;</td><td>&quot;5876 &quot;</td><td>&quot;5418 &quot;</td><td>&quot;4714 &quot;</td><td>&quot;4428 &quot;</td><td>&quot;4341 &quot;</td><td>&quot;4813 &quot;</td><td>&quot;4898 &quot;</td><td>&quot;4385 &quot;</td><td>&quot;4371 &quot;</td><td>&quot;3998 &quot;</td><td>&quot;4275 &quot;</td><td>&quot;4571 &quot;</td><td>&quot;5408 &quot;</td><td>&quot;5108 &quot;</td><td>&quot;4513 &quot;</td><td>&quot;4559 &quot;</td><td>&quot;4400 &quot;</td><td>&quot;4784 &quot;</td><td>&quot;4920 &quot;</td><td>&quot;4144 &quot;</td><td>&quot;4203 &quot;</td><td>&quot;3654 &quot;</td><td>&quot;4034 &quot;</td><td>&quot;4284 &quot;</td><td>&quot;5363 &quot;</td><td>&quot;5540 &quot;</td><td>&quot;4299 &quot;</td><td>&quot;4375 &quot;</td><td>&quot;4154 &quot;</td><td>&quot;4800 &quot;</td><td>&hellip;</td><td>&quot;5237 &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,ES&quot;</td><td>&quot;26512 &quot;</td><td>&quot;25126 &quot;</td><td>&quot;24212 &quot;</td><td>&quot;23636 &quot;</td><td>&quot;23332 &quot;</td><td>&quot;23263 &quot;</td><td>&quot;25574 &quot;</td><td>&quot;24176 &quot;</td><td>&quot;23817 &quot;</td><td>&quot;23557 &quot;</td><td>&quot;24227 &quot;</td><td>&quot;25635 &quot;</td><td>&quot;26366 &quot;</td><td>&quot;22626 &quot;</td><td>&quot;22378 &quot;</td><td>&quot;21086 &quot;</td><td>&quot;21438 &quot;</td><td>&quot;22306 &quot;</td><td>&quot;24734 &quot;</td><td>&quot;23569 &quot;</td><td>&quot;22838 &quot;</td><td>&quot;22490 &quot;</td><td>&quot;22272 &quot;</td><td>&quot;24296 &quot;</td><td>&quot;25466 &quot;</td><td>&quot;23578 &quot;</td><td>&quot;24727 &quot;</td><td>&quot;21257 &quot;</td><td>&quot;21886 &quot;</td><td>&quot;22138 &quot;</td><td>&quot;25306 &quot;</td><td>&quot;23398 &quot;</td><td>&quot;23051 &quot;</td><td>&quot;23143 &quot;</td><td>&quot;23451 &quot;</td><td>&quot;24824 &quot;</td><td>&hellip;</td><td>&quot;22687 &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EU27_2007&quot;</td><td>&quot;309105 &quot;</td><td>&quot;287339 &quot;</td><td>&quot;287960 &quot;</td><td>&quot;269936 &quot;</td><td>&quot;253688 &quot;</td><td>&quot;247664 &quot;</td><td>&quot;256970 &quot;</td><td>&quot;247177 &quot;</td><td>&quot;255012 &quot;</td><td>&quot;273018 &quot;</td><td>&quot;277968 &quot;</td><td>&quot;293885 &quot;</td><td>&quot;307265 &quot;</td><td>&quot;273095 &quot;</td><td>&quot;275609 &quot;</td><td>&quot;239036 &quot;</td><td>&quot;236535 &quot;</td><td>&quot;232986 &quot;</td><td>&quot;244810 &quot;</td><td>&quot;236451 &quot;</td><td>&quot;240472 &quot;</td><td>&quot;261900 &quot;</td><td>&quot;265990 &quot;</td><td>&quot;292959 &quot;</td><td>&quot;308762 &quot;</td><td>&quot;275703 &quot;</td><td>&quot;284384 &quot;</td><td>&quot;244536 &quot;</td><td>&quot;243814 &quot;</td><td>&quot;236253 &quot;</td><td>&quot;249425 &quot;</td><td>&quot;238042 &quot;</td><td>&quot;241642 &quot;</td><td>&quot;261619 &quot;</td><td>&quot;277408 &quot;</td><td>&quot;310240 &quot;</td><td>&hellip;</td><td>&quot;290223 &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EU27_2020&quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&hellip;</td><td>&quot;259266 &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EU28&quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&hellip;</td><td>&quot;291383 &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,FI&quot;</td><td>&quot;7471 &quot;</td><td>&quot;6729 &quot;</td><td>&quot;6947 &quot;</td><td>&quot;6282 &quot;</td><td>&quot;5570 &quot;</td><td>&quot;5432 &quot;</td><td>&quot;5391 &quot;</td><td>&quot;5734 &quot;</td><td>&quot;6134 &quot;</td><td>&quot;6784 &quot;</td><td>&quot;7372 &quot;</td><td>&quot;7169 &quot;</td><td>&quot;7596 &quot;</td><td>&quot;7123 &quot;</td><td>&quot;6954 &quot;</td><td>&quot;5725 &quot;</td><td>&quot;4808 &quot;</td><td>&quot;4508 &quot;</td><td>&quot;4304 &quot;</td><td>&quot;4772 &quot;</td><td>&quot;5090 &quot;</td><td>&quot;6398 &quot;</td><td>&quot;6475 &quot;</td><td>&quot;7722 &quot;</td><td>&quot;8533 &quot;</td><td>&quot;7742 &quot;</td><td>&quot;7808 &quot;</td><td>&quot;6933 &quot;</td><td>&quot;5777 &quot;</td><td>&quot;5014 &quot;</td><td>&quot;4875 &quot;</td><td>&quot;4873 &quot;</td><td>&quot;5643 &quot;</td><td>&quot;6755 &quot;</td><td>&quot;7390 &quot;</td><td>&quot;8709 &quot;</td><td>&hellip;</td><td>&quot;6578 &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td><td>&quot;: &quot;</td></tr></tbody></table></div>"],"text/plain":["shape: (15, 145)\n","┌─────────────────┬──────────┬──────────┬──────────┬───┬──────────┬──────────┬──────────┬──────────┐\n","│ freq,unit,produ ┆ 2008-01  ┆ 2008-02  ┆ 2008-03  ┆ … ┆ 2019-09  ┆ 2019-10  ┆ 2019-11  ┆ 2019-12  │\n","│ ct,indic_nrg,ge ┆ ---      ┆ ---      ┆ ---      ┆   ┆ ---      ┆ ---      ┆ ---      ┆ ---      │\n","│ o\\TIME_PERIOD   ┆ str      ┆ str      ┆ str      ┆   ┆ str      ┆ str      ┆ str      ┆ str      │\n","│ ---             ┆          ┆          ┆          ┆   ┆          ┆          ┆          ┆          │\n","│ str             ┆          ┆          ┆          ┆   ┆          ┆          ┆          ┆          │\n","╞═════════════════╪══════════╪══════════╪══════════╪═══╪══════════╪══════════╪══════════╪══════════╡\n","│ M,GWH,6000,15_1 ┆ 5529     ┆ 5098     ┆ 5561     ┆ … ┆ :        ┆ :        ┆ :        ┆ :        │\n","│ 07000,AT        ┆          ┆          ┆          ┆   ┆          ┆          ┆          ┆          │\n","│ M,GWH,6000,15_1 ┆ 7711     ┆ 7126     ┆ 6718     ┆ … ┆ :        ┆ :        ┆ :        ┆ :        │\n","│ 07000,BE        ┆          ┆          ┆          ┆   ┆          ┆          ┆          ┆          │\n","│ M,GWH,6000,15_1 ┆ 4435     ┆ 3678     ┆ 3780     ┆ … ┆ :        ┆ :        ┆ :        ┆ :        │\n","│ 07000,BG        ┆          ┆          ┆          ┆   ┆          ┆          ┆          ┆          │\n","│ M,GWH,6000,15_1 ┆ 461      ┆ 415      ┆ 340      ┆ … ┆ :        ┆ :        ┆ :        ┆ :        │\n","│ 07000,CY        ┆          ┆          ┆          ┆   ┆          ┆          ┆          ┆          │\n","│ …               ┆ …        ┆ …        ┆ …        ┆ … ┆ …        ┆ …        ┆ …        ┆ …        │\n","│ M,GWH,6000,15_1 ┆ 309105   ┆ 287339   ┆ 287960   ┆ … ┆ :        ┆ :        ┆ :        ┆ :        │\n","│ 07000,EU27_2007 ┆          ┆          ┆          ┆   ┆          ┆          ┆          ┆          │\n","│ M,GWH,6000,15_1 ┆ :        ┆ :        ┆ :        ┆ … ┆ :        ┆ :        ┆ :        ┆ :        │\n","│ 07000,EU27_2020 ┆          ┆          ┆          ┆   ┆          ┆          ┆          ┆          │\n","│ M,GWH,6000,15_1 ┆ :        ┆ :        ┆ :        ┆ … ┆ :        ┆ :        ┆ :        ┆ :        │\n","│ 07000,EU28      ┆          ┆          ┆          ┆   ┆          ┆          ┆          ┆          │\n","│ M,GWH,6000,15_1 ┆ 7471     ┆ 6729     ┆ 6947     ┆ … ┆ :        ┆ :        ┆ :        ┆ :        │\n","│ 07000,FI        ┆          ┆          ┆          ┆   ┆          ┆          ┆          ┆          │\n","└─────────────────┴──────────┴──────────┴──────────┴───┴──────────┴──────────┴──────────┴──────────┘"]},"execution_count":14,"metadata":{},"output_type":"execute_result"}],"source":["dataframe.head(n=N)"]},{"cell_type":"markdown","metadata":{"id":"so7MxvDIQrIP"},"source":["Here we notice a couple of issues:\n","\n","1. The dataset is pivoted, i.e. months are shown as columns, which is usually not so useful for analyzing time series.\n","2. All columns are loaded as text. The reason for this is that whenever there is no data, there are `:` symbols in the file.\n","3. The name of the first column looks strange (`freq,unit,product,indic_nrg,geo\\TIME_PERIOD`)\n","\n","Let's clean up the dataset by fixing these issues."]},{"cell_type":"markdown","metadata":{"id":"8Onr8f2OR2zy"},"source":["## Cleaning the dataset\n","\n","Let's start by unpivoting the dataframe. Polars dataframes offer the [`melt`](https://pola-rs.github.io/polars/user-guide/transformations/melt/#dataset) method to do this.\n","\n","We could either list all of the columns except the id column for melting, but that would not be practical in this case. Luckily, we can provide only the id variables to the `melt` method, everything else would be considered a value variable."]},{"cell_type":"code","execution_count":15,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":562},"executionInfo":{"elapsed":4,"status":"ok","timestamp":1702213231639,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"qW92JHc5Qhdh","outputId":"37fa9180-7fb0-4923-ebc0-b948725c9681"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (15, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>freq,unit,product,indic_nrg,geo\\TIME_PERIOD</th><th>month</th><th>value</th></tr><tr><td>str</td><td>str</td><td>str</td></tr></thead><tbody><tr><td>&quot;M,GWH,6000,15_107000,AT&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;5529 &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,BE&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;7711 &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,BG&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;4435 &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,CY&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;461 &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,CZ&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;8497 &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,DE&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;52597 &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,DK&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;4027 &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EA&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;212276 &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EE&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;1125 &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EL&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;5119 &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,ES&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;26512 &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EU27_2007&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;309105 &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EU27_2020&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;: &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EU28&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;: &quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,FI&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;7471 &quot;</td></tr></tbody></table></div>"],"text/plain":["shape: (15, 3)\n","┌─────────────────────────────────────────────┬──────────┬─────────┐\n","│ freq,unit,product,indic_nrg,geo\\TIME_PERIOD ┆ month    ┆ value   │\n","│ ---                                         ┆ ---      ┆ ---     │\n","│ str                                         ┆ str      ┆ str     │\n","╞═════════════════════════════════════════════╪══════════╪═════════╡\n","│ M,GWH,6000,15_107000,AT                     ┆ 2008-01  ┆ 5529    │\n","│ M,GWH,6000,15_107000,BE                     ┆ 2008-01  ┆ 7711    │\n","│ M,GWH,6000,15_107000,BG                     ┆ 2008-01  ┆ 4435    │\n","│ M,GWH,6000,15_107000,CY                     ┆ 2008-01  ┆ 461     │\n","│ …                                           ┆ …        ┆ …       │\n","│ M,GWH,6000,15_107000,EU27_2007              ┆ 2008-01  ┆ 309105  │\n","│ M,GWH,6000,15_107000,EU27_2020              ┆ 2008-01  ┆ :       │\n","│ M,GWH,6000,15_107000,EU28                   ┆ 2008-01  ┆ :       │\n","│ M,GWH,6000,15_107000,FI                     ┆ 2008-01  ┆ 7471    │\n","└─────────────────────────────────────────────┴──────────┴─────────┘"]},"execution_count":15,"metadata":{},"output_type":"execute_result"}],"source":["cleaned_dataframe = dataframe.melt(id_vars='freq,unit,product,indic_nrg,geo\\TIME_PERIOD', variable_name='month')\n","cleaned_dataframe.head(n=N)"]},{"cell_type":"markdown","metadata":{"id":"nZxAKBnRWcPc"},"source":["If you look at the `value` column above very carefully, you'll notice that there's another, quite subtle issue: there's a redundant space at the end of each value, which would lead to an error if we tried to cast the column into a float. So we'll have to get rid of it first.\n","\n","Some rows also contain letters for remarks, like `p` or `e`, so we'll have to remove those as well."]},{"cell_type":"code","execution_count":16,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":562},"executionInfo":{"elapsed":412,"status":"ok","timestamp":1702213232047,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"wNBVKQToVyaH","outputId":"f11b1953-1e74-432f-8d5b-cc0f4b496c5f"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (15, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>freq,unit,product,indic_nrg,geo\\TIME_PERIOD</th><th>month</th><th>value</th></tr><tr><td>str</td><td>str</td><td>str</td></tr></thead><tbody><tr><td>&quot;M,GWH,6000,15_107000,AT&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;5529&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,BE&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;7711&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,BG&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;4435&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,CY&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;461&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,CZ&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;8497&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,DE&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;52597&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,DK&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;4027&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EA&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;212276&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EE&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;1125&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EL&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;5119&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,ES&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;26512&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EU27_2007&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;309105&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EU27_2020&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;:&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EU28&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;:&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,FI&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;7471&quot;</td></tr></tbody></table></div>"],"text/plain":["shape: (15, 3)\n","┌─────────────────────────────────────────────┬──────────┬────────┐\n","│ freq,unit,product,indic_nrg,geo\\TIME_PERIOD ┆ month    ┆ value  │\n","│ ---                                         ┆ ---      ┆ ---    │\n","│ str                                         ┆ str      ┆ str    │\n","╞═════════════════════════════════════════════╪══════════╪════════╡\n","│ M,GWH,6000,15_107000,AT                     ┆ 2008-01  ┆ 5529   │\n","│ M,GWH,6000,15_107000,BE                     ┆ 2008-01  ┆ 7711   │\n","│ M,GWH,6000,15_107000,BG                     ┆ 2008-01  ┆ 4435   │\n","│ M,GWH,6000,15_107000,CY                     ┆ 2008-01  ┆ 461    │\n","│ …                                           ┆ …        ┆ …      │\n","│ M,GWH,6000,15_107000,EU27_2007              ┆ 2008-01  ┆ 309105 │\n","│ M,GWH,6000,15_107000,EU27_2020              ┆ 2008-01  ┆ :      │\n","│ M,GWH,6000,15_107000,EU28                   ┆ 2008-01  ┆ :      │\n","│ M,GWH,6000,15_107000,FI                     ┆ 2008-01  ┆ 7471   │\n","└─────────────────────────────────────────────┴──────────┴────────┘"]},"execution_count":16,"metadata":{},"output_type":"execute_result"}],"source":["cleaned_dataframe = cleaned_dataframe.with_columns(\n","    pl.col('value')\n","    .str.replace('d', '')\n","    .str.replace('p', '')\n","    .str.replace('e', '')\n","    .str.replace(': c', ': ')\n","    .str.replace('n', '')\n","    .str.replace(': u', ': ')\n","    .str.strip_chars()\n","    .name.keep()\n",")\n","cleaned_dataframe.head(n=N)"]},{"cell_type":"markdown","metadata":{"id":"JqVaoXYAU2fq"},"source":["Next, we'll want to get rid of the `: ` values in the `value` column and then convert the column to a numeric value. The easiest way to do that is to use the `.when().then().otherwise()` expression offered by Polars."]},{"cell_type":"code","execution_count":17,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":562},"executionInfo":{"elapsed":12,"status":"ok","timestamp":1702213232047,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"jYEhX0S6T5Iw","outputId":"a28ce601-a3ec-4bcc-858e-3460581f0652"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (15, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>freq,unit,product,indic_nrg,geo\\TIME_PERIOD</th><th>month</th><th>value</th></tr><tr><td>str</td><td>str</td><td>str</td></tr></thead><tbody><tr><td>&quot;M,GWH,6000,15_107000,AT&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;5529&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,BE&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;7711&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,BG&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;4435&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,CY&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;461&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,CZ&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;8497&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,DE&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;52597&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,DK&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;4027&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EA&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;212276&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EE&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;1125&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EL&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;5119&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,ES&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;26512&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EU27_2007&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;309105&quot;</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EU27_2020&quot;</td><td>&quot;2008-01 &quot;</td><td>null</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EU28&quot;</td><td>&quot;2008-01 &quot;</td><td>null</td></tr><tr><td>&quot;M,GWH,6000,15_107000,FI&quot;</td><td>&quot;2008-01 &quot;</td><td>&quot;7471&quot;</td></tr></tbody></table></div>"],"text/plain":["shape: (15, 3)\n","┌─────────────────────────────────────────────┬──────────┬────────┐\n","│ freq,unit,product,indic_nrg,geo\\TIME_PERIOD ┆ month    ┆ value  │\n","│ ---                                         ┆ ---      ┆ ---    │\n","│ str                                         ┆ str      ┆ str    │\n","╞═════════════════════════════════════════════╪══════════╪════════╡\n","│ M,GWH,6000,15_107000,AT                     ┆ 2008-01  ┆ 5529   │\n","│ M,GWH,6000,15_107000,BE                     ┆ 2008-01  ┆ 7711   │\n","│ M,GWH,6000,15_107000,BG                     ┆ 2008-01  ┆ 4435   │\n","│ M,GWH,6000,15_107000,CY                     ┆ 2008-01  ┆ 461    │\n","│ …                                           ┆ …        ┆ …      │\n","│ M,GWH,6000,15_107000,EU27_2007              ┆ 2008-01  ┆ 309105 │\n","│ M,GWH,6000,15_107000,EU27_2020              ┆ 2008-01  ┆ null   │\n","│ M,GWH,6000,15_107000,EU28                   ┆ 2008-01  ┆ null   │\n","│ M,GWH,6000,15_107000,FI                     ┆ 2008-01  ┆ 7471   │\n","└─────────────────────────────────────────────┴──────────┴────────┘"]},"execution_count":17,"metadata":{},"output_type":"execute_result"}],"source":["cleaned_dataframe = cleaned_dataframe.with_columns(\n","    pl\n","    .when(pl.col('value') == ':')\n","    .then(None)\n","    .otherwise(pl.col('value'))\n","    .name.keep()\n",")\n","cleaned_dataframe.head(n=N)"]},{"cell_type":"markdown","metadata":{"id":"j_yU2dExaaZf"},"source":["As you'll notice, we've replaced the missing values with a `null`. This is usually a good practice as this will help the framework identify missing values as such.\n","\n","Next we'll cast the values to a numeric value."]},{"cell_type":"code","execution_count":18,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":562},"executionInfo":{"elapsed":12,"status":"ok","timestamp":1702213232047,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"jJPkt34qXpaQ","outputId":"4e661c50-f651-4134-d1f3-e22e5f33265b"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (15, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>freq,unit,product,indic_nrg,geo\\TIME_PERIOD</th><th>month</th><th>value</th></tr><tr><td>str</td><td>str</td><td>f32</td></tr></thead><tbody><tr><td>&quot;M,GWH,6000,15_107000,AT&quot;</td><td>&quot;2008-01 &quot;</td><td>5529.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,BE&quot;</td><td>&quot;2008-01 &quot;</td><td>7711.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,BG&quot;</td><td>&quot;2008-01 &quot;</td><td>4435.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,CY&quot;</td><td>&quot;2008-01 &quot;</td><td>461.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,CZ&quot;</td><td>&quot;2008-01 &quot;</td><td>8497.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,DE&quot;</td><td>&quot;2008-01 &quot;</td><td>52597.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,DK&quot;</td><td>&quot;2008-01 &quot;</td><td>4027.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EA&quot;</td><td>&quot;2008-01 &quot;</td><td>212276.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EE&quot;</td><td>&quot;2008-01 &quot;</td><td>1125.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EL&quot;</td><td>&quot;2008-01 &quot;</td><td>5119.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,ES&quot;</td><td>&quot;2008-01 &quot;</td><td>26512.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EU27_2007&quot;</td><td>&quot;2008-01 &quot;</td><td>309105.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EU27_2020&quot;</td><td>&quot;2008-01 &quot;</td><td>null</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EU28&quot;</td><td>&quot;2008-01 &quot;</td><td>null</td></tr><tr><td>&quot;M,GWH,6000,15_107000,FI&quot;</td><td>&quot;2008-01 &quot;</td><td>7471.0</td></tr></tbody></table></div>"],"text/plain":["shape: (15, 3)\n","┌─────────────────────────────────────────────┬──────────┬──────────┐\n","│ freq,unit,product,indic_nrg,geo\\TIME_PERIOD ┆ month    ┆ value    │\n","│ ---                                         ┆ ---      ┆ ---      │\n","│ str                                         ┆ str      ┆ f32      │\n","╞═════════════════════════════════════════════╪══════════╪══════════╡\n","│ M,GWH,6000,15_107000,AT                     ┆ 2008-01  ┆ 5529.0   │\n","│ M,GWH,6000,15_107000,BE                     ┆ 2008-01  ┆ 7711.0   │\n","│ M,GWH,6000,15_107000,BG                     ┆ 2008-01  ┆ 4435.0   │\n","│ M,GWH,6000,15_107000,CY                     ┆ 2008-01  ┆ 461.0    │\n","│ …                                           ┆ …        ┆ …        │\n","│ M,GWH,6000,15_107000,EU27_2007              ┆ 2008-01  ┆ 309105.0 │\n","│ M,GWH,6000,15_107000,EU27_2020              ┆ 2008-01  ┆ null     │\n","│ M,GWH,6000,15_107000,EU28                   ┆ 2008-01  ┆ null     │\n","│ M,GWH,6000,15_107000,FI                     ┆ 2008-01  ┆ 7471.0   │\n","└─────────────────────────────────────────────┴──────────┴──────────┘"]},"execution_count":18,"metadata":{},"output_type":"execute_result"}],"source":["cleaned_dataframe = cleaned_dataframe.with_columns(\n","    pl.col('value')\n","    .cast(pl.Float32)\n","    .name.keep()\n",")\n","cleaned_dataframe.head(n=N)"]},{"cell_type":"markdown","metadata":{"id":"QzO9gCvEbMxK"},"source":["Now we've cast the value column into a floating-point value. Next, let's remove the trailing string from the month and the id column as well."]},{"cell_type":"code","execution_count":19,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":562},"executionInfo":{"elapsed":11,"status":"ok","timestamp":1702213232047,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"XGMKov7AavkT","outputId":"5420a5d9-21e5-43b6-8a7d-915dee59e574"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (15, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>freq,unit,product,indic_nrg,geo\\TIME_PERIOD</th><th>month</th><th>value</th></tr><tr><td>str</td><td>str</td><td>f32</td></tr></thead><tbody><tr><td>&quot;M,GWH,6000,15_107000,AT&quot;</td><td>&quot;2008-01&quot;</td><td>5529.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,BE&quot;</td><td>&quot;2008-01&quot;</td><td>7711.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,BG&quot;</td><td>&quot;2008-01&quot;</td><td>4435.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,CY&quot;</td><td>&quot;2008-01&quot;</td><td>461.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,CZ&quot;</td><td>&quot;2008-01&quot;</td><td>8497.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,DE&quot;</td><td>&quot;2008-01&quot;</td><td>52597.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,DK&quot;</td><td>&quot;2008-01&quot;</td><td>4027.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EA&quot;</td><td>&quot;2008-01&quot;</td><td>212276.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EE&quot;</td><td>&quot;2008-01&quot;</td><td>1125.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EL&quot;</td><td>&quot;2008-01&quot;</td><td>5119.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,ES&quot;</td><td>&quot;2008-01&quot;</td><td>26512.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EU27_2007&quot;</td><td>&quot;2008-01&quot;</td><td>309105.0</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EU27_2020&quot;</td><td>&quot;2008-01&quot;</td><td>null</td></tr><tr><td>&quot;M,GWH,6000,15_107000,EU28&quot;</td><td>&quot;2008-01&quot;</td><td>null</td></tr><tr><td>&quot;M,GWH,6000,15_107000,FI&quot;</td><td>&quot;2008-01&quot;</td><td>7471.0</td></tr></tbody></table></div>"],"text/plain":["shape: (15, 3)\n","┌─────────────────────────────────────────────┬─────────┬──────────┐\n","│ freq,unit,product,indic_nrg,geo\\TIME_PERIOD ┆ month   ┆ value    │\n","│ ---                                         ┆ ---     ┆ ---      │\n","│ str                                         ┆ str     ┆ f32      │\n","╞═════════════════════════════════════════════╪═════════╪══════════╡\n","│ M,GWH,6000,15_107000,AT                     ┆ 2008-01 ┆ 5529.0   │\n","│ M,GWH,6000,15_107000,BE                     ┆ 2008-01 ┆ 7711.0   │\n","│ M,GWH,6000,15_107000,BG                     ┆ 2008-01 ┆ 4435.0   │\n","│ M,GWH,6000,15_107000,CY                     ┆ 2008-01 ┆ 461.0    │\n","│ …                                           ┆ …       ┆ …        │\n","│ M,GWH,6000,15_107000,EU27_2007              ┆ 2008-01 ┆ 309105.0 │\n","│ M,GWH,6000,15_107000,EU27_2020              ┆ 2008-01 ┆ null     │\n","│ M,GWH,6000,15_107000,EU28                   ┆ 2008-01 ┆ null     │\n","│ M,GWH,6000,15_107000,FI                     ┆ 2008-01 ┆ 7471.0   │\n","└─────────────────────────────────────────────┴─────────┴──────────┘"]},"execution_count":19,"metadata":{},"output_type":"execute_result"}],"source":["cleaned_dataframe = cleaned_dataframe.with_columns(\n","    [\n","        pl.col('month').str.strip_chars().name.keep(),\n","        pl.col('freq,unit,product,indic_nrg,geo\\TIME_PERIOD').str.strip_chars().name.keep()\n","    ]\n",")\n","cleaned_dataframe.head(n=N)"]},{"cell_type":"markdown","metadata":{"id":"sxO_ZQCicO4T"},"source":["That looks much better. Now, we can focus our attention on splitting the id column into dedicated columns for each field."]},{"cell_type":"code","execution_count":20,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":562},"executionInfo":{"elapsed":11,"status":"ok","timestamp":1702213232047,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"vDKoGIE7bwrA","outputId":"e9382cab-791a-4790-fec7-b13af815b6bb"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (15, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>freq</th><th>unit</th><th>product</th><th>indic_nrg</th><th>geo</th><th>month</th><th>value</th></tr><tr><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f32</td></tr></thead><tbody><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;AT&quot;</td><td>&quot;2008-01&quot;</td><td>5529.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;BE&quot;</td><td>&quot;2008-01&quot;</td><td>7711.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;BG&quot;</td><td>&quot;2008-01&quot;</td><td>4435.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;CY&quot;</td><td>&quot;2008-01&quot;</td><td>461.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;CZ&quot;</td><td>&quot;2008-01&quot;</td><td>8497.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;DE&quot;</td><td>&quot;2008-01&quot;</td><td>52597.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;DK&quot;</td><td>&quot;2008-01&quot;</td><td>4027.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;EA&quot;</td><td>&quot;2008-01&quot;</td><td>212276.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;EE&quot;</td><td>&quot;2008-01&quot;</td><td>1125.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;EL&quot;</td><td>&quot;2008-01&quot;</td><td>5119.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;ES&quot;</td><td>&quot;2008-01&quot;</td><td>26512.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;EU27_2007&quot;</td><td>&quot;2008-01&quot;</td><td>309105.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;EU27_2020&quot;</td><td>&quot;2008-01&quot;</td><td>null</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;EU28&quot;</td><td>&quot;2008-01&quot;</td><td>null</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;FI&quot;</td><td>&quot;2008-01&quot;</td><td>7471.0</td></tr></tbody></table></div>"],"text/plain":["shape: (15, 7)\n","┌──────┬──────┬─────────┬───────────┬───────────┬─────────┬──────────┐\n","│ freq ┆ unit ┆ product ┆ indic_nrg ┆ geo       ┆ month   ┆ value    │\n","│ ---  ┆ ---  ┆ ---     ┆ ---       ┆ ---       ┆ ---     ┆ ---      │\n","│ str  ┆ str  ┆ str     ┆ str       ┆ str       ┆ str     ┆ f32      │\n","╞══════╪══════╪═════════╪═══════════╪═══════════╪═════════╪══════════╡\n","│ M    ┆ GWH  ┆ 6000    ┆ 15_107000 ┆ AT        ┆ 2008-01 ┆ 5529.0   │\n","│ M    ┆ GWH  ┆ 6000    ┆ 15_107000 ┆ BE        ┆ 2008-01 ┆ 7711.0   │\n","│ M    ┆ GWH  ┆ 6000    ┆ 15_107000 ┆ BG        ┆ 2008-01 ┆ 4435.0   │\n","│ M    ┆ GWH  ┆ 6000    ┆ 15_107000 ┆ CY        ┆ 2008-01 ┆ 461.0    │\n","│ …    ┆ …    ┆ …       ┆ …         ┆ …         ┆ …       ┆ …        │\n","│ M    ┆ GWH  ┆ 6000    ┆ 15_107000 ┆ EU27_2007 ┆ 2008-01 ┆ 309105.0 │\n","│ M    ┆ GWH  ┆ 6000    ┆ 15_107000 ┆ EU27_2020 ┆ 2008-01 ┆ null     │\n","│ M    ┆ GWH  ┆ 6000    ┆ 15_107000 ┆ EU28      ┆ 2008-01 ┆ null     │\n","│ M    ┆ GWH  ┆ 6000    ┆ 15_107000 ┆ FI        ┆ 2008-01 ┆ 7471.0   │\n","└──────┴──────┴─────────┴───────────┴───────────┴─────────┴──────────┘"]},"execution_count":20,"metadata":{},"output_type":"execute_result"}],"source":["cleaned_dataframe = (\n","    cleaned_dataframe\n","    .with_columns(\n","      pl.col('freq,unit,product,indic_nrg,geo\\TIME_PERIOD').str.split_exact(',', 4)\n","    )\n","    .unnest('freq,unit,product,indic_nrg,geo\\TIME_PERIOD')\n","    .rename({\n","        'field_0': 'freq',\n","        'field_1': 'unit',\n","        'field_2': 'product',\n","        'field_3': 'indic_nrg',\n","        'field_4': 'geo'}\n","    )\n",")\n","cleaned_dataframe.head(n=N)"]},{"cell_type":"markdown","metadata":{"id":"DJ4wmoNue6da"},"source":["So, what happened here?\n","\n","First, we used `.str.split_exact(',', 4)` to split the content of the id column into a structure of 5 elements.\n","\n","Then, we used the `.unnest()` method on the id column to break the structure into separate columns, which were named `field_0` to `field_4` respectively.\n","\n","Finally, we went on to renaming the columns to something more meaningful.\n","\n","There is only one step left before we can turn out attention to exploring the data: converting the `month` column into a `date`."]},{"cell_type":"code","execution_count":21,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":562},"executionInfo":{"elapsed":10,"status":"ok","timestamp":1702213232047,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"CiSk4SdscnxL","outputId":"7c1fd36f-c3cb-43b4-a608-7395d37d45eb"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (15, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>freq</th><th>unit</th><th>product</th><th>indic_nrg</th><th>geo</th><th>month</th><th>value</th></tr><tr><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>date</td><td>f32</td></tr></thead><tbody><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;AT&quot;</td><td>2008-01-01</td><td>5529.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;BE&quot;</td><td>2008-01-01</td><td>7711.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;BG&quot;</td><td>2008-01-01</td><td>4435.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;CY&quot;</td><td>2008-01-01</td><td>461.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;CZ&quot;</td><td>2008-01-01</td><td>8497.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;DE&quot;</td><td>2008-01-01</td><td>52597.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;DK&quot;</td><td>2008-01-01</td><td>4027.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;EA&quot;</td><td>2008-01-01</td><td>212276.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;EE&quot;</td><td>2008-01-01</td><td>1125.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;EL&quot;</td><td>2008-01-01</td><td>5119.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;ES&quot;</td><td>2008-01-01</td><td>26512.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;EU27_2007&quot;</td><td>2008-01-01</td><td>309105.0</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;EU27_2020&quot;</td><td>2008-01-01</td><td>null</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;EU28&quot;</td><td>2008-01-01</td><td>null</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GWH&quot;</td><td>&quot;6000&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;FI&quot;</td><td>2008-01-01</td><td>7471.0</td></tr></tbody></table></div>"],"text/plain":["shape: (15, 7)\n","┌──────┬──────┬─────────┬───────────┬───────────┬────────────┬──────────┐\n","│ freq ┆ unit ┆ product ┆ indic_nrg ┆ geo       ┆ month      ┆ value    │\n","│ ---  ┆ ---  ┆ ---     ┆ ---       ┆ ---       ┆ ---        ┆ ---      │\n","│ str  ┆ str  ┆ str     ┆ str       ┆ str       ┆ date       ┆ f32      │\n","╞══════╪══════╪═════════╪═══════════╪═══════════╪════════════╪══════════╡\n","│ M    ┆ GWH  ┆ 6000    ┆ 15_107000 ┆ AT        ┆ 2008-01-01 ┆ 5529.0   │\n","│ M    ┆ GWH  ┆ 6000    ┆ 15_107000 ┆ BE        ┆ 2008-01-01 ┆ 7711.0   │\n","│ M    ┆ GWH  ┆ 6000    ┆ 15_107000 ┆ BG        ┆ 2008-01-01 ┆ 4435.0   │\n","│ M    ┆ GWH  ┆ 6000    ┆ 15_107000 ┆ CY        ┆ 2008-01-01 ┆ 461.0    │\n","│ …    ┆ …    ┆ …       ┆ …         ┆ …         ┆ …          ┆ …        │\n","│ M    ┆ GWH  ┆ 6000    ┆ 15_107000 ┆ EU27_2007 ┆ 2008-01-01 ┆ 309105.0 │\n","│ M    ┆ GWH  ┆ 6000    ┆ 15_107000 ┆ EU27_2020 ┆ 2008-01-01 ┆ null     │\n","│ M    ┆ GWH  ┆ 6000    ┆ 15_107000 ┆ EU28      ┆ 2008-01-01 ┆ null     │\n","│ M    ┆ GWH  ┆ 6000    ┆ 15_107000 ┆ FI        ┆ 2008-01-01 ┆ 7471.0   │\n","└──────┴──────┴─────────┴───────────┴───────────┴────────────┴──────────┘"]},"execution_count":21,"metadata":{},"output_type":"execute_result"}],"source":["cleaned_dataframe = cleaned_dataframe.with_columns(\n","    pl.col('month').str.to_date(format='%Y-%m').name.keep()\n",")\n","cleaned_dataframe.head(n=N)"]},{"cell_type":"markdown","metadata":{"id":"ypDy7JYIpjN2"},"source":["With that we've cleaned and prepared our dataset for exploratory data analysis."]},{"cell_type":"markdown","metadata":{"id":"X-Z5XrXLprA3"},"source":["## Exploring the data"]},{"cell_type":"markdown","metadata":{"id":"1wu9e_FkskT1"},"source":["Let's start by checking the frequency of each item in the columns."]},{"cell_type":"code","execution_count":22,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":128},"executionInfo":{"elapsed":11,"status":"ok","timestamp":1702213232048,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"6w_Chd4thDd9","outputId":"0d74cac9-0b5d-4123-8b6a-f2d365dc83e7"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (1, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>freq</th><th>unit</th><th>product</th></tr><tr><td>struct[2]</td><td>struct[2]</td><td>struct[2]</td></tr></thead><tbody><tr><td>{&quot;M&quot;,211824}</td><td>{&quot;GWH&quot;,211824}</td><td>{&quot;6000&quot;,211824}</td></tr></tbody></table></div>"],"text/plain":["shape: (1, 3)\n","┌──────────────┬────────────────┬─────────────────┐\n","│ freq         ┆ unit           ┆ product         │\n","│ ---          ┆ ---            ┆ ---             │\n","│ struct[2]    ┆ struct[2]      ┆ struct[2]       │\n","╞══════════════╪════════════════╪═════════════════╡\n","│ {\"M\",211824} ┆ {\"GWH\",211824} ┆ {\"6000\",211824} │\n","└──────────────┴────────────────┴─────────────────┘"]},"execution_count":22,"metadata":{},"output_type":"execute_result"}],"source":["cleaned_dataframe.select([\n","    pl.col('freq').value_counts(),\n","    pl.col('unit').value_counts(),\n","    pl.col('product').value_counts(),\n","])"]},{"cell_type":"markdown","metadata":{"id":"iCYjZoAItR6w"},"source":["As you see above, the columns `freq`, `unit` and `product` only contain 1 distinct value each, so they won't add much value to the analysis. Let's drop them."]},{"cell_type":"code","execution_count":23,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":562},"executionInfo":{"elapsed":11,"status":"ok","timestamp":1702213232048,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"_bFvrOmzseje","outputId":"4e861cf1-e2c9-4332-d7a8-19423e040056"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (15, 4)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>indic_nrg</th><th>geo</th><th>month</th><th>value</th></tr><tr><td>str</td><td>str</td><td>date</td><td>f32</td></tr></thead><tbody><tr><td>&quot;15_107000&quot;</td><td>&quot;AT&quot;</td><td>2008-01-01</td><td>5529.0</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;BE&quot;</td><td>2008-01-01</td><td>7711.0</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;BG&quot;</td><td>2008-01-01</td><td>4435.0</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;CY&quot;</td><td>2008-01-01</td><td>461.0</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;CZ&quot;</td><td>2008-01-01</td><td>8497.0</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;DE&quot;</td><td>2008-01-01</td><td>52597.0</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;DK&quot;</td><td>2008-01-01</td><td>4027.0</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;EA&quot;</td><td>2008-01-01</td><td>212276.0</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;EE&quot;</td><td>2008-01-01</td><td>1125.0</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;EL&quot;</td><td>2008-01-01</td><td>5119.0</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;ES&quot;</td><td>2008-01-01</td><td>26512.0</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;EU27_2007&quot;</td><td>2008-01-01</td><td>309105.0</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;EU27_2020&quot;</td><td>2008-01-01</td><td>null</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;EU28&quot;</td><td>2008-01-01</td><td>null</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;FI&quot;</td><td>2008-01-01</td><td>7471.0</td></tr></tbody></table></div>"],"text/plain":["shape: (15, 4)\n","┌───────────┬───────────┬────────────┬──────────┐\n","│ indic_nrg ┆ geo       ┆ month      ┆ value    │\n","│ ---       ┆ ---       ┆ ---        ┆ ---      │\n","│ str       ┆ str       ┆ date       ┆ f32      │\n","╞═══════════╪═══════════╪════════════╪══════════╡\n","│ 15_107000 ┆ AT        ┆ 2008-01-01 ┆ 5529.0   │\n","│ 15_107000 ┆ BE        ┆ 2008-01-01 ┆ 7711.0   │\n","│ 15_107000 ┆ BG        ┆ 2008-01-01 ┆ 4435.0   │\n","│ 15_107000 ┆ CY        ┆ 2008-01-01 ┆ 461.0    │\n","│ …         ┆ …         ┆ …          ┆ …        │\n","│ 15_107000 ┆ EU27_2007 ┆ 2008-01-01 ┆ 309105.0 │\n","│ 15_107000 ┆ EU27_2020 ┆ 2008-01-01 ┆ null     │\n","│ 15_107000 ┆ EU28      ┆ 2008-01-01 ┆ null     │\n","│ 15_107000 ┆ FI        ┆ 2008-01-01 ┆ 7471.0   │\n","└───────────┴───────────┴────────────┴──────────┘"]},"execution_count":23,"metadata":{},"output_type":"execute_result"}],"source":["cleaned_dataframe = cleaned_dataframe.drop('freq', 'unit', 'product')\n","cleaned_dataframe.head(n=N)"]},{"cell_type":"markdown","metadata":{"id":"5jbhGkAat44F"},"source":["We continue looking at the frequencies of the remaining columns."]},{"cell_type":"code","execution_count":24,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":872},"executionInfo":{"elapsed":10,"status":"ok","timestamp":1702213232048,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"B4Imxpq9tjuc","outputId":"65e93b16-831f-469d-aa86-c7f116c3d826"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (36, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>indic_nrg</th><th>counts</th></tr><tr><td>str</td><td>u32</td></tr></thead><tbody><tr><td>&quot;16_107105B&quot;</td><td>5904</td></tr><tr><td>&quot;16_107101A&quot;</td><td>5904</td></tr><tr><td>&quot;17_107302&quot;</td><td>6192</td></tr><tr><td>&quot;B_100900&quot;</td><td>5472</td></tr><tr><td>&quot;15_107002&quot;</td><td>5472</td></tr><tr><td>&quot;16_107104&quot;</td><td>6192</td></tr><tr><td>&quot;16_107105E&quot;</td><td>5904</td></tr><tr><td>&quot;B_100300&quot;</td><td>6192</td></tr><tr><td>&quot;15_107001&quot;</td><td>5472</td></tr><tr><td>&quot;16_107104B&quot;</td><td>5904</td></tr><tr><td>&quot;16_107105C&quot;</td><td>5904</td></tr><tr><td>&quot;B_101400&quot;</td><td>5760</td></tr><tr><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;B_100320&quot;</td><td>5904</td></tr><tr><td>&quot;15_107005&quot;</td><td>5472</td></tr><tr><td>&quot;15_107003&quot;</td><td>5472</td></tr><tr><td>&quot;16_107100B&quot;</td><td>6192</td></tr><tr><td>&quot;B_100510&quot;</td><td>5904</td></tr><tr><td>&quot;B_100500&quot;</td><td>6192</td></tr><tr><td>&quot;16_107105C2&quot;</td><td>5904</td></tr><tr><td>&quot;B_107200&quot;</td><td>6192</td></tr><tr><td>&quot;16_107104E&quot;</td><td>5904</td></tr><tr><td>&quot;15_107000&quot;</td><td>5472</td></tr><tr><td>&quot;16_107103&quot;</td><td>6192</td></tr><tr><td>&quot;15_107301&quot;</td><td>5472</td></tr></tbody></table></div>"],"text/plain":["shape: (36, 2)\n","┌────────────┬────────┐\n","│ indic_nrg  ┆ counts │\n","│ ---        ┆ ---    │\n","│ str        ┆ u32    │\n","╞════════════╪════════╡\n","│ 16_107105B ┆ 5904   │\n","│ 16_107101A ┆ 5904   │\n","│ 17_107302  ┆ 6192   │\n","│ B_100900   ┆ 5472   │\n","│ …          ┆ …      │\n","│ 16_107104E ┆ 5904   │\n","│ 15_107000  ┆ 5472   │\n","│ 16_107103  ┆ 6192   │\n","│ 15_107301  ┆ 5472   │\n","└────────────┴────────┘"]},"execution_count":24,"metadata":{},"output_type":"execute_result"}],"source":["cleaned_dataframe.select(\n","    pl.col('indic_nrg').value_counts()\n",").unnest('indic_nrg')"]},{"cell_type":"markdown","metadata":{"id":"sjJ0iERJuSh2"},"source":["This shows us that we have 36 different indicators, with around 6,000 values per indicator."]},{"cell_type":"code","execution_count":25,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":872},"executionInfo":{"elapsed":10,"status":"ok","timestamp":1702213232048,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"hV3c7WPyuRWS","outputId":"c1a26530-101f-4ed4-e054-39cd62dbe123"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (43, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>geo</th><th>counts</th></tr><tr><td>str</td><td>u32</td></tr></thead><tbody><tr><td>&quot;DK&quot;</td><td>5184</td></tr><tr><td>&quot;LU&quot;</td><td>5184</td></tr><tr><td>&quot;SE&quot;</td><td>5184</td></tr><tr><td>&quot;BG&quot;</td><td>5184</td></tr><tr><td>&quot;CZ&quot;</td><td>5184</td></tr><tr><td>&quot;SK&quot;</td><td>5040</td></tr><tr><td>&quot;CY&quot;</td><td>5184</td></tr><tr><td>&quot;IT&quot;</td><td>5184</td></tr><tr><td>&quot;IE&quot;</td><td>5184</td></tr><tr><td>&quot;RO&quot;</td><td>5184</td></tr><tr><td>&quot;EU27_2020&quot;</td><td>5184</td></tr><tr><td>&quot;NO&quot;</td><td>5184</td></tr><tr><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;EU28&quot;</td><td>5184</td></tr><tr><td>&quot;RS&quot;</td><td>5184</td></tr><tr><td>&quot;HR&quot;</td><td>5184</td></tr><tr><td>&quot;EE&quot;</td><td>5184</td></tr><tr><td>&quot;EA&quot;</td><td>2592</td></tr><tr><td>&quot;PT&quot;</td><td>5184</td></tr><tr><td>&quot;NL&quot;</td><td>5184</td></tr><tr><td>&quot;EL&quot;</td><td>5184</td></tr><tr><td>&quot;SI&quot;</td><td>5184</td></tr><tr><td>&quot;EU27_2007&quot;</td><td>2592</td></tr><tr><td>&quot;BE&quot;</td><td>5184</td></tr><tr><td>&quot;ES&quot;</td><td>5184</td></tr></tbody></table></div>"],"text/plain":["shape: (43, 2)\n","┌───────────┬────────┐\n","│ geo       ┆ counts │\n","│ ---       ┆ ---    │\n","│ str       ┆ u32    │\n","╞═══════════╪════════╡\n","│ DK        ┆ 5184   │\n","│ LU        ┆ 5184   │\n","│ SE        ┆ 5184   │\n","│ BG        ┆ 5184   │\n","│ …         ┆ …      │\n","│ SI        ┆ 5184   │\n","│ EU27_2007 ┆ 2592   │\n","│ BE        ┆ 5184   │\n","│ ES        ┆ 5184   │\n","└───────────┴────────┘"]},"execution_count":25,"metadata":{},"output_type":"execute_result"}],"source":["cleaned_dataframe.select(\n","    pl.col('geo').value_counts()\n",").unnest('geo')"]},{"cell_type":"markdown","metadata":{"id":"mAeRKin_uev9"},"source":["We've got 43 different geos, with about 5000 values per geo, but with some major outliers, like `EU27_2007`, which has only 2592 values."]},{"cell_type":"code","execution_count":26,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":872},"executionInfo":{"elapsed":10,"status":"ok","timestamp":1702213232048,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"SEBQueY7uDYe","outputId":"01a9a774-fb78-4cfc-9982-4b555c10b0f3"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (144, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>month</th><th>counts</th></tr><tr><td>date</td><td>u32</td></tr></thead><tbody><tr><td>2011-04-01</td><td>1471</td></tr><tr><td>2010-10-01</td><td>1471</td></tr><tr><td>2011-06-01</td><td>1471</td></tr><tr><td>2011-12-01</td><td>1471</td></tr><tr><td>2008-11-01</td><td>1471</td></tr><tr><td>2011-08-01</td><td>1471</td></tr><tr><td>2010-12-01</td><td>1471</td></tr><tr><td>2008-02-01</td><td>1471</td></tr><tr><td>2009-01-01</td><td>1471</td></tr><tr><td>2010-09-01</td><td>1471</td></tr><tr><td>2012-10-01</td><td>1471</td></tr><tr><td>2016-09-01</td><td>1471</td></tr><tr><td>&hellip;</td><td>&hellip;</td></tr><tr><td>2018-10-01</td><td>1471</td></tr><tr><td>2018-11-01</td><td>1471</td></tr><tr><td>2015-11-01</td><td>1471</td></tr><tr><td>2010-01-01</td><td>1471</td></tr><tr><td>2018-01-01</td><td>1471</td></tr><tr><td>2013-06-01</td><td>1471</td></tr><tr><td>2015-04-01</td><td>1471</td></tr><tr><td>2019-07-01</td><td>1471</td></tr><tr><td>2008-08-01</td><td>1471</td></tr><tr><td>2012-02-01</td><td>1471</td></tr><tr><td>2015-06-01</td><td>1471</td></tr><tr><td>2019-09-01</td><td>1471</td></tr></tbody></table></div>"],"text/plain":["shape: (144, 2)\n","┌────────────┬────────┐\n","│ month      ┆ counts │\n","│ ---        ┆ ---    │\n","│ date       ┆ u32    │\n","╞════════════╪════════╡\n","│ 2011-04-01 ┆ 1471   │\n","│ 2010-10-01 ┆ 1471   │\n","│ 2011-06-01 ┆ 1471   │\n","│ 2011-12-01 ┆ 1471   │\n","│ …          ┆ …      │\n","│ 2008-08-01 ┆ 1471   │\n","│ 2012-02-01 ┆ 1471   │\n","│ 2015-06-01 ┆ 1471   │\n","│ 2019-09-01 ┆ 1471   │\n","└────────────┴────────┘"]},"execution_count":26,"metadata":{},"output_type":"execute_result"}],"source":["cleaned_dataframe.select(\n","    pl.col('month').value_counts()\n",").unnest('month')"]},{"cell_type":"markdown","metadata":{"id":"83TU__Ggu4-2"},"source":["We've got a total of 144 months, i.e. 12 years in the dataset."]},{"cell_type":"code","execution_count":27,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":376},"executionInfo":{"elapsed":15,"status":"ok","timestamp":1702213232356,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"XmQhb4g3u22O","outputId":"ed76d972-ebf0-494d-89bd-2d5d1596e84c"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (9, 5)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>describe</th><th>indic_nrg</th><th>geo</th><th>month</th><th>value</th></tr><tr><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td></tr></thead><tbody><tr><td>&quot;count&quot;</td><td>&quot;211824&quot;</td><td>&quot;211824&quot;</td><td>&quot;211824&quot;</td><td>211824.0</td></tr><tr><td>&quot;null_count&quot;</td><td>&quot;0&quot;</td><td>&quot;0&quot;</td><td>&quot;0&quot;</td><td>108232.0</td></tr><tr><td>&quot;mean&quot;</td><td>null</td><td>null</td><td>null</td><td>6298.541992</td></tr><tr><td>&quot;std&quot;</td><td>null</td><td>null</td><td>null</td><td>26036.425781</td></tr><tr><td>&quot;min&quot;</td><td>&quot;15_107000&quot;</td><td>&quot;AL&quot;</td><td>&quot;2008-01-01&quot;</td><td>-8166.0</td></tr><tr><td>&quot;25%&quot;</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>&quot;50%&quot;</td><td>null</td><td>null</td><td>null</td><td>282.725006</td></tr><tr><td>&quot;75%&quot;</td><td>null</td><td>null</td><td>null</td><td>1910.0</td></tr><tr><td>&quot;max&quot;</td><td>&quot;B_107200&quot;</td><td>&quot;UK&quot;</td><td>&quot;2019-12-01&quot;</td><td>310240.0</td></tr></tbody></table></div>"],"text/plain":["shape: (9, 5)\n","┌────────────┬───────────┬────────┬────────────┬──────────────┐\n","│ describe   ┆ indic_nrg ┆ geo    ┆ month      ┆ value        │\n","│ ---        ┆ ---       ┆ ---    ┆ ---        ┆ ---          │\n","│ str        ┆ str       ┆ str    ┆ str        ┆ f64          │\n","╞════════════╪═══════════╪════════╪════════════╪══════════════╡\n","│ count      ┆ 211824    ┆ 211824 ┆ 211824     ┆ 211824.0     │\n","│ null_count ┆ 0         ┆ 0      ┆ 0          ┆ 108232.0     │\n","│ mean       ┆ null      ┆ null   ┆ null       ┆ 6298.541992  │\n","│ std        ┆ null      ┆ null   ┆ null       ┆ 26036.425781 │\n","│ min        ┆ 15_107000 ┆ AL     ┆ 2008-01-01 ┆ -8166.0      │\n","│ 25%        ┆ null      ┆ null   ┆ null       ┆ 0.0          │\n","│ 50%        ┆ null      ┆ null   ┆ null       ┆ 282.725006   │\n","│ 75%        ┆ null      ┆ null   ┆ null       ┆ 1910.0       │\n","│ max        ┆ B_107200  ┆ UK     ┆ 2019-12-01 ┆ 310240.0     │\n","└────────────┴───────────┴────────┴────────────┴──────────────┘"]},"execution_count":27,"metadata":{},"output_type":"execute_result"}],"source":["cleaned_dataframe.describe()"]},{"cell_type":"markdown","metadata":{"id":"liNWELZwvKcU"},"source":["The describe statistics show us that we've got a total of 211,824 values in the dataset, out of which 108,232 are `null`, i.e. missing values.\n","\n","We saw above that the `indic_nrg` field is quite hard to read, because it's only a code of the indicator. It would be great if we could show the name of the indicator as well.\n","\n","Let's load the list of indicators for the `indic_nrg.csv` file."]},{"cell_type":"code","execution_count":28,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":562},"executionInfo":{"elapsed":11,"status":"ok","timestamp":1702213232357,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"H4jsHGJnvABU","outputId":"d817dca9-331a-41d8-8952-237facca1975"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (15, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>indicator_name</th><th>id</th></tr><tr><td>str</td><td>str</td></tr></thead><tbody><tr><td>&quot;imports&quot;</td><td>&quot;B_100300&quot;</td></tr><tr><td>&quot;imports_from_eu&quot;</td><td>&quot;B_100320&quot;</td></tr><tr><td>&quot;exports&quot;</td><td>&quot;B_100500&quot;</td></tr><tr><td>&quot;exports_to_EU&quot;</td><td>&quot;B_100510&quot;</td></tr><tr><td>&quot;gross_inland_consumption&quot;</td><td>&quot;B_100900&quot;</td></tr><tr><td>&quot;Used for pumped storage&quot;</td><td>&quot;17_107302&quot;</td></tr><tr><td>&quot;distribution_losses&quot;</td><td>&quot;B_101400&quot;</td></tr><tr><td>&quot;available_for_internal_market&quot;</td><td>&quot;B_107200&quot;</td></tr><tr><td>&quot;gross_electricity_generation_total&quot;</td><td>&quot;15_107000&quot;</td></tr><tr><td>&quot;gross_electricity_generation_hydro&quot;</td><td>&quot;15_107001&quot;</td></tr><tr><td>&quot;gross_electricity_generation_geothermal&quot;</td><td>&quot;15_107002&quot;</td></tr><tr><td>&quot;gross_electricity_generation_nuclear&quot;</td><td>&quot;15_107003&quot;</td></tr><tr><td>&quot;gross_electricity_generation_conventional_thermal…</td><td>&quot;15_107004&quot;</td></tr><tr><td>&quot;gross_electricity_generation_wind&quot;</td><td>&quot;15_107005&quot;</td></tr><tr><td>&quot;Gross electricity generation - Hydro (of which fr…</td><td>&quot;15_107301&quot;</td></tr></tbody></table></div>"],"text/plain":["shape: (15, 2)\n","┌─────────────────────────────────────────────────────┬───────────┐\n","│ indicator_name                                      ┆ id        │\n","│ ---                                                 ┆ ---       │\n","│ str                                                 ┆ str       │\n","╞═════════════════════════════════════════════════════╪═══════════╡\n","│ imports                                             ┆ B_100300  │\n","│ imports_from_eu                                     ┆ B_100320  │\n","│ exports                                             ┆ B_100500  │\n","│ exports_to_EU                                       ┆ B_100510  │\n","│ …                                                   ┆ …         │\n","│ gross_electricity_generation_nuclear                ┆ 15_107003 │\n","│ gross_electricity_generation_conventional_thermal   ┆ 15_107004 │\n","│ gross_electricity_generation_wind                   ┆ 15_107005 │\n","│ Gross electricity generation - Hydro (of which fro… ┆ 15_107301 │\n","└─────────────────────────────────────────────────────┴───────────┘"]},"execution_count":28,"metadata":{},"output_type":"execute_result"}],"source":["indicators = pl.read_csv(INDICATORS_FILE)\n","indicators.head(n=N)"]},{"cell_type":"markdown","metadata":{"id":"IX9rn0R3ynXl"},"source":["We have way too many indicators to analyze in a meaningful way, so we should limit our attention and keep only a few of them. I've preapred the list of indicators in a way that all the indicators we want to keep contain underscores, and the ones we want to drop do not contain any.\n","\n","This makes our life easier when we want to remove the unneeded indicators."]},{"cell_type":"code","execution_count":29,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":438},"executionInfo":{"elapsed":9,"status":"ok","timestamp":1702213232357,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"KTdv-zSnzEJj","outputId":"e7e24080-3fe3-4371-e95b-b9394e32590f"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (11, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>indicator_name</th><th>id</th></tr><tr><td>str</td><td>str</td></tr></thead><tbody><tr><td>&quot;imports_from_eu&quot;</td><td>&quot;B_100320&quot;</td></tr><tr><td>&quot;exports_to_EU&quot;</td><td>&quot;B_100510&quot;</td></tr><tr><td>&quot;gross_inland_consumption&quot;</td><td>&quot;B_100900&quot;</td></tr><tr><td>&quot;distribution_losses&quot;</td><td>&quot;B_101400&quot;</td></tr><tr><td>&quot;available_for_internal_market&quot;</td><td>&quot;B_107200&quot;</td></tr><tr><td>&quot;gross_electricity_generation_total&quot;</td><td>&quot;15_107000&quot;</td></tr><tr><td>&quot;gross_electricity_generation_hydro&quot;</td><td>&quot;15_107001&quot;</td></tr><tr><td>&quot;gross_electricity_generation_geothermal&quot;</td><td>&quot;15_107002&quot;</td></tr><tr><td>&quot;gross_electricity_generation_nuclear&quot;</td><td>&quot;15_107003&quot;</td></tr><tr><td>&quot;gross_electricity_generation_conventional_thermal…</td><td>&quot;15_107004&quot;</td></tr><tr><td>&quot;gross_electricity_generation_wind&quot;</td><td>&quot;15_107005&quot;</td></tr></tbody></table></div>"],"text/plain":["shape: (11, 2)\n","┌───────────────────────────────────────────────────┬───────────┐\n","│ indicator_name                                    ┆ id        │\n","│ ---                                               ┆ ---       │\n","│ str                                               ┆ str       │\n","╞═══════════════════════════════════════════════════╪═══════════╡\n","│ imports_from_eu                                   ┆ B_100320  │\n","│ exports_to_EU                                     ┆ B_100510  │\n","│ gross_inland_consumption                          ┆ B_100900  │\n","│ distribution_losses                               ┆ B_101400  │\n","│ …                                                 ┆ …         │\n","│ gross_electricity_generation_geothermal           ┆ 15_107002 │\n","│ gross_electricity_generation_nuclear              ┆ 15_107003 │\n","│ gross_electricity_generation_conventional_thermal ┆ 15_107004 │\n","│ gross_electricity_generation_wind                 ┆ 15_107005 │\n","└───────────────────────────────────────────────────┴───────────┘"]},"execution_count":29,"metadata":{},"output_type":"execute_result"}],"source":["indicators = indicators.filter(pl.col('indicator_name').str.contains('_'))\n","indicators.head(n=N)"]},{"cell_type":"markdown","metadata":{"id":"Y7hjPGL1z1U4"},"source":["In the next step, we're going to join the indicators dataframe with the cleaned dataset. We're going to do an inner join, which will eliminate the indicators not present in the `indicators` dataframe. This is a very efficient way to get rid of rows we don't need."]},{"cell_type":"code","execution_count":30,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":562},"executionInfo":{"elapsed":340,"status":"ok","timestamp":1702213232689,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"t2tFmiJrwHLa","outputId":"6afb0161-7862-46fe-c66e-903ca60ea362"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (15, 5)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>indic_nrg</th><th>geo</th><th>month</th><th>value</th><th>indicator_name</th></tr><tr><td>str</td><td>str</td><td>date</td><td>f32</td><td>str</td></tr></thead><tbody><tr><td>&quot;15_107000&quot;</td><td>&quot;AT&quot;</td><td>2008-01-01</td><td>5529.0</td><td>&quot;gross_electricity_generation_total&quot;</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;BE&quot;</td><td>2008-01-01</td><td>7711.0</td><td>&quot;gross_electricity_generation_total&quot;</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;BG&quot;</td><td>2008-01-01</td><td>4435.0</td><td>&quot;gross_electricity_generation_total&quot;</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;CY&quot;</td><td>2008-01-01</td><td>461.0</td><td>&quot;gross_electricity_generation_total&quot;</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;CZ&quot;</td><td>2008-01-01</td><td>8497.0</td><td>&quot;gross_electricity_generation_total&quot;</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;DE&quot;</td><td>2008-01-01</td><td>52597.0</td><td>&quot;gross_electricity_generation_total&quot;</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;DK&quot;</td><td>2008-01-01</td><td>4027.0</td><td>&quot;gross_electricity_generation_total&quot;</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;EA&quot;</td><td>2008-01-01</td><td>212276.0</td><td>&quot;gross_electricity_generation_total&quot;</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;EE&quot;</td><td>2008-01-01</td><td>1125.0</td><td>&quot;gross_electricity_generation_total&quot;</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;EL&quot;</td><td>2008-01-01</td><td>5119.0</td><td>&quot;gross_electricity_generation_total&quot;</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;ES&quot;</td><td>2008-01-01</td><td>26512.0</td><td>&quot;gross_electricity_generation_total&quot;</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;EU27_2007&quot;</td><td>2008-01-01</td><td>309105.0</td><td>&quot;gross_electricity_generation_total&quot;</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;EU27_2020&quot;</td><td>2008-01-01</td><td>null</td><td>&quot;gross_electricity_generation_total&quot;</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;EU28&quot;</td><td>2008-01-01</td><td>null</td><td>&quot;gross_electricity_generation_total&quot;</td></tr><tr><td>&quot;15_107000&quot;</td><td>&quot;FI&quot;</td><td>2008-01-01</td><td>7471.0</td><td>&quot;gross_electricity_generation_total&quot;</td></tr></tbody></table></div>"],"text/plain":["shape: (15, 5)\n","┌───────────┬───────────┬────────────┬──────────┬────────────────────────────────────┐\n","│ indic_nrg ┆ geo       ┆ month      ┆ value    ┆ indicator_name                     │\n","│ ---       ┆ ---       ┆ ---        ┆ ---      ┆ ---                                │\n","│ str       ┆ str       ┆ date       ┆ f32      ┆ str                                │\n","╞═══════════╪═══════════╪════════════╪══════════╪════════════════════════════════════╡\n","│ 15_107000 ┆ AT        ┆ 2008-01-01 ┆ 5529.0   ┆ gross_electricity_generation_total │\n","│ 15_107000 ┆ BE        ┆ 2008-01-01 ┆ 7711.0   ┆ gross_electricity_generation_total │\n","│ 15_107000 ┆ BG        ┆ 2008-01-01 ┆ 4435.0   ┆ gross_electricity_generation_total │\n","│ 15_107000 ┆ CY        ┆ 2008-01-01 ┆ 461.0    ┆ gross_electricity_generation_total │\n","│ …         ┆ …         ┆ …          ┆ …        ┆ …                                  │\n","│ 15_107000 ┆ EU27_2007 ┆ 2008-01-01 ┆ 309105.0 ┆ gross_electricity_generation_total │\n","│ 15_107000 ┆ EU27_2020 ┆ 2008-01-01 ┆ null     ┆ gross_electricity_generation_total │\n","│ 15_107000 ┆ EU28      ┆ 2008-01-01 ┆ null     ┆ gross_electricity_generation_total │\n","│ 15_107000 ┆ FI        ┆ 2008-01-01 ┆ 7471.0   ┆ gross_electricity_generation_total │\n","└───────────┴───────────┴────────────┴──────────┴────────────────────────────────────┘"]},"execution_count":30,"metadata":{},"output_type":"execute_result"}],"source":["cleaned_dataframe = cleaned_dataframe.join(indicators, left_on='indic_nrg', right_on='id')\n","cleaned_dataframe.head(n=N)"]},{"cell_type":"markdown","metadata":{"id":"2KTbXznb0Itl"},"source":["The number of rows dropeed from 211,824 to only 62,064.\n","\n","It would be great to move the indicators into the columns, as they are related to the same geo and the same month. This procedure is called pivoting."]},{"cell_type":"code","execution_count":31,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":837},"executionInfo":{"elapsed":9,"status":"ok","timestamp":1702213232689,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"v5BEORzZwdti","outputId":"9b2c4130-9f9c-4f65-906b-78427081ca1e"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (15, 13)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>geo</th><th>month</th><th>gross_electricity_generation_total</th><th>gross_electricity_generation_hydro</th><th>gross_electricity_generation_geothermal</th><th>gross_electricity_generation_nuclear</th><th>gross_electricity_generation_conventional_thermal</th><th>gross_electricity_generation_wind</th><th>imports_from_eu</th><th>exports_to_EU</th><th>gross_inland_consumption</th><th>distribution_losses</th><th>available_for_internal_market</th></tr><tr><td>str</td><td>date</td><td>f32</td><td>f32</td><td>f32</td><td>f32</td><td>f32</td><td>f32</td><td>f32</td><td>f32</td><td>f32</td><td>f32</td><td>f32</td></tr></thead><tbody><tr><td>&quot;AT&quot;</td><td>2008-01-01</td><td>5529.0</td><td>3050.0</td><td>0.0</td><td>0.0</td><td>2479.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>1109.0</td><td>0.0</td><td>6187.0</td></tr><tr><td>&quot;BE&quot;</td><td>2008-01-01</td><td>7711.0</td><td>261.0</td><td>0.0</td><td>4067.0</td><td>3383.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>1140.0</td><td>0.0</td><td>8400.0</td></tr><tr><td>&quot;BG&quot;</td><td>2008-01-01</td><td>4435.0</td><td>345.0</td><td>0.0</td><td>1535.0</td><td>2555.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-107.0</td><td>0.0</td><td>3808.0</td></tr><tr><td>&quot;CY&quot;</td><td>2008-01-01</td><td>461.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>461.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>436.0</td></tr><tr><td>&quot;CZ&quot;</td><td>2008-01-01</td><td>8497.0</td><td>266.0</td><td>0.0</td><td>2820.0</td><td>5411.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-1467.0</td><td>0.0</td><td>6352.0</td></tr><tr><td>&quot;DE&quot;</td><td>2008-01-01</td><td>52597.0</td><td>2265.0</td><td>0.0</td><td>12976.0</td><td>37356.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-3893.0</td><td>0.0</td><td>44608.0</td></tr><tr><td>&quot;DK&quot;</td><td>2008-01-01</td><td>4027.0</td><td>1054.0</td><td>0.0</td><td>0.0</td><td>2973.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-432.0</td><td>0.0</td><td>3430.0</td></tr><tr><td>&quot;EA&quot;</td><td>2008-01-01</td><td>212276.0</td><td>23365.0</td><td>486.0</td><td>69959.0</td><td>118466.0</td><td>0.0</td><td>null</td><td>null</td><td>1009.0</td><td>null</td><td>199686.0</td></tr><tr><td>&quot;EE&quot;</td><td>2008-01-01</td><td>1125.0</td><td>18.0</td><td>0.0</td><td>0.0</td><td>1107.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-150.0</td><td>0.0</td><td>864.0</td></tr><tr><td>&quot;EL&quot;</td><td>2008-01-01</td><td>5119.0</td><td>281.0</td><td>0.0</td><td>0.0</td><td>4838.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>121.0</td><td>0.0</td><td>4751.0</td></tr><tr><td>&quot;ES&quot;</td><td>2008-01-01</td><td>26512.0</td><td>3547.0</td><td>0.0</td><td>5504.0</td><td>17461.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-457.0</td><td>0.0</td><td>24597.0</td></tr><tr><td>&quot;EU27_2007&quot;</td><td>2008-01-01</td><td>309105.0</td><td>36437.0</td><td>486.0</td><td>89817.0</td><td>182365.0</td><td>0.0</td><td>null</td><td>null</td><td>-1194.0</td><td>null</td><td>287789.0</td></tr><tr><td>&quot;EU27_2020&quot;</td><td>2008-01-01</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td></tr><tr><td>&quot;EU28&quot;</td><td>2008-01-01</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td></tr><tr><td>&quot;FI&quot;</td><td>2008-01-01</td><td>7471.0</td><td>1445.0</td><td>0.0</td><td>2042.0</td><td>3984.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>1654.0</td><td>0.0</td><td>8836.0</td></tr></tbody></table></div>"],"text/plain":["shape: (15, 13)\n","┌───────────┬───────────┬───────────┬───────────┬───┬───────────┬───────────┬───────────┬──────────┐\n","│ geo       ┆ month     ┆ gross_ele ┆ gross_ele ┆ … ┆ exports_t ┆ gross_inl ┆ distribut ┆ availabl │\n","│ ---       ┆ ---       ┆ ctricity_ ┆ ctricity_ ┆   ┆ o_EU      ┆ and_consu ┆ ion_losse ┆ e_for_in │\n","│ str       ┆ date      ┆ generatio ┆ generatio ┆   ┆ ---       ┆ mption    ┆ s         ┆ ternal_m │\n","│           ┆           ┆ n_total   ┆ n_hydro   ┆   ┆ f32       ┆ ---       ┆ ---       ┆ arket    │\n","│           ┆           ┆ ---       ┆ ---       ┆   ┆           ┆ f32       ┆ f32       ┆ ---      │\n","│           ┆           ┆ f32       ┆ f32       ┆   ┆           ┆           ┆           ┆ f32      │\n","╞═══════════╪═══════════╪═══════════╪═══════════╪═══╪═══════════╪═══════════╪═══════════╪══════════╡\n","│ AT        ┆ 2008-01-0 ┆ 5529.0    ┆ 3050.0    ┆ … ┆ 0.0       ┆ 1109.0    ┆ 0.0       ┆ 6187.0   │\n","│           ┆ 1         ┆           ┆           ┆   ┆           ┆           ┆           ┆          │\n","│ BE        ┆ 2008-01-0 ┆ 7711.0    ┆ 261.0     ┆ … ┆ 0.0       ┆ 1140.0    ┆ 0.0       ┆ 8400.0   │\n","│           ┆ 1         ┆           ┆           ┆   ┆           ┆           ┆           ┆          │\n","│ BG        ┆ 2008-01-0 ┆ 4435.0    ┆ 345.0     ┆ … ┆ 0.0       ┆ -107.0    ┆ 0.0       ┆ 3808.0   │\n","│           ┆ 1         ┆           ┆           ┆   ┆           ┆           ┆           ┆          │\n","│ CY        ┆ 2008-01-0 ┆ 461.0     ┆ 0.0       ┆ … ┆ 0.0       ┆ 0.0       ┆ 0.0       ┆ 436.0    │\n","│           ┆ 1         ┆           ┆           ┆   ┆           ┆           ┆           ┆          │\n","│ …         ┆ …         ┆ …         ┆ …         ┆ … ┆ …         ┆ …         ┆ …         ┆ …        │\n","│ EU27_2007 ┆ 2008-01-0 ┆ 309105.0  ┆ 36437.0   ┆ … ┆ null      ┆ -1194.0   ┆ null      ┆ 287789.0 │\n","│           ┆ 1         ┆           ┆           ┆   ┆           ┆           ┆           ┆          │\n","│ EU27_2020 ┆ 2008-01-0 ┆ 0.0       ┆ 0.0       ┆ … ┆ 0.0       ┆ 0.0       ┆ 0.0       ┆ 0.0      │\n","│           ┆ 1         ┆           ┆           ┆   ┆           ┆           ┆           ┆          │\n","│ EU28      ┆ 2008-01-0 ┆ 0.0       ┆ 0.0       ┆ … ┆ 0.0       ┆ 0.0       ┆ 0.0       ┆ 0.0      │\n","│           ┆ 1         ┆           ┆           ┆   ┆           ┆           ┆           ┆          │\n","│ FI        ┆ 2008-01-0 ┆ 7471.0    ┆ 1445.0    ┆ … ┆ 0.0       ┆ 1654.0    ┆ 0.0       ┆ 8836.0   │\n","│           ┆ 1         ┆           ┆           ┆   ┆           ┆           ┆           ┆          │\n","└───────────┴───────────┴───────────┴───────────┴───┴───────────┴───────────┴───────────┴──────────┘"]},"execution_count":31,"metadata":{},"output_type":"execute_result"}],"source":["pivoted_dataframe = cleaned_dataframe.pivot(values='value', index=['geo', 'month'], columns='indicator_name', aggregate_function='sum')\n","pivoted_dataframe.head(n=N)"]},{"cell_type":"markdown","metadata":{"id":"-4LQejcN1QjS"},"source":["We're left with a dataframe with 6,192 rows and 13 columns. We can now start asking questions about our data.\n","\n","### Questions\n","\n","**Question #1**  \n","\n","How much energy did each country generate in total in the timeframe?\n","\n","To answer this question, we're going to group the data per geo and sum up the total energy generated. We'll sort them in descending order for convenience."]},{"cell_type":"code","execution_count":32,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":872},"executionInfo":{"elapsed":7,"status":"ok","timestamp":1702213232689,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"68bIm1AG0_2i","outputId":"71e48793-ee18-4a67-a82a-05640b7254c9"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (43, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>geo</th><th>gross_electricity_generation_total</th></tr><tr><td>str</td><td>f32</td></tr></thead><tbody><tr><td>&quot;EU27_2007&quot;</td><td>2.8242368e7</td></tr><tr><td>&quot;EA&quot;</td><td>1.973906e7</td></tr><tr><td>&quot;EU28&quot;</td><td>1.2451493e7</td></tr><tr><td>&quot;EU27_2020&quot;</td><td>1.1085141e7</td></tr><tr><td>&quot;FR&quot;</td><td>5.05411e6</td></tr><tr><td>&quot;DE&quot;</td><td>4.865313e6</td></tr><tr><td>&quot;UK&quot;</td><td>3.245098e6</td></tr><tr><td>&quot;IT&quot;</td><td>2.634892e6</td></tr><tr><td>&quot;ES&quot;</td><td>2.501921e6</td></tr><tr><td>&quot;TR&quot;</td><td>1.483779e6</td></tr><tr><td>&quot;PL&quot;</td><td>1.441953e6</td></tr><tr><td>&quot;SE&quot;</td><td>1.378885e6</td></tr><tr><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;LV&quot;</td><td>52394.0</td></tr><tr><td>&quot;IS&quot;</td><td>46900.0</td></tr><tr><td>&quot;CY&quot;</td><td>42500.0</td></tr><tr><td>&quot;LU&quot;</td><td>29573.0</td></tr><tr><td>&quot;MK&quot;</td><td>22774.0</td></tr><tr><td>&quot;MT&quot;</td><td>15034.0</td></tr><tr><td>&quot;ME&quot;</td><td>13326.0</td></tr><tr><td>&quot;EA19&quot;</td><td>0.0</td></tr><tr><td>&quot;GE&quot;</td><td>0.0</td></tr><tr><td>&quot;AL&quot;</td><td>0.0</td></tr><tr><td>&quot;UA&quot;</td><td>0.0</td></tr><tr><td>&quot;BA&quot;</td><td>0.0</td></tr></tbody></table></div>"],"text/plain":["shape: (43, 2)\n","┌───────────┬────────────────────────────────────┐\n","│ geo       ┆ gross_electricity_generation_total │\n","│ ---       ┆ ---                                │\n","│ str       ┆ f32                                │\n","╞═══════════╪════════════════════════════════════╡\n","│ EU27_2007 ┆ 2.8242368e7                        │\n","│ EA        ┆ 1.973906e7                         │\n","│ EU28      ┆ 1.2451493e7                        │\n","│ EU27_2020 ┆ 1.1085141e7                        │\n","│ …         ┆ …                                  │\n","│ GE        ┆ 0.0                                │\n","│ AL        ┆ 0.0                                │\n","│ UA        ┆ 0.0                                │\n","│ BA        ┆ 0.0                                │\n","└───────────┴────────────────────────────────────┘"]},"execution_count":32,"metadata":{},"output_type":"execute_result"}],"source":["(\n","    pivoted_dataframe\n","    .group_by('geo')\n","    .agg(pl.col('gross_electricity_generation_total').sum())\n","    .sort('gross_electricity_generation_total', descending=True)\n",")"]},{"cell_type":"markdown","metadata":{"id":"N7q1XPEy3eKk"},"source":["**Answer #1**  \n","\n","The table above shows how much energy each geo generated. The highest amount was generated by France, followed by Germany and the UK. Some countries have no data at all, thus their values are shown to be 0.\n"]},{"cell_type":"markdown","metadata":{"id":"PynqoLyl5OoK"},"source":["**Question #2**  \n","How much energy did France generate each year?\n","\n","To figure this out, we need to do something called [downsampling](https://machinelearningmastery.com/resample-interpolate-time-series-data-python/). Downsampling reduces the frequency, e.g. from months to years.\n","\n","In Polars, downsampling is really easy, it's simply a special case of grouping, done with the `group_by_dynamic` method. This method has a special requirement: the data has to be sorted by the column which contains the date (`month` in our case)."]},{"cell_type":"code","execution_count":33,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":469},"executionInfo":{"elapsed":7,"status":"ok","timestamp":1702213232689,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"dWe9vvde5OGb","outputId":"6fd1b19d-c3fa-4e89-d5c5-bda4a4ae1276"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (12, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>month</th><th>gross_electricity_generation_total</th></tr><tr><td>date</td><td>f32</td></tr></thead><tbody><tr><td>2008-01-01</td><td>573519.0</td></tr><tr><td>2009-01-01</td><td>542390.0</td></tr><tr><td>2010-01-01</td><td>572960.0</td></tr><tr><td>2011-01-01</td><td>564607.0</td></tr><tr><td>2012-01-01</td><td>560538.0</td></tr><tr><td>2013-01-01</td><td>561078.0</td></tr><tr><td>2014-01-01</td><td>557514.0</td></tr><tr><td>2015-01-01</td><td>568045.0</td></tr><tr><td>2016-01-01</td><td>553459.0</td></tr><tr><td>2017-01-01</td><td>0.0</td></tr><tr><td>2018-01-01</td><td>0.0</td></tr><tr><td>2019-01-01</td><td>0.0</td></tr></tbody></table></div>"],"text/plain":["shape: (12, 2)\n","┌────────────┬────────────────────────────────────┐\n","│ month      ┆ gross_electricity_generation_total │\n","│ ---        ┆ ---                                │\n","│ date       ┆ f32                                │\n","╞════════════╪════════════════════════════════════╡\n","│ 2008-01-01 ┆ 573519.0                           │\n","│ 2009-01-01 ┆ 542390.0                           │\n","│ 2010-01-01 ┆ 572960.0                           │\n","│ 2011-01-01 ┆ 564607.0                           │\n","│ …          ┆ …                                  │\n","│ 2016-01-01 ┆ 553459.0                           │\n","│ 2017-01-01 ┆ 0.0                                │\n","│ 2018-01-01 ┆ 0.0                                │\n","│ 2019-01-01 ┆ 0.0                                │\n","└────────────┴────────────────────────────────────┘"]},"execution_count":33,"metadata":{},"output_type":"execute_result"}],"source":["(\n","    pivoted_dataframe\n","    .filter(pl.col('geo') == 'FR')\n","    .sort('month')\n","    .group_by_dynamic('month', every='1y')\n","    .agg(pl.col('gross_electricity_generation_total').sum())\n",")"]},{"cell_type":"markdown","metadata":{"id":"ncsvdvMT6Z7m"},"source":["**Answer #2**  \n","\n","The table above shows us the energy generated by France each year. We can see a peak in 2008 and a 2nd peak in 2010.\n"]},{"cell_type":"markdown","metadata":{"id":"q5BtVp0465va"},"source":["**Question #3**\n","\n","Which country generated the highest proportion of its energy mix from renewable sources each year?\n","\n","We're going to define renewable energies as hydro, geothermal and wind for the purpose of this analysis.\n","\n","First, we'll have to create a new column for renewable sources. Then, we're going to aggregate the data up to an annual value and figure out which country did best each year."]},{"cell_type":"code","execution_count":34,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":376},"executionInfo":{"elapsed":6,"status":"ok","timestamp":1702213232689,"user":{"displayName":"Gabor Schulz","userId":"16620115095392214097"},"user_tz":-60},"id":"hEg6drq91444","outputId":"0d2f1730-14c6-47c6-cec5-71f614edfb1b"},"outputs":[{"data":{"text/html":["<div><style>\n",".dataframe > thead > tr,\n",".dataframe > tbody > tr {\n","  text-align: right;\n","  white-space: pre-wrap;\n","}\n","</style>\n","<small>shape: (9, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>month</th><th>geo</th><th>renewable_pct</th></tr><tr><td>date</td><td>str</td><td>f32</td></tr></thead><tbody><tr><td>2008-01-01</td><td>&quot;NO&quot;</td><td>99.169304</td></tr><tr><td>2009-01-01</td><td>&quot;NO&quot;</td><td>97.202293</td></tr><tr><td>2010-01-01</td><td>&quot;NO&quot;</td><td>95.778259</td></tr><tr><td>2011-01-01</td><td>&quot;NO&quot;</td><td>96.251648</td></tr><tr><td>2012-01-01</td><td>&quot;IS&quot;</td><td>99.966034</td></tr><tr><td>2013-01-01</td><td>&quot;IS&quot;</td><td>99.989006</td></tr><tr><td>2014-01-01</td><td>&quot;IS&quot;</td><td>100.016617</td></tr><tr><td>2015-01-01</td><td>&quot;IS&quot;</td><td>99.958076</td></tr><tr><td>2016-01-01</td><td>&quot;NO&quot;</td><td>97.677307</td></tr></tbody></table></div>"],"text/plain":["shape: (9, 3)\n","┌────────────┬─────┬───────────────┐\n","│ month      ┆ geo ┆ renewable_pct │\n","│ ---        ┆ --- ┆ ---           │\n","│ date       ┆ str ┆ f32           │\n","╞════════════╪═════╪═══════════════╡\n","│ 2008-01-01 ┆ NO  ┆ 99.169304     │\n","│ 2009-01-01 ┆ NO  ┆ 97.202293     │\n","│ 2010-01-01 ┆ NO  ┆ 95.778259     │\n","│ 2011-01-01 ┆ NO  ┆ 96.251648     │\n","│ 2012-01-01 ┆ IS  ┆ 99.966034     │\n","│ 2013-01-01 ┆ IS  ┆ 99.989006     │\n","│ 2014-01-01 ┆ IS  ┆ 100.016617    │\n","│ 2015-01-01 ┆ IS  ┆ 99.958076     │\n","│ 2016-01-01 ┆ NO  ┆ 97.677307     │\n","└────────────┴─────┴───────────────┘"]},"execution_count":34,"metadata":{},"output_type":"execute_result"}],"source":["(\n","    pivoted_dataframe\n","    .select(\n","        pl.col('geo'),\n","        pl.col('month'),\n","        pl.col('gross_electricity_generation_total'),\n","        (\n","            pl.col('gross_electricity_generation_hydro') +\n","            pl.col('gross_electricity_generation_geothermal') +\n","            pl.col('gross_electricity_generation_wind')\n","        ).alias('gross_electricity_generation_renewable'),\n","    )\n","    .sort('month')\n","    .group_by_dynamic('month', by='geo', every='1y')\n","    .agg(pl.col('gross_electricity_generation_total', 'gross_electricity_generation_renewable').sum())\n","    .select(\n","        pl.col('geo'),\n","        pl.col('month'),\n","        (pl.col('gross_electricity_generation_renewable') / pl.col('gross_electricity_generation_total') * 100).alias('renewable_pct')\n","    )\n","    .with_columns(\n","        pl.col('renewable_pct').max().over('month').alias('renewable_pct_max')\n","    )\n","    .filter(\n","        pl.col('renewable_pct') == pl.col('renewable_pct_max')\n","    )\n","    .sort('month')\n","    .select('month', 'geo', 'renewable_pct')\n",")"]},{"cell_type":"markdown","metadata":{"id":"g8TEwt73EyN5"},"source":["**Answer #3**  \n","This shows us that Norway and Iceland were continously competing with each other for the first place, as both countries essentially generated almost 100% of their electricity from renewable sources."]},{"cell_type":"markdown","metadata":{"id":"m5FYSau1FKIF"},"source":["The above expression looks quite hairy, so let's walk through each line one-by-one.\n","\n","`Line 1: (`  \n","I typically like to break such expressions into multiple lines for better readability. Starting with a bracket helps me avoid having to put a `\\` at the end of each line, further improving readability\n","\n","`Line 2: pivoted_dataframe`  \n","This is quite self-explanatory, we start off by taking the dataframe we built before.\n","\n","`Line 3: select(`  \n","We want to pick a couple of columns and build our new dataframe from them. The `select` method is made right for that purpose.\n","\n","`Line 4 - 6: pl.col(...)`  \n","We pick the `geo`, the `month` and the `gross_electricity_generation_total` column as they are, without doing anything special with them.\n","\n","`Line 7 - 12`  \n","We're adding up the hydro, geothermal and wind power per month and per geo, and calling the sum `gross_electricity_generation_renewable` using the `.alias` method.\n","\n","`Line 14: sort('month')`  \n","As we figured out before, we need to sort the data by the `month` for the `group_by_dynamic` to work as expected.\n","\n","`Line 15: .group_by_dynamic('month', by='geo', every='1y')`  \n","We are using the `month` column again to create aggergations on an annual level, but this time, we're adding the `by='geo'` argument, which let's us define which other columns we want to group by as well.\n","\n","`Line 16: .agg(pl.col('gross_electricity_generation_total', 'gross_electricity_generation_renewable').sum())`  \n","After grouping values on line 15, we're performing the aggregation on this line for the total and the renewable column.\n","\n","`Line 17 - 20: select(...)`  \n","We're selecting the geo and the month column, and we use the `gross_electricity_generation_renewable` and `gross_electricity_generation_total` column to calculate the percentage per geo and per year.\n","\n","`Line 21 - 23: with_columns(...)`  \n","We're calculating the maximum percentage of renewables. We use the `.over()` method to calculate the maximum percentage for each date entry in the dataset.\n","\n","`Line 24 - 26: filter(...)`  \n","We're checking if a given line is equal to that year's maximum value. If it is, then we've found the geo with the maximum percentage for the given year.\n","\n","`Line 27: .sort('month')`  \n","This is optional, but it makes it easier to read if we sort the values in ascending order by date.\n","\n","`Line 28: .select('month', 'geo', 'renewable_pct')`  \n","We don't need the `renewable_pct_max` column any more, so we can either select the columns we want (like we did here), or we could simply drop the columns we don't need any more.\n","\n","`Line 29: )`  \n","We close the parenthesis we opened on line 1."]},{"cell_type":"markdown","metadata":{"id":"Gii6PwhOKdso"},"source":["## Conclusion"]},{"cell_type":"markdown","metadata":{"id":"PiNMj-mJKj9o"},"source":["It turned out that polars offers a very easy API for working with time-based datasets for both cleaning and exploring the data. Pairing this with the ability to work on multiple CPUs, process datasets that are larger than the computer's memory thanks to lazy calculation, I believe that polars is an amazing tool for every data scientist's and analyst's toolbelt.\n","\n","**Go Polars!!!**"]}],"metadata":{"colab":{"authorship_tag":"ABX9TyNqv1eapa/8f95pIFaGw7dR","mount_file_id":"1S6gOFAvr9q6xaoR-xtoDD2YioBDoRD9n","provenance":[]},"kernelspec":{"display_name":"Python 3","name":"python3"},"language_info":{"name":"python"}},"nbformat":4,"nbformat_minor":0}