Rarst.net

Google Refine for messy data cleanup

Rarst — Sun, 22 Jan 2012 16:00:01 +0000

When there is even slight potential that something in spreadsheet(-like) data can be generated or filled wrong – it usually will. Fixing up data to be useful usually involves spreadsheet editor, wasted time and a lot of evil thoughts radiated.

Google Refine looks loosely like yet another spreadsheet editor, but is built precisely for fixing and cleaning up data.

What it does

Unlike most Google products Refine is software that needs to be downloaded and run locally. Technically it works like a local web server, which from practical point of view means that its interface is a web page in your browser of choice.

Data can be imported and exported in variety of formats and in-between is saved as part of Refine project.

General workflow is essentially picking and using facets to isolate sets broken/undesirable values and editing, removing or otherwise getting rid of them.

Strong features

Facets can be picked from predefined suggestions or constructed from scratch, using functions from provided expression language. They can range from very simplistic (facet by presence of specific word) to very complex (facet by largest Unicode character code, present in value).

For textual values Refine offers clustering feature that runs values through different algorithms and suggests those that are similar to be grouped together.

While spreadsheets editors often rely on dynamic values (columns based on other columns in some way), Refine treats data as static and applies all changes immediately. However all changes go into a log, which allows to roll back to any point as well as export changes as set of instructions an run them differently or even in different project.

Downsides

Refine is not overly user-friendly application. It gives user a lot of control but cares little for some simple things that would make a difference. For example it is somewhat weak with type casting – as result empty (null) values tend to wreak havoc as inappropriate input for operators. Easily fixable by changing them into empty strings (see the meaningful difference? neither do I) but after umpteen times just makes you wish it did that on its own.

Web page as interface is surprisingly robust. Still there are some things that just don’t work usual way (like aimlessly scrolling through) and facets tend to evaporate when project is closed, which is huge pain when you just nailed that perfect setup for the task.

Overall

Google Refine is rare combination of functionality that is both specific and flexible, while free and actively developed. It has plenty of learning curve (start with introduction videos) but it pays off well by turning data cleanup into relatively smooth and almost enjoyable process.

Home http://code.google.com/p/google-refine/

Possibly related posts:

WordPress community by the numbers

Rarst — Sat, 07 Jan 2012 16:00:04 +0000

One of my quiet resolutions for this year have been to spend some time looking into data processing and visualization. It is a little hard to look into new topic with little idea where to start, so I’ve been putting to the side some bookmarks on topic.

In a while I had a topic and some tools to get this small personal project going – I took results of large WordPress survey that run last summer and tried to shape it into visual reflection of community that took survey.

So after some serious struggles with data aggregation, defeating broken encodings and normalizing more currencies than I have ever seen in my life… I present you WordPress Survey 2011 in pie charts. And some bars.

Click through to for full dynamic version.

I will much aprecciate feedback how did this attempt go and probably will write about tools I used separately.

Possibly related posts:

Rainmeter – flexible desktop widget framework

Rarst — Mon, 26 Dec 2011 16:00:40 +0000

I had recently put together new computer and one of the apps that needed replacement was Samurize as long unmaintained and not eager to play well with 64 bit Windows version. I was very fine with how Samurize worked for me (my own simple config to sit on top of screen) so I looked for something very similar and Rainmeter widget framework is commonly mentioned alternative.

What it does

Rainmeter itself is an engine that runs in the background and powers up skins (I think widget is more generic term for such). Skins display data retrieved by Rainmeter – from as simple as free drive space to as complex as parsed out of remote web site.

Skins can look like anything and be visually rich (one of skins featured on site):

Or bland and minimalistic (my own work-in-progress skin):

Now that I compare it to my past Samurize config – my ability to put together something visually non-hideous had really improved since.

Strong features

The way Rainmeter works is very free-form – INI file based text configurations describe skins, which are mostly combinations of measures (data retrieval) and meters (visualization of measure output). Both have vast amounts of parameters and resulting skin can make use of image files (including rich color manipulation) and be interactive (react to data or user input).

