CODEWITHSUNDEEP
rdplyr
Alexis
Alexis

Reputation: 2294

Create a determined quantity of rows based on a dataset in R

I have the following dataset:

dataloc <- data.frame(id = c("FAS","FAS","FAS","FES","FES"),
                      gen = c(9,0,0,8,0),
                      lipd = c(0,2,3,0,5),
                      stringsAsFactors = FALSE)

So I have two groups: FAS and FES. For each group I want to create 3 rows which repeats the non zero value of gen, a row for each non-zero value of lipd and a final row that substract lipd sum from gen. Each row has a defined name. This is the expected output:

Name    Value
First   9
Second  9
Third   9
Added   2
Added   3
Result  4
First   8
Second  8
Third   8
Added   5
Result  3

I can create the result row with this code:

dataloc %>% group_by(id) %>% 
  summarise(value = sum(gen) - sum(lipd)) %>% 
  mutate(name = "Result") %>% 
  select("name","value")

But I kind of stuck in creating n rows based on non-zero values and name them. Any help in R base or tidyverse will be greatly appreciated.

Upvotes: 0

Views: 68

Answers (1)

Rui Barradas
Rui Barradas

Reputation: 76402

Here is a base R solution. Maybe there are simpler solutions but this one works.

sp <- split(dataloc, dataloc$id)


res <- lapply(sp, function(DF){
  i <- which(DF[['gen']] != 0)
  j <- which(DF[['lipd']] != 0)
  tmp <- rbind(DF[rep(i, 3), ], DF[j, ])
  Value <- rowSums(tmp[-1])
  Value <- c(Value, Value[1] - sum(DF[j, 'lipd']))
  Name <- c("First", "Second", "Third", rep("Added", length(j)), "Result")
  id <- rep(DF[['id']][1], length(Name))
  data.frame(id, Name, Value)
})

res <- do.call(rbind, res)
row.names(res) <- NULL
res
#    id   Name Value
#1  FAS  First     9
#2  FAS Second     9
#3  FAS  Third     9
#4  FAS  Added     2
#5  FAS  Added     3
#6  FAS Result     4
#7  FES  First     8
#8  FES Second     8
#9  FES  Third     8
#10 FES  Added     5
#11 FES Result     3

Final clean-up.

rm(sp)

Upvotes: 3

Related Questions