Major League Baseball Trade Networks, Part 1

One topic that has long fascinated me as network graph material is trade data between major league baseball (MLB) teams. I have previously created a static visualization showing activity at a macro level, i.e.- the number of trades between teams over a 100+ year period. Yet there was a desire to do something more, and to make it interactive so users would be able to sift through the data for their favorite teams to understand trade patterns through a visual representation. Today, after weeks spent tinkering with this topic, I finally have something to share, and will walk through how it is created and how to engage with it online. If you want to play with it before reading further, visit the Tigers trade network.

Here’s what we’ll wind up with:

Tigers trade network
Tigers trade network

My tools of choice in this endeavor are familiar ones to anyone working with baseball data, network graphs, or perhaps both, although I haven’t seen many instances of the latter. The trade data can be found at Retrosheet, as part of a seemingly boundless array of baseball data, both statistical and historical in nature. Gephi, the open source network analysis tool is again my choice for creating the network structure from the raw data, and Sigma.js is once more the tool for web implementation. Mix in a bit of Excel and PowerPoint for good measure, and we have all the tools necessary to create a pretty cool (IMHO) finished graph.

So let’s get started. Our first step is to go to the Retrosheeet site and download trade data, found at Retrosheet transactions. Be aware that there is much more than trade data in this dataset; free agent transactions, releases, and many other transaction types are available. My approach is to grab the entire dataset, which I can then load into a MySQL database for filtering and matching to other baseball data from both Retrosheet and the Lahman archives. For our example, only trades will be used; this leaves open the future possibility to examine free agent signings or other transaction types.

Once I have the data in MySQL (I’m purposely skipping over this process), the coding steps begin. This was a very iterative process as I gradually figured out how Gephi would play with the output data, but I won’t bore you with my multiple missteps. Instead, let’s have a look at the code snippets, and I’ll explain their usage and the thought process behind them. We’ll start with a view of the code, created within the (free) Toad for MySQL tool. In creating this code, we need to understand how Gephi (or other network analysis tools) work. At the risk of over-simplifying, Gephi only needs nodes and edges. Nodes will represent the players or teams in our visual, while edges will show the linkages within a single trade – who was traded for whom, and which players moved together from one organization to another.

Node creation is simple – we just grab all players involved in a trade, and do likewise for all teams. Here’s the code for players:

SELECT t.Player as Player, CONCAT(m.nameFirst, ” “, m.nameLast) as Name, count(*) as transactions

FROM trades2015 t
INNER JOIN Master m
ON t.Player = m.retroID

WHERE t.Type = ‘T’ and (t.TeamFrom > ‘A’ OR t.TeamTo > ‘A’)
GROUP BY t.Player, CONCAT(m.nameFirst, ” “, m.nameLast)

All we’re doing here is creating a node size for each player, based on the number of trades they are involved in.

For Teams, the logic is a bit more complex; since team names have changed from season to season, we need to join on both team and season to get the correct name assignments. We also want to account for the direction of each transaction, which we do using a UNION query.

SELECT b.Team AS Id, b.Name As Label, SUM(b.transactions) as Size
FROM
(SELECT t.TeamFrom as Team, te.name as Name, count(*) as transactions

FROM trades2015 t
INNER JOIN Teams te
ON te.teamID = t.TeamFrom and t.Season = te.yearID

WHERE t.Type = ‘T’ and t.TeamFrom > ‘A’
GROUP BY t.TeamFrom, te.name

UNION ALL

SELECT t.TeamTo as Team, te.name as Name, count(*) as transactions

FROM trades2015 t
INNER JOIN Teams te
ON te.teamID = t.TeamTo and t.Season = te.yearID

WHERE t.Type = ‘T’ and t.TeamFrom > ‘A’
GROUP BY t.TeamTo, te.name) b
GROUP BY b.Team, b.Name

After running the queries, we have results that can be posted into Excel or other spreadsheet software, where a tab-delimited file can be saved for use in Gephi. Our file data looks like this:

