Inputting data

September 9, 2009 4:43 PM

This should be super easy to do on Excel, right?

An example data set looks like this:

{(0,1),(1,0),(2,0),(3,1),(4,0),(5,0),(6,1),(7,1),(8,0),(9,1),(10,1),(11,0),(12,1),(13,1),(14,1),(15,1),(16,1),(17,1),(18,1),(19,1),(20,1),(21,2),(22,1),(23,1),(24,2),(25,1),(26,1),(27,2),(28,2),(29,1),(30,2)}

I'd like to plot those points (x,y). In this example x goes from 0 to 20. In other examples x will go from 0 to a couple hundred. It's a slowly growing function.

Here's the kicker: I am using Open Office Spreadsheet on a mac.

There should be two vertical columns. Of course, there is not. There is one ginormous horizontal row. I chose "comma" as deliminator. I don't think it understands there are two types of commas. One of them should signify a column break and one of them should signify a row break.

How can I make the data appear as two vertical columns?

Thank you!!

posted by water bear to technology (15 answers total)

x goes from 0 to 30, rather.
posted by water bear at 4:44 PM on September 9, 2009

A brute force way of doing this would be to do a mass find-replace, and replace ")," with ")-", and then use the dash as the deliminator.
posted by inigo2 at 4:59 PM on September 9, 2009

Find and replace "),(" with a different delimiter (a line break in particular would be ideal). Hopefully from there it should become more straightforward.
posted by telegraph at 5:00 PM on September 9, 2009

Text to columns? Try choosing ),( as your delimeter if that is possible, or replace ),( with something (perhaps ;) as your delimeter in a text file. Then you'll have a bunch of rows that look like x,y; you can either save as text and reimport (easier if you only have one or two files to do this for) or do something like this:

Assuming your column of x,y is A, you fill other rows as follows:
B1: FIND (",",A1)
C1: LEFT (A1,B1-1)
D1: RIGHT (A1, LEN(A1)-B1)
Your x is now in column C and y in column D.


(This might not be the most efficient way to do it in excel, but it works on my test case.)
posted by jeather at 6:07 PM on September 9, 2009

Ok I've changed the data so it only contains the y-values. Example:

{1,0,0,1,0,0,1,1,0,1,1,0,1,1,1,1,1,1,1,1,1,2,1,1,2,1,1,2,2,1,2}

I would like this to be one vertical column. Right now it is one long horizontal row. How can replace "comma" with "vertical return" instead of "horizontal tab"?

I just want to change a row into a column. that's it.
posted by water bear at 6:47 PM on September 9, 2009

Copy --> Paste transpose?
posted by caution live frogs at 6:56 PM on September 9, 2009

is there such a thing as Paste transpose?
posted by water bear at 7:02 PM on September 9, 2009

Yes, there is! Thank you!
posted by water bear at 7:03 PM on September 9, 2009

alas, the transpose option is not available in my version of open office. I need to either download a newer version or use a different computer. man...
posted by water bear at 7:13 PM on September 9, 2009

Comma separated values will work fine in openoffice.org, but it uses line returns to indicate a new row. Throw this dataset into a text editor and do a find and replace on ),( and replace it with a line return. Or use UNIX tools:

sed 's/),(/\n/g' INPUT.FILE | tr -d "{ \) \( }"
posted by pwnguin at 7:33 PM on September 9, 2009

Open Office claims to have text-to-columns in version 3.0, if you're using that version.
posted by jeather at 7:37 PM on September 9, 2009

OK, here's what I did.

Put your data in A1.
In A3 put "=FIND(",",$A$1,1)"
In A4 put "=FIND(",",$A$1,A3+1)" and drag fill until the cows come home.
In B2 put "=LEFT(A1,A3-1)"
In B3 put "=MID($A$1,A3+1,A4-A3-1)" and again, make with the dragging and filling.
in C2 put "=VALUE(B2)" and more dragging and filling.

Column C, from row 2 on will be your data.
posted by Kid Charlemagne at 7:45 PM on September 9, 2009

I once did a huge spreadsheet like this and used conditional formatting to decide whether the amino acid differences between two antibodies were a conservative or a radical substitution. Probably not the best tool for the job, but it was handy.
posted by Kid Charlemagne at 7:47 PM on September 9, 2009

You guys are awesome.
posted by water bear at 8:19 PM on September 9, 2009

Alternate for when you just have the Y values:
Put the data (just the y's, like you have above) in cell A1.
In cell A2, put "1", and drag down (so A3="2", A4="3", etc).
In cell B2, put =mid($A$1,A2*2,1). Drag down so B3=mid($A$1,A3*2,1), etc.

Cells B2 and down will be your data.
posted by inigo2 at 9:40 PM on September 9, 2009