OriginLab Corporation - Data Analysis and Graphing Software - 2D graphs, 3D graphs, Contour Plots, Statistical Charts, Data Exploration, Statistics, Curve Fitting, Signal Processing, and Peak Analysis     
 
Skip Navigation Links
All BooksExpand All Books
Origin HelpExpand Origin Help
Workbooks Worksheets and Worksheet ColumnsExpand Workbooks Worksheets and Worksheet Columns
Simple Manipulation of Worksheet DatasetsExpand Simple Manipulation of Worksheet Datasets

4.4.11 Data Filter

Discription

DataFilter.png

Origin offers data filters of three data formats, date time, numeric, and text. The data filter could be added, removed, enabled, disabled, or reapplied by button click in the Worksheet Data toolbar and customized with user defined filtering conditions.

Add/Remove Data Filter

To add or remove a data filter to the one or several columns:

  1. Highlight the desired column(s).
  2. Click the Add/Remove Data Filter button Button Add Remove Column Filter.png.

When a data filter is added, it is by default an empty filter, no filtering condition is set. Once filtering conditions are set, the filter is automatically named by the filter condition and the filter icon is fully filled with color green.

Column Filter 001.png

Clicking the Add/Remove Data Filter button on a selected column with an applied filter, removes the filter. The same thing is done when you clear a filter from the filter icon menu.

  1. Highlight the desired column(s) with data filters.
  2. Click the Filter icon and choose Clear Filter in the context menu.

Column Filter 002.png

If you want to toggle your filter on or off without losing your filter, just disable the filter (see next).

Enable/Disable Data Filter

When a data filter is added, it is possible to enable or disable it by either of the following:

  1. Highlight the desired column(s).
  2. Click the Enable/Disable Data Filter button Button Enable Disable Column Filter.png.

or

  1. Highlight the desired column.
  2. Click on the Filter icon and bring up context menu, check Enable Filter to enable and uncheck to disable.

Column Filter 003.png

If multiple columns are selected in the second case, only the data filter of the leftmost selected column will be disabled/enabled.

When a data filter is disabled, the filter icon turns to grey, and there is an "off" before the filter name.

Column Filter 004.png

If your selected range of columns include both enabled and disabled columns, when you click the Button Enable Disable Column Filter.png button, all columns will be disabled.

Reapply Data Filter

In case the data is updated or filtering condition is changed, you could click the Reapply Data Filter button Button Reapply Column Filter.png to reapply the data filter.

Customize Data Filters

Origin will detect the data type and automatically assign one of the three data filters (date, numeric, text) to the corresponding columns. The filtering conditions need to be set when customizing data filters.

There are two ways to customize a data filter,

  1. Click on the Filter icon to bring up a context menu.
  2. For each data filter type, several quick menu items are available, choose one to open the corresponding Simple Filter dialog.
  3. Set data filter conditions and click OK.

or

  1. Click on the Filter icon to bring up a context menu.
  2. Select the Custom Filter to open the Custom Data Filter dialog.
  3. Set data filter conditions and click Apply or OK.

Column Filter 005.png

Menu Options and Dialog Controls

Three general types of filters are supported, grouped by Format:

  • Date
  • Numeric
  • Text, Month or Day of Week.

Available menu options vary by Format. Origin uses the column's Format Properties to determine which menu options to display.

Date Filter

When the column Format = Date, you have the following filtering options:

Filter menu date.png

Clicking Equals, Before or After will open the Simple Date Filter dialog box. Clicking Between provides a simple dialog for setting a date range.

Filter simple date filter.png

Set the Formula Type, Value and Condition2 or From and To, to filter by date.

The Simple Date Filter Condition2 is used for And/Or filtering. The default state is None but you can set the drop-down to either And or Or and create a second filter condition.

Filter simple date condition2.png
None No second filter condition (default).
And Keep rows where the date and time holds true for both query conditions, and hide others.
Or Keep the rows where the date and time holds for either one of the two query conditions, and hide others.


