Tuesday, August 13, 2013

Eat Your Heart Out Ejyo - Odyssey Moon Resource Report

This is meant as behind-the-scenes for my latest TheMittani.com article: Odyssey Moon Resource Report

As always, I've made most of my data available for private review:
I was lucky enough to get an easy to parse database of moon data directly from Blake Armatage at K162space.com.  This made the map section of the article EXTREMELY easy to make, as opposed to a bunch of pavement pounding and guessing.  Unfortunately for you guys, I've been sworn to secrecy on the data source... so I will leave it up to him to post any more of that data.

This turned into a monumental undertaking.  Trying to slice-n-dice data into presentable formats took a ton of time.  Also, trying to understand and then present in a cogent manner the entire scope of these changes, from moon mining to T2 product, was a ton of work.

Map Data

I am still trying to get over my irrational fear of SQL and the datadump.  Though it took some crowbarring, I was able to force the data into a usable format.


By stacking the raw data K162space furnished into one database, I was able to merge that data set with the existing map data to get a table with counts, per system, of each moon resource.  I have included some basic map SQL fu in the code snippet gallery.

I had prodded Mynnna several times to try and check/improve the database I have, since GSF boasts the most complete database of money moons.  Unfortunately, beyond some jibes about certain groups being over reported (Thulium), I wasn't able to get better results.  As such, if you look at the R8 map, there are some pretty wide swaths missing in the data set in the south and the east.  This was an effort of "do the best you can", and having K162space's dataset was a godsend, even if it may not be 100% complete or accurate.

Once it was merged into the form above, it was onto JMP to try and build the charts.  Turns out mapping values to custom coordinates is a massive pain in the ass.  The solution?  Crunch the original table down further.  By expanding the table to include boolean values of if a system included a particular resource type, then using that boolean to trigger writing the X, Y, Z coordinates to each material.  Then with data columns like Cadmium (x), Cadmium (y), Cadmium (z), of either valid coordinates or NULL, I was able to overlay the data into very pretty maps.

Also, pro tip: 2d map coordinates are (X,Z), not (X,Y).  Credit to @AideronRobotics for finding the issue and pointing me at the correct pairing
This is wrong: X,Y R8 report:

Meanwhile, these are right:


Click to embiggen

I had to get somewhat creative with the data points on R16/R64 because they were completely overlaid.  So by making the "background" points large and the "foreground" points small, this allowed for a decent two-level effect that was better than when all the dots were the same size.  Also this allowed for multiple overlaid points in a single system to show up a little better.  In the future, it might be worth adding small offsets for each data point so they don't overlay as badly.

The REAL treat I wanted to do was recreate the hex-grid for regions/constellations or at least add borders using an ESRI map... but I was extremely out of my element on trying to make that.  Perhaps a project for another day.  If I could build an ESRI map, I could build heatmaps by rarity in each location, which would be sweet to have.

Also, those interested in data dump fu, I have included my SQL queries in the site's Code Snippet Gallery.

Graphs, Graphs EVERYWHERE

I learned my lesson last time with historical data.  This time I picked a reasonably arbitrary date (CCP Rise's HAC announcement) and locked in all the data I would need for that period.  Thankfully, that was a good 120d swath to get a picture before any changes were announced and out until all of Odyssey's messyness had subsided.

The topic I tried to include, but could not do so graphically, was Alchemy.  I added alchemy math to my advanced-material data feeds, but trying to include them in the final graphs was extremely cluttered.  Also, since the demand for T2 materials has been waning in the face of T1 hull preferences, no material was properly bottlenecked to really incentivize alchemy as a side path.  So rather than clutter and add another 2 pages to my report with essentially little value, I skirted the topic.

Trying to merge the above with all the other racials was messy and the added data didn't really add a lot of value.  So, though it's a little more convoluted, I think the below was a better use of that image space.
 Also, I found Google Drawings to be my savior in this endeavor.  Though replacing the graphs when that layer needed a change was more trouble than I would have liked, it took minutes to build really solid pictures and combine images, where real image processing like Photoshop or GIMP would be nearly impossible for me.  

Another big problem was the post-patch spike on new resources that didn't exist on patch day went absolutely nuts until supplies had been secured.  As such, I had to do a little graph finagling.

First, on metamaterials, I added a "production cost" dotted line over the first 10d
I did some data masking to try and accurately project the pre-patch cost, so that when it lined up with Odyssey release, it would show a reasonably flat transition.  I think the picture does reasonable justice, even if the intended line up missed my original goal.

It was more problematic on component build graphs.  In this case, though the instantaneous cost on patch-day was insane, the truth is probably no one was building them at that price.  So, I instead made a sliding conversion rate over the first 10d.  

Without conversion:

With conversion:

I know the shapes look pretty similar, but the spike is greatly reduced.  I contemplating cutting the 10d spike period completely, or forcing the axis to chop that data, but I think this is a reasonably fair look at the spike-and-decay how it probably played out among manufacturers.  The other option was to chart market prices on those items in the first 10d, but I didn't think that was a better picture than the manufacturing cost.

In total, I generated something on the order of 20 graphs while working on this project, and used maybe half of them.  Though my spreadsheet fu is strong, it was a lot of work to collate all that data.

Also, I have to give props to the admins over at TheMittani.com, because the recent contributor-side changes made this article 10x easier to transfer over compared to the Burn Jita article I wrote.  There was a bunch of streamlining, and it's almost as easy as writing here in Blogger.

Egg on my Face

Despite quadruple checking my work, seems I let a misprint through on one of my images.
What was printed:
What should have been printed:

And I sparked a real CSM conspiracy within minutes of my reddit posting.  Thankfully, CCP Fozzie was quick to note the error was mine, and I was able to push correct graphs back to the site.

I feel particularly terrible about this flub because I'm already trying to overcome the mass opinion that TheMittani.com is only a propaganda mouthpiece for CFC.  It's frankly the highest/best place I can publish this data and get it into the hands of the players.  The issue should be corrected now, and I will be more diligent about getting second reviews of my data in the future.

What's Next?

I still want to write up something about mining post-Odyssey, but with the recent announcement of CFC Interdiction of Caldari Ice, I think I will need to hold off another 30-40d.  I will see about gathering data so my turn around post event is a little faster than the ~3wks this article took.  At least mineral thoroughfares are much more direct and less convoluted than the entire T2 production sphere.  Also, I am open to more ideas about industry topics that require a deeper review.

I am also hoping to jump on a few smaller topics.  As much as writing these big articles is fun and all, it's a ton of work and I would like to get more articles out.