Create a Table with Alternating Total Rows Followed by Sub-Rows Using Dplyr and Tidyverse

1
votes
library(dplyr)
library(forcats)

Using the simple dataframe and code below, I want to create a table with total rows and sub-rows. For example, the first row would be "Region1" from the NEW column and 70 from the TotNumber column, then below that would be three rows for "Town1", "Town2", and "Town3", and their associated numbers from the Number column, and the same for "Region2" and "Region3". I attached a pic of the desired table...enter image description here

I'm also looking for a solution using dplyr and Tidyverse. 

Number<-c(10,30,30,10,56,30,40,50,33,10)
Town<-("Town1","Town2","Town3","Town4","Town5","Town6","Town7","Town8","Town9","Town10")

DF<-data_frame(Town,Number)


DF<-DF%>%mutate_at(vars(Town),funs(as.factor))

To create Region variable...

DF<-DF%>%mutate(NEW=fct_collapse(Town,
Region1=c("Town1","Town2","Town3"),
Region2=c("Town4","Town5","Town6"),
Region3=c("Town7","Town8","Town9","Town10")))%>%
group_by(NEW)%>%
summarise(TotNumber=sum(Number))
1
rdplyrtidyverse
Does my answer seem helpful to you? – acylam

1 Answers

0
votes

Modifying your last pipes and adding some addition steps:

library(dplyr)
library(forcats) 
DF%>%mutate(NEW=fct_collapse(Town,
                                 Region1=c("Town1","Town2","Town3"),
                                 Region2=c("Town4","Town5","Town6"),
                                 Region3=c("Town7","Town8","Town9","Town10")),
            NEW = as.character(NEW)) %>%
  group_by(NEW) %>%
  mutate(TotNumber=sum(Number))  %>%
  ungroup() %>%
  split(.$NEW) %>%
  lapply(function(x) rbind(setNames(x[1,3:4], names(x)[1:2]), x[1:2])) %>%
  do.call(rbind, .)

Results:

# A tibble: 13 × 2
      Town Number
*    <chr>  <dbl>
1  Region1     70
2    Town1     10
3    Town2     30
4    Town3     30
5  Region2     96
6    Town4     10
7    Town5     56
8    Town6     30
9  Region3    133
10   Town7     40
11   Town8     50
12   Town9     33
13  Town10     10

Data:

Number<-c(10,30,30,10,56,30,40,50,33,10)
Town<-c("Town1","Town2","Town3","Town4","Town5","Town6","Town7","Town8","Town9","Town10")

DF<-data_frame(Town,Number) %>%
  mutate_at(vars(Town),funs(as.factor))