Учитывая, что мы все еще находимся в предсезонном сумасшествии по поводу трансфера, что касается европейского футбола, я подумал, что было бы интересно составить график трансфера по футболу, чтобы увидеть, есть ли какие-нибудь интересные идеи, которые нужно иметь.
Мне потребовалось некоторое время, чтобы найти подходящий источник, но в конце концов я наткнулся на Transfermarkt.co.uk, в котором содержатся переводы, относящиеся, по крайней мере, к началу Премьер-лиги в 1992 году.
Я написал быстрый скрипт на Python для создания CSV-файла всех переводов. Вот как выглядит файл:
|
01
02
03
04
05
06
07
08
09
10
11
|
$ head -n 10 data/transfers.csvplayer,from_team,from_team_id,to_team,to_team_id,fee,seasonMartin Keown,Everton,29,Arsenal FC,11,"2,10 Mill. £",1992-1993John Jensen,Bröndby IF,206,Arsenal FC,11,"1,12 Mill. £",1992-1993Alan Miller,Birmingham,337,Arsenal FC,11,,1992-1993Jim Will,Sheffield Utd.,350,Arsenal FC,11,,1992-1993David Rocastle,Arsenal FC,11,Leeds,399,"1,68 Mill. £",1992-1993Perry Groves,Arsenal FC,11,Southampton FC,180,595 Th. £,1992-1993Ty Gooden,Arsenal FC,11,Wycombe Wand.,2805,?,1992-1993Geraint Williams,Derby,22,Ipswich Town,677,525 Th. £,1992-1993Jason Winters,Chelsea U21,9250,Ipswich Town,677,?,1992-1993 |
Я собираюсь создать следующий график, а затем мы напишем несколько запросов, которые исследуют цепочки передач с участием игроков и клубов.
Я написал несколько сценариев импорта с помощью команды LOAD CSV Neo4j, предварительно настроив соответствующие индексы:
|
1
2
3
4
|
create index on :Team(id);create index on :Season(name);create index on :Transfer(description);create index on :Player(name); |
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
// teamsload csv with headers from "file:///Users/markneedham/projects/football-transfers/data/teams.csv" as rowmerge (team:Team {id: toint(row.team_id)})on create set team.name = row.team; // seasonsload csv with headers from "file:///Users/markneedham/projects/football-transfers/data/transfers.csv" as rowmerge (season:Season {name: row.season})ON CREATE SET season.starts = toint(split(season.name, "-")[0]); // playersload csv with headers from "file:///Users/markneedham/projects/football-transfers/data/transfers.csv" as rowmerge (player:Player {name: row.player}); // transfersload csv with headers from "file:///Users/markneedham/projects/football-transfers/data/transfers.csv" as rowmatch (from:Team {id: toint(row.from_team_id)})match (to:Team {id: toint(row.to_team_id)})match (season:Season {name: row.season})match (player:Player {name: row.player}) merge (transfer:Transfer {description: row.player + " from " + from.name + " to " + to.name})merge (transfer)-[:FROM_TEAM]->(from)merge (transfer)-[:TO_TEAM]->(to)merge (transfer)-[:IN_SEASON]->(season)merge (transfer)-[:PLAYER]->(player); // connect transfersmatch (season)<-[:IN_SEASON]-(transfer:Transfer)-[:PLAYER]->(player)WITH player, season, transferORDER BY player.name, season.startsWITH player, COLLECT({s: season, t: transfer}) AS transfersUNWIND range(0, length(transfers)-2) AS idxWITH player, transfers[idx] AS t1, transfers[idx +1] AS t2WITH player, t1.t AS t1, t2.t AS t2MERGE (t1)-[:NEXT]->(t2); |
Все файлы и сценарии включены в этот список, если вы хотите поиграть с данными. Единственное, что вам нужно изменить, — это путь к файлу в каждой из строк «LOAD CSV».
Запрос «Connect Transfer» немного сложнее, чем другие — в этом мы сначала упорядочиваем переводы в порядке возрастания, сгруппированные по игрокам, а затем создаем связанный список переводов игрока.
Теперь, когда мы загрузили данные, давайте выясним, какой игрок был перенесен больше всего:
|
1
2
3
4
5
|
match path = (:Transfer)-[:NEXT*0..]->(transfer:Transfer)where NOT (transfer)-[:NEXT]->()RETURN path ORDER BY LENGTH(path) DESCLIMIT 1 |
Какие другие игроки часто перемещали команды?
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
|
match path = (first:Transfer)-[:NEXT*0..]->(transfer:Transfer), (player)<-[:PLAYER]-(transfer)where NOT ((transfer)-[:NEXT]->()) AND NOT ((first)<-[:NEXT]-())RETURN player.name, LENGTH(path) AS numberOfTransfers ORDER BY numberOfTransfers DESCLIMIT 10 ==> +--------------------------------------+==> | player.name | numberOfTransfers |==> +--------------------------------------+==> | "Craig Bellamy" | 7 |==> | "David Unsworth" | 6 |==> | "Andrew Cole" | 6 |==> | "Peter Crouch" | 6 |==> | "Les Ferdinand" | 5 |==> | "Kevin Phillips" | 5 |==> | "Mark Hughes" | 5 |==> | "Tommy Wright" | 4 |==> | "Carl Tiler" | 4 |==> | "Don Hutchison" | 4 |==> +--------------------------------------+==> 10 rows |
Какие клубы чаще всего используются в трансферах?
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
|
match (from)<-[:FROM_TEAM]-(t:Transfer)-[:TO_TEAM]->(to), (t)-[:PLAYER]->(p)RETURN from.name, to.name, COUNT(*) AS times, COLLECT(p.name) AS playersORDER BY times DESCLIMIT 10 ==> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+==> | from.name | to.name | times | players |==> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+==> | "West Ham United" | "Queens Park Rangers" | 13 | ["Keith Rowland","Iain Dowie","Tim Breacker","Ludek Miklosko","Bertie Brayley","Terrell Forbes","Steve Lomas","Hogan Ephraim","Nigel Quashie","Danny Gabbidon","Kieron Dyer","Robert Green","Gary O'Neil"] |==> | "Tottenham Hotspur" | "Portsmouth FC" | 12 | ["Paul Walsh","Andy Turner","Rory Allen","Justin Edinburgh","Tim Sherwood","Teddy Sheringham","Noé Pamarot","Pedro Mendes","Sean Davis","Jermain Defoe","Younès Kaboul","Kevin-Prince Boateng"] |==> | "Liverpool FC" | "West Ham United" | 12 | ["Julian Dicks","David Burrows","Mike Marsh","Don Hutchison","Neil Ruddock","Titi Camara","Rob Jones","Rigobert Song","Craig Bellamy","Joe Cole","Andy Carroll","Stewart Downing"] |==> | "Manchester United" | "Everton FC" | 9 | ["Andrey Kanchelskis","John O'Kane","Jesper Blomqvist","Phil Neville","Tim Howard","Louis Saha","Darron Gibson","Sam Byrne","Tom Cleverley"] |==> | "Newcastle United" | "West Ham United" | 9 | ["Paul Kitson","Shaka Hislop","Stuart Pearce","Wayne Quinn","Lee Bowyer","Kieron Dyer","Scott Parker","Nolberto Solano","Kevin Nolan"] |==> | "Blackburn Rovers" | "Leicester City" | 9 | ["Steve Agnew","Tim Flowers","Callum Davidson","John Curtis","Keith Gillespie","Craig Hignett","Nils-Eric Johansson","Bruno Berner","Paul Gallagher"] |==> | "Chelsea FC" | "Southampton FC" | 8 | ["Ken Monkou","Kerry Dixon","Neil Shipperley","Mark Hughes","Paul Hughes","Graeme Le Saux","Jack Cork","Ryan Bertrand"] |==> | "Birmingham City" | "Coventry City" | 8 | ["David Rennie","John Gayle","Liam Daish","Gary Breen","Stern John","Julian Gray","Lee Carsley","Gary McSheffrey"] |==> | "Southampton FC" | "Fulham FC" | 8 | ["Micky Adams","Kevin Moore","Terry Hurlock","Maik Taylor","Alan Neilson","Luís Boa Morte","Antti Niemi","Chris Baird"] |==> | "Portsmouth FC" | "Stoke City" | 8 | ["Kevin Harper","Lewis Buxton","Anthony Pulis","Vincent Péricard","Asmir Begovic","Marc Wilson","Elliot Wheeler","Alex Grant"] |==> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+==> 10 rows |
Были ли ситуации, когда игроков переводили в обоих направлениях?
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
|
match (from)<-[:FROM_TEAM]-(t:Transfer)-[:TO_TEAM]->(to), (t)-[:PLAYER]->(player)where id(from) < id(to)WITH from, to, COUNT(*) AS times, COLLECT(player.name) AS playersmatch (to)<-[:FROM_TEAM]-(t:Transfer)-[:TO_TEAM]->(from), (t)-[:PLAYER]->(player)RETURN from.name, to.name, times, COUNT(*) as otherWayTimes, players, COLLECT(player.name) AS otherWayPlayersORDER BY times + otherWayTimes DESCLIMIT 10 ==> +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+==> | from.name | to.name | times | otherWayTimes | players | otherWayPlayers |==> +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+==> | "Tottenham Hotspur" | "Portsmouth FC" | 12 | 5 | ["Paul Walsh","Andy Turner","Rory Allen","Justin Edinburgh","Tim Sherwood","Teddy Sheringham","Noé Pamarot","Pedro Mendes","Sean Davis","Jermain Defoe","Younès Kaboul","Kevin-Prince Boateng"] | ["Jermain Defoe","Niko Kranjcar","Younès Kaboul","Peter Crouch","Darren Anderton"] |==> | "West Ham United" | "Liverpool FC" | 4 | 12 | ["Julian Dicks","Daniel Sjölund","Yossi Benayoun","Javier Mascherano"] | ["Stewart Downing","Andy Carroll","Joe Cole","Craig Bellamy","Rigobert Song","Titi Camara","Rob Jones","Neil Ruddock","Don Hutchison","Julian Dicks","Mike Marsh","David Burrows"] |==> | "West Ham United" | "Queens Park Rangers" | 13 | 2 | ["Keith Rowland","Iain Dowie","Tim Breacker","Ludek Miklosko","Bertie Brayley","Terrell Forbes","Steve Lomas","Hogan Ephraim","Nigel Quashie","Danny Gabbidon","Kieron Dyer","Robert Green","Gary O'Neil"] | ["Andy Impey","Trevor Sinclair"] |==> | "West Ham United" | "Tottenham Hotspur" | 5 | 8 | ["Jermain Defoe","Frédéric Kanouté","Michael Carrick","Jimmy Walker","Scott Parker"] | ["Sergiy Rebrov","Mauricio Taricco","Calum Davenport","Les Ferdinand","Matthew Etherington","Bobby Zamora","Ilie Dumitrescu","Mark Robson"] |==> | "West Ham United" | "Portsmouth FC" | 8 | 5 | ["Martin Allen","Adrian Whitbread","Marc Keller","Svetoslav Todorov","Hayden Foxe","Shaka Hislop","Sébastien Schemmel","Hayden Mullins"] | ["Stephen Henderson","Teddy Sheringham","Shaka Hislop","Marc Keller","Lee Chapman"] |==> | "Newcastle United" | "West Ham United" | 9 | 3 | ["Paul Kitson","Shaka Hislop","Stuart Pearce","Wayne Quinn","Lee Bowyer","Kieron Dyer","Scott Parker","Nolberto Solano","Kevin Nolan"] | ["Demba Ba","Lee Bowyer","David Terrier"] |==> | "Birmingham City" | "Coventry City" | 8 | 4 | ["David Rennie","John Gayle","Liam Daish","Gary Breen","Stern John","Julian Gray","Lee Carsley","Gary McSheffrey"] | ["Scott Dann","David Burrows","Peter Ndlovu","David Smith"] |==> | "Manchester City" | "Portsmouth FC" | 8 | 4 | ["Paul Walsh","Carl Griffiths","Fitzroy Simpson","Eyal Berkovic","David James","Andrew Cole","Sylvain Distin","Tal Ben Haim"] | ["Benjani","Gerry Creaney","Kit Symons","Paul Walsh"] |==> | "Blackburn Rovers" | "Southampton FC" | 5 | 6 | ["David Speedie","Stuart Ripley","James Beattie","Kevin Davies","Zak Jones"] | ["Zak Jones","Egil Östenstad","Kevin Davies","Alan Shearer","Jeff Kenna","Tim Flowers"] |==> | "AFC Bournemouth" | "West Ham United" | 3 | 8 | ["Keith Rowland","Paul Mitchell","Scott Mean"] | ["Steve Jones","Matt Holland","Mohammed Berthé","Scott Mean","Paul Mitchell","Jamie Victory","Mark Watson","Stephen Purches"] |==> +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
Есть ли игроки, которые возвращаются в тот же клуб, в котором они были ранее?
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
match (player:Player)<-[:PLAYER]-(t1:Transfer)-[:FROM_TEAM]->(from)<-[:TO_TEAM]-(t2:Transfer)-[:FROM_TEAM]->(to), (t2)-[:PLAYER]->(player), (t1)-[:TO_TEAM]->(to)WHERE ID(to) < ID(from)WITH player, COLLECT([ from.name, " ⥄ ", to.name]) AS teamsRETURN player.name, REDUCE(acc = [], item in teams | acc + REDUCE(acc2 = "", i in item | acc2 + i)) AS thereAndBackORDER BY LENGTH(thereAndBack) DESCLIMIT 10 ==> +-------------------------------------------------------------------------------------+==> | player.name | thereAndBack |==> +-------------------------------------------------------------------------------------+==> | "Mark Stein" | ["Stoke City ⥄ Chelsea FC","Ipswich Town ⥄ Chelsea FC"] |==> | "Peter Beagrie" | ["Bradford City ⥄ Everton FC","Bradford City ⥄ Wigan Athletic"] |==> | "Richard Dryden" | ["Southampton FC ⥄ Stoke City","Southampton FC ⥄ Swindon Town"] |==> | "Robbie Elliott" | ["Bolton Wanderers ⥄ Newcastle United"] |==> | "Elliot Grandin" | ["Blackpool FC ⥄ Crystal Palace"] |==> | "Robert Fleck" | ["Chelsea FC ⥄ Norwich City"] |==> | "Paul Walsh" | ["Portsmouth FC ⥄ Manchester City"] |==> | "Rick Holden" | ["Manchester City ⥄ Oldham Athletic"] |==> | "Gary McAllister" | ["Liverpool FC ⥄ Coventry City"] |==> | "Lee Bowyer" | ["West Ham United ⥄ Newcastle United"] |==> +-------------------------------------------------------------------------------------+ |
Это все, что у меня есть на данный момент — если вы можете придумать какие-либо другие интересные пути для изучения, дайте мне знать, и я посмотрю.
| Ссылка: | Neo4j: Футбол передает график от нашего партнера JCG Марка Нидхэма в блоге Марка Нидхэма . |