App is very light on resources and plays well with most recent Windows versions, including 64 bit builds. You can even easily incorporate Aero transparency features in skins.

Downsides

While INI format is plain text and one of the easiest to grasp, it is really painful to bend it to describe complex concepts. Rainmeter probably started with functionality for which INI was perfectly sufficient, but more complex things (such as nesting rules and data calculations) are really pushing its limits.

As result it is often frustrating to express your thought in config or get absolutely unhelpful error message (hidden in log) that something that you are doing is not quite right. Pointing out that specific something is often not included.

Overall

It is hard to summarize Rainmeter, because how well it will work for you depends on how much you need from it and how dedicated you are to get it. On one end there are people who just want to download and use ready-made skin, on another – willing to study dozens of pages of manual (and maybe dive into Lua script support).

Anyway Rainmeter definitely is powerful and open source software project with native portable install option.

Home http://rainmeter.net/

Possibly related posts:

S3 Browser – portable client for Amazon S3

Rarst — Wed, 30 Nov 2011 16:00:08 +0000

Amazon S3 storage is quickly becoming staple for having some files in cloud. It’s reliable, affordable and big brand. I have to deal with it more often in context of WordPress backups and it is kinda inconvenient to just get file downloaded to your computer from S3.

S3 Browser is actively developed desktop client for managing files in S3 buckets.

What it does

S3 Browser takes your Amazon keys and connects to your account. Available buckets are automatically listed and clicking through them loads lists of contained files.

App gives you full range of file manipulation (upload, download, delete) and can manipulate both files and bucket themselves.

Strong features

Supports bulk operation, that is really nice when you need to get a lot of stuff up or down.

Aside from straight file manipulation S3 Browser also focuses on giving you access to many specific options, from managing buckets and user properties to configuring static web site hosting.

Downsides

App really, really, really likes to nag about Pro version – which allows commercial usage and has range of advanced functions, as well as more snappy implementation of transfers.

I suppose it’s effective at selling to people who need more features, but for occasional basic usage it is annoying.

Overall

App works nicely, has plenty of functions, native portable option and is very actively developed. The downside is heavy nagging and upselling in free version.

Home http://s3browser.com/

Possibly related posts:

Wallpaper size and layout for 1280×800 Android tablet

Rarst — Sat, 12 Nov 2011 16:00:21 +0000

One would think that 1280×800 screen calls for wallpaper of such dimensions. But apparently Google is set on shaking logic of wallpapers in Android, because it’s harder to figure out by version.

There is a lot of advice floating on trying to hammer wallpaper into tablet with various apps from market, but really it works great to use high resolution image and crop it to work without rescaling.

I am dealing with Samsung 8.9″ tablet with 1280×800 screen and Android 3.1. Your mileage may vary.

Image crop

When you want to set image as wallpaper, instead of going with it or asking how do you want it to fit – Android gives you confusing cross-shaped crop area to apply. Pretty much anything I have tried to feed it at first ended up horribly blurry and cut down.

Since wallpaper should fit screen 1:1 pixel for best looks the first issue to use image of correct dimensions. Bundled wallpapers look great and crisp, but there are too few of them.

Required dimensions

After some googling turned out that native wallpaper resolution is 1920×1408. See any logic connecting it to 1280×800 of real screen resolution? Neither do I.

Effectively this means that you won’t find much of such wallpapers around and will need to crop larger image in advance. And will need something really huge to crop, because even 1920×1080 of HD resolution is too small vertically.

Visible areas

Next, let’s deal with that cross shape. Wallpaper is never completely visible on screen. Instead:

two main areas are visible in portrait and landscape mode respectively;
areas to the left and right become visible when scrolling home screen;
area to the top becomes visible in applications menu;
area to the bottom becomes visible in home screen settings menu;
corners will be pretty much unreachable.

