GraphGists

疯狂三月推荐引擎

5nlEMrk

(March Madness)←[:MADE_SANE_WITH]-(Neo4j {version:2.2})

疯狂三月是极少数在社交场合成为数据极客能获得回报的时刻之一。它是文档齐全的数据和流行文化罕见的结合。沃伦·巴菲特的十亿美元赌局吸引了从华尔街量化分析师到硅谷工程师，再到各地的点球成金式业余分析师的兴趣。

一切皆相对

对我来说，篮球关乎关系——当然，有些球队明显比其他球队更强。然而，几乎总是存在某种相对表现偏差。由于某些因素的共同作用，球队的表现会比其平均表现预测的更好或更差，无论是因为臭名昭著的球迷观众，还是因为能瓦解对方领先区域联防的控球后卫，抑或是长达数十年的宿怨激励球员更加努力。表现是相对的。这些统计数据在一整个赛季中难以追踪，跨越时间则更加困难。

其次，迭代该模型无论是编写查询还是保持合理的性能都非常耗时。我积累了过去四个赛季的大量数据（约 50,000 场比赛），包括得分、地点、日期等等。我们可以轻松添加更精细的信息或更历史的数据——虽然并非出于真正的统计原因，只为方便我自己，我决定在我的模型中，这些“胜负关系”几乎每四年就完全更新一次（因为现役球员毕业并离开）。我们稍后将详细阐述这些作为毕达哥拉斯期望模型函数的球队之间的“胜负”关系。

步骤

步骤 1：从想法 —> 图模型

我不是个聪明的小伙子。

然而，我拥有几个很聪明的工具。其中最主要的工具是 Neo4j（Neo Technology 的图数据库）。所以，我像处理所有图项目一样开始——从我计划最常问的问题和一块白板（或在这种情况下是一张纸）开始。

表 1. 想法
eT6VsHM

稍加努力后变为

表 2. 想法 v2
zvTPnin

步骤 2：及时赶到 时间！

在将任何数据加载到 Neo4j 之前，我首先需要创建上述模型中的时间树。Neo4j 一位出色的工程师为我完成了繁重的工作，并编写了一段简短的 Cypher 代码片段来生成我需要的时间模型。

我从他关于创建时间树的博客文章中借鉴了这段代码。

WITH range(2013, 2015) AS years, range(1,12) as months
FOREACH(year IN years |
  MERGE (y:Year {year: year})
  FOREACH(month IN months |
    CREATE (m:Month {month: month})
    MERGE (y)-[:HAS_MONTH]->(m)
    FOREACH(day IN (CASE
                      WHEN month IN [1,3,5,7,8,10,12] THEN range(1,31)
                      WHEN month = 2 THEN
                        CASE
                          WHEN year % 4 <> 0 THEN range(1,28)
                          WHEN year % 100 <> 0 THEN range(1,29)
                          WHEN year % 400 <> 0 THEN range(1,29)
                          ELSE range(1,28)
                        END
                      ELSE range(1,30)
                    END) |
      CREATE (d:Day {day: day})
      MERGE (m)-[:HAS_DAY]->(d))))
WITH *
MATCH (year:Year)-[:HAS_MONTH]->(month)-[:HAS_DAY]->(day)
WITH year,month,day
ORDER BY year.year, month.month, day.day
WITH collect(day) as days
FOREACH(i in RANGE(0, length(days)-2) |
    FOREACH(day1 in [days[i]] |
        FOREACH(day2 in [days[i+1]] |
            CREATE UNIQUE (day1)-[:NEXT]->(day2))));

结果看起来像这样

表 3. 我的时间模型
lTG2TKf

步骤 3: my.csv —> Graph.db

Neo4j 附带了一个非常强大的 ETL 工具，称为 LOAD CSV。我们将使用它。我下载了一堆 NCAA 比分，然后巧妙地将下载的 Excel 电子表格数据转换为 CSV 格式。我将它们托管在公共 Dropbox 中，链接可在上面链接的 GitHub 仓库中找到。我们将导入几个 CSV 文件，每个文件代表一个给定赛季，然后根据球队名称将它们连接在一起。因为这只是一个 Gist，我们将只加载大约一千场比赛，而不是过去四个赛季的 50,000 场。

