Reputation: 1036
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:
So for expected output, I am ok with either:
Thank you for your help!
Upvotes: 1
Views: 333
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
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
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