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(RIORS for ON=A+RIORS 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.