您将如何在 Mathematica 中执行数据透视表函数? [英] How would you do a PivotTable function in Mathematica?

问题描述

Excel 中的数据透视表(或者，交叉制表) 非常有用.有没有人已经想过如何在 Mathematica 中实现类似的功能?

PivotTables in Excel (or, cross tabulations) are quite useful. Has anyone already thought about how to implement a similar function in Mathematica?

推荐答案

我对数据透视表的使用不熟悉，但是以上面链接页面上的例子，我提出这个:

I am not familiar with the use of pivot tables, but taking the example on the page linked above, I propose this:

Needs["Calendar`"]
key = # -> #2[[1]] & ~MapIndexed~
       {"Region", "Gender", "Style", "Ship Date", "Units", "Price", "Cost"};
choices = {
   {"North", "South", "East", "West"},
   {"Boy", "Girl"},
   {"Tee", "Golf", "Fancy"},
   IntegerString[#, 10, 2] <> "/2011" & /@ Range@12,
   Range@15,
   Range[8.00, 15.00, 0.01],
   Range[6.00, 14.00, 0.01]
   };
data = RandomChoice[#, 150] & /@ choices // Transpose;

这会创建看起来像这样的 data:

This creates data that looks like:

 {"East", "Girl", "Golf", "03/2011", 6, 12.29`, 6.18`},
 {"West", "Boy", "Fancy", "08/2011", 6, 13.01`, 12.39`},
 {"North", "Girl", "Golf", "05/2011", 1, 14.87`, 12.89`},
 {"East", "Girl", "Golf", "09/2011", 3, 13.99`, 6.25`},
 {"North", "Girl", "Golf", "09/2011", 13, 12.66`, 8.57`},
 {"East", "Boy", "Fancy", "10/2011", 2, 14.46`, 6.85`},
 {"South", "Boy", "Golf", "11/2011", 13, 12.45`, 11.23`}
 ...

那么:

h1 = Union@data[[All, "Region" /. key]];
h2 = Union@data[[All, "Ship Date" /. key]];

Reap[
   Sow[#[[{"Units", "Ship Date"} /. key]], #[["Region" /. key]]] & ~Scan~ data,
   h1,
   Reap[Sow @@@ #2, h2, Total @ #2 &][[2]] &
][[2]];

TableForm[Join @@ %, TableHeadings -> {h1, h2}]

这是一个粗略的例子，但它提供了如何做到这一点的想法.如果您有更具体的要求，我会尝试解决它们.

This is a rough example, but it gives an idea of how this may be done. If you have more specific requirements I will attempt to address them.

Manipulate 块被大量复制，但我相信我的 pivotTableData 更有效，并且我试图正确本地化符号，因为它现在作为可用代码而不是比一个粗略的例子.

The Manipulate block is largely copied, but I believe my pivotTableData is more efficient, and I sought to localize symbols correctly, since this is now presented as usable code rather than a rough example.

我从相同的示例数据开始，但我嵌入了字段标题，因为我觉得这更能代表正常使用.

I start with the same sample data, but I embed the field headings, since I feel this is more representative of normal use.

data = ImportString[#, "TSV"][[1]] & /@ Flatten[Import["http://lib.stat.cmu.edu/datasets/CPS_85_Wages"][[28 ;; -7]]];

data = Transpose[{
    data[[All, 1]], 
    data[[All, 2]] /. {1 -> "South", 0 -> "Elsewhere"}, 
    data[[All, 3]] /. {1 -> "Female", 0 -> "Male"},
    data[[All, 4]], 
    data[[All, 5]] /. {1 -> "Union Member", 0 -> "No member"}, 
    data[[All, 6]],
    data[[All, 7]], 
    data[[All, 8]] /. {1 -> "Other", 2 -> "Hispanic", 3 -> "White"}, 
    data[[All, 9]] /. {1 -> "Management", 2 -> "Sales", 3 -> "Clerical", 4 -> "Service", 5 -> "Professional", 6 -> "Other"}, 
    data[[All, 10]] /. {0 -> "Other", 1 -> "Manufacturing", 2 -> "Construction"}, 
    data[[All, 11]] /. {1 -> "Married", 0 -> "Unmarried"}
}];

PrependTo[data,
  {"Education", "South", "Sex", "Experience", "Union", "Wage", "Age", "Race", "Occupation", "Sector", "Marriatal status"}
  ];

我的 pivotTableData 是自包含的.

pivotTableData[data_, field1_, field2_, dependent_, op_] :=
  Module[{key, sow, h1, h2, ff},
    (key@# = #2[[1]]) & ~MapIndexed~ data[[1]];
    sow = #[[key /@ {dependent, field2}]] ~Sow~ #[[key@field1]] &;
    {h1, h2} = Union@data[[2 ;;, key@#]] & /@ {field1, field2};
    ff = # /. {{} -> Missing@"NotAvailable", _ :> op @@ #} &;
    {
     {h1, h2},
     Join @@ Reap[sow ~Scan~ Rest@data, h1, ff /@ Reap[Sow @@@ #2, h2][[2]] &][[2]]
    }
  ]

pivotTable 仅依赖于 pivotTableData:

pivotTable[data_?MatrixQ] :=
 DynamicModule[{raw, t, header = data[[1]], opList =
    {Mean              -> "Mean of \[Rule]",
     Total             -> "Sum of \[Rule]",
     Length            -> "Count of \[Rule]",
     StandardDeviation -> "SD of \[Rule]",
     Min               -> "Min of \[Rule]",
     Max               -> "Max of \[Rule]"}},
  Manipulate[
   raw = pivotTableData[data, f1, f2, f3, op];
   t = ConstantArray["", Length /@ raw[[1]] + 2];
   t[[1, 1]] = Control[{op, opList}];
   t[[1, 3]] = Control[{f2, header}];
   t[[2, 1]] = Control[{f1, header}];
   t[[1, 2]] = Control[{f3, header}];
   {{t[[3 ;; -1, 1]], t[[2, 3 ;; -1]]}, t[[3 ;; -1, 3 ;; -1]]} = raw;
   TableView[N@t, Dividers -> All],
   Initialization :> {op = Mean, f1 = data[[1,1]], f2 = data[[1,2]], f3 = data[[1,3]]}
  ]
 ]

使用很简单:

pivotTable[data]

这篇关于您将如何在 Mathematica 中执行数据透视表函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！