Coding

Google Spreadsheets: COUNT with Multiple Values in Cells

It’s been more than two years since I wrote a post on using Google Spreadsheets to use COUNT and FILTER in tandem to count values with multiple conditions.

Circling back, it does very well at allowing you to count when you need to validate multiple conditions. However, it falls apart the minute that your data is either messy or comes grouped with other data inside of a cell. Here’s an example where my original post would have trouble with:

Google Spreadsheets: Multiple Values in Cells

Why does it fall apart? Each cell can have multiple values inside of it.

To dig a little further, we have to understand that my original post called for using the FILTER function to pare down the data set into the specific rows that we need, and then using the COUNT function to count the result of the filter. However, FILTER does not allow for wildcards in its conditions for filtering, making it extremely difficult to account for data that is in the same cell as other data.

Luckily, there’s an easy way to get around it, using a crude but workable solution for counting values with multiple conditions, with sources that have multiple values in cells, extensible to messy or not-very-nice data.



style="display:inline-block;width:728px;height:90px"
data-ad-client="ca-pub-9705548764325874"
data-ad-slot="9050629881">

The Scenario

In the above example, let’s say we’d like to count the number of items inside of Column A and Column B for every row that Item A appears in.

Manually counting them, we know that for Item A, it is listed on a total of 3 rows (Rows 2, 3, and 7) and has 7 values listed to its name in Column A, and 4 values listed in Column B. Hopefully I haven’t lost you, but I’m saying that we count the number of values inside Column A every time Item A is listed, and we do the same for Column B.

How would we go about doing this in Google Spreadsheets?

For the impatient, here’s what the formula looks like:

=COUNTA(SPLIT(JOIN(",", FILTER(B:B, SEARCH("*A*", A:A))), ","))

I know, I know, it looks odd and confusing, so let’s break it down starting from the very inside formulas.

=SEARCH("*A*", A:A)

This is what we’re using as a condition inside of FILTER. SEARCH allows us to parse through the values in a column or row and find characters or values that we’re looking for. In this particular instance, I am looking for the character “A”, but you could be looking for whole words, sequences of characters, you name it, and you have the possibility of adding wildcards on the outside of the term you’re looking for to make it catch every instance properly. Experiment with this, because I’m not 100% sure on whether or not the wildcards help in every situation.

=FILTER(B:B, SEARCH("*A*", A:A))

When SEARCH is used in conjunction with FILTER, it returns a list of rows or columns (as specified by the first parameter in FILTER, in this case it’s B:B) that returned a TRUE condition for the SEARCH. The result would look like this:

X, Y, Z
X, Y, Z
Z

This is returning B2, B3, and B7 respectively from my above example, because they had Item A in their Item column.