//Loading 750 games from the 2015 Regular Season
LOAD CSV WITH HEADERS from 'https://dl.dropboxusercontent.com/u/313565755/ncaa2015.csv' AS line
WITH line, toINT(line.Year) as Year, toINT(line.Month) as Month, toINT(line.Day) as Day
LIMIT 750
WHERE line.ignore IS NOT NULL
MATCH (:Year {year:Year})-[:HAS_MONTH]->(:Month {month:Month})-[:HAS_DAY]->(t:Day {day:Day})
CREATE (game:Game {winner:line.winnerName})-[:OCCURED_ON]->(t)
WITH line, game
MERGE (team:Team {name:line.Team})
WITH line, game, team
MERGE(opp:Team {name:line.Opponent})
WITH line, game, team, opp
WITH line, toINT(line.opponentScore) as oppScore, toINT(line.teamScore) as teamScore, team, opp, game, toFLOAT(line.teamDiff) as teamDiff, toFLOAT(line.oppDiff) as oppDiff
CREATE (team)-[:PLAYED_IN {scored:teamScore, differential:teamDiff, location:line.teamLocation}]->(game)<-[:PLAYED_IN {scored:oppScore, differential:oppDiff, location:line.oppLocation}]-(opp)

步骤 4：历史、胜利和一点数学

我决定根据棒球中的一个概念（毕达哥拉斯期望）在每支球队之间创建一种名为 :WINPOWER 的关系。它本质上是根据得分与失分来分配胜率。我加入了一个衰减因子，以赋予最近的比赛比很久以前的比赛更大的权重。

表 4. 毕达哥拉斯期望
b0da4b28fff6eb1edcdc59da5cff7934 
//Assigning Pythagorean Expectation
MATCH (a:Team)-[aa:PLAYED_IN]->(game)<-[bb:PLAYED_IN]-(b:Team)
WITH toFloat(aa.scored*aa.scored) as team2, toFloat(bb.scored*bb.scored) as opp2, game, a,b
//
//Remember that Pythagorean Expectation is (points_scored^2 / (points scored^2 + points allowed^2))
//
WITH ((team2)/(team2+opp2)) as PyEx, game,a,b
//
// tying the game to the correct day in our time tree
MATCH (game)-[:OCCURED_ON]->(day)<-[:HAS_DAY]-(month)<-[:HAS_MONTH]-(year)
//
//setting March 15th to the day before the tournament and calculating how many days ago the last game was played
WITH (365*2015 + 2015 /4 - 2015 /100 + 2015 /400 + 15 + (153*3+8)/5) as dayBeforeTournament,
(365*(year.year) + (year.year)/4 - (year.year)/100 + (year.year)/400 + (day.day) + (153*(month.month)+8)/5) as oldYear, PyEx,a,b
//
//assuming that "win relevance" decays linearly over 4 years or how long any part of those same teams are playing against one another represented by "weight"
//
WITH ((4*365.25)-(dayBeforeTournament-oldYear))/(4*365.25) as weight, PyEx, a, b
//
//adding up all of the weights*pythagorean expectation for a current win probability
//
WITH SUM(weight*PyEx) as winPower, a, b
//creating a new relationship that stores a team's given probability of defeating another team as of March 15th
//
MERGE (a)-[w:WINPOWER]->(b)
SET w.winPower = winPower;
UBcDDF6 
MATCH (a)-[w:WINPOWER]->(b)
RETURN a.name as `Team 1`, w.winPower as `Winpower Against`, b.name as `Team 2`
ORDER BY w.winPower DESC
LIMIT 10

步骤 5：丰厚回报

现在是激动人心的部分了，让我们通过几场比赛来看看能否准确预测谁会赢。

比赛 1：海军 vs 密歇根州立大学

//bracketmaker, bracketmaker, make me a billion
//
CREATE (g:SimulatedGame)
WITH g
MATCH (a:Team)-[x:WINPOWER]->(b:Team), (a)<-[y:WINPOWER]-(b)
WHERE a.name = 'Navy' AND b.name = 'Michigan St'
MERGE (a)-[:SIMULATED]->(g)<-[:SIMULATED]-(b)
WITH b,a,g,x,y,
CASE
WHEN x.winPower > y.winPower
THEN a.name
ELSE b.name
END AS winName
SET g.winner=winName
RETURN a.name AS `Team 1`, x.winPower AS `Team 1 WP`, b.name AS `Team 2`, y.winPower AS `Team 2 WP`,  g.winner AS `Winner of Matchup`
MATCH (a)-[:SIMULATED]->(g)<-[:SIMULATED]-(b)
WHERE a.name = 'Navy' AND b.name = 'Michigan St'
RETURN a, b, g;

