{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Team and City Analysis for \"Google Cloud & NCAA® ML Competition 2020-NCAAW\"\nIn this notebook, I will dive into the relationship between the teams and the cities which are the games played. \n\nMy questions are:\n1. Which teams are favorite on championships? What about the unsuccessful ones?\n2. Which team has an advantage or disadvantage on which city?\n\nLet's start with the data we have."},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load in \n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# Any results you write to the current directory are saved as output.\n\n# Ignoring future warnings.\n\nimport warnings\nwarnings.simplefilter(action='ignore', category=FutureWarning)\n\n# Importing Plotly packages.\n\nimport plotly.graph_objects as go\nfrom plotly.subplots import make_subplots\nimport plotly.express as px","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"I will use \"Cities.csv\", \"WGameCities.csv\", \"WTeams.csv\" files for this kernel."},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"cities = pd.read_csv(\"/kaggle/input/google-cloud-ncaa-march-madness-2020-division-1-womens-tournament/WDataFiles_Stage1/Cities.csv\")\nprint(cities.info())\nprint(cities.head())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\"Cities.csv\" provides a master list of cities that have been locations for games played. I will use it for extract city names from the CityIDs.\n\n- CityID - a four-digit ID number uniquely identifying a city.\n- City - the text name of the city."},{"metadata":{"trusted":true},"cell_type":"code","source":"wgamecities = pd.read_csv(\"/kaggle/input/google-cloud-ncaa-march-madness-2020-division-1-womens-tournament/WDataFiles_Stage1/WGameCities.csv\")\nprint(wgamecities.info())\nprint(wgamecities.head())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\"WGameCities.csv\" identifies all games, starting with the 2010 season, along with the city that the game was played in. Games from the regular season and the NCAA® tourney are all listed together. The CityID is present in more than 98% of games since the 2010 season.\n\nI will use this file as main file.\n\n- Season, DayNum, WTeamID, LTeamID - these four columns are sufficient to uniquely identify each game.\n- CityID - the ID of the city where the game was played, as specified by the CityID column in the Cities.csv file."},{"metadata":{"trusted":true},"cell_type":"code","source":"wteams = pd.read_csv(\"/kaggle/input/google-cloud-ncaa-march-madness-2020-division-1-womens-tournament/WDataFiles_Stage1/WTeams.csv\")\nprint(wteams.info())\nprint(wteams.head())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\"WTeams.csv\" identifies the different college teams present in the dataset. I will use it for extract team names from the TeamIDs.\n\n- TeamID - a 4 digit id number, from 3000-3999, uniquely identifying each NCAA® women's team.\n- TeamName - a compact spelling of the team's college name, 16 characters or fewer.\n\nNow we are ready to go and prepare our main data."},{"metadata":{"trusted":true},"cell_type":"code","source":"data = wgamecities\ndata[\"WTeamName\"] = [wteams.at[wteams[wteams.TeamID == i].index.item(), \"TeamName\"] for i in data.WTeamID]\ndata[\"LTeamName\"] = [wteams.at[wteams[wteams.TeamID == i].index.item(), \"TeamName\"] for i in data.LTeamID]\ndata[\"City\"] = [cities.at[cities[cities.CityID == i].index.item(), \"City\"] for i in data.CityID]\ndata[\"State\"] = [cities.at[cities[cities.CityID == i].index.item(), \"State\"] for i in data.CityID]\nprint(data.info())\nprint(data.head())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Before answering our questions, there is one more thing to do. I need to create another dataframe that is includes \"Number of Wins\", \"Number of Loses\" and \"Winning Percentage\" for each team. After that we are ready to go with the questions."},{"metadata":{"trusted":true},"cell_type":"code","source":"teamsWL = []\nfor team in data.WTeamName.unique():\n    wins = len(data[data.WTeamName == team])\n    loses = len(data[data.LTeamName == team])\n    teamsWL.append([team, wins, loses])\nteamsWL = pd.DataFrame(teamsWL, columns=[\"TeamName\", \"NumberOfWins\", \"NumberOfLoses\"])\nteamsWL[\"WinningPercentage\"] = teamsWL.NumberOfWins / (teamsWL.NumberOfWins+teamsWL.NumberOfLoses)\nteamsWL.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# 10 Most Winning Teams\nLet's see 10 most winning teams with a bar plot."},{"metadata":{"trusted":true},"cell_type":"code","source":"teams=teamsWL.sort_values(by=[\"NumberOfWins\"], ascending=False)[\"TeamName\"][:10]\nwins=teamsWL.sort_values(by=[\"NumberOfWins\"], ascending=False)[\"NumberOfWins\"][:10]\nloses=teamsWL.sort_values(by=[\"NumberOfWins\"], ascending=False)[\"NumberOfLoses\"][:10]\n\nfig = go.Figure(data=[\n    go.Bar(name='NumberOfWins', x=teams, y=wins, marker_color=\"#10316b\"),\n    go.Bar(name='NumberOfLoses', x=teams, y=loses, marker_color=\"#801336\")\n])\n\nfig.update_layout(barmode='stack',\n                  title=\"10 Most Winning Teams\",\n                  xaxis_title=\"Teams\",\n                  yaxis_title=\"Played Games\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"When look at the bar plot, I detected some misunderstanding. I'm accepting the teams successfulness with their total wins but what about the loses? For instance, we can assume that the teams \"Gonzaga\" and \"Louisville\" have same number of wins but there is a difference with their number of loses. So I should look for their winning percentages."},{"metadata":{"trusted":true},"cell_type":"code","source":"teams=teamsWL.sort_values(by=[\"WinningPercentage\"], ascending=False)[\"TeamName\"][:10]\nwins=teamsWL.sort_values(by=[\"WinningPercentage\"], ascending=False)[\"NumberOfWins\"][:10]\nloses=teamsWL.sort_values(by=[\"WinningPercentage\"], ascending=False)[\"NumberOfLoses\"][:10]\nwin_lose=teamsWL.sort_values(by=[\"WinningPercentage\"], ascending=False)[\"WinningPercentage\"][:10]\n\nfig = go.Figure(data=[\n    go.Bar(name='NumberOfWins', x=teams, y=wins, marker_color=\"#10316b\", text=win_lose),\n    go.Bar(name='NumberOfLoses', x=teams, y=loses, marker_color=\"#801336\", text=win_lose)\n])\n\nfig.update_layout(barmode='stack',\n                  title=\"10 Most Successful Teams by Winning Percentage\",\n                  xaxis_title=\"Teams\",\n                  yaxis_title=\"Played Games\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"10 most successful teams has changed. We can say that, \"Connecticut\", \"Baylor\" and \"Notre Dame\" have more than 90% winning rate and they have dominated the league for the past 10 years. So what about the last year?"},{"metadata":{"trusted":true},"cell_type":"code","source":"teamsWL2019 = []\nfor team in data.WTeamName.unique():\n    wins = len(data[(data.WTeamName == team) & (data.Season == 2019)])\n    loses = len(data[(data.LTeamName == team) & (data.Season == 2019)])\n    teamsWL2019.append([team, wins, loses])\nteamsWL2019 = pd.DataFrame(teamsWL2019, columns=[\"TeamName\", \"NumberOfWins\", \"NumberOfLoses\"])\nteamsWL2019[\"WinningPercentage\"] = teamsWL2019.NumberOfWins / (teamsWL2019.NumberOfWins+teamsWL2019.NumberOfLoses)\nteamsWL2019.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"teams=teamsWL2019.sort_values(by=[\"WinningPercentage\"], ascending=False)[\"TeamName\"][:10]\nwins=teamsWL2019.sort_values(by=[\"WinningPercentage\"], ascending=False)[\"NumberOfWins\"][:10]\nloses=teamsWL2019.sort_values(by=[\"WinningPercentage\"], ascending=False)[\"NumberOfLoses\"][:10]\nwin_lose=teamsWL2019.sort_values(by=[\"WinningPercentage\"], ascending=False)[\"WinningPercentage\"][:10]\n\nfig = go.Figure(data=[\n    go.Bar(name='NumberOfWins', x=teams, y=wins, marker_color=\"#10316b\", text=win_lose),\n    go.Bar(name='NumberOfLoses', x=teams, y=loses, marker_color=\"#801336\", text=win_lose)\n])\n\nfig.update_layout(barmode='stack',\n                  title=\"10 Most Successful Teams by Winning Percentage at 2019\",\n                  xaxis_title=\"Teams\",\n                  yaxis_title=\"Played Games\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# 10 Most Losing Teams"},{"metadata":{"trusted":true},"cell_type":"code","source":"teams=teamsWL.sort_values(by=[\"WinningPercentage\"])[\"TeamName\"][:10]\nwins=teamsWL.sort_values(by=[\"WinningPercentage\"])[\"NumberOfWins\"][:10]\nloses=teamsWL.sort_values(by=[\"WinningPercentage\"])[\"NumberOfLoses\"][:10]\nwin_lose=teamsWL.sort_values(by=[\"WinningPercentage\"])[\"WinningPercentage\"][:10]\n\nfig = go.Figure(data=[\n    go.Bar(name='NumberOfLoses', x=teams, y=loses, marker_color=\"#801336\", text=win_lose),\n    go.Bar(name='NumberOfWins', x=teams, y=wins, marker_color=\"#10316b\", text=win_lose),\n])\n\nfig.update_layout(barmode='stack',\n                  title=\"10 Most Unsuccessful Teams by Winning Percentage\",\n                  xaxis_title=\"Teams\",\n                  yaxis_title=\"Played Games\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"teams=teamsWL2019.sort_values(by=[\"WinningPercentage\"])[\"TeamName\"][:10]\nwins=teamsWL2019.sort_values(by=[\"WinningPercentage\"])[\"NumberOfWins\"][:10]\nloses=teamsWL2019.sort_values(by=[\"WinningPercentage\"])[\"NumberOfLoses\"][:10]\nwin_lose=teamsWL2019.sort_values(by=[\"WinningPercentage\"])[\"WinningPercentage\"][:10]\n\nfig = go.Figure(data=[\n    go.Bar(name='NumberOfLoses', x=teams, y=loses, marker_color=\"#801336\", text=win_lose),\n    go.Bar(name='NumberOfWins', x=teams, y=wins, marker_color=\"#10316b\", text=win_lose),\n])\n\nfig.update_layout(barmode='stack',\n                  title=\"10 Most Unsuccessful Teams by Winning Percentage at 2019\",\n                  xaxis_title=\"Teams\",\n                  yaxis_title=\"Played Games\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Advantaged and Disadvantaged Cities For Top Teams\nI will show top team's advantaged and disadvantaged cities with piecharts below. First, let's prepare our data."},{"metadata":{"trusted":true},"cell_type":"code","source":"teamWinningCities = []\nfor team in teamsWL.sort_values(by=[\"WinningPercentage\"], ascending=False)[\"TeamName\"][:10]:\n    for city in data[data.WTeamName == team].City.unique():\n        winNumber = len(data[(data.WTeamName == team) & (data.City == city)])\n        loseNumber = len(data[(data.LTeamName == team) & (data.City == city)])\n        teamWinningCities.append([team, city, winNumber, loseNumber])\nteamWinningCities = pd.DataFrame(teamWinningCities, columns=[\"TeamName\", \"City\", \"NumberOfWins\", \"NumberOfLoses\"])\nteamWinningCities[\"WinningPercentage\"] = teamWinningCities.NumberOfWins / (teamWinningCities.NumberOfWins+teamWinningCities.NumberOfLoses)\nteamWinningCities.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def plotSubPies(team):\n    df1 = teamWinningCities[teamWinningCities.TeamName == team].sort_values(by=[\"TeamName\", \"NumberOfWins\", \"WinningPercentage\"], ascending=False)\n    label1 = df1.City[:5]\n    value1 = df1.NumberOfWins[:5]\n    color1 = [\"#052F5F\", \"#003777\", \"#0650A5\", \"#0774F2\", \"#7AA5D6\"]\n    \n    df2 = teamWinningCities[teamWinningCities.TeamName == team].sort_values(by=[\"TeamName\", \"NumberOfLoses\", \"WinningPercentage\"], ascending=False)\n    label2 = df2.City[:5]\n    value2 = df2.NumberOfLoses[:5]\n    color2 =[\"#580C1F\", \"#74121D\", \"#A7333F\", \"#A51C30\", \"#C52233\"]\n    \n    fig = make_subplots(rows=1, cols=2, specs=[[{'type':'domain'}, {'type':'domain'}]],\n                        subplot_titles=[team+\"'s Most Winning Cities\", team+\"'s Most Losing Cities\"])\n    fig.add_trace(go.Pie(labels=label1, values=value1, name=team+\"'s Most Winning Cities\", marker_colors=color1),\n                  1, 1)\n    fig.add_trace(go.Pie(labels=label2, values=value2, name=team+\"'s Most Losing Cities\", marker_colors=color2),\n                  1, 2)\n\n    fig.update_traces(hoverinfo=\"label+percent\")\n\n    fig.update_layout(\n        title_text=team+\"'s Statistics for Cities\")\n    fig.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(\"Top 10 Teams:\")\nfor i, team in enumerate(teamsWL.sort_values(by=[\"WinningPercentage\"], ascending=False)[\"TeamName\"][:10]):\n    print(\"{}. {}\".format(i+1, team))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plotSubPies(\"Connecticut\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plotSubPies(\"Baylor\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plotSubPies(\"Notre Dame\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plotSubPies(\"Stanford\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plotSubPies(\"WI Green Bay\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plotSubPies(\"FL Gulf Coast\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plotSubPies(\"Maryland\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plotSubPies(\"Gonzaga\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plotSubPies(\"Princeton\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plotSubPies(\"James Madison\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Conclusion\nIn this kernel, I tried to answering some questions with the \"Geography\" data. Now we know that, which teams are better, which city is more advantaged or disadvantaged for which teams. You can feed your model with this approach to get better results on the competition."}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":1}