1

I'm trying to add additional rows to my data table with the column totals so that when I display on ggplot, I am able to filter by "Total" for my selectInput in my Shiny app. However, because I have various data types (i.e. date, string and numeric), it makes it more complicated.

Here's a sample df:

data.frame(
  Date = rep(seq(as.Date("2018-01-01"), by= "1 day", length.out= 3), 3),
  Company = c("A", "A", "A", "B", "B", "B", "C", "C", "C"),
  Attr_1 = c("AB", "AC", "AD", "AB", "AC", "AD", "AB", "AC", "AD"),
  Attr_2 = c(1,2,3,4,5,6,7,8,9)
)

Here's what I'm hoping to achieve:

Date       Company Attr_1 Attr_2
2018-01-01       A     AB      1
2018-01-02       A     AC      2
2018-01-03       A     AD      3
2018-01-01       B     AB      4
2018-01-02       B     AC      5
2018-01-03       B     AD      6
2018-01-01       C     AB      7
2018-01-02       C     AC      8
2018-01-03       C     AD      9
2018-01-01   Total     AB     12
2018-01-02   Total     AC     15
2018-01-03   Total     AD     18

Does anyone have an easy solution for this? What I can think of is to calculate the colSums manually and then rbind back into this dataframe. But is there a simpler solution?

Improve this question
2
  • R E A D Commented Sep 26, 2018 at 10:41
  • Sorry guys, I've edited my question to make it more accurate to the problem that I'm facing. Commented Sep 26, 2018 at 15:37

4 Answers 4

Reset to default
2 
df = data.frame(
  Company = c("A", "B", "C", "D", "A", "B"),
  Attr_1 = c(12,13,14,14,3,5),
  Attr_2 = c(1,2,3,4,5,4)
)

library(dplyr)

bind_rows(df, df %>%
               summarise_at(vars(matches("Attr")), funs(sum)) %>%
               mutate(Company = "Total"))

#   Company Attr_1 Attr_2
# 1       A     12      1
# 2       B     13      2
# 3       C     14      3
# 4       D     14      4
# 5       A      3      5
# 6       B      5      4
# 7   Total     61     19

Solution to your edit:

df %>%
  group_by(Date, Attr_1) %>%
  summarise(Attr_2 = sum(Attr_2),
            Company = "Total") %>%
  ungroup() %>%
  bind_rows(df, .)
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks @AntoniosK, but this solution doesn't work for my revised problem. May I know if you have a solution to the edited problem instead?
I've updated my answer...
Thanks for your solution! I've tried it but unfortunately, the "Total" column is in the format of character, while my previous Company column comprises of factors and I got the following warning message: "binding factor and character vector, coercing into character vector". Even after adding factor("Total", levels="Total") into the summarise argument, I'm still getting the following warning: "In bind_rows_(x, .id) : Unequal factor levels: coercing to character" In the end, I decided to use rbind instead of bind_rows. Is there a reason why I rbind works but bind_rows doesn't?
Warning is not an error. The job is still done! But if you want to eliminate warnings try to use character variables instead of factors from the beginning. Can you do that, or you want me to update my answer?
I'm able to do that myself, thanks a lot for the clarification! Appreciate it!
1

A solution that works even if there is a 'W' company.

data.frame(
  Company = c("A", "B", "W", "D", "A", "B"),
  Attr_1 = c(12,13,14,14,3,5),
  Attr_2 = c(1,2,3,4,5,4),
  stringsAsFactors=FALSE
) -> df

df %>% summarise_if(is.numeric,sum) %>%
  mutate(Company='Total') %>% 
  bind_rows(df,.)
#  Company Attr_1 Attr_2
#1       A     12      1
#2       B     13      2
#3       W     14      3
#4       D     14      4
#5       A      3      5
#6       B      5      4
#7   Total     61     19
Improve this answer

3 Comments

Thanks @Nicholas2, may I know if you have any solution to my updated problem?
It is just a matter of grouping on Date and Attr_1 and summarizing Attr_2 before row_binding. But before trying anything are you sure that there is only one value for Attr_1 for each Date?
There may be more than 1 value for Attr_1 for each date, but I manage to figure out a solution for it. Thanks for your help!
1

Here's a base R solution:

df <- data.frame(
  Company = c("A", "B", "C", "D", "A", "B"),
  Attr_1 = c(12,13,14,14,3,5),
  Attr_2 = c(1,2,3,4,5,4)
)
rbind(df, data.frame(Company = "Total", Attr_1 = sum(df$Attr_1), Attr_2 = sum(df$Attr_2)))

Output:

  Company Attr_1 Attr_2
1       A     12      1
2       B     13      2
3       C     14      3
4       D     14      4
5       A      3      5
6       B      5      4
7   Total     61     19
Improve this answer

Comments

1

I find adorn_totals from the janitorpackage very useful for this (and other) tasks

library( janitor )

df %>% adorn_totals()

# Company Attr_1 Attr_2
#       A     12      1
#       B     13      2
#       C     14      3
#       D     14      4
#       A      3      5
#       B      5      4
#   Total     61     19
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.