I’ve made a template to illustrate (clickable for full size version):

So despite total image size, only 800×800 central section will be visible in both screen modes.

After you find image of sufficient dimensions you can use template to precisely determine how to crop it best, before uploading to device:

And then, when device prompts you to crop, carefully drag out crop area to cover all of image both vertically and horizontally:

As result you will get perfect fit – no rescaling and crisp look of original wallpaper.

Overall

To sum it up – for best results you need large original image with compact central point. Asymmetrical wallpapers won’t work nicely at all, portrait mode won’t even scroll very far to the sides.

Possibly related posts:

HeidiSQL – portable and feature rich MySQL client

Rarst — Sun, 30 Oct 2011 16:00:38 +0000

I was stuck with native Query Browser for a while, but mostly because at the time it was stable and reliable choice for horribly outdated MySQL installation (embedded in software product).

Lately I’ve been revisiting MySQL frontend tools in context of web hosting and HeidiSQL clicked with me almost immediately as light yet feature-rich client.

What it does

As any tool of such kind the task HeidiSQL performs is quite defined – connect to database(s) and retrieve or manipulate data. So difference between tools boils down to performance, convenience and extra functions that go beyond running simple SELECTs all day long.

This one scores nicely on all:

database is accessed directly;

interface is very responsive and sufficiently visual;

there are plenty of functions to make life easier.

Strong features

As for general usage I like that app has live log that displays any and all operations you perform – both those run explicitly by queries and anything else like just navigating around. It is good reference, educational and queries from there can be quickly copied as text or into new query tab via context menu.

Among more impressive functions – export implementation is great. While going through exporting/importing through a file is no-brainer in most tools, HeidiSQL actually allows you to set up transfers between different servers. Not much difference on technical level, but huge improvement to the workflow.

Oh, and global (as in whole database or server) search. I wish someone told me about this some years back.

Downsides

While export/migrate is very impressive, import is somewhat more basic – SQL and CSV. Good enough, but stuffing some Excel file into table would seem common enough task to be supported.

At times interface gets confusing (in how-do-I-unpress-what-I-just-pressed fashion) and also a little glitchy (like buttons on toolbar duplicating).

Overall

Snappy and functional open source app with native portable version available. Doesn’t quite compete at level of commercial front-end monsters of a software, but still very useful.

Home http://www.heidisql.com/

Download http://www.heidisql.com/download.php

Possibly related posts:

Invisible operating system

Rarst — Thu, 20 Oct 2011 15:00:15 +0000

Couple of times this year I found myself in discussions, defending still using Windows XP. Operating system discussions are weird, being about features whose usefulness is imaginary (I’ve seen infinity times more mentions of ReadyBoost as good feature than computers using it) and never about features being really important.

Because the most important feature operating system can have is to get the hell out of your way and don’t prevent you from getting things done.

Logic ignored

Trying to be invisible should be main principle of any OS. The whole definition of OS is to get your software talking to your hardware. Nothing about being in-your-face and coming with kitchen sink of stuff.

However relationship of existing operating systems with this concept seems to vary between accidentally positive and openly negative.

Windows tends to slip into being invisible (takes couple service packs), which promptly makes Microsoft jump into action and stir the things up. Every other Windows release is horrible wreck, the only purpose of which is to keep you alert, moving and not stay in comfortable shell of good version.

Linux despises invisible. My experience with Linux started (many years ago) from swapping between two displays back and forth to get through installation process. Modern Linux distributions for desktop can fake invisible for some minutes, but it’s never long or sincere.

Mac OS doesn’t consider concept of invisible appropriate in its circles. As rest of the company products it strives to hammer sense of belonging into your head. Letting your mind wander to other things for a second is horrible mistake in its playbook (those giants docks look like tombstones to screen space for a reason).

ChromeOS seems to have stumbled right into invisibility… For the wrong purposes of course. It has nothing to do with letting you getting things done and everything with getting you to do things right way – locked into cloud.

Need to sell

