ON CREATE SET w += {{ games:toInteger(row.G), minutes:toFloat(row.MP), points:toFloat(row.PTS),
assists:toFloat(row.AST), rebounds:toFloat(row.TRB), steals:toFloat(row.STL),
blocks:toFloat(row.BLK), `ft%`:toFloat(row.FTP), `fg%`:toFloat(row.FGP), `3p%`:toFloat(row.`3PP`) }}
WITH p,w
MATCH (p)-[l:PLAYED_AT]->(r:Roster{{year:w.year}})
SET w.age = l.age
"""
CREATE_COACH_AWARD = """
MERGE (a:Award{name:"Coach of the Year", short:"COY"})
WITH a
LOAD CSV WITH HEADERS FROM "file:///awards/COY.csv" AS row
MATCH (c:Coach{name:row.Coach}), (t:Team)
WHERE row.Tm IN LABELS(t)
WITH t, c, a, row
MERGE (c)-[w:WON{year:toInteger(row.Season)}]->(a)
ON CREATE SET w += { games:toInteger(row.G), wins:toInteger(row.W), losses:toInteger(row.L),
`win%`:toFloat(row.`W/LP`), team:t.name}
"""
award_queries = []
for key, value in PERSONAL_AWARDS.items():
award_queries.append(CREATE_PERSONAL_AWARDS.format(name=key, short=value))
award_queries.append(CREATE_COACH_AWARD)
award_graph_updater = GraphUpdater("award", award_queries)
WITH row, toInteger("{year}") as year
WHERE NOT row.Tm = "TOT"
MATCH (t:Team)
WHERE row.Tm IN LABELS(t)
MERGE (r:Roster{{team:t.name, year:year}})-[:OF_TEAM]->(t)
MERGE (p:Player{{name:row.Player}})
MERGE (p)-[m:PLAYED_AT]->(r)
// Player regular season stats
ON CREATE SET m = {{ position: row.Pos, age: toInteger(row.Age), games:toInteger(row.G), year:year,
games_started:toInteger(row.GS), minutes:toFloat(row.MP), points:toFloat(row.PTS), assists:toFloat(row.AST),
rebounds:toFloat(row.TRB), steals:toFloat(row.STL), blocks:toFloat(row.BLK), `ft%`:toFloat(row.FTP),
`fg%`:toFloat(row.FGP), `2p%`:toFloat(row.`2PP`), `3p%`:toFloat(row.`3PP`) }}
WITH row, r, t
CALL apoc.create.addLabels(r, [row.Tm, t.short]) YIELD node
RETURN node
"""
ADD_PLAYER_CURRENT_AGE = """
MATCH (p:Player)-[r:PLAYED_AT{{year:{current_year}}}]->(:Roster)
SET p.age = r.age
"""
players_queries = []
for year in SUPPORTED_YEARS:
players_queries.append(CREATE_PLAYERS_AND_ROSTERS.format(year=str(year)))
players_queries.append(ADD_PLAYER_CURRENT_AGE.format(current_year=CURRENT_YEAR))
player_graph_updater = GraphUpdater("player", players_queries)
from source.core_objects.graph_updater import GraphUpdater
ADD_AWARD_PROPERTY = """
MATCH (m:Award)<-[w:WON]-(a)
WHERE a:Coach OR a:Player
WITH toLower(m.short) AS award, w, a
WITH replace(award," ","_") AS award_name, w, a
ORDER BY w.year
WITH COLLECT(w.year) AS v, award_name, a
CALL apoc.do.when(SIZE(v) > 1,
'CALL apoc.create.setProperty([a], award_name, v) YIELD node RETURN NULL',
'CALL apoc.create.setProperty([a], award_name, v[0]) YIELD node RETURN NULL',
{a:a, award_name:award_name, v:v}) YIELD value RETURN NULL
"""
award_properties_graph_updater = GraphUpdater("award properties",
[ADD_AWARD_PROPERTY])
MERGE (home_team)-[:HOSTED]->(g1:Home:Games:RegularSeason{{team:home_team.team, year:home_team.year}})
MERGE (away_team)-[:VISITED]->(g2:Road:Games:RegularSeason{{team:away_team.team, year:away_team.year}})
WITH g1, g2, n
CALL apoc.do.when(n.home_team_score > n.away_team_score,
'MERGE (g1)-[:WON]->(n)<-[:LOST]-(g2)',
'MERGE (g1)-[:LOST]->(n)<-[:WON]-(g2)',
{{g1:g1, g2:g2, n:n}})
YIELD value AS v RETURN NULL
"""
DELETE_NEW_LABEL = """
MATCH (n:New)
REMOVE n:New
"""
queries = []
for year in SUPPORTED_YEARS:
if year in EXCEPTIONAL_YEAR_GAMES.keys():
queries.append(
CREATE_ALL_GAMES.format(year=year,
games=EXCEPTIONAL_YEAR_GAMES[year]))
else:
queries.append(CREATE_ALL_GAMES.format(year=year, games=1230))
queries.append(CREATE_HOME_AWAY_GAMES.format(year=year))
queries.append(DELETE_NEW_LABEL)
regular_season_game_graph_updater = GraphUpdater("regular season game",
queries)
from source.constants import SUPPORTED_YEARS
from source.core_objects.graph_updater import GraphUpdater
CREATE_COACHES = """
LOAD CSV WITH HEADERS FROM "file:///coaches/{year}_coaches.csv" AS row
MATCH (roster:Roster{{year:{year}}})
WHERE row.team_label in LABELS(roster)
MERGE (c:Coach{{name:row.name}})
MERGE (c)-[r:COACHED]->(roster)
ON CREATE SET r = {{
games:toInteger(row.games),
wins:toInteger(row.wins),
losses:toInteger(row.losses)
}}
WITH r, toInteger((r.wins / toFloat(r.games) * 1000))/1000.0 as winning
SET r.`win%` = winning
"""
coaches_queries = []
for year in SUPPORTED_YEARS:
coaches_queries.append(CREATE_COACHES.format(year=str(year)))
coach_graph_updater = GraphUpdater("coach", coaches_queries)
ADD_SEASONS_PLAYED = """
MATCH (p:Player)-[r:PLAYED_AT]->(:Roster)
WITH SIZE(COLLECT(DISTINCT r.year)) as s, p
SET p.seasons_played = s
"""
ADD_LOYALTY = """
MATCH (p:Player)-[l:PLAYED_AT]->(:Roster)
WHERE SIZE(p.teams_played) = 1 AND p.seasons_played >= 5
SET p:Loyal
"""
ADD_NICKNAMES = """
LOAD CSV WITH HEADERS FROM "file:///nicknames.csv" AS row
MATCH (p:Player{name:row.name})
SET p.nickname = row.nick
"""
initial_properties_graph_updater = GraphUpdater("initial properties", [
ADD_ROSTER_WINS_AND_LOSSES, ADD_PLAYER_POSITION_AND_TEAMS,
ADD_PLAYER_CAREER_STATS, ADD_COACH_PROPERTIES,
ADD_WINNING_PERCENTAGE.format(label="Coach",
wins="total_wins",
games="total_games",
win_name="total_win%"),
ADD_WINNING_PERCENTAGE.format(
label="Roster", wins="wins", games="games", win_name="win%"),
ADD_SEASONS_PLAYED, ADD_LOYALTY, ADD_NICKNAMES
])
from source.core_objects.graph_updater import GraphUpdater
ADD_CHAMPIONS_LABEL = """
MATCH (r:Roster)-[:WON]->(:Series:Playoff{round: "NBA Finals"})
SET r:Champions
"""
ADD_TEAM_CHAMPIONS = """
MATCH (r:Roster:Champions)-[:OF_TEAM]->(t:Team)
WITH COLLECT(r.year) AS years, t
SET t.titles=years
"""
ADD_PLAYER_COACH_CHAMPIONS = """
MATCH (r:Roster:Champions)<-[]-(a)
WHERE a:Coach OR a:Player
WITH a, COLLECT(r.year) AS years
SET a.titles = years
"""
champion_graph_updater = GraphUpdater(
"champion",
[ADD_CHAMPIONS_LABEL, ADD_TEAM_CHAMPIONS, ADD_PLAYER_COACH_CHAMPIONS])
WHERE toUpper(r1.team) = row.home_team AND toUpper(r2.team) = row.away_team
MATCH (r1)--(s:Series:Playoff)--(r2)
WITH date(row.start_time) AS date, row, r1, r2, s
MERGE (g:Game:Playoff{{date:date, away_team:r2.team, home_team:r1.team,
away_team_score:toInteger(row.away_team_score) ,home_team_score:toInteger(row.home_team_score)}})
MERGE (g)-[:OF_SERIES]->(s)
SET g.final_score = g.home_team_score + '-' + g.away_team_score
"""
ADD_PLAYOFF_GAME_WINNER = """
MATCH (g:Game:Playoff)
CALL apoc.do.when(g.home_team_score > g.away_team_score,
'SET g.winner = g.home_team, g.loser = g.away_team',
'SET g.winner = g.away_team, g.loser = g.home_team',
{g:g}) YIELD value RETURN NULL
"""
queries = []
for year in SUPPORTED_YEARS:
if year in EXCEPTIONAL_YEAR_GAMES.keys():
queries.append(
CREATE_PLAYOFF_GAMES.format(year=year,
games=EXCEPTIONAL_YEAR_GAMES[year]))
else:
queries.append(CREATE_PLAYOFF_GAMES.format(year=year, games=1230))
queries.append(ADD_PLAYOFF_GAME_WINNER)
playoff_game_graph_updater = GraphUpdater("playoff game", queries)
MATCH (p)<-[:OF_SERIES]-(g:Game:Playoff)
WITH p, g
ORDER BY g.date
WITH COLLECT(g) AS games, p
UNWIND games AS game
WITH apoc.coll.indexOf(games, game) + 1 as number, game, p
SET game.game_number = number
"""
ADD_PLAYOFF_SERIES_DATE = """
MATCH (s:Playoff:Series)<-[:OF_SERIES]-(g:Game:Playoff)
WITH g, s ORDER BY g.game_number
WITH COLLECT(g) AS games, s
WITH games[0] AS p, games[-1] AS pp, s
SET s.date = p.date.day + '.' + p.date.month + '-' + pp.date.day + '.' + pp.date.month, s.year = p.date.year
"""
game_properties_graph_updater = GraphUpdater("game properties", [
SET_GAMES_TEAM_LABEL, SET_GAME_WINNER_AND_FINAL_SCORE,
ADD_GAMES_WINS_LOSSES.format(relation='WON', prop='wins'),
ADD_GAMES_WINS_LOSSES.format(relation='LOST', prop='losses'),
ADD_GAMES_FINAL_SCORE, ADD_GAMES_WIN_PERCENTAGE,
ADD_ROSTER_HOME_ROAD_STATS.format(game_label='Road', game_type='road'),
ADD_ROSTER_HOME_ROAD_STATS.format(game_label='Home', game_type='home'),
ADD_ROSTER_CHARACTER.format(
prop='home_win%', min='0.85', character='HomeGuards'),
ADD_ROSTER_CHARACTER.format(
prop='road_win%', min='0.7', character='RoadHunters'),
ADD_PLAYOFF_GAME_NUMBER, ADD_PLAYOFF_SERIES_DATE
])
MATCH (p:Player{{name:name}})
CALL apoc.create.setProperties([p],["draft_pick","draft_class","pre_draft_team"],
[toInteger(row.{pick}),toInteger({year}), row.Pre]) YIELD node
RETURN node
"""
ADD_OLD_DRAFTS = """
LOAD CSV WITH HEADERS FROM "file:///draft/1993_2017_drafts.csv" AS row
WITH SPLIT(row.player, ",") AS names, row
WITH names[1] + " " + names[0] AS name, row
""" + CREATE_DRAFT_PROPERTIES
ADD_SPECIFIC_DRAFT = """
LOAD CSV WITH HEADERS FROM "file:///draft/{year}_draft.csv" AS row
WITH row, row.Player as name
""" + CREATE_DRAFT_PROPERTIES
ADD_PRE_DRAFT_TEAM = """
MATCH (p:Player)
WHERE EXISTS(p.pre_draft_team)
MERGE (r:Team:PreDraft{name:p.pre_draft_team})
MERGE (p)-[:PLAYED_AT]->(r)
"""
draft_graph_updater = GraphUpdater("draft", [
ADD_SPECIFIC_DRAFT.format(year="1992", pick="Pk"),
ADD_OLD_DRAFTS.format(year="row.year", pick="pick"),
ADD_SPECIFIC_DRAFT.format(year="2018", pick="Pk"),
ADD_SPECIFIC_DRAFT.format(year="2019", pick="Pk"), ADD_PRE_DRAFT_TEAM
])
ADD_ADDITIONAL_LABELS = """
MATCH (t:Team{{short:{short}}})
CALL apoc.create.addLabels(t,{labels}) YIELD node
RETURN node
"""
CREATE_DIVISIONS_AND_CONFERENCES = """
LOAD CSV WITH HEADERS FROM "file:///divisions.csv" AS row
MERGE (c:Conference{name:row.conference})
MERGE (d:Division{name:row.division})-[:FROM]->(c)
WITH row, c, d
MATCH (t:Team{name:row.name})
MERGE (t)-[:FROM]->(d)
WITH row, c, d, t
CALL apoc.create.addLabels([t,d,c],[row.conference]) YIELD node AS m
CALL apoc.create.setProperty([t,d], "conference", [row.conference]) YIELD node AS n
CALL apoc.create.setProperty([t], "division", [row.division]) YIELD node AS o
RETURN o
"""
team_queries = [CREATE_NBA_TEAMS]
for team, labels in INVALID_TEAMS_LABELS.items():
team_queries.append(ADD_ADDITIONAL_LABELS.format(short=team,
labels=labels))
team_queries.append(CREATE_DIVISIONS_AND_CONFERENCES)
team_graph_updater = GraphUpdater("team", team_queries)
from source.constants import CURRENT_YEAR
from source.core_objects.graph_updater import GraphUpdater
ADD_ROOKIE_LABEL = """
MATCH (p:Player)-[r:PLAYED_AT]->(:Roster)
WHERE r.year = 2020 AND p.seasons_played = 1
SET p:Rookie
"""
rookie_graph_updater = GraphUpdater(
"rookie", [ADD_ROOKIE_LABEL.format(year=str(CURRENT_YEAR - 1))])
WITH s, row, a, b
SET a.wins = toInteger(row.Wins), a.losses = toInteger(row.Losses),
b.wins = toInteger(row.Losses), b.losses = toInteger(row.Wins)
WITH s,row, a
CALL apoc.create.addLabels([s],[row.Conference]) YIELD node AS ser
SET s.games= a.wins + a.losses
"""
ADD_FINAL_SCORE = """
MATCH (s:Series:Playoff)<-[r:WON]-(:Roster)
WITH toString(r.wins) + "-" + toString(r.losses) AS games, s
SET s.final_score = games
"""
ADD_IS_SWEEP = """
MATCH (s:Series:Playoff)
CALL apoc.do.when(s.games = 4,
'SET s.is_sweep=true, s:Sweep',
'SET s.is_sweep=false',{s:s}) YIELD value
RETURN value
"""
playoff_series_graph_updater = GraphUpdater("playoff series", [
ADD_PLAYOFF_SERIES,
ADD_FINAL_SCORE,
ADD_IS_SWEEP
])