My Sunday, 10K and not so big data

After 200+ blog posts, my addlepated brain isn't even going to request a fact-check on this one: I don't like running. Never have, never will. Our church has, in recent years, supported World Vision, which (among many other things) helps Africans obtain clean drinking water. One of their fundraisers is the team in the Chicago Marathon. It's a wonderful cause, and many folks from our church- including committed couch potatoes- have stepped up and taken up running just to be able to help out. It truly is a great cause, but it's not me. Any number of quotes could sum up my position here, but Donnie and Marie Osmond's theme song from their television series of many years ago sums it up pretty well: "She's a little bit country, and he's a little bit rock 'n' roll". Clint Eastwood's "Dirty Harry" was even more spot-on: "A man's gotta know his imitations".

Yeah. "Don't like" doesn't even begin to describe my feelings about running.

When I was in the Air Force, running was a thing we had to do. The area I was stationed at was quite hilly, and I constantly had shin splints. We used to get together a few times a week after class (it was a training installation) to run. Yuck.

I've long since given it up, but at the time I smoked.

Sooo..., just before the command was given to start the run, someone would yell, "SMOKERS TO THE REAR!" and about a dozen of us would quickly move to the rear in Cirque du Soliel precision, and the run would commence. We would start off as soon as we had lit up. I kid you not: we smoked while running.

So yeah, even though I've quit smoking, I still hate running. I respect those that do run- for whatever reason- but it ain't me, babe. No, no, no, it ain't me babe.

So... 10K: it's not running. It's steps.

I've worn a pedometer on a regular basis for the past few years. I've considered other devices, such as a Fitbit, but in the end I've always decided in favor of my Omron pedometer. As much as I'm a fan of big data, there are just some things I don't want to put in the cloud, and my exercise activity is one of them. I download data from my pedometer to my PC, and I can view it in a nice frontend there. Or, I can download a .csv and use my own analytical tools.

10K steps is a good benchmark. There seems to be a concensus on the internet that 10K is a good daily goal to reach for us in the sedentary Western daily routine. As I have a desk job (AND work at night!), 10K is generally a challenge. So today was a good day.

As always, I am hochspeyer, blogging data analysis and management so you don't have to.

Data Intellligence... and a "slight" oops

I want to start off by stating for the record (whatever that is) that although I'm okay with doing it, data entry is NOT my forte, and it is not something I normally enjoy doing. Mind you, I'm not bad at 10-key entry- I learned it through trial-and-error, and am not bad at it still. Still, it isn't one of my favorite data activities.  And yet, in the course of building Forty-Two (my database), I find that I am tasked to do data entry at every turn.

So, ... I do data entry... because I must.

I'm trying pretty hard to keep the blog from becoming a somewhat witty changelog for my database, so I'll throw out a few speeds and feeds and then move on blog-wise.

The other day, I had just posted something on twitter, and twitter's AI recommended a few connections. One of them them turned out to be a long lost relative. I'm not going to go into the details here, but I'm kinda excited, as this is the 1st time I've run into a relative online based upon a machine learning suggestion.

WAIT FOR IT...

I accidentally pressed "Publish" instead of "Save" the other day, and I think that's a first for me. For those who read the unedited, incomplete blog post, I apologize.  In any event...

It's Saturday night in my world. My Friday at work was pretty quiet, and for the first time in several weeks I got home at a decent hour. Got a decent amount of sleep, had a great time of worship at The Bridge, and then Jennifer and I took Meerkat over to our local Walmart Neighborhood Market, where (amongst other things) we picked up a couple of their most excellent take and bake pizzas. As its late, I'm just finish this with these few updates and call it a night.

My last task for the night is some Windows Media Player updates. By "updates", I mean I'm ripping several CDs and deleting a few from the library.

As always, I am hochspeyer, blogging data analysis and management so you don't have to.

Happy birthday, Meerkat! (*and: my Bacon number!)

I haven't mentioned Meerkat, our trusty Subaru Outback, in some time. It's a good time, though, as Meerkat's 2nd birthday was the 2nd of April. Granted, she's a bit older than that... but not much- that's the day we brought her home. And for those who may be wondering why I refer to our car with a feminine pronoun, it's merely borrowing from the naval tradition of referring to ships with a feminine pronoun.  And Jennifer reminded me that much like that day two years ago, it snowed. So, Happy Birthday, Meerkat!

