[R] [OT] (slightly) - OpenOffice Calc and text files

[David Winsemius]

On Oct 13, 2010, at 1:13 PM, Schwab,Wilhelm K wrote:

> Hello all,
>
> I had a very strange looking problem that turned out to be due to  
> unexpected (by me at least) format changes to one of my data files.   
> We have a small lab study in which each run is represented by a row  
> in a tab-delimited file; each row identifies a repetition of the  
> experiment and associates it with some subjective measurements and  
> times from our notes that get used to index another file with lots  
> of automatically collected data.  In short, nothing shocking.
>
> In a moment of weakness, I opened the file using (I think it's  
> version 3.2) of OpenOffice Calc to edit something that I had mangled  
> when I first entered it, saved it (apparently the mistake), and  
> reran my analysis code.  The results were goofy, and the problem was  
> in my code that runs before R ever sees the data.  That code was  
> confused by things that I would like to ensure don't happen again,  
> and I suspect that some of you might have thoughts on it.
>
> The problems specifically:
>
> (1) OO seems to be a little stingy about producing tab-delimited  
> text; there is stuff online about using the csv and editing the  
> filter and folks (presumably like us) saying that it deserves to be  
> a separate option.

You have been little stingy yourself about describing what you did. I  
see no specifics about the actual data used as input nor the specific  
operations. I just opened an OO.o Calc workbook and dropped a  
character vector, "1969-12-31 23:59:50" copied from help(POSIXct) into  
a2. I then copied it to a3 and formatted it to be in the precanned  
format, MM/DD/YYYY HH:MM:SS , noticed that it had not been interpreted  
as a data-time vlaue at all so entered =TODAY()+TIME(13;0;0) in a4 and  
=TIME(13;0;0) in a5, formated to a user specified custom time format  
of YYYY-MM-DD HH:MM:SS


Copied a5 to c1:c5
  saved to a text-csv file specifying the field separator as tab and  
the text-delimiter as '"' and got:

""time"		1899-12-30 13:00:00
"1969-12-31 23:59:50"		1899-12-30 13:00:00
"1969-12-31 23:59:50"		1899-12-30 13:00:00
2010-10-13 13:00:00		1899-12-30 13:00:00
1899-12-30 13:00:00		1899-12-30 13:00:00


This handling of dates and times does not seem particularly difficult  
to elicit andseems to represent dates in YYYY and times in "military  
time".

>
> (2) Dates that I had formatted as YYYY got chopped to YY (did we not  
> learn anything last time?<g>) and times that I had formatted in 24  
> hours ended up AM/PM.
>
> Have any of you found a nice (or at least predictable) way to use OO  
> Calc to edit files like this?

I didn't do anything I thought was out of the ordinary and so cannot  
reproduce your problem. (This was on a Mac, but OO.o is probably going  
to behave the same across *NIX cultures.)

-- 
David

>  If it insists on thinking for me, I wish it would think in 24 hour  
> time and 4 digit years :)

Is it possible that you have not done enough thinking for _it_?

> I work on Linux, so Excel is off the table, but another spreadsheet  
> or text editor would be a viable option, as would configuration  
> changes to Calc.
>
> Bill
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

David Winsemius, MD
West Hartford, CT