Friday, March 14, 2008 - 07:31
I've been writing quite a bit this week about my search for a cross platform spread sheet program that would support pivot tables and make pie graphs correctly.
This all started because of a bug that my students encountered in Microsoft Excel, on Windows. I'm not personally motivated to look for something new, since Office 2004 on Mac OS 10.5 doesn't seem to have the same bugs that appear on Windows. However, I would like things to work for my students. Since I don't want to have to write instructions for every software system on the planet, Google Docs would be my ideal answer, if it supported pivot tables, since it runs over the web and presents a consistent interface on all systems.
Anyway, I heard from many enthusiastic Open Office fans and I asked if they could show me if OO has the features I want (pivot tables and pie graphs).
The is the second post describing what we found.
Before we proceed, I neglected (in the challenge) to provide readers with sufficient information about our experiment. This post has a flow chart that shows where the data came from and what we were trying to do with it.
The results:
Challenger #1
Matt Platte thinks we need a biology spellchecker. Oh Matt, if only you knew! Free-form data entry is exactly why we're stuck with all those horribly long pull-down menus whenever we need to type a state or country name at a web site. But, more on that issue another time.
He also gave us an interesting formula for parsing data.
I don't know, Matt. In Excel, I just select the column, open the Data menu and choose "Convert text to columns." It's pretty easy, but I know this isn't obvious, 'cause I had to make a movie to show how to do this.
Enough chit chat. Matt almost has it right and the rhododendron entry (spelled correctly!) shows he has a good sense of humor. But, there are two problems - one of course lies with that data clean up issue (it's not Matt's fault that there isn't an anthrobacter). The other, though is a real problem. The same colors are getting used twice in the same graph for different species of bacteria (Bacillus and uncultured, Acinetobacter and rhodendron, and Arthrobacter and Streptomyces).
Does this mean the there are limited color choices in Open Office? This wouldn't be good since it makes it harder to interpret the data.
Challenger #2 Paul Schofield also got this almost right. I really liked the way that he placed the key right in the middle of the two graphs (swingkyd agrees). That's a great way to show that the graphs are using a consistent coloring scheme. My image doesn't look as nice as Paul's since I reduced it to make it fit. Paul's graphs have the same problem as Matt's though. Pie graphs should not show different kinds of data with the same color.
Challenger #3, swingkyd Again, this is almost right, but the repeated color mistake has been repeated again.
Challenger #4 Sorry, Tomas . The top graphs are missing the keys and both sets of the colors wrong. The reason that we're graphing the data as pies is that pies allow us to compare the overall populations of the two biomes. This doesn't work the members have the same color wherever they appear. For example, if I have Arthrobacter and it's colored yellow, it should appear with a yellow color in both graphs.
Challenger #5 The last challenger, gkpeter, had the same repeated coloring problem, but did use that very nice way of putting the key in the middle. 
Challenger #6
Joe approached this challenge in a completely different way. He lost some of our bacterial species and abandoned the pie graph idea altogether. I can see some merits to his approach (not losing the data, making a bar graph). I like the overall picture that you get with a pie, though, but the bar graph does have some appeal.
I do wonder: are the color choices in Open Office really so limited? Can you use more colors when you need them? And, it's only fair, too that I show you that I get a little of the same repeated color problem with Excel on my Mac. Bacillus and Pseudomonas are both shown with the same color, here, when I'd rather have them be different. At least the color only repeated once. I guess as we get more types of bacteria, we're going to see this problem more often.
So, thanks everyone! I will look into OO a bit more and see if I can get around the coloring problem or maybe I'll give up on pies and use bar graphs, which I can do in Google Docs.
Whatever happens, I appreciate your help.
Challenger #2 Paul Schofield also got this almost right. I really liked the way that he placed the key right in the middle of the two graphs (swingkyd agrees). That's a great way to show that the graphs are using a consistent coloring scheme. My image doesn't look as nice as Paul's since I reduced it to make it fit. Paul's graphs have the same problem as Matt's though. Pie graphs should not show different kinds of data with the same color.
Challenger #3, swingkyd Again, this is almost right, but the repeated color mistake has been repeated again.
Challenger #4 Sorry, Tomas . The top graphs are missing the keys and both sets of the colors wrong. The reason that we're graphing the data as pies is that pies allow us to compare the overall populations of the two biomes. This doesn't work the members have the same color wherever they appear. For example, if I have Arthrobacter and it's colored yellow, it should appear with a yellow color in both graphs.
Challenger #5 The last challenger, gkpeter, had the same repeated coloring problem, but did use that very nice way of putting the key in the middle.
I do wonder: are the color choices in Open Office really so limited? Can you use more colors when you need them? And, it's only fair, too that I show you that I get a little of the same repeated color problem with Excel on my Mac. Bacillus and Pseudomonas are both shown with the same color, here, when I'd rather have them be different. At least the color only repeated once. I guess as we get more types of bacteria, we're going to see this problem more often.