{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# MLS National TV Lineup\n", "\n", "By Christian McDonald\n", "\n", "Major League Soccer announced their [2018 national TV lineup](https://www.mlssoccer.com/post/2018/01/04/mls-unveils-2018-us-and-canada-national-broadcast-schedule) on Thursday, Jan. 4th. A quick scan of the lineup seemed heavy on Atlanta, Seattle, Portland and the L.A. and N.Y. teams, but it was hard to know for sure. I didn't see Houston or Dallas very often, nor our maybe-Texas-team-to-be Columbus Crew.\n", "\n", "So I thought I would practice some analysis skills and take a look at the matchups, seeing who was favored in the schedule. Surely TV market size has a lot to do with these results, but we needed another reason to talk about soccer in the office.\n", "\n", "## Goals\n", "- games by team\n", "- breakdown by language\n", "- Who is a home more\n", "\n", "## To Do\n", "- This is not DRY. I need to turn the chart/table making into a function, which I could then feed the language table into it.\n", "- I sure would like to add a title to the charts. Perhaps leather is too limited, but it does show how to do it in the [leather docs](http://leather.readthedocs.io/), but it doesn't work the same inside agate.\n", "\n", "## Of note\n", "- The data was scraped using the Chrome Scraper tool, inserted into a [Google Spreadsheet and cleaned up](https://docs.google.com/spreadsheets/d/e/2PACX-1vT1wVvMBp-OBmOm0tDfc6LZuPoz6D8X7NppUkHn6O51-KoIqjA67S7E9SIjaT3Oo_HT73nEeUUaz_48/pubhtml), then downloaded as CSV.\n", "- The result was used in the story [Columbus Crew SC schedule provides limited exposure for Texas audience](http://www.statesman.com/sports/columbus-crew-schedule-provides-limited-exposure-for-texas-audience/Um8mpSm4HisuWR9ieEeXVI/)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## What I see ...\n", "\n", "Atlanta is clearly the darling overall, and why not after hosting the [most-attended MLS game ever](http://mercedesbenzstadium.com/atlanta-united-break-mls-attendance-record-4th-largest-attended-match-world/) last year in the new Mercedes Benz stadium.\n", "\n", "Atlanta is also a decent TV market, [ranking 10th for 2016-2017](http://www.nielsen.com/content/dam/corporate/us/en/docs/solutions/measurement/television/2016-2017-nielsen-local-dma-ranks.pdf), the latest rankings I could find.\n", "\n", "MLS is clearly taking advantage of the innaugural season of LAFC to showcase the left-coast big-city rivalry, along with three showings of the New York derby. The popular Seattle and Portland teams are up there with still-relatively-new teams NYC FC and Orlando.\n", "\n", "Austin's maybe-team-to-be gets only two national matches, and the other Texas teams don't fare too well on the English-language channels. That said, FC Dallas and the Houston Dynamo do top the Spanish matchups.\n", "\n", "Atlanta also hosts the most games, with the LA teams and English-language channel Portland games also high on the list.\n", "\n", "See the output below:" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import agate\n", "import warnings\n", "warnings.filterwarnings('ignore')" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "| column | data_type |\n", "| --------- | --------- |\n", "| Date Time | DateTime |\n", "| Date | Date |\n", "| Match | Text |\n", "| Network | Text |\n", "| Spanish | Boolean |\n", "| Home | Text |\n", "| Visitor | Text |\n", "\n" ] } ], "source": [ "raw = agate.Table.from_csv('../data/mlstv.csv')\n", "print(raw)" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "len of both: 86\n", "len of english: 61\n", "len of spanish: 25\n" ] } ], "source": [ "# filtering for language\n", "\n", "both = raw\n", "english = raw.where(lambda r: r['Spanish'] is False)\n", "spanish = raw.where(lambda r: r['Spanish'] is True)\n", "print('len of both: {}'.format(len(both)))\n", "print('len of english: {}'.format(len(english)))\n", "print('len of spanish: {}'.format(len(spanish)))\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## All National TV" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "len of visitor: 86\n", "len of home: 86\n", "len of teams: 172\n", "\n", "| team | Matches |\n", "| ---------------------- | ------- |\n", "| Atlanta United | 16 |\n", "| LAFC | 16 |\n", "| New York City FC | 14 |\n", "| New York Red Bulls | 14 |\n", "| LA Galaxy | 13 |\n", "| Orlando City SC | 13 |\n", "| Seattle Sounders | 13 |\n", "| D.C. United | 12 |\n", "| Portland Timbers | 12 |\n", "| Chicago Fire | 7 |\n", "| FC Dallas | 7 |\n", "| Sporting Kansas City | 7 |\n", "| Houston Dynamo | 6 |\n", "| San Jose Earthquakes | 5 |\n", "| Toronto FC | 4 |\n", "| Philadelphia Union | 3 |\n", "| Colorado Rapids | 2 |\n", "| Columbus Crew SC | 2 |\n", "| Minnesota United FC | 2 |\n", "| New England Revolution | 2 |\n", "| Real Salt Lake | 2 |\n" ] }, { "data": { "image/svg+xml": [ "Matches51015200teamReal Salt LakeNew England RevolutionMinnesota United FCColumbus Crew SCColorado RapidsPhiladelphia UnionToronto FCSan Jose EarthquakesHouston DynamoSporting Kansas CityFC DallasChicago FirePortland TimbersD.C. UnitedSeattle SoundersOrlando City SCLA GalaxyNew York Red BullsNew York City FCLAFCAtlanta United" ], "text/plain": [ "" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# create a single list of all teams in matchups\n", "home = both.select(['Home', 'Date']).rename(column_names = ['team', 'date'])\n", "visitor = both.select(['Visitor', 'Date']).rename(column_names = ['team', 'date'])\n", "teams = agate.Table.merge([visitor, home])\n", "\n", "print('len of visitor: {}'.format(len(visitor)))\n", "print('len of home: {}'.format(len(home)))\n", "print('len of teams: {}\\n'.format(len(teams)))\n", "teams_pivot = teams.pivot('team').rename(column_names = {'Count': 'Matches'})\n", "teams_chart = teams_pivot.order_by('team').order_by('Matches', reverse=True)\n", "teams_chart.print_table(max_rows=None, max_column_width=None)\n", "teams_chart.to_csv('../data/all_tv.csv')\n", "teams_chart.bar_chart('team', 'Matches','../charts/all_tv.svg')\n", "teams_chart.bar_chart('team', 'Matches')\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## English language" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "len of en_visitor: 61\n", "len of en_home: 61\n", "len of en_teams: 122\n", "\n", "| team | Matches |\n", "| ---------------------- | ------- |\n", "| Seattle Sounders | 13 |\n", "| Atlanta United | 12 |\n", "| LAFC | 12 |\n", "| New York City FC | 11 |\n", "| Orlando City SC | 11 |\n", "| Portland Timbers | 11 |\n", "| New York Red Bulls | 10 |\n", "| LA Galaxy | 9 |\n", "| D.C. United | 8 |\n", "| Sporting Kansas City | 7 |\n", "| Toronto FC | 4 |\n", "| Chicago Fire | 2 |\n", "| Columbus Crew SC | 2 |\n", "| Minnesota United FC | 2 |\n", "| Philadelphia Union | 2 |\n", "| Colorado Rapids | 1 |\n", "| FC Dallas | 1 |\n", "| Houston Dynamo | 1 |\n", "| New England Revolution | 1 |\n", "| Real Salt Lake | 1 |\n", "| San Jose Earthquakes | 1 |\n" ] }, { "data": { "image/svg+xml": [ "Matches510150teamSan Jose EarthquakesReal Salt LakeNew England RevolutionHouston DynamoFC DallasColorado RapidsPhiladelphia UnionMinnesota United FCColumbus Crew SCChicago FireToronto FCSporting Kansas CityD.C. UnitedLA GalaxyNew York Red BullsPortland TimbersOrlando City SCNew York City FCLAFCAtlanta UnitedSeattle Sounders" ], "text/plain": [ "" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "en_visitor = english.select(['Visitor', 'Date']).rename(column_names = ['team', 'date'])\n", "en_home = english.select(['Home', 'Date']).rename(column_names = ['team', 'date'])\n", "en_teams = agate.Table.merge([en_visitor, en_home])\n", "\n", "print('len of en_visitor: {}'.format(len(en_visitor)))\n", "print('len of en_home: {}'.format(len(en_home)))\n", "print('len of en_teams: {}\\n'.format(len(en_teams)))\n", "\n", "en_teams_pivot = en_teams.pivot('team').rename(column_names = {'Count': 'Matches'})\n", "en_teams_chart = en_teams_pivot.order_by('team').order_by('Matches', reverse=True)\n", "en_teams_chart.to_csv('../data/en_tv.csv')\n", "en_teams_chart.print_table(max_rows=None, max_column_width=None)\n", "en_teams_chart.bar_chart('team', 'Matches','../charts/en_tv.svg')\n", "en_teams_chart.bar_chart('team', 'Matches')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Spanish language" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "len of es_visitor: 25\n", "len of es_home: 25\n", "len of es_teams: 50\n", "\n", "| team | Matches |\n", "| ---------------------- | ------- |\n", "| FC Dallas | 6 |\n", "| Chicago Fire | 5 |\n", "| Houston Dynamo | 5 |\n", "| Atlanta United | 4 |\n", "| D.C. United | 4 |\n", "| LA Galaxy | 4 |\n", "| LAFC | 4 |\n", "| New York Red Bulls | 4 |\n", "| San Jose Earthquakes | 4 |\n", "| New York City FC | 3 |\n", "| Orlando City SC | 2 |\n", "| Colorado Rapids | 1 |\n", "| New England Revolution | 1 |\n", "| Philadelphia Union | 1 |\n", "| Portland Timbers | 1 |\n", "| Real Salt Lake | 1 |\n" ] }, { "data": { "image/svg+xml": [ "Matches2460teamReal Salt LakePortland TimbersPhiladelphia UnionNew England RevolutionColorado RapidsOrlando City SCNew York City FCSan Jose EarthquakesNew York Red BullsLAFCLA GalaxyD.C. UnitedAtlanta UnitedHouston DynamoChicago FireFC Dallas" ], "text/plain": [ "" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "es_visitor = spanish.select(['Visitor', 'Date']).rename(column_names = ['team', 'date'])\n", "es_home = spanish.select(['Home', 'Date']).rename(column_names = ['team', 'date'])\n", "es_teams = agate.Table.merge([es_visitor, es_home])\n", "\n", "print('len of es_visitor: {}'.format(len(es_visitor)))\n", "print('len of es_home: {}'.format(len(es_home)))\n", "print('len of es_teams: {}\\n'.format(len(es_teams)))\n", "\n", "es_teams_pivot = es_teams.pivot('team').rename(column_names = {'Count': 'Matches'})\n", "es_teams_chart = es_teams_pivot.order_by('team').order_by('Matches', reverse=True)\n", "es_teams_chart.to_csv('../data/es_tv.csv')\n", "es_teams_chart.print_table(max_rows=None, max_column_width=None)\n", "es_teams_chart.bar_chart('team', 'Matches','../charts/es_tv.svg')\n", "es_teams_chart.bar_chart('team', 'Matches')" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "| Date | Match | Network | Spanish |\n", "| ---------- | ------------------ | ------- | ------- |\n", "| 2018-03-31 | LA Galaxy vs. LAFC | FOX | False |\n", "| 2018-07-26 | LAFC vs. LA Galaxy | ESPN | False |\n", "| 2018-08-24 | LA Galaxy vs. LAFC | ESPN | False |\n" ] } ], "source": [ "la_derby = both.where(lambda row: row['Match'] in {\n", " 'LA Galaxy vs. LAFC',\n", " 'LAFC vs. LA Galaxy'}).order_by('Date')\n", "la_derby.select(['Date', 'Match', 'Network', 'Spanish']).print_table(max_column_width=None)" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "| Date | Match | Network | Spanish |\n", "| ---------- | --------------------------------------- | ------- | ------- |\n", "| 2018-05-05 | New York Red Bulls vs. New York City FC | ESPN | False |\n", "| 2018-07-08 | New York City FC vs. New York Red Bulls | FS1 | False |\n", "| 2018-08-22 | New York City FC vs. New York Red Bulls | FS1 | False |\n" ] } ], "source": [ "ny_derby = both.where(lambda row: row['Match'] in {\n", " 'New York City FC vs. New York Red Bulls',\n", " 'New York Red Bulls vs. New York City FC'}).order_by('Date')\n", "ny_derby.select(['Date', 'Match', 'Network', 'Spanish']).print_table(max_column_width=None)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Who hosts the most?\n", "\n", "Who hosts the most home games on National TV?" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "| Home | Matches |\n", "| -------------------- | ------- |\n", "| Atlanta United | 10 |\n", "| LAFC | 8 |\n", "| LA Galaxy | 7 |\n", "| New York Red Bulls | 7 |\n", "| Portland Timbers | 6 |\n", "| Seattle Sounders | 6 |\n", "| D.C. United | 6 |\n", "| Sporting Kansas City | 5 |\n", "| Orlando City SC | 5 |\n", "| Chicago Fire | 5 |\n", "| New York City FC | 4 |\n", "| Toronto FC | 3 |\n", "| Philadelphia Union | 3 |\n", "| Houston Dynamo | 3 |\n", "| San Jose Earthquakes | 2 |\n", "| Colorado Rapids | 2 |\n", "| FC Dallas | 2 |\n", "| Columbus Crew SC | 1 |\n", "| Real Salt Lake | 1 |\n" ] } ], "source": [ "home_team_pivot = both.pivot('Home').rename(column_names = {'Count': 'Matches'}).order_by(\n", " 'Matches',\n", " reverse=True\n", ")\n", "home_team_pivot.print_table()" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "| Home | Matches |\n", "| -------------------- | ------- |\n", "| Atlanta United | 8 |\n", "| LA Galaxy | 6 |\n", "| Portland Timbers | 6 |\n", "| LAFC | 6 |\n", "| Seattle Sounders | 6 |\n", "| Sporting Kansas City | 5 |\n", "| D.C. United | 5 |\n", "| Orlando City SC | 4 |\n", "| New York Red Bulls | 4 |\n", "| Toronto FC | 3 |\n", "| New York City FC | 2 |\n", "| Philadelphia Union | 2 |\n", "| Chicago Fire | 1 |\n", "| San Jose Earthquakes | 1 |\n", "| Colorado Rapids | 1 |\n", "| Columbus Crew SC | 1 |\n" ] } ], "source": [ "# Hosting on English national TV\n", "en_home_team_pivot = english.pivot('Home').rename(column_names = {'Count': 'Matches'}).order_by(\n", " 'Matches',\n", " reverse=True\n", ")\n", "en_home_team_pivot.print_table()" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "| Home | Matches |\n", "| -------------------- | ------- |\n", "| Chicago Fire | 4 |\n", "| Houston Dynamo | 3 |\n", "| New York Red Bulls | 3 |\n", "| New York City FC | 2 |\n", "| FC Dallas | 2 |\n", "| LAFC | 2 |\n", "| Atlanta United | 2 |\n", "| Real Salt Lake | 1 |\n", "| Orlando City SC | 1 |\n", "| Philadelphia Union | 1 |\n", "| LA Galaxy | 1 |\n", "| San Jose Earthquakes | 1 |\n", "| Colorado Rapids | 1 |\n", "| D.C. United | 1 |\n" ] } ], "source": [ "# Hosting on English national TV\n", "es_home_team_pivot = spanish.pivot('Home').rename(column_names = {'Count': 'Matches'}).order_by(\n", " 'Matches',\n", " reverse=True\n", ")\n", "es_home_team_pivot.print_table()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Does the same match appear on more than one network?\n", "\n", "I pivot by both Date and Match, which will count records that have the both. I order in reverse to show there are no cases where two matchups appear more than one. I did test this by creating a fake dupe record to make sure it works." ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "| Date | Match | Count |\n", "| ---------- | ----------------------------------------- | ----- |\n", "| 2018-03-04 | Sporting Kansas City vs. New York City FC | 1 |\n", "| 2018-03-04 | LA Galaxy vs. Portland Timbers | 1 |\n", "| 2018-03-04 | Seattle Sounders vs. LAFC | 1 |\n", "| 2018-03-31 | LA Galaxy vs. LAFC | 1 |\n", "| 2018-03-31 | San Jose Earthquakes vs. New York City FC | 1 |\n" ] } ], "source": [ "dupe_check = both.pivot(['Date', 'Match'])\n", "dupe_check.order_by('Count', reverse=True).limit(5).print_table(max_column_width=None)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "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.6.4" } }, "nbformat": 4, "nbformat_minor": 1 }