group by in dplyr and calculating percentages

Viewed 28049

I have following dataframe in r

   Service      Container_Pick_Day
    ABC              0
    ABC              1
    ABC              1
    ABC              2
    ABC              NA
    ABC              0
    ABC              1
    DEF              NA
    DEF              0
    DEF              1
    DEF              1
    DEF              1
    DEF              2
    DEF              1

Column Container_Pick_Day is numeric and consist of NA values. What I want to do is calculate Service wise percentage of containers picked up on 0th day,after 1 day,2 day and so on ignoring NA values

Desired dataframe would be

 Service      Container_Pick_Day      Percentage
    ABC              0                (2/6)*100 = 33.33 
    ABC              1                (3/6)*100 = 50  
    ABC              2                (1/6)*100 = 16.67
    DEF              0                (1/6)*100 = 16.67
    DEF              1                (3/6)*100 = 50
    DEF              2                (1/6)*100 = 16.67

I did following in R,but its generating NA values in output

  df%>% 
     group_by(Service) %>%
     summarise(pick_day_perc = n()/sum(Container_Pick_Day),na.rm=T) %>% 
     as.data.frame()

Do I have to group by Service and Container_Pick_Day both ?

1 Answers
Related