How to change or replace row names based on dictionary

Question

I want to change the ICD-10 code in a row to the name of the disease, according to the ICD-10 code dictionary provided.

This is initial data

id <- c("1","2","3")
Dx1 <- c("E119", "I251","I20")
Dx2 <- c("I20", "I251","E119")
Dx3 <- c("I251", "E119","I20")
df <- data.frame(id,Dx1,Dx2,Dx3)
df

This is the ICD-10 code dictionary, in this example there are 3 codes, but in reality, the ICD-10 code contains 94 thousand codes.

ICD <- c("I251", "E119","I20")
Disease <- c("Acute Myocard Infarct", "Type 2 Diabetes", "Chest Pain")
CodeDictionary <- data.frame(ICD,Disease)
CodeDictionary

and this is my target

id <- c("1","2","3")
Dx1 <- c("Type 2 Diabetes", "Acute Myocard Infarct","Chest Pain")
Dx2 <- c("Chest Pain", "Acute Myocard Infarct","Type 2 Diabetes")
Dx3 <- c("Acute Myocard Infarct", "Type 2 Diabetes","I20")
dfGoal <- data.frame(id,Dx1,Dx2,Dx3)
dfGoal

I tried the inner join from dplyr but it didn't work. Thank you for your help!

score 1 · Accepted Answer · answered Jun 29 '20 at 16:58

You can use the plyr package to do this with the function mapvalues(...). However, you have to iterate over the columns which is not ideal.

library(plyr)

id <- c("1","2","3")
Dx1 <- c("E119", "I251","I20")
Dx2 <- c("I20", "I251","E119")
Dx3 <- c("I251", "E119","I20")
df <- data.frame(id,Dx1,Dx2,Dx3)

dfGoal <- df

for(i in c(2:dim(df)[2])){
  dfGoal[,i] <- mapvalues(df[,i],
  from = c("I251", "E119","I20"),
  to = c("Acute Myocard Infarct", "Type 2 Diabetes", "Chest Pain"))
}

dfGoal

There are more techniques that you might find useful in this thread: https://stackoverflow.com/a/25790005/7120715

Thank you, sir! this solved my problem. and the thread was very useful — Dite Bayu, Jun 30 '20 at 12:55

Jeffrey Brabec · Answer 2 · 2020-06-29T16:54:58.893

I think inner_join was the right direction. Before that though you'll want to use pivot_longer and then once you have the disease name you can use pivot_wider to get the your dfGoal:

library(tidyverse)

id <- c("1","2","3")
Dx1 <- c("E119", "I251","I20")
Dx2 <- c("I20", "I251","E119")
Dx3 <- c("I251", "E119","I20")
df <- data.frame(id,Dx1,Dx2,Dx3)
df


ICD <- c("I251", "E119","I20")
Disease <- c("Acute Myocard Infarct", "Type 2 Diabetes", "Chest Pain")
CodeDictionary <- data.frame(ICD,Disease)
CodeDictionary

id <- c("1","2","3")
Dx1 <- c("Type 2 Diabetes", "Acute Myocard Infarct","Chest Pain")
Dx2 <- c("Chest Pain", "Acute Myocard Infarct","Type 2 Diabetes")
Dx3 <- c("Acute Myocard Infarct", "Type 2 Diabetes","I20")
dfGoal <- data.frame(id,Dx1,Dx2,Dx3)
dfGoal

df_goal <- df %>%
  pivot_longer(-id, names_to = "diagnoses", values_to = "ICD") %>%
  inner_join(CodeDictionary, by = "ICD") %>%
  select(id, diagnoses, Disease) %>%
  pivot_wider(names_from = diagnoses, values_from = Disease)

Hope this helps!

How to change or replace row names based on dictionary

2 Answers2