I have a data that is looks like the table on the left, andI want to get a table that looks like right. What should I do. I should be very straightforward but I just don't know how to start it.
3 Answers
Try this dplyr approach. And please next time as mentioned in valious comments add a sample of data using dput() and do not include images from data in MS Excel as it can be tedious to copy all values from an image. You can use summarise() and paste0() in order to reach what you want:
library(dplyr)
#Data
df <- data.frame(ID=c(rep(1,4),rep(2,2),rep(3,4)),
Day=c(1,3,3,5,2,4,4,4,4,5),
Result=c('a','a','b','c','c','a','','c','d','f'),stringsAsFactors = F)
#Code
df %>% group_by(ID,Day) %>%
summarise(Result=paste0(Result[Result!=''],collapse = '/'))
Output:
# A tibble: 7 x 3
# Groups: ID [3]
ID Day Result
<dbl> <dbl> <chr>
1 1 1 a
2 1 3 a/b
3 1 5 c
4 2 2 c
5 2 4 a
6 3 4 c/d
7 3 5 f
Comments
An option with data.table
library(data.table)
setDT(df)[Result != '', .(Result = paste(Result, collapse="/")), .(ID, Day)]
# ID Day Result
#1: 1 1 a
#2: 1 3 a/b
#3: 1 5 c
#4: 2 2 c
#5: 2 4 a
#6: 3 4 c/d
#7: 3 5 f
data
df <- structure(list(ID = c(1, 1, 1, 1, 2, 2, 3, 3, 3, 3), Day = c(1,
3, 3, 5, 2, 4, 4, 4, 4, 5), Result = c("a", "a", "b", "c", "c",
"a", "", "c", "d", "f")), class = "data.frame", row.names = c(NA,
-10L))
Comments
You are most likely looking for base::aggregate(), which is the standard approach for doing a grouped aggregation in R. The aggregator function should find non-empty unique values.
aggregate(Result ~ ID + Day, df, function(x) unique(x[x != ""]))
1 Comment
Nick
Code-only answers may solve the OP's problem, but an explanation will help other users understand why.
dput()or an actual data frame assignment statement in providing data. The idea is to allow others to easily copy and paste your example code into their coding environment - that's the easiest and fastest way to help you.