Nullsec Alliance Data TransformationPosted: 2011-09-21 Filed under: eveonline | Tags: alliances, csv, data, excel, mysql, nullsec, pivottable, RipardTeg, spreadsheet 1 Comment
Messy numbers to some, or a wealth of information for others. Here is a quick rundown of how I processed the data to form the Nullsec Alliance Growth and Decline post.
1. Get raw data from Wollari at Dotlan.
allianceID,date,members 151380924,26.06.2007,622 166439722,26.06.2007,1130 284278305,26.06.2007,252 288377808,26.06.2007,1151 386292982,26.06.2007,412 477769446,26.06.2007,252 628991027,26.06.2007,1062 632866070,26.06.2007,2267 673381830,26.06.2007,416
2. Load Eve Alliance information via API into a table.
3. Import Dotlan data into MySQL table and join allianceID on both tables to generate more human friendly data set that includes Alliance Names and Tickets.
SELECT alliances.allianceID, alliances.name, alliances.shortName, date, members FROM allianceHistory, alliances WHERE alliances.allianceID = allianceHistory.allianceID
4. Export data into an Excel readable file.
Once in Excel, I had to reformat the date from dd.mm.yyyy to yyyy-mm-dd to allow proper chart generation. First, I used the text to column feature with a comma ‘,’ set as the deliminator. Secondly, in a new column, I used a formula to combine the date in my desired format.
Hide the D, E, and F columns and we’re got a good set of data to analyze.
5. Select all 87,543 rows and generate a PivotTable using the Alliance Name as the legend, date as the Axis Fields, and a summation of the Member Count as the Value.
Now the fun part is thinking of WHAT is significant.
Some of the first things that I wanted to show was the rise and fall of the NC, growth of the DRF, and some charting for the larger Alliances like Goons and Test. These charts can be seen in the original post linked at the top.
RipardTeg of Jester’s Trek came to me with idea for a new report that I think will prove rather interesting. I’m going to be working with him to answer the question of ‘whether small sov-holding null-sec alliances are surviving in the last two years, or being folded into the mega-alliances over time’.
This type of Eve Community collaboration with passionate, exemplary individuals is what really keeps me playing. Stay tuned.