EVE online is a MMORPG where the players fly spaceships to blow each other to pieces and smack talk in forums and chats online.
The developers recently released a CSV data dump containing all the PvP kills (spaceships blown apart by other players) in the last month.
I thought it was an excellent excuse to test out one of the projects I starred on github and never got to try out: q, a nifty tool that allows you to use SQL queries on CSV or otherwise tabular text files.
As a first step, since q was having some problems with the text encoding in CCP release, I got rid of most of the colums I didn’t care about:
$ mkdir eve-killdump
$ cd eve-killdump/
$ unzip ~/Downloads/WWB_killdump.zip
$ cut -d, -f10-12,14,16-18 killdump.csv > onlymatters.csv
At this point my “onlymatters.csv” file looked like this:
Coercer,Destroyer,2016-03-10 21:29:37,Lamaa,The Bleak Lands,12585330.52,6692045.33
Then I ran my first query, I wanted to see which ships were destroyed the most:
$ q -H -d, -w none "SELECT destroyedShipType, destroyedShipGroup, COUNT(*) as nDestroyed FROM ./onlymatters.csv GROUP BY destroyedShipType ORDER BY nDestroyed" | tail -n 10
Mobile Tractor Unit,Mobile Tractor Unit,8712
Unsurprisingly, a lot of Frigates (the cheapest/easiest to fly spaceship type) are destroyed the most. And most of them of Gallente manufacturing! Interesting.
Then I wanted to see the destroyed ships grouped by “ship group” (shall we call it “class”?) and ran:
$ q -H -d, -w none "SELECT destroyedShipType, destroyedShipGroup, COUNT(*) as nDestroyed FROM ./onlymatters.csv GROUP BY destroyedShipGroup ORDER BY nDestroyed" | tail -n 10
Again, Frigates are second only to Capsules (every pilot has a capsule to fly when his ship gets blown apart, BUT the capsule can be blown apart too, so I guess the Capsule is the most flown “ship” in EVE!) in number of ship lost.
That’s it for now, was a nice excercise to test out q a bit :)
Fly safe! o/