{ "cells": [ { "cell_type": "code", "execution_count": 20, "id": "48b01e32-c70e-495a-a4f0-629495e805be", "metadata": { "tags": [] }, "outputs": [], "source": [ "import pandas as pd\n", "import numpy as np\n", "import boto3\n", "from datetime import datetime, date\n" ] }, { "cell_type": "code", "execution_count": 3, "id": "41a06a2a-02f1-4534-b2c7-132395609e78", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/home/ec2-user/anaconda3/envs/python3/lib/python3.10/site-packages/fsspec/registry.py:272: UserWarning: Your installed version of s3fs is very old and known to cause\n", "severe performance issues, see also https://github.com/dask/dask/issues/10276\n", "\n", "To fix, you should specify a lower version bound on s3fs, or\n", "update the current installation.\n", "\n", " warnings.warn(s3_msg)\n" ] } ], "source": [ "arima = pd.read_csv('s3://dev-cucumbers/eimpara/Moments_2023_batches/ARIMA_chunks/ARIMA_CHUNKS_0178_20231110-123152.csv')\n", "df = arima.copy()" ] }, { "cell_type": "code", "execution_count": 5, "id": "ebd3c3f3", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "RangeIndex: 8128 entries, 0 to 8127\n", "Data columns (total 11 columns):\n", " # Column Non-Null Count Dtype \n", "--- ------ -------------- ----- \n", " 0 ISRC 8128 non-null object \n", " 1 pred_type 8128 non-null object \n", " 2 mse 8128 non-null float64\n", " 3 avg_streams_train 8128 non-null float64\n", " 4 avg_streams_test 8128 non-null float64\n", " 5 median_streams_train 8128 non-null float64\n", " 6 median_streams_test 8128 non-null float64\n", " 7 linear_gradient_train 8128 non-null float64\n", " 8 linear_gradient_test 8128 non-null float64\n", " 9 sum_forecast_errors 8128 non-null float64\n", " 10 len_df 8128 non-null int64 \n", "dtypes: float64(8), int64(1), object(2)\n", "memory usage: 698.6+ KB\n" ] } ], "source": [ "df.info()" ] }, { "cell_type": "code", "execution_count": 6, "id": "0b167d59-9f80-4346-82c6-d97b05ae7783", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ISRCpred_typemseavg_streams_trainavg_streams_testmedian_streams_trainmedian_streams_testlinear_gradient_trainlinear_gradient_testsum_forecast_errorslen_dfsign
0ADA010813602actuals_crossing_predicted17.71463919.67669217.57142919.016.00.0451130.285714-17.771140overall_negative
1AEA0D2012725actuals_crossing_predicted82.04944092.43609067.85714392.068.0-0.2556391.5714293.252140overall_positive
2AEA0D2015170actuals_crossing_predicted74.760579122.21052693.285714120.094.0-0.4661651.857143-28.158140overall_negative
3AEA0D2083476actuals_crossing_predicted17.17575818.30827115.00000018.014.00.0075190.285714-9.450140overall_negative
4AEA0D2139164actuals_crossing_predicted112.61416271.44360956.28571470.055.0-0.323308-1.14285750.687140overall_positive
\n", "
" ], "text/plain": [ " ISRC pred_type mse avg_streams_train \\\n", "0 ADA010813602 actuals_crossing_predicted 17.714639 19.676692 \n", "1 AEA0D2012725 actuals_crossing_predicted 82.049440 92.436090 \n", "2 AEA0D2015170 actuals_crossing_predicted 74.760579 122.210526 \n", "3 AEA0D2083476 actuals_crossing_predicted 17.175758 18.308271 \n", "4 AEA0D2139164 actuals_crossing_predicted 112.614162 71.443609 \n", "\n", " avg_streams_test median_streams_train median_streams_test \\\n", "0 17.571429 19.0 16.0 \n", "1 67.857143 92.0 68.0 \n", "2 93.285714 120.0 94.0 \n", "3 15.000000 18.0 14.0 \n", "4 56.285714 70.0 55.0 \n", "\n", " linear_gradient_train linear_gradient_test sum_forecast_errors len_df \\\n", "0 0.045113 0.285714 -17.771 140 \n", "1 -0.255639 1.571429 3.252 140 \n", "2 -0.466165 1.857143 -28.158 140 \n", "3 0.007519 0.285714 -9.450 140 \n", "4 -0.323308 -1.142857 50.687 140 \n", "\n", " sign \n", "0 overall_negative \n", "1 overall_positive \n", "2 overall_negative \n", "3 overall_negative \n", "4 overall_positive " ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df['sign'] = np.where(df['sum_forecast_errors'] > 0, 'overall_positive', 'overall_negative')\n", "df.head()" ] }, { "cell_type": "code", "execution_count": 8, "id": "e0fdbe7d-785e-4edf-8309-cd834f0b0145", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "pred_type\n", "actuals_crossing_predicted 7191\n", "actuals_below_predicted 550\n", "actuals_above_predicted 387\n", "Name: count, dtype: int64" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df['pred_type'].value_counts()" ] }, { "cell_type": "markdown", "id": "38c0e3e0-aa64-4747-9abf-53b27634d702", "metadata": {}, "source": [ "# Manual Buckets (sum of forecast errors)" ] }, { "cell_type": "code", "execution_count": 9, "id": "a43b3f51-abae-4b16-b38c-d33b957e3660", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "count 8128.000000\n", "mean -3.241088\n", "std 487.459474\n", "min -21662.727000\n", "25% -26.849000\n", "50% -2.008000\n", "75% 22.481500\n", "max 20920.334000\n", "Name: sum_forecast_errors, dtype: float64" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df['sum_forecast_errors'].describe()" ] }, { "cell_type": "code", "execution_count": 10, "id": "c0b3f2d5-6ff0-462b-8026-88557f40e12a", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/tmp/ipykernel_14354/1569395936.py:1: FutureWarning: Series.__getitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To access a value by position, use `ser.iloc[pos]`\n", " mean = df['sum_forecast_errors'].describe()[1]\n", "/tmp/ipykernel_14354/1569395936.py:2: FutureWarning: Series.__getitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To access a value by position, use `ser.iloc[pos]`\n", " perc_25 = df['sum_forecast_errors'].describe()[4]\n", "/tmp/ipykernel_14354/1569395936.py:3: FutureWarning: Series.__getitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To access a value by position, use `ser.iloc[pos]`\n", " median = df['sum_forecast_errors'].describe()[5]\n", "/tmp/ipykernel_14354/1569395936.py:4: FutureWarning: Series.__getitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To access a value by position, use `ser.iloc[pos]`\n", " perc_75 = df['sum_forecast_errors'].describe()[6]\n" ] } ], "source": [ "mean = df['sum_forecast_errors'].describe()[1]\n", "perc_25 = df['sum_forecast_errors'].describe()[4]\n", "median = df['sum_forecast_errors'].describe()[5]\n", "perc_75 = df['sum_forecast_errors'].describe()[6]" ] }, { "cell_type": "code", "execution_count": 11, "id": "4b7a73b3-ad86-4aa3-9ad3-7adab3f90309", "metadata": {}, "outputs": [], "source": [ "conditions = [(df['sum_forecast_errors']<= perc_75),\n", " ((df['sum_forecast_errors']> perc_75) & (df['sum_forecast_errors']<= mean)),\n", " (df['sum_forecast_errors'] > mean)]\n", "\n", "choices = ['doing_unnoticeably_better',\n", " 'doing_marginally_better',\n", " 'doing_much_better']\n", "\n", "df['Performance_test_period'] = np.select(conditions, choices)\n" ] }, { "cell_type": "code", "execution_count": 12, "id": "749a9beb-7e3b-414b-aaa3-6de8eede94d7", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Performance_test_period\n", "doing_unnoticeably_better 6096\n", "doing_much_better 2032\n", "Name: count, dtype: int64" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df['Performance_test_period'].value_counts()" ] }, { "cell_type": "code", "execution_count": 13, "id": "c4d97bea-7336-4528-ab40-e5e0434d653b", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/tmp/ipykernel_14354/1987262818.py:1: FutureWarning: Series.__getitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To access a value by position, use `ser.iloc[pos]`\n", " mean = df['linear_gradient_test'].describe()[1]\n", "/tmp/ipykernel_14354/1987262818.py:2: FutureWarning: Series.__getitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To access a value by position, use `ser.iloc[pos]`\n", " perc_25 = df['linear_gradient_test'].describe()[4]\n", "/tmp/ipykernel_14354/1987262818.py:3: FutureWarning: Series.__getitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To access a value by position, use `ser.iloc[pos]`\n", " median = df['linear_gradient_test'].describe()[5]\n", "/tmp/ipykernel_14354/1987262818.py:4: FutureWarning: Series.__getitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To access a value by position, use `ser.iloc[pos]`\n", " perc_75 = df['linear_gradient_test'].describe()[6]\n" ] } ], "source": [ "mean = df['linear_gradient_test'].describe()[1]\n", "perc_25 = df['linear_gradient_test'].describe()[4]\n", "median = df['linear_gradient_test'].describe()[5]\n", "perc_75 = df['linear_gradient_test'].describe()[6]" ] }, { "cell_type": "code", "execution_count": 14, "id": "aa78941c-ea77-4449-b032-d420eccb6de4", "metadata": {}, "outputs": [], "source": [ "conditions = [(df['linear_gradient_test']<= perc_25),\n", " ((df['linear_gradient_test']> perc_25) & (df['linear_gradient_test']<= median)),\n", " ((df['linear_gradient_test']> median) & (df['linear_gradient_test']<= perc_75)),\n", " (df['linear_gradient_test'] > perc_75)]\n", "\n", "choices = ['down_linear_trend',\n", " 'marginally_down_linear_trend',\n", " 'marginally_up_linear_trend',\n", " 'up_linear_trend']\n", "\n", "df['Slope_test_period'] = np.select(conditions, choices)\n" ] }, { "cell_type": "code", "execution_count": 15, "id": "611349df-49a6-48fd-a977-6d242faf1eb5", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Slope_test_period\n", "marginally_down_linear_trend 2242\n", "down_linear_trend 2047\n", "up_linear_trend 1944\n", "marginally_up_linear_trend 1895\n", "Name: count, dtype: int64" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df['Slope_test_period'].value_counts()" ] }, { "cell_type": "code", "execution_count": 16, "id": "c0b36427-fec8-4b0c-a1a4-023506f055c3", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Performance_test_periodSlope_test_periodcount
0doing_unnoticeably_bettermarginally_down_linear_trend1839
1doing_unnoticeably_betterdown_linear_trend1596
2doing_unnoticeably_bettermarginally_up_linear_trend1483
3doing_unnoticeably_betterup_linear_trend1178
4doing_much_betterup_linear_trend766
5doing_much_betterdown_linear_trend451
6doing_much_bettermarginally_up_linear_trend412
7doing_much_bettermarginally_down_linear_trend403
\n", "
" ], "text/plain": [ " Performance_test_period Slope_test_period count\n", "0 doing_unnoticeably_better marginally_down_linear_trend 1839\n", "1 doing_unnoticeably_better down_linear_trend 1596\n", "2 doing_unnoticeably_better marginally_up_linear_trend 1483\n", "3 doing_unnoticeably_better up_linear_trend 1178\n", "4 doing_much_better up_linear_trend 766\n", "5 doing_much_better down_linear_trend 451\n", "6 doing_much_better marginally_up_linear_trend 412\n", "7 doing_much_better marginally_down_linear_trend 403" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "unique_combos = df[['Performance_test_period', 'Slope_test_period']].value_counts().reset_index(name='count')\n", "unique_combos\n" ] }, { "cell_type": "code", "execution_count": 17, "id": "5ee610bb-b077-475e-8107-7aa8bdcd5ed6", "metadata": {}, "outputs": [], "source": [ "table_to_save = df[['ISRC','avg_streams_test','avg_streams_train','sum_forecast_errors', 'Performance_test_period', 'Slope_test_period']].copy()\n" ] }, { "cell_type": "code", "execution_count": 18, "id": "46d2c10c-82d1-4fcb-9b8b-92e7c7852764", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ISRCavg_streams_testavg_streams_trainsum_forecast_errorsPerformance_test_periodSlope_test_period
0ADA01081360217.57142919.676692-17.771doing_unnoticeably_bettermarginally_down_linear_trend
1AEA0D201272567.85714392.4360903.252doing_unnoticeably_bettermarginally_up_linear_trend
2AEA0D201517093.285714122.210526-28.158doing_unnoticeably_betterup_linear_trend
3AEA0D208347615.00000018.308271-9.450doing_unnoticeably_bettermarginally_down_linear_trend
4AEA0D213916456.28571471.44360950.687doing_much_betterdown_linear_trend
.....................
8123GBCEJ210000721.28571422.1729320.052doing_unnoticeably_bettermarginally_up_linear_trend
8124GBCEJ2100022297.571429363.706767136.905doing_much_betterdown_linear_trend
8125GBCEJ210002334.14285733.488722-119.738doing_unnoticeably_betterdown_linear_trend
8126GBCEJ210007084.57142994.894737-30.006doing_unnoticeably_betterup_linear_trend
8127GBCEJ210007161.85714372.7819556.393doing_unnoticeably_betterup_linear_trend
\n", "

8128 rows × 6 columns

\n", "
" ], "text/plain": [ " ISRC avg_streams_test avg_streams_train sum_forecast_errors \\\n", "0 ADA010813602 17.571429 19.676692 -17.771 \n", "1 AEA0D2012725 67.857143 92.436090 3.252 \n", "2 AEA0D2015170 93.285714 122.210526 -28.158 \n", "3 AEA0D2083476 15.000000 18.308271 -9.450 \n", "4 AEA0D2139164 56.285714 71.443609 50.687 \n", "... ... ... ... ... \n", "8123 GBCEJ2100007 21.285714 22.172932 0.052 \n", "8124 GBCEJ2100022 297.571429 363.706767 136.905 \n", "8125 GBCEJ2100023 34.142857 33.488722 -119.738 \n", "8126 GBCEJ2100070 84.571429 94.894737 -30.006 \n", "8127 GBCEJ2100071 61.857143 72.781955 6.393 \n", "\n", " Performance_test_period Slope_test_period \n", "0 doing_unnoticeably_better marginally_down_linear_trend \n", "1 doing_unnoticeably_better marginally_up_linear_trend \n", "2 doing_unnoticeably_better up_linear_trend \n", "3 doing_unnoticeably_better marginally_down_linear_trend \n", "4 doing_much_better down_linear_trend \n", "... ... ... \n", "8123 doing_unnoticeably_better marginally_up_linear_trend \n", "8124 doing_much_better down_linear_trend \n", "8125 doing_unnoticeably_better down_linear_trend \n", "8126 doing_unnoticeably_better up_linear_trend \n", "8127 doing_unnoticeably_better up_linear_trend \n", "\n", "[8128 rows x 6 columns]" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "table_to_save" ] }, { "cell_type": "code", "execution_count": 22, "id": "bd2790fa", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Table saved to S3 bucket: dev-cucumbers, with file name: eimpara/Moments_2023_batches/Manual_clustering_2023_subset_20231111-152249.csv\n" ] } ], "source": [ "def save_dataframe_s3(df):\n", " s3 = boto3.client('s3')\n", " bucket_name = 'dev-cucumbers'\n", " path = 'eimpara/Moments_2023_batches/'\n", " table_name = 'Manual_clustering_2023_subset'\n", " today = datetime.today().strftime('%Y%m%d-%H%M%S')\n", " filepath = path + table_name + '_{}.csv'.format(today)\n", " csv_buffer = df.to_csv(index=False).encode('utf-8')\n", " # Save the CSV file to S3\n", " s3.put_object(Body=csv_buffer, Bucket=bucket_name, Key=filepath)\n", " print(f\"Table saved to S3 bucket: {bucket_name}, with file name: {filepath}\")\n", "\n", "save_dataframe_s3(table_to_save)" ] }, { "cell_type": "code", "execution_count": null, "id": "52072159", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "conda_python3", "language": "python", "name": "conda_python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.10.13" } }, "nbformat": 4, "nbformat_minor": 5 }