The problem, that OS developers see, with invisible that it is too good for users to leave. If it works then don’t mess with it. So sensible. So bad for sales.

Thus starts the cycle where legitimate improvements are spun with time-proven bullshit, beneficial to sales. On the upward path you find yourself under shower of great (or seemingly great) new things. On the downward path you find yourself in a hole… With only path forward – the wheel rolls toward new version and path back is coincidentally cut off by that excessive hardware that seemed excessive and incredible on the way up… and suddenly became barely sufficient on downwards slope.

Bundle the candy

The biggest joke in OS features is bundled software. It makes perfect weights to carefully balance the motion… To the point that blatant crappiness is openly standard.

Microsoft maintains the most intricate and flexible file copying software, yet refuses to make half decent file manager. Mention of Internet Explorer makes good chunk of web developers hysterical. System utilities alternate between most boring software ever and scraps of third party licensed tech. Both often inferior to standalone products of Microsoft and lone freeware developers out there either.

Overall

The invisible operating system is an ideal, strangely at war with goals and practices of those who actually make operating systems. Would we ever see one? One can hope.

And so far Windows XP is as close as it gets. Until the wheel rolls so far ahead I would have to catch up.

Possibly related posts:

Are mobiles what PCs would be without Windows?

Solarized – comfortable color scheme for code

Rarst — Thu, 29 Sep 2011 15:00:39 +0000

I am strongly visual person, yet choice of colors is something I am no good at. While I can appreciate it, the process of going from nothing to working ensemble of colors falls on overly artistic side for me. That is why I am always charmed even more by color combinations, that are not only pleasant, but functional in design and mathematical in nature.

Colorbrewer is my constant reference for graphs and now I have discovered Solarized – color scheme aimed at presentation of code and rich text.

Basic principles

Solarized includes sixteen colors, split into content (main text), background and accents. The most impressive trick as that it includes both light and dark backgrounds which work with same text colors. Accent colors can be used to put together common schemes (analogous, complementary and so on), but amazingly they don’t fall apart even in more wild combinations.

Resources

While Solarized as scheme is essentially set of colors (and provided as such in more color systems that I know) it was engineered to work well with code highlight.

It is provided as ready-made color schemes for number of code editors, terminal apps, color palettes for graphical editors and build instructions for custom implementations.

Practical experience

Since it caught my attention in context of PhpStorm it was no-brainer to try it with. Color schemes for PhpStorm are available, only bit hidden under umbrella platform name IntelliJ IDEA.

While native color scheme in PhpStorm is perfectly functional, I found Solarized considerably better experience. It is more pleasant to look at and it handles multiple colors better – noting jumps out at you, while legibility and contrast are perfectly noticeable.

Overall

Solarized might look a little washed out and not overly flashy, but it has a lot of amazing aspects and great color scheme for code. Also I am already itching to bend and apply it for other purposes. :)

Link http://ethanschoonover.com/solarized

Possibly related posts:

WorkFlowy.com – ultimate todo.txt

Rarst — Sun, 11 Sep 2011 15:00:16 +0000

Some days ago I had another spike of desire to organize things. Which is typically fruitless attempt to use some todo (fails because of rigid rules) or mind map (fails because of mess) solution. todo.txt is staple name for a reason – plain text file, organized by you and to your taste, is about only thing that doesn’t drive you crazy. Or at least not too fast.

But I just might have found something better for myself, because WorkFlowy web app manages to deliver all of plain text experience and more, without typical blunders.

What it does

It is a little hard to come up with precise description for WorkFlowy. It brands itself as “organize your brain” tool, but seems to be mostly referred to as “todo list” type. Truth is – it’s neither mindmap or specialized todo list. I’d define it as list editor.

The interface is single-screen and sheet of paper lookalike, while content itself is organized into giant nested list. You can focus on specific list branches (and quickly jump out via breadcrumb navigation) and fold/unfold them. You start with empty list, so it is completely up to you how to build and shape it. Any list item is effectively list root itself and you can add notes if you need it to keep additional text.

