Better alternative to pivot rows into columns

Question

trying to stack these columns in powershell, and it works! however, it feels like there should be an easier way to do this. please let me know if you have alternatives that accomplish the same goal

$lines = @'
5 
b d7 e
c f
'@

$lines = $lines.Split("`n")

$max = $lines | % {$_.trim().split(' ').count} | sort -desc | select -f 1
$count = 0
$obj = New-Object psobject
foreach ($line in $lines) {
    $obj | Add-Member -MemberType NoteProperty -Name $count -Value $line
    $count++
}

for ($x = 0; $x -lt $count; $x++) {
    for ($y = 0; $y -lt $count; $y++) {
        $obj.$y.trim().split(' ')[$x]
    }
}

desired output would be this:

5
b
c
d7
f
e

here's a more random example of input and desired output. the script:

$alpha = 65..90 | % { [char]$_ }
$lines = for ($i = 0; $i -lt $alpha.Count; $i ++) {
    $line = ''
    $cols = Get-Random -Minimum 1 -Maximum 6
    for ($j = 0; $j -lt $cols; $j++) {
        $line += $alpha[$i+$j] + ' '
    }
    $line.Trim()
    $i = $i + $cols - 1
}

$lines = $lines.Split("`n")

$lines
# Rest of code same as above.

the first section shows the array. the second section shows what i want it to look like. (reminder: script already works, just looking for alternatives)

A
B C D E
F G
H I
J
K L M N
O
P Q R S
T U V W
X Y Z

A
B
F
H
J
K
O
P
T
X
C
G
I
L
Q
U
Y
D
M
R
V
Z
E
N
S
W

Just to clarify can you show the desired output. Your input will always be a single string like that? — Matt
– Matt, Commented Jan 6, 2016 at 2:02

Matt · Accepted Answer · 2016-01-06 11:48:54Z

1

Given $lines is one of the here-strings you have above something like this would work just fine. Text coming from file would be just as easy to insert here.

$linesArray = $lines -split "`r`n" | ForEach-Object{,($_.Trim() -split '\s')}
$columns = ($linesArray | Measure-Object Count -Maximum).Maximum

$result = for($index =0; $index -lt $columns;$index++){
        $linesArray | Where-Object {$index -lt $_.Length} | ForEach-Object{$_[$index]} | Where-Object{![string]::IsNullOrEmpty($_)}
}

Where $result contains the "transposed" arrray. Split $lines into lines and for each line split on spaces (Trim()ed in case of trailing spaces which I found in your example).

Count the maximum columns so that we know how our loop will be structured. Then simply call the nth element of each line in order. Nulls are ignored so calling non exiting elements is a non issue.

Tested with PowerShell v4 but can be downgraded easy if need be. $columns is the only thing that would need to be changed.

I made a couple of changes to address comments. If a strict mode is enabled that flags accessing non-existent elements a simple where clause will prevent accessing those elements that do no exist. It also would be proper to address the nulls that are returned in case of post processing issues.

edited Jan 6, 2016 at 11:48

answered Jan 6, 2016 at 2:21

Matt

47k9 gold badges90 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user4003407 Over a year ago

With Set-StrictMode -Version 3 PowerShell produce IndexOutOfRangeException if you try to access not existing array element, so I would be add explicit array length check.

user4003407 Over a year ago

And without strict mode your code produce many $null values in the $result.

Matt Over a year ago

@PetSerAl both easy things to address I suppose. Just trying to keep it terse. I see the array addressing as a feature. Silly nulls

Matt Over a year ago

@PetSerAl I think that should cover it.

user4003407 Over a year ago

IMHO, Where-Object {$index -lt $_.Length} before ForEach-Object is a bit simpler.

Doug Finke · Accepted Answer · 2016-01-06 02:26:43Z

1

$lines=@'
5
b d7 e
c f
'@ -split "`r`n" 

$delimiter=' '
$max=-1
$xform=[ordered]@{}
$lineCount=0

foreach($line in $lines) {    
    $lineCount++
    $key="line$($lineCount)"

    $parts = @($line.trim().split($delimiter))
    $xform.$key = $parts

    if($parts.count -gt $max) {$max=$parts.count}
}

foreach($idx in 0..($max-1)) {    
    foreach($key in $xform.Keys) {
        $xform.$key[$idx]
    }
}

edited Jan 6, 2016 at 2:26

answered Jan 6, 2016 at 2:17

Doug Finke

6,8432 gold badges34 silver badges48 bronze badges

Collectives™ on Stack Overflow

Better alternative to pivot rows into columns

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related