显然我们看到密歇根州立大学是赢家。

比赛 2：肯塔基 vs UT 阿灵顿

//bracketmaker, bracketmaker, make me a billion
//
MATCH (a)-[:PLAYED_IN]->(g:Game)<-[:PLAYED_IN]-(b)
WHERE a.name = 'Kentucky' AND b.name = 'UT Arlington'
RETURN a, g, b

糟糕……没有结果。这意味着在我们的常规赛期间，肯塔基和 UT 阿灵顿没有互相比赛（在过去四年的 NCAA 锦标赛中也没有互相比赛）

好吧，为什么我们不比较他们之前都交手过的球队，然后取他们“胜负分数”的平均值或总和呢？例如，我们可以推断，如果肯塔基总是赢密歇根州立大学，而密歇根州立大学总是赢汉普顿，那么肯塔基很可能会赢汉普顿。我们可以轻松比较所有共同交手过的球队以及他们在对阵这些球队时的表现，从而对谁应该获胜做出很好的猜测。有了完整的数据集，我们可以在 64 强赛中全面进行这项工作，然而使用截断的“Graph Gist”数据集，我挑选了一个示例来说明。

//What if they've never played each other?!
CREATE (g:SimulatedGame)
WITH g
MATCH (a:Team)-[aa:WINPOWER]->(intermediate:Team)<-[bb:WINPOWER]-(b:Team)
WHERE a.name = 'Kentucky' AND b.name = 'UT Arlington'
WITH g, a, aa, b, bb,
CASE
WHEN SUM(aa.winPower) > SUM(bb.winPower)
THEN a.name
ELSE b.name
END AS winName
SET g.winner=winName
WITH*
MERGE (a)-[:SIMULATED]->(g)<-[:SIMULATED]-(b)
WITH*
RETURN a.name AS `Team 1`, sum(aa.winPower) AS `Team 1 WP`, b.name AS `Team 2`, sum(bb.winPower) AS `Team 2 WP`, g.winner AS `Winner of Matchup`

结束

Z2t3Hee medium

参考

下载原始数据集和本文中所有代码片段：https://github.com/kvangundy/graph-graph-baby/

疯狂三月推荐引擎

Kevin Van Gundy <kevin@neotechnology.com> :neo4j-version: 2.3.0 :author: Kevin Van Gundy :twitter: @kevinvangundy

5nlEMrk

(March Madness)←[:MADE_SANE_WITH]-(Neo4j {version:2.2})

疯狂三月是极少数在社交场合成为数据极客能获得回报的时刻之一。它是文档齐全的数据和流行文化罕见的结合。沃伦·巴菲特的十亿美元赌局吸引了从华尔街量化分析师到硅谷工程师，再到各地的点球成金式业余分析师的兴趣。

一切皆相对

对我来说，篮球关乎关系——当然，有些球队明显比其他球队更强。然而，几乎总是存在某种相对表现偏差。由于某些因素的共同作用，球队的表现会比其平均表现预测的更好或更差，无论是因为臭名昭著的球迷观众，还是因为能瓦解对方领先区域联防的控球后卫，抑或是长达数十年的宿怨激励球员更加努力。表现是相对的。这些统计数据在一整个赛季中难以追踪，跨越时间则更加困难。

其次，迭代该模型无论是编写查询还是保持合理的性能都非常耗时。我积累了过去四个赛季的大量数据（约 50,000 场比赛），包括得分、地点、日期等等。我们可以轻松添加更精细的信息或更历史的数据——虽然并非出于真正的统计原因，只为方便我自己，我决定在我的模型中，这些“胜负关系”几乎每四年就完全更新一次（因为现役球员毕业并离开）。我们稍后将详细阐述这些作为毕达哥拉斯期望模型函数的球队之间的“胜负”关系。

步骤

步骤 1：从想法 —> 图模型

我不是个聪明的小伙子。

然而，我拥有几个很聪明的工具。其中最主要的工具是 Neo4j（Neo Technology 的图数据库）。所以，我像处理所有图项目一样开始——从我计划最常问的问题和一块白板（或在这种情况下是一张纸）开始。

表 5. 想法
eT6VsHM

稍加努力后变为

表 6. 想法 v2
zvTPnin

步骤 2：及时赶到 时间！

在将任何数据加载到 Neo4j 之前，我首先需要创建上述模型中的时间树。Neo4j 一位出色的工程师为我完成了繁重的工作，并编写了一段简短的 Cypher 代码片段来生成我需要的时间模型。