There is no rich markup and very few dynamic elements:

links are made active automatically;
list items can be marked as completed (you can toggle global show/hide completed mode);
words starting with # (tags) and @ (people) are turned into quick search links.

Strong features

The user interface experience is not just polished. It is probably one of the most convenient and snappy web apps I had ever tried. Even with minimalistic approach there are quite a few interface concepts, yet they become natural in seconds.

Speed is strong focus, both for WorkFlowy itself and how you use it. There is extensive set of hotkeys and you can enable in setting optional on-screen cheat sheet for them.

Any section of list can be shared for viewing or editing. You just get unique URL to send people and they can see or contribute to that specific part of list. They don’t need to register with WorkFlowy for either and changes they make are synced to you very fast.

Downsides

I encountered quite a few times annoying bug with last character in lines disappearing. Can’t reproduce it reliably and according to email reply from developers Opera is not officially supported (but works quite fine in practice).

There is mobile version and I tried it for the sake of complete review. It kinda works, but isn’t as smooth as desktop web browser experience. My phone is probably closer to low-end of current Android standards, so your mileage may vary.

Needs and wants

At start I had constant “needs more features” thoughts, but really mindlessly stuffing it with features is more likely to spoil experience, than improve it.

One aspect I am kinda concerned about is backup. I started with small list just-to-try and in couple of days WorkFlowy sucked inside all of my work and most of my life. It is very easy to get plain text copy of your list with export feature, but that is manual operation and I strongly believe that automation is essential for proper backup. Something like “email me my list every day/week” would be great to have.

Oh, and I kinda hope there will be an API down the road… Just because it would rock and open some integration opportunities. :)

Overall

I don’t think WorkFlowy is quite whole-brain organizing thing, but it is exceptionally good way to organize things, that make sense as a list. It quickly becomes essential tool and being still in underknown/development phase… I really look forward to its future.

Link https://workflowy.com/

Possibly related posts:

Zalman ZM-MFC3 fan controller

Rarst — Sun, 21 Aug 2011 15:00:16 +0000

Friend had asked me to help with fans getting old and noisy in his PC (which I put together for him couple years ago). One of the issues was that he has more fans in case than motherboard could handle. As result some were connected to power supply and run at max, some were disconnected altogether.

I had suggested to buy set of better fans and route them through fan controller. Out of few models suggested he picked Zalman ZM-MFC3 controller with extras of temperature and power load measurement.

Looks and build

Panel fits into five inch slot of case and is about one third of optical drive in depth. It turned out to be more convenient to set it up outside and then get into case.

It has greyish color that will go nicely with any dark case. Most of it occupied by screen and single knob for control. The one issue with knob is that if you have case with door over front panel, knob is large enough to prevent it from closing properly.

The power to the controller is provided by single molex connector. Which is somewhat flimsy and sits on circuit board, rather than case. I was afraid to break it when connecting.

Fan controls

Controller supports three (four with bundled Y cable) 3-pin voltage-controlled fans and one 4-pin PWM-controlled one. The RPM of each connected fan is displayed on screen and can be individually adjusted.

There are also wires to extend to fans further away and pass-through connector for in between CPU cooler and motherboard.

Temperature sensors

There are four temperature sensors and small bits of transparent tape to fix them in place where needed. On screen temperature readings are grouped one by one with fans, which might or might not make sense in specific setup.

Power load

Power load meter looks like a large power brick from some gadget and goes between power line and PSU. There is a bracket for back of case, which accept signal from it (confusingly using USB connector, which it isn’t) and forwards to controller.

Overall

Most convenient way to deal with multiple fans ever. Having RPM, temperature and control in one place is great. The construction could use some improvements (smaller knob, sturdier connectors in the back) but functionality itself is great.

Link http://www.zalman.com/ENG/product/Product_Read.asp?idx=341

Possibly related posts: