# How to split data frame into groups, combine rows

I have a large data set with 405 columns, many rows, and data from 15 sites. Each site has 27 columns, each one one quadrats data. Rows are species data.

I would like to split the data into the 15 sites and be able to use functions such as adding or averaging together all 27 columns to get an idea of the species presence at each site. I tried creating a vector of sites and using this to split the data.

Example:

"n<-rep(27,15)                    #repeats 27 15 times (15 sites, 27 quadrats per site in 2018)
names<-c("BB", "BEP","BKP", "BP","BY",'DB','DP','H1P','LTP','NB','NP','NRP','OP','ZB','ZP')
sites18<-as.factor(rep(names[rep(1:15, n)]) )     #site name replicated 27 times


When I use the split function, it loses the species data and makes one long column for each site.

split18<-as.data.frame(split(t(p18),sites18))


I need another solution, perhaps something with an apply function, but I have been unable to find a good solution.

If I understood you correctly, you want to vertically split your data frame and horizontally union the resulting data frames. Here is an example for what I would do in this case:

#assuming exemplary dataset
dataset <- data.frame(matrix(rnorm(n = 27 * 15 * 10), nrow = 10));
colnames(dataset) <- paste(
as.character(sites18)
,rep(1:27, length.out = 27 * 15)
,sep = "_");
str(dataset);

#create list of data frames using a vertical split
(list_df_by_group <- lapply(
names
,function(name) dataset[, paste(rep(name, 27), 1:27, sep = "_")]));
names(list_df_by_group) <- names;
str(list_df_by_group);

#horizontally union data frames
final_dataset <- data.frame(matrix(ncol = 28, nrow = 0));
colnames(final_dataset) <- c("group", as.character(1:27));
for(name in names){
colnames(list_df_by_group[[name]]) <- as.character(1:27);
final_dataset <- rbind(
final_dataset,
cbind(
data.frame(group = rep(name, nrow(list_df_by_group[[name]])))
,list_df_by_group[[name]]));
}
str(final_dataset);

• Hi @Franziska , Thanks for taking the time to answer my question! This isn't quite what i'm looking for. for a final product, I am looking for 15 columns, each column for a site. For instance, I want to add together all values for each row for each site. I realize i could do this manually like this "cbind(rowSums(p18[,1:27], rowSums(p18[,28:56], ....etc...". However I am trying to learn how to automate. Perhaps if I modify your "horizontally union data frames" section? – Rebecca Swab Jan 14 '19 at 20:46

Maybe you can do it like this, with tidyverse functions:

library(tidyverse)
n <- rep(27, 15)
# repeats 27 15 times (15 sites, 27 quadrats per site in 2018)

names <- c("BB", "BEP", "BKP", "BP", "BY", "DB", "DP", "H1P", "LTP", "NB", "NP", "NRP", "OP", "ZB", "ZP")

sites18 <- names[rep(1:15, n)] # site name replicated 27 times

speciesdata <- data.frame(matrix(nrow = 2, ncol = 405, data = runif(405 * 2)))
colnames(speciesdata) <- sites18
colnames(speciesdata) <- map2_chr(colnames(speciesdata), rep(1:27, 15),
function(x,y) {
sprintf("%s%02d", x, y)
})

speciesdata_processed <- speciesdata %>%
gather(section, value, colnames(speciesdata)) %>%
separate(section, sep="[0-9]+\$", into = c("site", NA), remove =FALSE) %>%
select(site, section, value)


New dataframe speciesdata_processed is now:

 head(speciesdata_processed)
site section   value
1   BB    BB01 0.40905
2   BB    BB01 0.86480
3   BB    BB02 0.08407
4   BB    BB02 0.32020
5   BB    BB03 0.13081
6   BB    BB03 0.12592


Calculate Means:

speciesdata_processed %>%
group_by(site) %>%
summarize(avg_val = mean(value))


Result:

   site  avg_val
<chr>   <dbl>
1 BB      0.513
2 BEP     0.498
3 BKP     0.548
4 BP      0.445
5 BY      0.532
6 DB      0.434
7 DP      0.507
8 H1P     0.455
9 LTP     0.548
10 NB      0.495
11 NP      0.504
12 NRP     0.464
13 OP      0.498
14 ZB      0.475
15 ZP      0.490