我从他关于创建时间树的博客文章中借鉴了这段代码。

WITH range(2013, 2015) AS years, range(1,12) as months
FOREACH(year IN years |
  MERGE (y:Year {year: year})
  FOREACH(month IN months |
    CREATE (m:Month {month: month})
    MERGE (y)-[:HAS_MONTH]->(m)
    FOREACH(day IN (CASE
                      WHEN month IN [1,3,5,7,8,10,12] THEN range(1,31)
                      WHEN month = 2 THEN
                        CASE
                          WHEN year % 4 <> 0 THEN range(1,28)
                          WHEN year % 100 <> 0 THEN range(1,29)
                          WHEN year % 400 <> 0 THEN range(1,29)
                          ELSE range(1,28)
                        END
                      ELSE range(1,30)
                    END) |
      CREATE (d:Day {day: day})
      MERGE (m)-[:HAS_DAY]->(d))))
WITH *
MATCH (year:Year)-[:HAS_MONTH]->(month)-[:HAS_DAY]->(day)
WITH year,month,day
ORDER BY year.year, month.month, day.day
WITH collect(day) as days
FOREACH(i in RANGE(0, length(days)-2) |
    FOREACH(day1 in [days[i]] |
        FOREACH(day2 in [days[i+1]] |
            CREATE UNIQUE (day1)-[:NEXT]->(day2))));

结果看起来像这样

表 7. 我的时间模型
lTG2TKf

步骤 3: my.csv —> Graph.db

Neo4j 附带了一个非常强大的 ETL 工具，称为 LOAD CSV。我们将使用它。我下载了一堆 NCAA 比分，然后巧妙地将下载的 Excel 电子表格数据转换为 CSV 格式。我将它们托管在公共 Dropbox 中，链接可在上面链接的 GitHub 仓库中找到。我们将导入几个 CSV 文件，每个文件代表一个给定赛季，然后根据球队名称将它们连接在一起。因为这只是一个 Gist，我们将只加载大约一千场比赛，而不是过去四个赛季的 50,000 场。

//Loading 750 games from the 2015 Regular Season
LOAD CSV WITH HEADERS from 'https://dl.dropboxusercontent.com/u/313565755/ncaa2015.csv' AS line
WITH line, toINT(line.Year) as Year, toINT(line.Month) as Month, toINT(line.Day) as Day
LIMIT 750
WHERE line.ignore IS NOT NULL
MATCH (:Year {year:Year})-[:HAS_MONTH]->(:Month {month:Month})-[:HAS_DAY]->(t:Day {day:Day})
CREATE (game:Game {winner:line.winnerName})-[:OCCURED_ON]->(t)
WITH line, game
MERGE (team:Team {name:line.Team})
WITH line, game, team
MERGE(opp:Team {name:line.Opponent})
WITH line, game, team, opp
WITH line, toINT(line.opponentScore) as oppScore, toINT(line.teamScore) as teamScore, team, opp, game, toFLOAT(line.teamDiff) as teamDiff, toFLOAT(line.oppDiff) as oppDiff
CREATE (team)-[:PLAYED_IN {scored:teamScore, differential:teamDiff, location:line.teamLocation}]->(game)<-[:PLAYED_IN {scored:oppScore, differential:oppDiff, location:line.oppLocation}]-(opp)

步骤 4：历史、胜利和一点数学

我决定根据棒球中的一个概念（毕达哥拉斯期望）在每支球队之间创建一种名为 :WINPOWER 的关系。它本质上是根据得分与失分来分配胜率。我加入了一个衰减因子，以赋予最近的比赛比很久以前的比赛更大的权重。

