Merge rows with different values into a single row in R

Aaron Z 0
Viewed 26

I have a dataset that looks like this:

ID           | age | disease
smith192     | 17  | lung_cancer
green484     | 12  | diabetes
green484     | 13  | heart_irregularities
tom584       | 12  | colon_cancer
tom584       | 14  | diabetes
tom584       | 15  | malnutrition

And I would like R to organize it into this:

ID           | age_1 | disease_1    | age_2 | disease_2            | age_3 | disease_3    |
smith192     | 17    | lung_cancer  | NA    | NA                   | NA    | NA           |
green484     | 12    | diabetes     | 13    | heart_irregularities | NA    | NA           |
tom584       | 12    | colon_cancer | 14    | diabetes             | 15    | malnutrition |

Any help would be greatly appreciated!

pivot reshape dataframe r
1 Answers

You could create disease indices for each ID and then pivot the data to wide.

  • base
df |>
  transform(n = ave(ID, ID, FUN = seq)) |>
  reshape(direction = "wide", idvar = "ID", timevar = "n", v.names = c("age", "disease"))

#         ID age.1    disease.1 age.2            disease.2 age.3    disease.3
# 1 smith192    17  lung_cancer    NA                 <NA>    NA         <NA>
# 2 green484    12     diabetes    13 heart_irregularities    NA         <NA>
# 4   tom584    12 colon_cancer    14             diabetes    15 malnutrition
  • tidyverse
library(dplyr)
library(tidyr)

df %>%
  group_by(ID) %>%
  mutate(n = 1:n()) %>%
  ungroup() %>%
  pivot_wider(ID, names_from = n, values_from = c(age, disease))

# # A tibble: 3 × 7
#   ID       age_1 age_2 age_3 disease_1    disease_2            disease_3
#   <chr>    <dbl> <dbl> <dbl> <chr>        <chr>                <chr>
# 1 smith192    17    NA    NA lung_cancer  NA                   NA
# 2 green484    12    13    NA diabetes     heart_irregularities NA
# 3 tom584      12    14    15 colon_cancer diabetes             malnutrition
Data
df <- structure(list(ID = c("smith192", "green484", "green484", "tom584",
"tom584", "tom584"), age = c(17, 12, 13, 12, 14, 15), disease = c("lung_cancer",
"diabetes", "heart_irregularities", "colon_cancer", "diabetes",
"malnutrition")), class = "data.frame", row.names = c(NA, -6L))
Darren Tsai0
Related