Quiet@Remove["Global`*"];
SetDirectory[NotebookDirectory[]];

data2019=Import["data-vse.xlsx",{"Data",5}];
data2020=Import["data-vse.xlsx",{"Data",6}];

time2019=data2019[[All,1]];
time2020=data2020[[All,1]];

polishData[data_,time_]:=Module[{withTime,dropTitle,deNULL,polishedData,startState,endState,deAnom,spotreby,title,mu,sigma,zScore,zScoreFilter,DataOMHeader,OMseries,timeDifference,rocniSpotreba},
withTime=Transpose[{time,data}];
title=Take[withTime][[1,2]];
dropTitle=Drop[withTime,{1,1}];
deNULL=DeleteCases[dropTitle/. ""->x,{_,x}];
startState=deNULL[[1,2]];
endState=deNULL[[-1,2]];
deAnom=DeleteCases[deNULL,{_,x_}/;x>endState||x<startState];
rocniSpotreba=endState-startState;
spotreby=Table[{title,Round[24*60*deAnom[[i,1]]],((deAnom[[i+1,2]]-deAnom[[i,2]])/(24*(deAnom[[i+1,1]]-deAnom[[i,1]])))},{i,1,Length[deAnom]-1}];
mu=Mean[spotreby[[All,3]]];
sigma=StandardDeviation[spotreby[[All,3]]];
zScore=Table[{title,spotreby[[i,2]],spotreby[[i,3]],If[sigma==0,Null,((spotreby[[i,3]]-mu)/sigma)],mu,sigma,rocniSpotreba},{i,1,Length[spotreby]}];
zScoreFilter=DeleteCases[zScore,{_,_,_,x_}/;x==Null]

];

NumOfPoints2019=Length[data2019[[1,All]]];
NumOfPoints2020=Length[data2020[[1,All]]];

tableMuSigma2019=Table[{polishData[data2019[[All,i]],time2019][[2,1]],polishData[data2019[[All,i]],time2019][[2,5]],polishData[data2019[[All,i]],time2019][[2,6]]},{i,2,NumOfPoints2019}];
tableMuSigma2020=Table[{polishData[data2020[[All,i]],time2020][[2,1]],polishData[data2020[[All,i]],time2020][[2,5]],polishData[data2020[[All,i]],time2020][[2,6]]},{i,2,NumOfPoints2020}];
Export["tableMuSigma2019.csv",tableMuSigma2019];
Export["tableMuSigma2020.csv",tableMuSigma2020];

test2019=polishData[data2019[[All,3]],time2019]
test2020=polishData[data2020[[All,3]],time2020]

Export["test2019.csv",test2019];
Export["test2020.csv",test2020];

tableOfData2020=Table[polishData[data2020[[All,i]],time2020],{i,2,NumOfPoints2020}];
tableOfData2019=Table[polishData[data2019[[All,i]],time2019],{i,2,NumOfPoints2019}];

DataOMHeader={"Identifikace","Cas", "Vykon","ZScore","Mu","Sigma"};
export2019=Prepend[Flatten[tableOfData2019,1],Flatten[DataOMHeader,2]];
export2020=Prepend[Flatten[tableOfData2020,1],Flatten[DataOMHeader,2]];

Export["export2019.csv",export2019];
Export["export2020.csv",export2020];

tableSpotreba2019=Table[{polishData[data2019[[All,i]],time2019][[2,1]],polishData[data2019[[All,i]],time2019][[2,7]]},{i,2,NumOfPoints2019}];

tableSpotreba2020=Table[{polishData[data2020[[All,i]],time2020][[2,1]],polishData[data2020[[All,i]],time2020][[2,7]]},{i,2,NumOfPoints2020}];

Export["tableSpotreba2019.csv",tableSpotreba2019];
Export["tableSpotreba2020.csv",tableSpotreba2020];

Quiet@Remove["Global`*"];
SetDirectory[NotebookDirectory[]];

registr1VT=Import["Spotreby-Analyza.xlsx",{"Data",1}];
registr2NT=Import["Spotreby-Analyza.xlsx",{"Data",2}];

time=registr1VT[[All,1]];
ident=Drop[registr1VT[[1,All]],1];
calcConsumpt[data_,time_]:=Module[{withTime,dropTitle,deNULL,sorted,dif,nonZeroDif,totalConsumpt,timePeriod,avgConsumptYear},
withTime=Transpose[{time,data}];
dropTitle=Drop[withTime,{1,1}];
deNULL=DeleteCases[dropTitle/. ""->x,{_,x}];
sorted=Sort[deNULL,#1[[1]]<#2[[1]]&];
dif=Differences[sorted];
nonZeroDif=Select[dif[[All,2]],#>0&];
totalConsumpt=Total[nonZeroDif];
timePeriod=deNULL[[-1,1]]-deNULL[[1,1]];
avgConsumptYear=(totalConsumpt/timePeriod)*365.25
]

makeADataPoint[OMnum_]:={ident[[OMnum-1]],calcConsumpt[registr1VT[[All,OMnum]],time],calcConsumpt[registr2NT[[All,OMnum]],time]};
dataSpotreby=Table[makeADataPoint[i],{i,2,Length[ident]}];
dataSHlavickou=Insert[dataSpotreby,{"OM ID", "VT Spotreba - rocni prumer","NT Spotreba - rocni prumer"},1];
Export["data o rocni spotrebe.csv",dataSHlavickou];