表 8. 毕达哥拉斯期望
b0da4b28fff6eb1edcdc59da5cff7934 
//Assigning Pythagorean Expectation
MATCH (a:Team)-[aa:PLAYED_IN]->(game)<-[bb:PLAYED_IN]-(b:Team)
WITH toFloat(aa.scored*aa.scored) as team2, toFloat(bb.scored*bb.scored) as opp2, game, a,b
//
//Remember that Pythagorean Expectation is (points_scored^2 / (points scored^2 + points allowed^2))
//
WITH ((team2)/(team2+opp2)) as PyEx, game,a,b
//
// tying the game to the correct day in our time tree
MATCH (game)-[:OCCURED_ON]->(day)<-[:HAS_DAY]-(month)<-[:HAS_MONTH]-(year)
//
//setting March 15th to the day before the tournament and calculating how many days ago the last game was played
WITH (365*2015 + 2015 /4 - 2015 /100 + 2015 /400 + 15 + (153*3+8)/5) as dayBeforeTournament,
(365*(year.year) + (year.year)/4 - (year.year)/100 + (year.year)/400 + (day.day) + (153*(month.month)+8)/5) as oldYear, PyEx,a,b
//
//assuming that "win relevance" decays linearly over 4 years or how long any part of those same teams are playing against one another represented by "weight"
//
WITH ((4*365.25)-(dayBeforeTournament-oldYear))/(4*365.25) as weight, PyEx, a, b
//
//adding up all of the weights*pythagorean expectation for a current win probability
//
WITH SUM(weight*PyEx) as winPower, a, b
//creating a new relationship that stores a team's given probability of defeating another team as of March 15th
//
MERGE (a)-[w:WINPOWER]->(b)
SET w.winPower = winPower;
UBcDDF6 
MATCH (a)-[w:WINPOWER]->(b)
RETURN a.name as `Team 1`, w.winPower as `Winpower Against`, b.name as `Team 2`
ORDER BY w.winPower DESC
LIMIT 10

步骤 5：丰厚回报

现在是激动人心的部分了，让我们通过几场比赛来看看能否准确预测谁会赢。

比赛 1：海军 vs 密歇根州立大学

//bracketmaker, bracketmaker, make me a billion
//
CREATE (g:SimulatedGame)
WITH g
MATCH (a:Team)-[x:WINPOWER]->(b:Team), (a)<-[y:WINPOWER]-(b)
WHERE a.name = 'Navy' AND b.name = 'Michigan St'
MERGE (a)-[:SIMULATED]->(g)<-[:SIMULATED]-(b)
WITH b,a,g,x,y,
CASE
WHEN x.winPower > y.winPower
THEN a.name
ELSE b.name
END AS winName
SET g.winner=winName
RETURN a.name AS `Team 1`, x.winPower AS `Team 1 WP`, b.name AS `Team 2`, y.winPower AS `Team 2 WP`,  g.winner AS `Winner of Matchup`
MATCH (a)-[:SIMULATED]->(g)<-[:SIMULATED]-(b)
WHERE a.name = 'Navy' AND b.name = 'Michigan St'
RETURN a, b, g;

显然我们看到密歇根州立大学是赢家。

比赛 2：肯塔基 vs UT 阿灵顿

//bracketmaker, bracketmaker, make me a billion
//
MATCH (a)-[:PLAYED_IN]->(g:Game)<-[:PLAYED_IN]-(b)
WHERE a.name = 'Kentucky' AND b.name = 'UT Arlington'
RETURN a, g, b

糟糕……没有结果。这意味着在我们的常规赛期间，肯塔基和 UT 阿灵顿没有互相比赛（在过去四年的 NCAA 锦标赛中也没有互相比赛）

好吧，为什么我们不比较他们之前都交手过的球队，然后取他们“胜负分数”的平均值或总和呢？例如，我们可以推断，如果肯塔基总是赢密歇根州立大学，而密歇根州立大学总是赢汉普顿，那么肯塔基很可能会赢汉普顿。我们可以轻松比较所有共同交手过的球队以及他们在对阵这些球队时的表现，从而对谁应该获胜做出很好的猜测。有了完整的数据集，我们可以在 64 强赛中全面进行这项工作，然而使用截断的“Graph Gist”数据集，我挑选了一个示例来说明。

//What if they've never played each other?!
CREATE (g:SimulatedGame)
WITH g
MATCH (a:Team)-[aa:WINPOWER]->(intermediate:Team)<-[bb:WINPOWER]-(b:Team)
WHERE a.name = 'Kentucky' AND b.name = 'UT Arlington'
WITH g, a, aa, b, bb,
CASE
WHEN SUM(aa.winPower) > SUM(bb.winPower)
THEN a.name
ELSE b.name
END AS winName
SET g.winner=winName
WITH*
MERGE (a)-[:SIMULATED]->(g)<-[:SIMULATED]-(b)
WITH*
RETURN a.name AS `Team 1`, sum(aa.winPower) AS `Team 1 WP`, b.name AS `Team 2`, sum(bb.winPower) AS `Team 2 WP`, g.winner AS `Winner of Matchup`

结束

Z2t3Hee medium

参考

下载原始数据集和本文中所有代码片段：https://github.com/kvangundy/graph-graph-baby/

© . All rights reserved.