如何在R中创建一个类似Excel中具有多个标题行和列的数据透视表？

huangapple
go评论104阅读模式
英文:

How to make a pivot table with multi-headers rows and columns in R like in Excel?

问题

  1. 我想从：
  3.                         cyl gear am
  4.     马自达 RX4             6    4  1
  5.     马自达 RX4 Wag         6    4  1
  6.     大通 710              4    4  1
  7.     黄蜂4驱动              6    3  0
  8.     黄蜂运动旅行车        8    3  0
  9.     英勇者                 6    3  0
  10.     Duster 360           8    3  0
  11.     Merc 240D            4    4  0
  12.     Merc 230             4    4  0
  13.     Merc 280             6    4  0
  14.     Merc 280C            6    4  0
  15.     Merc 450SE           8    3  0
  16.     Merc 450SL           8    3  0
  17.     Merc 450SLC          8    3  0
  18.     Cadillac Fleetwood   8    3  0
  19.     Lincoln Continental  8    3  0
  20.     Chrysler Imperial    8    3  0
  21.     Fiat 128             4    4  1
  22.     本田 Civic           4    4  1
  23.     丰田 Corolla         4    4  1
  24.     丰田 Corona          4    3  0
  25.     道奇 Challenger       8    3  0
  26.     AMC Javelin          8    3  0
  27.     Camaro Z28           8    3  0
  28.     Pontiac Firebird     8    3  0
  29.     Fiat X1-9            4    4  1
  30.     保时捷 914-2          4    5  1
  31.     Lotus Europa         4    5  1
  32.     Ford Pantera L       8    5  1
  33.     法拉利 Dino           6    5  1
  34.     玛莎拉蒂 Bora         8    5  1
  35.     Volvo 142E           4    4  1
  36.     
  37. 到
  39. [![在这里输入图片描述][1]][1]
  41. 我可以从制作一个长格式的摘要表格开始
  43.     library(tidyverse)
  44.     mtcars %>%
  45.       summarise(nb = n(), .by = c(cyl, gear, am)) %>%
  46.       arrange(cyl, gear, am)
  47.     
  48.        cyl gear am nb
  49.     1    4    3  0  1
  50.     2    4    4  0  2
  51.     3    4    4  1  6
  52.     4    4    5  1  2
  53.     5    6    3  0  2
  54.     6    6    4  0  2
  55.     7    6    4  1  2
  56.     8    6    5  1  1
  57.     9    8    3  0 12
  58.     10   8    5  1  2
  60. 但我不知道怎么继续
英文:

I want to go from :

  1.                     cyl gear am
  2. Mazda RX4             6    4  1
  3. Mazda RX4 Wag         6    4  1
  4. Datsun 710            4    4  1
  5. Hornet 4 Drive        6    3  0
  6. Hornet Sportabout     8    3  0
  7. Valiant               6    3  0
  8. Duster 360            8    3  0
  9. Merc 240D             4    4  0
  10. Merc 230              4    4  0
  11. Merc 280              6    4  0
  12. Merc 280C             6    4  0
  13. Merc 450SE            8    3  0
  14. Merc 450SL            8    3  0
  15. Merc 450SLC           8    3  0
  16. Cadillac Fleetwood    8    3  0
  17. Lincoln Continental   8    3  0
  18. Chrysler Imperial     8    3  0
  19. Fiat 128              4    4  1
  20. Honda Civic           4    4  1
  21. Toyota Corolla        4    4  1
  22. Toyota Corona         4    3  0
  23. Dodge Challenger      8    3  0
  24. AMC Javelin           8    3  0
  25. Camaro Z28            8    3  0
  26. Pontiac Firebird      8    3  0
  27. Fiat X1-9             4    4  1
  28. Porsche 914-2         4    5  1
  29. Lotus Europa          4    5  1
  30. Ford Pantera L        8    5  1
  31. Ferrari Dino          6    5  1
  32. Maserati Bora         8    5  1
  33. Volvo 142E            4    4  1

