| |||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||
Reading 3-dimensional arrays Description This example shows how to read a 3-dimensional array from an excel file (threedimarr.mos) and from a generic spreadsheet (threedimarr5.mos) where the last index is specifed 'across the columns', resulting in a pivot-style table format where the first N-1 columns contain index values (here: 2 index columns). Model versions (2) and (4) show the same example for databases, and model version (6) works with data in CSV format. Note that standard use of the 'partndx' option for spreadsheets assumes that the last index is a set of type 'range', the example partidxstr.mos defines a utility routine 'makelabel' that makes it possible to work with a 'set of string' for the last index.
Source Files By clicking on a file name, a preview is opened at the bottom of this page.
Data Files
partidxstr.mos
(!******************************************************
Mosel Example Problems
======================
file partidxstr.mos
```````````````````
Reading partial-index table format with string column header
for the last index for spreadsheet files in XLS, XLSX or CSV format
The subroutine 'makelabel' provides a generic way of creating
the required header definition for the 'partndx' format.
(c) 2024 Fair Isaac Corporation
author: S.Heipcke,Y.Colombani, Jan. 2024
*******************************************************!)
model partidxstr
uses "mmsystem", "mmreflect", "mmsheet"
(!@doc.
@descr Constructing the header selection string for reading a partially indexed array
@param fname Spreadsheet file name (XLS, XLSX, or CSV format)
@param rangename Name of a sheet or range (first sheet if not specified)
@param u Array to be populated from the specified input
@info The data array 'u' serves to determine the number of indices. If the array has N dimensions, it is assumed that the values of the first N-1 indices are contained in the N-1 initial columns of the data range and all other columns correspond to index values for the last dimension of the input array.
!)
public function makelabel(fname:text,rangename:string,u:any):string
declarations
colnames:dynamic array(1..1,1..1000) of string ! Due to the 'dynamic' flag this will work even if the actually selected area exceeds 1000 columns
label:text
allcols:set of string
colorder:array(string) of integer
cr: range
end-declarations
initialisations from fname
colnames as "noindex;"+rangename
end-initialisations
if not u is array then
writeln("Third argument of function 'makelabel' must be an array.")
return
end-if
with nbdim=u.array.nbdim, ndx=u.array.index(nbdim).string do
allcols:=union(i in nbdim..colnames.size){string(colnames(1,i))}
if not allcols <= ndx then ndx+=allcols; end-if
cr:=1..colnames.size
forall(i in cr) colorder(colnames(1,i)):=i
label:="skiph;partndx;"+rangename+"(#1"
forall(c in 2..nbdim-1) label+=",#"+c
forall(c in ndx|c in allcols) label+=",#"+colorder(c)
end-do
returned:=string(label+')')
end-function
!*******************************************************************
procedure readpartindxl(drvfilename:string)
declarations
TabA: dynamic array(string,range,string) of real
TabP: dynamic array(S1:set of string,R:range,S2:set of string) of real
it: iterator
end-declarations
!**** Reading a partially indexed table with string header fields
fopen(drvfilename,F_INPUT)
initialisations from getfname(F_INPUT)
TabA as makelabel(getfname(F_INPUT),"[]",TabA)
end-initialisations
! fclose(F_INPUT)
writeln("Index sets:", TabA.index(1), TabA.index(2), TabA.index(3))
! Enumerate the existing array entries (using an iterator since sets are not named)
inititer(it,TabA)
while (nextcell(it)) writeln(it.indices, ":", TabA(it))
!**** Reading a partially indexed table with preselection
S1:={"xy","Z","BB","ab"} ! Prepopulating the index set
! fopen(drvfilename,F_INPUT)
! Various range specifications that result in the same selection:
initialisations from getfname(F_INPUT)
TabP as makelabel(getfname(F_INPUT),"pidxtest",TabP) ! predef. range name
! TabP as makelabel(getfname(F_INPUT),"[1$A:F]",TabP)
! TabP as makelabel(getfname(F_INPUT),"[A:F]",TabP)
end-initialisations
fclose(F_INPUT)
writeln("Index sets:", S1,R,S2)
! Enumerate the existing array entries
forall(i in S1, j in R, k in S2 | exists(TabP(i,j,k)))
writeln([i,j,k], ":", TabP(i,j,k))
end-procedure
!*******************************************************************
procedure readpartindcsv
declarations
TabA: dynamic array(string,range,string) of real
TabP: dynamic array(S1:set of string,R:range,S2:set of string) of real
it: iterator
end-declarations
!**** Reading a partially indexed table with string header fields
fopen("mmsheet.csv:partidxdata.csv",F_INPUT)
initialisations from getfname(F_INPUT)
TabA as makelabel(getfname(F_INPUT),"[]",TabA)
end-initialisations
! fclose(F_INPUT)
writeln("Index sets:", TabA.index(1), TabA.index(2), TabA.index(3))
! Enumerate the existing array entries (using an iterator since sets are not named)
inititer(it,TabA)
while (nextcell(it)) writeln(it.indices, ":", TabA(it))
!**** Reading a partially indexed table with preselection
S1:={"xy","Z","BB","ab"} ! Prepopulating the index set
! fopen("mmsheet.csv:partidxdata.csv",F_INPUT)
initialisations from getfname(F_INPUT)
TabP as makelabel(getfname(F_INPUT),"[A:F]",TabP)
end-initialisations
fclose(F_INPUT)
writeln("Index sets:", S1,R,S2)
! Enumerate the existing array entries
forall(i in S1, j in R, k in S2 | exists(TabP(i,j,k)))
writeln([i,j,k], ":", TabP(i,j,k))
end-procedure
!**********************************************************
writeln("XLS:")
readpartindxl("mmsheet.xls:partidxdata.xls")
writeln("XLSX:")
readpartindxl("mmsheet.xlsx:partidxdata.xlsx")
writeln("CSV:")
readpartindcsv
end-model
| |||||||||||||||||||||||||||||||||||||||
| © Copyright 2024 Fair Isaac Corporation. |
