As a newbie to manipulating CSV files within PowerShell I need a foot up with the following problem.

stats.csv contains data for a time series of two objects A,B. I need to create a report referencing some values from this and also need to calculate new values from the data it contains.

stats.csv available here: stats.csv file · GitHub

The first value on each row is the time series (PT). There are two rows for each time event in the file, one for each object (ON) A and B. The second value for each row is the object (ON). As you can see if you scroll through the CSV, there will be two entries for each PT - one for ON A and another for ON B. Each ON has it’s own unique data for values for the PT for TT,RIO,TB,RS,WS,RT that need to be calculated or passed directly to a new CSV.

The following calculations are needed for the report (excuse the crudeness below):

$total_tt = sum TT where PT value is the same for ON=A And ON=B

e.g. for the same PT entries @ 02/06/2017 15:45

$total_tt would be 12804.49537

$total_tb = sum TB where PT value is the same for ON=A And ON=B

e.g. for the same PT entries @ 02/06/2017 15:45

$total_tb would be 180.226005

$avg_rp = (if $total_tt>0(sum RIO where same PT for ON=A and=B)/$total_tt)

e.g. for PT entries @ 02/06/2017 15:45

$avg_rp would be 53.81826021 (percentage)

$avg_rs = (if sum of RT from same PT value for ON=A And ON=B>0(RIO*RS for ON=A+RIO*RS where ON=B)/sum of RIO for A and B)

e.g. for PT entries @ 02/06/2017 15:45

$avg_rs would be 9.944376086

$avg_ws = within the same TS(if $total_tt-RIOA-RIOB>0((TTA-RIOA)*WSA+(TTB-RIOB)*WSB/(RIOA-RIOB)

e.g. for PT entries @ 02/06/2017 15:45

$avg_ws would be 17.33865279

$avg_rt = average RT where same PT value is observed for ON=A And ON=B

e.g. for PT entries @ 02/06/2017 15:45

$avg_rt would be 0.293554

Create a 1st output file: report_detail.csv with the following values (some newly calculated, some from the original csv):

PT,$total_tt,$total_tb,$avg_rp,$avg_rs,$avg_ws,$avg_rt,RT(ON:A from stats.csv),RT(ON:B from stats.csv)

Next, return the row numbers from report_detail.csv that contain the maximum and 95th percentile value for $total_tt and send them to report_headlines.csv with first row headers for the csv:

measurement,PT,$total_tt,$total_tb,$avg_rp,$avg_rs,$avg_ws,$avg_rt,RT(ON:A from original CSV),RT(ON:B from original CSV).

Any help is appreciated to get me started.