Joe Crozier
Joe Crozier

Reputation: 1036

R tidyverse, Reshaping data to one row per subject, but several columns are affected

I have a sample data frame like this:

sample2<-structure(list(`Full Name` = c("Smith, Jane", NA, NA, NA, 
                           NA, NA, "Doe, John", NA, NA, NA), `Age 
           (Y)` = c("24", 
                    NA, NA, NA, NA, NA, "22", NA, NA, NA), Gender = c("F", NA, NA, 
                                                                      NA, NA, NA, "M", NA, NA, NA), `Procedure Performed 
           (ICD9 Code)` = c("34.04 INSERTION OF INTERCOSTAL CATHETER FOR DRAINAGE", 
                            "86.59 CLOSURE OF SKIN AND SUBCUTANEOUS TISSUE OTHER SITES", 
                            "87.03 COMPUTERIZED AXIAL TOMOGRAPHY OF HEAD", "88.01 COMPUTERIZED AXIAL TOMOGRAPHY OF ABDOMEN", 
                            "87.41 COMPUTERIZED AXIAL TOMOGRAPHY OF THORAX", NA, "96.04 INSERTION OF ENDOTRACHEAL TUBE", 
                            "57.94 INSERTION OF INDWELLING URINARY CATHETER", "99.29 INJECTION OR INFUSION OF OTHER THERAPEUTIC OR PROPHYLACTIC SUBSTANCE", 
                            "38.02 INCISION OF OTHER VESSELS OF HEAD AND NECK"), `Interventions RH` = c("xray", 
                                                                                                        "CT Head", NA, NA, NA, NA, "CT Chest - Referring Hospital", "Chest Tube Placement", "Ct Head", 
                                                                                                        NA)), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"
                                                                                                        ))

As you can see, there are lots of mostly empty rows. All of the mostly empty rows below a subjects name are associated with that subject. I'd like to reshape my data set to be one row per subject, and I need some help.

I've looked at other answers on here for this, and I've asked my friends about it, and I keep seeing the "gather and then spread it" answer, but this particular situation is challenging me for a few specific reasons:

  1. The rows are empty in the "full name" column that I'd usually key by. I.e. I can't tell R to gather up all the Jane Smith rows, because they don't have her name in them.
  2. There are several columns I want to spread out. I would like to create multiple columns out of the "procedure performed" column, i.e. procedure1, procedure2, etc.. and I'd like to create multiple columns out of the "Interventions Rih" column.
  3. I'm probably going to end up text searching the data for particular words or phrases during the analysis, so if the coding is easier to just squish every procedure for a particular patient into the "procedure performed" column for that patient (in one row), I'm ok with that too.

So for expected output, I am ok with either: enter image description here

or: enter image description here

Thank you for your help!

Upvotes: 1

Views: 333

Answers (3)

Dave Gruenewald
Dave Gruenewald

Reputation: 5689

This can be pretty readily tackled with the newest updates to tidyr v1.0.0.

I'm also going to rename your columns so they are a bit easier to work with:

library(tidyr)
library(dplyr)

sample_pivot_df <- sample2 %>% 
  # First renaming cols, but you can choose not to do this
  rename(full_name = `Full Name`,
         age = `Age 
                        (Y)`,
         gender = Gender,
         procedure = `Procedure Performed 
                        (ICD9 Code)`,
         intervention = `Interventions RH`) %>% 
  # Fill for repeated entries
  fill(full_name, age, gender) %>% 
  # Add a dummy column for number of procedures
  group_by(full_name) %>% 
  mutate(procedure_count = row_number()) %>% 
  ungroup() %>% 
  # pivot the data wider for `procedure` and `intervention`
  pivot_wider(names_from = procedure_count,
              values_from = c(procedure, intervention)) 

Which gives us the following:

