Additional columns for a summarised DataFrame

Question

I want to add columns to a summarized data frame that counts a particular factor.

bookplace <- data.frame(type = c("reading", "reading", "reading", "reading", "lending", "lending"), 
                        sex = c("male", "female", "male", "female", "male", "female"), 
                        usage = c(103, 102, 23, 14, 16, 8), 
                        date = c("1/1/18","1/1/18","1/1/18","1/1/18","1/1/18","1/1/18"),
                        stringsAsFactors = FALSE)

The result should be (considering male and female as the added columns):

year  type    users  male  female
2018  lending    24    16       8
2018  reading   242   126     116

I tried using mutate to add the column and then summarize with the following code:

bookplace %>% 
  mutate(males=count(sex=="male"),
         females=count(sex=="female")) %>%
  group_by(year=format(date,"%Y"), type) %>% 
  summarize(users=sum(usage))

But I have the following error message:

Error in UseMethod("groups") : no applicable method for 'groups' applied to an object of class "logical"

Please, any help will be greatly appreciated.

Please create your example correctly. Not clear about the expected output. What are 'users' based on the input example — akrun, Jul 18 '19 at 01:47
Hello akrun, I have edited the example, 'users' is the sum of 'usage'. — Alexis, Jul 18 '19 at 01:56
Hello, I added what 'usage' is, is defined as the name of the third column. — Alexis, Jul 18 '19 at 02:00

score 2 · Answer 1 · answered Jul 18 '19 at 02:12

Here's the answer using dplyr

bookplace <- data.frame(c("reading", "reading", "reading", 
                          "reading", "lending", "lending"), 
                        c("male", "female", "male", "female", "male", "female"), 
                        c(103, 102, 23, 14, 16, 8), 
                        c("1/1/18","1/1/18","1/1/18","1/1/18","1/1/18","1/1/18"))
colnames(bookplace) <- c("type","Gender","Usage","Year")
bookplace$Year <- as.Date(bookplace$Year, format = "%d/%m/%Y")
check <- bookplace%>%group_by(Year,type)%>%summarise(Users = sum(Usage),male = sum(Usage[ Gender =="male"]),
                                                     female = sum(Usage[Gender == "female"]))

i got the idea from this question Summarize with conditions in dplyr

Hello @Omar Abd El-Naser your solution worked good too. Thank you. — Alexis, Jul 18 '19 at 02:54

score 1 · Accepted Answer · answered Jul 18 '19 at 02:08

A tidyverse solution. Assuming that dates are %m/%d/%y. If not, change the format string accordingly.

library(dplyr)
library(tidyr)

bookplace %>% 
  mutate(year = format(as.Date("1/1/18", "%m/%d/%y"), "%Y")) %>% 
  group_by(year, sex, type) %>% 
  summarise(Total = sum(usage)) %>% 
  ungroup() %>% 
  spread(sex, Total) %>% 
  mutate(users = female + male)

Result:

# A tibble: 2 x 5
  year  type    female  male users
  <chr> <chr>    <dbl> <dbl> <dbl>
1 2018  lending      8    16    24
2 2018  reading    116   126   242

Is the solution I needed. Thank you very much @neilfws – Alexis Jul 18 '19 at 02:53 — Alexis, Jul 18 '19 at 02:53

Additional columns for a summarised DataFrame

2 Answers2