#Introduction

Every pharmacy buyer eventually faces the same questions: What is running low? What is about to expire? Where is our money sitting on the shelf Most of the time those answers live in a spreadsheet, and getting them means a lot of sorting filters, and hand-built formulas.

This code-through shows how to answer those questions in R with dplyr, the tidyverse package for data manipulation. We will work through the six dplyr “verbs” used most ofen, one at a time, on na small inventory dataset. At the end, we chain them together inot a one-step reorder report.

The scenario: You are the buyer for a hospital infusion pharmacy, It is the start of the month, and your manager wants to know (1) which items need to be reordered, (2) what is close to expiring, and (3) how much inventory value each drug class is holding.

What you will learn

Verb What it does Spreadsheet equivalent
select() Keep or drop columns Hiding columns
filter() Keep rows that meet a condition AutoFilter
arrange() Sort rows Sort A-Z /Z-A
mutate() Create or change columns Writing a formula in a new column
group_by() + summarize() Collapse groups into summary rows Pivot table
%>% (the pipe) Chain steps together Doing one step after another

Setup

Install dplyr once with install.packages("dplyr"), the load it at the top of every script. We will also load ggplot2 for one chart at the end.

library(dplyr)
library(ggplot2)

The Toy Dataset

Real inventory data is proprietarty, so this tutorial uses a small made-up data set of 12 infusion and injectable products. The drug names are real, but every count, cost and date is invented for teaching.

inventory <- data.frame( 
  drug    = c("Vancomycin 1 g vial", "Ceftriaxone 1 g vial", "Cefazolin 2 g vial", "Piperacillin-Tazobcatam 4.5 g", "Meropenem 1 g vial", "Heparin 5,000 units/mL", "Enoxaparin 40 mg syringe", "Ondansetron 4 mg/2 mL", "Pantoprazole 40 mg vial", "Infliximab 100 mg vial", "Iron Sucrose 100 mg/5 mL", "Normal Saline 1 L bag"),
  drug_class = c("Antibiotic", "Antibiotic", "Antibiotic", "Antibiotic", "Antibiotic", "Anticoagulant", "Anticoagulant", "Antiemetic", "PPI", "Monoclonal Antibody", "Hematologic", "IV Fluid"),
  on_hand = c(40, 120, 35, 8, 22, 60, 150, 200, 45, 6, 30, 400),
  par_level = c(80, 100, 60, 40, 20, 50, 120, 150, 60, 10, 25, 500),
  monthly_use = c(95,110, 70, 45, 15, 40, 130, 180, 50, 8, 20, 600),
  unit_cost = c(4.5, 1.25, 3.10, 9.80, 7.40, 2.20, 3.75, 0.45, 2.90, 780.00, 38.50, 1.10),
  expiration = as.Date(c("2027-03-31", "2026-11-30", "2027-07-31", "2026-12-31",
                         "2027-01-31", "2026-10-15", "2027-08-31", "2029-02-24",
                         "2026-11-05", "2026-12-31", "2027-09-30", "2027-02-28"))
)

Before manipulating any dataset, look at it. glimpse() shows every column, its type, and the first few values - sideways, so it fits on the screen no matter how many columns you have.

glimpse(inventory)
## Rows: 12
## Columns: 7
## $ drug        <chr> "Vancomycin 1 g vial", "Ceftriaxone 1 g vial", "Cefazolin …
## $ drug_class  <chr> "Antibiotic", "Antibiotic", "Antibiotic", "Antibiotic", "A…
## $ on_hand     <dbl> 40, 120, 35, 8, 22, 60, 150, 200, 45, 6, 30, 400
## $ par_level   <dbl> 80, 100, 60, 40, 20, 50, 120, 150, 60, 10, 25, 500
## $ monthly_use <dbl> 95, 110, 70, 45, 15, 40, 130, 180, 50, 8, 20, 600
## $ unit_cost   <dbl> 4.50, 1.25, 3.10, 9.80, 7.40, 2.20, 3.75, 0.45, 2.90, 780.…
## $ expiration  <date> 2027-03-31, 2026-11-30, 2027-07-31, 2026-12-31, 2027-01-31…

Reading the glimpse <chr> is text, <dbl> is a number and <date> is a date. Having expiration stored a a true date and not text, is what will let us do date math later

The Pipe: %>%

Before the verbs, one piece of syntax. The pipe %>% takes whatever is on its left and passes it in as the first arguement of the function on its right. It’s read outloud as “and then.”

# These two lines to the exact same thing: 
head(inventory, 3)
inventory %>%  head(3) # "take, inventory AND THEN showw the first 3 rows"

The pipe does not matter much for one step. It matters a lot whe you strong five steps together, because the code reads top to bottom in the same order you think about the problem.

