<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Lockefox on Medium]]></title>
        <description><![CDATA[Stories by Lockefox on Medium]]></description>
        <link>https://medium.com/@HLIBIndustry?source=rss-d55d2a8c9f94------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*TOONDYF8LygLKDuE.jpeg</url>
            <title>Stories by Lockefox on Medium</title>
            <link>https://medium.com/@HLIBIndustry?source=rss-d55d2a8c9f94------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 00:28:59 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@HLIBIndustry/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[MongoDB vs Pandas]]></title>
            <link>https://medium.com/@HLIBIndustry/mongodb-vs-pandas-5abe2c5ff6f3?source=rss-d55d2a8c9f94------2</link>
            <guid isPermaLink="false">https://medium.com/p/5abe2c5ff6f3</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[nosql]]></category>
            <category><![CDATA[mongodb]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[python-pandas]]></category>
            <dc:creator><![CDATA[Lockefox]]></dc:creator>
            <pubDate>Wed, 23 Aug 2017 14:30:14 GMT</pubDate>
            <atom:updated>2017-08-24T00:09:27.440Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Stop Trying To Load Into Pandas — And Just Load Into Pandas</em></p><p>noSQL opens up a wild world of schemaless possibilities…but what to do with all those classical Big Data tools that expect traditional SQL data? Are you doomed to design complex queries? What about more complex data shapes?</p><p>In this post, we will walk through a real world example with some common data shapes and talk about ways to handle them directly from raw documents inside python’s most powerful data package: <a href="http://pandas.pydata.org/">Pandas</a>.</p><h3>Let’s Talk About <a href="https://zkillboard.com/">zKillboard</a></h3><p>zKillboard (ZKB) is backed by MongoDB and stores/serves killmail data from the online game <a href="https://community.eveonline.com/">EVE Online</a>. If you wanted to know more about that jerk who blew up your favorite spaceship, you’d check ZKB. Also ZKB offers an <a href="https://github.com/zKillboard/zKillboard/wiki/API-(Killmails)">excellent API</a> for external devs to use their data. We will just be scratching the surface of what they offer in this demo.</p><p>A popular metagame is “solo PvP”: a 1v1 test of skill. ZKB has made this easy to search for with their solo modifier, so let’s <a href="https://zkillboard.com/kill/64158479/">grab some data</a></p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/bdfbe80cfae375a2866ee18b2be19fee/href">https://medium.com/media/bdfbe80cfae375a2866ee18b2be19fee/href</a></iframe><p>This represents a pretty common, yet complex, data problem. If this were SQL, we could index on killID and split list elements into new tables, and maybe argue if the dict elements might require separate tables — But MongoDB don’t care, store it raw!</p><p>There are some easy queries we could ask of this data without trouble thanks to <a href="https://docs.mongodb.com/manual/reference/operator/projection/positional/">$projection</a> which would load neatly into Pandas as key/value sets. Things like:</p><ul><li>value destroyed: [&#39;zkb.fittedValue&#39;, &#39;zkb.totalValue&#39;]</li><li>violent locations: [&#39;solarSystemID&#39;, &#39;zkb.locationID&#39;]</li><li>MTBF (mean time between fights): [&#39;killTime&#39;]</li></ul><p>But as we want more and more complex data, $projection becomes a clumsy tool to get it.</p><h3>Pivot Dicts</h3><p>A really powerful format right now would be to flatten out all those dict elements into one flat table so we could ask all sorts of cheeky SQL questions. <a href="https://www.dataquest.io/blog/python-json-tutorial/">Other sources</a> talk about flattening data before feeding it to Pandas; but what is the point of using a vectorized library if you start with a <em>by-every-element</em> for-loop transformation. what is the best way to transform position, victim, and zkb keys?</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/ed54c9b37d07f3237b406e310bfa6799/href">https://medium.com/media/ed54c9b37d07f3237b406e310bfa6799/href</a></iframe><p>Thankfully, we can play fast-and-loose with Pandas row elements. Here we treat the ugly dict columns like JSON-blobs.</p><p>Let’s clean this up!</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/2a67b6790681c1a4e77317c9f0150258/href">https://medium.com/media/2a67b6790681c1a4e77317c9f0150258/href</a></iframe><p>What did we do?</p><ol><li>Separate and pivot JSON-blob columns into their own “tables”: list(dict()) makes DataFrames</li><li><a href="https://pandas.pydata.org/pandas-docs/stable/generated/pandas.concat.html">Concat</a> the new frames onto the old one: row index has not changed, can copy-paste data column-wise</li><li><a href="https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.drop.html">Drop</a> JSON-blob columns for pretty-print (optional)</li></ol><p>This could of course be streamlined: looking for dtype==object and dynamically re-configuring the DataFrame. But be warned datetime might register as object. Also, if there are list elements like attackers or items, this method will not work, because row index will no longer be 1:1.</p><h3>Pivot Lists</h3><p>The second dataset we’d like to manipulate are cells with list elements. If we were filtering into SQL, these list elements would be separate tables accessed via JOIN. But MongoDB doesn’t split collections — the point is to store everything together. Though it’s less obvious what the best course to reorganize data like items and attackers.</p><h4>Append Method</h4><p>Though a pure-Pandas solution should show performance improvements, it can be very easy to miss the mark. In our first test, we used an “append” method that unwinds list elements directly, to abysmal performance.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/4d4660c81b78d54487ce2d2cb0ca03f2/href">https://medium.com/media/4d4660c81b78d54487ce2d2cb0ca03f2/href</a></iframe><p>This breaks the cardinal rule of DataFrames: <strong><em>DO NOT USE FOR LOOPS</em></strong>. Even using the more performant itertuples method rather than iteritems, we see a 20x slowdown vs a pure-python solution. Lastly, this method is ugly and hacky; no one will know what we’re doing</p><h4>Melt Method</h4><p>In effect, we’re looking for a <a href="https://pandas.pydata.org/pandas-docs/stable/generated/pandas.melt.html">melt</a> operation. Just as if we were using JOIN between two tables, duplicated “index keys” is alright, as long as we can get to a flattened structure.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/021c39c3d888a31e141c2d333bab6412/href">https://medium.com/media/021c39c3d888a31e141c2d333bab6412/href</a></iframe><p>Do note, this transformation cannot be done blindly. We can’t pivot both list keys into the same dataset (items, attackers). Though it would be valid to first apply the dict pivot to flatten the whole table before trying the list operation.</p><p>A few notes to avoid pitfalls:</p><ul><li>All NA values must be dropped before dict-pivot: keep data types uniform</li><li>Index-keys are required to map one-to-many via JOIN</li><li>Strictly-uniform dict elements are not required, but encouraged by MongoDB</li></ul><h3>How Does It Perform</h3><p>Talk is cheap, why don’t you <a href="https://github.com/EVEprosper/ProsperBlogs/tree/master/Mongo_vs_Pandas">try it on your own machine!</a></p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/17c3bc68856e3285a27897feefe6c95e/href">https://medium.com/media/17c3bc68856e3285a27897feefe6c95e/href</a></iframe><p>This should be a convincing case to just do as much data transformation inside Pandas as possible, rather than fight noSQL documents in their raw forms. With a couple easy methods, we’ve added the ability to handle two common MongoDB data shapes.</p><p>What’s interesting is the non-linear performance of the melt method. I personally have a theory about “reindexing” that might be the root of this ballooning runtime, but melt performance decreases with dataframe length; on Python and R.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5abe2c5ff6f3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How A Bad SQL Schema Is Born]]></title>
            <link>https://medium.com/@HLIBIndustry/how-a-bad-sql-schema-is-born-ba211bcc3c8d?source=rss-d55d2a8c9f94------2</link>
            <guid isPermaLink="false">https://medium.com/p/ba211bcc3c8d</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[schema]]></category>
            <category><![CDATA[eve-online]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <dc:creator><![CDATA[Lockefox]]></dc:creator>
            <pubDate>Thu, 06 Jul 2017 16:15:00 GMT</pubDate>
            <atom:updated>2017-07-06T16:15:00.912Z</atom:updated>
            <content:encoded><![CDATA[<p>Fans of <a href="https://www.youtube.com/user/EVEProsper">EVE Prosper</a> will know we store intra-day prices for <a href="https://community.eveonline.com/">EVE Online</a> hubs going back a couple of years. Fans <em>won’t know</em> about the terrible database design under the hood — until now. I recently had to patch some bugs, but wanted to review a painful table: So let’s kill some of those rumors I’m some sort of super-genius</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/480/1*8keebIEOMWgk-q4Wc3FYEw.jpeg" /></figure><h3>The Road To Hell</h3><p>We use <a href="https://eve-central.com/">EVE Central</a> (EC)— which provides a <a href="http://api.eve-central.com/api/marketstat/json?typeid=34&amp;usesystem=30000142">well formatted endpoint</a> — for collecting price snapshots. Nearly every spreadsheet jockey in EVE has used EC’s tools, or ones like them.</p><p>One of my first rules of data scraping is “keep the data like the source”. Transforming incoming data can cause headaches later. At best, there might be a readability problem, but at worst, useful data could be thrown away without any chance of recovery.</p><p>A problem arises though: EC splits the tree into “buys” and “sells”. Which transformation is best? Do you keep the data as-is, or pre-pivot the table to handle the data?</p><h3>Cementing Mistakes</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/814/1*7rurE94yGXd9Iaza9DYMsA.png" /><figcaption>Forgive me, for I have sinned — MySQL</figcaption></figure><p>This was the schema we settled on when writing the scraper. There’s a lot going on here, so let’s break it down.</p><h4>Splitting Buys and Sells</h4><p>This was, by far, the biggest mistake in storing this data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/456/1*rNepR9xscHNduFdGctQoZw.png" /><figcaption>FML — Every query looks like this</figcaption></figure><p>To plot the data, we want each vector in its own column. This costs a pivot in SQL. This is expensive, making queries heavy — And forget about debug readability.</p><p>I ended up torn between two styles.</p><ul><li>Column-wise: Each buy/sell key in a column</li><li>Row-wise: Maintain the orderbook style, double up on rows</li></ul><p>In the end, I sided with the data source, but it was very much the wrong decision. It even caused bugs downstream: when cleaning up after a bug, unexpected holes ended up in the data frame thanks to the required pivot.</p><h4>Date and Time — No DateTime</h4><p>This brilliant idea went something like: “if I split out hours ahead of time, timezone filtering would be easy!” This idea was bad. Though we don’t keep the same resolution across all locations, joining data could be done at application time rather than pre-split in the database.</p><p>What’s more, not having datetime by default, sorting/filtering is actually <em>harder</em>. I optimized for a problem we never ended up having.</p><h4>Location Information</h4><p>EVE Central allows for some query filtering, and I wanted to allow for wider-ranging queries in the future. But things played out differently than expected. Though we could add more EC data, other EVE updates made this the wrong path.</p><p>Also, this is why we normalize. If more information was required, it’s a quick JOIN away with <a href="https://www.fuzzwork.co.uk/dump/">EVE’s SDE</a>.</p><h4>WTF Indexes</h4><p>I have no idea what’s going on here… These are bad and I feel bad…</p><h3>If You Could Do It Over</h3><p>Though we are transitioning away from SQL to noSQL, let’s talk about a better plan. I am by no means a SQL expert, so YMMV.</p><ul><li>Change the data source to <a href="https://esi.tech.ccp.is/latest/#!/Market/get_markets_region_id_orders">ESI</a> — get data straight from the source</li><li>Pre-pivot useful data — it’s easy to add/drop columns in queries, not rows</li><li>Don’t prematurely optimize — Don’t design for problems you don’t have</li></ul><h4>Better Summaries</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/581/1*qLU2Sk5AU3FjcDQum0SWcw.png" /></figure><p>Since the stats most customers will use are already summarized, I’d pivot <em>across</em> to columns instead of <em>down</em> to rows. This allows for a saner column data set going into graphing, without the need to pivot coming out of the store.</p><p>It might be tempting to keep the raw data, but snapshotting the orderbook is extremely expensive. Without filtering, we expect to see 200k rows/query. And filtering still leaves us with 75k+ rows. As a temporary store, this data might be interesting, but keeping more than a few days worth of data would destroy our budget.</p><h3>Lessons From Mistakes</h3><p>The real pain with schema design is you may not realize the cost of your mistakes until it’s too late to fix them. Also, I have found it extremely difficult to get good advice; instead relying on hindsight to learn from awful ideas. SQL is an incredibly powerful technology, but benefits from an expert eye when designing systems with more than a couple simple tables.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ba211bcc3c8d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Let’s Talk About (Python) Logging]]></title>
            <link>https://medium.com/@HLIBIndustry/python-logging-custom-handlers-f3ba784a9452?source=rss-d55d2a8c9f94------2</link>
            <guid isPermaLink="false">https://medium.com/p/f3ba784a9452</guid>
            <category><![CDATA[logging]]></category>
            <category><![CDATA[python3]]></category>
            <category><![CDATA[webhooks]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[coding]]></category>
            <dc:creator><![CDATA[Lockefox]]></dc:creator>
            <pubDate>Fri, 31 Mar 2017 16:46:17 GMT</pubDate>
            <atom:updated>2017-03-31T16:46:17.783Z</atom:updated>
            <cc:license>https://creativecommons.org/licenses/by-nc-sa/4.0/</cc:license>
            <content:encoded><![CDATA[<p>Coming from a self-taught, hack-tastic, stack-overflow, LMGTFY background, finding best practices has required a lot of unlearning. <a href="http://docs.python-guide.org/en/latest/">Python Hitchhiker’s Guide</a> is an excellent resource, but I end up learning most from personal failure. I imagine many other homegamer-grade devs feel a similar frustration in their coding, maybe unaware of built-in tools or how to use them.</p><p>I’ve come around to logging in a big way recently. Though swapping out debug print statements for a proper logger seems easy and obvious, I think many devs underestimate the power of <a href="https://docs.python.org/3.6/library/logging.html">python’s built-in logger</a>. Not only can a good logging system aide debugging and development, but it also can be used to automatically alert humans when things go wrong.</p><h3>How Python Handles Logging</h3><p><a href="https://docs.python.org/3.6/library/logging.html">Python’s logger</a> is split into two parts, the logger and its handlers. The logger allows messages to be passed into the logging chain, and the handler objects dictate what happens to those messages.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/878/1*SqUfUKXaoJlbDHL6WErPmw.png" /></figure><p>This means you can configure and attachhandler objects to suit the needs of your app. Need to debug print to stdout — StreamHandler(). Need to log to file, but auto-delete after x days— TimedRotatingFileHandler(). Need to use the system log— SysLogHandler().</p><p>What’s more, because handlers are called at execution time, humans can be alerted of bad behavior immediately using something like SMTPhandler(). It’s so useful, I have written custom webhook handlers so critical errors make it to HipChat, Slack, or Discord.</p><h3>Life Hacking Your Logging Tools</h3><h4>Common Coding Your Logger</h4><p>To make my life easier, I wrote a log-builder in <a href="https://github.com/EVEprosper/ProsperCommon">ProsperCommon</a> to wrap major functionality in a standardized way: defining things from <a href="https://docs.python.org/3.6/library/logging.html#logrecord-attributes">formats</a> and file behavior to specific handler wrapping. Now every app can import the one library and easily setup the logger it needs in a few lines.</p><p>Though this is powerful, I would caution against distributing this on <a href="https://pypi.python.org/pypi">PyPI</a>. I use <a href="https://gemfury.com/">GemFury</a> to host personal libraries and keep tighter control on releases.</p><h4>Make Your Own Handlers</h4><p>Don’t be afraid to roll-your-own handlers. Though python comes with many handlers built-in, sometimes DIY is best.</p><pre>class MyNewHandler(logging.Handler):  # inherit from Handler class<br>    def __init__(self, special_args):<br>        logging.Handler.__init__(self)  # run parent __init__ class<br>        ## Do custom setup stuff <br>        ## Save special_args, setup files, etc</pre><pre>    def emit(self, record):  # override Handler&#39;s `emit` method<br>        ## Do log-time actions here <br>        ## Send to webhook, send to file, write an email, whatever</pre><p>By hijacking the existing Handler class, it’s easy to add whatever custom behavior to a new handler. Additionally, platform-specific flourishes such as HipChat’s coloring or Slack’s formatting can be implemented here. Anything in emit() will be executed when a log message arrives.</p><h4>Make Your Own Levels</h4><p>Especially useful when using logging as an alerting service, custom levels provide a way to better control logging behavior. Refer to <a href="https://docs.python.org/3.6/library/logging.html#logging-levels">Logging Levels</a> for more notes.</p><pre>logging.addLevelName(int_custom_level, &#39;ALERT&#39;)<br>def alert(self, message, *args, **kwargs):<br>    &quot;&quot;&quot;<a href="http://stackoverflow.com/a/13638084">http://stackoverflow.com/a/13638084</a>&quot;&quot;&quot;<br>    if self.isEnabledFor(int_custom_level):<br>        self._log(int_custom_level, message, args, **kwargs)<br>logging.Logger.alert = alert</pre><h3>Embracing Best Practices</h3><h4>Library Loggers are Null Loggers</h4><p>Though you should absolutely write logging messages everywhere in the codebase, there are a lot of places where yielding those log messages isn’t desired. <em>Push</em> loggers into libraries, rather than collecting logs unexpectedly. Also a NullHandler will avoid errors such as: AttributeError: ‘NoneType&#39; object has no attribute ‘debug&#39;</p><pre>import logging</pre><pre>DEFAULT_LOGGER = logging.getLogger(&#39;NULL&#39;)<br>DEFAULT_LOGGER.addHandler(logging.NullHandler())</pre><h4>Only Create Loggers In Main</h4><p>This ties in with the previous point: the only place that should create an active logging object is main(). There are many services that can execute main, like create_app() in <a href="http://flask.pocoo.org/">Flask</a> or main(self) in <a href="http://plumbum.readthedocs.io/en/latest/cli.html">Plumbum</a>, but active loggers should only come from these runtime functions.</p><p>We want to control behavior between modes. Production, Testing, and Debug are three different modes with their own requirements. Testing shouldn’t alert chat-webhooks. Production shouldn’t log to stdout.</p><h4>Capture Those Exceptions</h4><p>Funfact: str(Exception) == &#39;&#39;, be careful when logging exception objects.</p><pre>except Exception as err_msg:<br>## Bad Logging ##<br>    logging.error(&#39;Things went wrong: {0}&#39;.format(err_msg))<br>## &quot;Things went wrong: &quot;</pre><pre>## Better Logging ##<br>    logging.error(&#39;Things went wrong: {0}&#39;.format(repr(err_msg)))<br>## &quot;Things went wrong: Execption_class message&quot;</pre><pre>## Best Logging ##<br>    logging.error(&#39;Things went wrong&#39;, exc_info=True)<br>## &quot;Things went wrong &lt;stack_trace&gt;&quot;</pre><p>Though <a href="https://docs.python.org/3/library/functions.html#repr">repr()</a> is a better tool for capturing the exception name, using exc_info=True will attach a stack trace into the log message. An excellent resource for debugging when things go terribly wrong, though maybe overkill for less-than-critical errors.</p><h3>Death To Print(), Long Live Logger</h3><p>Switching to logging has changed the way I approach code. Instead of working backwards at the end to change debug prints into useful information, logging messages act more like comments. Additionally, I’ve gained the confidence to launch always-on services such as webhosts and bots, because someone will be notified when things go down in flames. It also makes bugs obvious and loud at work, compelling devs to fix bad behavior.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f3ba784a9452" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>