=JOIN(",", (FILTER(B:B, SEARCH("*A*", A:A)))

Using JOIN, you can combine all of our FILTER results into a single cell, where each cell’s values are separated by a comma (first parameter from JOIN, in this case “,” means separate values using a comma) and makes it nice and simple for us to analyze just this one cell.

X, Y, Z,X, Y, Z,Z

This is what our data now looks like, all inside of a single cell.

=COUNTA(SPLIT(JOIN(",", FILTER(B:B, SEARCH("*A*", A:A))), ","))

We arrive back at the original Google Spreadsheets formula, with SPLIT and COUNTA appearing. SPLIT allows us to break apart a single cell into many cells that contain each and every value that we have in the cell, as long as we let it know how the data is separated. In this case, it’s separated by commas because of the work we did above with JOIN, and we use COUNTA to count the non-numeric values that the data represents.

Doing this for Item A, we can quickly see that it has 7 items in Column A, and 4 items in Column B.

Easy peasy eh?

I linked the functions into the documentation for Google Spreadsheets as I listed them above, but here they are again, just in case:

If you end up trying this out, let me know what you think, or if you have any questions, or if you have any improvements or suggestions!

Goodbye Nights: The Start of Small, Actionable Projects

Goodnight Moon

There are 24 hours in the day, and I already spend around 20 hours a day sleeping, eating, commuting, or working, which leaves just about 4 hours for whatever it is that I can cram in there.

Those 4 hours are about to disappear.

Starting in September (that’s THIS month!), I will be attempting to create small little projects for myself. These projects have to be completed by the month’s end, and I am giving myself the entirety of the month to design, build, and deliver these little projects.

This month, I’ve been toying with the concept of board game discovery.

I’ve long felt that board game websites, such as Board Game Geek, do a great job of providing the core game data along with a vibrant, and growing community. However, they fail to do any sort of good job at showing me board games similar to the board games I most enjoy. Their Top 100 list is too broad and general (and, occasionally too hardcore) for my tastes.

THE IDEA: Build a website that allows me to look at games, and immediately view related games. For example, taking a look at Zombicide, it would show me other games like Pandemic (because of the co-op factor) or Last Night on Earth (because of the zombie factor.)

The specifics have to be worked out, but of course, and I have the rest of the month to do so! If you’d like to make suggestions or even pitch in (however that maybe,) don’t be shy and reach out. I’m always happy to have collaborators.

This audacious idea of building a project a month is a healthy mix of wanting to fulfill my goal for 2013 of building more content, and simply wanting to solve some of my problems.

Will I make any money off of this? Ideally, but I’m not banking on it.
Will I become famous for this? Probably not.
Will I have fun?

You bet.

Issues with Push Notifications for iOS

iPhone Push Issues

With the news that the Gmail app for iOS was updated two days ago, I decided to give it a go as a replacement to the native iOS Mail app. And for the first time in three years, Google apps are dominating the lower app bar on my phone. (Pictured above!)

However, and this isn’t a knock against the app because it’s wonderful, I have a rather weird quirk about iOS Push Notifications: the notification badge remains despite being corrected elsewhere.

For example, with the Gmail app, if I see a notification for a new email pop up and I am at my desk, I will go ahead and look at it on my web browser, because why look at it on a smaller screen with a larger one available right? Unlike the native Mail app, however, if I clear a notification off of the device, the push notification (especially badges) still remain.

This isn’t just for Gmail, but it happens with Facebook Messenger, Fitocracy, Twitter, and every other app that uses push notifications to alert you of messages, interactions, and whatever else.

I’ll have to dig around a bit, but is this just an oversight by the app developers, or is this a technical limitation for push notifications?

Octocat Attacks: Our entry for the GitHub Game Off

Near the end of October, a blog post from GitHub caught my eye, entitled: GitHub Game Off.

In short, GitHub was running a competition for game developers to build their games, host their code on GitHub, and have it loosely based on a git concept (forking, branching, etc). We were free to build it however we want as long as it could be open source. As a life long gamer, it’s always been a dream to build my own game, and that’s a dream that’s been also shared by awesome guy Wayne Sang.

We had been toying around with the idea of building out a game idea that Wayne had several months ago, and before Game Off, we had decided to build something smaller to get us acclimated with each other’s style and capabilities. GitHub Game Off presented itself as an opportunity to finally make this happen with real deadlines and actual work needing to be produced.

That game? Octocat Attacks.

Octocat Attacks Title Screen

You can view the source code here, and the playable version of the game here.

Most of the rest of this post is going to talk about the development side of things, as there were quite a few things I learned along the way.

Creating the Concept

When Wayne agreed to build a game for the competition, we sat down and hammered out a concept pretty quickly. I suggested that we use Flash, as it was probably the fastest way to get up and running with a game especially with established libraries already available, and that we make a puzzle game because “it’s far easier to build a silly puzzle game than a full blown action game!

Just for the record, I was going to eat those words.

We sat down for several hours to hammer out the concept: it would be match-3 style puzzle, it would be about a giant alien attacking Earth, and various countries coming together to build separate parts of a robot to defend against the alien. The loose association with git was that each country was essentially working on its own “branch” of the master robot repo, and completing a level was that particular country “pushing” their part toward the final product. Each round was timed, and your score affects the quality of the piece that is created, with three different possible tiers in quality, which also affected your final battle with the alien.

I also did some research around the best Flash library to use to build games, and I landed upon what seemed like the most developed and easiest to get started with: Flixel.

There were a handful of other engines available, but Flixel was really far along in development and actually powered games I had heard of (like Canabalt!) and so I ran with it. Just as a side note, once you start using it, Flixel really feels like it was built more for twitch-based games rather than puzzle games, I was lucky to find the Flixel Power Tools set which extends the capabilities of Flixel even further, allowing me to take care of some of the issues I was having with sprites in Flixel.

Starting to Code

Once my environment was set up, I began to write a few test games just to get a feel of Flixel and Actionscript.

Have I mentioned that I haven’t really touched code in a serious way since 2009? Have I also mentioned that I haven’t touched ActionScript since 2005?

Granted, I was very familiar with programming in the first place, so the learning curve wasn’t very steep for me, but it was one thing to be figuring out what I can and can’t do with Flixel, and it was a completely different beast trying to do it while learning ActionScript 3.

However, I got a prototype up and running relatively quickly. According to my records, we started brainstorming on October 27th, and I had a prototype with a 5×7 board full of temporary game pieces that could switch places on October 31st. I was rapidly iterating on the first prototype, creating 90% of the game mechanics by November 12th: puzzle piece generation, piece movement (swapping places), match checking and clearing, and empty space refilling. Nothing was 100% as it should be for a completed game, but it was a very quick start.

Around this time, Wayne chipped in with his awesome pixel art, and the game was finally starting to come together.

Refactoring Mania

Regardless of the level of stability, I was rather unhappy with how game pieces were being moved around and being checked as matches, so I spent a good week refactoring everything. And I mean everything.

Suddenly the game became less jittery and resource intensive, I had created a queue for the checking and clearing of pieces, but I was still being plagued by my code to animate the refilling of pieces on the board. It was a problem that I am still having trouble with to this day, and I feel like I’ve smashed my head against it enough times that I may need to refactor the entire thing to provide a different approach.

Oh well, that’s what branches are for, right?

The important lesson I have is that, and I didn’t know this because I am a complete newbie, Flash and ActionScript 3 runs code synchronously (I think.) This was a problem with the initial way I was refilling pieces, because I essentially had a for loop that would check every single spot on the board, and if there was no sprite within that spot, it would start the animation to move all pieces above the empty square downward to fill the empty spot and create the new piece. However, if you have two (or more) empty squares on top of each other, the new piece creation happens simultaneously and you have stacked pieces in the same square.

As you can tell, I am not very experienced with puzzle game animations!

Octocat Emerges

On November 19th, Wayne sent me an email where he sketched out the alien: he had taken the Octocat, of GitHub fame, and turned it into the alien monster attacking the Earth. It shot lasers from its eyes, it was adorable, and I think it gave me a bit of extra motivation to see this project go all the way.

Alien Octocat

Aw, aren’t you a horrible little creature?

Final Stretch

It feels like I’ve left out a lot of details, but that’s because the entire month felt like a blur. I was constantly trying to fix our animation problem while building out the HUD (score, timer, etc) and the functionality to power the HUD. Wayne was churning out all of the necessary art assets for the game, and it was starting to come together.

Eventually, we decided that we weren’t going to complete the game in time, and we were alright with that. We were both willing to continue working on the game at a more leisurely pace after the deadline had passed, and that’s one of the side projects I am really looking forward to.

At the end of the day, the v0.1 build of Octocat Attacks, as in the one we’re submitting to GitHub Game Off, is a very incomplete game. We have an incomplete puzzle engine, no audio, and our content is far from complete.

However, we got it out there. We took the effort to start our project and bring it this far, and we’re continuing to push on. I’m very happy with how the game looks right now thanks to the hard (and amazing) work that Wayne has put into his pixel art, and our game is functional, which is a lot more than I expected when we started!

It’s been a really fun and educational experience to build a game from scratch, and you better believe that Wayne and I are going to continue developing games.

Octocat Attacks Stage Select
Octocat Attacks Gameplay

Week 1 of Coding: Ouch, right in the feels.

Ah, glorious Thursday. Not even close to the end of the week and I’m writing a wrap-up of my week so far. Want to know why?

My project is broken.

Yup, absolutely broken. Well, it works up to a certain point (and was really fun writing!) but there’s no logical way to finish it.

Recapping

This week, I was spending the majority of my days getting myself setup on Python and writing a script that accesses the Last.FM API to grab all of my scrobbles throughout the years, categorize them into genre, and present the data by year to visualize the change in my musical tastes over the years.

Everything went well until the “categorizing into genres” part, because… trying to pin a single genre to an artist is apparently very difficult. Last.FM doesn’t use “genres,” they have tags that are user applied and include a high level of variance. MusicBrainz, which Last.FM utilizes (I believe) also uses tags. Scraping Wikipedia and AllMusic resulted in gigantic piles of genre for each artist, so I’ve resorted to a more manual version: I tag them all as a specific genre by hand.

It hurts. Right in the wrists. (And the feels.)

However, this week has been a really fun experience and only makes me look forward to the next project. Before I talk about that, there are some important lessons I learned throughout the week.

1) Homebrew and pip are your best friends.

