{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.\n", "SPDX-License-Identifier: Apache-2.0" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# English Premier League Teams and Stadiums\n", "This notebook uses a property graph containing the teams that took part in the 2019/20 English Premier League season. The graph includes information about the teams, their stadiums and the cities where they play.\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Check the status of our connection\n", "The three cells below can be used to check the version of the workbench, the current configuration, and the status of the Neptune cluster." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%graph_notebook_version" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%graph_notebook_config" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%status" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Create the graph\n", "The cell below creates the property graph. A set of queries that can be run against the data follows in the subsequent cells." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%gremlin\n", "\n", "// Leagues\n", "g.addV(\"League\").\n", " property(id,'EPL-2019-20').\n", " property(\"name\", \"English Premier League\").\n", " property(\"nickname\", \"EPL\").\n", " as(\"epl\").\n", "// Teams\n", " addV(\"Team\").\n", " property(id,\"Arsenal\").\n", " property(\"name\",\"Arsenal\").\n", " property(\"fullName\",\"Arsenal F. C.\",).\n", " property(\"nickname\",\"The Gunners\").\n", " property(\"founded\",1886).\n", " as(\"arsenal\").\n", " addV(\"Team\").\n", " property(id,\"Chelsea\").\n", " property(\"name\",\"Chelsea\").\n", " property(\"fullName\",\"Chelsea F.C.\").\n", " property(\"nickname\", \"The Blues\").\n", " property(\"founded\",1905).\n", " as(\"chelsea\").\n", " addV(\"Team\").\n", " property(id,\"Spurs\").\n", " property(\"name\",\"Tottenham Hotspur\").\n", " property(\"fullName\",\"Tottenham Hotspur F.C.\").\n", " property(\"nickname\",\"Spurs\").\n", " property(\"founded\",1882).\n", " as(\"spurs\").\n", " addV(\"Team\").\n", " property(id,\"WestHam\").\n", " property(\"name\",\"West Ham United\").\n", " property(\"fullName\",\"West Ham United F.C.\").\n", " property(\"nickname\",\"The Hammers\").\n", " property(\"founded\",1895).\n", " as(\"westham\").\n", " addV(\"Team\").\n", " property(id,\"Liverpool\").\n", " property(\"name\",\"Liverpool\").\n", " property(\"fullName\",\"Liverpool F.C.\").\n", " property(\"nickname\",\"The Reds\").\n", " property(\"founded\",1892).\n", " as(\"liverpool\").\n", " addV(\"Team\").\n", " property(id,\"Everton\").\n", " property(\"name\",\"Everton\").\n", " property(\"fullName\",\"Everton F.C.\").\n", " property(\"nickname\",\"The Toffees\").\n", " property(\"founded\",1878).\n", " as(\"everton\").\n", " addV(\"Team\").\n", " property(id,\"ManUtd\").\n", " property(\"name\",\"Manchester United\").\n", " property(\"fullName\",\"Manchester United F.C.\").\n", " property(\"nickname\",\"The Red Devils\").\n", " property(\"founded\",1878).\n", " as(\"manutd\").\n", " addV(\"Team\").\n", " property(id,\"ManCity\").\n", " property(\"name\",\"Manchester City\").\n", " property(\"fullName\",\"Manchester City F.C\").\n", " property(\"nickname\",\"The Citizens\").\n", " property(\"founded\",1880).\n", " as(\"mancity\").\n", " addV(\"Team\").\n", " property(id,\"Wolves\").\n", " property(\"name\", \"Wolverhampton Wanderers\" ).\n", " property(\"fullName\",\"Wolverhampton Wanderers F.C\" ).\n", " property(\"nickname\",\"Wolves\").\n", " property(\"founded\",1877).\n", " as(\"wolves\").\n", " addV(\"Team\").\n", " property(id,\"Burnley\").\n", " property(\"name\", \"Burnley\").\n", " property(\"fullName\", \"Burnley F.C\").\n", " property(\"nickname\", \"The Clarets\").\n", " property(\"founded\", 1882).\n", " as(\"burnley\").\n", " addV(\"Team\").\n", " property(id,\"SheffUtd\").\n", " property(\"name\", \"Sheffield United\").\n", " property(\"fullName\", \"Sheffield United F.C\").\n", " property(\"nickname\", \"The Blades\").\n", " property(\"founded\", 1889).\n", " as(\"sheffutd\").\n", " addV(\"Team\").\n", " property(id,\"CrystalPalace\").\n", " property(\"name\", \"Crystal Palace\").\n", " property(\"fullName\", \"Crystal Palace F.C\").\n", " property(\"nickname\", \"Eagles\").\n", " property(\"founded\", 1905).\n", " as(\"palace\").\n", " addV(\"Team\").\n", " property(id,\"Southampton\").\n", " property(\"name\", \"Southampton\").\n", " property(\"fullName\", \"Southampton F.C\").\n", " property(\"nickname\", \"The Saints\").\n", " property(\"founded\", 1885).\n", " as(\"soton\").\n", " addV(\"Team\").\n", " property(id,\"Newcastle\").\n", " property(\"name\", \"Newcastle United\").\n", " property(\"fullName\", \"Newcastle United F.C\").\n", " property(\"nickname\", \"The Magpies\").\n", " property(\"founded\", 1892).\n", " as(\"newcastle\").\n", " addV(\"Team\").\n", " property(id,\"Brighton\").\n", " property(\"name\", \"Brighton and Hove Albion\").\n", " property(\"fullName\", \"Brighton and Hove Albion F.C\").\n", " property(\"nickname\", \"Seagulls\").\n", " property(\"founded\", 1901).\n", " as(\"brighton\").\n", " addV(\"Team\").\n", " property(id,\"Watford\").\n", " property(\"name\", \"Watford\").\n", " property(\"fullName\", \"Watford F.C.\").\n", " property(\"nickname\", \"Hornets\").\n", " property(\"founded\", 1898).\n", " as(\"watford\").\n", " addV(\"Team\").\n", " property(id,\"Bournemouth\").\n", " property(\"name\", \"Bournemouth\").\n", " property(\"fullName\", \"Bournemouth F.C.\").\n", " property(\"nickname\", \"The Cherries\").\n", " property(\"founded\", 1899).\n", " as(\"bournemouth\").\n", " addV(\"Team\").\n", " property(id,\"AstonVilla\").\n", " property(\"name\", \"Aston Villa\").\n", " property(\"fullName\", \"Aston Villa F.C.\").\n", " property(\"nickname\", \"The Villans\").\n", " property(\"founded\", 1897).\n", " as(\"villa\").\n", " addV(\"Team\").\n", " property(id,\"Leicester\").\n", " property(\"name\", \"Leicester City\").\n", " property(\"fullName\", \"Leicester City F.C.\").\n", " property(\"nickname\", \"The Foxes\").\n", " property(\"founded\", 1884).\n", " as(\"leicester\").\n", " addV(\"Team\").\n", " property(id,\"Norwich\").\n", " property(\"name\", \"Norwich City\").\n", " property(\"fullName\", \"Norwich City F.C.\").\n", " property(\"nickname\", \"The Canaries\").\n", " property(\"founded\", 1902).\n", " as(\"norwich\").\n", "// Stadiums\n", " addV(\"Stadium\").\n", " property(id,\"The_Emirates\").\n", " property(\"name\", \"The Emirates\").\n", " property(\"capacity\", 60704).\n", " property(\"opened\", 2006).\n", " as(\"em\").\n", " addV(\"Stadium\").\n", " property(id,\"Stamford_Bridge\").\n", " property(\"name\", \"Stamford Bridge\").\n", " property(\"capacity\", 40834).\n", " property(\"opened\", 1877).\n", " as(\"sb\").\n", " addV(\"Stadium\").\n", " property(id,\"Tottenham_Hotspur_Stadium\").\n", " property(\"name\", \"Tottenham Hotspur Stadium\").\n", " property(\"capacity\", 62214).\n", " property(\"opened\", 2019).\n", " as(\"th\").\n", " addV(\"Stadium\").\n", " property(id,\"London_Stadium\").\n", " property(\"name\", \"London Stadium\").\n", " property(\"capacity\", 60000).\n", " property(\"opened\", 2016).\n", " as(\"ls\").\n", " addV(\"Stadium\").\n", " property(id,\"Anfield\").\n", " property(\"name\", \"Anfield\").\n", " property(\"capacity\", 53394).\n", " property(\"opened\", 1884).\n", " as(\"af\").\n", " addV(\"Stadium\").\n", " property(id,\"Goodison_Park\").\n", " property(\"name\", \"Goodison Park\").\n", " property(\"capacity\", 39414).\n", " property(\"opened\", 1892).\n", " as(\"gp\").\n", " addV(\"Stadium\").\n", " property(id,\"Old_Trafford\").\n", " property(\"name\", \"Old Trafford\").\n", " property(\"capacity\", 75643).\n", " property(\"opened\", 1910).\n", " as(\"ot\").\n", " addV(\"Stadium\").\n", " property(id,\"The_Etihad\").\n", " property(\"name\", \"Etihad Stadium\").\n", " property(\"capacity\", 55107).\n", " property(\"opened\", 2003).\n", " as(\"et\").\n", " addV(\"Stadium\").\n", " property(id,\"Molineux\").\n", " property(\"name\", \"Molineux Stadium\").\n", " property(\"capacity\", 32050).\n", " property(\"opened\", 1889).\n", " as(\"mo\").\n", " addV(\"Stadium\").\n", " property(id,\"Turf_Moor\").\n", " property(\"name\", \"Turf Moor\").\n", " property(\"capacity\", 21944).\n", " property(\"opened\", 1883).\n", " as(\"tm\").\n", " addV(\"Stadium\").\n", " property(id,\"Bramall_Lane\").\n", " property(\"name\", \"Bramall Lane\").\n", " property(\"capacity\", 32125).\n", " property(\"opened\", 1855).\n", " as(\"bl\").\n", " addV(\"Stadium\").\n", " property(id,\"Selhurst_Park\").\n", " property(\"name\", \"Selhurst Park Stadium\").\n", " property(\"capacity\", 25486).\n", " property(\"opened\", 1924).\n", " as(\"sp\").\n", " addV(\"Stadium\").\n", " property(id,\"St_Marys\").\n", " property(\"name\", \"St. Mary's Stadium\").\n", " property(\"capacity\", 32384).\n", " property(\"opened\", 2001).\n", " as(\"sm\").\n", " addV(\"Stadium\").\n", " property(id,\"St_James_Park\").\n", " property(\"name\", \"St. James' Park\").\n", " property(\"capacity\", 52305).\n", " property(\"opened\", 1880).\n", " as(\"sjp\").\n", " addV(\"Stadium\").\n", " property(id,\"Amex_Stadium\").\n", " property(\"name\", \"American Express Community Stadium\").\n", " property(\"capacity\", 30750).\n", " property(\"opened\", 2011).\n", " as(\"amx\").\n", " addV(\"Stadium\").\n", " property(id,\"Vicarage_Road\").\n", " property(\"name\", \"Vicarage Road\").\n", " property(\"capacity\", 22200).\n", " property(\"opened\", 1922).\n", " as(\"vr\").\n", " addV(\"Stadium\").\n", " property(id,\"Vitality_Stadium\").\n", " property(\"name\", \"Vitality Stadium\").\n", " property(\"capacity\", 11364).\n", " property(\"opened\", 1910).\n", " as(\"vs\").\n", " addV(\"Stadium\").\n", " property(id,\"Villa_Park\").\n", " property(\"name\", \"Villa Park\").\n", " property(\"capacity\", 42095).\n", " property(\"opened\", 1897).\n", " as(\"vp\").\n", " addV(\"Stadium\").\n", " property(id,\"King_Power_Stadium\").\n", " property(\"name\", \"King Power Stadium\").\n", " property(\"capacity\", 32261).\n", " property(\"opened\", 2002).\n", " as(\"kp\").\n", " addV(\"Stadium\").\n", " property(id,\"Carrow_Road_Stadium\").\n", " property(\"name\", \"Carrow Road Stadium\").\n", " property(\"capacity\", 27359).\n", " property(\"opened\", 1935).\n", " as(\"cr\").\n", "// Cities\n", " addV(\"City\").\n", " property(id,\"City_London\").\n", " property(\"name\", \"London\").\n", " as(\"lon\").\n", " addV(\"City\").\n", " property(id,\"City_Liverpool\").\n", " property(\"name\", \"Liverpool\").\n", " as(\"liv\").\n", " addV(\"City\").\n", " property(id,\"City_Manchester\").\n", " property(\"name\", \"Manchester\").\n", " as(\"man\").\n", " addV(\"City\").\n", " property(id,\"City_Wolverhampton\").\n", " property(\"name\", \"Wolverhampton\").\n", " as(\"wol\").\n", " addV(\"City\").\n", " property(id,\"City_Leicester\").\n", " property(\"name\", \"Leicester\").\n", " as(\"lei\").\n", " addV(\"City\").\n", " property(id,\"City_Brighton\").\n", " property(\"name\", \"Brighton\").\n", " as(\"bri\").\n", " addV(\"City\").\n", " property(id,\"City_Southampton\").\n", " property(\"name\", \"Southampton\").\n", " as(\"sou\").\n", " addV(\"City\").\n", " property(id,\"City_Newcastle\").\n", " property(\"name\", \"Newcastle\").\n", " as(\"nwc\").\n", " addV(\"City\").\n", " property(id,\"City_Sheffield\").\n", " property(\"name\", \"Sheffield\").\n", " as(\"shf\").\n", " addV(\"City\").\n", " property(id,\"City_Birmingham\").\n", " property(\"name\", \"Birmingham\").\n", " as(\"bmx\").\n", " addV(\"City\").\n", " property(id,\"City_Watford\").\n", " property(\"name\", \"Watford\").\n", " as(\"wat\").\n", " addV(\"City\").\n", " property(id,\"City_Norwich\").\n", " property(\"name\", \"Norwich\").\n", " as(\"nor\").\n", " addV(\"City\").\n", " property(id,\"City_Bournemouth\").\n", " property(\"name\", \"Bournemouth\").\n", " as(\"bou\").\n", " addV(\"City\").\n", " property(id,\"City_Burnley\").\n", " property(\"name\", \"Burnley\"). \n", " as(\"bur\").\n", "// Edges for EPL membership, stadium and city\n", " addE(\"CURRENT_LEAGUE\").from(\"arsenal\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"arsenal\").to(\"em\").\n", " addE(\"CITY\").from(\"em\").to(\"lon\").\n", " addE(\"CURRENT_LEAGUE\").from(\"chelsea\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"chelsea\").to(\"sb\").\n", " addE(\"CITY\").from(\"sb\").to(\"lon\").\n", " addE(\"CURRENT_LEAGUE\").from(\"spurs\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"spurs\").to(\"th\").\n", " addE(\"CITY\").from(\"th\").to(\"lon\").\n", " addE(\"CURRENT_LEAGUE\").from(\"westham\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"westham\").to(\"ls\").\n", " addE(\"CITY\").from(\"ls\").to(\"lon\").\n", " addE(\"CURRENT_LEAGUE\").from(\"liverpool\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"liverpool\").to(\"af\").\n", " addE(\"CITY\").from(\"af\").to(\"liv\").\n", " addE(\"CURRENT_LEAGUE\").from(\"everton\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"everton\").to(\"gp\").\n", " addE(\"CITY\").from(\"gp\").to(\"liv\").\n", " addE(\"CURRENT_LEAGUE\").from(\"manutd\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"manutd\").to(\"ot\").\n", " addE(\"CITY\").from(\"ot\").to(\"man\").\n", " addE(\"CURRENT_LEAGUE\").from(\"mancity\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"mancity\").to(\"et\").\n", " addE(\"CITY\").from(\"et\").to(\"man\").\n", " addE(\"CURRENT_LEAGUE\").from(\"wolves\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"wolves\").to(\"mo\").\n", " addE(\"CITY\").from(\"mo\").to(\"wol\").\n", " addE(\"CURRENT_LEAGUE\").from(\"burnley\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"burnley\").to(\"tm\").\n", " addE(\"CITY\").from(\"tm\").to(\"bur\").\n", " addE(\"CURRENT_LEAGUE\").from(\"sheffutd\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"sheffutd\").to(\"bl\").\n", " addE(\"CITY\").from(\"bl\").to(\"shf\").\n", " addE(\"CURRENT_LEAGUE\").from(\"palace\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"palace\").to(\"sp\").\n", " addE(\"CITY\").from(\"sp\").to(\"lon\").\n", " addE(\"CURRENT_LEAGUE\").from(\"soton\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"soton\").to(\"sm\").\n", " addE(\"CITY\").from(\"sm\").to(\"sou\").\n", " addE(\"CURRENT_LEAGUE\").from(\"newcastle\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"newcastle\").to(\"sjp\").\n", " addE(\"CITY\").from(\"sjp\").to(\"nwc\").\n", " addE(\"CURRENT_LEAGUE\").from(\"watford\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"watford\").to(\"vr\").\n", " addE(\"CITY\").from(\"vr\").to(\"wat\").\n", " addE(\"CURRENT_LEAGUE\").from(\"leicester\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"leicester\").to(\"kp\").\n", " addE(\"CITY\").from(\"kp\").to(\"lei\").\n", " addE(\"CURRENT_LEAGUE\").from(\"villa\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"villa\").to(\"vp\").\n", " addE(\"CITY\").from(\"vp\").to(\"bmx\").\n", " addE(\"CURRENT_LEAGUE\").from(\"brighton\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"brighton\").to(\"amx\").\n", " addE(\"CITY\").from(\"amx\").to(\"bri\").\n", " addE(\"CURRENT_LEAGUE\").from(\"bournemouth\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"bournemouth\").to(\"vs\").\n", " addE(\"CITY\").from(\"vs\").to(\"bou\").\n", " addE(\"CURRENT_LEAGUE\").from(\"norwich\").to(\"epl\").\n", " addE(\"STADIUM\").from(\"norwich\").to(\"cr\").\n", " addE(\"CITY\").from(\"cr\").to(\"nor\")\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Verify the data was inserted" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%gremlin\n", "g.V().groupCount().by(label).unfold()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%gremlin\n", "g.E().groupCount().by(label).unfold()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Build a visualization of the graph\n", "Run the cell below and select the Graph tab to see a visualization of the results. The various hints you can provide and settings you can adjust when building visualizations are explained in the blog post located [here](https://aws.amazon.com/blogs/database/visualize-query-results-using-the-amazon-neptune-workbench/)." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%gremlin -p v,ine,outv,oute,inv,oute,inv\n", "g.V().hasLabel('League').inE().outV().outE().inV().outE().inV().path().by('name').by(label)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Delete the entire data set\n", "This cell can be used to delete the data set. The drop is done using explicit IDs to try and avoid conflicts with any other data you may have loaded in your graph." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%gremlin\n", "g.V().hasId('Arsenal', 'Chelsea', 'Spurs', 'WestHam', 'Liverpool', 'Everton', 'ManUtd', 'ManCity', \n", " 'Wolves', 'Burnley', 'SheffUtd', 'CrystalPalace', 'Southampton', 'Newcastle', 'Brighton',\n", " 'Watford', 'Bournemouth', 'AstonVilla', 'Leicester', 'Norwich', 'The_Emirates', \n", " 'Stamford_Bridge', 'Tottenham_Hotspur_Stadium', 'London_Stadium', 'Anfield', 'Goodison_Park', \n", " 'Old_Trafford', 'The_Etihad', 'Molineux', 'Turf_Moor', 'Bramall_Lane', 'Selhurst_Park', \n", " 'St_Marys', 'St_James_Park', 'Amex_Stadium', 'Vicarage_Road', 'Vitality_Stadium', 'Villa_Park',\n", " 'King_Power_Stadium', 'Carrow_Road_Stadium', 'City_London', 'City_Liverpool', 'City_Manchester', \n", " 'City_Wolverhampton', 'City_Leicester', 'City_Brighton', 'City_Southampton', 'City_Newcastle', \n", " 'City_Sheffield', 'City_Birmingham', 'City_Watford', 'City_Norwich', 'City_Bournemouth', \n", " 'City_Burnley', 'EPL-2019-20').drop()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### How many teams were in the league that season?" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%gremlin\n", "g.V().hasLabel('Team').count()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%gremlin\n", "g.V('EPL-2019-20').in('CURRENT_LEAGUE').count()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Team info\n", "Find the teams in the graph and their properties." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%gremlin\n", "g.V().hasLabel('Team').valueMap()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Team and stadium info" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%gremlin\n", "g.V().hasLabel('Team').\n", " project('name','stadium','city').\n", " by('name').\n", " by(out('STADIUM').values('name')).\n", " by(out('STADIUM').out('CITY').values('name'))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Teams based in London" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "scrolled": true }, "outputs": [], "source": [ "%%gremlin\n", "g.V().has('City','name','London').in('CITY').in('STADIUM').values('name')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Stadiums in London" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%gremlin -p v,outv\n", "g.V().has('City','name','London').in('CITY').path().by('name')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Teams in London plus their stadiums" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "scrolled": false }, "outputs": [], "source": [ "%%gremlin -p v,outv,outv\n", "g.V().has('City','name','London').in('CITY').in('STADIUM').path().by('name')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Was Coventry in the league that season?\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%gremlin\n", "g.V().has('Team','name','Coventry').\n", " fold().\n", " coalesce(unfold(),constant('Not in the EPL that year'))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Using text predicates to find sub-strings\n", "This query looks for any teams that have the string \"ou\" in their name." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%gremlin\n", "g.V().has('Team','name',containing('ou')).values('name')\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Find everything the graph contains related to Arsenal" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%gremlin\n", "\n", "g.V().hasId('Arsenal').\n", " project('names-and-age','stadium','city').\n", " by(valueMap().by(unfold())).\n", " by(out('STADIUM').values('name')).\n", " by(out('STADIUM').out('CITY').values('name')).unfold()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### How many stadiums are in each city?" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%gremlin\n", "g.V().hasLabel('Stadium').\n", " groupCount().\n", " by(out('CITY')).\n", " order(local).\n", " by(values,desc).\n", " unfold()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### What year did each stadium open?" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%gremlin\n", "g.V().hasLabel('Stadium').\n", " valueMap('name','opened').by(unfold()).\n", " order().\n", " by(select('opened'))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Stadiums ordered by descending capacity" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%gremlin\n", "g.V().hasLabel('Stadium').\n", " order().\n", " by('capacity',desc).\n", " valueMap('name','capacity').\n", " by(unfold())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Number of teams founded in a given year" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%gremlin\n", "g.V().hasLabel('Team').\n", " group().\n", " by('founded').\n", " by('name').\n", " order(local).\n", " by(keys).\n", " unfold()\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Page Width\n", "If you would like Jupyter to maximise the horizontal screen real estate run the cell below." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from IPython.core.display import display, HTML\n", "display(HTML(\"\"))" ] } ], "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.5" } }, "nbformat": 4, "nbformat_minor": 2 }