[Solved] Skipping empty cells when pasting a column of data

[docboardman]

Hi Everyone:

I'm using Calc in OpenOffice 3.0.0. I'm using it to analyse data collected from a monitoring device. The device measures many items and is set up to take readings at 8Hz (i.e. 8 times a second). Only some of the parameters are actually read at 8Hz, however, while the others are only read at 4Hz, 2Hz or even 1Hz. Columns of data for these parameters have lots of empty cells. I have been trying to copy these columns then paste them so that the empty cells are not pasted, in other words, to end up with a column containing no empty cells. If I select Edit > Paste Special, I get a window where one option is "Skip Empty Cells." When I select this and then click on "OK" I get the whole column pasted, including the empty cells. Am I doing something wrong or is it actually possible to skip empty cells when pasting?

Cheers,

--------- Doc

[Villeroy]

The wording "Skip Empty Cells" is misleading. The option as I understand it should be labeled "Don't overwrite existing values with blank values"

[acknak]

I don't understand how this is going to work, since you haven't described how your data are laid out, but you can use a "filter" to copy data from one column to another, and copy only the non-empty cells.

1) Select your data column
2) Data > Filter > Standard filter
Value: -not empty-
More > Copy results to: AA1 (or wherever you want)
OK

[docboardman]

Villeroy - Thanks for that. The function could probably be labeled better, but that explains why it doesn't work.

acknak - Okay, well the short answer is: That worked. Thank you very much.

Longer answer: The programme that collects this data was written by someone else. Maybe a group of people, I don't know. Anyway, I'm the one who has to use it from time to time. Being semi calc illiterate, I kind of learn as I go along. The monitoring programme takes readings every 0.125 sec (or 8hz). The time is displayed in column A in the spreadsheet it produces. All the other columns contain data from different sensors that are monitored. Each column shows the data from one sensor. Some sensors are monitored and recorded every 0.125 sec so, as you look down that column, there is an entry into each cell. Other sensors are monitored (and hence recorded) less often, for example once every 0.5 sec. As you look down that column you'll see an entry into one cell, then three empty cells, then the next entry. If I want to display that data in a graph or chart, I end up with one that plots lots of zeros for all the empty cells. So, instead of a line, say, I end up with lots of spikes because the line goes from a data point, then to three zeros, then to the next data point, etc. By filtering out all the empty cells, I can now plot this data into a graph that makes studying the data a lot easier.

Anyway, I don't know if that helps you or not. It would be easier to show you, I guess. The main thing is: I tried your advice and it seems to work. I'll go off and play with it now to see if I can do what I want with it.

Thanks very much,

---------------- Doc

[acknak]

Ok, that's pretty much what I imagined.

The problem I'm thinking of is that if you take a column of data that has empty cells and "compress" it to remove the empty ones, then you lose the time column, or the times in the time column don't match the measurements. As long as you deal with that somehow, and make sure to keep the correct time for each point, it should be ok.

[docboardman]

Exactly, and the time column is a problem. The only solution I have at the moment is to create a separate time column for each sensor column. All I have to do is figure out what the recording time interval is and what time the first recording was taken. If I then enter the first two time data points I can select those two cells and drag it down to the bottom of the column. That will automatically fill in the other time points at the correct interval for when the measurements were recorded. I always check a few entries near the end to see that the times and recorded data match up to the original. If I want to plot several lines of data in a graph, the time scale covers the entire time frame regardless of whether a column of data was recorded at 8Hz or 2Hz (or whatever frequency it was). Not very elegant and a bit tedious, I know, but it gets the job done.

Speaking of graphs, and I know this takes it off topic, but I'd like to print out a graph where one of the sensors outputs is displayed as a bar chart and the rest as lines. At the moment I plot them all as lines because this way I can use the XY (scatter) plot and put time on the X Axis. I see there is a selection for what I want (Chart Type > Column and Line) but I lose control over the X axis then. Is there a way to have something like an XY (scatter) plot only where one of the sensor outputs is displayed as a bar chart while the others are line?

I'll start a new thread about that if you think it is better.

Cheers,

---------------- Doc

[acknak]

Well, this may be easier than you think. If I take a guess at what your sheet looks like, and make a chart from it, I don't see any problem: OOo Chart simply ignores the empty cells:

So the solution may be as simple as upgrading to OOo 3 (3.0.1 was released yesterday)

[docboardman]

Hmmmmm. I see what you mean. I looked at a smaller sample of my data (each sensor column has something like 2000 or so cells). If I just look at one column, and keep it down to something like 60 cells or so, then it plots out fine and ignores the empty cells. Not sure what is going on now. I'm kind of short on time at the moment, so it may be a few days before I can try to get to the bottom of this. I've got 3.0.0 at the moment, I'll upload the latest and see if it helps. But first I want to try and understand what the problem was initially. Maybe I'll reduce things down to just a few seconds of readings (5 sec would be about 40 rows) and just a couple of sensors. See what that looks like. I might have just been looking at too much to understand what is going on. I'll see if I can post the result. Do you just cut data from the spreadsheet and paste it into the reply box?

Cheers,

-------------- Doc

[acknak]

... I'll see if I can post the result. Do you just cut data from the spreadsheet and paste it into the reply box?

We can only dream--unfortunately, no.

You can post a screen capture, as I did, or you can copy/paste your data as text. If you paste it as text, it's a little easier to see if you select it and click the

Code: Select all

 tag just above the message area.

You can also attach a Calc document: use the "Upload Attachment" link (below the message entry area after you click "POST REPLY").  [url=http://user.services.openoffice.org/en/forum/viewtopic.php?f=74&t=8289][Forum] How to attach a document here[/url]

[docboardman]

Thanks acknak. I'm away till the 3rd. After that I should have more time to look at this. I'll continue this thread then. Really appreciate all your tutorials on this.

[acknak]

Where is all fell apart (again) was when I tried to change the plot to columns and lines. The data from one sensor would make more sense as a column than as a line. The output from other sensors look better as lines. Unfortunately when I changed the graph to this, I just got nonsense.

Sorry, but this won't work at all. Both the "column" and "line" chart types are not X vs. Y plots, but category vs. Y plots. The "X" axis position is not based on the values in the X column, it's just evenly distributed over all the X values. The X values are just labels--they might as well be "apples", "oranges", "pears", ...

Unfortunately, OOo Chart does not support any column-/histogram-/stairstep-style X-Y plots. If you need that, you'll have to switch to a more technically-oriented plotting software. OOo is more focused on "business" graphics.

[docboardman]

Okay. I noticed that about the x-axis. I was just about to embark on trying to modify that somehow, but I guess I'll abandon that. In that case the XY Scatter chart will have to do. And it actually works fine, I was just trying to squeeze the last bit of refinement from it.

From my point of view, this can be marked solved if that helps with things.

Cheers,

---------------- Doc