getting longer term trends from T4 data

[Ian Miller.]

I'm playing around T4 data files and CSVPNG and although the results are great for detail for a single day one thing I would like to do is to have graphs for a longer period to look for trends. e.g plot CPU idle (or parhaps rolling average) for the whole week or month.

Before I start writing some T4 CSV file mangling code has already done this already?

[John Gillings]

Ian,
Yes it's already done, and yes, you already have it!

If you have a default installation of T4, there's an image in T4$SYS called T4$APRC (APpendReCord). Use T4$APRC.CLD to define the command. It's used to glue T4 data files together by column:

$ T4APRC file1,file2,... dest

will append file1,file2,... to an *existing* T4 destination file. (I found that a bit confusing when I first tried to use it - make a COPY of your "base" file then append to it).

If the CSV files have been copied around, you may need to SET FILE/ATTR=RFM:STMLF first.

Adding rows is much easier, it's just a basic APPEND operation. Depending on what
utility you use to view the data, you may need to drop the first 4 records of the appended files, and the graphs might look a bit weird if there are large discontinuities in the time samples.

I'm not aware of any utilities to do this. On 8.2 you could use the new features of the SEARCH command, PIPE and APPEND (left as an exercise).

[Ian Miller.]

John - you are saying append the daily csv files together (losing the header rows on the 2nd and subsequent files) then process the result?
In the header rows there does not appear to be an end datetime (there is a start) so I think it will work. I could use csvpng to process the file and plot the average or whatever.

To what new features of SEARCH in V8.2 are you refering?
(I'm doing this on V7.3-1 but I'm curious).

[Ian Miller.]

appending files without the header rows appears to work subject to a limit in csvpng of 50000 rows (so can't do a whole month but a week is ok).

[John Gillings]

Ian,

I haven't used CSVPNG much, so wasn't aware of a row count limit. I'm not sure if the start/end values in the header rows are used anywhere of consequence.

Maybe there's a need for a utility that can reduce the data for long term trends? First cut could simply drop every second row. Alternative would be to reduce a block of N rows into one (arithmetic mean, mode, median etc...). SMOP ;-)

SEARCH has a bunch of new qualifiers, for example "/SKIP" which allows you to skip the first N matches, "/LIMIT" to display only N matches and "/REMAINING" to request everything in the file after the first match. There's also support for wildcard matches (not quite up to regular expression flexibility, but not too bad). These can be used to slice up files. For example, if we know the month of the samples for T4 files, something like:

$ PIPE SEARCH input.csv -
"AUG-2004"/SKIP=1/REMAINING |
APPEND SYS$PIPE output.csv

should strip off the header (untested).

Something that might work in current versions of SORT might be:

$ PIPE SEARCH input.csv ",0," |
APPEND SYS$PIPE output.csv

This assumes that every data sample has at least one zero, but none of the header rows contain a zero.

Idea! You could use SEARCH to reduce your data by dropping rows... Just generate a CSV file with two columns of data, column headings "select1" and "select2". In the first column, have two values, say, "00" and "11" (I'm assuming none of the output files will have fields with value "00"). For dopping every second row, alternate the values. The second column is all single zeros, and fill the sample time column with a single date/time. Now use T4$APRC to add this data to the right hand end of your source data. Make sure there are more rows in the select file than your source data, I'm fairly sure T4$APRC will truncate the data to the shortest file. Then use SEARCH as above to select rows containing ",00,0". Change the pattern of "11" and "00" to adjust the selection of rows (drop 1 in 3 or 2 in 3 etc...)

Obviously it would be better to write a program to munge the files, but as a Q&D hack... :-)

[Ian Miller.]

I think I will have to write some T4 data file managling code - parhaps this is the incentive to get back to learning perl :-)

[Ray Price]

CSVPNG already has features for culling out selected rows, selected columns, and periodic rows from a T4 file.

Check out CSVPNG_tutorial.pdf, CSVPNG_1064.txt and examples.txt in your CSVPNG distribution.

You can sent a T4 file through CSVPNG and generate a new T4-like file that contains only selected paramters, or only selected time frames (0800 - 1600) or every nth record with averages of the n records that were skipped.

So, reducing the row count should be fairly easy.