Writing basic scripts in Python? Yeah, that’s no sweat.
Want to write more complex scripts that might require external libraries? Yeah, have fun compiling and installing that stuff.

Well, okay, in actuality, it’s still relatively simple. But compared to typing ‘pip pymongo‘ into the terminal? It’s quite a bit more complex!

With homebrew and pip, I managed to get MongoDB onto my development machine, install the PyMongo driver, and install the unidecode library. In a matter of seconds.

Sa-weet!

2) Unicode can burn in hell.

I spent the better part of today and yesterday figuring out how to wrangle with unicode. A few of the artists from my scrobble list have Asian characters in their name on Last.FM, and Python (or MongoDB) automatically turns them into their unicode representations.

That’s all good and well, but turning them back (and using them in functions) is an absolute nightmare. Thank goodness for unidecode for (temporarily) solving that nightmare.

3) MongoDB is pretty awesome.

During the project, I was able to pull my scrobbles down from Last.FM but I wanted to insert them into a database.

I’m used to working with MySQL so I attempted to get that up and running. Well, after half an hour of yelling at my computer, I decided to take the lazy route and check out my alternatives that might be quicker. The suggested alternatives were SQLite or some sort of NoSQL solution. I figured it would also be a good opportunity to try out those fancy datastores I kept seeing on Hacker News and settled on MongoDB.

