import and compare values in csv files according to a condition

Question

I have lots of csv files in a folder. Each file contains a column with date, year and time and the second column values for each time. I would like to import all these files and for each file take a day with only the highest mean value from 1Ohr to 17hr and then basically count the average from all values from this day with the highest mean value dividing by nominal value and have a list of average values for each file eventually.

I started importing and selecting the last two columns (6=date,time;7=values) and all rows except for 1st and I had to also replace ; for , because of EU format:

PILC120=FileNames[All,"G:\\Stats"];
curPILC120=Import[#,"Table","FieldSeparator"->";"][[2;;,-2;;]]&/@PILC120;
nominalValue=200;

Then I tried to convert the 6th column with date and time from string to number and compare the highest mean value for each day:

selPeakVal=curPILC120[All,{1->(DateObject[FromDigits/@{#3,#2,#,##4}]&@@StringSplit[#,"." | " " | ":"]&)}][GroupBy[DateObject[#,"Day"]&@*First]][All,TimeSeries,{1->TimeObject}][MaximalBy[Mean@TimeSeriesWindow[#,TimeObject/@{{10,00,00},{17,00,00}}]&]][First];

Then I defined function for average value in %:

funcPILC120[x_]:=Round[Mean[x/nominalPILC120*100],5];

Applying the function:

avePILC120=funcPILC120/@selPeakVal;

But my code doesn't work and I don't know what is wrong nor what is missing. Can anybody help me, please?

Thank you,

Here is the link with csv files: https://gofile.io/?c=R4IUFG

Answer 1

Update - Add function to process a file and save computations in an Association

Any other required computations can be added to the function and the association.

processFile[file_] := 
  Module[{data, selectedData, dataByDate, dataValues, datePlot, 
    datePlotMax, datePlotMin, datePlotMean, combinedDatePlot},

   data = Import[file, "Table", "FieldSeparators" -> ";"][[2 ;;, -2 ;;]];

   selectedData = 
    data // Select[(h = ToExpression@StringTake[First@#, 12 ;; 13]; h >= 10 && h <= 17) &];

   dataByDate = 
    data //
       GroupBy[StringTake[First@#, 10] & -> Last] //
       Select[Length@# == 144 &] // (* Remove incomplete data *)
       KeyMap[DateObject[{#, {"Day", "Month", "Year"}}] &];

   dataValues = dataByDate // Values;

   datePlot = 
    dataValues // 
     ListPlot[#, DataRange -> {0, 24}, ImageSize -> Medium, 
       PlotTheme -> "Detailed", PlotLabels -> None] &;

   datePlotMax = 
    dataValues // Transpose // Map[Max] //
     ListPlot[#, DataRange -> {0, 24}, ImageSize -> Medium, 
       PlotTheme -> "Detailed", Joined -> True, PlotStyle -> Red, 
       PlotLabels -> "Max"] &;

   datePlotMin = 
    dataValues // Transpose // Map[Min] //
     ListPlot[#, DataRange -> {0, 24}, ImageSize -> Medium, 
       PlotTheme -> "Detailed", Joined -> True, PlotStyle -> Green, 
       PlotLabels -> "Min"] &;

   datePlotMean = 
    dataValues // Transpose // Map[Mean] //
     ListPlot[#, DataRange -> {0, 24}, ImageSize -> Medium, 
       PlotTheme -> "Detailed", Joined -> True, PlotStyle -> Blue, 
       PlotLabels -> "Mean"] &;

   combinedDatePlot = 
    Show[datePlotMax, datePlotMin, datePlotMean, datePlot, 
     PlotRange -> All, FrameLabel -> {"Hour", "Value"}];

   <|FileBaseName@FileNameTake@file -> <|"data" -> data, 
      "selectedData" -> selectedData, "dataByDate" -> dataByDate,
      "datePlot" -> datePlot, "datePlotMax" -> datePlotMax, 
      "datePlotMin" -> datePlotMin, 
      "combinedDatePlot" -> combinedDatePlot|>|>];

Process a list of files

files = {
   "~/Downloads/Stats/K01-17(ANK120).csv", 
   "~/Downloads/Stats/K70-37 (ANK120).csv",
   "~/Downloads/Stats/RS3710-TS1948 (ANK,120).csv", 
   "~/Downloads/Stats/RS3790-TS3775 (ANK,120).csv",
   "~/Downloads/Stats/RS8650-TS3660 (ANK,120).csv", 
   "~/Downloads/Stats/RS5520-TS8902 (ANK,120).csv"};

results = processFile /@ files // Association;

The plot of value vs. hour of day for all days in the corresponding file.

#["combinedDatePlot"] & /@ results // Normal //
  Partition[#, UpTo[2]] & //
  Grid[#, Alignment -> Right, Spacings -> {0, 2}] &

There is a lot of variation in the data values in each of these files as you mentioned in your comment. Hope the approach above will help you perform the analysis you need.

---

Not sure what you mean by "basically count the average from all values from this day with the highest mean value dividing by nominal value and have a list of average values for each file eventually". If you can clarify that, I can help. Meanwhile, here is a way to simplify part of your code and speed it up by avoiding DateObject and TimeSeries. It makes use of the fact that the date/time strings are uniformly formatted.

data = Import["~/Downloads/Stats/K01-17(ANK120).csv", "Table", "FieldSeparators" -> ";"][[2 ;;, -2 ;;]];

selectedData = data // Select[(h = ToExpression@StringTake[First@#, 12 ;; 13]; h >= 10 && h <= 17) &];

maxMeanDay = selectedData // GroupBy[StringTake[First@#, 10] & -> Last] // 
               Map[Mean] // ReverseSort // Take[#, 1] &

(* <|"02.12.2018" -> 54.3485|> *)