asked on

Assistance with powershell script

Trying this question again...

I have a stats app that I record a volleyball match onto. It allows me to send a raw report (gamenotes.txt) of what actions occurred within the game.
A volleyball set is won when one team gets to 25 first, but they must win by 2 points. A full match is best of 3 or best of 5, depending on the venue/format. This example is a report from the women's USA match vs Thailand. It is just the first set, however. But I think it's a good representation of what could be included.

Each line of the report will begin with the score (0-1) followed by a series of actions that occurred during that one rally. Each action will have the player's jersey number in parenthesis (unless it's an opponent action, then the 'jersey' will be "THEM").

Example:
(0-1), SR-3( 2), AT(13), BE(25),

A serve came from the opponent and was rated a '3' (SR = serve reception), recorded by player 2. Player 13 Attempted (AT) to terminate the rally but failed. Finally player 25 recorded a blocking error (BE).

It's fairly easy to read for the most part, but here's where it gets tricky. If an action is entered incorrectly, the app doesn't remove it, it records it with a preceding minus sign.

Example:
(9-12), S(10), -S(10), S(13), D(23), E(23),

The score is 9-12. #10 serves (S). OOPS - not #10...fix that. #13 was the server! #23 gets a recorded dig (D) and #23 ends the rally with a hitting error (E).