Id Label Size
aardd001 David Aardsma 4
aaroh101 Hank Aaron 1
aased001 Don Aase 1
abadf001 Fernando Abad 1
abbae101 Ed Abbaticchio 1
abbeb101 Bert Abbey 1
abbof101 Fred Abbott 1
abboj001 Jim Abbott 2

and for the team entries:


OAK Oakland Athletics 936
PH4 Philadelphia Athletics 6
PHA Philadelphia Athletics 355
PHI Philadelphia Blue Jays 28
PHI Philadelphia Phillies 1445
PHI Philadelphia Quakers 3
PIT Pittsburg Alleghenys 9
PIT Pittsburgh Pirates 1416

This is all Gephi requires for displaying nodes – an ID, a Label, and size. Even the label and size are not required fields, but they do make things easier if done in advance. So far, so good. Next we’ll move on to the somewhat more involved process of creating edge files.

As I progressed deeper into this project, it became evident to me that there were four different types of edges to display. The first two were obvious and easy – players being traded to a team, or from a team. Yet I also wanted to see the other players involved in each transaction, which necessitated the addition of two more edge type – traded with other players, and traded for other players. Note that in many cases just two or three of these might come into play, and for many prominent players, we’ll have none at all. Thus, the likes of an Al Kaline or Ted Williams will not be found in any of these graphs, as they remained with a single team for their entire careers.

Here’s the final edge code I wound up with to create the four categories of trades to be displayed in a graph. Gephi requires three edge attributes – a source value, a target value, and an edge type. The edge type must be either undirected or directed; for our graph, all edges will be directed, since we intend to show the bi-directional movements within each transaction. The first bit of code is for instances where a player was traded from a team:

SELECT tr.Season, tr.TransactionID, tr.PrimaryDate, tr.TeamFrom AS Source, tr.Player as Target,
CASE WHEN tr.Type = ‘T’ THEN ‘Trade’ WHEN tr.Type = ‘F’ THEN ‘Free Agent Signing’ WHEN tr.Type = ‘Fg’
THEN ‘Free Agent Granted’ WHEN tr.Type = ‘R’ THEN ‘Release’ END as Category,
CONCAT(m.nameFirst, ” “, m.nameLast, ” “, CASE WHEN tr.Type = ‘T’ THEN ‘Traded’ WHEN tr.Type = ‘F’ THEN ‘Free Agent Signing’ WHEN tr.Type = ‘Fg’
THEN ‘Free Agent Granted’ WHEN tr.Type = ‘R’ THEN ‘Released’ END, ” on “, tr.PrimaryDate, ” from “, t.name) AS Label, ‘Directed’ as Type, ‘Traded From’ as CategoryDetail

FROM trades2015 tr
INNER JOIN Master m ON tr.player = m.retroID
INNER JOIN Teams t ON tr.TeamFrom = t.teamIDretro and t.yearID = tr.season

WHERE tr.type = ‘T’

Note the legacy code covering free agency and releases, rendered moot by the WHERE clause. These will have to wait for another set of graphs. In a similar fashion we have code for trades where a player comes to a team.

SELECT tr.Season, tr.TransactionID, tr.PrimaryDate, tr.Player AS Source, tr.TeamTo as Target,
CASE WHEN tr.Type = ‘T’ THEN ‘Trade’ WHEN tr.Type = ‘F’ THEN ‘Free Agent Signing’ WHEN tr.Type = ‘Fg’
THEN ‘Free Agent Granted’ WHEN tr.Type = ‘R’ THEN ‘Release’ END as Category,
CONCAT(m.nameFirst, ” “, m.nameLast, ” “, CASE WHEN tr.Type = ‘T’ THEN ‘Traded’ WHEN tr.Type = ‘F’ THEN ‘Free Agent Signing’ WHEN tr.Type = ‘Fg’
THEN ‘Free Agent Granted’ WHEN tr.Type = ‘R’ THEN ‘Released’ END, ” on “, tr.PrimaryDate, ” to “, t.name) AS Label, ‘Directed’ as Type, ‘Traded To’ as CategoryDetail