Got it up and running within minutes on default settings, and it’s been pretty smooth sailing so far. Inserting and retrieving data has been a breeze (my dataset is only 50,000 items) and I have enjoyed the experience.

I’m not skilled enough (yet) to really grasp the differences between the different types of datastores, and I make no attempt at doing so. I was just enamoured by the incredibly short amount of time it took for me to get up and running on Mongo.

4) I took breaks by learning Spanish.

I’ve been experimenting with the Pomodoro technique (25 minute sprints, 5 minute breaks) and it’s been a really good way of creating hard deadlines and stop-points for work.

However, I generally surf during breaks and get carried away for more than 5 minutes, so I wanted to do something that allowed for shorter bursts.

Enter Duolingo.

It turns out, doing one or two lessons on Duolingo were perfect – I would sit here shouting Spanish phrases and words at my computer and laughing all the while, and my alarm would go off and I would get right back into the work.

As a result, I’ve familiarized myself with basic Spanish words and phrases, and I am working my way through as much of the Spanish portion of Duolingo as I can. It’s a win-win situation, as far as I can tell!

Want to follow my Spanish-learning progress? Check out my profile on Duolingo.

Here comes week 2!

I haven’t decided exactly what project I’ll be working on next week, but I would really like to focus on starting to incorporate tests into my code. I’ve been ignoring them for now because I figured it would be beneficial to familiarize myself with syntax over anything else.

However, if I want to get any better at this, I’ll have to learn to write code that isn’t complete crap and I think tests would be a good place to start.

In the mean time, I’m going to try to categorize my music and complete my musical visualization. Thanks for reading my weekly brain dump!

Gallery Separation with WordPress Lightbox Gallery Plugin

While working on a friend’s blog, I ran into a bit of a problem with having multiple Lightbox galleries on a page in WordPress: all images on a page will be in the same gallery despite being from different posts. It would basically mean that clicking on an image from one post’s gallery will allow a reader to navigate to all images on a page.

There was a simple fix, but it was rather manual. It involved taking the gallery shortcode for WordPress:

1
[gallery]

And adding a class to separate the galleries:

