# Translate Spreadsheet Commands to MatrixOP?

I would like to learn MatrixOP. I admit to being a bit puzzled by how to use it effectively. I am especially interested to translate "spreadsheet-like" calculations in to Igor Pro.

Here is something that can be done in a spreadsheet with no effort: average and standard uncertainties in rows of data. Here is what I cobbled together using what I know as the standard.

Function DoCalcs()

wave rtimes0, rtimes_ave, rtimes_std

variable ic

for (ic=0;ic<5;ic+=1)
make/N=3/FREE ww
ww = rtimes0[ic][p]
WaveStats/Q ww
rtimes_ave[ic] = v_avg
rtimes_std[ic] = v_sdev
endfor
return 0

end

How would this be done in Igor Pro using MatrixOP?
A close approximation to spreadsheet functions is to use MatrixOp functions sumCols or sumRows and divide by numRows or numCols to get the average. In Igor Pro 7 there is an averageCols function, which can be implemented after a transpose to do the equivalent operation on rows. I'm not sure how this handles NaNs though. I am stuck to suggest something for standard deviation calculation using MatrixOp.
jjweimer wrote:
I would like to learn MatrixOP. I admit to being a bit puzzled by how to use it effectively. I am especially interested to translate "spreadsheet-like" calculations in to Igor Pro.

Welcome to MatrixOP :)

The main thing to remember about this operation is that it is "matrix-centric" (i.e., focuses on 2D data) but it also handles all 1D waves as a special case of Nx1 matrices. It handles 3D and 4D on a layer-by-layer basis, and it has some special operations that extract smaller dimensional subsets from 3D and 4D waves.

Quote:
Here is something that can be done in a spreadsheet with no effort: average and standard uncertainties in rows of data. Here is what I cobbled together using what I know as the standard.

Function DoCalcs()

wave rtimes0, rtimes_ave, rtimes_std

variable ic

for (ic=0;ic<5;ic+=1)
make/N=3/FREE ww
ww = rtimes0[ic][p]
WaveStats/Q ww
rtimes_ave[ic] = v_avg
rtimes_std[ic] = v_sdev
endfor
return 0

end

How would this be done in Igor Pro using MatrixOP?

Step 1: skip the make inside the loop and re-write:
make/N=3/FREE ww
ww = rtimes0[ic][p]

as:
`MatrixOP/O/FREE ww=row(rtimes0,ic)`

so MatrixOP creates a wave that contains the row data and you do not need the separate Make command.

Step 2:
WaveStats/Q ww
rtimes_ave[ic] = v_avg
rtimes_std[ic] = v_sdev

So it seems that you want to average over rows and compute the stdv.

You have several options here depending on what you might want to do with intermediate values. If you wanted to compute these one at a time (not recommended), you could use the MatrixOP mean() function.

The most efficient MatrixOP translation is to skip the loop completely using the following three lines:
MatrixOP/O/FREE aa=rtimes0^t  // transpose; more efficient
MatrixOP/O avg=averageCols(aa)
MatrixOP/O var=sqrt(varcols(aa))

I hope this helps,

A.G.
WaveMetrics, Inc.

jjweimer wrote:
I would like to learn MatrixOP. I admit to being a bit puzzled by how to use it effectively. I am especially interested to translate "spreadsheet-like" calculations in to Igor Pro.

Here is something that can be done in a spreadsheet with no effort: average and standard uncertainties in rows of data. Here is what I cobbled together using what I know as the standard.

Thanks for the insights.

Yes, AG, MatrixOP is now on my radar to learn. :-)

I might be so bold as to ask that averageRows, averageCols, varRows, and varCols become standard expressions in MatrixOP's lexicon. While I know how a transpose works, folks who use spreadsheet calculations for just the basics typically do not. For them (and me too), it would be nice to forego the "thinking about it" part and just be able to write ...

MatrixOP/O avg=averageRows(rtimes0)
MatrixOP/O var=sqrt(varRows(rtimes0))

I might also be even bolder and wonder whether someday we might write this for MatrixOP (and other operations that might be done in sequences) ...

INVOKE MatriOP/O
avgR = averageRows(...)
varR = sqrt(varRows(...))
avgC = averageCols(...)
varC = sqrt(varCols(...))
UNINVOKE MatrixOP

This would make a compilation of "spreadsheet-like" functions easier to document and teach.

--
J. J. Weimer
Chemistry / Chemical & Materials Engineering, UAH
jjweimer wrote:

I might be so bold as to ask that averageRows, averageCols, varRows, and varCols become standard expressions in MatrixOP's lexicon. While I know how a transpose works, folks who use spreadsheet calculations for just the basics typically do not. For them (and me too), it would be nice to forego the "thinking about it" part and just be able to write ...

I will plead guilty; I have not used spreadsheet programs in about 20 years so their terminology is not exactly familiar. Also, when I designed MatrixOP, spreadsheet functionality and terminology where not even a remote consideration; I was just trying to provide a powerful tool for advanced users.

