# import and compare values in csv files according to a condition

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

• What is nominalPILC120? – Rohit Namjoshi Feb 19 '20 at 17:44
• Just a nominal current value which is related to system protection device. – Waco Feb 25 '20 at 8:49

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 = {

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|> *)

• Let´s say I have 10 csvs where I have values (it is actually el. loading and it is daily load diagrram for 3 month) for 3 months and I get them every day every 10s. Values for different days can differ quite a lot sometimes and sometimes can be even zero for certain period of time. However, the system must be dimensioned for the worst scenario, therefore it must be the day with highest loading (in 3 moth period) in the most crucial time which is between 10h and 17h. Once the program finds this "worst" day in the csv according to 10-17h condition, the next step should be – Waco Feb 20 '20 at 8:56
• to find average value in this whole day, thus from 0-24h. And this process should be make for all csvs in folder. Then I will have a list with average values in % for each item (csv). In the end, these average values will be sorted in group by 5% and counted, e.g. 3 items are loaded on 50%, 2 items on 45%, 2 on 30%, 1 on 20%, 2 on 10% and the plot will show dependence between the average loading and number of items in %. This plot shows you, if the system is overloaded or not. – Waco Feb 20 '20 at 8:56
• If you ask why there should be an average value for whole day (0-24h) and not the condition value (between 10-17h), it is because of time heat constant because the item can be loaded on much lower loading in time between 0 to 10h and 17 to 24h compared to 10-17h time. I also think it can better reflect the fact that the item could be turned off the previous day or on a relatively low loading. – Waco Feb 20 '20 at 8:57
• Or maybe, there can be two lists one with values between 0-17h andthe other with 0-24h interval and compare them and if they don't differ much, the program could also use 0-17h values or maybe just print both to see difference. – Waco Feb 20 '20 at 8:57
• Thanks for the explanation. The way I would approach this is to read/process each file and build an association where the top level keys are the file names, second level keys are data and computed metrics (mean for each day, day with highest mean 0-24, highest mean 10-17, etc.) for that file. When the data is structured in this form, it will be a lot easier to analyze and generate plots. If I have time later today I will update my answer with some code. – Rohit Namjoshi Feb 20 '20 at 18:52