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.

$\endgroup$
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}} *)
$\endgroup$
2
  • 2
    $\begingroup$ DateList["2016/03/23,08:00"] also works (+1) $\endgroup$
    – kglr
    Mar 28 '16 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 '16 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}}

$\endgroup$
2
  • $\begingroup$ @meddy, my pleasure. Welcome to mma.se. $\endgroup$
    – kglr
    Mar 28 '16 at 2:17
  • $\begingroup$ @M.R, Thanks a lot for your answer. All answer is work for me. $\endgroup$
    – meddy
    Mar 28 '16 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}} *)
$\endgroup$
2
  • $\begingroup$ Thanks for your answer. $\endgroup$
    – meddy
    Mar 31 '16 at 0:19
  • $\begingroup$ No worries! Welcome to the group! $\endgroup$
    – Aisamu
    Mar 31 '16 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}}

$\endgroup$
1
  • $\begingroup$ Thanks a lot. all answers work for me. $\endgroup$
    – meddy
    Mar 31 '16 at 0:20

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.