FROM trades2015 tr
INNER JOIN Master m ON tr.player = m.retroID
INNER JOIN Teams t ON tr.TeamTo = t.teamIDretro and t.yearID = tr.season

WHERE tr.type = ‘T’

Next, it’s time to create linkages with players from the same transaction, first those moving in the same direction (traded with) in the trade.

SELECT tr.Season, tr.TransactionID, tr.PrimaryDate, tr.Player AS Source, tr2.Player AS Target,
CASE WHEN tr.Type = ‘T’ THEN ‘Trade’ WHEN tr.Type = ‘F’ THEN ‘Free Agent Signing’ WHEN tr.Type = ‘Fg’
THEN ‘Free Agent Granted’ WHEN tr.Type = ‘R’ THEN ‘Release’ END as Category,
CONCAT(m.nameFirst, ” “, m.nameLast, ” “, CASE WHEN tr.Type = ‘T’ THEN ‘Traded’ WHEN tr.Type = ‘F’ THEN ‘Free Agent Signing’ WHEN tr.Type = ‘Fg’
THEN ‘Free Agent Granted’ WHEN tr.Type = ‘R’ THEN ‘Released’ END, ” on “, tr.PrimaryDate, ” with “, m2.nameFirst, ” “, m2.nameLast) AS Label, ‘Directed’ as Type,
‘Traded With’ as CategoryDetail

FROM trades2015 tr
INNER JOIN trades2015 tr2
ON tr.TransactionID = tr2.TransactionID
INNER JOIN Master m ON tr.player = m.retroID
INNER JOIN Master m2 ON tr2.player = m2.retroID

WHERE tr.type = ‘T’

Note the need to duplicate the Master table in the code, since we now require multiple player names to populate the Source and Target fields in Gephi. The same holds true for our last snippet, where players are traded for one another.

SELECT tr.Season, tr.TransactionID, tr.PrimaryDate, tr.Player AS Source, tr2.Player AS Target,
CASE WHEN tr.Type = ‘T’ THEN ‘Trade’ WHEN tr.Type = ‘F’ THEN ‘Free Agent Signing’ WHEN tr.Type = ‘Fg’
THEN ‘Free Agent Granted’ WHEN tr.Type = ‘R’ THEN ‘Release’ END as Category,
CONCAT(m.nameFirst, ” “, m.nameLast, ” “,CASE WHEN tr.Type = ‘T’ THEN ‘Traded’ WHEN tr.Type = ‘F’ THEN ‘Free Agent Signing’ WHEN tr.Type = ‘Fg’
THEN ‘Free Agent Granted’ WHEN tr.Type = ‘R’ THEN ‘Released’ END, ” on “, tr.PrimaryDate, ” for “, m2.nameFirst, ” “, m2.nameLast) AS Label, ‘Directed’ as Type,
‘Traded For’ as CategoryDetail

FROM trades2015 tr
INNER JOIN trades2015 tr2
ON tr.TransactionID = tr2.TransactionID
INNER JOIN Master m ON tr.player = m.retroID
INNER JOIN Master m2 ON tr2.player = m2.retroID

WHERE tr.type = ‘T’

Each of these bits of code outputs results, which are then copied and pasted into our edges spreadsheet. Here are five rows showing each of our four trade categories:

Season TransactionID PrimaryDate Source Target Category Label Type CategoryDetail
2010 62908 20100731 KCA ankir001 Trade Rick Ankiel Traded on 20100731 from Kansas City Royals Directed Traded From
2010 60709 20100831 TEX ariaj001 Trade Joaquin Arias Traded on 20100831 from Texas Rangers Directed Traded From
2010 62264 20101118 COL barmc001 Trade Clint Barmes Traded on 20101118 from Colorado Rockies Directed Traded From
2010 72627 20101217 TBA bartj001 Trade Jason Bartlett Traded on 20101217 from Tampa Bay Rays Directed Traded From
2010 72622 20100709 TEX beavb001 Trade Blake Beavan Traded on 20100709 from Texas Rangers Directed Traded From

