r - 汇总和传播数据

Question

我有类似下面的数据:

df=data.frame(
company=c("McD","McD","McD","KFC","KFC"),
Title=c("Crew Member","Manager","Trainer","Crew Member","Manager"),
Manhours=c(12,NA,5,13,10)
)
df

我希望对其进行操作并获得如下数据框:

 df=data.frame(
   company=c("KFC", "McD"),
   Manager=c(1,1),
   Surbodinate=c(1,2),
   TotalEmp=c(2,3),
   TotalHours=c(23,17)  
  )

我已经设法对员工及其人数进行了如下操作和分类:

df<- df %>%
   mutate(Role = if_else((Title=="Manager" ),
                         "Manager","Surbodinate"))%>%  
   count(company,  Role) %>%  
   spread(Role, n, fill=0)%>%
   as.data.frame() %>%
   mutate(TotalEmp= select(., Manager:Surbodinate) %>% 
       apply(1, sum, na.rm=TRUE))

另外，我将工时总结如下:

df <- df %>%group_by(company) %>%
    summarize(TotalHours = sum(Manhours, na.rm = TRUE))

我将如何同时组合这两个步骤，或者是否有更清洁/更简单的方法来获得所需的输出？

Answer 1

dplyr 解决方案:

df %>%
    mutate(Title = if_else((Title=="Manager" ),
                          "Manager","Surbodinate")) %>%
    group_by(company) %>%
    summarise(Manager = sum(Title == "Manager"), Subordinate = sum(Title == "Surbodinate"), TotalEmp = n(), Manhours = sum(Manhours, na.rm = TRUE))

  company Manager Subordinate TotalEmp Manhours
  <fct>     <int>       <int>    <int>    <dbl>
1 KFC           1           1        2       23
2 McD           1           2        3       17