Subject: reading data from csv files

From: "Jakson A. Aquino" <[email-masked]>
Date: Thu, 7 Sep 2006 08:15:25 -0300

Hello All,

I've made some changes in the algorithm used by statist to open data files.
All files that were opened should continue to be read as before, but now
statist is capable of reading .csv files as created by spreadsheet programs.

Statist should open most files without problems, automatically detecting if
the file has a header with variable names and correctly parsing the lines
with data. To achieve this goal, among other changes, I added double quotes
and commas to the ignore[] string (data.c), used to distinguish between data
and field separators.

I added the following options to let users fine tune statist behavior if it
fails to open a file correctly:

--header             : the file has column names in the first line
--noheader           : the file does not have column names
--sep <char>         : field separator character
--dec <char>         : decimal delimiter character (default: '.')
--na-string <string> : indicator of missing values (default: "M")

In the scripts with tests that are attached to this e-mail, it was possible
to open files with a wide range of formatting styles, and it was necessary
to use command line options only when the field separator was an empty space
and the decimal delimiter was a comma, as can be seen in the table below:

         Command line options to open a data file with statist
                 according to how the data is formatted
============================================================================
                               |       dec = .        |      dec = ,            
                               |----------------------|---------------------
                               | sep = ,  | sep = ' ' | sep = , | sep = ' '  
-------------------------------|----------|-----------|---------|-----------
header         | quoted data   |          |           |         |
(statist style)|---------------|----------|-----------|---------|-----------
( #% )         | not quoted    |          |           |         | --dec ","  
---------------|---------------|----------|-----------|---------|-----------
               | quoted data   |          |           |         |
header         |---------------|----------|-----------|---------|-----------
               | not quoted    |          |           |         | --dec ","  
---------------|---------------|----------|-----------|---------|-----------
               | quoted data   |          |           |         |
no header      |---------------|----------|-----------|---------|-----------
               | not quoted    |          |           |         | --dec ","  
============================================================================
Note: the combination of (data not quoted & dec = "," & sep = ",")
is possible only for integer values.

I also updated the documentation to reflect the changes, and if no problem
is detected in the new algorithm, it will be part of the next release of
statist.  For now, the changes are available in the cvs:

cvs -z3 -d:pserver:[email-masked]:/home/statist/jail/statistrepository co statist

One disadvantage of these new features is that statist is taking about
30% more time to load a file.

All commentaries and suggestions are welcome!

Best regards,

Jakson

---

From: Andreas Beyer <[email-masked]>
Date: Thu, 07 Sep 2006 09:47:07 -0700

Hi Jakson,

Your extensions are certainly valuable! Particularly I like automatic 
detection of the input format. Fine-tuning via command line options is 
good, but eventually you may want to allow for defining the details when 
opening the file (in interactive mode). This would allow to define 
details for every file separately.  For instance there could be a sub-menu
DATA MANAGEMENT/FILE FORMAT OPTIONS

Andreas

---

From: "Jakson A. Aquino" <[email-masked]>
Date: Thu, 7 Sep 2006 16:46:44 -0300

Hi Andreas,

On Thu, Sep 07, 2006 at 09:47:07AM -0700, Andreas Beyer wrote:

> Fine-tuning via command line options is good, but eventually you may
> want to allow for defining the details when opening the file (in
> interactive mode). 

This isn't difficult to implement. We have just to define the
better way of doing it.

> This would allow to define details for every file separately.  For
> instance there could be a sub-menu DATA MANAGEMENT/FILE FORMAT
> OPTIONS

I can add an option to statistrc defining whether statist will try to
auto detect the file format or ask the user. In auto detect mode (and
not in silent mode), if some error happens (different number of
columns for different lines), the user would be asked to set the
options.  Before asking the questions, statist could show the first
few lines (truncated after about 40 characters), just as it shows the
content of the current directory when asking for a file name.

The sub-menu DATA MANAGEMENT/FILE FORMAT is useful too. In auto detect
mode, statist will read:

     1,1 2,2        1 1 2 2
     1,1 2,2   as   1 1 2 2
     1,1 2,2        1 1 2 2

Because statist would find the same number of columns in all lines,
there would be no error message. Only the presence of column names
would avoid that statist wrongly read this file. The sub-menu item
would make statist re-read the file with the correct options. Would
this statist behavior be OK in your opinion? Or were you think in
something different?

Jakson