{
  "metadata": {
    "kernelspec": {
      "display_name": "Jupyter Notebook",
      "name": "jupyter"
    }
  },
  "nbformat_minor": 5,
  "nbformat": 4,
  "cells": [
    {
      "cell_type": "code",
      "id": "ea3ea917-220c-4fd1-a6c7-b1bd512d26e6",
      "metadata": {
        "language": "sql",
        "resultVariableName": "dataframe_1"
      },
      "source": "%%sql -r dataframe_1\nuse database FACTS;\nuse schema DEV;",
      "outputs": [],
      "execution_count": null
    },
    {
      "id": "a5c2fc80-69ed-4866-bec6-8a5c61a75bf4",
      "cell_type": "code",
      "metadata": {
        "language": "python"
      },
      "source": "!pip install -U ipywidgets",
      "outputs": [],
      "execution_count": null
    },
    {
      "id": "f6ac4f2d-84f8-4588-8396-eaf288e70e33",
      "cell_type": "code",
      "metadata": {
        "language": "python"
      },
      "source": "# Imports \n# General python packages\nimport numpy as np\nimport pandas as pd\nimport datetime as dt\nimport matplotlib.pyplot as plt\n\nimport warnings\nwarnings.filterwarnings('ignore')\n\n# Import Snowpark\nfrom snowflake.snowpark.context import get_active_session\nsession = get_active_session()",
      "outputs": [],
      "execution_count": null
    },
    {
      "id": "a8d0a386-0cca-4bc8-b961-83c3b9a27685",
      "cell_type": "code",
      "metadata": {
        "language": "python"
      },
      "source": "isrc_geo_q = \"\"\"select *\n    from facts.qa.TADAS_HISTORICAL;\"\"\"\nisrc_geo_df = session.sql(isrc_geo_q).to_pandas()",
      "outputs": [],
      "execution_count": null
    },
    {
      "id": "e7c9d495-eedb-4543-bbf6-5c14b9b7b2e2",
      "cell_type": "code",
      "metadata": {
        "language": "python"
      },
      "source": "isrc_geo_df.info()",
      "outputs": [],
      "execution_count": null
    },
    {
      "id": "849f26ab-ef31-4a0a-9e67-4f5e5289f7a7",
      "cell_type": "code",
      "metadata": {
        "language": "python"
      },
      "source": "isrc_geo_df = isrc_geo_df.sort_values(['ISRC_CD', 'GEO_COUNTRY', 'REPORT_DATE']).reset_index(drop=True)\nisrc_geo_df['date_rank'] = isrc_geo_df.groupby(['ISRC_CD', 'GEO_COUNTRY'])['REPORT_DATE'].rank(method='dense').astype(int)\n\nisrc_geo_df['is_30'] = 0\nisrc_geo_df.loc[isrc_geo_df['CONSECUTIVE_TREND'] >= 30, 'is_30'] = 1\n\nisrc_geo_df['shift_30'] = isrc_geo_df.groupby(['ISRC_CD', 'GEO_COUNTRY'])['is_30'].shift(-29)\n\nisrc_geo_df['shift_30_34'] = isrc_geo_df.groupby(['ISRC_CD', 'GEO_COUNTRY'])['is_30'].shift(-30)\nfor s in [-31, -32, -33]:\n    shifted = isrc_geo_df.groupby(['ISRC_CD', 'GEO_COUNTRY'])['is_30'].shift(s)\n    isrc_geo_df.loc[isrc_geo_df['shift_30_34'] == 0, 'shift_30_34'] = shifted\n\nisrc_geo_df.loc[isrc_geo_df['date_rank'] <= 30, 'shift_30_34'] = np.nan\n\nisrc_geo_df['ground_truth'] = 0\nisrc_geo_df.loc[isrc_geo_df['shift_30_34'] > 0, 'ground_truth'] = 1",
      "outputs": [],
      "execution_count": null
    },
    {
      "id": "d4769c99-148a-46c5-98fc-27077a8d28b8",
      "cell_type": "code",
      "metadata": {
        "language": "python"
      },
      "source": "bins = np.arange(0, 1.1, 0.1)\nisrc_geo_df = isrc_geo_df[~isrc_geo_df['shift_30_34'].isnull()]\nisrc_geo_df['tadas_bucket'] = pd.cut(isrc_geo_df['TADAS_30DAY_SCORE'], bins=bins, right=False)\n\ncal = isrc_geo_df.groupby('tadas_bucket', observed=False).agg(\n    success_rate=('is_30', 'mean'),\n    count=('is_30', 'count')\n).reset_index()\n\ncal['bucket_mid'] = [b.mid for b in cal['tadas_bucket']]\n\nfig, ax1 = plt.subplots(figsize=(10, 6))\n\nax1.bar(cal['bucket_mid'], cal['count'], width=0.08, alpha=0.3, color='steelblue', label='Count')\nax1.set_ylabel('Count', color='steelblue')\nax1.tick_params(axis='y', labelcolor='steelblue')\n\nax2 = ax1.twinx()\nax2.plot(cal['bucket_mid'], cal['success_rate'], 'o-', color='darkorange', linewidth=2, label='Actual Success Rate')\nax2.plot([0, 1], [0, 1], '--', color='gray', label='Perfect Calibration')\nax2.set_ylabel('P(ground_truth = 1)', color='darkorange')\nax2.tick_params(axis='y', labelcolor='darkorange')\nax2.set_ylim(0, 1)\n\nax1.set_xlabel('TADAS_30 Bucket')\nax1.set_title('Calibration Curve: TADAS_30 vs Actual Success Rate')\nax1.set_xticks(np.arange(0.05, 1.05, 0.1))\nax1.set_xticklabels([f'{int(b*100-5)}-{int(b*100+5)}%' for b in np.arange(0.05, 1.05, 0.1)], rotation=45)\n\nlines1, labels1 = ax1.get_legend_handles_labels()\nlines2, labels2 = ax2.get_legend_handles_labels()\nax2.legend(lines1 + lines2, labels1 + labels2, loc='upper left')\n\nplt.tight_layout()\nplt.show()",
      "outputs": [],
      "execution_count": null
    },
    {
      "id": "a919c030-45d8-4654-a1fc-5f223eeb80b3",
      "cell_type": "code",
      "metadata": {
        "language": "python"
      },
      "source": "",
      "outputs": [],
      "execution_count": null
    }
  ]
}