Oh, and we just turned 11,000 miles (17,600 km) on the odometer. Now, that doesn't sound like a lot, and in truth it really isn't. According to http://project.wnyc.org/, the average commute for my ZIP (postal) code is around 28 minutes, which is above the national average of 25.4 minutes. My average commute is under eight minutes; on a bad day, getting caught by a train, my commute time will still be under twelve minutes.

In an effort to keep this from becoming a database changelog, I'm just going to say that work on the Lego datasaet is proceeding nicely. I haven't done anything with Forty-Two lately, but that's okay as my time has gone into the dataset.

Lastly, I'd like to introduce you to a term I use quite often IRL: "my good e-buddy". I use this most often when I'm referring to someone whom I've never met except for online contact, or someone I do know IRL, but who is more of an acquaintance than anything else. The reason I mention this is my good e-buddy @lindaregber had an interesting tweet the other day which I liked well enough to RT. She posted a study from Research at Facebook which states that there is an average separation of 3.57 for Facebook users; in other words, you are only separated from ANY Facebook user by <4 persons. That's pretty cool; I'd like to see something along the lines of The Oracle of Bacon for "ordinary folks". Although I'm not a famous person, and I have never acted, I have a Bacon number of 3, which means I'm closer to Kevin Bacon than I am to any random Facebook user! How? Alex Trebec was in Dying Young  with Vincent D'Onofrio, who was in JFK with Kevin Bacon. So, how do I get the 3? Alex has a Bacon number of 2; I met Alex in Frankfurt a.M. after trying out for Jeopardy in a Stars & Stripes sponsored event.

As always, I am hochspeyer, blogging data analysis and management so you don't have to.

This IS normal(ized) in my world

I've mentioned once or twice (I think) that I'm not a professional DBA; I do this stuff for fun! (??? ORLY?)

Yup, 'tis true: it's something of a hobby, or repast, or possibly a fairly intense interest. In any event, this interest has made me money before, and hopefully will do so again. This particular post is especially ironic from a few angles: for starters, I've also mentioned that I try to do as much normalization of a database right from the start, and if you read the last post AND/OR you're a dba or involved with databases, you'll note that I've pretty much thrown out all of the data normalization conventions for this flat database. The reason is pretty simple: I know my client (me!), and I know what data WILL be needed, and what data MIGHT be needed. Also, in the end, this Excel workbook is still data.

So, to recap, I have a .txt file that I have converted into something resembling a flat database. Three of the current four columns have repetitive (if not identical) data. Why?

The answer is simple in my context, but if this were someone trying to apply a fix to an existing problem for, say, a paying client, and trying to explain that you had to create repetitive data to make the resulting database entries more efficient... well, not necessarily so simple.

I was talking with one of my coworkers earlier this week, who is a data guy at heart. A month or so ago, he had taken a stab at trying to extract those target part numbers from the text file and had dissatisfying results. The other day, I showed him the new and improved .xlsx data file, and warned him that this was something akin to anti-normalization. When he looked at the file, he cringed in agreement. However, once I explained exactly why I had created columns with duplicate or near duplicate data, he agreed with my process. And therein lies the proverbial "rub": even though I've formatted this .xlsx file as a flat database (which came to me as a .txt file, which I'm guessing was extracted from some other format), it isn't a database- even a flat one.

It's a dataset. Period. And as it is a dataset, it needs cleansing, not normalizing.

Now, having said that, the casual reader is probably wondering how this dataset will be utilized. Well, THIS is where it gets interesting (ORLY??). Before I can import ANY data into the database, it needs to be usable. In my world, this means I need to be able to count pieces by size, color, family and type; in other words, I need to make this usable by Forty-Two (the master database). So, once I clean up the data, my goal is to import all of it into Access as a part of the inventory.

That day is still far away.

As always, I am hochspeyer, blogging data analysis and management so you don't have to.

Data cleansing, or Purge #4

I had about 50% of what passes for a normal length post written when I decided to scrap it and start afresh. I think I've mentioned that I truly hate rewriting anything, but the post just wasn't going anywhere. More importantly, I had no logical transition to a data breakthrough.

For the past few weeks I've been working on my Peeron-based database (Working with someone else's data and Meanwhile, back in the Secret Underground Lair...). It had been slow going, and I asked a coworker about it. My shy coworker suggested that my solution might be found in Excel.

