Format html-output from R table like excel pivot w/ option "Do not repeat item labels"

Question

I have casted (reshape2) a dataframe table with the following structure:

Region | school

|Region | school | Year1 | Year2  |
-----------------------------------
|R1     | S1     | Value1| Value2 |
|R1     | S2     | Value3| Value4 |
|R1     | S3     | Value5| Value6 |
|R2     | S4     | Value7| Value8 |
|R2     | S5     | Value9| Value10|

So for each region, there are severeal schools. I would would like to only show the region once, like this:

|Region | school | Year1 | Year2  |
-----------------------------------
|R1     | S1     | Value1| Value2 |
|       | S2     | Value3| Value4 |
|       | S3     | Value5| Value6 |
|R2     | S4     | Value7| Value8 |
|       | S5     | Value9| Value10|

I am pretty sure I have seen this in many xtables - but I can't find and example now. Any ideas? If your are in doubt as to what I am trying to do; In Excel this is option can be found (it is the default i think) under pivottable tools -> design -> Report layout.

Below my casted datafram (dput):

structure(list(region = c("Hovedstaden", "Hovedstaden", "Hovedstaden", 
"Hovedstaden", "Hovedstaden", "Midtjylland", "Midtjylland", "Midtjylland", 
"Midtjylland", "Midtjylland", "Midtjylland", "Midtjylland", "Midtjylland", 
"Midtjylland", "Midtjylland", "Midtjylland", "Midtjylland", "Midtjylland", 
"Midtjylland", "Midtjylland", "Nordjylland", "Nordjylland", "Nordjylland", 
"Nordjylland", "Nordjylland", "SjÃ¦lland", "SjÃ¦lland", "SjÃ¦lland", 
"SjÃ¦lland", "SjÃ¦lland", "SjÃ¦lland", "SjÃ¦lland", "Syddanmark", 
"Syddanmark", "Syddanmark", "Syddanmark", "Syddanmark", "Syddanmark", 
"Syddanmark"), school = c("Campus Bornholm", "CPH - Uddannelsescenter KÃ¸benhavn Vest", 
"Erhvervsskolen NordsjÃ¦lland", "KÃ¸benhavns Tekniske Skole", 
"TEC Teknisk Erhvervsskole Center", "Den jydske HaandvÃ¦rkerskole", 
"Herningsholm Erhvervsskole", "Holstebro Tekniske Skole", "Learnmark Horsens", 
"ProfessionshÃ¸jskolen VIA University College", "Skive Tekniske Skole", 
"Teknisk Skole Silkeborg", "Tradium", "Tradium, Tekniske erhvervsudd. og Teknisk Gymnasium, HTX", 
"Uddannelsescenter Holstebro", "UddannelsesCenter RingkÃ¸bing-Skjern", 
"UddannelsesCenter RingkÃ¸bing-Skjern, teknisk skole", "Viden Djurs", 
"Vitus Bering Danmark", "AARHUS TECH", "Erhvervsskolerne Aars", 
"EUC Nord", "EUC Nordvest", "Nordvestjysk Uddannelsescenter", 
"Tech College Aalborg", "CELF - Center for erhv.rettede udd. Lolland-Falster", 
"CEUS", "EUC NordvestsjÃ¦lland", "EUC SjÃ¦lland", "Roskilde Tekniske Skole", 
"Roskilde Tekniske Skole - Pulsen", "Selandia - CEU", "EUC Syd", 
"EUC Vest", "HANSENBERG", "Odense Tekniske Skole", "Svendborg Erhvervsskole", 
"Syddansk Erhvervsskole Odense-Vejle", "Vejle Tekniske Skole"
), `2003` = c(0, 0, 83, 0, 294, 120, 73, 73, 0, 0, 28, 28, 0, 
29, 0, 0, 29, 0, 29, 47, 0, 25, 0, 25, 78, 0, 36, 25, 52, 0, 
55, 18, 66, 45, 45, 64, 15, 0, 72), `2004` = c(1243, 0, 71, 0, 
296, 122, 79, 79, 0, 0, 39, 39, 0, 30, 0, 0, 30, 0, 5, 53, 0, 
36, 0, 36, 82, 0, 53, 36, 58, 0, 76, 31, 56, 54, 54, 92, 30, 
0, 82), `2005` = c(1397, 0, 80, 0, 348, 132, 123, 123, 0, 0, 
44, 0, 0, 37, 0, 0, 37, 0, 37, 61, 0, 65, 65, 0, 143, 0, 56, 
40, 61, 0, 57, 26, 76, 53, 53, 119, 34, 0, 102), `2006` = c(1676, 
7, 98, 0, 423, 176, 132, 7, 0, 0, 48, 48, 0, 42, 0, 0, 42, 0, 
5, 69, 0, 87, 87, 0, 154, 0, 69, 47, 70, 0, 89, 42, 74, 81, 81, 
122, 38, 0, 119), `2007` = c(6, 6, 94, 94, 416, 190, 118, 13, 
0, 13, 57, 0, 0, 62, 0, 62, 0, 62, 0, 71, 7, 72, 72, 0, 193, 
0, 69, 53, 71, 78, 0, 42, 82, 68, 68, 108, 52, 0, 142), `2008` = c(2090, 
2090, 79, 79, 345, 158, 113, 9, 0, 6, 42, 0, 0, 31, 0, 0, 0, 
31, 0, 62, 423, 76, 76, 0, 141, 53, 0, 31, 43, 90, 0, 26, 84, 
62, 62, 0, 28, 193, 0), `2009` = c(1687, 1687, 58, 0, 237, 90, 
55, 0, 0, 55, 23, 23, 28, 0, 28, 0, 0, 28, 0, 42, 244, 37, 37, 
0, 68, 38, 0, 44, 34, 59, 0, 21, 41, 39, 39, 0, 15, 104, 0), 
    `2010` = c(1043, 1043, 70, 70, 285, 113, 56, 0, 56, 0, 32, 
    0, 29, 0, 29, 0, 0, 29, 0, 52, 287, 52, 52, 0, 75, 41, 0, 
    23, 34, 56, 0, 27, 52, 35, 0, 0, 35, 145, 0), `2011` = c(1223, 
    0, 66, 0, 279, 130, 73, 0, 73, 0, 22, 0, 23, 0, 23, 0, 0, 
    0, 0, 53, 0, 44, 44, 0, 90, 51, 0, 35, 32, 65, 0, 28, 35, 
    45, 0, 0, 31, 141, 0), `2012` = c(0, 0, 72, 0, 349, 130, 
    93, 0, 93, 0, 46, 0, 26, 0, 9, 0, 0, 0, 0, 56, 0, 56, 0, 
    0, 101, 41, 0, 38, 28, 76, 0, 34, 44, 47, 0, 0, 32, 186, 
    0), `2013` = c(0, 0, 56, 0, 286, 142, 79, 0, 79, 0, 25, 0, 
    27, 0, 27, 0, 0, 0, 0, 65, 0, 56, 0, 0, 112, 41, 0, 19, 39, 
    72, 0, 36, 44, 49, 0, 0, 19, 143, 0), `2014` = c(0, 0, 18, 
    0, 49, 38, 26, 0, 26, 0, 7, 0, 7, 0, 7, 0, 0, 0, 0, 7, 0, 
    8, 0, 0, 20, 9, 0, 18, 10, 9, 0, 10, 11, 15, 0, 0, 7, 36, 
    0)), .Names = c("region", "school", "2003", "2004", "2005", 
"2006", "2007", "2008", "2009", "2010", "2011", "2012", "2013", 
"2014"), row.names = c(NA, -39L), class = "data.frame")

Does [this answer](http://stackoverflow.com/a/6892758/1270695) help you? — A5C1D2H2I1M1N2O1R2T1, Apr 18 '14 at 05:31
@AnandaMahto - Thanks! That's the functionality I am looking for. However - I would much prefer a solution that works with html output and rmarkdown. - Looking into taRifx now though. — Andreas, Apr 18 '14 at 05:42
I have just [Gmisc and htmlTable](http://gforge.se/2013/02/tables-from-r-into-word/) which looks promising.Will report back later - after the todler have been fed :-o — Andreas, Apr 18 '14 at 06:23

score 1 · Answer 1 · answered May 28 '14 at 10:53

1

the function htmlTable ind Gmisc package can provide two-level coloumnlabels and rowlabels (using cgroup/rgroup and n.croup/n.rgroup. See e.g. http://timelyportfolio.blogspot.dk/2013/04/tables-are-like-cockroaches.html and http://gforge.se/2013/02/tables-from-r-into-word/

answered May 28 '14 at 10:53

Andreas

6,612
14
59
69

1

an example would turn this into a more useful answer (links can be broken) – baptiste May 28 '14 at 11:05

Format html-output from R table like excel pivot w/ option "Do not repeat item labels"

1 Answers1