To

如何在R中创建一个类似Excel中具有多个标题行和列的数据透视表？

I can start with making a summary table in long format

  1. library(tidyverse)
  2. mtcars %>% 
  3. summarise(nb = n(), .by = c(cyl, gear, am)) %>% 
  4. arrange(cyl, gear, am)
  5. cyl gear am nb
  6. 1    4    3  0  1
  7. 2    4    4  0  2
  8. 3    4    4  1  6
  9. 4    4    5  1  2
  10. 5    6    3  0  2
  11. 6    6    4  0  2
  12. 7    6    4  1  2
  13. 8    6    5  1  1
  14. 9    8    3  0 12
  15. 10   8    5  1  2

But I don't know how to go from here

答案1

得分: 1

If you're just looking for tidyverse code to pivot your existing table wider, you can use pivot_wider() from the tidyr package.

如果您只想找到整理现有表格宽度的tidyverse代码，您可以使用tidyr包中的pivot_wider()。

If you're looking for something that has an aesthetic like an excel pivot table, you could use the pivottabler package.

如果您寻找的是具有类似Excel数据透视表的美观效果，您可以使用pivottabler包。

英文:

If you're just looking for tidyverse code to make to pivot your existing table wider, you can use pivot_wider() from the tidyr package.

  1. library(tidyverse)
  2. mtcars %>% 
  3.   summarise(nb = n(), .by = c(cyl, gear, am)) %>% 
  4.   arrange(cyl, gear, am) %>% 
  5.   pivot_wider(names_from="am", values_from="nb", values_fill=0)
  6. #> # A tibble: 8 × 4
  7. #>     cyl  gear   `0`   `1`
  8. #>   <dbl> <dbl> <int> <int>
  9. #> 1     4     3     1     0
  10. #> 2     4     4     2     6
  11. #> 3     4     5     0     2
  12. #> 4     6     3     2     0
  13. #> 5     6     4     2     2
  14. #> 6     6     5     0     1
  15. #> 7     8     3    12     0
  16. #> 8     8     5     0     2

If you're looking for something that has an aesthetic that's more like an excel pivot table, you could use the pivottabler package:

  1. library(pivottabler)
  2. pt <- PivotTable$new()
  3. pt$addData(mtcars)
  4. pt$addColumnDataGroups("am")
  5. pt$addRowDataGroups("cyl", header="# Cylinders")
  6. pt$addRowDataGroups("gear", header="# Gears") 
  7. pt$defineCalculation(calculationName="Count", summariseExpression="n()")
  8. pt$renderPivot(showRowGroupHeaders = TRUE)

如何在R中创建一个类似Excel中具有多个标题行和列的数据透视表？<!-- -->

<sup>Created on 2023-05-14 with reprex v2.0.2</sup>

答案2

得分: 1

使用 ftable 进行操作。无需使用任何包。

  1. ftable(am ~ cyl + gear, mtcars)
  2. ##          am  0  1
  3. ## cyl gear         
  4. ## 4   3        1  0
  5. ##     4        2  6
  6. ##     5        0  2
  7. ## 6   3        2  0
  8. ##     4        2  2
  9. ##     5        0  1
  10. ## 8   3       12  0
  11. ##     4        0  0
  12. ##     5        0  2

如果重要的是将0单元格显示为空格，则使用以下代码：

  1. ft <- ftable(am ~ cyl + gear, mtcars)
  2. ft[ft == 0] <- ""
  3. ft
  4. ##          am  0  1
  5. ## cyl gear         
  6. ## 4   3       1    
  7. ##     4       2  6 
  8. ##     5          2 
  9. ## 6   3       2    
  10. ##     4       2  2 
  11. ##     5          1 
  12. ## 8   3       12   
  13. ##     4            
  14. ##     5          2