Verb 1: select() - Choose Columns

The Pharmacist in Charge does not need every column for a quick stock check. select() keeps only the columns you name.

inventory %>% 
  select(drug, on_hand, par_level)
drug on_hand par_level
Vancomycin 1 g vial 40 80
Ceftriaxone 1 g vial 120 100
Cefazolin 2 g vial 35 60
Piperacillin-Tazobcatam 4.5 g 8 40
Meropenem 1 g vial 22 20
Heparin 5,000 units/mL 60 50
Enoxaparin 40 mg syringe 150 120
Ondansetron 4 mg/2 mL 200 150
Pantoprazole 40 mg vial 45 60
Infliximab 100 mg vial 6 10
Iron Sucrose 100 mg/5 mL 30 25
Normal Saline 1 L bag 400 500

You can also drop columns with a minus sign, or grab a range with a colon:

inventory %>% 
  select (-unit_cost, -expiration) %>% 
  head(3)
drug drug_class on_hand par_level monthly_use
Vancomycin 1 g vial Antibiotic 40 80 95
Ceftriaxone 1 g vial Antibiotic 120 100 110
Cefazolin 2 g vial Antibiotic 35 60 70 
inventory %>% 
  select(drug:par_level) %>% 
  head(3)
drug drug_class on_hand par_level
Vancomycin 1 g vial Antibiotic 40 80
Ceftriaxone 1 g vial Antibiotic 120 100
Cefazolin 2 g vial Antibiotic 35 60

Verb 2: filter() - Choose Rows

filter() keeps only the rows where a condition is TRUE. Which items are below thier par level?

inventory %>% 
  filter(on_hand < par_level) %>% 
  select(drug, on_hand, par_level)
drug on_hand par_level
Vancomycin 1 g vial 40 80
Cefazolin 2 g vial 35 60
Piperacillin-Tazobcatam 4.5 g 8 40
Pantoprazole 40 mg vial 45 60
Infliximab 100 mg vial 6 10
Normal Saline 1 L bag 400 500

Six of the twelve items are below par. Conditions can be combined: a comma or & means AND, and | means OR.

# Antibiotics that are below par
inventory %>% 
  filter(drug_class == "Antibiotic", on_hand < par_level) %>% 
  select(drug, drug_class)
drug drug_class
Vancomycin 1 g vial Antibiotic
Cefazolin 2 g vial Antibiotic
Piperacillin-Tazobcatam 4.5 g Antibiotic 
#Anything that is a Monoclonal Antibody OR costs more than $30 per unit

inventory %>% 
  filter(drug_class == "Monoclonal Antibody" | unit_cost > 30) %>% 
  select(drug, drug_class, unit_cost)
drug drug_class unit_cost
Infliximab 100 mg vial Monoclonal Antibody 780.0
Iron Sucrose 100 mg/5 mL Hematologic 38.5

To match against a list of values, %in% is cleaner than a long chain og |:

inventory %>% 
  filter(drug_class %in% c("Anticoagulant", "Hematologic")) %>% 
  select(drug, drug_class)
drug drug_class
Heparin 5,000 units/mL Anticoagulant
Enoxaparin 40 mg syringe Anticoagulant
Iron Sucrose 100 mg/5 mL Hematologic

