spotmarket – pivot and resample


I published v0.2, which includes a bunch of hacked together proof of concept code. This week I dove into the pandas library, focusing on the data manipulation tools that are inherit to pandas. This post covers some of the highlights and demonstrates the initial charting that I’ve been producing.

More to come on what I plan to do with the data; for now those details have to be a trade secret.

Data Frame

The crux of pandas is the dataframe and this week I discoverd that you can load your query results right into a dataframe and not have to write a parser. Notice in the below code that I run the query and place the results into a dataframe with a ‘fetchall()’. This really saves me a lot of headaches when passing around chunks of data and makes it easy to write functions to combine data with overlapping values. Return the dataframe and do more ::work::.

Here’s a query that I use to get NPC kills by region when given a regionID.

def getnpckills_byregion(regionID):
    conn = psycopg2.connect(conn_string)
    cursor = conn.cursor()
    sql = """SELECT
    SUM(mapkills."factionKills") as SUM_factionKills,
      "mapRegions"."regionID" = "mapSolarSystems"."regionID" AND
      "mapSolarSystems"."solarSystemID" = mapkills."solarSystemID" AND
      public."mapSolarSystems"."regionID" = %s
    GROUP BY mapkills."timestamp", public."mapSolarSystems"."regionID", "mapRegions"."regionName"
    ORDER BY timestamp DESC
    data = (regionID, )
    cursor.execute(sql, data)
    df = pd.DataFrame(cursor.fetchall(),columns=['regionID', 'regionName', 'SUM_factionKills', 'timestamp'])
    return df


Doing a pivot in pandas is amazingly elegant and you can perform the operation in one statement.


df.pivot(index='timestamp', columns='regionName', values='SUM_factionKills')



Another powerful tool in the library are the resampling options. I’m still trying adjusting the parameters to find a balance on data granularity.

The below graphs show NPC kills over all regions over time. One graph has no resampling while the other has values that I feel display a daily value, normalizing the peaks and valleys that occur due to server downtime and differing activity rates due to timezone.

conversion = "12H"
v_window = 8
v_minperiods = 1
pd.rolling_mean(df.resample(conversion, fill_method='bfill'), window=v_window, min_periods=v_minperiods)



Ratting activity in Blood Raiders regions:

2016-01-28_blood_raiders_regions_sum 2016-01-28_blood_raiders_regions

Ratting activity in Serpentis regions:

2016-01-28_serpentis_regions_sum 2016-01-28_serpentis_regions

More to come. Find me on #k162space on


Capital Runs 1-229 (Final)


Is is more profitable to build a Carrier from BPCs or BPOs? That question was the initial spark that started my interest in capital production in 2011. Using a BPC copy pack from the market was hardly profitable so I made a plan to own and operate a set of capital BPOs.

Over time as profits started to materialize, more BPOs were purchased to round out production queues. Carrier success then started to bleed into Dreadnought production. With the upcoming industry changes in the Crius expansion, the fate of my capital operation is unknown so I have paused the project.

Here are the final numbers for almost 3 years (2.93 to be exact) of capital production. Note that Dreadnought production was a recent addition, only starting in mid-2013. Special tribute goes to my industry partner, Raath, as he was the lead on this massive project.

tl;dr Build Naglfars and Archons.





Naglfar sales in Delve outperformed all other types and regions yet showed the most standard deviation.


The Naglfar outperformed every other hull type when it came to profitability per time period. This is due to the balance update it received in the Odyssey update where it became a viable doctrine ship. The Archon, due to the popularity of the Slowcat doctrine, was also solid performer.



Relocating Capital Building Operations

With the stabilization of the Fountain and Delve regions over the past few months, the amount of sales we have seen in our building system have decreased. Raath made a command decision to leave the region in search for greener pastures.


Operation Performance

Our time in G-TT5V spanned 385 days where we built 162 capital hulls to sell on the local market. Our sales totaled 221.4 B ISK with a total profit coming in at 43.3 B.  Performance metrics show a margin of 4.1%, an average of 3.4 B per month in profit, and a velocity of 5.06 hulls moved per week.

Top Performers

Given the balance changes to the Naglfar hull that came in the Odyssey update, we have seen a very strong demand the the hull; it is not a surprise that it has been a top performer. If you want to mass build, pick the Archon or Thanatos.



Supplying Nullsec Capital Systems


I built and sold things to help the efforts of local alliances over the past two years. This post is a timeslice of nullsec politics, wars, and the efforts of an industrialist who helped build the ships that eventually returned to dust.


I have a database of all the transactions I have been involved in since I started to produce items in Eve. The chart below shows the amount of transactions per day filtered by major nullsec stations. By major I mean what is considered the region’s ‘capital’ or main staging system at the time. The chart it neat by itself, but adding a sense of nullsec politics during that trading time can really explain a lot more about the data.

D2-HOS Learning the Ropes

In the Winter of 2011 I found myself in my first nullsec powerblock after leaving wormhole space. At the time, D2 was the home of FCON in Pure Blind.

I was in an alliance that was blue to FCON and we were able to setup POS operations in their space. I helped out my corporation by running many logistical operations to setup our POS moon mining and reaction network.

With all of my trips into Pure Blind from Empire, I became skilled at Capital and Jump Freighter cyno operations. I leaned how to form proper bookmarks, which stations had terrible docking radii, and the best times to perform logistical work given roams and AFK cloakers.

G8AD-C Building Big

During my time in the Northern Collation, I built and supplied many Alpha Maelstroms as detailed in this post. Some 344 Maelstroms were supplied to ready NC pilots that were purging the Pure Blind and Cloud Ring from Ev0ke’s hands under crushing server lag and failed-to-load battle grids.

As the battlefront changed from Pure Blind to Cloud Ring, I moved my operations into Wildly Inappropriate‘s home system as many fleets were staging out of this location. In this location I setup operations for capital production and began to supply the local market with Carrier hulls.

If you look at the trends for D2 and G8, you can see the eventual decline of Pure Blind and the population move into G8 for the purge of Cloud Ring.

GE-8JV Infiltrator

Isn’t that Against ALL Authorities‘ home system in Catch? Yes it is.

During the time of poor faith in Eve’s future, I spent a month idle in highsec pondering what to do next. My CEO at the time was interested in some rental space in the far region of Omist, an area that I had little to no interest in.

I found myself in a position where I was blue to -A- so I pieced together a cyno chain and started to supply their local market. The amount of transactions per day was extremely high; there were a few CTAs that caused me to push 200/300+ transactions per day on occasion.

Back in Pure Blind

Eventually I lost blue status to -A- and with it my access to GE-8. I took a small break from Eve and then came back to sell goods in an area that I was familiar to me — Pure Blind.

The old NC was only a shell of its former self. People were scattered, the blocs were realigning, FCON was moving to Branch, and old groups such as WI were being absorbed into the greater Goon kingdom. There were minimal fights and therefore minimal production opportunities.

6VDT-H TEST Market Please Ignore

Having been active on Reddit and finding great humor in TEST’s mentality, I applied and joined TEST. What followed was an age of prosperity as I was able to supply large-scale efforts. At this point in my building and trading career, I was able to have 50-60 B on the market at any given time and my logistical legs were fully developed. Multi-cyno trips, battleship construction, and capital construction were all at my fingertips.

1DH-SX Nice Region, We’ll Take It

This NPC station in Delve was used by TEST as a base of operations when the Delve campaign escalated from ‘good fights’ to ‘you need to leave now’. On this war effort I worked with a focused group of other TEST industrialists, traders, and FCs to make sure we were always two steps ahead of fleet movements. This was my proudest moment so far for TEST; though I was not able to contribute on the grid, I made sure that we were going to win the logistics fight.

K-6K16 New Home

New region, new home. This station went up to serve as TEST’s new capital system. I am currently working in Delve and supplying the market there, though in a rather limited capacity than before. As you can see in the above chart, the amount of transactions per day is on a slow decline. I’ve found that I will take periodic breaks and then returning when a new prospect garners my attention.

F2OY-X Gallente Administrative Outpost

Yesterday a new outpost went up in Querious in F2OY-X.

If you listen to the June 28 State of the Alliance ( address from TEST leadership, you will learn that TEST originally requested that Goons not get involved in their ‘Good Fight’ campaign.

Now that things have escalated, Goons are on the field and are going to be entrenched in this campaign. Since a large numbers of Goon corporations need a place to call home, and a Gallente Office is the best Outpost choice since it contains the most number of offices.

Banlish on has been documenting Outpost builds and their political significance for a while now. Here is a recent quote from him on the reason this outpost went up and what it means for the war:

When I was living in Querious leading AtlasDOT and then into leading in FAIL I tried to push forward the idea to the AAA block that to control Delve/PB/Querious they should put office outposts in EVERY single system that connected to NPC delve. I was shot down time after time and no one would listen to me, being able to make every fleet that left 1DH or 319 run past an office outpost controlled by the different alliances in the SoCo would make life there a living hell… …

With F2O now having a station, only 4 other locations remain to put office outposts up and totally ruin the ability for the SoCo to pretty much ever take back the Delve core. … the outpost Goons put down is in SUCH a good place to fucking ruin everyone’s day. … Surrounding NPC 0.0 with office outposts is a good way to beat on the people that take refuge there…


In addition to this post, Banlish is a welth of political knowledge and he has many comments in the large, large thread called 0.0 Politics Discussion.

In January of 2010 during the purge of Cloud Ring, Goons established control by dropping a Gallente outpost in  B-DBYQ. It seems like this strategy works and so we are seeing it again with the Delve campaign.

Side Note: I’ve found some gems of history about other regions and Outposts. As I find them, l will link them here:
Branch History

Delve Financial Warfare

After I get home from work and eat dinner, I usually only have around an hour a night that I can dedicate to Eve. This open hour doesn’t give me a lot of time to participate in any of the large TEST fleets so I am going to help out the war in a different way. In addition to the spaceship war, I am going to launch a fiancial war on the current residents of Delve, Querious, and Period Basis.

One of my trading alts is going to fly around and mess up the markets. I’m going to polish off his skills so I can have region-wide abilities to buy, sell, and modify prices.

Here are some rough goals:

  1. Drive up the prices of doctrine items
  2. Buy out stocks of ships, ammo, guns, and fuel
  3. Undercut to drive out logistical people that stock markets

I’m going to start with 31B in funds and possibly scale up to 50B depending on how successful this becomes.

I have never done any type of mass manipulation on this scale so there is a high risk that it will not be successful. I will try to document any significant advancements in my campaign here so stay tuned.


T2 Alpha Maelstrom Cherry

Out of the spreadsheets and into the fire! I have always been interested in the ebbs and flows of power in nullsec and as such, I have a subcapital character in TEST for participation in the pony giggles.

I saw a Jabber announcement go out for a fleet that worked perfectly for me — after US dinner, before bed.

A lot of firsts happened on this fleet. It was my first official non-kitchen sink/random home defense subcapital fleet, the maiden voyage of my T2 Alpha Maelstrom (finally have Large Projectile Turret V), first time using optimized overview settings, and my first nullsec final blow.

(don’t laugh all you PVP’ers — I’m going to try to do a battle report)

Alpha > Scimitar > Huginn formup in Fountain. 140 in fleet mainly consisting of a Maelstromball with fast tackle support that headed into Delve. We encountered and played catch-up/evade/backtrack with a Nulli Secunda AHAC/T3/Dictor gang of about 100. Now blue’d Pandemic Legion joined us on a few jumps as we played around with the Nulli Secunda gang.

Three major encounters resulted in us blapping their ships as they came through gates while being tailed.

Once we jumped through, our Alpha Maelstroms pulsed MWDs and sat at optimal using Depleted Uranium L (1.2x tracking) ammo while fast tackle stayed on gate to lock down targets.

After a few T3s, some Guardians, and all their Dictor were down, they retreated home while we reinforced a few structures with the help of PL.

This was also my first time using Liberty Prime’s UI Generator settings and I have to say, their standard theme is quite nice.

I was easily able to flip between friendly/hostile/warp points and the color highlighting really helped when identifying targets. I recommend trying this theme out if you have any issues with your current Overview setup.

So watch out, this industrialist is growing teeth!