CODEWITHSUNDEEP
rdplyrdata.tabletidyr
abdkumar
abdkumar

Reputation: 142

How to group the data by id and get unique values of all columns in R?

I have a table with ID and other columns. I want to group the data by Ids and get the unique values of all columns. enter image description here

from above table group by ID and get unique(Alt1, Alt2, Alt3)

Resul should be in vector form
A -> 1,2,3,5
B ->1,3,4,5,7

Upvotes: 0

Views: 2841

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388817

We can get data in long format and for each ID make a list of unique values. 

library(dplyr)
library(tidyr)

df1 <- df %>%
  pivot_longer(cols = -ID) %>%
  group_by(ID) %>%
  summarise(value = list(unique(value))) %>%
  unnest(value)

df1

#    ID   value
#   <fct> <dbl>
# 1 A         1
# 2 A         3
# 3 A         2
# 4 A         5
# 5 B         1
# 6 B         4
# 7 B         5
# 8 B         3
# 9 B         6
#10 B         7

We can store it as a list if needed using split. 

split(df1$value, df1$ID)
#$A
#[1] 1 3 2 5

#$B
#[1] 1 4 5 3 6 7

data.table equivalent of the above would be : 

library(Data.table)

setDT(df)
df2 <- melt(df, id.vars = 'ID')[, .(value = list(unique(value))), ID]

unique values are present in df2$value as a vector.

data

df <- data.frame(ID = c('A', 'A', 'B', 'B'), 
       Alt1 = c(1, 2, 1, 3), 
       Alt2 = c(3, 5, 4, 6), 
       Alt3 = c(1, 3, 5, 7))

Upvotes: 3

Related Questions