Uniting several rows of data frame

Question

Is there a way to unite several rows of a data frame with multiple columns?

I have data from different animal organs - I want to contrast individuals' organs. One row should contain only measurements of an individual but from all sampled organs (columns). Any idea? I tried the melt command did not manage it.

Here is some of my data:

structure(list(ID = c("BB1", "BB1", "BB1"), ID.organ = c("BB1-B", 
"BB1-L", "BB1-M"), d15NAIR = c(6.244803447, 5.263374719, 6.28820367
), brain = c(-31.00047084, NA, NA), eyes = c(NA_real_, NA_real_, 
NA_real_), liver = c(NA, -30.8483728, NA), muscle = c(NA, NA, 
-29.67755736)), row.names = c(NA, 3L), class = "data.frame")

remember to add the R tag to you questions, so that people know to which language the question belongs — DS_UNI, Nov 04 '21 at 10:15

score 0 · Answer 1 · answered Nov 04 '21 at 10:13

you're on the right way with melt, you just have to be sure which melt function you're using data.table::melt or reshape2::melt, and there's quite a few ways to do this take a look at the answers here.

Here's a couple with your data:

mydata <- structure(list(ID = c("BB1", "BB1", "BB1"), 
                          ID.organ = c("BB1-B",  "BB1-L", "BB1-M"), 
                          d15NAIR = c(6.244803447, 5.263374719, 6.28820367),
                          brain = c(-31.00047084, NA, NA), 
                          eyes = c(NA_real_, NA_real_, NA_real_),
                          liver = c(NA, -30.8483728, NA), 
                          muscle = c(NA, NA, -29.67755736)), 
                     row.names = c(NA, 3L), 
                     class = "data.frame")

library(reshape2)
melt(mydata, id.vars=c("ID", "ID.organ",  "d15NAIR"), na.rm = TRUE)

library(tidyr)
gather(mydata, organ, value, c("brain", "liver", "muscle"), na.rm = TRUE)

#     ID ID.organ  d15NAIR variable     value
# 1  BB1    BB1-B 6.244803    brain -31.00047
# 8  BB1    BB1-L 5.263375    liver -30.84837
# 12 BB1    BB1-M 6.288204   muscle -29.67756

score 0 · Accepted Answer · answered Nov 09 '21 at 18:22

I found the solution although it seems bit stupid, because the mean is not computing acutal arithmetic means:

my_data <- my_data[,-c(2,3)] %>%
  pivot_longer(cols = brain:muscle) 
    
acast(my_data, ID ~ name, mean,na.rm=T)

Then it takes the original value.

Uniting several rows of data frame

2 Answers2