2
$\begingroup$

I have imported csv file which is a Daily Load Diagram consisting of 3 months (1st dec 2018 to 28th feb 2019) and the values of current are taken every 10 seconds.

cv = FileNames["M:\\RS3710-TS1948.csv"]
{DateObject@
      StringReplace[#1, 
       x : DigitCharacter .. ~~ "." ~~ y : DigitCharacter .. ~~ "." ~~
          z : DigitCharacter .. ~~ " " ~~ rest__ :> 
        z <> "/" <> y <> "/" <> x <> " " <> 
         rest], #2} & @@@ # & /@ (Import[#, "Table", 
      "FieldSeparators" -> ";"][[2 ;;, {-2, -1}]] & /@ cv)

Well, since I care mainly about peak time (11:00-16:00) I would like to find a day with the highest mean value in this peak time range and once found then just basically Plot this one day from 0:00 to 23:55. There might be days which have higher values outside peak time than the day with the highest peak time values but I only care as I said about the highest mean peak time value not the highest mean value for the whole day.

I don't know which condition to use to compare dates and times together nor how I should handle this. Could you help me with this problem? Thank you for for advices. Here is the link for csv file with Daily Load Diagram:

https://gofile.io/?c=OH21Sm

Update

The posted answer works nicely for the example file I provided. However, I also tried to import a different one which has step also 10 min but taken at different times like 59:59 but it says there is failure and "The window contains no values" (see screenshot). I just rewrote that part of the code TimeObject /@ {{10, 59, 59}, {15, 59, 59}}.

different time

An example dataset for this case can be found here: https://gofile.io/?c=OH21Sm

$\endgroup$
2
$\begingroup$

Here is one approach using TimeSeriesWindow, TimeObject and TakeLargestBy: (for the query syntax, see Dataset)

(* import the data *)
ds = Dataset@Import[
    "RS3710-TS1948.csv",
    "Table",
    "FieldSeparators" -> ";",
    HeaderLines -> 1 (* skip the first line *)
    ];

ds[(* get the two relevant columns *)
      All,
      -2 ;;
      ][
     All,
     {(* for the first column, *)
      1 -> ((* convert the string to a DateObject by splitting it, 
        reordering the parts and converting them to numbers*)
        DateObject[FromDigits /@ {#3, #2, #, ##4}] & @@
          StringSplit[#, "." | " " | ":"] &
        )
      }
     ][
    (* group the datapoints by day *)
    GroupBy[DateObject[#, "Day"] &@*First]
    ][
   All,
   TimeSeries,(* 
   convert the datasets for each day to a TimeSeries... *)
   {1 -> TimeObject} (* after converting the date to a TimeObject *)
   ][
  TakeLargestBy[(* find the day with the highest value of *)
   (* the mean of the value in the window specified *)
   Mean@Quiet[
      Check[(* 
       catch TimeSeriesWindow::tswndt messages and return {-∞} in those cases *)
       TimeSeriesWindow[#, TimeObject /@ {{11, 0, 0}, {16, 0, 0}}],
       {-∞},
       TimeSeriesWindow::tswndt
       ],
      TimeSeriesWindow::tswndt
      ] &,
      1
   ]
  ][
 First,(*get the first result *)
 DateListPlot (* and plot it *)
 ]

Note: Updated the code to handle days where no data are available in the relevant window. Essentially, we check for any TimeSeriesWindow::tswndt messages and return {-∞} in those cases, ensuring that Mean and TakeLargestBy work as intended. I have also exchanged MaximalBy with TakeLargestBy, which handles the -∞ correctly.

enter image description here

$\endgroup$
  • $\begingroup$ Thank you, I really appreciate it and it works very nice. However, I also tried to import a different one which has step also 10 min but taken at different times like 59:59 but it says there is failure and The window contains no values (see pic). I just rewrote that part of the code TimeObject /@ {{10, 59, 59}, {15, 59, 59}}. $\endgroup$ – Vocis Aug 9 at 12:28
  • $\begingroup$ Also like to file with different time gofile.io/?c=WPgPC5 $\endgroup$ – Vocis Aug 9 at 12:38
  • $\begingroup$ The issue with your new dataset is the first day which only contains a single point right before midnight. I have updated the code in the answer to handle those cases properly $\endgroup$ – Lukas Lang Aug 9 at 13:37

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.