{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "from dao.Database import Database\n",
    "from data_model import MysqlConfig, OddsJamOrder, OddsjamBet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def get_oddsjam_order_data_from_db()->List:\n",
    "    config_file_path = 'config\\mysql_config.json'\n",
    "    mysql_config = MysqlConfig.parse_file(config_file_path)\n",
    "    dao = Database(mysql_config)\n",
    "\n",
    "    select_query = \"SELECT * FROM bet.oddsjam_order where bet_status in ('won', 'lost');\"\n",
    "    raw_data_list = dao.fetchall(query=select_query)\n",
    "    order_data_list = [OddsJamOrder(**data).model_dump() for data in raw_data_list]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "simulation_data = get_oddsjam_order_data_from_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_df = pd.DataFrame(order_data_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_benefit(row):\n",
    "    home_or_away = row['home_or_away']\n",
    "    price = row[f'{home_or_away}_price'] / 100\n",
    "    if row['outcome'] == -1:\n",
    "        return -1\n",
    "    if price >= 0:\n",
    "        return price \n",
    "    else:\n",
    "        return 1 / abs(price) \n",
    "    \n",
    "db_df['outcome'] = db_df['bet_status'].apply(lambda x: 1 if x == 'won' else -1)\n",
    "db_df['benefit'] = db_df.apply(get_benefit, axis=1)\n",
    "db_df['date'] = db_df['start_timestamp'].apply(lambda x: datetime.fromtimestamp(x // 1000).strftime(\"%Y-%m-%d\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_odds(row):\n",
    "    home_or_away = row['home_or_away']\n",
    "    price = row[f'{home_or_away}_price'] / 100\n",
    "    if price >= 0:\n",
    "        return price\n",
    "    else:\n",
    "        return 1 / abs(price) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_df['odds2'] = db_df.apply(get_odds, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3.5232254542910955"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_df['odds2'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "bet_status\n",
       "lost    19316\n",
       "won      8249\n",
       "dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_df.groupby('bet_status').size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3.4551825216755248"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_df[db_df['bet_status'].isin(['won', 'lost'])]['benefit'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-1477.1127282023094"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_df[['home_price', 'away_price', 'home_or_away', 'bet_status', 'benefit']]['benefit']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    1780.000000\n",
       "mean        1.165759\n",
       "std         0.338662\n",
       "min         0.359712\n",
       "25%         0.909091\n",
       "50%         1.080000\n",
       "75%         1.360000\n",
       "max         4.000000\n",
       "Name: odds2, dtype: float64"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_df[(db_df['market_width'] <= 25) & (db_df['market_width'] >= 20)]['odds2'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "bet_status\n",
       "lost    1231\n",
       "won     1080\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_df[(db_df['market_width'] <= 25) & (db_df['market_width'] >= 20)].groupby(['bet_status']).size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.4764044943820225"
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "848 / (848+932)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0318921348314607"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "848 / (848+932) * (1.166 + 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    5902.000000\n",
       "mean        2.026751\n",
       "std         1.825651\n",
       "min         0.166667\n",
       "25%         1.020000\n",
       "50%         1.430000\n",
       "75%         2.200000\n",
       "max        25.000000\n",
       "Name: odds2, dtype: float64"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_df[db_df['bet_status'] == 'won']['odds2'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    13439.000000\n",
       "mean         4.082506\n",
       "std          3.955954\n",
       "min          0.235294\n",
       "25%          1.500000\n",
       "50%          2.700000\n",
       "75%          5.400000\n",
       "max         35.000000\n",
       "Name: odds2, dtype: float64"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_df[db_df['bet_status'] == 'lost']['odds2'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_df = pd.read_excel('PEV 3.11-10.26.xlsx', sheet_name='原始数据')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "data_df = db_df.copy()\n",
    "data_df = data_df[data_df['market_width'] <= 25]\n",
    "data_df = data_df[data_df['market_width'] >= 20]\n",
    "# data_df = data_df[data_df['selected_sportsbook'] == '1XBet']\n",
    "data_df['investment'] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "odds_df = data_df.groupby('date').agg({'odds2': 'mean'}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.plotly.v1+json": {
       "config": {
        "plotlyServerURL": "https://plot.ly"
       },
       "data": [
        {
         "name": "lost",
         "type": "bar",
         "x": [
          "2024-08-26T00:00:00",
          "2024-08-27T00:00:00",
          "2024-08-28T00:00:00",
          "2024-08-29T00:00:00",
          "2024-08-30T00:00:00",
          "2024-08-31T00:00:00",
          "2024-09-01T00:00:00",
          "2024-09-02T00:00:00",
          "2024-09-03T00:00:00",
          "2024-09-04T00:00:00",
          "2024-09-05T00:00:00"
         ],
         "y": [
          11,
          96,
          138,
          109,
          190,
          99,
          289,
          62,
          89,
          98,
          50
         ],
         "yaxis": "y"
        },
        {
         "name": "won",
         "type": "bar",
         "x": [
          "2024-08-26T00:00:00",
          "2024-08-27T00:00:00",
          "2024-08-28T00:00:00",
          "2024-08-29T00:00:00",
          "2024-08-30T00:00:00",
          "2024-08-31T00:00:00",
          "2024-09-01T00:00:00",
          "2024-09-02T00:00:00",
          "2024-09-03T00:00:00",
          "2024-09-04T00:00:00",
          "2024-09-05T00:00:00"
         ],
         "y": [
          12,
          113,
          94,
          79,
          178,
          88,
          284,
          59,
          56,
          80,
          37
         ],
         "yaxis": "y"
        },
        {
         "mode": "markers+lines",
         "name": "平均赔率",
         "type": "scatter",
         "x": [
          "2024-08-26T00:00:00",
          "2024-08-27T00:00:00",
          "2024-08-28T00:00:00",
          "2024-08-29T00:00:00",
          "2024-08-30T00:00:00",
          "2024-08-31T00:00:00",
          "2024-09-01T00:00:00",
          "2024-09-02T00:00:00",
          "2024-09-03T00:00:00",
          "2024-09-04T00:00:00",
          "2024-09-05T00:00:00"
         ],
         "y": [
          1.1965734109952426,
          1.2294773511597243,
          1.3513840696836157,
          1.209601147300343,
          1.1063211834713325,
          1.2904563185996685,
          1.0492181007177963,
          1.2070565768132386,
          1.154097538043856,
          1.1587947147909168,
          1.2470557464128864
         ],
         "yaxis": "y2"
        }
       ],
       "layout": {
        "barmode": "group",
        "legend": {
         "title": {
          "text": "Variables"
         }
        },
        "template": {
         "data": {
          "bar": [
           {
            "error_x": {
             "color": "#2a3f5f"
            },
            "error_y": {
             "color": "#2a3f5f"
            },
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "bar"
           }
          ],
          "barpolar": [
           {
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "barpolar"
           }
          ],
          "carpet": [
           {
            "aaxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "baxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "type": "carpet"
           }
          ],
          "choropleth": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "choropleth"
           }
          ],
          "contour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "contour"
           }
          ],
          "contourcarpet": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "contourcarpet"
           }
          ],
          "heatmap": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmap"
           }
          ],
          "heatmapgl": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmapgl"
           }
          ],
          "histogram": [
           {
            "marker": {
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "histogram"
           }
          ],
          "histogram2d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2d"
           }
          ],
          "histogram2dcontour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2dcontour"
           }
          ],
          "mesh3d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "mesh3d"
           }
          ],
          "parcoords": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "parcoords"
           }
          ],
          "pie": [
           {
            "automargin": true,
            "type": "pie"
           }
          ],
          "scatter": [
           {
            "fillpattern": {
             "fillmode": "overlay",
             "size": 10,
             "solidity": 0.2
            },
            "type": "scatter"
           }
          ],
          "scatter3d": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatter3d"
           }
          ],
          "scattercarpet": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattercarpet"
           }
          ],
          "scattergeo": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergeo"
           }
          ],
          "scattergl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergl"
           }
          ],
          "scattermapbox": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattermapbox"
           }
          ],
          "scatterpolar": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolar"
           }
          ],
          "scatterpolargl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolargl"
           }
          ],
          "scatterternary": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterternary"
           }
          ],
          "surface": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "surface"
           }
          ],
          "table": [
           {
            "cells": {
             "fill": {
              "color": "#EBF0F8"
             },
             "line": {
              "color": "white"
             }
            },
            "header": {
             "fill": {
              "color": "#C8D4E3"
             },
             "line": {
              "color": "white"
             }
            },
            "type": "table"
           }
          ]
         },
         "layout": {
          "annotationdefaults": {
           "arrowcolor": "#2a3f5f",
           "arrowhead": 0,
           "arrowwidth": 1
          },
          "autotypenumbers": "strict",
          "coloraxis": {
           "colorbar": {
            "outlinewidth": 0,
            "ticks": ""
           }
          },
          "colorscale": {
           "diverging": [
            [
             0,
             "#8e0152"
            ],
            [
             0.1,
             "#c51b7d"
            ],
            [
             0.2,
             "#de77ae"
            ],
            [
             0.3,
             "#f1b6da"
            ],
            [
             0.4,
             "#fde0ef"
            ],
            [
             0.5,
             "#f7f7f7"
            ],
            [
             0.6,
             "#e6f5d0"
            ],
            [
             0.7,
             "#b8e186"
            ],
            [
             0.8,
             "#7fbc41"
            ],
            [
             0.9,
             "#4d9221"
            ],
            [
             1,
             "#276419"
            ]
           ],
           "sequential": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ],
           "sequentialminus": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ]
          },
          "colorway": [
           "#636efa",
           "#EF553B",
           "#00cc96",
           "#ab63fa",
           "#FFA15A",
           "#19d3f3",
           "#FF6692",
           "#B6E880",
           "#FF97FF",
           "#FECB52"
          ],
          "font": {
           "color": "#2a3f5f"
          },
          "geo": {
           "bgcolor": "white",
           "lakecolor": "white",
           "landcolor": "#E5ECF6",
           "showlakes": true,
           "showland": true,
           "subunitcolor": "white"
          },
          "hoverlabel": {
           "align": "left"
          },
          "hovermode": "closest",
          "mapbox": {
           "style": "light"
          },
          "paper_bgcolor": "white",
          "plot_bgcolor": "#E5ECF6",
          "polar": {
           "angularaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "radialaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "scene": {
           "xaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "yaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "zaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           }
          },
          "shapedefaults": {
           "line": {
            "color": "#2a3f5f"
           }
          },
          "ternary": {
           "aaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "baxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "caxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "title": {
           "x": 0.05
          },
          "xaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          },
          "yaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          }
         }
        },
        "title": {
         "text": "Comparison of Two Variables Across Categories"
        },
        "xaxis": {
         "title": {
          "text": "日期"
         }
        },
        "yaxis": {
         "title": {
          "text": "金额"
         }
        },
        "yaxis2": {
         "overlaying": "y",
         "side": "right",
         "title": {
          "text": "收益率"
         }
        }
       }
      }
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import plotly.graph_objects as go\n",
    "\n",
    "\n",
    "\n",
    "df = pd.pivot_table(data_df,  index=['date'], columns=['bet_status'], aggfunc='size', fill_value=0).reset_index()\n",
    "df = df.sort_values(by='date')\n",
    "date_x = df['date'].tolist()\n",
    "# 创建Plotly图形对象\n",
    "fig = go.Figure()\n",
    "\n",
    "cols = df.columns[1:]\n",
    "for col in cols:\n",
    "    y_data = df[col].tolist()\n",
    "    # 添加第一个变量的数据\n",
    "    fig.add_trace(go.Bar(\n",
    "        x=date_x,\n",
    "        y=y_data,\n",
    "        name=col,\n",
    "        # marker_color='indianred'  # 设置颜色\n",
    "        yaxis='y1'\n",
    "    ))\n",
    "\n",
    "fig.add_trace(go.Scatter(\n",
    "    x=odds_df['date'],\n",
    "    y=odds_df['odds2'],\n",
    "    mode='markers+lines',\n",
    "    name='平均赔率',\n",
    "    yaxis='y2'\n",
    "))\n",
    "\n",
    "# 更新布局\n",
    "fig.update_layout(\n",
    "    barmode='group',  # 将柱状图设置为并排显示\n",
    "    title='Comparison of Two Variables Across Categories',\n",
    "    xaxis=dict(title='日期'),\n",
    "    yaxis=dict(title='金额'),\n",
    "    yaxis2=dict(title='收益率', overlaying='y', side='right'),\n",
    "    legend_title='Variables'\n",
    ")\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>bet_id</th>\n",
       "      <th>game_id</th>\n",
       "      <th>away_bet_name</th>\n",
       "      <th>home_bet_name</th>\n",
       "      <th>away_no_vig_price</th>\n",
       "      <th>home_no_vig_price</th>\n",
       "      <th>away_price</th>\n",
       "      <th>home_price</th>\n",
       "      <th>away_sportsbooks</th>\n",
       "      <th>...</th>\n",
       "      <th>sportsbook_orderid</th>\n",
       "      <th>home_or_away</th>\n",
       "      <th>start_timestamp</th>\n",
       "      <th>selected_sportsbook</th>\n",
       "      <th>bet_status</th>\n",
       "      <th>outcome</th>\n",
       "      <th>benefit</th>\n",
       "      <th>date</th>\n",
       "      <th>odds2</th>\n",
       "      <th>investment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>edge:10012-40335-2024-08-31:1st_half_team_tota...</td>\n",
       "      <td>edge:10012-40335-2024-08-31:1st_half_team_tota...</td>\n",
       "      <td>10012-40335-2024-08-31</td>\n",
       "      <td>Oud-Heverlee Leuven Under 0.5</td>\n",
       "      <td>Oud-Heverlee Leuven Over 0.5</td>\n",
       "      <td>102.85</td>\n",
       "      <td>-102.85</td>\n",
       "      <td>-110.0</td>\n",
       "      <td>-102.0</td>\n",
       "      <td>[FanDuel, DraftKings]</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>home</td>\n",
       "      <td>1725129900000</td>\n",
       "      <td>Bovada</td>\n",
       "      <td>won</td>\n",
       "      <td>1</td>\n",
       "      <td>0.980392</td>\n",
       "      <td>2024-09-01</td>\n",
       "      <td>0.980392</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>edge:10012-40335-2024-08-31:total_goals:over_2...</td>\n",
       "      <td>edge:10012-40335-2024-08-31:total_goals:over_2...</td>\n",
       "      <td>10012-40335-2024-08-31</td>\n",
       "      <td>Under 2.5</td>\n",
       "      <td>Over 2.5</td>\n",
       "      <td>-101.35</td>\n",
       "      <td>101.35</td>\n",
       "      <td>-102.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>[Bodog, Bovada]</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>home</td>\n",
       "      <td>1725129900000</td>\n",
       "      <td>BetOnline</td>\n",
       "      <td>lost</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>2024-09-01</td>\n",
       "      <td>1.050000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>edge:10035-41288-2024-08-31:total_goals:over_2...</td>\n",
       "      <td>edge:10035-41288-2024-08-31:total_goals:over_2...</td>\n",
       "      <td>10035-41288-2024-08-31</td>\n",
       "      <td>Under 2.5</td>\n",
       "      <td>Over 2.5</td>\n",
       "      <td>-121.90</td>\n",
       "      <td>121.90</td>\n",
       "      <td>-134.0</td>\n",
       "      <td>124.0</td>\n",
       "      <td>[FanDuel]</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>home</td>\n",
       "      <td>1725129000000</td>\n",
       "      <td>BetOnline</td>\n",
       "      <td>won</td>\n",
       "      <td>1</td>\n",
       "      <td>1.240000</td>\n",
       "      <td>2024-09-01</td>\n",
       "      <td>1.240000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>edge:10068-27381-24-35:point_spread:northweste...</td>\n",
       "      <td>edge:10068-27381-24-35:point_spread:northweste...</td>\n",
       "      <td>10068-27381-24-35</td>\n",
       "      <td>Tulsa -37.5</td>\n",
       "      <td>Northwestern State +37.5</td>\n",
       "      <td>100.40</td>\n",
       "      <td>-100.40</td>\n",
       "      <td>105.0</td>\n",
       "      <td>-110.0</td>\n",
       "      <td>[betPARX, BetRivers]</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>away</td>\n",
       "      <td>1724976000000</td>\n",
       "      <td>betPARX</td>\n",
       "      <td>lost</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>2024-08-30</td>\n",
       "      <td>1.050000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>edge:10068-27381-24-35:total_points:over_53_5:...</td>\n",
       "      <td>edge:10068-27381-24-35:total_points:over_53_5:...</td>\n",
       "      <td>10068-27381-24-35</td>\n",
       "      <td>Under 53.5</td>\n",
       "      <td>Over 53.5</td>\n",
       "      <td>132.39</td>\n",
       "      <td>-132.39</td>\n",
       "      <td>122.0</td>\n",
       "      <td>-130.0</td>\n",
       "      <td>[DraftKings]</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>home</td>\n",
       "      <td>1724976000000</td>\n",
       "      <td>betPARX</td>\n",
       "      <td>won</td>\n",
       "      <td>1</td>\n",
       "      <td>0.769231</td>\n",
       "      <td>2024-08-30</td>\n",
       "      <td>0.769231</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>edge:10068-27381-24-35:total_points:over_53:un...</td>\n",
       "      <td>edge:10068-27381-24-35:total_points:over_53:un...</td>\n",
       "      <td>10068-27381-24-35</td>\n",
       "      <td>Under 53</td>\n",
       "      <td>Over 53</td>\n",
       "      <td>109.70</td>\n",
       "      <td>-109.70</td>\n",
       "      <td>110.0</td>\n",
       "      <td>-110.0</td>\n",
       "      <td>[betPARX, BetRivers]</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>away</td>\n",
       "      <td>1724976000000</td>\n",
       "      <td>betPARX</td>\n",
       "      <td>lost</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>2024-08-30</td>\n",
       "      <td>1.100000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>edge:10068-27381-24-35:total_points:over_54_5:...</td>\n",
       "      <td>edge:10068-27381-24-35:total_points:over_54_5:...</td>\n",
       "      <td>10068-27381-24-35</td>\n",
       "      <td>Under 54.5</td>\n",
       "      <td>Over 54.5</td>\n",
       "      <td>114.29</td>\n",
       "      <td>-114.29</td>\n",
       "      <td>105.0</td>\n",
       "      <td>-114.0</td>\n",
       "      <td>[Fanatics]</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>home</td>\n",
       "      <td>1724976000000</td>\n",
       "      <td>betPARX</td>\n",
       "      <td>won</td>\n",
       "      <td>1</td>\n",
       "      <td>0.877193</td>\n",
       "      <td>2024-08-30</td>\n",
       "      <td>0.877193</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>edge:10068-27381-24-35:total_points:over_54:un...</td>\n",
       "      <td>edge:10068-27381-24-35:total_points:over_54:un...</td>\n",
       "      <td>10068-27381-24-35</td>\n",
       "      <td>Under 54</td>\n",
       "      <td>Over 54</td>\n",
       "      <td>122.41</td>\n",
       "      <td>-122.41</td>\n",
       "      <td>112.0</td>\n",
       "      <td>-122.0</td>\n",
       "      <td>[DraftKings]</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>home</td>\n",
       "      <td>1724976000000</td>\n",
       "      <td>BetRivers</td>\n",
       "      <td>won</td>\n",
       "      <td>1</td>\n",
       "      <td>0.819672</td>\n",
       "      <td>2024-08-30</td>\n",
       "      <td>0.819672</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>edge:10068-27381-24-35:total_points:over_55:un...</td>\n",
       "      <td>edge:10068-27381-24-35:total_points:over_55:un...</td>\n",
       "      <td>10068-27381-24-35</td>\n",
       "      <td>Under 55</td>\n",
       "      <td>Over 55</td>\n",
       "      <td>-112.29</td>\n",
       "      <td>112.29</td>\n",
       "      <td>-110.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>[BookMaker]</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>away</td>\n",
       "      <td>1724976000000</td>\n",
       "      <td>BookMaker</td>\n",
       "      <td>lost</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>2024-08-30</td>\n",
       "      <td>0.909091</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>edge:10068-27381-24-35:total_points:over_56:un...</td>\n",
       "      <td>edge:10068-27381-24-35:total_points:over_56:un...</td>\n",
       "      <td>10068-27381-24-35</td>\n",
       "      <td>Under 56</td>\n",
       "      <td>Over 56</td>\n",
       "      <td>-104.24</td>\n",
       "      <td>104.24</td>\n",
       "      <td>-112.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>[DraftKings]</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>home</td>\n",
       "      <td>1724976000000</td>\n",
       "      <td>betPARX</td>\n",
       "      <td>won</td>\n",
       "      <td>1</td>\n",
       "      <td>1.050000</td>\n",
       "      <td>2024-08-30</td>\n",
       "      <td>1.050000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 50 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   id  \\\n",
       "1   edge:10012-40335-2024-08-31:1st_half_team_tota...   \n",
       "9   edge:10012-40335-2024-08-31:total_goals:over_2...   \n",
       "15  edge:10035-41288-2024-08-31:total_goals:over_2...   \n",
       "19  edge:10068-27381-24-35:point_spread:northweste...   \n",
       "25  edge:10068-27381-24-35:total_points:over_53_5:...   \n",
       "26  edge:10068-27381-24-35:total_points:over_53:un...   \n",
       "27  edge:10068-27381-24-35:total_points:over_54_5:...   \n",
       "28  edge:10068-27381-24-35:total_points:over_54:un...   \n",
       "30  edge:10068-27381-24-35:total_points:over_55:un...   \n",
       "32  edge:10068-27381-24-35:total_points:over_56:un...   \n",
       "\n",
       "                                               bet_id                 game_id  \\\n",
       "1   edge:10012-40335-2024-08-31:1st_half_team_tota...  10012-40335-2024-08-31   \n",
       "9   edge:10012-40335-2024-08-31:total_goals:over_2...  10012-40335-2024-08-31   \n",
       "15  edge:10035-41288-2024-08-31:total_goals:over_2...  10035-41288-2024-08-31   \n",
       "19  edge:10068-27381-24-35:point_spread:northweste...       10068-27381-24-35   \n",
       "25  edge:10068-27381-24-35:total_points:over_53_5:...       10068-27381-24-35   \n",
       "26  edge:10068-27381-24-35:total_points:over_53:un...       10068-27381-24-35   \n",
       "27  edge:10068-27381-24-35:total_points:over_54_5:...       10068-27381-24-35   \n",
       "28  edge:10068-27381-24-35:total_points:over_54:un...       10068-27381-24-35   \n",
       "30  edge:10068-27381-24-35:total_points:over_55:un...       10068-27381-24-35   \n",
       "32  edge:10068-27381-24-35:total_points:over_56:un...       10068-27381-24-35   \n",
       "\n",
       "                    away_bet_name                 home_bet_name  \\\n",
       "1   Oud-Heverlee Leuven Under 0.5  Oud-Heverlee Leuven Over 0.5   \n",
       "9                       Under 2.5                      Over 2.5   \n",
       "15                      Under 2.5                      Over 2.5   \n",
       "19                    Tulsa -37.5      Northwestern State +37.5   \n",
       "25                     Under 53.5                     Over 53.5   \n",
       "26                       Under 53                       Over 53   \n",
       "27                     Under 54.5                     Over 54.5   \n",
       "28                       Under 54                       Over 54   \n",
       "30                       Under 55                       Over 55   \n",
       "32                       Under 56                       Over 56   \n",
       "\n",
       "    away_no_vig_price  home_no_vig_price  away_price  home_price  \\\n",
       "1              102.85            -102.85      -110.0      -102.0   \n",
       "9             -101.35             101.35      -102.0       105.0   \n",
       "15            -121.90             121.90      -134.0       124.0   \n",
       "19             100.40            -100.40       105.0      -110.0   \n",
       "25             132.39            -132.39       122.0      -130.0   \n",
       "26             109.70            -109.70       110.0      -110.0   \n",
       "27             114.29            -114.29       105.0      -114.0   \n",
       "28             122.41            -122.41       112.0      -122.0   \n",
       "30            -112.29             112.29      -110.0       110.0   \n",
       "32            -104.24             104.24      -112.0       105.0   \n",
       "\n",
       "         away_sportsbooks  ... sportsbook_orderid  home_or_away  \\\n",
       "1   [FanDuel, DraftKings]  ...               None          home   \n",
       "9         [Bodog, Bovada]  ...               None          home   \n",
       "15              [FanDuel]  ...               None          home   \n",
       "19   [betPARX, BetRivers]  ...               None          away   \n",
       "25           [DraftKings]  ...               None          home   \n",
       "26   [betPARX, BetRivers]  ...               None          away   \n",
       "27             [Fanatics]  ...               None          home   \n",
       "28           [DraftKings]  ...               None          home   \n",
       "30            [BookMaker]  ...               None          away   \n",
       "32           [DraftKings]  ...               None          home   \n",
       "\n",
       "   start_timestamp selected_sportsbook bet_status outcome   benefit  \\\n",
       "1    1725129900000              Bovada        won       1  0.980392   \n",
       "9    1725129900000           BetOnline       lost      -1 -1.000000   \n",
       "15   1725129000000           BetOnline        won       1  1.240000   \n",
       "19   1724976000000             betPARX       lost      -1 -1.000000   \n",
       "25   1724976000000             betPARX        won       1  0.769231   \n",
       "26   1724976000000             betPARX       lost      -1 -1.000000   \n",
       "27   1724976000000             betPARX        won       1  0.877193   \n",
       "28   1724976000000           BetRivers        won       1  0.819672   \n",
       "30   1724976000000           BookMaker       lost      -1 -1.000000   \n",
       "32   1724976000000             betPARX        won       1  1.050000   \n",
       "\n",
       "          date     odds2 investment  \n",
       "1   2024-09-01  0.980392          1  \n",
       "9   2024-09-01  1.050000          1  \n",
       "15  2024-09-01  1.240000          1  \n",
       "19  2024-08-30  1.050000          1  \n",
       "25  2024-08-30  0.769231          1  \n",
       "26  2024-08-30  1.100000          1  \n",
       "27  2024-08-30  0.877193          1  \n",
       "28  2024-08-30  0.819672          1  \n",
       "30  2024-08-30  0.909091          1  \n",
       "32  2024-08-30  1.050000          1  \n",
       "\n",
       "[10 rows x 50 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "status       date  active  inactive\n",
      "0      2024-01-01       2         1\n",
      "1      2024-01-02       1         1\n",
      "2      2024-01-03       0         1\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 示例数据\n",
    "data = {\n",
    "    'date': ['2024-01-01', '2024-01-01', '2024-01-01', '2024-01-02', '2024-01-02', '2024-01-03'],\n",
    "    'status': ['active', 'inactive', 'active', 'active', 'inactive', 'inactive']\n",
    "}\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# 将日期列转换为日期类型\n",
    "df['date'] = pd.to_datetime(df['date'])\n",
    "\n",
    "# 使用 pivot_table 进行聚合\n",
    "result = pd.pivot_table(df, values='status', index=['date'], columns=['status'], aggfunc='size', fill_value=0).reset_index()\n",
    "\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "bet_status       date  lost  won\n",
      "0          2024-08-26    11   12\n",
      "1          2024-08-27    96  113\n",
      "2          2024-08-28   138   94\n",
      "3          2024-08-29   109   79\n",
      "4          2024-08-30   190  178\n",
      "5          2024-08-31    99   88\n",
      "6          2024-09-01   289  284\n",
      "7          2024-09-02    62   59\n",
      "8          2024-09-03    89   56\n",
      "9          2024-09-04    98   80\n",
      "10         2024-09-05    50   37\n",
      "Index(['date', 'lost', 'won'], dtype='object', name='bet_status')\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "\n",
    "\n",
    "# 使用 pivot_table 进行聚合\n",
    "result = pd.pivot_table(data_df,  index=['date'], columns=['bet_status'], aggfunc='size', fill_value=0).reset_index()\n",
    "\n",
    "print(result)\n",
    "print(result.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-01-01</td>\n",
       "      <td>active</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-01-01</td>\n",
       "      <td>inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-01-01</td>\n",
       "      <td>active</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-01-02</td>\n",
       "      <td>active</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-01-02</td>\n",
       "      <td>inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2024-01-03</td>\n",
       "      <td>inactive</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date    status\n",
       "0 2024-01-01    active\n",
       "1 2024-01-01  inactive\n",
       "2 2024-01-01    active\n",
       "3 2024-01-02    active\n",
       "4 2024-01-02  inactive\n",
       "5 2024-01-03  inactive"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "年化夏普率: -1.4002737338136018\n",
      "ROI: -0.011875712320100193\n"
     ]
    }
   ],
   "source": [
    "def clac_closing_balance(day_benefit_list, pre_balance=1000, pre_benefit=0):\n",
    "    closing_balance_list = []\n",
    "    for i, benefit in enumerate(day_benefit_list):\n",
    "        closing_balance = pre_balance + pre_benefit / 3 + benefit * 2 / 3\n",
    "        closing_balance_list.append(closing_balance)\n",
    "        pre_balance = closing_balance\n",
    "        pre_benefit = benefit\n",
    "    return closing_balance_list\n",
    "\n",
    "def calc_in_transit_funds_ratio(daily_investment_list, closing_balance_list, start_closing_balance=1000):\n",
    "    assert len(daily_investment_list) == len(closing_balance_list)\n",
    "    ratio_list = []\n",
    "    for i, daily_investment in enumerate(daily_investment_list):\n",
    "        if i == 0:\n",
    "            ratio = daily_investment / start_closing_balance\n",
    "        else:\n",
    "            ratio = daily_investment / closing_balance_list[i-1]\n",
    "        ratio_list.append(ratio)\n",
    "    return ratio_list\n",
    "\n",
    "\n",
    "init_balance = 1000\n",
    "res_df = data_df.groupby('date').agg({'investment':'sum', 'benefit':'sum'}).rename(columns={'investment':'当日投入', 'benefit':'日收益'}).reset_index()\n",
    "res_df['日收益率'] = res_df['日收益'] / res_df['当日投入']\n",
    "res_df['累计收益'] = res_df['日收益'].cumsum()\n",
    "res_df['累计投入'] = res_df['当日投入'].cumsum()\n",
    "res_df['累计收益率'] = res_df['累计收益'] / res_df['累计投入']\n",
    "\n",
    "day_benefit_list = res_df['日收益'].tolist()\n",
    "closing_balance_list = clac_closing_balance(day_benefit_list=day_benefit_list, pre_balance=init_balance)\n",
    "res_df['日末余额(1.6天结算)'] = closing_balance_list\n",
    "\n",
    "daily_investment_list = res_df['当日投入'].tolist()\n",
    "res_df['在途资金比例'] = calc_in_transit_funds_ratio(daily_investment_list=daily_investment_list, closing_balance_list=closing_balance_list, start_closing_balance=init_balance)\n",
    "annualized_sharpe_ratio = res_df['日收益'].sum() / init_balance / res_df['日收益率'].std() * ((365 / len(res_df))**0.5)\n",
    "print(f'年化夏普率: {annualized_sharpe_ratio}')\n",
    "roi = res_df['日收益'].sum() / res_df['当日投入'].sum()\n",
    "print(f'ROI: {roi}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.plotly.v1+json": {
       "config": {
        "plotlyServerURL": "https://plot.ly"
       },
       "data": [
        {
         "name": "日末余额",
         "type": "bar",
         "x": [
          "2024-08-26T00:00:00",
          "2024-08-27T00:00:00",
          "2024-08-28T00:00:00",
          "2024-08-29T00:00:00",
          "2024-08-30T00:00:00",
          "2024-08-31T00:00:00",
          "2024-09-01T00:00:00",
          "2024-09-02T00:00:00",
          "2024-09-03T00:00:00",
          "2024-09-04T00:00:00",
          "2024-09-05T00:00:00"
         ],
         "y": [
          1002.6656885456886,
          1029.320085562455,
          1031.6353596927408,
          1014.877157613626,
          1004.9262841780602,
          1010.5006139053265,
          1012.8869095027875,
          1015.040231362553,
          997.2052265455578,
          980.6588565360646,
          974.1046903384964
         ],
         "yaxis": "y"
        },
        {
         "mode": "markers+lines",
         "name": "日收益率",
         "type": "scatter",
         "x": [
          "2024-08-26T00:00:00",
          "2024-08-27T00:00:00",
          "2024-08-28T00:00:00",
          "2024-08-29T00:00:00",
          "2024-08-30T00:00:00",
          "2024-08-31T00:00:00",
          "2024-09-01T00:00:00",
          "2024-09-02T00:00:00",
          "2024-09-03T00:00:00",
          "2024-09-04T00:00:00",
          "2024-09-05T00:00:00"
         ],
         "y": [
          0.17384925297968776,
          0.1817336321334136,
          -0.06688902311428041,
          -0.09243710870965964,
          -0.01694897264848009,
          0.06139093881401034,
          -0.0037706970033483623,
          0.035622210587666514,
          -0.19936311011066554,
          -0.058234435568632774,
          -0.053429707249929806
         ],
         "yaxis": "y2"
        },
        {
         "mode": "markers+lines",
         "name": "累计收益率",
         "type": "scatter",
         "x": [
          "2024-08-26T00:00:00",
          "2024-08-27T00:00:00",
          "2024-08-28T00:00:00",
          "2024-08-29T00:00:00",
          "2024-08-30T00:00:00",
          "2024-08-31T00:00:00",
          "2024-09-01T00:00:00",
          "2024-09-02T00:00:00",
          "2024-09-03T00:00:00",
          "2024-09-04T00:00:00",
          "2024-09-05T00:00:00"
         ],
         "y": [
          0.17384925297968776,
          0.18095199109662177,
          0.05703148399117068,
          0.013933178120379116,
          0.0027913825488691288,
          0.01187018704065157,
          0.00683522830064485,
          0.008667540166362694,
          -0.006075589985235103,
          -0.010250173849373946,
          -0.011875712320100193
         ],
         "yaxis": "y2"
        },
        {
         "mode": "markers+lines",
         "name": "在途资金比例",
         "type": "scatter",
         "x": [
          "2024-08-26T00:00:00",
          "2024-08-27T00:00:00",
          "2024-08-28T00:00:00",
          "2024-08-29T00:00:00",
          "2024-08-30T00:00:00",
          "2024-08-31T00:00:00",
          "2024-09-01T00:00:00",
          "2024-09-02T00:00:00",
          "2024-09-03T00:00:00",
          "2024-09-04T00:00:00",
          "2024-09-05T00:00:00"
         ],
         "y": [
          0.023,
          0.20844435227771982,
          0.22539150187983306,
          0.1822349323660187,
          0.3626054613992025,
          0.1860833007795684,
          0.5670456723281954,
          0.11946052305029518,
          0.1428514806800882,
          0.1784988638864379,
          0.08871586629758899
         ],
         "yaxis": "y2"
        }
       ],
       "layout": {
        "template": {
         "data": {
          "bar": [
           {
            "error_x": {
             "color": "#2a3f5f"
            },
            "error_y": {
             "color": "#2a3f5f"
            },
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "bar"
           }
          ],
          "barpolar": [
           {
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "barpolar"
           }
          ],
          "carpet": [
           {
            "aaxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "baxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "type": "carpet"
           }
          ],
          "choropleth": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "choropleth"
           }
          ],
          "contour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "contour"
           }
          ],
          "contourcarpet": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "contourcarpet"
           }
          ],
          "heatmap": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmap"
           }
          ],
          "heatmapgl": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmapgl"
           }
          ],
          "histogram": [
           {
            "marker": {
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "histogram"
           }
          ],
          "histogram2d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2d"
           }
          ],
          "histogram2dcontour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2dcontour"
           }
          ],
          "mesh3d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "mesh3d"
           }
          ],
          "parcoords": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "parcoords"
           }
          ],
          "pie": [
           {
            "automargin": true,
            "type": "pie"
           }
          ],
          "scatter": [
           {
            "fillpattern": {
             "fillmode": "overlay",
             "size": 10,
             "solidity": 0.2
            },
            "type": "scatter"
           }
          ],
          "scatter3d": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatter3d"
           }
          ],
          "scattercarpet": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattercarpet"
           }
          ],
          "scattergeo": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergeo"
           }
          ],
          "scattergl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergl"
           }
          ],
          "scattermapbox": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattermapbox"
           }
          ],
          "scatterpolar": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolar"
           }
          ],
          "scatterpolargl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolargl"
           }
          ],
          "scatterternary": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterternary"
           }
          ],
          "surface": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "surface"
           }
          ],
          "table": [
           {
            "cells": {
             "fill": {
              "color": "#EBF0F8"
             },
             "line": {
              "color": "white"
             }
            },
            "header": {
             "fill": {
              "color": "#C8D4E3"
             },
             "line": {
              "color": "white"
             }
            },
            "type": "table"
           }
          ]
         },
         "layout": {
          "annotationdefaults": {
           "arrowcolor": "#2a3f5f",
           "arrowhead": 0,
           "arrowwidth": 1
          },
          "autotypenumbers": "strict",
          "coloraxis": {
           "colorbar": {
            "outlinewidth": 0,
            "ticks": ""
           }
          },
          "colorscale": {
           "diverging": [
            [
             0,
             "#8e0152"
            ],
            [
             0.1,
             "#c51b7d"
            ],
            [
             0.2,
             "#de77ae"
            ],
            [
             0.3,
             "#f1b6da"
            ],
            [
             0.4,
             "#fde0ef"
            ],
            [
             0.5,
             "#f7f7f7"
            ],
            [
             0.6,
             "#e6f5d0"
            ],
            [
             0.7,
             "#b8e186"
            ],
            [
             0.8,
             "#7fbc41"
            ],
            [
             0.9,
             "#4d9221"
            ],
            [
             1,
             "#276419"
            ]
           ],
           "sequential": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ],
           "sequentialminus": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ]
          },
          "colorway": [
           "#636efa",
           "#EF553B",
           "#00cc96",
           "#ab63fa",
           "#FFA15A",
           "#19d3f3",
           "#FF6692",
           "#B6E880",
           "#FF97FF",
           "#FECB52"
          ],
          "font": {
           "color": "#2a3f5f"
          },
          "geo": {
           "bgcolor": "white",
           "lakecolor": "white",
           "landcolor": "#E5ECF6",
           "showlakes": true,
           "showland": true,
           "subunitcolor": "white"
          },
          "hoverlabel": {
           "align": "left"
          },
          "hovermode": "closest",
          "mapbox": {
           "style": "light"
          },
          "paper_bgcolor": "white",
          "plot_bgcolor": "#E5ECF6",
          "polar": {
           "angularaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "radialaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "scene": {
           "xaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "yaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "zaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           }
          },
          "shapedefaults": {
           "line": {
            "color": "#2a3f5f"
           }
          },
          "ternary": {
           "aaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "baxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "caxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "title": {
           "x": 0.05
          },
          "xaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          },
          "yaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          }
         }
        },
        "title": {
         "text": "日末余额(1.6天结算) 和 累计收益率 折线图"
        },
        "xaxis": {
         "title": {
          "text": "日期"
         }
        },
        "yaxis": {
         "title": {
          "text": "金额"
         }
        },
        "yaxis2": {
         "overlaying": "y",
         "side": "right",
         "tickformat": ".1%",
         "title": {
          "text": "收益率"
         }
        }
       }
      }
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import plotly.graph_objects as go\n",
    "from plotly.subplots import make_subplots\n",
    "\n",
    "\n",
    "\n",
    "res_df['日期'] = pd.to_datetime(res_df['date'])\n",
    "\n",
    "fig = go.Figure()\n",
    "fig.add_trace(go.Bar(\n",
    "    x=res_df['日期'],\n",
    "    y=res_df['日末余额(1.6天结算)'],\n",
    "    name='日末余额',\n",
    "    yaxis='y1'))\n",
    "\n",
    "for col in ['日收益率', '累计收益率', '在途资金比例']:\n",
    "    fig.add_trace(go.Scatter(\n",
    "        x=res_df['日期'],\n",
    "        y=res_df[col],\n",
    "        mode='markers+lines',\n",
    "        name=col,\n",
    "        yaxis='y2'))\n",
    "\n",
    "\n",
    "\n",
    "fig.update_layout(\n",
    "    title='日末余额(1.6天结算) 和 累计收益率 折线图',\n",
    "    xaxis=dict(title='日期'),\n",
    "    yaxis=dict(title='金额'),\n",
    "    yaxis2=dict(title='收益率', overlaying='y', side='right', tickformat='.1%'),\n",
    ")\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 基于market width 进行分层， 然后计算年华夏普率值， 进行资金配比"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>outcome</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-1.0</td>\n",
       "      <td>39838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.0</td>\n",
       "      <td>37469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>2804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-0.5</td>\n",
       "      <td>164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.5</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   outcome  count\n",
       "0     -1.0  39838\n",
       "4      1.0  37469\n",
       "2      0.0   2804\n",
       "1     -0.5    164\n",
       "3      0.5     91"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_df.groupby('outcome').agg({'away_team': 'count'}).rename(columns={'away_team': 'count'}).reset_index().sort_values(by='count', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.1811803969722776"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_df['odds EU'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "37.78343433288728"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "1.01**365"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}