When dealing with large dataset, you may want to reduce data before performing analysis or graphing. There are multiple ways to reduce data in Origin. This post will demonstrate how to reduce rows and columns from GUI and the corresponding LabTalk scripts.
Download Reduce_Rows_Columns.zip to follow the procedures below. Results are kept in Results folder of the opj file.
Go to Folder1 and workbook Nitrite. The data is collected in every 0.01 sec.
First let’s reduce data to around every second. Highlight Col(A) and Col(B), and choose Worksheet: Reduce Rows… menu. Set the settings as following.
After clicking OK, the reduced data are added to new columns.
Right click on Sparklines row in workbook and choose Add or Update Sparklines. Click OK to add sparklines of each column. Comparing column B and D, you can see many peaks are lost in the reduced data.
To preserve peak shapes, we need to keep more data. Just left click the green lock on column C and choose Change Parameters context menu to open the dialog again. Change Delete Rows to 9 and click OK. Now the data is reduced to every 0.1 second. The sparklines of column C and D are updated and show a better data reduction.
Reduce Rows in Origin is based on X-Function wreducerows. To see the script behind it, left click the green lock
on column C and choose Generate Script context menu. The script is dumped into Script Window. You can use the script in the future to do such data reduction.
There are other Reduce Methods, e.g. Reduce N rows with merged values: mean, min, max, etc.
Note: You can also use Worksheet: Worksheet Query to set conditions. Rows that fit the condition can be extracted to a new sheet. E.g. set condition
- B>dmin AND B<dmax to extract rows with min and max excluded
- i>= list(max(col(B)),col(B)) to extract rows from maximum of B on
Similarly we have Reduce Columns dialog to reduce data column wise. Look at Sensor workbook in Folder1. There are many 3 XY pairs of data for each sensor. All X columns are the same.
If you want to remove all duplicated X columns and get the average Y values for each sensor,you need to use the Reduce Columns dialog twice.
First, delete every other column starting from the 3rd column to remove all duplicated X columns. See the images below.
Then with the new worksheet active, open the Reduce Columns dialog again to merge every 3 Y columns by averaging. See the images below.
You can also run following scripts to run reduce columns twice without opening dialog.
wreducecols start:=3 ow:=<new>; //delete every other column first 3rd column on wreducecols method:=merge ncols:=3 merge:=ave start:=2 ow:=<new>; //merge every 3 columns by averaging, starting from 2nd column on
From the dialog, you can click the arrow next to Dialog Theme anytime to generate script based on your current settings .
7 Comments on “Reduce Rows or Columns from Worksheet”
Hello, let me share my Problem,
I have two collumns: the time and the corresponding singal. I am only interested in the values of my singal after the maximum. (It is an impuls and then singal decays, however the Impuls appears in every measurement after a different time period. measungring freqeuncy 1000 Hz ). How do I delete all rows before the row with maxium? There are a lot of rows…
Could you try Worksheet: Split Worksheet…
set Split Mode: By Reference Columns
Set Reference Column as the signal column
Split by Direction Change.
Include Turning Point in Next Group
This way, it will split the worksheet into a new workbook with two sheets.
1st sheet will have all rows to max point.
2nd sheet will have all rows from max point on. This is the sheet u want.
Here is another way:
With your worksheet active, choose Worksheet: Worksheet Script…
Enter the following in Condition: field
Choose Extract to New Worksheet or New Workbook and click OK.
list(max(col(B)),col(B)) can find the row no. of maximum value in column B.
So the condition above will find all row indexes bigger or equal to the row number of maximum signal and extract them into a new sheet/book.
Let me share my problem. A worksheet with 2000 columns x 2500 rows that will be transformed to a matrix, and plotted as a contour map. I would like to reduce the matrix size not by deleting columns, but by averaging. Thus, I would like to have a final matrix of, say, 500 x 2500, with Column 1 being the average of the 1th to the 5th, Columnn 2 being the average of the 6th to the 10th and so on. I am stuck on my project due to that. Any help would be really appreciatted.
Congrats for the blog.
Two ways depending on if you import data to workbook window or matrix book window.
1. If data is imported in workbook, you can choose Worksheet: Reduce Columns.
Set Reduce Method to be Reduce N columns with merged values.
Merge Columns: 4 — — since 2000/500=4 (every 4 columns average)
Merge by: Average.
Then you can highlight all data and plot contour.
2. If data is imported in Matrix book
Choose Matrix: Shrink menu.
Set Column factor to be 4 — since 2000/500=4 (every 4 columns average)
Set Row factor to be 1.
You can set Output to be New Book since u can compare the result.
Plot the contour.
I have many data , in some rows , theire is (s) how I can delete all rowes contain (s)
You can first choose Edit: Replace menu.
Set Data Type as String.
Enter *(s)* in Find What field.
Enter — in Replace with field.
Check Use wildcards checkbox.
Click Replace all so all cells with (s) will turn into missing values.
Then choose Worksheet: Reduce Rows… menu.
Choose Reduce Method as “Delete rows with missing values”.
This will remove rows with missing values in that row of any column.
Hope this works.