For more advanced filtering, use the Custom Data Filter dialog, available when you click the Custom Filter menu item at the bottom of the Date filter menu.

Numeric Filter

When the column Format = Numeric, you have the following filtering options:

Filter menu numeric.png

Clicking Equals, Less Than or Greater Than will open the Simple Numeric Filter dialog box. Clicking Between provides a simple dialog for setting a numeric range.

Filter simple numeric filter.png

Set the Formula Type, Value and Condition2 or From and To, to filter by number.

The Simple Numeric Filter Condition2 is used for And/Or filtering. The default state is None but you can set the drop-down to either And or Or and create a second filter condition.

Filter simple numeric condition2.png
None No second filter condition (default).
And Keep rows where the numeric condition holds true for both query conditions, and hide others.
Or Keep rows where the numeric condition holds for either one of the two query conditions, and hide others.


Clicking Top 10 or Bottom 10 opens the the Top N dialog box. Here you can filter out all but the highest or lowest values by number of Items or by Percent.

Filter numeric topN.png


For more advanced filtering, use the Custom Data Filter dialog, available when you click the Custom Filter menu item at the bottom of the Date filter menu.

Text Filter

The text filter will be applied if the data format of the selected column is Text, Month or Day of Week.

From the quick menu, you can select/deselect the check boxes to show/hide corresponding text entries.

Column Filter Menu 002.png

Custom Filter (Simple Text)

Click the Custom Filter quick menu item to bring up the Custom Filter(Simple Text) dialog.

Column Filter Menu 003.png

  • The Entry column lists all the unique text entries from the selected range. The Count column lists the frequency of the corresponding text entry in the selected range.
  • Clicking on either column header will sort the results in the data panel by ascending or descending order.
  • Use check boxes to show or hide the rows corresponding to each entry. Clearing a box hides corresponding rows.
After applying a text filter, the Filter label row will contain a list of the text entries which have not been hidden with the filter. By default, the entries in this cell are separated by a space (" "). The system variable @TFS can be used to switch the separator: 0=Enter, 1=Space, 2=Comma, 3=Semicolon.
Filter text label row.png

Another system variable @TFL can be used to set the max number of characters in the text filter label row. By default, this value is 50. The first string and last string will always show in full (sorting is alphabetical); unlisted strings will be shown as "...".

For information on these two LabTalk System Variables see the LabTalk System Variable List.

Custom Filter (Advanced Text)

Select the Advanced check box to bring up the Custom Filter(Advanced Text) dialog.

Column Filter Menu 004.png

  • The Condition panel is used to input filter conditions. Click the triangle button to bring up the fly-out menu to call text-related functions.
  • The Before Condition Script panel is used to define LabTalk script which will run before the filtering operation.
  • The variable i can be used in the Condition panel as a substitute for row index. For example, i>15 means the rows after the 15th row. It also can be used in the Custom Data Filter dialog.
  • Wildcards are supported:
Usage of special characters in the advanced panel of text filter
Symbol Usage
 ?(question mark) Stands for any single character, e.g. "a?c" finds "abc" or "adc" but will not find "abbc"
*(asterisk) Stands for any string of characters, e.g. "abc*e" finds "abcde" or "abcdde" or "abce"
==(two equation marks) Stands for full match, e.g. x=="a*" finds exactly "a*" but not "abc"

The following short tutorial will show you how to use the wildcard control for text filter.

  1. Import the Automobile.dat file from \Samples\Statistics\ into Origin.
  2. Highlight column B and click the Add/Remove Data Filter button Button Add Remove Column Filter.png.
  3. Click on the Filter icon and select Custom Filter.
    Column Filter 006.png
  4. Check for the Advanced check box.
  5. In the Query box, enter:
    x LIKE "S*"
  6. Click OK and then Yes for the reminder message(if any).
  7. Go back to the original worksheet, only the rows with text starting with "s" remain.

Custom Data Filter dialog

The Custom Data Filter dialog is used to perform advanced filtering of Date and Numeric data.

