Get Max Min Ave every hours

[#1]

Hi,

Attached is an example of raw data I am getting every day.

I would like to create a new table with the Max, Min and Average every hours for each columns.

Thanks for your help.

[#2]

One way is to define some auxiliary formula columns, then make the summary table. To see this, do 'File > New > New Script', cut and paste the code below, then do 'Edit > Run Script':

NamesDefaultToHere(1);

dt = Data Table( "Test.jmp");

// Add some formula columns

dt << New Column( "Year",

Numeric,

"Continuous",

Format( "Best", 12 ),

Formula( Year( :Time ) )

);

dt << New Column( "Day of Year",

Numeric,

"Continuous",

Format( "Best", 12 ),

Formula( Day Of Year( :Time ) )

);

dt << New Column( "Hour",

Numeric,

"Continuous",

Format( "Best", 12 ),

Formula( Hour( :Time ) )

);

// Make the summary table

dt2 = dt << Summary(

Group( :Year, :Day of Year, :Hour ),

Mean( :A ),

Mean( :B ),

Mean( :C ),

Mean( :D ),

Mean( :E ),

Freq( "None" ),

Weight( "None" )

);

(Depending on the scope of your data, you might not need the 'Year' column, but it's better to be safe).

[#3 · Accepted solution]

Yes, Summary() is the platform I would use. If you for some reason don't want to add columns to the original table, the platform supports custom transformed formulas as a grouping variable. Here's an jsl example:

Data Table("Test.jmp") << Summary(

    Group(

        Transform Column(

            "Time (whole hours)",

            Format("Locale Date Time h:m", 19),

            Formula(Floor(:Time / 3600) * 3600)

        )

    ),

    Mean(:A),

    Mean(:B),

    Mean(:C),

    Mean(:D),

    Mean(:E),

    Min(:A),

    Min(:B),

    Min(:C),

    Min(:D),

    Min(:E),

    Max(:A),

    Max(:B),

    Max(:C),

    Max(:D),

    Max(:E),

);

[#4]

t is exatly what I need !

thanks very much for the quick answer !!