Iterate through CSV and create an array

Here is an approach you can use a combination of c# available to Powershell (which can be MUCH more efficient handling larger files and data).

The first component is you need some consistent logic to isolate the API category you want each URL to be assigned. From your screenshots, sometimes it seems you use last segment of the URL but others it is some path in the middle of the resource.

Here is just a quick approach where you pass in an array of categories, and if it can be matched to URI in any way, then that category is used. Otherwise, the URI stands as its own category. Please replace with whatever logic you want here.

function Get-ApiCategory {
    param([string[]] $Categories, [string] $Text)
    foreach ($c in $Categories) {
        if ($Text.IndexOf($c) -gt 0) {
            return $c
        }
    }
    return $Text # Not found
}

Then, here is a method that (1) reads the large CSV file row-by-row and uses basic parsing logic (since your source data seems simple enough) without loading the full file into memory, and then (2) exports a CSV file with summary data.

function Write-SummaryToFile {
    param([string[]] $Categories, [string] $InputFile, [string] $Output)

    # Parse the file line-by-line (optimize for memory)
    $result = @{}
    $lineNum = 0
    Write-Host $InputFile
    foreach ($line in [System.IO.File]::ReadLines($InputFile)) {
        if ($lineNum++ -lt 1) { continue } # Skip header
        $cols = $line.Split(',')
        $category = Get-ApiCategory $Categories $cols[0]
        $new = @{
            Category = $category
            Count = [int]$cols[1]
            AvgResponse = [double]$cols[2]
        }
        if ($result.ContainsKey($category)) {
            $weighted = $result[$category].AvgResponse * $result[$category].Count
            $result[$category].Count += $new.Count
            $result[$category].AvgResponse = ($weighted + $new.AvgResponse * $new.Count) / $result[$category].Count;
        } else {
            $result[$category] = $new
        }
    }

    # Output to file
    if (Test-Path $Output) { Remove-Item $Output }
    try {
        $stream = [System.IO.StreamWriter] $Output
        $stream.WriteLine('Scenario,Count,Avg_Response_Time')
        $result.Values | ForEach-Object { $stream.WriteLine([string]::Format("{0},{1},{2}", $_.Category, $_.Count, $_.AvgResponse.ToString("0.##"))) }
    }
    finally {
        $stream.Dispose()
    }

}

Then, you are able to call these methods in an example like this:

$categories = @('MoveRequestQueue', 'DeliveryDate')
Write-SummaryToFile $categories 'c:\dev\scratch\ps1\test.csv' 'C:\dev\scratch\ps1\Output.csv'