I thought about that for a bit. I'm not bad with Excel (and that statement alone could be the subject of SEVERAL blog posts!), so I did a bit of exploring. In Excel 2007, on the Data tab, resides "text to columns". As I recall, this feature has been available for some time, but this has been the first time that I've used it. Recapping my challenge: I've got a text file (the Peeron data) which has the part numbers I need, plus much other ancillary data which I may or may not need.  Important part: my source data is a .txt file.

So, I copied the master file to create a new working file. In the old master file, I executed text to columns; in the new (working), and then copied column A to the working file. This accomplished a few things: it preserved Peeron's numbering, as well as their descriptions.

Skipping a few steps, what I have now are four columns: Column D is unmodified Peeron data, column C is a size column, column B is called "Base w/mods" and column A is the "Base" column. I plan to add at least one more column at some point which will be a custom "internal" part number.

But... for now, I'm happy with my data, which is more than I've honestly been able to say about it for a few years! Seriously, this is one of those rubber meets the road blogs,where the plan actually comes together and progress is made! As promised, I've added another sheet to the workbook, and have begun extracting the extraneous data entries from the main spreadsheet, such as stickers, obsolete and moved part numbers.

Most all of the remainder of the process is going to be manual, but the text to columns wizard probably saved me upwards of sixty+  hours of work, assuming 300 rows per hour with the old cut and paste method I had been using. And I say upwards, because I think I only achieved 300 rows per hour once; generally, I've been at 200 or fewer rows per hour.

I'm desperately trying to get this published. I've said on more than one occasion that sometimes blogs just seem to write themselves. Well, this is not one of those occasions.

I'm going to close with a few speeds and feeds. As best I can tell, there were 18,512 rows of data in the original Peeron file. With the new and improved format, I've culled out close to 900 rows of data which are of no use to me. I still have quite a way to go, but for the first time in some while I'm seeing that there is a pot of gold at the end of this particular rainbow.

As always, I am hochspeyer, blogging data analysis and management so you don't have to.

Meanwhile, back in the Secret Underground Lair....

Those kind folk who have been following this blog for a bit may be aware that our home office, the Secret Underground Lair (SUL), is undergoing a bit of a reconfiguration. That's actually a "bit" of an understatement.

Before going to work last Monday evening (3/7 or 7/3 depending on your calendar), Mr. T and I spent about a hour of quality time grunting, straining and sweating as we resituated eighteen cartons of books. While moving the books, I discovered a few things: not all of the boxes or plastic storage containers were properly or efficiently packed, and more importantly, there are MANY books in storage that I need access to, so we will be swapping some books out.of storage and replacing them with books which really should be in storage.  It was worth it, though: for the moment, we have TWO walkways in the office. Mr. T tried some scenarios in Microsoft Visio, though, and is currently of the opinion that we will not be able to maintain this corridor if we want the best use of our limited space. I hope to do a bit of work in the Dungeon to free up some additional space. As we are on the cusp of Daylight Saving Time (ugh!), it's nearly midnight, so any more SUL work will take place tomorrow (Sunday 3/13 or 13/3!). And if anyone is doing the math, it has taken me seven days to get this far with this latest blog entry... overtime is great for our bottom line, but it definitely puts a damper on other things- like blogging!

Data!

In my previous post Working with someone else's data, I mentioned that I'm using peeron.com's data as the basis for my Lego data. I also indicated that although it's a great list, I have a good amount of work to do before it is useful to me. The following two lines are a sampling of consecutive rows copied from the .txt file, and illustrate quite well my "issues" with their data:

3001px2 Brick 2 x 4 with Eyes and Wavey Mouth Pattern   nose, nostril, snout
3001px20        Brick 2 x 4 with Red Danger Stripes and Two Horizontal Stripes Pattern

For starters, peeron.com has done a nice job of making a list of Lego parts. Their website is excellent, and their search engine is very nice. However, I'm not interested in their frontend, or even their instruction scans.

I'm cataloging and categorizing MY data, and their part number list is what I need. And although I've also mentioned before that this list is at LEAST four years out of date, up to date is not what I initially need. I need the basics first. And although peeron's 18,511 rows of data may seem impressive to someone who isn't an Adult Fan of Lego (AFOL), for me the list is horribly incomplete as I have purchased sets that were manufactured after 2012. Also, it has a lot of data that is extraneous for me, primarily part numbers for sticker sheets and parts which have somehow been redesignated, renumbered or somehow made obsolete. So, I need to do a bit of rework on their data.

