The following code defined a matrix.
$a = @('a','b','x',10),
@('a','b','y',20),
@('c','e','x',50),
@('c','e','y',30)
$a | % { "[$_]"}
I want to pivot the array by x and y. The expected result array should be
[a b 10 20]
[c e 50 30]
- - -- --
x y
I think it needs group-object and then mapping. How to use group-object on an array?
(BTW, why the question has been down voted twice?)
You can't use Group-Object with an array (at least not the way you want) since Group-Object works on object properties. A workaround is to organize your rows into a label that you want to group on, followed by the values to assign to the group. Then you can group on the label:
$a | %{
new-object PsObject -prop @{"label" = "$($_[0]),$($_[1])"; value=@{ $_[2]=$_[3]}}
} | Group-Object label
So, then you have a group with your entries stroed as an array of hashtables within each group:
Count Name Group
----- ---- -----
2 a,b {@{value=System.Collections.Hashtable; label=a,b}, @{value=System.Collections.Hashtable; label=a,b}}
2 c,e {@{value=System.Collections.Hashtable; label=c,e}, @{value=System.Collections.Hashtable; label=c,e}}
You can then expand out each row to get the info you desire:
$a | %{
new-object PsObject -prop @{"label" = "$($_[0]),$($_[1])"; value=@{ $_[2]=$_[3]}}
} |
group label | % {
"[$(@($_.Name -split ",") + @($_.Group.value.values))]"
}
which gives:
[a b 10 20]
[c e 50 30]
To answer your second comment, no the above won;t guarantee the order. To guarantee it, you'll have to be explicit:
$a | %{
new-object PsObject -prop @{"label" = "$($_[0]),$($_[1])"; value=@{ $_[2]=$_[3]}}
} |
group label | % {
"[$(@($_.Name -split ",") + @($_.Group.value.x, $_.Group.value.y))]"
}
$_.Group.value.values guarantees x being always in front of y? And what if the output need to in the order of [a b y x], [c e y x]?
$_.Group.value.x in the updated solution works on Windows 10 but not on Windows 7. It will get the error of Property 'x' cannot be found on this object. Make sure that it exists. At line:10 char:35 + "[$(@($_.Name -split ",") + @($_.Group.value.x, $_.Group.value.y))]" + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], PropertyNotFoundException + FullyQualifiedErrorId : PropertyNotFoundStrict