2010 62908 20100731 ankir001 ATL Trade Rick Ankiel Traded on 20100731 to Atlanta Braves Directed Traded To
2010 60709 20100831 ariaj001 NYN Trade Joaquin Arias Traded on 20100831 to New York Mets Directed Traded To
2010 62264 20101118 barmc001 HOU Trade Clint Barmes Traded on 20101118 to Houston Astros Directed Traded To
2010 72627 20101217 bartj001 SDN Trade Jason Bartlett Traded on 20101217 to San Diego Padres Directed Traded To
2010 72622 20100709 beavb001 SEA Trade Blake Beavan Traded on 20100709 to Seattle Mariners Directed Traded To

2010 62908 20100731 ankir001 blang001 Trade Rick Ankiel Traded on 20100731 for Gregor Blanco Directed Traded For
2010 62908 20100731 ankir001 chavj001 Trade Rick Ankiel Traded on 20100731 for Jesse Chavez Directed Traded For
2010 62908 20100731 ankir001 collt001 Trade Rick Ankiel Traded on 20100731 for Tim Collins Directed Traded For
2010 60709 20100831 ariaj001 franj004 Trade Joaquin Arias Traded on 20100831 for Jeff Francoeur Directed Traded For
2010 72627 20101217 bartj001 figuc001 Trade Jason Bartlett Traded on 20101217 for Cole Figueroa Directed Traded For

2010 62908 20100731 ankir001 farnk001 Trade Rick Ankiel Traded on 20100731 with Kyle Farnsworth Directed Traded With
2010 66840 20101219 betay001 greiz001 Trade Yuniesky Betancourt Traded on 20101219 with Zack Greinke Directed Traded With
2010 72622 20100709 beavb001 luekj001 Trade Blake Beavan Traded on 20100709 with Josh Lueke Directed Traded With
2010 72622 20100709 beavb001 smoaj001 Trade Blake Beavan Traded on 20100709 with Justin Smoak Directed Traded With
2010 62908 20100731 blang001 chavj001 Trade Gregor Blanco Traded on 20100731 with Jesse Chavez Directed Traded With

We have now successfully prepared the data for Gephi. In our next post, I’ll examine the process starting with the Gephi data import phase. Thanks for reading!

