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


Inferno 1.2 Procurer BPO Speculation


Along with the rebalancing the roles of the mining barges, their mineral requirements were also adjusted. The build requirements for the Tech 1 barges about doubled and therefore the BPO NPC price was adjusted to reflect the new value.

Here is my research on buying Procurer BPOs at pre-Inferno 1.2 prices to sell after the patch.

[Aug 9 update] It looks like CCP changed the NPC price in the Inferno 1.2.1 patch.

ORE Space

Since these BPOs are only seeded in the Outer Ring in two ORE stations, I was able to get a good view on how many were bought by using the market window.

Out of normal BPO purchases in ORE space amounted to 1,264 units starting in late July.

Patch Changes

CCP changed the values before going live. They had a different number published on the Test server and knowingly seeded a different value on the live server. *shakes fist*

Other Barges

I looked considered the increase in BPO price, the new role, market velocity, and added in a little bit of my own gut feeling. This showed that the Procurer BPO was the item with the most potential for profit.

ISK Injection

Given that these were purchased for 450 M and are now worth 0.508 B 0.838 B that means 73.3 B 491 B was created from nothing, or about 0.3% 2% of the normal 24 T ISK faucet amount stated in the 2012 Fanfest presentation. This amount doesn’t seem too noteworthy.

Speculating on Profits

In 76 days about 124 pre-Inferno 1.2 Procurer BPOs moved in Jita slightly above their NPC price of 450 M. If 1,264 out of normal units and around 561 move in Jita in the same time period, that means it will take about 1,118 days (3.06 years) for this stock to clear assuming that demand remains the same.

Twenty BPOs

I bought 20 Procurer BPOs at the old NPC price for a total investment of 9 B. Taking in account the new price of 0.508 B 0.838 B, we can value them at 10.16 B 16.8 B. This shows a 1.16 B 7.76 B increase in the NPC value of the items. I was expecting more profit, but that’s what you get when you speculate on patch day.

I have a few options on how to turn a profit with the BPOs:

  1. Sell them in Jita. I believe that due to the market saturation with pre-Inferno 1.2 prints, it will be a while before we start to see the new NPC cost in Jita of 0.508 B 0.838 B + a good margin. I could slowly sell the stock off as prices rise.
  2. Improve value by researching them and then sell.
  3. Produce BPCs for invention/manufacturing. As of this writing, there are not a lot of BPCs available on the open market.

I plan on doing all three to help spread the risk and start to get a return on this speculation adventure.