script process of sorting (with multiple column data) and averaging (certain other column data)

[iyjang]

Hello,

I had a lot of help dealing with my bus log data, previously.

Now my interest is to sort them according to some column values, and then get average result of them.

Since DIAdem does not seem to support sorting function after data loaded, it seems to be a difficult issue for me;

it would be helpful if I can get any related clue/approach/function/advice of making this kind of script.

I attached an excel file for example. 

In the first 'raw' sheet, there are lines of raw data with column names of "year, month, day, ID, ID2, point, condition".

Actual data had a lot more columns, but I simplified it for convenience.

What I'm trying to do is, with input (from DialogBox or simply variable of script as a beginning; I can do this) of "year" and "month" (e.g.: 2015, 6),

to get the resultant data as shown in 'expected result' sheet.

The result is sorted by 'ID' and 'condition' value. 'Condition' is 0 or 1, so the average of 'point' values for each ID and condition is obtained. 

Group for 'condition' of 2 will be the average result of 'condition' 0 and 1 (whole data). (So there're always three groups: for 'condition' 0, 1, and 2.)

Currently, 'ID' includes 12345 and 54321, but there can be more values, also.

'Num' column is the number of raw data used to get the average for corresponding 'year-month-ID-condition' set.

Is there any efficient approach or function you recommend?

I think you may need more details for example data, so please feel free to ask me again.

Thanks in advance.

Inyoung

[AndreasK · Accepted solution]

The interactive part to modify data in DIAdem is collected in the "ANALYSIS" tab aon the left.

The second entry "Channel Functions" contains the "Sort Channel Values" method.

To automate this it is possible to use the macro recorder in tge script module.

[Brad_Turpin · Accepted solution]

Hi Inyoung,

I created a VBScript that sorts and averages your data as you indicated, but I used the CSV file you sent earlier, which I loaded with the "TenergyBusLogData" I posted for you previously.  Please load the "Sample_InyoungJang_CSV_file_load.csv" data file using the "TenergyBusLogData" DataPlugin, then run the attached VBScript.  You need to have the attached VBS and TDV files in the same folder on your computer, then you need to run the VBS file in DIAdem SCRIPT.

You can use other data files, but please test first with the "Sample_InyoungJang_CSV_file_load.csv" data file, which I used for testing.  The average values are listed in the table header above each column, along with the number of values used to calculate the averages.  The actual values used to calculate the averages are listed in the columns of each VIEW table.

If you want to change the order of the Groups, and thus change the order of the VIEW tabs, you can change the order of the channels used to sort in lines 19-21 of the VBScript.

Brad Turpin

DIAdem Product Support Engineer

National Instruments

[iyjang]

Thanks, Andreas and Brad!

I can use your attached VBScript with previous files, and it seems working.

I have one more question regarding this file: how can I access to 'average' header?

I mean, what should I do if I want to represent those average values to tables or graphs in REPORT panel?

[iyjang]

I think I found it; Function - ChnPropGet

Thanks.

Inyoung

[AndreasK]

You can always open DIAdem Script and drag the property from the data portal to the script.

This will give you the line to read/write the property.