Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a problem to convert tide data with the form as follows.

2016/03/23,08:00,0.89,* 2016/03/23,09:00,1.41,* 2016/03/23,10:00,1.67,* 2016/03/23,11:00,1.44,* 2016/03/23,12:00,0.82,* 2016/03/23,13:00,0.27,* 2016/03/23,14:00,-0.20,* 2016/03/23,15:00,-0.51,*

But, if the data is long, so I must use the command "Import". Unfortunately, Usually, if I use "Import" command for CSV data, the Output format of those data is also change, for example becomes like this: In[342]:= input = Flatten[Import["C:\Data\tide data Ota 2016 maret.CSV"]] [enter image description here]1

My question is how to convert that format to become such form like this:

{{{2016, 3, 23, 8, 0, 0}, 0.89}, {{2016, 3, 23, 9, 0, 0}, 1.41}, {{2016, 3, 23, 10, 0, 0}, 1.67}, {{2016, 3, 23, 11, 0, 0}, 1.44}, {{2016, 3, 23, 12, 0, 0}, 0.82}, {{2016, 3, 23, 13, 0, 0}, 0.27}, {{2016, 3, 23, 14, 0, 0}, -0.20}, {{2016, 3, 23, 15, 0, 0}, -0.51}}

My purpose if this convert is success, I can use DateListPlot to draw tidal water level.

share|improve this question

Check out the documentation for DateString:

DateList[{"2016/03/23,08:00", {"Year", "/", "Month", "/", "Day", ",", 
   "Hour", ":", "Minute"}}]
(*=> {2016, 3, 23, 8, 0, 0.}*)

You don't need to specify the format out completely. So we can just parse the string like this:

input = "2016/03/23,08:00,0.89,* 2016/03/23,09:00,1.41,* \
2016/03/23,10:00,1.67,* 2016/03/23,11:00,1.44,* \
2016/03/23,12:00,0.82,* 2016/03/23,13:00,0.27,* \
2016/03/23,14:00,-0.20,* 2016/03/23,15:00,-0.51,*";

data = StringCases[input, Shortest[d__ ~~ "," ~~ n : NumberString ~~ ",*"] 
          :> {DateList@d, ToExpression@n}]

which yields

  (* {{{2016, 3, 23, 8, 0, 0.}, 0.89}, {{2016, 3, 23, 9, 0, 0.}, 1.41},
  {{2016, 3, 23, 10, 0, 0.}, 1.67}, {{2016, 3, 23, 11, 0, 0.}, 1.44},     
  {{2016, 3, 23, 12, 0, 0.}, 0.82}, {{2016, 3, 23, 13, 0, 0.}, 0.27},             
  {{2016, 3, 23, 14, 0, 0.}, -0.2}, {{2016, 3, 23, 15, 0, 0.}, -0.51}} *)
share|improve this answer
2  
DateList["2016/03/23,08:00"] also works (+1) – kglr Mar 28 at 1:59
    
Thanks for the answer, but I should convert not only the dates, but also the water level values: 0.89, 1.41, 1.67,1.44,0.82,0.27,-0.20 and -0.51 are water level. Therefore, the format should be like this {{2016, 3, 23, 8, 0, 0}, 0.89} – meddy Mar 28 at 2:09
inp = "2016/03/23,08:00,0.89,* 2016/03/23,09:00,1.41,* \
2016/03/23,10:00,1.67,* 2016/03/23,11:00,1.44,* \
2016/03/23,12:00,0.82,* 2016/03/23,13:00,0.27,* \
2016/03/23,14:00,-0.20,* 2016/03/23,15:00,-0.51,*";

{DateList[# <> "," <> #2], ToExpression@#3} & @@@ 
   (StringSplit[#, ","] & /@ StringSplit[inp, ",*"])

{{{2016, 3, 23, 8, 0, 0.}, 0.89}, {{2016, 3, 23, 9, 0, 0.}, 1.41}, {{2016, 3, 23, 10, 0, 0.}, 1.67}, {{2016, 3, 23, 11, 0, 0.}, 1.44}, {{2016, 3, 23, 12, 0, 0.}, 0.82}, {{2016, 3, 23, 13, 0, 0.}, 0.27}, {{2016, 3, 23, 14, 0, 0.}, -0.2}, {{2016, 3, 23, 15, 0, 0.}, -0.51}}

share|improve this answer
    
Thanks a lot for your answer – meddy Mar 28 at 2:16
    
@meddy, my pleasure. Welcome to mma.se. – kglr Mar 28 at 2:17
    
@M.R, Thanks a lot for your answer. All answer is work for me. – meddy Mar 28 at 2:47

You can also use a fully specified DatePattern.

From M.R.'s answer,

data = StringCases[input, 
  d : DatePattern[{"Year", "/", "Month", "/", "Day", ",", "Hour", ":", "Minute"}]
    ~~ "," ~~ n : NumberString ~~ ",*" :> {DateList@d, ToExpression@n}]

(* {{{2016,3,23,8,0,0.},0.89},{{2016,3,23,9,0,0.},1.41}, 
    {{2016,3,23,10,0,0.},1.67},{{2016,3,23,11,0,0.},1.44},
    {{2016,3,23,12,0,0.},0.82},{{2016,3,23,13,0,0.},0.27},
    {{2016,3,23,14,0,0.},-0.2},{{2016,3,23,15,0,0.},-0.51}} *)
share|improve this answer
    
Thanks for your answer. – meddy Mar 31 at 0:19
    
No worries! Welcome to the group! – Aisamu Mar 31 at 12:11
inp = "2016/03/23,08:00,0.89,* 2016/03/23,09:00,1.41,* \
2016/03/23,10:00,1.67,* 2016/03/23,11:00,1.44,* \
2016/03/23,12:00,0.82,* 2016/03/23,13:00,0.27,* \
2016/03/23,14:00,-0.20,* 2016/03/23,15:00,-0.51,*";

MapAt[Function[l, Sequence @@ l], #, 2] & /@ (TakeDrop[#, 5] & /@ 
   Partition[ToExpression@StringCases[inp, NumberString], 6])

{{{2016,3,23,8,0},0.89},{{2016,3,23,9,0},1.41},{{2016,3,23,10,0},1.67},{{2016,3,23,11,0},1.44},{{2016,3,23,12,0},0.82},{{2016,3,23,13,0},0.27},{{2016,3,23,14,0},-0.2},{{2016,3,23,15,0},-0.51}}

share|improve this answer
    
Thanks a lot. all answers work for me. – meddy Mar 31 at 0:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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