Visualization of my blog categories

Posted on February 13th, 2010 by Sacha Chua

This visualizes how often I blogged something with a tag in a given year, sorted by all-time popularity. There are more categories, but I skipped them. The height of each block represents how many blog posts I wrote in that category, while the different blocks represent the years, ending with 2010 at the far right. The graph reflects changing interests and recurring themes.

This visualizes some of the things I’ve been writing about in 2010. We’re only a month in, so the last line is pretty small, and in some cases (n < 4) not even visible.

Sparkline bar graphs created with Sparklines for Excel. Initial categories table created with the following SQL incantation:

select p.post_date, p.post_title, terms.name from wp_posts p inner join wp_term_relationships tr on p.id=tr.object_id inner join wp_term_taxonomy tt on tr.term_taxonomy_id=tt.term_taxonomy_id inner join wp_terms terms on tt.term_id=terms.term_id into outfile '/tmp/categories.csv';

then imported and tweaked in Microsoft Excel.

More posts about: blog, blogging, visual, visualization // 5 Comments »

Short URL: http://sachachua.com/blog/p/7036

http://flowingmotion.wordpress.com Jo Jordan

So clever. What was the data file?
http://sachachua.com Sacha Chua

The data file was a CSV created using the SQL statement above. It had the post date, title, and category name. I then used the YEAR function in Microsoft Excel to extract just the year, and I used a pivot table to sum it all up. I could’ve done that in SQL too (the summary, and probably the year calculation too), but pulling in the raw post data meant that I could slice and dice it in other ways.
http://flowingmotion.wordpress.com Jo Jordan

Thanks Sacha. I think I would have to export a CSV file from WordPress.com Then I can find my way though Excel from there.

Great idea!
- http://sachachua.com Sacha Chua
  
  Oh, that’s what you meant. =) Yes, you’ll probably have to export your posts and then figure out what to do with them. One of the perks of hosting my own WordPress installation is that I can play around with the database myself.
http://flowingmotion.wordpress.com Jo Jordan

Good point. I am in the process of migrating my blog. Maybe I should do that first.

On This Day...

2013: Starting from scratch with Windows 8 — I ran into the dreaded 0xc00000e9 Unexpected I/O error on Windows 8, which meant that my drive was probably messed [...]
2012: Pre-experiment potluck — Today was my pre-experiment potluck, organized by Jennifer Nolan. I brought cookies, she brought cupcakes (which she had frosted with [...]
2011: On friendship and getting better at it — A quiet week led me to thinking about friendship and how I can learn more about it. W- and I [...]
2008: BBDB: Show an address list — I sometimes feel like bringing out my stationery, my fountain pen, and a coil of stamps, and writing cards to [...]
2008: Chapter 6: Being Big Brother (plan) — I haven’t been writing about Emacs lately. Here’s my outline so that you can help keep me honest. =) My [...]
2007: Gwah — I occasionally forget to drink enough water, particularly when a lot of things are happening at the same time. Sometimes I [...]
2007: Help getting on my feet = priceless — Last Sunday, I showed Wayne Young the apartment I’m planning to rent. He’ll sign the contract as my guarantor, so he should [...]
2007: Crafting day at the Gorey — Last Saturday was crafting day at The Gorey, where Quinn, Jed and Leigh live. I created three pieces: a 3-strand faux [...]
2006: I could’ve danced all night… — … and in fact, I just did. Aside from a dinner break and a couple of dances that I skipped to [...]
2006: Tsokolate eh! — (Heh, whoops, forgot to post this.) It must be a sign. Look! A recipe for tsokolate eh, that sinfully rich hot chocolate [...]

sacha chua living an awesome life