I am trying to use bind_rows and summarize_if to add a total bottom row in a sample data set. There are different postings that are related to this type of question, but not exactly my issue. Additionally, some posted questions have so much other code and data that I wind up spending more time trying to figure out the code and example instead of how the answer applies more generally.
With that in mind, I have a simple sample data set.
Reproducible example:
library(tidyverse)
library(readxl)
sample_pivot_data <- structure(list(Group = c("A", "B", "A", "A", "A", "B", "B", "B",
"C", "C", "C"), Season = c("Winter", "Summer", "Winter", "Fall",
"Spring", "Winter", "Fall", "Spring", "Winter", "Summer", "Summer"
), Expense = c("Insurance", "Rent", "Utilities", "Misc", "Insurance",
"Rent", "Utilities", "Insurance", "Rent", "Utilities", "Misc"
), Fixed_Variable = c("Fixed", "Fixed", "Variable", "Variable",
"Fixed", "Fixed", "Variable", "Variable", "Fixed", "Variable",
"Variable"), Amount = c(300, 200, 400, 300, 800, 400, 200, 300,
450, 230, 120)), row.names = c(NA, -11L), class = c("tbl_df",
"tbl", "data.frame"))
# A look at the data:
> sample_pivot_data
# A tibble: 11 x 5
Group Season Expense Fixed_Variable Amount
<chr> <chr> <chr> <chr> <dbl>
1 A Winter Insurance Fixed 300
2 B Summer Rent Fixed 200
3 A Winter Utilities Variable 400
4 A Fall Misc Variable 300
5 A Spring Insurance Fixed 800
6 B Winter Rent Fixed 400
7 B Fall Utilities Variable 200
8 B Spring Insurance Variable 300
9 C Winter Rent Fixed 450
10 C Summer Utilities Variable 230
11 C Summer Misc Variable 120
I found a similar problem which was addressed in this post here which gave me this solution which works:
# This works, no syntax issues
my_pivot <- sample_pivot_data %>%
group_by(Group, Fixed_Variable) %>%
summarize(category_total = sum(Amount)) %>%
pivot_wider(names_from = Fixed_Variable, values_from = category_total) %>%
ungroup() %>%
mutate(GrandTotal = rowSums(.[-1])) %>%
bind_rows(summarize_all(.,
funs(if (is.numeric(.))
sum(.)
else
"Grand_Total"))
) %>%
print()
# A tibble: 4 x 4
Group Fixed Variable GrandTotal
<chr> <dbl> <dbl> <dbl>
1 A 1100 700 1800
2 B 600 500 1100
3 C 450 350 800
4 Grand_Total 2150 1550 3700
When I tried to do the same thing, but use summarize_if with the below code, I get an error:
Error in UseMethod("tbl_vars") :
no applicable method for 'tbl_vars' applied to an object of class "function" I looked here as a possible solution to the error, but I didn't follow how this applied in this case.
# This does not work
my_pivot2 <- sample_pivot_data %>%
group_by(Group, Fixed_Variable) %>%
summarize(category_total = sum(Amount)) %>%
pivot_wider(names_from = Fixed_Variable, values_from = category_total) %>%
ungroup() %>%
mutate(GrandTotal = rowSums(.[-1])) %>%
bind_rows(summarize_if(is.numeric, sum, na.rm = TRUE)) %>%
print()
If someone could explain why the above doesn't work I would appreciate it. On a related note, I also tried bind_rows(summarize_all(., list(~if(is.numeric(.)) sum(.) else "Grand_Total" ))) which worked but RStudio kept giving me an indication that parenthesis were unmatched...perhaps a different question, but I figured I would mention rather than posting an entirely separate question.