Quote:

I might also be even bolder and wonder whether someday we might write this for MatrixOP (and other operations that might be done in sequences) ...

INVOKE MatriOP/O
avgR = averageRows(...)
varR = sqrt(varRows(...))
avgC = averageCols(...)
varC = sqrt(varCols(...))
UNINVOKE MatrixOP

This would make a compilation of "spreadsheet-like" functions easier to document and teach.

I am not convinced that the syntax above has any benefits besides the fact that you are factoring out MatrixOP. I think it helps to remember that MatrixOP is not just another way to compute wave1+wave2. It has significance as a Matrix operation and for that it seems useful to designate the command as such. If that is not immediately obvious, consider what it means to execute
MatrixOP/O aa=wave1*wave2

when wave1 is 3D and wave2 is 2D.

A.G.
WaveMetrics, Inc.

Igor wrote:

I will plead guilty; I have not used spreadsheet programs in about 20 years so their terminology is not exactly familiar. Also, when I designed MatrixOP, spreadsheet functionality and terminology where not even a remote consideration; I was just trying to provide a powerful tool for advanced users.

AG ... Perhaps we should take this discussion to a side one-on-one thread. In a nutshell, I think that bringing in some basic spreadsheet terminology to Igor Pro would really enhance it in two ways. First, it would offer a stronger side-by-side competition between Igor Pro and "that other analysis program" whose Origin is based on a spreadsheet paradigm. Secondly, it really will offer folks like me who teach undergraduate courses a stronger platform to advocate for Igor Pro as the best-next-step in programming tool after a spreadsheet package (rather than MatLab or MathCad or Mathematica or python or GNUplot or ... not really much of anything else).

Igor wrote:

I am not convinced that the syntax above has any benefits besides the fact that you are factoring out MatrixOP. I think it helps to remember that MatrixOP is not just another way to compute wave1+wave2. It has significance as a Matrix operation and for that it seems useful to designate the command as such. If that is not immediately obvious, consider what it means to execute
MatrixOP/O aa=wave1*wave2

when wave1 is 3D and wave2 is 2D.

I am drawing on analogies to Maple. When a particular set of sequential operations requires a particular package, the syntax used is to start with a statement > with(_package_ ) as a predicate to the full set of steps that follow. I offer this only as a case to collapse the syntax of MatrixOP for a set of code lines, not to defeat the functionality of MatrixOP. And, I offer it perhaps as a thought for Igor Pro 8, not Igor Pro 7. :-)

--
J. J. Weimer
Chemistry / Chemical & Materials Engineering, UAH
Here's a starting point for a discussion of basic spreadsheet functions that I would like to have easier equivalents to Igor Pro ...

This operation invokes spreadsheet-equivalent commands on a 2-D matrix of data.
Commands return a 1-D wave of values in the result. Many of the commands supported in the
Spreadsheet operation have direct analogs in other functions or operations in Igor Pro.
The commands are reproduced here as well to give users who know spreadsheet commands
a clearer translation of their common syntax when using Igor Pro.

The syntax of the Spreadsheet operation is

result — a 1-D wave
command — an function command call (see list below)
matrix — a 2D wave
direction — the specific term rows or cols

Commands that are currently supported are below.
They have a literal one-to-one translation to common spreadsheet applications.

average
stdev
sum
count

Example

Spreadsheet/Z result = average(dailyruns,cols) // returns the average across columns of dailyruns

--
J. J. Weimer
Chemistry / Chemical & Materials Engineering, UAH
jjweimer wrote:

AG ... Perhaps we should take this discussion to a side one-on-one thread. In a nutshell, I think that bringing in some basic spreadsheet terminology to Igor Pro would really enhance it in two ways. First, it would offer a stronger side-by-side competition between Igor Pro and "that other analysis program" whose Origin is based on a spreadsheet paradigm. Secondly, it really will offer folks like me who teach undergraduate courses a stronger platform to advocate for Igor Pro as the best-next-step in programming tool after a spreadsheet package (rather than MatLab or MathCad or Mathematica or python or GNUplot or ... not really much of anything else).

Glad to do so when I return to the office. I'm on vacation in Korea at the moment.

AG
With Igor 7, you can get pretty far already, especially with the Wavestats/PCST flag

I made some simple functions that make use of the PCST flag to achieve the desired result.

On a 64 bit windows machine, I placed the attached file in the following folder:
C:\Program Files\WaveMetrics\Igor Pro 7 Folder\Igor Procedures

Once that file is in place, open up Igor and you have direct access to call the functions:
average
count
stdev

Try it:
Make/D/O/N = (100,100) testdata = p*q
average(testdata)

If I understood what you are looking for, this already gives the desired result without waiting for Igor 8, or having to invoke some extra package, or even mess with XOPs.
This will only operate on column data, and of course the point is well taken that for higher dimensions things get complicated, but I think it handles what you are looking for.