146 thoughts on “Major League Baseball Trade Networks, Part 1

  1. After checking out a few of the blog articles on your website, I honestly like your technique of blogging. I added it to my bookmark webpage list and will be checking back soon. Take a look at my website as well and tell me how you feel.

  2. An impressive share! I’ve just forwarded this onto a co-worker who had been doing a little homework on this. And he in fact ordered me dinner due to the fact that I discovered it for him… lol. So let me reword this…. Thanks for the meal!! But yeah, thanks for spending some time to talk about this subject here on your blog.

  3. After looking over a few of the blog articles on your blog, I seriously appreciate your technique of writing a blog. I saved as a favorite it to my bookmark website list and will be checking back soon. Please check out my website too and let me know how you feel.

  4. An intriguing discussion is definitely worth comment. I do believe that you need to publish more about this subject matter, it might not be a taboo matter but typically people don’t discuss such issues. To the next! Cheers!

  5. An outstanding share! I have just forwarded this onto a friend who has been doing a little homework on this. And he actually ordered me dinner simply because I stumbled upon it for him… lol. So let me reword this…. Thanks for the meal!! But yeah, thanks for spending some time to talk about this topic here on your site.

  6. An impressive share! I have just forwarded this onto a colleague who was doing a little research on this. And he in fact ordered me breakfast because I stumbled upon it for him… lol. So allow me to reword this…. Thank YOU for the meal!! But yeah, thanks for spending time to discuss this topic here on your web page.

  7. May I simply say what a comfort to discover a person that really knows what they are discussing on the internet. You certainly understand how to bring a problem to light and make it important. More and more people should check this out and understand this side of your story. I was surprised that you are not more popular given that you most certainly have the gift.

  8. Having read this I thought it was extremely informative. I appreciate you finding the time and effort to put this information together. I once again find myself spending a lot of time both reading and posting comments. But so what, it was still worthwhile!

  9. After looking into a few of the blog posts on your blog, I really appreciate your technique of writing a blog. I book marked it to my bookmark website list and will be checking back soon. Please visit my website too and let me know how you feel.

  10. I absolutely love your website.. Excellent colors & theme. Did you create this web site yourself? Please reply back as I’m looking to create my own site and would love to find out where you got this from or exactly what the theme is called. Kudos!

  11. I blog often and I really appreciate your information. This article has truly peaked my interest. I will take a note of your blog and keep checking for new details about once a week. I subscribed to your Feed too.

  12. Good day! I simply wish to offer you a huge thumbs up for your great info you’ve got right here on this post. I’ll be coming back to your blog for more soon.

  13. After looking into a handful of the blog posts on your web site, I seriously appreciate your technique of blogging. I saved as a favorite it to my bookmark site list and will be checking back in the near future. Please check out my web site as well and let me know how you feel.

  14. This is the perfect website for everyone who wants to understand this topic. You know a whole lot its almost tough to argue with you (not that I really will need to…HaHa). You definitely put a new spin on a topic which has been discussed for a long time. Excellent stuff, just wonderful.

  15. After checking out a number of the blog articles on your website, I seriously appreciate your way of writing a blog. I book-marked it to my bookmark website list and will be checking back in the near future. Take a look at my web site as well and tell me how you feel.

  16. Good day! I could have sworn I’ve been to this blog before but after browsing through many of the posts I realized it’s new to me. Anyhow, I’m definitely happy I discovered it and I’ll be book-marking it and checking back frequently!

  17. An impressive share! I have just forwarded this onto a coworker who had been conducting a little homework on this. And he in fact bought me lunch because I found it for him… lol. So let me reword this…. Thanks for the meal!! But yeah, thanks for spending time to discuss this matter here on your web page.

  18. Good post. I learn something totally new and challenging on blogs I stumbleupon on a daily basis. It’s always useful to read through content from other writers and practice a little something from other sites.

  19. Hi there! This article could not be written any better! Looking at this article reminds me of my previous roommate! He continually kept preaching about this. I’ll send this post to him. Pretty sure he will have a very good read. I appreciate you for sharing!

  20. For years, I’ve battled unpredictable blood sugar
    level swings that left me feeling drained and inactive.

    But given that integrating Sugar Defender right into my regular, I have actually noticed a substantial improvement in my overall power and security.
    The dreadful mid-day thing of the past, and I appreciate that this natural solution accomplishes
    these results without any undesirable or negative reactions.
    truthfully been a transformative discovery for me.

  21. For several years, I’ve fought uncertain blood sugar swings that left me feeling drained pipes and tired.
    Yet given that incorporating Sugar Protector right into my routine,
    I have actually discovered a significant improvement in my overall power and stability.
    The dreadful mid-day distant memory, and I appreciate that this all-natural solution attains these
    results with no undesirable or damaging reactions. honestly been a transformative discovery
    for me.

  22. Including Sugar Protector into my day-to-day regimen overall
    well-being. As somebody who focuses on healthy and balanced eating, I value the additional security this supplement provides.
    Since starting to take it, I have actually seen a significant improvement in my
    power levels and a significant reduction in my wish for unhealthy snacks such a such an extensive influence on my every day
    life.

  23. Including Sugar Defender into my day-to-day routine has been a game-changer for my total health.
    As a person who already focuses on healthy and balanced
    consuming, this supplement has actually offered an added boost of defense.
    in my power levels, and my desire for undesirable snacks
    so uncomplicated can have such a profound effect on my life.

  24. As someone who’s always bewared regarding my blood
    sugar, locating Sugar Protector has actually been an alleviation. I really feel a lot more in control, and my current check-ups have actually revealed favorable enhancements.
    Recognizing I have a trusted supplement to sustain my regular offers me
    comfort. I’m so happy for Sugar Defender’s impact on my wellness!

  25. Including Sugar Defender right into my daily routine has
    actually been a game-changer for my overall health.
    As someone that already prioritizes healthy eating, this supplement has actually supplied an added increase of security.
    in my energy levels, and my need for unhealthy snacks so effortless can have such a profound effect on my
    daily life.

  26. I blog often and I seriously thank you for your information. Your article has truly peaked my interest. I will take a note of your site and keep checking for new details about once a week. I subscribed to your RSS feed as well.

  27. Can I just say what a relief to discover someone that truly knows what they are talking about on the net. You actually know how to bring an issue to light and make it important. A lot more people must read this and understand this side of the story. I was surprised you’re not more popular because you definitely possess the gift.

  28. Howdy! Do you know if they make any plugins to assist with
    Search Engine Optimization? I’m trying to get my blog to
    rank for some targeted keywords but I’m not seeing very good gains.
    If you know of any please share. Appreciate it! I saw similar text here: Eco wool

  29. Hi, I do think your web site could possibly be having browser compatibility problems. When I look at your web site in Safari, it looks fine however, if opening in Internet Explorer, it has some overlapping issues. I merely wanted to provide you with a quick heads up! Apart from that, wonderful blog!

  30. I seriously love your website.. Great colors & theme. Did you build this web site yourself? Please reply back as I’m hoping to create my very own site and would love to learn where you got this from or just what the theme is named. Appreciate it!

  31. You are so cool! I don’t believe I’ve truly read through something like that before. So nice to discover another person with a few unique thoughts on this topic. Seriously.. many thanks for starting this up. This web site is one thing that is needed on the web, someone with some originality.

  32. You are so cool! I do not think I’ve read through a single thing like that before. So great to discover someone with a few genuine thoughts on this issue. Really.. many thanks for starting this up. This web site is something that is needed on the internet, someone with a bit of originality.

  33. I blog frequently and I genuinely appreciate your information. The article has truly peaked my interest. I am going to bookmark your site and keep checking for new information about once a week. I opted in for your RSS feed too.

  34. Greetings, I think your website could possibly be having web browser compatibility issues. When I take a look at your web site in Safari, it looks fine however when opening in I.E., it has some overlapping issues. I simply wanted to give you a quick heads up! Besides that, excellent website!

  35. Howdy! I could have sworn I’ve been to this website before but after browsing through many of the articles I realized it’s new to me. Regardless, I’m certainly happy I found it and I’ll be book-marking it and checking back often.

  36. sugar defender Ingredients For several
    years, I have actually battled unforeseeable blood sugar swings that left me feeling
    drained and lethargic. But considering that incorporating Sugar
    Defender into my regular, I have actually discovered a significant enhancement in my general power and stability.

    The dreaded mid-day thing of the past, and I appreciate that this all-natural solution accomplishes these outcomes without any unpleasant or damaging responses.
    honestly been a transformative exploration for me.
    sugar defender reviews

  37. Hi! I could have sworn I’ve been to this website before but after browsing through some of the articles I realized it’s new to me. Regardless, I’m definitely delighted I stumbled upon it and I’ll be bookmarking it and checking back regularly.

  38. Hello there! This post couldn’t be written any better! Reading through this post reminds me of my previous roommate! He constantly kept preaching about this. I most certainly will forward this post to him. Fairly certain he will have a good read. Many thanks for sharing!

  39. sugar defender reviews Finding Sugar Defender has been a game-changer for me,
    as I have actually constantly been vigilant
    regarding managing my blood glucose degrees. With this supplement, I
    really feel equipped to take charge of my health, and my newest clinical exams
    have mirrored a considerable turnaround. Having a credible ally in my corner provides me with a sense of security and reassurance,
    and I’m deeply glad for the extensive distinction Sugar Protector has made in my well-being.
    sugar defender ingredients

  40. Hello there! I could have sworn I’ve been to this blog before but after looking at many of the articles I realized it’s new to me. Nonetheless, I’m certainly pleased I discovered it and I’ll be bookmarking it and checking back frequently!

  41. This is the perfect website for everyone who hopes to understand this topic. You understand so much its almost hard to argue with you (not that I personally would want to…HaHa). You certainly put a brand new spin on a topic that’s been written about for many years. Excellent stuff, just excellent.

Leave a Reply

Your email address will not be published. Required fields are marked *