The down side is that the correction may not immediately follow the goof. :( So it may require working backwards until the exact same action/player combination is found.

I have most of the code written for what I'm doing so far. However, I ultimately want to evolve this into something helpful for reporting on the statistics. For example, If #13 at the end of the set/match has 20 attempts, kills 15 of those and only errors once, I would want to display her hitting % which is calculated by (K-E)/AT, or in this case 70%.

I'll just upload the report file for now in case someone wants to come up with their own solution for looping through the data. But I'll be glad to post my current script if desired.

sirbounty

ASKER

I will add - these are the actions that can occur in a rally:
S = Serve, SA = Service ACE, SE = Service Error
K = Kill, AT = Attempt, E = hitting error
BS = Solo block, BA = Block Assist, BE = Blocking error
D = Dig, DigErr = Digging error
BHE = Ball Handling Error
A = Assist
SR-0, SR-1, SR-2, SR-3 are degrees of a pass rating. 0 = lost point, 3 = perfect

Dustin Saunders

I think the challenge here is not so much the coding, and more understanding what needs to be coded (I know nothing about volleyball other than trying to date the players back in high school).

SO.... if there is an error, if it does not immediately follow 'the goof' does it at least appear in the same line each time?

Is a 'kill' a score? And an attempt a try at scoring or just a ball passing over the net?

Dustin Saunders

And, can you post the current script? That would maybe help explain more what needs to be done.

Dustin Saunders

Ok how about this approach-- break apart your game.txt into a dataTable where you can perform select operations. Then you can select any row of data and count by action and player #, and then subtract any actions that start with "-" and match the player number.

Once you have the info in tables, you can select whatever criteria you want, dump to csv, SQL, blah blah.

i.e.

$file = "D:\Profile Data\Dustin\Desktop\game1.txt"
$game = Get-Content $file

$rallyTables = @{}

function MakeActionTable
{
    $dt = New-Object System.Data.DataTable
    $c1 = New-Object System.Data.DataColumn 'player',([string])
    $c2 = New-Object System.Data.DataColumn 'action',([string])
    $c3 = New-Object System.Data.DataColumn 'score1',([int])
    $c4 = New-Object System.Data.DataColumn 'score2',([int])
    $dt.Columns.Add($c1)
    $dt.Columns.Add($c2)
    $dt.Columns.Add($c3)
    $dt.Columns.Add($c4)
    return, $dt
}

function AddActRow($dt, $pla, $act, $s1, $s2)
{
    $row = $dt.NewRow()
    $row.action = $act
    $row.player = $pla
    $row.score1 = $s1
    $row.score2 = $s2
    $dt.Rows.Add($row)
}

$gameTable = MakeActionTable

$rallyNo = 1

foreach ($rally in $game)
{
    $scoreString = $rally.Substring(0,$rally.IndexOf(',')).Replace('(','').Replace(')','').Split('-')
    $s1 = $scoreString[0]
    $s2 = $scoreString[1]
    $plays = $rally.Substring($rally.IndexOf(", "))
    $plays = $plays.Substring(0,$plays.LastIndexOf(","))
    $actions = $plays.Split(',')
    $rallyTables[$rallyNo] = MakeActionTable
    foreach ($action in $actions)
    {
        if ($action)
        {
            $thisAction = $action.Split('(').Replace(')','').Trim()
            AddActRow $rallyTables[$rallyNo] $thisAction[1] $thisAction[0] $s1 $s2
        }
    }
    $gameTable.Merge($rallyTables[$rallyNo])
    $rallyNo++
}

foreach ($row in $gameTable.Rows)
{
    Write-Host $row.player"|"$row.action"-"$row.score1":"$row.score2
}

Open in new window

Just an example to convert your game to tables that you can select from. The output shows the actions in order and the score of the game at the time of the action (as an example).

As a side note, I put each rally into a separate table collected in a hash table, so you could go back for each $game and select from any particular rally using $rallyTable[$I] where i is the turn you want to single out. (of course, we could write rally# into the tables too, or search by score at the time, blah blah)

Dustin Saunders

Sorry to bombard you with posts, I like interesting problems. But here is code at the bottom to do the select. This will now break down the game into how many times each unique player did each unique action. You can use this method for your stats as illustrate in the example code. For instance, player 2 had 2 DigErr but 1 rolled back. The output shows 2 -1 error count for a total of DigErr 1 time.

$file = "D:\Profile Data\Dustin\Desktop\game1.txt"
$game = Get-Content $file

$rallyTables = @{}

function MakeActionTable
{
    $dt = New-Object System.Data.DataTable
    $c1 = New-Object System.Data.DataColumn 'player',([string])
    $c2 = New-Object System.Data.DataColumn 'action',([string])
    $c3 = New-Object System.Data.DataColumn 'score1',([int])
    $c4 = New-Object System.Data.DataColumn 'score2',([int])
    $dt.Columns.Add($c1)
    $dt.Columns.Add($c2)
    $dt.Columns.Add($c3)
    $dt.Columns.Add($c4)
    return, $dt
}

function AddActRow($dt, $pla, $act, $s1, $s2)
{
    $row = $dt.NewRow()
    $row.action = $act
    $row.player = $pla
    $row.score1 = $s1
    $row.score2 = $s2
    $dt.Rows.Add($row)
}

$gameTable = MakeActionTable

$rallyNo = 1

foreach ($rally in $game)
{
    $scoreString = $rally.Substring(0,$rally.IndexOf(',')).Replace('(','').Replace(')','').Split('-')
    $s1 = $scoreString[0]
    $s2 = $scoreString[1]
    $plays = $rally.Substring($rally.IndexOf(", "))
    $plays = $plays.Substring(0,$plays.LastIndexOf(","))
    $actions = $plays.Split(',')
    $rallyTables[$rallyNo] = MakeActionTable
    foreach ($action in $actions)
    {
        if ($action)
        {
            $thisAction = $action.Split('(').Replace(')','').Trim()
            AddActRow $rallyTables[$rallyNo] $thisAction[1] $thisAction[0] $s1 $s2
        }
    }
    $gameTable.Merge($rallyTables[$rallyNo])
    $rallyNo++
}

#calculate actions
$ps = $gameTable.DefaultView.ToTable($true, "player")
$as = $gameTable.DefaultView.ToTable($true, "action")
foreach ($p in $ps)
{
    foreach ($a in $as)
    {
        if ($a.action.Substring(0,1) -ne "-")
        {
            $count = $gameTable.Select("action = '$($a.action)' AND player='$($p.player)'").Length
            $ncount = $gameTable.Select("action = '-$($a.action)' AND player='$($p.player)'").Length
            $adjCount = $count - $ncount
            if ($adjCount -gt 0)
            {
                Write-Host "+"$count "-"$ncount
                Write-Host "Player "$p.player" did action "$a.action" "$adjCount" times."
            }
        }
    }
}

Open in new window

+ 2 - 1
Player  2  did action  DigErr   1 times.

Open in new window

sirbounty

ASKER

To answer your questions above, yes a kill is a score (error is for the other team), as is a service ace (service error is for the opponent). An attempt is a try - the player hit the ball but it was eventually returned back over the net. A block assist is a single point spread among 2 players (in high level teams, it could even be among 3 players)

As for the error in the report, not unfortunately the goof may not appear on the same line, but it shouldn't be too far below it - most common is the same line, or the very next line.

I'll check through the code submissions - did you still want to see my version? I did go the datatable route, but that might be a better approach since I want to eventually pull this into excel.

Thanks!

SOLUTION

Dustin Saunders

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sirbounty

ASKER

I know there's a lot I can do with a datatable, but I'm not sure of the best approach to simply remove those goofs, with the end result being a chronological display of each rally in order?
I see that you're doing it by player. Do I need to loop through it once that way first, removing the erroneous data and then come back through?

Output display I'm hoping for would be something like
Player 3 served an ace. Score is 1-0
Player 7 had a reception error (SR-0). Score is 1-1
Player 4 had a great/good/okay (SR-3,-2,-1) reception. Player 5 had an assist. Player 2 had a kill. Score is 2-1
etc.

With a per-set total displaying per player percentages (a set is played to 25 points, but you must win by 2). I.e.
Serve %: Player1 - 80%, Player2 - 45%, etc.
Hitting %: Player 1 - 42%, Player2 - 38%, etc.
I can do the calculations I think, if I can figure out how to extract the totals.

Dustin Saunders

See this example:

$file = "C:\test\game1.txt"
$game = Get-Content $file

$rallyTables = @{}

function MakeActionTable
{
    $dt = New-Object System.Data.DataTable
    $c1 = New-Object System.Data.DataColumn 'player',([string])
    $c2 = New-Object System.Data.DataColumn 'action',([string])
    $c3 = New-Object System.Data.DataColumn 'score1',([int])
    $c4 = New-Object System.Data.DataColumn 'score2',([int])
    $dt.Columns.Add($c1)
    $dt.Columns.Add($c2)
    $dt.Columns.Add($c3)
    $dt.Columns.Add($c4)
    return, $dt
}

function AddActRow($dt, $pla, $act, $s1, $s2)
{
    $row = $dt.NewRow()
    $row.action = $act
    $row.player = $pla
    $row.score1 = $s1
    $row.score2 = $s2
    $dt.Rows.Add($row)
}

function ReportAction($1, $2, $type)
{
    
    if ($type -eq "play")
    {$string = "Player " + $1 + " had action " + $2 + ".  " }
    else {$string = "Score is " + $s1 + "-" + $s2 + "."}
    return $string
}

$gameTable = MakeActionTable

$rallyNo = 1

foreach ($rally in $game)
{
    $report = ""
    $scoreString = $rally.Substring(0,$rally.IndexOf(',')).Replace('(','').Replace(')','').Split('-')
    $s1 = $scoreString[0]
    $s2 = $scoreString[1]
    $plays = $rally.Substring($rally.IndexOf(", "))
    $plays = $plays.Substring(0,$plays.LastIndexOf(","))
    $actions = $plays.Split(',')
    $rallyTables[$rallyNo] = MakeActionTable
    foreach ($action in $actions)
    {
        if ($action)
        {
            $thisAction = $action.Split('(').Replace(')','').Trim()
            AddActRow $rallyTables[$rallyNo] $thisAction[1] $thisAction[0] $s1 $s2
            $report = $report + (ReportAction $thisAction[1] $thisAction[0] "play")
        }
    }
    $report = $report + (ReportAction $s1 $s2 "score")
    $gameTable.Merge($rallyTables[$rallyNo])
    $rallyNo++
    Write-Host $report
}

<#calculate actions
$ps = $gameTable.DefaultView.ToTable($true, "player")
$as = $gameTable.DefaultView.ToTable($true, "action")
foreach ($p in $ps)
{
    foreach ($a in $as)
    {
        if ($a.action.Substring(0,1) -ne "-")
        {
            $count = $gameTable.Select("action = '$($a.action)' AND player='$($p.player)'").Length
            $ncount = $gameTable.Select("action = '-$($a.action)' AND player='$($p.player)'").Length
            $adjCount = $count - $ncount
            if ($adjCount -gt 0)
            {
                Write-Host "+"$count "-"$ncount
                Write-Host "Player "$p.player" did action "$a.action" "$adjCount" times."
            }
        }
    }
} #>

Open in new window

So I'm writing the verbose actions as they occur in the format you have above. You would just do a replace or a join select to get the action that corresponds to the letter (I just left the letter as the action but you get the idea.) This code produces:

Player 2 had action SR-3.  Player 13 had action AT.  Player 25 had action BE.  Score is  0-1.
Player THEM had action SE.  Score is  1-1.
Player 13 had action S.  Player 25 had action AT.  Player 13 had action D.  Player 23 had action AT.  Player 5 had action BE.  Score is  1-2.
Player 10 had action SR-3.  Player 5 had action K.  Score is  2-2.
Player 25 had action S.  Player THEM had action K.  Score is  2-3.
Player 23 had action SR-1.  Player 23 had action E.  Score is  2-4.
Player 2 had action SR-3.  Player 5 had action AT.  Player 2 had action D.  Player 23 had action AT.  Player 10 had action D.  Player 6 had action A.  Player 25 had ac
tion K.  Score is  3-4.
Player 23 had action D.  Player 2 had action DigErr.  Score is  3-5.
Player 10 had action SR-1.  Player 6 had action A.  Player 10 had action K.  Score is  4-5.
Player 5 had action SE.  Score is  4-6.
Player 23 had action SR-1.  Player 13 had action E.  Score is  4-7.
Player 10 had action SR-3.  Player 6 had action A.  Player 13 had action K.  Score is  5-7.
Player 6 had action S.  Player THEM had action K.  Score is  5-8.
Player THEM had action SA.  Score is  5-9.
Player 10 had action SR-3.  Player 10 had action E.  Score is  5-10.
Player 2 had action SR-3.  Player 13 had action AT.  Player 13 had action BA.  Player 10 had action BA.  Score is  6-10.
Player 10 had action S.  Player 23 had action D.  Player 6 had action A.  Player 25 had action K.  Score is  7-10.
Player 10 had action S.  Player 2 had action D.  Player 6 had action A.  Player 25 had action K.  Score is  8-10.
Player 10 had action S.  Player 23 had action D.  Player 23 had action E.  Score is  8-11.
Player 23 had action SR-3.  Player 6 had action A.  Player 25 had action K.  Score is  9-11.
Player 10 had action S.  Player 10 had action -S.  Player 13 had action S.  Player 23 had action D.  Player 23 had action E.  Score is  9-12.
Player THEM had action SE.  Score is  10-12.
Player 1 had action S.  Player THEM had action E.  Score is  11-12.
Player 1 had action SE.  Score is  11-13.
Player 2 had action SR-3.  Player 1 had action A.  Player 23 had action K.  Score is  12-13.
Player 23 had action S.  Player 2 had action DigErr.  Score is  12-14.
Player 2 had action -DigErr.  Score is  12-13.
Player 2 had action D.  Player 1 had action D.  Player 23 had action A.  Player 12 had action K.  Score is  13-13.
Player 23 had action S.  Player 10 had action AT.  Player 10 had action D.  Player THEM had action BS.  Score is  13-14.
Player 10 had action SR-3.  Player 10 had action AT.  Player 5 had action AT.  Player 1 had action D.  Player THEM had action BS.  Score is  13-15.
Player 10 had action SR-3.  Player 1 had action A.  Player 23 had action K.  Score is  14-15.
Player 5 had action S.  Player 23 had action D.  Player 1 had action BHE.  Score is  14-16.
Player THEM had action SE.  Score is  15-16.
Player 6 had action S.  Player THEM had action DigErr.  Score is  16-16.
Player 6 had action S.  Player 25 had action BA.  Player 13 had action BA.  Score is  17-16.
Player 6 had action S.  Player THEM had action K.  Score is  17-17.
Player 10 had action SR-2.  Player 25 had action AT.  Player 25 had action BE.  Score is  17-18.
Player 10 had action SR-1.  Player 25 had action AT.  Player 13 had action BE.  Score is  17-19.
Player 23 had action SR-3.  Player 25 had action AT.  Player 2 had action D.  Player 13 had action AT.  Player 13 had action BA.  Player 10 had action BA.  Score is  1
8-19.
Player 10 had action S.  Player 2 had action D.  Player 23 had action AT.  Player 6 had action D.  Player 25 had action AT.  Player 6 had action A.  Player 25 had acti
on K.  Score is  19-19.
Player 10 had action S.  Player 13 had action AT.  Player 2 had action D.  Player 2 had action D.  Player 6 had action D.  Player 23 had action AT.  Player 6 had actio
n A.  Player 25 had action K.  Score is  20-19.
Player 10 had action S.  Player 6 had action D.  Player 25 had action AT.  Player 6 had action D.  Player 25 had action AT.  Player THEM had action E.  Score is  21-19
.
Player 10 had action S.  Player 10 had action -S.  Player 10 had action S.  Score is  22-19.
Player 10 had action S.  Score is  23-19.
Player 10 had action S.  Player THEM had action K.  Score is  23-20.
Player THEM had action SE.  Score is  24-20.
Player 3 had action S.  Player 3 had action D.  Player 23 had action E.  Score is  24-21.
Player 23 had action SR-3.  Player 6 had action A.  Player 25 had action K.  Score is  25-21.

Open in new window

You can go back and do the play by play as needed as well since we retained each rally in a separate table, we can call by $rallyTables[$rallyNo] where $rallyNo is the turn or rally we want to inspect.

In the datatable you can manipulate it however you want, join with whatever, export in whatever format, etc.

sirbounty

ASKER

I've modified the reportaction function with the setup I had in one of my functions to display the action name instead of the code.

So, it hits the default when it's a goof ( -S in one example). Presumably I won't need default once this is complete.
Once the rallytable has been built, I can find that entry by using
$rallyTables[21].Select("action like '-%'")

So, I suspect, I can have a final clean-up routine that iterates each rallytable and performs a query where each field matches and the action field matches with a prefix of "-". Does that seem like a good approach for this?

I love what you've produced here. I'm looking forward to building on it. And you have really gotten me using datatables a lot more over the past few weeks. Thanks! :^)

function ReportAction($1, $2, $type) {
    if ($type -eq "play") {
        switch ($2) {
            'S'  {$play = 'a serve';break}
            'SA' {$play = 'an ACE serve';break}
            'SE' {$play = 'a service error';break}
            'AT' {$play = 'an attack attempt';break}
            'K'  {$play = 'a Kill';break}
            'BS' {$play = 'a solo block';break}
            'BA' {$play = 'a block assist';break}
            'BE' {$play = 'a block error';break}
            'A'  {$play = 'an assist';break}
            'D'  {$play = 'a dig';break}
            'E'  {$play = 'an attack error';break}
            'DigErr' {$play = 'a dig error';break}
            'BHE'    {$play = 'a ball handling error';break}
            {$PSItem -like 'SR-?'} {
                $Rating=$2.Split('-')[1]
                $play = "a pass rating of $rating"
                break
            }
            default {
                ''
            }
        } #end switch
        $string = ('{0} recorded {1}' -f $1, $play)
    } else {
        $string = ('Score is {0}-{1}' -f $1, $2)
    }
    return $string
}

Open in new window

Dustin Saunders

Here's something that should help:

$file = "C:\test\game1.txt"
$game = Get-Content $file

$rallyTables = @{}

function MakeActionTable
{
    $dt = New-Object System.Data.DataTable
    $c1 = New-Object System.Data.DataColumn 'player',([string])
    $c2 = New-Object System.Data.DataColumn 'action',([string])
    $c3 = New-Object System.Data.DataColumn 'score1',([int])
    $c4 = New-Object System.Data.DataColumn 'score2',([int])
    $c5 = New-Object System.Data.DataColumn 'rally',([int])
    $c6 = New-Object System.Data.DataColumn 'step',([int])
    $dt.Columns.Add($c1)
    $dt.Columns.Add($c2)
    $dt.Columns.Add($c3)
    $dt.Columns.Add($c4)
    $dt.Columns.Add($c5)
    $dt.Columns.Add($c6)
    return, $dt
}

function AddActRow($dt, $pla, $act, $s1, $s2, $rn, $stp)
{
    $row = $dt.NewRow()
    $row.action = $act
    $row.player = $pla
    $row.score1 = $s1
    $row.score2 = $s2
    $row.rally = $rn
    $row.step = $stp
    $dt.Rows.Add($row)
}

function ReportAction($1, $2, $type)
{
    if ($2.Substring(0,1) -ne "-")
    {
        if ($type -eq "play")
        {$string = "Player " + $1 + " had action " + $2 + ".  " }
        else {$string = "Score is " + $s1 + "-" + $s2 + "."}
        return $string
    }
}

$gameTable = MakeActionTable

$rallyNo = 1

foreach ($rally in $game)
{
    $report = ""
    $step = 1
    $scoreString = $rally.Substring(0,$rally.IndexOf(',')).Replace('(','').Replace(')','').Split('-')
    $s1 = $scoreString[0]
    $s2 = $scoreString[1]
    $plays = $rally.Substring($rally.IndexOf(", "))
    $plays = $plays.Substring(0,$plays.LastIndexOf(","))
    $actions = $plays.Split(',')
    $rallyTables[$rallyNo] = MakeActionTable
    foreach ($action in $actions)
    {
        if ($action)
        {
            $thisAction = $action.Split('(').Replace(')','').Trim()
            AddActRow $rallyTables[$rallyNo] $thisAction[1] $thisAction[0] $s1 $s2 $rallyNo $step
            $report = $report + (ReportAction $thisAction[1] $thisAction[0] "play")
            $step++;
        }
    }
    $report = $report + (ReportAction $s1 $s2 "score")
    $gameTable.Merge($rallyTables[$rallyNo])
    $rallyNo++
    Write-Host $report
}

Open in new window

We can record each rally # and each action taken (I called it 'step') in order. Now when you need to correct an error, you can get the rally & step of each correction then roll back in order to the next occurrence (moving backwards) of the action and remove it from the game row.

So if we have -BA( 6) at Rally 4, Step 3 and we have the BA( 6) to be removed at Rally 2, Step 4 we can roll back through steps then through rallies until we hit 2,4 at which point we have a match and we need to remove that row. Then your output should reflect the correction. It's easy to do now that we have number stamped each action in chronological order.

Does that make sense?

ASKER CERTIFIED SOLUTION

Dustin Saunders

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sirbounty

ASKER

Very much appreciated! Thanks :^)