The "reboot" seems to be something of a thing in Hollywood these days, and so it only follows that Life would imitate "art" as I reboot my Lego database.
For those who have been following this blog for some time, I think you may be aware of my Lego database project. It is one component of "42", my personal, ultimate repository of those possessions of mine which I have chosen to catalog.
I've just started using Microsoft Office 365, and the Lego data came from rebrickable.com. Their dataset is very comprehensive, but I don't think I've ever downloaded ANY Lego dataset that was usable by me as downloaded... and this data is no exception.
I mentioned that the data is quite comprehensive, which means it contains things which I don't necessarily need or want. For example, I do not care about any decals, paper or cardboard items, books, or even certain parts of the Lego product line. So, these need to be removed. The data also appears to come from more than one source, and formatting is necessary. Punctuation needs to be removed from many entries, and part names need to be standardized. Some data needs spelling changes- there are cases of the Queen's English being used, so "windscreen" and "tyre" need to be changed to "windshield" and "tire". These are the major changes that need to happen before I can even think about exporting to an Access database. And these are just a few examples.
But, I digress. Here are the numbers as of December 4th.
When I first started out, there were approximately 29,000 records. As of last night, after culling out items which I knew I would not be inventorying, I had 27,164 unique items. When I deduped the group to include ONLY the base part numbers- excluding all decoration variants, I ended up with a working list of 8,747 unique base part numbers. As I copy the part descriptions to the new inventory list, they are being further culled.
I am now at the point where everything must be done by hand. I find myself going back and forth between rebrickable and my flat database, verifying that the part number in the list is a part that I (may) actually own... and want to count. At a certain point, some of the data is subjective, and even though it is valid, I will count a complete assembly rather than, say, a special tile, wheels and tires as separate parts.
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
Showing posts with label data cleansing. Show all posts
Showing posts with label data cleansing. Show all posts
Monday, November 19, 2018
Wednesday, March 30, 2016
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.
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.
Subscribe to:
Posts (Atom)