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 ])