CODEWITHSUNDEEP
rdplyr
SS_BPS
SS_BPS

Reputation: 1

How to subtract specific values within columns in R with dplyr

I have a df like below and need to subtract cost_amt for site = Inpatient vs site = NASON for component = Total, scenario = Base. This value should = 81611-48624. I've seen code for subtracting columns from each other, but I just want to subtract these two rows within the specific columns. 

> costs
        site       component cost_amt scenario
1  Inpatient           Total    81611     Base
2  Inpatient Hospitalization    53360     Base
3  Inpatient      Procedures    15690     Base
4  Inpatient           Drugs    12561     Base
5      NASON           Total    48624     Base
6      NASON Hospitalization    23526     Base
7      NASON      Procedures    12537     Base
8      NASON           Drugs    12561     Base
9  Inpatient           Total    58776 Scenario
10 Inpatient Hospitalization    42003 Scenario
11 Inpatient      Procedures    10313 Scenario
12 Inpatient           Drugs     6460 Scenario
13     NASON           Total    31482 Scenario
14     NASON Hospitalization    16304 Scenario
15     NASON      Procedures     8718 Scenario
16     NASON           Drugs     6460 Scenario

Upvotes: 0

Views: 47

Answers (2)

akrun
akrun

Reputation: 886948

An option with dcast

library(data.table)
dcast(setDT(costs), scenario + component ~ site, 
     value.var = 'cost_amt')[, value := Inpatient -NASON][]
#   scenario       component Inpatient NASON value
#1:     Base           Drugs     12561 12561     0
#2:     Base Hospitalization     53360 23526 29834
#3:     Base      Procedures     15690 12537  3153
#4:     Base           Total     81611 48624 32987
#5: Scenario           Drugs      6460  6460     0
#6: Scenario Hospitalization     42003 16304 25699
#7: Scenario      Procedures     10313  8718  1595
#8: Scenario           Total     58776 31482 27294

data

costs <- structure(list(site = c("Inpatient", "Inpatient", "Inpatient", 
"Inpatient", "NASON", "NASON", "NASON", "NASON", "Inpatient", 
"Inpatient", "Inpatient", "Inpatient", "NASON", "NASON", "NASON", 
"NASON"), component = c("Total", "Hospitalization", "Procedures", 
"Drugs", "Total", "Hospitalization", "Procedures", "Drugs", "Total", 
"Hospitalization", "Procedures", "Drugs", "Total", "Hospitalization", 
"Procedures", "Drugs"), cost_amt = c(81611L, 53360L, 15690L, 
12561L, 48624L, 23526L, 12537L, 12561L, 58776L, 42003L, 10313L, 
6460L, 31482L, 16304L, 8718L, 6460L), scenario = c("Base", "Base", 
"Base", "Base", "Base", "Base", "Base", "Base", "Scenario", "Scenario", 
"Scenario", "Scenario", "Scenario", "Scenario", "Scenario", "Scenario"
)), class = "data.frame", row.names = c("1", "2", "3", "4", "5", 
"6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16"))

Upvotes: 1

r2evans
r2evans

Reputation: 160407

library(dplyr)
dat %>%
  pivot_wider(
    id_cols = c("scenario", "component"),
    names_from = "site",
    values_from = "cost_amt"
  ) %>%
  mutate(
    value = Inpatient - NASON
  )
# # A tibble: 8 x 5
#   scenario component       Inpatient NASON value
#   <chr>    <chr>               <int> <int> <int>
# 1 Base     Total               81611 48624 32987
# 2 Base     Hospitalization     53360 23526 29834
# 3 Base     Procedures          15690 12537  3153
# 4 Base     Drugs               12561 12561     0
# 5 Scenario Total               58776 31482 27294
# 6 Scenario Hospitalization     42003 16304 25699
# 7 Scenario Procedures          10313  8718  1595
# 8 Scenario Drugs                6460  6460     0

Data:

dat <- read.table(header=TRUE, stringsAsFactors=FALSE, text="
     site       component cost_amt scenario
Inpatient           Total    81611     Base
Inpatient Hospitalization    53360     Base
Inpatient      Procedures    15690     Base
Inpatient           Drugs    12561     Base
    NASON           Total    48624     Base
    NASON Hospitalization    23526     Base
    NASON      Procedures    12537     Base
    NASON           Drugs    12561     Base
Inpatient           Total    58776 Scenario
Inpatient Hospitalization    42003 Scenario
Inpatient      Procedures    10313 Scenario
Inpatient           Drugs     6460 Scenario
    NASON           Total    31482 Scenario
    NASON Hospitalization    16304 Scenario
    NASON      Procedures     8718 Scenario
    NASON           Drugs     6460 Scenario")

Upvotes: 2

Related Questions