How to combine values with the same column name into a new dataframe in R?

user_11021403 0
Viewed 47

I have the following dataset

Original dataset:

ID    Col1    Col1    Col1    Col2    Col2    Col2
A     Dog                     House
B     Dog                             Car     Bike
C             Cat             House
D                     Mouse                   Bike

Is there any way to create a new dataframe that combines all values with the same column name like below

Expected dataset:

ID    Col1    Col2    
A     Dog     House
B     Dog     Car, Bike
C     Cat     House
D     Mouse   Bike
r
2 Answers

You could do something like this:

df <- structure(list(
  ID = c("A", "B", "C", "D"),
  Col1 = c("Dog", "Dog", NA, NA),
  Col1 = c(NA, NA, "Cat", NA),
  Col1 = c(NA, NA, NA, "Mouse"),
  Col2 = c("House", NA, "House", NA),
  Col2 = c(NA, "Car", NA, NA),
  Col2 = c(NA, "Bike", NA, "Bike")
),
class = c("data.frame"), row.names = c(NA, -4L)
)

library(dplyr)
library(tidyr)
library(purrr)

vars_to_unite <- unique(names(df))[unique(names(df)) != "ID"]
renamed_df <- as_tibble(df, .name_repair = "unique")

map_dfc(vars_to_unite, 
        ~unite(
          select(renamed_df, starts_with(.x)), 
          col = !!.x, sep = ", ", na.rm = TRUE
        )) %>% 
  mutate(ID = df$ID)

#> # A tibble: 4 × 3
#>   Col1  Col2      ID   
#>   <chr> <chr>     <chr>
#> 1 Dog   House     A    
#> 2 Dog   Car, Bike B    
#> 3 Cat   House     C    
#> 4 Mouse Bike      D

Created on 2022-06-01 by the reprex package (v2.0.1)

user_191453680

Base R solution:

# Input data: df => data.frame
df <- structure(list(
  ID = c("A", "B", "C", "D"),
  Col1 = c("Dog", "Dog", NA, NA),
  Col1 = c(NA, NA, "Cat", NA),
  Col1 = c(NA, NA, NA, "Mouse"),
  Col2 = c("House", NA, "House", NA),
  Col2 = c(NA, "Car", NA, NA),
  Col2 = c(NA, "Bike", NA, "Bike")
),
  class = c("data.frame"), row.names = c(NA, -4L)
)


# Split-Apply-Combine: res => data.frame
res <- data.frame(
  do.call(
    cbind, 
      lapply(
        split.default(
          df,
          names(df)
        ),
        function(x){
          apply(
            x, 
            1, 
            FUN = function(y){
              toString(
                na.omit(y)
              )
            }
          )
        }
      )
  )[,unique(names(df))],
  stringsAsFactors = FALSE,
  row.names = row.names(df)
)

# output Result: data.frame => stdout(console)
res
user_92031580
Related