{"id":1618,"date":"2022-04-13T18:35:58","date_gmt":"2022-04-13T22:35:58","guid":{"rendered":"http:\/\/visual-baseball.com\/wordpress\/?p=1618"},"modified":"2025-08-22T14:24:20","modified_gmt":"2025-08-22T14:24:20","slug":"trade-network-updates-part-2-node-code","status":"publish","type":"post","link":"https:\/\/visual-baseball.com\/wordpress\/2022\/04\/13\/trade-network-updates-part-2-node-code\/","title":{"rendered":"Trade Network Updates, Part 2 (node code)"},"content":{"rendered":"

Over the last 10 days I’ve been playing around with code that will enable some new versions of the MLB trade networks I premiered way back in 2015. The goal this time around is to factor in the future value of a trade to each of the participating teams. There are multiple measures that could be used for this assessment but I’m going with the WAR162 value from Neil Paine’s 538 github<\/a> data source. Here’s how the site describes the War162 measure: JEFFBAGWELL WAR per 162 team games<\/em>. Now you may ask why Jeff Bagwell? While he was a talented hitter for many years, his name is used as an acronym for this:<\/p>\n

“The file “jeffbagwell_war_historical.csv” contains wins above replacement (WAR) data — according to JEFFBAGWELL (the Joint Estimate Featuring FanGraphs and B-R Aggregated to Generate WAR, Equally Leveling Lists), which averages together WAR from Baseball-Reference.com and FanGraphs — plus various other metrics for MLB since 1901.”<\/em> Fun stuff, right?<\/p>\n

The bottom line from my perspective is that this measure provides a robust way of assessing the value of a trade based on performance after the trade date. Did one team benefit while another team received a player who added no future value? Or did both teams make out equally well? Or was the trade of minimal value for both sides? These are the questions I’m attempting to visually address using network analysis and visualization.<\/p>\n

Now comes the technical aspect for all you database and code lovers. First step is to create the network nodes; in this case we need to display the individual trade transactions, teams, and players. Let’s look first at the transactions using the Visual-Baseball MySQL source data:<\/p>\n

SELECT CONCAT(a.Id, ‘-‘, a.PrimaryDate) as Id, CONCAT(‘Transaction ‘,a.Id,’ is from the ‘, a.Season, ‘ season’) AS Label,
\n‘Trade’ AS Type, SUM(a.Size) AS Size
\nFROM
\n(SELECT tr.TransactionID AS Id, tr.Season, tr.PrimaryDate, ROUND(SUM(h.WAR162),1) as Size
\nFROM historical_WAR_and_more h
\nINNER JOIN People p
\nON h.key_bbref = p.bbrefID
\nINNER JOIN trades2021 tr
\nON p.retroID = tr.Player
\nINNER JOIN Teams t
\nON tr.TeamTo = t.teamID<\/p>\n

WHERE tr.Season >= 1901 and h.year_ID >= tr.season and tr.Type = ‘T’ and tr.TeamTo = t.teamID and LENGTH(tr.TeamFrom) = 3
\nAND tr.Season = t.yearID and t.franchID = h.franch_ID<\/p>\n

GROUP BY tr.TransactionID, tr.season, tr.TeamFrom, tr.TeamTo, tr.PrimaryDate) a<\/p>\n

GROUP BY a.Id, a.PrimaryDate, a.Season;<\/em><\/p>\n

Here we are simply creating a node for each trade transaction, a label showing the teams involved and the trade date, and summing up the previously mentioned WAR162 to size the nodes. This will be an important part of the graph – trades that created large future values (for one or both teams) will be more prominent in the graph. Small value trades will be represented by very small nodes indicating their relative lack of importance. This one was a challenge, but finally got the code to deliver the expected results.<\/p>\n

The next step is to create team nodes; in this case we’ll provide a constant size:<\/p>\n

SELECT t.franchID AS Id, tf.franchName AS Label, 15 AS Size<\/p>\n

FROM historical_WAR_and_more h
\nINNER JOIN People p
\nON h.key_bbref = p.bbrefID
\nINNER JOIN trades2021 tr
\nON p.retroID = tr.Player
\nINNER JOIN Teams t
\nON tr.TeamFrom = t.teamID
\nINNER JOIN TeamsFranchises tf
\nON t.franchID = tf.franchID<\/p>\n

WHERE tr.season >= 1901 and h.year_ID >= tr.season and tr.Type = ‘T’ and h.team_ID = tr.TeamTo and LENGTH(tr.TeamFrom) = 3<\/p>\n

GROUP BY t.franchID, tf.franchName;<\/em><\/p>\n

By applying a constant node size of 15, each team will have a similar appearance in the graph which will not distract us from the trade values (some will be much larger than 15). <\/p>\n

Our third and final node step is to provide information on all players involved in one or more trades:<\/p>\n

SELECT Id, Label, ‘Player’ AS Type, 5 AS Size
\nFROM
\n(SELECT p.playerID AS Id,
\nCONCAT(h.player_name, ‘ (‘, p.birthYear,’-‘,p.deathYear,’)’,’ played from ‘,LEFT(p.debut,4),’ to ‘,LEFT(p.finalGame,4)) AS Label<\/p>\n

FROM historical_WAR_and_more h
\nINNER JOIN People p
\nON h.key_bbref = p.bbrefID
\nINNER JOIN trades2021 tr
\nON p.retroID = tr.Player<\/p>\n

WHERE tr.season >= 1901 and h.year_ID >= tr.season and tr.Type = ‘T’ and LENGTH(tr.TeamFrom) = 3
\nand LENGTH(tr.TeamTo) = 3
\nAND p.deathYear > 1900 <\/p>\n

GROUP BY h.player_name, p.playerID, p.birthYear, p.deathYear, p.debut, p.finalGame<\/p>\n

UNION ALL<\/p>\n

SELECT p.playerID AS Id,
\nCONCAT(h.player_name, ‘ (‘, p.birthYear,’-‘,’ )’,’ played from ‘,LEFT(p.debut,4),’ to ‘,LEFT(p.finalGame,4)) AS Label<\/p>\n

FROM historical_WAR_and_more h
\nINNER JOIN People p
\nON h.key_bbref = p.bbrefID
\nINNER JOIN trades2021 tr
\nON p.retroID = tr.Player<\/p>\n

WHERE tr.season >= 1901 and h.year_ID >= tr.season and tr.Type = ‘T’ and LENGTH(tr.TeamFrom) = 3
\nand LENGTH(tr.TeamTo) = 3
\nAND ISNULL(p.deathYear) <\/p>\n

GROUP BY h.player_name, p.playerID, p.birthYear, p.deathYear, p.debut, p.finalGame) a
\nGROUP BY Id, Label;<\/em><\/p>\n

Here we are running a UNION query so we can gather information about the players moving in each direction of a trade (from one team to another). We then combine that information and apply a fixed size of 5 since there are far more players than teams. We’ll have the ability in the finished networks to zoom in and see more about each player.<\/p>\n

Each of these 3 outputs (trades, teams, and players) is combined into a single input file that will feed Gephi. We should wind up with between 10k and 20k nodes which we’ll be able to filter
\nand zoom on in the network graph. I have high hopes for this set of networks (there may be one for each team as well as a comprehensive one) as it should really help display the most important trades in MLB history.<\/p>\n

That’s it for our node creation process; the next post will share how we create the edges that will connect trades to teams and teams to players. Thanks for reading!<\/p>\n","protected":false},"excerpt":{"rendered":"

Over the last 10 days I’ve been playing around with code that will enable some new versions of the MLB trade networks I premiered way back in 2015. The goal this time around is to factor in the future value of a trade to each of the participating teams. There are multiple measures that could […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10,12,8],"tags":[15,16,33],"class_list":["post-1618","post","type-post","status-publish","format-standard","hentry","category-data","category-network-graphs","category-trade-networks","tag-code","tag-gephi","tag-nodes"],"yoast_head":"\nTrade Network Updates, Part 2 (node code) - Visual-Baseball<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/visual-baseball.com\/wordpress\/2022\/04\/13\/trade-network-updates-part-2-node-code\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Trade Network Updates, Part 2 (node code) - Visual-Baseball\" \/>\n<meta property=\"og:description\" content=\"Over the last 10 days I’ve been playing around with code that will enable some new versions of the MLB trade networks I premiered way back in 2015. The goal this time around is to factor in the future value of a trade to each of the participating teams. There are multiple measures that could […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/visual-baseball.com\/wordpress\/2022\/04\/13\/trade-network-updates-part-2-node-code\/\" \/>\n<meta property=\"og:site_name\" content=\"Visual-Baseball\" \/>\n<meta property=\"article:published_time\" content=\"2022-04-13T22:35:58+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-08-22T14:24:20+00:00\" \/>\n<meta name=\"author\" content=\"kc2519\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"kc2519\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/visual-baseball.com\\\/wordpress\\\/2022\\\/04\\\/13\\\/trade-network-updates-part-2-node-code\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/visual-baseball.com\\\/wordpress\\\/2022\\\/04\\\/13\\\/trade-network-updates-part-2-node-code\\\/\"},\"author\":{\"name\":\"kc2519\",\"@id\":\"https:\\\/\\\/visual-baseball.com\\\/wordpress\\\/#\\\/schema\\\/person\\\/d4ccbd596ab01720245999831d866f7f\"},\"headline\":\"Trade Network Updates, Part 2 (node code)\",\"datePublished\":\"2022-04-13T22:35:58+00:00\",\"dateModified\":\"2025-08-22T14:24:20+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/visual-baseball.com\\\/wordpress\\\/2022\\\/04\\\/13\\\/trade-network-updates-part-2-node-code\\\/\"},\"wordCount\":1042,\"keywords\":[\"Code\",\"Gephi\",\"Nodes\"],\"articleSection\":[\"Data\",\"Network Graphs\",\"Trade Networks\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/visual-baseball.com\\\/wordpress\\\/2022\\\/04\\\/13\\\/trade-network-updates-part-2-node-code\\\/\",\"url\":\"https:\\\/\\\/visual-baseball.com\\\/wordpress\\\/2022\\\/04\\\/13\\\/trade-network-updates-part-2-node-code\\\/\",\"name\":\"Trade Network Updates, Part 2 (node code) - Visual-Baseball\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/visual-baseball.com\\\/wordpress\\\/#website\"},\"datePublished\":\"2022-04-13T22:35:58+00:00\",\"dateModified\":\"2025-08-22T14:24:20+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/visual-baseball.com\\\/wordpress\\\/#\\\/schema\\\/person\\\/d4ccbd596ab01720245999831d866f7f\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/visual-baseball.com\\\/wordpress\\\/2022\\\/04\\\/13\\\/trade-network-updates-part-2-node-code\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/visual-baseball.com\\\/wordpress\\\/2022\\\/04\\\/13\\\/trade-network-updates-part-2-node-code\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/visual-baseball.com\\\/wordpress\\\/2022\\\/04\\\/13\\\/trade-network-updates-part-2-node-code\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/visual-baseball.com\\\/wordpress\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Trade Network Updates, Part 2 (node code)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/visual-baseball.com\\\/wordpress\\\/#website\",\"url\":\"https:\\\/\\\/visual-baseball.com\\\/wordpress\\\/\",\"name\":\"Visual-Baseball\",\"description\":\"Baseball Analysis and Visualization\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/visual-baseball.com\\\/wordpress\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/visual-baseball.com\\\/wordpress\\\/#\\\/schema\\\/person\\\/d4ccbd596ab01720245999831d866f7f\",\"name\":\"kc2519\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/8fd7c926b41a850fd0037a90412c8e7116b19c458c184fb0d1f2b912a46601cc?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/8fd7c926b41a850fd0037a90412c8e7116b19c458c184fb0d1f2b912a46601cc?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/8fd7c926b41a850fd0037a90412c8e7116b19c458c184fb0d1f2b912a46601cc?s=96&d=mm&r=g\",\"caption\":\"kc2519\"},\"sameAs\":[\"https:\\\/\\\/visual-baseball.com\\\/wordpress\"],\"url\":\"https:\\\/\\\/visual-baseball.com\\\/wordpress\\\/author\\\/kc2519\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Trade Network Updates, Part 2 (node code) - Visual-Baseball","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/visual-baseball.com\/wordpress\/2022\/04\/13\/trade-network-updates-part-2-node-code\/","og_locale":"en_US","og_type":"article","og_title":"Trade Network Updates, Part 2 (node code) - Visual-Baseball","og_description":"Over the last 10 days I’ve been playing around with code that will enable some new versions of the MLB trade networks I premiered way back in 2015. The goal this time around is to factor in the future value of a trade to each of the participating teams. There are multiple measures that could […]","og_url":"https:\/\/visual-baseball.com\/wordpress\/2022\/04\/13\/trade-network-updates-part-2-node-code\/","og_site_name":"Visual-Baseball","article_published_time":"2022-04-13T22:35:58+00:00","article_modified_time":"2025-08-22T14:24:20+00:00","author":"kc2519","twitter_card":"summary_large_image","twitter_misc":{"Written by":"kc2519","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/visual-baseball.com\/wordpress\/2022\/04\/13\/trade-network-updates-part-2-node-code\/#article","isPartOf":{"@id":"https:\/\/visual-baseball.com\/wordpress\/2022\/04\/13\/trade-network-updates-part-2-node-code\/"},"author":{"name":"kc2519","@id":"https:\/\/visual-baseball.com\/wordpress\/#\/schema\/person\/d4ccbd596ab01720245999831d866f7f"},"headline":"Trade Network Updates, Part 2 (node code)","datePublished":"2022-04-13T22:35:58+00:00","dateModified":"2025-08-22T14:24:20+00:00","mainEntityOfPage":{"@id":"https:\/\/visual-baseball.com\/wordpress\/2022\/04\/13\/trade-network-updates-part-2-node-code\/"},"wordCount":1042,"keywords":["Code","Gephi","Nodes"],"articleSection":["Data","Network Graphs","Trade Networks"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/visual-baseball.com\/wordpress\/2022\/04\/13\/trade-network-updates-part-2-node-code\/","url":"https:\/\/visual-baseball.com\/wordpress\/2022\/04\/13\/trade-network-updates-part-2-node-code\/","name":"Trade Network Updates, Part 2 (node code) - Visual-Baseball","isPartOf":{"@id":"https:\/\/visual-baseball.com\/wordpress\/#website"},"datePublished":"2022-04-13T22:35:58+00:00","dateModified":"2025-08-22T14:24:20+00:00","author":{"@id":"https:\/\/visual-baseball.com\/wordpress\/#\/schema\/person\/d4ccbd596ab01720245999831d866f7f"},"breadcrumb":{"@id":"https:\/\/visual-baseball.com\/wordpress\/2022\/04\/13\/trade-network-updates-part-2-node-code\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/visual-baseball.com\/wordpress\/2022\/04\/13\/trade-network-updates-part-2-node-code\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/visual-baseball.com\/wordpress\/2022\/04\/13\/trade-network-updates-part-2-node-code\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/visual-baseball.com\/wordpress\/"},{"@type":"ListItem","position":2,"name":"Trade Network Updates, Part 2 (node code)"}]},{"@type":"WebSite","@id":"https:\/\/visual-baseball.com\/wordpress\/#website","url":"https:\/\/visual-baseball.com\/wordpress\/","name":"Visual-Baseball","description":"Baseball Analysis and Visualization","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/visual-baseball.com\/wordpress\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/visual-baseball.com\/wordpress\/#\/schema\/person\/d4ccbd596ab01720245999831d866f7f","name":"kc2519","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/8fd7c926b41a850fd0037a90412c8e7116b19c458c184fb0d1f2b912a46601cc?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/8fd7c926b41a850fd0037a90412c8e7116b19c458c184fb0d1f2b912a46601cc?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/8fd7c926b41a850fd0037a90412c8e7116b19c458c184fb0d1f2b912a46601cc?s=96&d=mm&r=g","caption":"kc2519"},"sameAs":["https:\/\/visual-baseball.com\/wordpress"],"url":"https:\/\/visual-baseball.com\/wordpress\/author\/kc2519\/"}]}},"_links":{"self":[{"href":"https:\/\/visual-baseball.com\/wordpress\/wp-json\/wp\/v2\/posts\/1618","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/visual-baseball.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/visual-baseball.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/visual-baseball.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/visual-baseball.com\/wordpress\/wp-json\/wp\/v2\/comments?post=1618"}],"version-history":[{"count":7,"href":"https:\/\/visual-baseball.com\/wordpress\/wp-json\/wp\/v2\/posts\/1618\/revisions"}],"predecessor-version":[{"id":1625,"href":"https:\/\/visual-baseball.com\/wordpress\/wp-json\/wp\/v2\/posts\/1618\/revisions\/1625"}],"wp:attachment":[{"href":"https:\/\/visual-baseball.com\/wordpress\/wp-json\/wp\/v2\/media?parent=1618"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/visual-baseball.com\/wordpress\/wp-json\/wp\/v2\/categories?post=1618"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/visual-baseball.com\/wordpress\/wp-json\/wp\/v2\/tags?post=1618"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}