1
[gallery class="gallery2"]

However, this was a manual process and I was sure that there was a way to make it more automated so that I wouldn’t have to add a class for each gallery, perhaps using the post ID as the class name to separate them?

After some digging around, I found this in /wp-content/plugins/lightbox-gallery/lightbox-gallery.php:

507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
extract(shortcode_atts(array(
	'order'      => 'ASC',
	'orderby'    => 'menu_order ID',
	'id'         => $post->ID,
	'itemtag'    => 'dl',
	'icontag'    => 'dt',
	'captiontag' => 'dd',
	'columns'    => $columns,
	'size'       => $size,
	'include'    => '',
	'exclude'    => '',
	'lightboxsize' => $lightboxsize,
	'meta'       => 'false',
	'class'      => 'gallery1',
	'nofollow'   => false,
	'from'       => '',
	'num'        => '',
	'page'       => $page,
	'before' => '<div class="gallery_pagenavi">' . __('Pages:'), 'after' => '</div>',
	'link_before' => '', 'link_after' => '',
	'next_or_number' => 'number', 'nextpagelink' => __('Next page'),
	'previouspagelink' => __('Previous page'), 'pagelink' => '%', 'pagenavi' => 1
), $attr));

A simple change with the ‘class’ part of the the array do exactly what I need:

520
	'class'      => "'" . $post->ID . "'",

Ta-da! Multiple Lightbox galleries on a single page are now automatically separated by post ID without any extra manual fuss. Photos in each post will now remain sandboxed inside of each post, regardless of how many are listed on a page.

Enjoy!

Feels good, man.

Awesome person, Pearl Chen, just pointed out that one of my photos for Ladies Learning Code made it into an episode of Penny Arcade TV’s Extra Credits entitled “So You Want To Be a Programmer (Part 1).”

It’s not exactly a stunning achievement, but it feels good to know that something you’ve captured gets used for something as awesome as Extra Credits.

Bonus: it’s a great video for anyone who is curious about or wants to become better at programming.

Creating an Image with Dynamic Text in PHP

As Christmas approaches, many of my friends have begun their countdowns to that wonderful holiday at the end of the year.

One of my friends started her countdown back in August, when she opened up her calendar and had to count the number of days until Christmas and continuously keep track of what day it was and where her count was. I told her that I could spend the 5 minutes to help her out using the power of the internets, and that’s how my Christmas page was born.

Today, I spent a bit more time spicing up the page. It now has a Calvin & Hobbes image that has a bunch of static text, but will update the number of days dynamically.



style="display:inline-block;width:728px;height:90px"
data-ad-client="ca-pub-9705548764325874"
data-ad-slot="6321796284">

To preface: I had no idea how to do this, and found out that PHP has a rather sizeable GD and Image Function library. I used this example to start:

1
2
3
4
5
6
7
8
9
10
11
12
<!--?php 
 
header("Content-type: image/png");
$string = $_GET['text'];
$im     = imagecreatefrompng("images/button1.png");
$orange = imagecolorallocate($im, 220, 210, 60);
$px     = (imagesx($im) - 7.5 * strlen($string)) / 2;
imagestring($im, 3, $px, 9, $string, $orange);
imagepng($im);
imagedestroy($im);
 
?-->

Unfortunately, you can only use some LATIN2 font with imagestring(), and did it fit the style of the Calvin & Hobbes comic I was using, but it was a good start.

In order to load your own font and dictate its size, we can use imagettftext(). I had to include the Comic Sans (Don’t hurt me!) font file in the same folder as the PHP file.

Here is the complete code for my dynamic Christmas image:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<!--?php 
// Filename: christmas-image.php
 
header("Content-type: image/png");
$string = $_GET['days'];
$im     = imagecreatefrompng("calvin_resolutions.png");
$black = imagecolorallocate($im, 0, 0, 0);
$font = 'comicsans.ttf';
 
$initialX = '153';
$initialY = '35';
$increaseY = '23';
 
$fontSize = '16';
$fontRotation = '0';
 
$firstline = 'What do you mean';
$secondline = 'there are ' . $string . ' days until';
$thirdline = 'Christmas?! What am';
$fourthline = 'I supposed to do until';
$fifthline = 'then? WAIT?! I am';
$sixthline = 'not a patient man!';
 
