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:
destroyedShipType,destroyedShipGroup,killTime,solarSystemName,regionName,iskLost,iskDestroyed 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 Atron,Frigate,6067 Catalyst,Destroyer,7702 Caracal,Cruiser,7903 Svipul,Tactical Destroyer,8165 Velator,Rookie ship,8481 Tristan,Frigate,8700 Mobile Tractor Unit,Mobile Tractor Unit,8712 Thrasher,Destroyer,8914 Ibis,Rookie ship,10503 Capsule,Capsule,155253
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 Manticore,Stealth Bomber,10924 Megathron,Battleship,11947 Svipul,Tactical Destroyer,15961 Drake,Combat Battlecruiser,16478 Malediction,Interceptor,22912 Ibis,Rookie ship,28716 Coercer,Destroyer,31393 Maller,Cruiser,44608 Tristan,Frigate,100919 Capsule,Capsule,158907
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/