OpenOffice Calc Challenge

[codehawk]

Hi Guys,

I was reading a message over at ScienceBlogs (www.scienceblogs.com) where someone teaching a bio-tech related class posed a 'challenge' to openoffice users, to see if she realistically could encourage her students to use openoffice calc instead of Excel. (Turns out she was having trouble with a specific bug in Excel..)

Here is her 'challenge' :

The message is reproduced below, and the original can be found at

http://scienceblogs.com/digitalbio/2008/03/openoffice_oh_my.php

"The contest rules

1. Get a copy of our data here and save the data file as text (okay, you probably already knew that).

2. Parse the third column so that the culture state, biomes, and bacterial genera and species are separated into four (or more columns).

3. Clean up the data.

4. Make a pivot table (or the equivalent) to count the numbers of each genus found in each of the two biomes. You don't need to count the bacteria that live in unknown biomes, we're only interested in the bacteria that were found in the forest and creek.

5. You will probably have to clean up the data again and redo the pivot tables. (This is real life kinds of stuff that we're doing here after all.)

The final table should look like this:

table.png

6. Make two pie charts from your pivot table so that we can compare the bacteria that live in the different biomes. Warning: this was the part that almost everyone got wrong.

7. Take screen shots of your pivot table and your two pie graphs.

8. Share the images with me and the rest of the world. You can put them on-line somewhere, like your blog or flickr or something and add the link here in the comment section.

Or, if you have a gmail account, you can open up Google Documents, make a presentation, and insert your images. Then click the Publish tab and put the link to your file in the comment section.

Game on!"

Hope someone knows Calc well enough to do this in a short amount of time.

Kind regards,

Dimitry.

[swingkyd]

this all can be done quite easily.
[edit for clarity]
the problem is "Clean up this data"... what does the "cleaned up data" look like...I don't know what you are trying to do.

Technically all that is asked can be done.
Pivot-Tabe -> Data Pilot

[swingkyd]

for example. are we supposed to know what is a typo and what is not?

[Villeroy]

The new version 2.4 will include a text-to-columns tool like Excel. With the current version I can split the fields in Writer before I paste the Writer table as unformatted text into Calc. Using a comma-locale, I've got to tag the numeric column as "US-English" (dot decimals).

[swingkyd]

This worked for me... but if the length of the chart changes, of course, as Villeroy states, the chart will not automatically update.
I suppose having the data pilot populate a named range might help in "binding" the results to charts.
Here is my result:
My result attempt

[codehawk]

Thanks for the effort guys.

I've posted on her blog about this thread. Hopefully It'll be useful to her.

Dimitry.

[jrkrideau]

I had a look at the data and decided that since I don't have any idea of what the geni are there was no sense in attempting the problem.

<Start Rand>
To be a bit of a curmudgeon it looks like like just about all basic data gathering rules are skipped here. It is good to hear that OOo can handle the problem but a tiny bit of forethought should have reduced the problem to an almost trivial level in the first place.

Oh yes, the use of piecharts is to be deplored as well.

<End Rant>
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2

[acknak]

I hear 'ya jrkrideau, but that's the real world I'm afraid. Believe me, the best biological databases in existence are riddled with this kind of sloppy data. It's just a fact of life that you have to do a lot of this kind of clean up before you can start thinking about what it means.

Anyway, it was a fun trip; thanks for posting the link. I certainly see no reason why OOo is not more than adequate for this.

Here's what I ended up with (I did the pie charts, but I don't think that's a good way to present the results): biome_lab.png

(If anyone has trouble viewing that screenshot, please PM me--my browser is acting funny)

PS: If anyone wants an explanation of what this lab is supposed to be about, just ask. It's not as complicated as it sounds.

[jrkrideau]

[JJJoseph]

[quote="Villeroy"]The new version 2.4 will include a text-to-columns tool like Excel./quote]

That is such good news. I never realized how much I used this in Excel until I found that I couldn't do it with OO Calc.

[Villeroy]

[swingkyd]