//imagestring($im, $font, $px, 20, $string, $black);
imagettftext($im, $fontSize, $fontRotation, $initialX, $initialY, $black, $font, $firstline);
imagettftext($im, $fontSize, $fontRotation, $initialX, $initialY+$increaseY, $black, $font, $secondline);
imagettftext($im, $fontSize, $fontRotation, $initialX, $initialY+($increaseY*2), $black, $font, $thirdline);
imagettftext($im, $fontSize, $fontRotation, $initialX, $initialY+($increaseY*3), $black, $font, $fourthline);
imagettftext($im, $fontSize, $fontRotation, $initialX, $initialY+($increaseY*4), $black, $font, $fifthline);
imagettftext($im, $fontSize, $fontRotation, $initialX, $initialY+($increaseY*5), $black, $font, $sixthline);
imagepng($im);
imagedestroy($im);
 
?-->

The positioning of the text is specific to the Calvin & Hobbes image I used, and I made it easier on myself by creating a variable for the X and Y positions as well as the amount I wanted to push the next lines down.

All you have to do now is refer to the PHP file and pass the variables through the URL.

<img alt="" src="christmas-image.php?days=&lt;?php echo $remainingDays; ?&gt;" />

The PHP file will create a PNG that has the updated text. That was pretty simple, no?

Google Spreadsheets: Using COUNT with Multiple Conditions

This past week, I spent a good amount of time analyzing our current customers with PostageApp and figuring out some of the metrics that we use to determine the health of Postage.

Of course, there’s only one tool for the job: a spreadsheet.

Being somewhat familiar with Microsoft Excel, I figured this would be a piece of cake to do on Google Docs Spreadsheets, considering I won’t be doing anything ridiculous. I’d just have to record all of our data and use a bunch of math to figure out some numbers.

Then I hit a snag: I couldn’t count the number of rows if I had more than one condition.

I was using the COUNTIF function to count the number of rows given a specific value inside a column, but it would fail if I tried to do more than one at a time.

I searched for solutions and found quite a few, but many that did not work, especially with Google Spreadsheets. Finally, I stumbled onto a solution: using COUNT with FILTER!

What?

Here’s an example, multiple column data sheet that has some string information along with numeric information:

Let’s say I wanted to get a count of how many rows there were with ’223′ under the ‘Derp Column’ and ‘Herp’ under the ‘Herp Again’ column. (Don’t ask, I’ve been on Reddit far too much.)

This is the formula you would use to get that count:

1
=COUNT(FILTER(B:B ; B:B=223 ; C:C="Herp"))

The important part here is the FILTER function. The first parameter (B:B)) is the row or column which you are trying to sort through (should be the one you are applying at least one condition on), which in our case is column B (‘Derp Column’), and any other parameters (as separated by the semi-colon) are conditions which you are applying to filter the row. The result should be, of course, 1.

You can be pretty flexible when applying conditions. For example, you can also use this to figure out how many rows have a number greater than 100 in ‘Derp Column’:

1
=COUNT(FILTER(B:B ; B:B&gt;=100))

This is a really neat way to quickly sort and count through data programmatically, and I hope it can be useful to those of you who are using Google Docs Spreadsheets to do calculations like I am! Cheers.

The Cake Isn’t A Lie

Yesterday, I tweeted out that I was going to give CakePHP a try over the weekend, with the end goal of creating a plugin for CakePHP to be able to send emails via PostageApp.

CakePHP!

Welp, I’ve done the bare minimum to give it a try – I went through the ‘create a blog’ example they had in their documentation.

What you end up with is a pretty simple web page that lists all of the posts (title and body) that are in your database, and you can add, edit, or delete posts as you see fit. It was pretty darn easy to set up, and it gave me a much better understanding of the MVC coding process.

I’m going to fiddle with it some more until I can actually add functionality to it (thinking of adding comments) and some sort of layout. If you want to check out what I’ve built so far, here’s the link: hellocake.upatom.com

Feel free to add/edit/delete posts on the site, and even feel more free to leave me weird messages!

Let’s see how far I can get with this!