Use Powershell to perform summary and consolidation like awk

Question

I have an awk script that generates a summary of several columns based on specific rules. I need to achieve the same on a Windows server running PowerShell v4.

TxnStat|Station|Ccy|Fcy|Lcy|Date
NEW|BRANCH|USD|1000|10|20190410
NEW|ATM|GBP|100|25|20190410
NEW|ATM|GBP|50|10|20190410
NEW|BRANCH|GBP|200|47|20190410
NEW|BRANCH|USD|250|20|20190410

The idea is to group all records that share the same Station (field 2) and Currency (field 3) and then:

1. Calculate the sum of Fcy Amount for the group (field 4)
2. Calculate sum of LCY amount for the group (field 5)
3. Calculate field 5/field 4 (i.e. exch rate)
4. Get the average exch rate (exch/no. of items per group)

The result I'm looking for is this. I am stripping out the header:

NEW,BRANCH,USD,30.00,1250.00,0.02,0.01,20190410
NEW,ATM,GBP,35.00,150.00,0.23,0.12,20190410
NEW,BRANCH,GBP,47.00,200.00,0.23,0.23,20190410

I used the awk script below to achieve this:

tail -n+2 TEST.CSV | awk 'BEGIN{FS="|";OFS=","}
{fcy_tot[$2$3]+=$4;num_recs[$2$3]+=1;
lcy_tot[$2$3]+=$5;str_1[$2$3]=$1OFS$2OFS$3;
dt_str[$2$3]=$6;}
END
{for (i in fcy_tot)
{exch=lcy_tot[i]/fcy_tot[i];
avg_rate=exch/num_recs[i];
printf "%s %.2,%.2f,%.2f,%.2f,%.2f,%s\n",
str_1[i],lcy_tot[i],fcy_tot[i],exch,avg_rate,dt_str[i]}}'

It's a one-liner but I've broken it here for clarity.

What have I done so far?

I did some Googling and landed on the group-object function but I was only able to find the sum on one field using the measure -sum command. I need guidance on how to create custom calculations on multiple specific fields.

Answer 1

Given the input file is input.txt in the above format,

this script ( could also be formatted as a one liner) :

Import-Csv .\input.txt -Delim '|'|Group-Object Station,Ccy | ForEach-object {
    $Fcy,$Lcy = ($_.Group|Measure-Object Fcy,lcy -Sum).Sum
    $ExchRate = $Lcy / $Fcy
    $AvgExchR = $ExchRate / $_.Count
    [PSCustomObject]@{
        TxnStat = $_.Group[0].TxnStat
        Station = $_.Group[0].Station
        Ccy     = $_.Group[0].Ccy  
        Fcy     = $Fcy.ToString('0.00')
        Lcy     = $Lcy.ToString('0.00')
        ExchRate= $ExchRate.ToString('0.00')
        AvgExchR= $AvgExchR.ToString('0.00')
        Date    = $_.Group[0].Date
    }
} | Format-Table -AutoSize # Format-Object just to visualize

yields:

TxnStat Station Ccy Fcy      Lcy   ExchRate AvgExchR Date
------- ------- --- ---      ---   -------- -------- ----
NEW     BRANCH  USD 1250.00  30.00 0.02     0.01     20190410
NEW     ATM     GBP 150.00   35.00 0.23     0.12     20190410
NEW     BRANCH  GBP 200.00   47.00 0.24     0.24     20190410

Looks like you swapped Fcy,Lcy in you sample output.

To output as a csv without header replace the Format-Table with

| ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Set-Content output.csv

But Export-Csv/ConvertTo-Csv double quote all fields

"NEW","BRANCH","USD","1250.00","30.00","0.02","0.01","20190410"
"NEW","ATM","GBP","150.00","35.00","0.23","0.12","20190410"
"NEW","BRANCH","GBP","200.00","47.00","0.24","0.24","20190410"

That could also be dealt with, with a bit effort.

I didn't check explicitly if I used post PSv4 commands.

## Q:\Test\2019\04\10\SU_1423881.ps1

$Output = Import-Csv .\input.txt -Delim '|' | Group-Object Station,Ccy | ForEach-object {
    $Fcy,$Lcy = ($_.Group | Measure-Object Fcy,lcy -Sum).Sum
    $Prod = [Double] 0; ($_.Group|ForEach-Object{$Prod+=[Double]$_.Fcy*$_.Lcy})
    $ExchRate = $Lcy / $Fcy
    $AvgExchR = $ExchRate / $_.Count
    [PSCustomObject]@{
        TxnStat = $_.Group[0].TxnStat
        Station = $_.Group[0].Station
        Ccy     = $_.Group[0].Ccy
        Fcy     = $Fcy.ToString('0.00')
        Lcy     = $Lcy.ToString('0.00')
        ExchRate= $ExchRate.ToString('0.00')
        AvgExchR= $AvgExchR.ToString('0.00')
        AvgWeigh= ($Prod / $Fcy).ToString('0.00')
        Date    = $_.Group[0].Date
    }
}
$Output | Format-Table -AutoSize
#$Output | ConvertTo-Csv -Not | Select-Object -Skip 1 | Set-Content output.csv

Sample output:

> .\SU_1423881.ps1

TxnStat Station Ccy Fcy     Lcy   ExchRate AvgExchR AvgWeigh Date
------- ------- --- ---     ---   -------- -------- -------- ----
NEW     BRANCH  USD 1250,00 30,00 0,02     0,01     12,00    20190410
NEW     ATM     GBP 150,00  35,00 0,23     0,12     20,00    20190410
NEW     BRANCH  GBP 200,00  47,00 0,24     0,24     47,00    20190410