Column Filter Menu 001.png

  • Use the Condition panel to input filter conditions. Note that you can click the triangle button Button Select Data Right Triangle.png to open a fly-out menu for inserting of date- and numeric-related functions.
  • The Before Condition script panel (if not visible, click the Button Show Scripts.png button) is used to define LabTalk scripts to run before filtering. Note, again, the triangle button Button Select Data Right Triangle.png gives quick access to functions for use in your script.

Custom Data Filter Buttons

Test The rows which meet the filter condition will be highlighted in the original worksheet, these rows will remain after filtering.
OK Apply the change of filter conditions and close the dialog.
Cancel Close the dialog without applying the modification of filter conditions.
Apply Apply the change of filter conditions without closing the dialog.


For numeric filtering, a few built-in LabTalk functions are not included in the fly-out menu. The following table documents these functions:

Expression Usage
x.between(x1,x2) Return the sub range of x between user-input values x1 and x2, equal to
x<=x2 && x>=x1
.

*See note below table.

x.top(10,0) Return the top 10 values of x.
x.top(10,1) Return the top 10% of values of x.
x.bottom(10,0) Return the bottom 10 values of x.
x.bottom(10,1) Return the bottom 10% of values of x.
x.top(n,0/1) Return the top n values of x; when 0 is chosen, n is the number of items, when 1 is chosen, n is the percentage.
x.bottom(n,0/1) Return the bottom n values of x; when 0 is chosen, n is the number of items, when 1 is chosen, n is the percentage.

* In this expression, x1 and x2 are typically row numbers. If you wish to use variables in this expression, you should not use "x1" and "x2" as they are widely-used system variables and their values may change. Instead, consider using page variables v1...v4.

For Text, Date, and Numeric filters, Origin also supports the function x.count() to count the number of the duplicated data. For example: To keep text data which number is over 3, in the Custom Data Filter dialog you can set: Condition: x.count() > 3

Filter Menu

Custom Data Filter Filter Menu.png

From the Filter menu you can:

  • Load samples
  • Load saved custom filters
  • Save custom filters

Loaded filters display at the bottom of this menu (if none loaded, reads "Empty").

Ignore Hidden Rows

By default, rows hidden by a filter are ignored in graphing operations. You can change this behavior and include such rows using either of the following:

  1. Activate a worksheet.
  2. In the Command or Script window, run the script:
wks.ignorehidden = 0;

or

  1. From the top menu, select Format:Worksheet or press F4 to open the Worksheet Properties dialog.
  2. In the Miscellaneous tab, uncheck Ignore Hidden Rows on Plotting and Analysis.
Note: Hidden rows are not ignored in LT scripts and Set Values.


When you apply a mask and add a data filter on the column data, the masked data is still shown on the column by default.

Whether showing the masked data on the column with data filter, that is controlled by the system variable @FBM.

  • 0:Hide
  • 1:Show

Save Data Filter into Operation

In the Recalculate lock icon's context menu for Copy Columns to... and Pivot Table, there are three worksheet filter options. They are used to control whether the results will be affected by further filter changes.

Note: Recalculate Mode of Copy Columns and Pivot Table should be set to Auto or Manual.

Filters Lock.png

Worksheet Filters:Lock

When this option is selected, the result will be locked from data filter condition change of source column(s). So when the filter condition in the source column worksheet is changed, it will not trigger update in the result columns.

Worksheet Filters:Reload

This option is only available when the Worksheet Filter: Lock has been selected already. It reloads the data filter condition from the source column(s) to the result column(s). i.e. after you changed the data filter condition of the source column(s), click this option to trigger the auto update of the locked result column(s), so that the same filter condition applies to result column(s) as well.

Worksheet Filters:Push Back

This option is only available when the Worksheet Filter: Lock has been selected already. It pushes the initial data filter condition back to the source column(s). i.e. after you changed the data filter condition of the source worksheet, click this option and the most recent data filter condition that has been applied from source column(s) to result column(s) will be pushed back to the source worksheet. Note that if you applied a data filter directly to result column(s), it will not be pushed back to source.

 

© OriginLab Corporation. All rights reserved.