Filter rows in relation to value held within the same column

Question

I have a dataset containing the service level agreement (SLA) scores of various suppliers and want to use r/rmarkdown to generate a report each month that highlights the suppliers doing well and those who are underperforming.

A reprex of the data is included below. How can I filter the data based on whether the supplier achieves the benchmark score? I would like it to return the supplier names, SLAs, and scores for those entries where the corresponding benchmark score has not been achieved. "Is the score for this supplier and this SLA higher than the corresponding benchmark SLA score?"

supplier_name <- c("benchmark", "benchmark", "benchmark", "supplier1", "supplier1", "supplier1", "supplier2", "supplier2", "supplier2", "supplier3", "supplier3", "supplier3")
sla <- c("sla1", "sla2", "sla3", "sla1", "sla2", "sla3", "sla1", "sla2", "sla3", "sla1", "sla2", "sla3")
score <- c("100", "95", "100", "100", "100", "99", "100", "98", "100", "80", "82", "95")
df <- tibble(supplier_name, sla, score)

Is my data in a suitable shape? or would it be more appropriate to have a vector containing the benchmark scores to use in some comparison?

Thanks.

What is the expected output for the data shared?

Ronak Shah
– Ronak Shah

2021-09-13 10:52:12 +00:00
Commented Sep 13, 2021 at 10:52 — Ronak Shah
– Ronak Shah, Commented Sep 13, 2021 at 10:52

Peter · Accepted Answer · 2021-09-13 10:57:17Z

If you wanted a graphical approach, you could try...

Note, with the data organised more rationally this could be simplified. It you've got lots of suppliers adding jitter to the points will help distinguish suppliers.

library(tibble)
library(dplyr)
library(ggplot2)


df_bmk <- 
  df %>% 
  filter(supplier_name == "benchmark")

df_sup <- 
  df %>% 
  filter(supplier_name != "benchmark") %>% 
  mutate(score = as.numeric(score))

ggplot(df_sup, aes(sla, score, colour = supplier_name))+
  geom_point()+
  geom_hline(data = df_bmk, aes(yintercept = as.numeric(score)))+
  facet_wrap(~sla, scale = "free_x")+
  labs(x = NULL)+
  theme(axis.text.x = element_blank(),
        axis.ticks.x = element_blank())

^{Created on 2021-09-13 by the reprex package (v2.0.0)}

walter · Accepted Answer · 2021-09-13 10:56:44Z

1

I would split it into two data frames, so that you can join the benchmark onto each supplier.

library(dplyr)
benchmark <- df %>% filter(supplier_name == "benchmark")
suppliers <- df %>% filter(supplier_name != "benchmark")

suppliers %>% left_join(benchmark, by=c("sla"), suffix=c("", ".benchmark")) %>% filter(score < score.benchmark)

edited Sep 13, 2021 at 10:56

answered Sep 13, 2021 at 10:38

walter

5383 silver badges8 bronze badges

Collectives™ on Stack Overflow

Filter rows in relation to value held within the same column

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related