OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

OpenOffice Calc Challenge

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
codehawk
Newbie
Newbie


Joined: 12 Mar 2008
Posts: 2

PostPosted: Wed Mar 12, 2008 10:25 am    Post subject: OpenOffice Calc Challenge Reply with quote

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.
Back to top
View user's profile Send private message
swingkyd
OOo Advocate
OOo Advocate


Joined: 15 Sep 2004
Posts: 479

PostPosted: Wed Mar 12, 2008 10:52 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
swingkyd
OOo Advocate
OOo Advocate


Joined: 15 Sep 2004
Posts: 479

PostPosted: Wed Mar 12, 2008 11:33 am    Post subject: Reply with quote

for example. are we supposed to know what is a typo and what is not?
Code:
Acinetobacter
Acinetobacter johnsonii strain
Acinetobacter radioresistens
Acinetobacter sp.
Acinetorbacter

Is "Acinetobacter" different from "Acinetobacter johnsonii strain" or "Acinetorbacter"?
Since I don't know what this is talking about, it is doubtful I would be able to get the "correct" data output. However, I created a Data Pilot table which does what she is asking... and pie chart too... Approx. 15 mins to do with the help of a better application called CSVed (open source of course) to manipulate the raw data to something that OO.org can further manipulate.

The above lists the results of the "Genus" column of the data pilot.
the table header looks like this:
Code:
Count - Genus   Biome      
Genus   creek   forest   unknown


Making pie charts from the table is not so difficult:
Quote:
6. Make two pie charts from your pivot table so that we can compare the bacteria that live in the different biomes.

I don't know what this means.
[edit] pie charts don't seem to like "blank" numbers in the definitions. is this a Calc bug?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Mar 12, 2008 11:48 am    Post subject: Reply with quote

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 wrote:
Making pie charts from the table is not so difficult:
Quote:
6. Make two pie charts from your pivot table so that we can compare the bacteria that live in the different biomes.

I don't know what this means.

In Excel you can bind charts to pivot tables so the chart series always get adjusted to the changing pivot table. Impossible in Calc.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
swingkyd
OOo Advocate
OOo Advocate


Joined: 15 Sep 2004
Posts: 479

PostPosted: Wed Mar 12, 2008 12:36 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
codehawk
Newbie
Newbie


Joined: 12 Mar 2008
Posts: 2

PostPosted: Wed Mar 12, 2008 1:21 pm    Post subject: Reply with quote

Thanks for the effort guys.

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

Dimitry.
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Thu Mar 13, 2008 6:15 am    Post subject: Reply with quote

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. Smile

<End Rant>
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
acknak
Moderator
Moderator


Joined: 13 Aug 2004
Posts: 4295
Location: ~ 40°N,75°W

PostPosted: Thu Mar 13, 2008 10:42 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Fri Mar 14, 2008 6:06 am    Post subject: Reply with quote

acknak wrote:
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.


I don't do biology data but I have seen the same in lots of other places. Smile
I had one interesting report not too long ago where someone was walking at 70 miles an hour and another where someone could have been in a hospital but the hospital could have been in any of three countries.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
JJJoseph
Power User
Power User


Joined: 25 Sep 2005
Posts: 81

PostPosted: Fri Mar 21, 2008 8:53 pm    Post subject: text-to-columns tool Reply with quote

[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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Mar 22, 2008 2:11 am    Post subject: Re: text-to-columns tool Reply with quote

JJJoseph wrote:


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.


It was there in the Writer since the beginning of time. Just copy and paste.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
swingkyd
OOo Advocate
OOo Advocate


Joined: 15 Sep 2004
Posts: 479

PostPosted: Tue Mar 25, 2008 11:28 am    Post subject: Re: text-to-columns tool Reply with quote

Villeroy wrote:

It was there in the Writer since the beginning of time. Just copy and paste.

I personally find the Calc "texttocolumns" uno package far better than the Excel text->columns.
The Calc extension can easily split based on multiple delimiter types. It is a single dialog and very easy to use. I find the Excel version with it's multi-page dialogs extremely cumbersome.
It is similar to the dialog in Writer, but the calc version works better IMHO. Especially when dealing with thousands of lines of data.
It is a shame the developers haven't included the extension as part of the distribution back when it was available in 2004 or earlier. If nobody knows about it (except us "old" folks), then it can be very frustrating for new users.
I think the latest version is found in this link.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group