1
$\begingroup$

The CSV file linked below has 3 columns: "PT_ID" (char 4), "PHYS_ID" (char 2) and "VISIT_START_TIME" (varchar eg "0:00:15" hours minutes seconds)

"https://docs.google.com/spreadsheet/pub?key=0AqPfEWYQJhXSdGF4ck5pblVlaHB0bkE0MWhsOWZ6M2c&output=csv"

Import["https://docs.google.com/spreadsheet/pub?key=\
0AqPfEWYQJhXSdGF4ck5pblVlaHB0bkE0MWhsOWZ6M2c&output=csv", 
 "DateStringFormat" -> {"Hour", ":", "Minute", ":", "Second"}, 
 "Numeric" -> False]

Setting "Numeric" -> False will result in this output, so the hh:mm:ss is not matched

{Q001,01,0:12:15}

While setting "Numeric" -> True results in the following 2 seemingly unrelated problems:

{Q001,1,{2014,1,1}}

  1. "PHYS_ID" is cast to Integer
  2. the 3-element List contains Year, Month, Day rather than Hours, Minute, Second

Are there obvious solutions to both 1 and 2?

http://reference.wolfram.com/mathematica/ref/format/CSV.html isn't very usable.

EDIT (based on answers/comments):

If it is known a priori which elements (columns) should be handled (here column 3, rows >=2), then using "Numeric"->False but not using DateStringFormat, the data can be post-processed using:

MapAt[DateList[#][[4 ;;]] &, tmp, {2 ;;, 3}]

But the ideal is to have Import deal with it given the documented options in CSV.

DateStringFormat almost works. Used along with "Numeric"->True, it incorrectly returns the first 3 elements of DateList@"0:12:15" --> {2014, 1, 1, 0, 12, 15.} rather than the entire list. But if DateStringFormat is not used (and with "Numeric" -> True), then "0:12:15" is imported as-is (String).

$\endgroup$
5
  • $\begingroup$ So what is the output format you are seeking? $\endgroup$ Apr 29, 2014 at 22:10
  • $\begingroup$ My Internet connection isn't working properly right now; could you include the raw data in that remote file, please? $\endgroup$
    – Mr.Wizard
    Apr 30, 2014 at 0:06
  • $\begingroup$ @Mr.Wizard, since this is an Import problem, I'm not sure how to include the raw data in an inline format. What do you suggest? $\endgroup$ Apr 30, 2014 at 4:24
  • $\begingroup$ @MikeHoneychurch, the ideal state would be to not cast strings to integer (eg, preserve "01") but at the same time to parse the time, eg as {0,12,15} (hour, minute, second, as specified in "DateStringFormat") $\endgroup$ Apr 30, 2014 at 4:27
  • $\begingroup$ I assumed from output=csv that it is a text-based CSV file. If not disregard that request; I'll try again later, or hopefully someone will have already solved your problem. $\endgroup$
    – Mr.Wizard
    Apr 30, 2014 at 5:27

1 Answer 1

1
$\begingroup$

I think perhaps you've confused what DateStringFormat does. It provides a means to describe the input format - it is always then converted into DateList format unless other options of import are used that preclude conversion. It has nothing to do with the resulting imported "format".

That said, if I understand your question, what you want is to import, then massage the import:

data = Import["c:\\users\\rasher\\downloads\\importexample.csv", "Numeric" -> False];

massaged=MapAt[ToExpression[
   StringReplace[#, h__ ~~ ":" ~~ m__ ~~ ":" ~~ s__ :> 
                    "{" <> h <> "," <> m <> "," <> s <> "}"]] &, data, {2 ;;, 3}]

The result, in massaged, will be the first two fields, untouched in format, as strings, and the hour, minute, and seconds as the third field as a numeric list (which it appears is your desire). If you want the time as strings, just remove the "ToExpression" part of above and adjust format as desired.

$\endgroup$
2
  • $\begingroup$ I should have stated the intent was to avoid having to specify which parts (columns) to massage. Ideally, M should still interpret dates even with "Numeric"->False. $\endgroup$ Apr 30, 2014 at 16:52
  • $\begingroup$ DateStringFormat almost works. Used along with "Numeric"->True, it incorrelctly returns the first 3 elements of DateList@"0:12:15" --> {2014, 1, 1, 0, 12, 15.} rather than the entire list. But if DateStringFormat is not used (and with "Numeric" -> True), then "0:12:15" is imported as-is (String). Isn't this a correct use of DateStringFormat? $\endgroup$ Apr 30, 2014 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.