要生成 LaTeX 表格，请使用 xtable：

  1. library(xtable)
  2. xtableFtable(ft)
  3. ## % latex table generated in R 4.3.0 by xtable 1.8-4 package
  4. ## % Sun May 14 09:48:41 2023
  5. ## \begin{table}[ht]
  6. ## \centering
  7. ## \begin{tabular}{lll |rr}
  8. ##   \hline
  9. ##      &      & am & \multicolumn{1}{l}{  0} & \multicolumn{1}{l}{  1} \\ 
  10. ##   cyl & gear &    & \multicolumn{1}{l}{   } & \multicolumn{1}{l}{   } \\ 
  11. ##    \hline
  12. ##   4   & 3    &    & 1  &    \\ 
  13. ##       & 4    &    & 2  & 6  \\ 
  14. ##       & 5    &    &    & 2  \\ 
  15. ##   6   & 3    &    & 2  &    \\ 
  16. ##       & 4    &    & 2  & 2  \\ 
  17. ##       & 5    &    &    & 1  \\ 
  18. ##   8   & 3    &    & 12 &    \\ 
  19. ##       & 4    &    &    &    \\ 
  20. ##       & 5    &    &    & 2  \\ 
  21. ##    \hline
  22. ## \end{tabular}
  23. ## \end{table}
英文:

Use ftable. No packages are used.

  1. ftable(am ~ cyl + gear, mtcars)
  2. ##          am  0  1
  3. ## cyl gear         
  4. ## 4   3        1  0
  5. ##     4        2  6
  6. ##     5        0  2
  7. ## 6   3        2  0
  8. ##     4        2  2
  9. ##     5        0  1
  10. ## 8   3       12  0
  11. ##     4        0  0
  12. ##     5        0  2

If it is important to have the 0 cells shown as spaces then

  1. ft &lt;- ftable(am ~ cyl + gear, mtcars)
  2. ft[ft == 0] &lt;- &quot;&quot;
  3. ft
  4. ##          am  0  1
  5. ## cyl gear         
  6. ## 4   3       1    
  7. ##     4       2  6 
  8. ##     5          2 
  9. ## 6   3       2    
  10. ##     4       2  2 
  11. ##     5          1 
  12. ## 8   3       12   
  13. ##     4            
  14. ##     5          2

To generate latex use xtable

  1. library(xtable)
  2. xtableFtable(ft)
  3. ## % latex table generated in R 4.3.0 by xtable 1.8-4 package
  4. ## % Sun May 14 09:48:41 2023
  5. ## \begin{table}[ht]
  6. ## \centering
  7. ## \begin{tabular}{lll |rr}
  8. ##   \hline
  9. ##      &amp;      &amp; am &amp; \multicolumn{1}{l}{  0} &amp; \multicolumn{1}{l}{  1} \\ 
  10. ##   cyl &amp; gear &amp;    &amp; \multicolumn{1}{l}{   } &amp; \multicolumn{1}{l}{   } \\ 
  11. ##    \hline
  12. ##   4   &amp; 3    &amp;    &amp; 1  &amp;    \\ 
  13. ##       &amp; 4    &amp;    &amp; 2  &amp; 6  \\ 
  14. ##       &amp; 5    &amp;    &amp;    &amp; 2  \\ 
  15. ##   6   &amp; 3    &amp;    &amp; 2  &amp;    \\ 
  16. ##       &amp; 4    &amp;    &amp; 2  &amp; 2  \\ 
  17. ##       &amp; 5    &amp;    &amp;    &amp; 1  \\ 
  18. ##   8   &amp; 3    &amp;    &amp; 12 &amp;    \\ 
  19. ##       &amp; 4    &amp;    &amp;    &amp;    \\ 
  20. ##       &amp; 5    &amp;    &amp;    &amp; 2  \\ 
  21. ##    \hline
  22. ## \end{tabular}
  23. ## \end{table}

huangapple
  • 本文由 发表于 2023年5月14日 18:24:58
  • 转载请务必保留本文链接：https://go.coder-hub.com/76246965.html
  • dplyr
  • excel
  • pivot-table
  • r
  • summarize
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定