1
$\begingroup$

I have a Table generated by reading a set of Excel files using the code:

files = FileNames["*.xls", {"D:\\Dados\\"}]
data = Table[
  Import[files[[j]], {"Data", 1, Range[5, 133], {2, 4}}], {j, 1,
   Length[files]}]

But unfortunately, the program that generates the spreadsheets is writing the date and time in text format, and the result of the reading (data) is:

{{{"2021-08-01 05:47:47", 0.}, {"2021-08-01 05:52:47",
   0.}, {"2021-08-01 05:57:47", 26.3}, {"2021-08-01 06:02:47",
   48.3}, {"2021-08-01 06:07:48", 85.4}, {"2021-08-01 06:12:48",
   178.7}, {"2021-08-01 06:17:48", 260.9}, {"2021-08-01 06:22:49",
   309.1}, {"2021-08-01 06:27:49", 378.5}, {"2021-08-01 06:32:49",
   347.3}, ,...
....
..., {"2021-08-11 16:00:42", 929.7}, {"2021-08-11 16:05:43",
   862.2}, {"2021-08-11 16:10:43", 781.9}, {"2021-08-11 16:15:43",
   716.}, {"2021-08-11 16:20:44", 641.5}, {"2021-08-11 16:25:44",
   575.4}}}

I need to have this "date and time" column splited in two columns. One column containing the value of the day and another column containing the value of decimal hour.

Can someone help me?

Thank you in advance.

$\endgroup$

2 Answers 2

2
$\begingroup$
data = {{"2021-08-01 05:47:47", 0.}, {"2021-08-01 05:52:47", 0.}, 
   {"2021-08-01 05:57:47", 26.3}, {"2021-08-01 06:02:47", 48.3},
   {"2021-08-01 06:07:48", 85.4}, {"2021-08-01 06:12:48", 178.7},
   {"2021-08-01 06:17:48", 260.9}, {"2021-08-01 06:22:49", 309.1}, 
   {"2021-08-01 06:27:49", 378.5}, {"2021-08-01 06:32:49",  347.3}};

ClearAll[splitdates1]
splitdates1 = Map[Flatten] @* MapAt[StringSplit, {All, 1}];

splitdates1 @ data
 {{"2021-08-01", "05:47:47", 0.}, {"2021-08-01", "05:52:47", 0.},
  {"2021-08-01", "05:57:47", 26.3}, {"2021-08-01", "06:02:47", 48.3}, 
  {"2021-08-01", "06:07:48", 85.4}, {"2021-08-01", "06:12:48", 178.7},
  {"2021-08-01", "06:17:48", 260.9}, {"2021-08-01", "06:22:49", 309.1}, 
  {"2021-08-01", "06:27:49", 378.5}, {"2021-08-01", "06:32:49", 347.3}}
splitdates1 @ data // Grid[#, Dividers -> All] &

enter image description here

Alternatively, create DateObjects and TimeObjects from the first column elements:

ClearAll[splitdates2]
splitdates2 = Map[Flatten] @*
 MapAt[Apply[{DateObject@#, TimeObject@#2} &]@*StringSplit, {All, 1}];


splitdates2 @ data // Grid[#, Dividers -> All] &

enter image description here

We can also use properties of DateObjects to get the desired split:

ClearAll[splitdates3]
splitdates3 = Map[Flatten] @*
 MapAt[{DateObject@DateObject[#][{"Year", "Month", "Day"}], TimeObject @ #} &, {All, 1}];


splitdates3 @ data // Grid[#, Dividers -> All] &

same picture

$\endgroup$
0
$\begingroup$

Edit:

files = FileNames["*.xls", {"D:\\Dados\\"}]
data = First@Table[
  Import[files[[j]], {"Data", 1, Range[5, 133], {2, 4}}], {j, 1,
   Length[files]}];

data[[All, 1]]=StringSplit /@ data[[All, 1]];
data

Original Answer:

You can use StringSplit

data = {{"2021-08-01 05:47:47", 0.}, {"2021-08-01 05:52:47", 
    0.}, {"2021-08-01 05:57:47", 26.3}, {"2021-08-01 06:02:47", 
    48.3}, {"2021-08-01 06:07:48", 85.4}, {"2021-08-01 06:12:48", 
    178.7}, {"2021-08-01 06:17:48", 260.9}, {"2021-08-01 06:22:49", 
    309.1}, {"2021-08-01 06:27:49", 378.5}, {"2021-08-01 06:32:49", 
    347.3}};

StringSplit /@ data[[All, 1]]

{{"2021-08-01", "05:47:47"}, {"2021-08-01", "05:52:47"}, {"2021-08-01", "05:57:47"}, {"2021-08-01", "06:02:47"}, {"2021-08-01", "06:07:48"}, {"2021-08-01", "06:12:48"}, {"2021-08-01", "06:17:48"}, {"2021-08-01", "06:22:49"}, {"2021-08-01", "06:27:49"}, {"2021-08-01", "06:32:49"}}

$\endgroup$
2
  • $\begingroup$ This is very strange. When I show "date" on the notebook the result doesn't appear with quotes, like date = {{2021-08-01 05:47:47, 0.}, {2021-08-01 05:52:47, 0.},... When I copy and paste into this message the quotes are placed in the text like: date = {{"2021-08-01 05:47:47", 0.}, {"2021-08-01 05:52:47", 0.},... Your suggestion to use StringSplit results in the error: StringSplit::strse: String or list of strings expected at position 1 in StringSplit[{2021-08-01 05:47:47,0.}]. Also, your solution is removing the column of values. The result must contain 3 columns. $\endgroup$ Aug 12, 2021 at 12:34
  • $\begingroup$ That was just to give you an idea. See my edit. $\endgroup$ Aug 12, 2021 at 13:42

Your Answer

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

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