Lately I am trying to post here every time I find out something useful that I did not really know how to do. This is something I had to Google yesterday and is very useful. In my case, the problem arises when using OPNET. If you are an OPNET user, you know that results can either be visualized with the OPNET tool (very useful for rapid visualization and check of results, but not good to make nice plots) or export the results to an Excel spreadsheet.

Sometimes you have a plot with literally tens of thousands of data points and you would like to down-sample it. OPNET has an option in the results panel that allegedly down-samples the plot, but it takes forever and ever to run and the outcome is never what I need. So what I do is the following. Say that you have your data in two columns:

img1

We are going to add a third column with the following operation: “MOD(ROW(A1),N)”. Change N by the down-sampling you want. For example, if N=20, we will be eventually using 1 data point from every 20.

img2

Now we copy [D,1] on the entire column D. This will result in column D being populated as follows: 1, 2, …, N-1, 1, 2, …, N-1, 1, 2, …

img3

Now we select column D clicking on top of it and we go to “Data – Filter”. A little arrow appears on top of the column. Clicking on it we can filter the values of the spreadsheet based on column D. Select only the value “1” and click OK. I usually do it selecting “1” so I always keep my very first sample. The result is a “new” spreadsheet (note that the actual spreadsheet did not change and all the data is still saved in the file, only what is visualized changes) with only one data point out of every N. You can go ahead and plot it normally as you always do with Excel.

img4

I am sure there are ways to do this easier and more efficiently. If you know any, please write a comment and let me know.

Street Dogs Savin Hill album was playing while I wrote this. Great music to start the day.

Advertisements