4
$\begingroup$

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.

Improve this question
$\endgroup$

4 Answers 4

Reset to default
8
$\begingroup$

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}} *)
Improve this answer
$\endgroup$
2
  • 2
    $\begingroup$ DateList["2016/03/23,08:00"] also works (+1) $\endgroup$
    – kglr
    Mar 28, 2016 at 1:59
  • $\begingroup$ 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} $\endgroup$
    – meddy
    Mar 28, 2016 at 2:09
6
$\begingroup$ 
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}}

Improve this answer
$\endgroup$
2
  • $\begingroup$ @meddy, my pleasure. Welcome to mma.se. $\endgroup$
    – kglr
    Mar 28, 2016 at 2:17
  • $\begingroup$ @M.R, Thanks a lot for your answer. All answer is work for me. $\endgroup$
    – meddy
    Mar 28, 2016 at 2:47
2
$\begingroup$

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}} *)
Improve this answer
$\endgroup$
2
  • $\begingroup$ Thanks for your answer. $\endgroup$
    – meddy
    Mar 31, 2016 at 0:19
  • $\begingroup$ No worries! Welcome to the group! $\endgroup$
    – Aisamu
    Mar 31, 2016 at 12:11
1
$\begingroup$ 
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}}

Improve this answer
$\endgroup$
1
  • $\begingroup$ Thanks a lot. all answers work for me. $\endgroup$
    – meddy
    Mar 31, 2016 at 0:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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