I am not sure if it is well advised or not, but I definitely do this sort of thing with some of my favorite custom functions - another selling point of Igor Pro in my mind.
ExcelCommands.ipf (870 bytes)
BMangum wrote:
With Igor 7, you can get pretty far already, especially with the Wavestats/PCST flag

That's a good point. There's also the /RMD flag that allows you to specify a subset of the data that should be considered in the more familiar [rows][cols]... subrange specification.

BMagnum wrote:

On a 64 bit windows machine, I placed the attached file in the following folder:
C:\Program Files\WaveMetrics\Igor Pro 7 Folder\Igor Procedures

For the record, we recommend that you place things like this in the Igor Procedures directory within your Igor Pro User Files directory, not in Igor's own folder. For more information, execute:
DisplayHelpTopic "Igor Pro User Files"

I see the direction this is going. Thanks. Unfortunately, I have (yet) to download the Igor Pro 7 beta. Even so, I see three "problems" with this approach.

* The return for a spreadsheet operation is "hidden". For example, where do you get the results after you do the `average(...)` function?
* Translations of the syntax from a spreadsheet to Igor Pro is not exactly preserved in all cases. The change `sumalong(...) <- sum(...)` requires an extra step and may not be intuitive to everyone (why not `sumrange(...)` for example?).
* Folks have to load extra stuff

I suggest that, when considering the inclusion of spreadsheet options in Igor Pro, the discussion must start from this question: Does this method support the ability for a NOVICE USER to step in to Igor Pro and "see" spreadsheet commands. By novice, I mean "anyone who has essentially no clue how to program anything at all". The translation for spreadsheet -> Igor Pro has got to be as easy as possible for them; it has got to be "oh, I see how to do this", not "oh, I'll have to _learn_ how to do this". Otherwise, novice users will drop it and go on to something else. I also suggest that, the development should be folded in to Igor Pro as though it is a built-in "package". IOW, it should be wrapped in an in-built operation `Spreadsheet ...`. This is a convention that "sticks" for novice users and seasoned programmers alike.

I see that I missed the option to set subranges, as can be done in a spreadsheet. Food for further thought.

--
J. J. Weimer
Chemistry / Chemical & Materials Engineering, UAH
jjweimer wrote:
I suggest that, when considering the inclusion of spreadsheet options in Igor Pro, the discussion must start from this question: Does this method support the ability for a NOVICE USER to step in to Igor Pro and "see" spreadsheet commands. By novice, I mean "anyone who has essentially no clue how to program anything at all". The translation for spreadsheet -> Igor Pro has got to be as easy as possible for them; it has got to be "oh, I see how to do this", not "oh, I'll have to _learn_ how to do this". Otherwise, novice users will drop it and go on to something else. I also suggest that, the development should be folded in to Igor Pro as though it is a built-in "package". IOW, it should be wrapped in an in-built operation `Spreadsheet ...`. This is a convention that "sticks" for novice users and seasoned programmers alike.

I thought I'd chime in at this point to say: I know exactly what you mean. However, I think that spreadsheets and Igor are diammetrically opposed. When showing somebody Igor for the first time. I explain that a Table is not like a spreadsheet and explain about 1D waves. The ipf posted above is a good workaround for this use case IMO.
sjr51 wrote:
...However, I think that spreadsheets and Igor are diammetrically opposed. When showing somebody Igor for the first time. I explain that a Table is not like a spreadsheet and explain about 1D waves. The ipf posted above is a good workaround for this use case IMO.

I'd like to be able to teach novices (my undergraduate students) that "Igor Pro is an extension starting from and going beyond spreadsheets" rather than "Igor Pro is diametrically opposed to a spreadsheet program". I agree, a data table in Igor Pro is NOT a spreadsheet, and I would not argue that it ever should be (that can be "the Origin way"). Finally, I know for certain that novices (again ... my undergraduate students) will see the posted ipf as a workaround in the same way that construction workers might see the back end of a screwdriver can be a hammer in a pinch, but then they will just go get a hammer.

Here's the instructions I would love to give to a novice ...

* Use the load data menu to import your data from Excel in to Igor Pro. Put the data in a wave called mydata
* On the command line, type these four commands ...
-- Display colaverage
-- Modifygraph ... // add colstev as the error bars
* You now have the starting point to make a publication-ready graph. See, wasn't that so much faster than mucking about in Excel? :-)

--
J. J. Weimer
Chemistry / Chemical & Materials Engineering, UAH
jjweimer wrote:

Here's the instructions I would love to give to a novice ...

* Use the load data menu to import your data from Excel in to Igor Pro. Put the data in a wave called mydata
* On the command line, type these four commands ...
-- Display colaverage
-- Modifygraph ... // add colstev as the error bars
* You now have the starting point to make a publication-ready graph. See, wasn't that so much faster than mucking about in Excel? :-)

If you're using Igor 7, it's actually that easy: