2.7.2.2 Worksheet Data ManipulationWorksheet-Data-Manipulation
In this section we present examples of X-Functions for basic data processing. For direct access to worksheet data, see Range Notation.
worksheet
Copy Worksheet Data
Copy a Worksheet
The wcopy X-Function is used to create a copy worksheetworksheet, copy of the specified worksheet.
The following example duplicates the current worksheet, creating a new workbook with the copied worksheet:
wcopy 1! [<new>]1!;
Copy a Range of CellsCopy Range
The wrcopy X-Function is used to copy a range of cells from one worksheet to another. It also allows you to specify a source row to be used as the Long Names in the destination worksheet.
The following script copies rows from 5 to 9 of [book1]sheet1! to a worksheet named CopiedValues in Book1 (if the worksheet does not exist it will be created), and assigns the values in row 4 from [book1]sheet1! to the long name of the destination worksheet, [book1]CopiedValues!
wrcopy iw:=[book1]sheet1! r1:=5 r2:=10 name:=4 ow:=CopiedValues!;
To copy column and matrix object, please refer to Copy Column and Copy Matrix Data respectively.
Reduce Worksheet Data
Reduce Worksheet Dataworksheet, reduce data
Origin has several data reducing X-Functions like reduce_ex, reducedup, reducerows and reducexy. These X-Functions provide different ways of creating a smaller dataset from a larger one. Which one you choose will depend on what type of input data you have, and what type of output data you want.
Examples
The following script will create a new X and Y column where the Y will be the mean value for each of the duplicate X values.
reducedup col(B);
The following script will reduce the active selection (which can be multiple columns or an entire worksheet, independent of X or Y plotting designation) by a factor of 3. It will remove rows 2 and 3 and then rows 5 and 6, leaving rows 1 and 4, etc. By default, the reduced values will go to a new worksheet.
reducerows npts:=3;
The following script will average every n numbers (5 in the example below) in column A and output the average of each group to column B. It is the same as the ave LabTalk function, which would be written as col(b)=ave(col(a),5):
reducerows irng:=col(A) npts:=5 method:=ave rd:=col(b);
Extract Worksheet Data
worksheet, extract dataExtract Worksheet Data
Partial data from a worksheet can be extracted using conditions involving the data columns, using the wxt X-function.
// Import a sample data file
newbook;
string fname$ = system.path.program$ + "samples\statistics\automobile.dat";
impasc;
// Define range using some of the columns
range rYear=1, rMake=2, rHP=3;
type "Number of rows in raw data sheet= $(rYear.GetSize())";
// Define a condition string and extract data
// to a new named sheet in the same book
string strCond$="rYear >= 1996 and rHP<70 and rHP>60 and rMake[i]$=Honda";
wxt test:=strCond$ ow:="Extracted Rows"! num:=nExtRows;
type "Number of rows extracted = $(nExtRows)";
Output To New Workbook
You can also direct the output to a new workbook, instead of a new worksheet in the existing workbook, by changing the following line:
wxt test:=strCond$ ow:=[<new name:="Result">]"Extracted"! num:=nExtRows;
As you can see, the only difference from the earlier code is that we have added the workbook part of the range notation for the ow variable, with the <new> keyword. (show links and indexing to <new> modifiers, options, like template, name, etc)
Use Wildcard Search
LabTalk uses * and ? characters for wildcard in string comparison. You can try changing the strCond as follows:
string strCond$ = "rYear >= 1996 and rHP<70 and rHP>60 and rMake[i]$=*o*";
to see all the other makes of cars with the letter o.
Delete Worksheet Data
Delete Worksheet Data
Deleting the Nth row can be accomplished with the reducerows X-Function, described above.
This example demonstrates deleting every Nth column in a worksheet using a for-loop:
int ndel = 3; // change this number as needed;
int ncols = wks.ncols;
int nlast = ncols - mod(ncols, ndel);
// Need to delete from the right to the left
for(int ii = nlast; ii > 0; ii -= ndel)
{
delete wcol($(ii));
}
Sort Worksheet
Sort Worksheetworksheet, sort
The following example shows how to perform nested sorting of data in a worksheet using the wsort X-Function:
// Start a new book and import a sample file
newbook;
string fname$ = system.path.program$ + "Samples\Statistics\automobile.dat";
impasc;
// Set up vectors to specify nesting of columns and order of sorting;
// Sort nested: primary col 2, then col 1, then col 3:
dataset dsCols = {2, 1, 3};
// Sort col2 ascending, col 1 ascending, col 3 descending:
dataset dsOrder = {1, 1, 0};
wsort nestcols:=dsCols order:=dsOrder;
Split Worksheet
Origin provides the X-Function, wsplit, for the purpose of splitting one worksheet's columns into multiple worksheets.
The example below is going to import multiple CSV files, and then get the Amplitude data from all the data file into a worksheet, and then convert this worksheet to matrix to make a contour plot.
// Create a new workbook
newbook;
// Find all csv files in the specified folder
string strPath$ = system.path.program$ + "Samples\Batch Processing\";
findfiles path:=strPath$ fname:=csvFiles$ ext:=csv;
// Import all found csv files into one worksheet
impCSV fname:=csvFiles$ // All found csv files
options.Mode:=1 // From second file, start new column
options.names.FNameToSht:=0 // Not rename worksheet
options.names.FNameToBk:=0 // Not rename workbook
options.HeaderLines.SubHeaderLines:=2 // Two subheader lines
options.HeaderLines.LongNames:=1 // First subheader line is LongName
options.HeaderLines.Units:=2; // Second subheader line is Units
// Split the worksheet according to the Long Name
// The columns with the same Long Name will be in the same result worksheet
// All the result worksheets will be in the same new workbook
wsplit mode:=label label:=L;
// Activate the Amplitude worksheet
page.active$ = Amplitude;
// Convert the Amplitude worksheet to matrix directly
w2m;
// Make a contour for the amplitude
worksheet -p 226 contour;
Unstack/Stack Categorical Data
Unstack DataStack Data
Unstack Worksheet Columns
At times unstacking categorical data is desirable for analysis and/or plotting purposes. The wunstackcol X-Function is the most convenient way to perform this task from script.
In this example, categorical data is imported, and we want to unstack the data by a particular category, which we specify with the input range irng2. The data to be displayed (by category) is referenced by input range irng1. In this example, the column ranges are input directly, but range variables can also be used.
// Import automobile data
newbook;
string fpath$ = "\Samples\Statistics\Automobile.dat";
string fname$ = system.path.program$ + fpath$;
impasc;
// Unstack all other columns using automobile Make, stored in col 2
// Place "Make" in Comments row of output sheet
wunstackcol irng1:=(1, 3:7) irng2:=2 label:="Comments";
The result is a new worksheet with the unstacked data.
Stack Worksheet Columns
Staking categorical data is something like reverse operation of unstacking categorical data. In the original dataset, samples belong to different groups is stored in different columns. After stacking, the samples will be in different rows in the same column, with an additional column in the worksheet providing the group information. You can use wstackcol to stack worksheet columns.
In the following example, we open a workbook with categorical data first. And then with the first worksheet activated, and stack column B, C, and D by rows, including another column to be A.
// Open a workbook
string strBook$ = system.path.program$;
strBook$ += "Samples\Statistics\Body.ogw";
doc -o %(strBook$);
// Stack column B, C, D in Male worksheet
// Include column A as another column
// Method is By Rows
wstackcol irng:=(2:4) tr.identifiers:={L} include:=1 method:=1;
The result is a new worksheet in the same workbook with the stacked data.
Pivot Table
The wpivot X-Function is available for the purpose of quickly summarizing the data, so to analyze, compare, and detect the relationships in the data. That is an easy way to present data information.
// Create a new workbook
// And import a data file
newbook;
fname$ = system.path.program$ + "Samples\Statistics\HouseholdCareSamples.xls";
impExcel lname:=1;
// Make sure "HQ Family Mart" worksheet is activate
// And make a copy of this worksheet
page.active$ = "HQ Family Mart";
wcopy ow:=[<new>]"HQ Family Mart"!;
// Pivot table, row source is Make
// Column source is Brand, and data is Number in shelf
// The result will show the number of products in shelf
// for different brands and different makes
wpivot row:=col(D) col:=col(F) data:=col(K)
method:=sum total:=1 sort_total:=no sum:=1;
// Activate the source data worksheet
page.active$ = "HQ Family Mart";
// Pivot table, row source, column source and data column are the same
// For the smaller values, it will combine them across columns
// by 10% of the total
// In the result worksheet, the column info. is put to user-defined parameter rows
// The row name is the name of column's Long Name in source worksheet
wpivot row:=col(D)
col:=col(F)
data:=col(K)
method:=sum total:=1 sort_total:=no sum:=1
dir:=col threshold:=10 // Combine smaller values across column, by 10%
// Put column info (from column's Long Name) to user-defined parameters row
pos:=udl udlabel:=L;
Worksheet Filter
Worksheet Filter (Data Filter) in Origin is column-based filter to reduce rows of worksheet data by using the specified condition, so to hide the undesired rows for relevant data analysis and graphing. Three data formats are supported: numeric, text and date/time. In LabTalk, you can use the wks.col (wks.col.filter, wks.col.filter$, wks.col.filterenabled, wks.col.filterprescript$, and wks.col.filterx$) object to handle the data filter. And to run/re-apply the filter, use the wks.runfilter() method.
// Create a new workbook, and import the data
newbook;
string fname$ = system.path.program$ + "Samples\Statistics\Automobile.dat";
impasc;
// Set data filter for column 1, numeric type
wks.col1.filter = 1; // Add filter
wks.col1.filterx$ = year; // Set the variable "year" to represent column 1
// Set filter condition, between 1995 and 2000
wks.col1.filter$ = "year.between(1995,2000)";
// Set data filter for column 2, text type
wks.col2.filter = 1; // Add filter
wks.col2.filterx$ = make; // Set the variable "make" to represent column 2
// Set before query script
wks.col2.filterprescript$ = "string strFavorite$ = GMC";
wks.col2.filter$ = "make = strFavorite$"; // Set filter query string
// Run the worksheet filter
wks.runfilter();
// Disable the filter in column 1
wks.col1.filterenabled = 0;
// Re-apply the worksheet filter
wks.runfilter();
To detect whether there is filter in a worksheet, you can use the wks.hasfilter() method.
// If the active worksheet has filter, return 1, otherwise, return 0
wks.hasfilter() = ;
|