> sample_pivot_df
# A tibble: 2 x 15
  full_name age   gender procedure_1 procedure_2 procedure_3 procedure_4 procedure_5 procedure_6
  <chr>     <chr> <chr>  <chr>       <chr>       <chr>       <chr>       <chr>       <chr>      
1 Smith, J… 24    F      34.04 INSE… 86.59 CLOS… 87.03 COMP… 88.01 COMP… 87.41 COMP… NA         
2 Doe, John 22    M      96.04 INSE… 57.94 INSE… 99.29 INJE… 38.02 INCI… NA          NA         
# … with 6 more variables: intervention_1 <chr>, intervention_2 <chr>, intervention_3 <chr>,
#   intervention_4 <chr>, intervention_5 <chr>, intervention_6 <chr>

The caveat here is that using tidyr::fill may give you unintended results if any patients have an NA for age or gender.

Upvotes: 1

Fino
Fino

Reputation: 1784

You can also do this using data.table for the aggregation and zoo to fill NA values. I've changed your column names to make the code more readable.

library(data.table)
library(zoo)
setDT(sample2)
names(sample2) <- c("Name", "Age", "Gender", "Procedure", "Interventions")
sample2[, Name := na.locf(Name)] 


newSample = sample2[,.(
  Age = first(Age),
  Gender = first(Gender),
  aggProcedure = paste(Procedure[!is.na(Procedure)],collapse=","),
  aggInterventions = paste(Interventions[!is.na(Interventions)],collapse=",")), 

  by= Name]

Upvotes: 3

Nareman Darwish
Nareman Darwish

Reputation: 1261

You can do that in a much easier format first you use fill() function which fills null values with previous data, then you group your data and summarize it with pasting/concatenating it.

library(dplyr)
library(tidyr)

sample2<-
  structure(list(
    `Full Name` = c("Smith, Jane", NA, NA, NA, NA, NA, "Doe, John", NA, NA, NA), 
    `Age (Y)` = c("24", NA, NA, NA, NA, NA, "22", NA, NA, NA), 
    Gender = c("F", NA, NA, NA, NA, NA, "M", NA, NA, NA), 
    `Procedure Performed (ICD9 Code)` = 
      c("34.04 INSERTION OF INTERCOSTAL CATHETER FOR DRAINAGE",
        "86.59 CLOSURE OF SKIN AND SUBCUTANEOUS TISSUE OTHER SITES", 
        "87.03 COMPUTERIZED AXIAL TOMOGRAPHY OF HEAD", 
        "88.01 COMPUTERIZED AXIAL TOMOGRAPHY OF ABDOMEN", 
        "87.41 COMPUTERIZED AXIAL TOMOGRAPHY OF THORAX", 
        NA, 
        "96.04 INSERTION OF ENDOTRACHEAL TUBE",
        "57.94 INSERTION OF INDWELLING URINARY CATHETER", 
        "99.29 INJECTION OR INFUSION OF OTHER THERAPEUTIC OR PROPHYLACTIC SUBSTANCE", 
        "38.02 INCISION OF OTHER VESSELS OF HEAD AND NECK"), 
    `Interventions RH` = c("xray", "CT Head", NA, NA, NA, NA, 
                           "CT Chest - Referring Hospital", "Chest Tube Placement", 
                           "Ct Head", NA)), 
    row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"))

new_sample <-
  sample2 %>%
  fill("Full Name") %>%
  fill("Age (Y)") %>%
  fill("Gender") %>%
  mutate(`Procedure Performed (ICD9 Code)` = replace_na(`Procedure Performed (ICD9 Code)`, ""),
         `Interventions RH` = replace_na(`Interventions RH`, "")) %>%
  group_by(`Full Name`, `Age (Y)`, `Gender`) %>%
  summarise(`Procedure Performed (ICD9 Code)` = 
              paste(`Procedure Performed (ICD9 Code)`, collapse =" "),
            `Interventions RH` = 
              paste(`Interventions RH`, collapse = " "))

Upvotes: 2

Related Questions