The red bolded text above is two consecutive rows of data copied from their .txt file. Based on these two lines, I hope to show how much work I have cut out for me.

For starters, I took their .txt file and copied it into an Excel spreadsheet. My first task is to make two columns out of this data- one for the part number, and one for the description. I'm using 3001- this is the Lego designation for the ubiquitous 2x4 brick which nearly every human being has stepped on at least three times. The next few characters describe the variation/description of the brick. So far, so good. First problem: I don't know of an easy way to separate the part numbers from the descriptions- I'm certain there's some software that can parse the data, find the first empty space, and move the next set of characters into a new column. So, until I find a software solution, I'm doing CRTL+X, CRTL+V to manipulate things. Second problem: Once they are separated, I can't sort these, because although px2 comes before px20, px20-23 come before px3 in Excel. And some of these numbers run into the hundreds. So, nearly every suffix will need to be reformatted to actually display in numeric order. To do this, I'm going to create a new part number that my database will use; if I ever create my own Lego website, only the original Lego part numbers will be used.

After the columns are created (and before I start tweaking the part numbers), I'm going to pull out the extraneous part numbers and put them in a separate worksheet. After that, the remaining data descriptions will get trimmed down and standardized with a few hits with Find and Replace.

I've currently got 2,022 rows separated- roughly 11% of the 1st stage is complete. That's it for now.

As always, I am hochspeyer, blogging data analysis and management so you don't have to.

Working with someone else's data

At a certain point in one's data career (plain English: pretty near every day, and with nearly every piece of data one normally touches!), you will have the "opportunity" to work with data someone else has prepared, or at least accumulated or aggregated or in some way modified. As I create Forty-Two, "most" of the data I will be using is data which I have entered (created) into a table. There is one notable exception to this, though, and it is for me a moderately to severely painful one: Lego parts.

As mentioned in a previous post, my experience tells me that the best place for my Lego data is in a spreadsheet- at least initially. And although calculations can be done in Access, Excel is much easier for me. And, they can be linked at a later date.

However, there is a HUGE caveat to the Lego data. The title refers to someone else's data. In my case, I'm using peeron.com's parts list. It's the best one that I've found, and many other AFOL (Adult Fan of Lego) sites use it.as a parts reference. The problem with the list and site is that they're horribly out of date. The list has a very standard naming convention, and the last update was nearly four years ago at the time of this writing. However, as it is the best, easiest to use and most complete list currently available, I've decided to use it. I can update newer parts as I find them on other websites.

The other issue with the Peeron data is that it is a .txt file. Not bad when importing to Excel- just copy and paste. However, to make it usable, I have to manually edit the 18K+ rows of data. As I'm in no great hurry to finish this phase of the project, it's not too much of an issue. Still, .... I know a guy (as they say) who might be able to help. More on that later.

The final issue with Peeron is that its creator strove to make it a very complete parts list. As such, there's a great deal of data which I actually don't need- stickers and superseded part numbers are two types which come to mind immediately. So, if my guy can fix my primary issue, the other issues will be much easier to deal with. If not, then I still have a great deal of work ahead of me. UPDATE: I'm not really surprised- but also not horribly disappointed- that we were not able to fix the data.

Before I forget, I wanted to post a brief update on the blog itself. I'm not quite sure when I did this last, but I think it was around the time the blog hit 10K viewers. As of today, the blog has over 15K viewers in fifty-five countries on six continents (c'mon, Antarctica!). Africa is represented by three countries, Asia by seventeeen, Australia by one, Europe by twenty-seven (I'm counting Russia in the Europe column rather than Asia), North America by six and South America by one. What's most amazing to me is that of these fifty-five countries, I could only count seven where English was either the official language, a dominant language, or one of a group of commonly accepted languages.

To each and every reader- THANK YOU!

Last: a small compilation of my blogs dealing with data (for those who are interested in how a small-time operator handles data)

http://hochspeyer.blogspot.com/2016/02/you-said-this-was-about-data-analysis.html
http://hochspeyer.blogspot.com/2015/06/data-science-pt-1.html
http://hochspeyer.blogspot.com/2016/02/a-database-against-rules.html
http://hochspeyer.blogspot.com/2015/05/data-defined.html
http://hochspeyer.blogspot.com/2015/02/forty-two-v7-or-so.html

As always, I am hochspeyer, blogging data analysis and management so you don't have to.