FICO
FICO Xpress Optimization Examples Repository
FICO Optimization Community FICO Xpress Optimization Home
Back to examples browserPrevious exampleNext example

Dataframe formats

Description

This example demonstrates dataframe-style representation of data. This functionality is provided for CSV format files via the 'diskdata' I/O driver and subroutine of module mmetc, for the spreadsheet I/O drivers 'xls', 'xlsx', and 'csv' of module mmsheet, and for databases accessed through an ODBC connection via SQL commands.

  • dataframecsv.mos: reading and writing dataframes for CSV format data via 'diskdata'
  • dataframedb.mos: dataframe-style reading of database tables via 'SQLdataframe'
  • dataframesht.mos: reading and writing dataframes for spreadsheets and CSV format data via mmsheet
Further explanation of this example: Whitepaper 'Using ODBC and other database interfaces with Mosel', Section Examples - Working with dataframe formats


Source Files
By clicking on a file name, a preview is opened at the bottom of this page.
dataframecsv.mos[download]
dataframedb.mos[download]
dataframesht.mos[download]

Data Files





dataframecsv.mos

(!******************************************************
   Mosel Example Problems
   ======================

   file dataframecsv.mos
   `````````````````````
   Dataframe functionality for reading and writing CSV files
   via 'diskdata' (I/O driver and subroutine)
   
  (c) 2021 Fair Isaac Corporation
      author: S.Heipcke, Oct 2021, rev. Dec. 2022
*******************************************************!) 
model 'dataframecsv'
 uses 'mmsystem', 'mmetc'

 public declarations
   data=`
C_e,C_d,C_c,C_b,C_a,C_s
1,,"3",true,5.5,"r 1"
6,7,"8",,10.5,"r 2"
`
   datanh=`
1,,"3",true,5.5,"r 1"
6,7,"8",,10.5,"r 2"
`
   datacsv=`
C_e,C_d,C_c,C_b,C_a,C_s
1,,"3",true,5.5,r 1
6,7,"8",,10.5,"r "" 2"
`
   datasepfmt=`
C_e;C_d;C_c;C_b;C_a;C_s
1;;"3";true;5,5;"r 1"
6;7;"8";;10,5;"r 2"
`
 end-declarations

 CSVFILE:="text:data"
 CSVFILENH:="text:datanh"
 CSVFMTFILE:="text:datacsv"
 CSVSEPFILE:="text:datasepfmt"

!*** I/O driver version populating row counter and column (field names) indices
 declarations
   dfd: dynamic array(r:range, s:set of string) of text
   dff: array(rf:range, sf:set of string) of text
   dfa: dynamic array(ra:range, sa:set of string) of any
 end-declarations
 initialisations from 'mmetc.diskdata:'
   dfd as "dataframe;skiph;"+CSVFILE
   dff as "dataframe;skiph;"+CSVFILE
   dfa as "dataframe;skiph;"+CSVFILE
 end-initialisations
 writeln("dyn:", dfd.size, " dns:", dff.size) ! Output displayed: dyn:10 dns:12
 writeln(dfa)
 initialisations to 'mmetc.diskdata:'
   dfd as "dataframe;skiph;res.csv"           ! Output CSV file with header line
   dfd as "dataframe;resnh.csv"               ! Output CSV file without header
 end-initialisations

!*** Subroutine version populating row counter and column (field names) indices
 declarations
   dfd1: dynamic array(r1:range, s1:set of string) of text
   dff1: array(r1f:range, s1f:set of string) of text
   dfa1: dynamic array(ra1:range, sa1:set of string) of any
 end-declarations
 diskdata(ETC_DATAFRAME+ETC_SKIPH, CSVFILE, dfd1)
 diskdata(ETC_DATAFRAME+ETC_SKIPH, CSVFILE, dff1)
 diskdata(ETC_DATAFRAME+ETC_SKIPH, CSVFILE, dfa1)
 writeln("dyn:", dfd.size, " dns:", dff.size) ! Output displayed: dyn:10 dns:12
 writeln(dfa1)
! Output CSV file with and without header line
 diskdata(ETC_SKIPH+ETC_OUT+ETC_CSV+ETC_DATAFRAME, "res1.csv", dfd1)
 diskdata(ETC_OUT+ETC_CSV+ETC_DATAFRAME, "resnh1.csv", dfd1)

!*** No header line
 declarations
   dfd2: dynamic array(r21:range, r2:range) of text
 end-declarations
 initialisations from 'mmetc.diskdata:'
   dfd2 as "dataframe;"+CSVFILENH
 end-initialisations
! Same as:
! diskdata(ETC_DATAFRAME, CSVFILENH, dfd2)
 writeln("size df2=", dfd2.size)            ! Output displayed: size df2=10
 writeln("r1=", r21, " r2=", r2)            ! Output displayed: r1=1..2 r2=1..6

!*** Selection of fields (I/O driver version only)
 declarations
   dfd3: dynamic array(r3:range,s3:set of string) of text
   dfa3: dynamic array(range,set of string) of any
 end-declarations
 initialisations from 'mmetc.diskdata:'
   dfd3 as "dataframe;skiph;csv(C_d,C_b);"+CSVFILE
   dfa3 as "dataframe;skiph;cols(C_d,C_b);"+CSVFILE
! Same as:
!   dfa3 as "dataframe;skiph;typedcsv(C_d,C_b);"+CSVFILE
!   dfa3 as "dataframe;skiph;cols(#2,#4);"+CSVFILE
 end-initialisations
 writeln("size df3=", dfd3.size)            ! Output displayed: size df3=2
 writeln("is integer:", dfa3(2,'C_d') is integer, 
         ", is boolean:", dfa3(1,'C_b') is boolean)

!*** Fixed number of rows
 declarations
   dfd4: dynamic array(1..1,s4:set of string) of text
 end-declarations
 initialisations from 'mmetc.diskdata:'
   dfd4 as "dataframe;skiph;"+CSVFILE
 end-initialisations
! Same as:
! diskdata(ETC_DATAFRAME+ETC_SKIPH, CSVFILE, df4)
 writeln("size df4=", dfd4.size)           ! Output displayed: size df4=5

!*** Typed (integer) array
 declarations
   dfi: dynamic array(ri:range,si:set of string) of integer
 end-declarations
! Reading data of other types into an integer array will result in an
! I/O error: instead of stopping we continue to display what has been read
 setparam("ioctrl",true)
  initialisations from 'mmetc.diskdata:'
   dfi as "dataframe;skiph;"+CSVFILE
 end-initialisations
! Same as:
! diskdata(ETC_DATAFRAME+ETC_SKIPH, CSVFILE, dfi)
 setparam("ioctrl",false)
 if getparam("iostatus")<>0 then
   writeln("Error reading input file, interrupted in line ", ri.size)
 end-if
! Display what we have managed to read
 writeln(dfi, " size dfi=", dfi.size)      ! Ouput displayed: size dfi=4

!*** Alternative separator characters (I/O driver version only)
 declarations
   dfd5: dynamic array(range, set of string) of text
   dff5: array(range, set of string) of text
   dfa5: dynamic array(range, set of string) of any
 end-declarations
 initialisations from 'mmetc.diskdata:'
   dfd5 as "dataframe;skiph;dsep=,;fsep=;;"+CSVSEPFILE
   dff5 as "dataframe;skiph;dsep=,;fsep=;;"+CSVSEPFILE
   dfa5 as "dataframe;skiph;dsep=,;fsep=;;"+CSVSEPFILE
 end-initialisations
 writeln("dyn:", dfd5.size, " dns:", dff5.size) ! Output displayed: dyn:10 dns:12
 writeln(dfa5)
 initialisations to 'mmetc.diskdata:'
   dfa5 as "dataframe;skiph;csv;dsep=,;fsep=;;ressep.csv"  ! Output CSV-format file with header line
   dfa5 as "dataframe;dsep=,;fsep=;;resnhsep.csv"  ! Output to file without header
 end-initialisations

!*** Reading file with CSV-style string formating
 declarations
   dfd6: dynamic array(range, set of string) of text
   dff6: array(range, set of string) of any
   dfa6: dynamic array(range, set of string) of any
 end-declarations
 initialisations from 'mmetc.diskdata:'
   dfd6 as "dataframe;skiph;csv;"+CSVFMTFILE       ! Untyped (all read as text)
   dff6 as "dataframe;skiph;csv;"+CSVFMTFILE       ! Untyped
   dfa6 as "dataframe;skiph;typedcsv;"+CSVFMTFILE  ! Typed
 end-initialisations
! Same as:
! diskdata(ETC_DATAFRAME+ETC_SKIPH+ETC_TYPEDCSV, CSVFMTFILE, dfa6)
 writeln("dyn:", dfd6.size, " dns:", dff6.size) ! Output displayed: dyn:10 dns:12
 writeln("is string:", dff6(1,'C_e') is string, 
         ", is string:", dff6(1,'C_b') is string,
	 ", string:", dff6(2,'C_s')='r " 2')
 writeln("is integer:", dfa6(1,'C_e') is integer, 
         ", is boolean:", dfa6(1,'C_b') is boolean,
	 ", string:", dfa6(1,'C_s')="r 1")

end-model

Back to examples browserPrevious exampleNext example