Solved: Re: Extracting yearly maxima

BjornM_Sweden · ‎Sep 18, 2023

I have 28 years of hourly measured data and I need to extract the maximum value of each 12 month period from the 1st of July to the last of June. The data is in an Excel file. The first column contains the data in the form of YMD e.g. 2023-09-18. The second column contains the hour e.g. 01:00:00. The third column contains the measured parameter e.g. 18.9.

Is there an "easy" way to extract the maximum of each 12 month period, using Mathcad Prime? I have the latest version.

Kind regards

Björn

Fred_Kohlhepp · ‎Sep 18, 2023

Might actually be easier in EXCEL.

If you have hourly data, then a year is 24 x 365 = 8760 entries except for the seven leap years that would have 8784.

You can divide the data set into 28 vectors (one for each year) then take the maximum of each vector. (You can also have minimum, average, and standard deviation . . .)

Note that if you format the date column in EXCEL from "date" to "number" before you move it into Mathcad it will be more tractable.

View solution in original post

Werner_E · ‎Sep 18, 2023

Can you provide a sample Excel file (thinned out heavily, of course 😉 so we can see the exact format, headers, etc.

You may try to read in the Excel file using READEXCEL() and then loop through the data, resetting the max so far if July pops up again.

I guess date and time (time seems to be irrelevant anyway) end up as a string in Prime, right?

BjornM_Sweden · ‎Sep 19, 2023

Thank you! I will provid an Excel file. Maybeit is still better to use Excel which Fred below suggest. I will try that.

Fred_Kohlhepp · ‎Sep 18, 2023

Might actually be easier in EXCEL.

If you have hourly data, then a year is 24 x 365 = 8760 entries except for the seven leap years that would have 8784.

You can divide the data set into 28 vectors (one for each year) then take the maximum of each vector. (You can also have minimum, average, and standard deviation . . .)

Note that if you format the date column in EXCEL from "date" to "number" before you move it into Mathcad it will be more tractable.

BjornM_Sweden · ‎Sep 19, 2023

Thank you! That seems to be a good idea. I will try that.

terryhendicott · ‎Sep 19, 2023

Hi,

I have not altered the Excel file. Reading it with READEXCEL() in Mathcad gets the Excel day number days since 1/1/1900 not a text date and the time as a fraction of the day multiply fraction by 24 to get hours etc

What is missing is a way of reading the time stamp in day number and fractions of a day and returning the text date and the time.

Converting an old C++ Georgian calendar date functions to Mathcad then to Prime this is no longer a problem.

You just have to change the day number from Excel to Georgian which is just an addition.

Cheers

Terry

BjornM_Sweden · ‎Sep 19, 2023

Thank you so much! I did not know that there are som many helpful people in this community. I did not expect help so soon, if any.