Plot line chart with percentage ratio of EMPLOYEE present and absent count in event by month.

[Nirav]

Rshiny: Plot line chart with percentage ratio of EMPLOYEE present and absent count in event by month.

Here is the sample data but in actuality there are 100+ employees,
So result should be required as line colour code by employee name.

Data set like:

Employee		Status			Month_Yr
A				PRESENT			01/2019
C				PRESENT			01/2019
B				PRESENT			01/2019
C				PRESENT			02/2019
D				PRESENT			03/2019
A				PRESENT			01/2019
B				PRESENT			03/2019
C				PRESENT			01/2019
B				ABSENT			01/2019
D				ABSENT			01/2019
A				ABSENT			01/2019
C				PRESENT			02/2019
B				PRESENT			01/2019
A				PRESENT			02/2019
A				ABSENT			02/2019
D				ABSENT			03/2019
C				PRESENT			01/2019
C				ABSENT			01/2019
C				ABSENT			01/2019
A				ABSENT			02/2019
C				ABSENT			04/2019
B				ABSENT			01/2019

Code I tried:

library(rpivotTable)
rpivotTable(sub_data5,  aggregatorName = "Count as Fraction of Rows",  rows = "Employee",  cols = "Month_Yr",  width = "100%",  height = "1200px", rendererName = "Line Chart")

But I want to display a line plot with average present value from total no. of events employees have attended.

Exmp:
Total events for employee A in 01/2019 is 4 where he was only present in 1 then it should display 25% result for that month. ((1/4) * 100) %

[andresrcs]

You are not showing any code for plotting, can you turn this into a REPRoducible EXample (reprex) to exemplify your problem.

To help you get started here is a reprex of how to calculate proportions by employee

library(dplyr)

sample_df <- data.frame(
  stringsAsFactors = FALSE,
          Employee = c("A","C","B","C","D","A",
                       "B","C","B","D","A","C","B","A","A","D","C","C",
                       "C","A","C","B"),
            Status = c("PRESENT","PRESENT","PRESENT",
                       "PRESENT","PRESENT","PRESENT","PRESENT","PRESENT",
                       "ABSENT","ABSENT","ABSENT","PRESENT","PRESENT",
                       "PRESENT","ABSENT","ABSENT","PRESENT","ABSENT","ABSENT",
                       "ABSENT","ABSENT","ABSENT"),
          Month_Yr = c("01/2019","01/2019","01/2019",
                       "02/2019","03/2019","01/2019","03/2019","01/2019",
                       "01/2019","01/2019","01/2019","02/2019","01/2019",
                       "02/2019","02/2019","03/2019","01/2019","01/2019",
                       "01/2019","02/2019","04/2019","01/2019")
)

sample_df %>% 
    count(Month_Yr, Employee, Status) %>% 
    group_by(Month_Yr, Employee) %>% 
    mutate(Prop = n/sum(n))
#> # A tibble: 14 x 5
#> # Groups:   Month_Yr, Employee [9]
#>    Month_Yr Employee Status      n  Prop
#>    <chr>    <chr>    <chr>   <int> <dbl>
#>  1 01/2019  A        ABSENT      1 0.333
#>  2 01/2019  A        PRESENT     2 0.667
#>  3 01/2019  B        ABSENT      2 0.5  
#>  4 01/2019  B        PRESENT     2 0.5  
#>  5 01/2019  C        ABSENT      2 0.4  
#>  6 01/2019  C        PRESENT     3 0.6  
#>  7 01/2019  D        ABSENT      1 1    
#>  8 02/2019  A        ABSENT      2 0.667
#>  9 02/2019  A        PRESENT     1 0.333
#> 10 02/2019  C        PRESENT     2 1    
#> 11 03/2019  B        PRESENT     1 1    
#> 12 03/2019  D        ABSENT      1 0.5  
#> 13 03/2019  D        PRESENT     1 0.5  
#> 14 04/2019  C        ABSENT      1 1

^{Created on 2020-03-17 by the reprex package (v0.3.0.9001)}

[Nirav]

Thank you, its working

# Calculate total count of status and add new column for its percentage value.
sub_data5 <- sub_data5 %>% 
    count(Month_yr, Employee, Status) %>% 
    group_by(Month_yr, Employee) %>% 
    mutate(Percent = (n/sum(n))*100)

# Convert Percentage decimal to string 
sub_data5$`Percent `  <- (as.integer(sub_data5$`Percent`)+" %")

# Drop row values with ABSENT
sub_data5<-sub_data5[!(sub_data5$Status=="ABSENT"),]

#Employee can't use in plot because it's used as grouping so convert DF in to new DF
sub_data10 <- as.data.frame(sub_data5)

# Plot line chart.
plo <- ggplot(data=sub_data10, aes(x=Month_yr, y=Precent, group=Employee, colour=Employee)) +
    geom_line() +
    geom_point()

fig <- ggplotly(plo)

fig

[andresrcs]

This would be a way to do the same but with tidyverse syntax

library(tidyverse)
library(scales)

sample_df <- data.frame(
    stringsAsFactors = FALSE,
    Employee = c("A","C","B","C","D","A",
                 "B","C","B","D","A","C","B","A","A","D","C","C",
                 "C","A","C","B"),
    Status = c("PRESENT","PRESENT","PRESENT",
               "PRESENT","PRESENT","PRESENT","PRESENT","PRESENT",
               "ABSENT","ABSENT","ABSENT","PRESENT","PRESENT",
               "PRESENT","ABSENT","ABSENT","PRESENT","ABSENT","ABSENT",
               "ABSENT","ABSENT","ABSENT"),
    Month_Yr = c("01/2019","01/2019","01/2019",
                 "02/2019","03/2019","01/2019","03/2019","01/2019",
                 "01/2019","01/2019","01/2019","02/2019","01/2019",
                 "02/2019","02/2019","03/2019","01/2019","01/2019",
                 "01/2019","02/2019","04/2019","01/2019")
)

sample_df %>% 
    count(Month_Yr, Employee, Status) %>% 
    group_by(Month_Yr, Employee) %>% 
    mutate(Percent= n/sum(n)) %>% 
    filter(Status == "ABSENT") %>% 
    ggplot(aes(x = Month_Yr, y = Percent, colour=Employee)) +
    geom_line() +
    geom_point() +
    scale_y_continuous(labels = percent_format())