Nsryjdtyk

I am interested in cycling through a list of diagnosis codes and populating a new variable with a previously calculated risk score if the value matches, and if there are multiple matches populate the new variable with the highest risk score.

I am hoping to take the long form of the original data set and for each ID match the proc number with the highest risk score and store both the proc number and risk score in separate variables.

I have some experience using if loops to do similar things in wide data, but cannot figure out how to do it this way. I have no experience matching and then storing the highest value, so don't even know where to start with this.

Data to see what I am getting at:

Here is the data from for the diagnosis codes

dz <-c("disease_1", "disease_2", "disease_3", "disease_4")

code <-c(124, 546, 890, 898)

risk_score <-c(10, 122, 45, 98)

df <-data.frame(dz, code, risk_score)

And the simulated data set I am interested in

 id <- c(1,1,1,2,2,2,2,3,3,4,4,4,4,4,4,5,5,5)

 proc <-c(244,546,234,345,890,123,434,634,233,345,124,234,634,546,789,890,567,124)

 proc<-as.character(proc)

 data<-data.frame(id, proc)

so what I want to achieve is something like this

id<-c(1,2,3,4,5)

code_match<-c(546,890,124,546,890)

highest_risk_score <-c(122,45,10,122,45)

output_df<-data.frame(id, code_match, highest_risk_score)

with this output

  id code_match highest_risk_score

1  1        546                122

2  2        890                 45

3  3        124                 10

4  4        546                122

5  5        890                 45

with id being the identifier, the code_match being the code with the highest risk score, and the highest_risk_score being the value of the risk score (the highest value for that id).

We'll use an alternate way of creating those data frames:

data.frame(

  dz = c("disease_1", "disease_2", "disease_3", "disease_4"),

  code = as.character(c(124, 546, 890, 898)),

  risk_score = c(10, 122, 45, 98),

  stringsAsFactors = FALSE

) -> df



data.frame(

  id = c(1, 1, 1, 2, 2, 2, 2, 3, 3, 4, 4, 4, 4, 4, 4, 5, 5, 5),

  proc = as.character(c(244, 546, 234, 345, 890, 123, 434, 634, 233, 345, 124, 234, 634, 546, 789, 890, 567, 124)),

  stringsAsFactors = FALSE

) -> data

Here's one way (in tidyverse and base R) to do this:

57 compiled 📦 dependency tidyverse solution:

library(tidyverse)



filter(data, proc %in% df$code) %>%

  left_join(df, by=c("proc"="code")) %>%

  group_by(id) %>%

  top_n(1) %>%

  slice(1) %>%

  select(id, code_match = proc, highest_risk_score = risk_score)

## # A tibble: 4 x 3

## # Groups:   id [4]

##      id code_match highest_risk_score

##   <dbl> <chr>                   <dbl>

## 1    1. 546                      122.

## 2    2. 890                       45.

## 3    4. 546                      122.

## 4    5. 890                       45.

0 📦 (ok, 1 — stats — which comes along for the ride with base R) Base R solution

tmp <- merge(data[with(data, proc %in% df$code),], df, by.x = "proc", by.y = "code")



do.call(

  rbind.data.frame,

  lapply(

    split(tmp, tmp$id),

    function(x) {

      x[which.max(x$risk_score),]

    }

  )

)[,-3] -> tmp



setNames(tmp[,c(2,1,3)], c("id", "code_match", "highest_risk_score"))

##   id code_match highest_risk_score

## 1  1        546                122

## 2  2        890                 45

## 4  4        546                122

## 5  5        890                 45

You've not mentioned how to handle not-matches so this just ignores them.