3
$\begingroup$

I have data in this form:

data={"00:00 08:15 8.25 21", "08:15 10:45 2.50 7", "10:45 12:00 1.25 20",
"12:00 22:00 10.00 1", "22:00 22:15 0.25 23", "22:15 00:00 1.75 5"}

I want to construct a table that consists of two columns. The first column would consist of time in format 'HH:MM:SS.SS'. The second column would consist of a category which would be the last number from each of the strings (i.e. 21, 7, 20, 1, 23, 5).

The way it would be calculated would be taking the first and second time from each string as start and end point and each row would represent 0.5 second.

So far example the first 5 rows would look like this:

Time Category
00:00:00.00 21
00:00:00.50 21
00:00:01.00 21
00:00:01.50 21
00:00:02.00 21

This would continue until "08:15:00.00" and then would continue with category "7" and so on.

Thanks in advance for any help or suggestions.

$\endgroup$
1
  • 1
    $\begingroup$ I cannot understand the question. $\endgroup$
    – Syed
    Feb 18, 2022 at 16:02

2 Answers 2

4
$\begingroup$
splitdata = StringSplit /@ data;

dateRanges = 100 Mod[Range[##, 1/2] & @@@ 
   Map[AbsoluteTime, 
     ReplaceAll[{a_, "00:00"} :> {a, "24:00"}] @ splitdata[[All, ;; 2]], {-1}], 
  24 60 60];

toHMSList = StringRiffle[
   Riffle[
     IntegerString[IntegerDigits[#, MixedRadix[{60, 60, 100}], 4], 10, 2], 
     {":", ":", "."}], 
  ""] &;

result = MapThread[Thread @* List, 
   {Map[Map @ toHMSList] @ dateRanges, splitdata[[All, -1]]}];

Length /@ result
{59401, 18001, 9001, 72001, 1801, 12601}

First 4 columns:

Column[result[[All, ;; UpTo[4]]]]

enter image description here

Grid[Prepend[
  Join @@ result[[All, ;; UpTo[4]]], 
  {"Time", "Category"}], 
 Dividers -> {{False, True}, {False, True, {False}}}]

enter image description here

Alternatively, we can use DateRange with desired shape for $DateStringFormat to get the same result:

splitdata = StringSplit /@ data;

dranges = Map[StringDrop[#, -1] &] @
   Block[{$DateStringFormat = {"Hour", ":", "Minute", ":", "SecondExact"}}, 
    Map[Apply[DateRange[##, {1/2, "Second"}] &] @* Map[DateString]] @ 
  ReplaceAll[{a_, "00:00"} :> {a, "24:00"}] @ splitdata[[All, ;; 2]]];

resultB = MapThread[Thread@*List, {dranges, splitdata[[All, -1]]}];

resultB == result
True

Update: A function that combines all steps:

ClearAll[toHMSList, reOrg]

toHMSList = StringRiffle[
   Riffle[IntegerString[IntegerDigits[#, MixedRadix[{60, 60, 100}], 4], 10, 2],
   {":", ":", "."}], ""] &;

reOrg[dat_] := Module[{sd = StringSplit /@ dat, dates, values, dr}, 
  dates = sd[[All, ;; 2]]; values = sd[[All, -1]]; 
  dr = 100 Mod[Range[##, 1/2] & @@@ 
      Map[AbsoluteTime, ReplaceAll[{a_, "00:00"} :> {a, "24:00"}]@dates, {-1}], 
    24 60 60];
  MapAt[toHMSList, MapThread[Thread@*List, {dr, values}], {All, All, 1}]]

Example:

reOrg[data] == result
$\endgroup$
10
  • $\begingroup$ Thanks for the great answer, it works well but I just have one problem still. I see for the category 5 data ("22:15 00:00 1.75 5") it is returning nothing whereas it should be 12600 points (i.e. 00:00 - 22:15 = 1.75hrs = 12600 points). I guess it's because there are two instances of "00:00" in the data so doesn't know which comes first. Is there a way to edit the code so it takes this into account? Thanks again $\endgroup$
    – wmv
    Feb 21, 2022 at 8:55
  • 1
    $\begingroup$ @wmv, please see the update. $\endgroup$
    – kglr
    Feb 21, 2022 at 14:39
  • $\begingroup$ I maybe worded it badly but that's now too many points for the category 5 data. The data rows for category 5 should be from 22:15 to 00:00 which equates to 1.75hrs of data which in turn equates to 12600 rows. The way i worded it before made it sound like it was from 00:00 to 22:15 which would equate to 22.25 hrs (160201 rows). Thanks again $\endgroup$
    – wmv
    Feb 21, 2022 at 14:53
  • 1
    $\begingroup$ @wmv, please see the new version. $\endgroup$
    – kglr
    Feb 21, 2022 at 15:11
  • 1
    $\begingroup$ @wmv, sorry; copy/pasted wrong code block. Try the new version. $\endgroup$
    – kglr
    Mar 1, 2022 at 16:07
5
$\begingroup$

Mind you that the list you're asking is large. There are 24*60*60*2 or 172800 half-seconds in a day.

data = {"00:00 08:15 8.25 21", "08:15 10:45 2.50 7", "10:45 12:00 1.25 20", "12:00 22:00 10.00 1", "22:00 22:15 0.25 23", "22:15 00:00 1.75 5"};
list2 = {}; (*lists will go in here*)
Do[
   a = StringSplit[data][[i]]; (*split the data points in each string*)
   b = ToExpression[StringSplit[a[[1 ;; 2]], ":"]]; (*separate HH and MM*)
   c = (60*#[[1]] + #[[2]] & /@ b); (*initial & final times, in minute*)
   l = c /. {m0_, m1_} -> (m1 - m0)*(60*2); (*number of 0.5s in-between*)
   list = {}; (*time point with category will go in here*)
   Do[
      x = 50*n; (*.SS*)
      y = c[[1]] + Floor[x/100/60]; (*MM*)
      z1 = {Floor[y/60], Mod[y, 60], Mod[Floor[x/100], 60], Mod[x, 100]}; (*time point*)
      z2 = IntegerString[z1, 10, 2]; (*time point in 2-digit format*)
      AppendTo[list,
               (*{time point string, category number}*)
               {z2[[1]] <> ":" <> z2[[2]] <> ":" <> z2[[3]] <> "." <> z2[[4]], Last@a}
      ];
   , {n, 0, l - 1}];
   AppendTo[list2, list];
, {i, 1, Length@data}]

To pick the list corresponding to the first data "00:00 08:15 8.25 21", just do list2[[1]] and so on. If you just want the list to be flat, do

list2 = Flatten[list2, 1];

enter image description here

The code excludes end data points like {08:15:00.00, 21}. If you want to include them as well, change the range of n to {n, 0, l}.

$\endgroup$
2
  • $\begingroup$ Thank you so much! that works perfectly $\endgroup$
    – wmv
    Feb 18, 2022 at 17:03
  • $\begingroup$ Thanks for the great answer, it works well but I just have one problem still. I see for the category 5 data ("22:15 00:00 1.75 5") it is returning nothing whereas it should be 12600 points (i.e. 00:00 - 22:15 = 1.75hrs = 12600 points). I guess it's because there are two instances of "00:00" in the data so doesn't know which comes first. Is there a way to edit the code so it takes this into account? Thanks again $\endgroup$
    – wmv
    Feb 21, 2022 at 13:48

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.