Common mistake: filter(druv_class = "Antibiotic) with a single = throws an error. Inside filter(), you are testing equality, so you need a double ==.

Verb 3: arrange() - Sort Rows

arrange() sorts from smallest to largest by default. Wrap a column in desc() ti flip it, What expires first?

inventory %>% 
  arrange(expiration) %>% 
  select(drug,expiration) %>% 
  head(5)
drug expiration
Heparin 5,000 units/mL 2026-10-15
Pantoprazole 40 mg vial 2026-11-05
Ceftriaxone 1 g vial 2026-11-30
Piperacillin-Tazobcatam 4.5 g 2026-12-31
Infliximab 100 mg vial 2026-12-31

And which items tie up the most money per unit?

inventory %>% 
  arrange(desc(unit_cost)) %>% 
  select(drug,unit_cost) %>% 
  head(5)
drug unit_cost
Infliximab 100 mg vial 780.0
Iron Sucrose 100 mg/5 mL 38.5
Piperacillin-Tazobcatam 4.5 g 9.8
Meropenem 1 g vial 7.4
Vancomycin 1 g vial 4.5

Verb 4: mutate() - Create New Columns

mutate() is where dplyr starts doing real work for a buyer. It addes new columns calculated from existing ones, just like typing a formula into a new spreadsheet column - except it applies to every row at once and never gets dragged down teh wrong range.

We will add four columns a buyer actually uses:

  • inventory_value - how much money is sitting on the shelf (on_hand× unit_cost)
  • days_supply - how many days the current stock will last at our usual rate of use
  • days_to_expire - days between today’s review and the epiration date
  • reorder_qty - how many units bring us back up to par (zero if we are already there)
review_date <- as.Date("2026-10-09") # fixed date so the results are reproducible 

inventory_plus <- inventory %>% 
  mutate(
    inventory_value = on_hand * unit_cost, 
    days_supply     = round(on_hand / (monthly_use / 30),1),
    days_to_expire  = as.numeric(expiration - review_date),
    reorder_qty.    = pmax(par_level - on_hand,0)
  )

Here is the before and after. Compare the column list to the glimpse() from earlier - four new columns now sit at the end.

glimpse(inventory_plus)
## Rows: 12
## Columns: 11
## $ drug            <chr> "Vancomycin 1 g vial", "Ceftriaxone 1 g vial", "Cefazo…
## $ drug_class      <chr> "Antibiotic", "Antibiotic", "Antibiotic", "Antibiotic"…
## $ on_hand         <dbl> 40, 120, 35, 8, 22, 60, 150, 200, 45, 6, 30, 400
## $ par_level       <dbl> 80, 100, 60, 40, 20, 50, 120, 150, 60, 10, 25, 500
## $ monthly_use     <dbl> 95, 110, 70, 45, 15, 40, 130, 180, 50, 8, 20, 600
## $ unit_cost       <dbl> 4.50, 1.25, 3.10, 9.80, 7.40, 2.20, 3.75, 0.45, 2.90, …
## $ expiration      <date> 2027-03-31, 2026-11-30, 2027-07-31, 2026-12-31, 2027-0…
## $ inventory_value <dbl> 180.0, 150.0, 108.5, 78.4, 162.8, 132.0, 562.5, 90.0, …
## $ days_supply     <dbl> 12.6, 32.7, 15.0, 5.3, 44.0, 45.0, 34.6, 33.3, 27.0, 2…
## $ days_to_expire  <dbl> 173, 52, 295, 83, 114, 6, 326, 869, 27, 83, 356, 142
## $ reorder_qty.    <dbl> 40, 0, 25, 32, 0, 0, 0, 0, 15, 4, 0, 100

Why pmax() and not max()? max() returns one number for the whole column. pmax() (“parallel max”) compares row by row, so each drug gets its own answer: either the shortfall or zero.

Labeling rows with case_when()

A number like days_to_expire = 14 is useful, but a label is faster to scan. case_when() works liek a series of IF/ELSE statements inside mutate(). R checks each condition in order and uses the first one that is TRUE.

inventory_plus <- inventory_plus %>% 
  mutate(
    expiry_flag = case_when(
      days_to_expire <= 30 ~ "Expires < 30 days",
      days_to_expire <= 90 ~ "Expires < 90 days",
      TRUE                 ~  "OK" #everything else
    ), 
    stock_status = case_when(
      days_supply < 7          ~ "Critical",
      on_hand     < par_level   ~ "Below par",
      on_hand     > par_level* 1.2 ~ "Overstocked",
      TRUE   ~ "At par"
      
    )
  )

inventory_plus %>% 
  select(drug, days_supply, stock_status, days_to_expire, expiry_flag)
drug days_supply stock_status days_to_expire expiry_flag
Vancomycin 1 g vial 12.6 Below par 173 OK
Ceftriaxone 1 g vial 32.7 At par 52 Expires < 90 days
Cefazolin 2 g vial 15.0 Below par 295 OK
Piperacillin-Tazobcatam 4.5 g 5.3 Critical 83 Expires < 90 days
Meropenem 1 g vial 44.0 At par 114 OK
Heparin 5,000 units/mL 45.0 At par 6 Expires < 30 days
Enoxaparin 40 mg syringe 34.6 Overstocked 326 OK
Ondansetron 4 mg/2 mL 33.3 Overstocked 869 OK
Pantoprazole 40 mg vial 27.0 Below par 27 Expires < 30 days
Infliximab 100 mg vial 22.5 Below par 83 Expires < 90 days
Iron Sucrose 100 mg/5 mL 45.0 At par 356 OK
Normal Saline 1 L bag 20.0 Below par 142 OK

Right away, the table shows that Piperacillin-Taxobactam is both running critically low 8and* close to expiring - a good candidate fora. smaller, more frequent order.

Common mistake: mutate() does not change the original data unless you save the result with <-. If your new column “dissappears,” check that you assigned it back to an object.

Verb 5: group_by() + summarize() - Pivot Tables in Code

summarize() collapses many rows into one summary row. On its own, it summarizes the whol dataset:

inventory_plus %>% 
  summarise(
    total_items = n(),
    total_value = sum(inventory_value)
  )
total_items total_value
12 7869.7

Add group_by first, and you get one summary row per group - the same idea as a pivot table. Hos is inventory value spread across drug classes?

class_summary <- inventory_plus %>% 
  group_by(drug_class) %>% 
  summarize(
    items = n(),
    total_value = sum(inventory_value),
    items_below_par = sum(on_hand < par_level),
    avg_days_supply = round(mean(days_supply),1)
  ) %>% 
  arrange(desc(total_value))

class_summary
drug_class items total_value items_below_par avg_days_supply
Monoclonal Antibody 1 4680.0 1 22.5
Hematologic 1 1155.0 0 45.0
Anticoagulant 2 694.5 0 39.8
Antibiotic 5 679.7 3 21.9
IV Fluid 1 440.0 1 20.0
PPI 1 130.5 1 27.0
Antiemetic 1 90.0 0 33.3

A trick inside summarize(): sum(on_hand < par_level) counts rows. The comparison makes a column of TRUE / FALSE, and R treats TRUE as 1 and FALSE as 0 when adding them up.

One Monoclonal Antibody item (6 vials of Infliximab) holds more value than all five antibiotics combined. That is a classical finding in pharmacy inventory: a few high-cost products drive most of the dollars, so they deserve the tightest par levels.

A quick chart makes the point hard to miss:

ggplot(class_summary, aes(x = reorder(drug_class, total_value), y = total_value))+
  geom_col(fill = "#18bc9c") +
  geom_text(aes(label = scales::dollar(total_value)), hjust = -0.1, size = 3.5) +
  coord_flip() +
  scale_y_continuous(labels = scales::dollar, expand = expansion(mult = c(0, 0.2)))+
  labs(title = "Inventory Value by Drug Class",
       subtitle = "One high-cost biologic outweighs every other class",
       x = NULL, y = "Value on hand") +
theme_minimal(base_size = 12)

Common mistake: A grouped data frame stays grouped. If you keep piping into more mutate() or summarize() steps, they will still run per grpup. Add ungroup() when you are ginished with the grouping.

Putting it all together: The Reorder Report

Here is the real payoff. Each verb on its own is small; chained toether with the pipe, they become a complete, repeatable workflow. This single block starts from the raw inventory and produces the report the manager has asked for:

reorder_report <- inventory %>% 
  mutate(
    days_supply = round(on_hand / (monthly_use /30),1),
    days_to_expire = as.numeric(expiration - review_date),
    reorder_qty = pmax(par_level - on_hand, 0),
    order_cost = reorder_qty * unit_cost
  ) %>% 
  filter(reorder_qty > 0 ) %>% 
  arrange(days_supply) %>%
  select(drug, on_hand, par_level, days_supply, reorder_qty, order_cost, days_to_expire)

reorder_report
drug on_hand par_level days_supply reorder_qty order_cost days_to_expire
Piperacillin-Tazobcatam 4.5 g 8 40 5.3 32 313.6 83
Vancomycin 1 g vial 40 80 12.6 40 180.0 173
Cefazolin 2 g vial 35 60 15.0 25 77.5 295
Normal Saline 1 L bag 400 500 20.0 100 110.0 142
Infliximab 100 mg vial 6 10 22.5 4 3120.0 83
Pantoprazole 40 mg vial 45 60 27.0 15 43.5 27

And the bottom line for the order:

reorder_report %>% 
  summarize(
    items_to_order = n(),
    units_to_order = sum(reorder_qty),
    total_order_cost = scales::dollar(sum(order_cost))
  )
items_to_order units_to_order total_order_cost
6 216 $3,844.60

Read the chain out loud using “and then”: take the inventory, and then calculate supply and order amounts, and then keep items that need ordering, and then sort by urgency, and then keep the columns that matter.

Why this beats a spreadsheet: Next month, you replace the data with a fresh export and re-run the same block. No formulas to drag down, no filters to reset, and no risk that someone sorted one column without the others. The code is the documentation of how the report was built.

Summary

Question from the buyer dplyr answer
“Show me just the stock columns.” select(drug, on_hand, par_level)
“What is below par?” filter(on_hand < par_level)
“What expires first?” arrange(expiration)
“How many days will this last?” mutate(days_supply = on_hand / (monthly_use / 30))
“Where is our money by class?” group_by(drug_class) %>% summarize(sum(inventory_value))
“Give me the whole reorder list.” Chain them all with %>%

Three habits will save you the most debugging time:

  1. glimpse() before and after every major step so you can see what changed.
  2. Save results with <- — dplyr never changes your data in place.
  3. ungroup() when you are done with grouped calculations.

Resources