CODEWITHSUNDEEP
rfor-loopsumframe
sofiap
sofiap

Reputation: 3

Summing data base colums in R

I am starting to using R and it's really probably that my question is simple one, but nonetheless I have spent a lot of time trying to figure out what I am doing wrong and to no avail.

I have to thank you because I discover this site last week searching through other questions. But now as someone new, it is often difficult to interpret other people's code.

My version of RStudio is: 1.1.442

My question is that I have two data frames one with some years and one with some items that were found in severals trawls , and I need to summarize items and make another variable. Which appear the summarize of items per year and trawl.

So, I made a loop with for to have the same bottom trawls and the same year in order to sum items.

I made a simplification of my data base.

BT<- c(1, 1, 2, 2, 2, 3, 3, 3, 3, 3)
YEAR<- c(2007, 2007, 2008, 2008, 2008, 2009, 2009, 2009, 2009, 2009)
W<- c(95, 6, 60, 50, 4, 21, 56, 44, 23, 4) 
Data1= data.frame(BT,YEAR,W)

Trawl<- c(1, 2, 3)
Year<- c(2007, 2008, 2009)
Data2= data.frame(Trawl,Year)
peso=cbind()

for(i in 1:length(Data1$BT)) {
  ind=which(Data2$Trawl == Data1$BT[i] & Data2$Year == Data1$YEAR[i])

  print(i)
  print(ind)
  print(Data1$W[ind])
  peso[i]=Data1$W[ind]
  sumaGr[i]=colSums(peso[i])
}

And I get this:

Error in colSums(peso[i]) : 'x' must be an array of at least two dimensions

But I don't know how to fix it. I would appreciate all your help and advices. Thank you in advance.

Upvotes: 0

Views: 96

Answers (2)

Gwang-Jin Kim
Gwang-Jin Kim

Reputation: 9910

if(!require(dplyr)) {
  install.packages("dplyr")
  require(dplyr) 
} # for 'inner_join()' install and/or load package dplyr


# Rename for fusion of the two data frames
colnames(Data1) <- c("BT", "Year", "W")
# colnames for 'By=' must look the same!

data1.new <- inner_join(Data1, Data2, by="Year")

# inspect data1.new
data1.new

# split by "Trawl"
df.list <- split(data1.new, data1.new$Trawl)

# summarize each of the data frames in this list
summaries.list <- lapply(df.list, summary)

# But I think what youw ant is colMeans, colSums etc.
colMeans.list <- lapply(df.list, colMeans)
colSums.list  <- lapply(df.list, colSums)

# colMeans(df) is acatually function(df) {apply(df, 2, FUN=mean)}
# in this way you can use any variadic function to make it
# applicable to a whole column (variadic functions are those
# which can take any number of arguments).
# if there is a non-variadic function - let's say max():
# let's say
# max() takes only two arguments (that's not true ...)
# but let's assume it takes only two arguments, then 
# function(your.vector) Reduce(max, your.vector) makes it variadic
# e.g. maximum of a column:
colMax <- function(df) {apply(df, 2, FUN=function(vec) Reduce(max, vec))}
colMax.list   <- lapply(df.list, colMax)

# inspect those objects
colMeans.list
colSums.list
colMax.list

# you can reduce the results using Reduce(rbind, ...)
means.by.trawl.mat <- Reduce(rbind, colMeans.list)
sums.by.trawl.mat  <- Reduce(rbind, colSums.list)
maxs.by.trawl.mat  <- Reduce(rbind, colMax.list)

# give rownames
rownames(means.by.trawl.mat) <- means.by.trawl.mat[, "BT"]
rownames(sums.by.trawl.mat)  <- sums.by.trawl.mat[, "BT"]
rownames(maxs.by.trawl.mat)  <- maxs.by.trawl.mat[, "BT"]

# result
> means.by.trawl.mat
  BT Year    W Trawl
1  1 2007 50.5     1
2  2 2008 38.0     2
3  3 2009 29.6     3
> sums.by.trawl.mat
   BT  Year   W Trawl
2   2  4014 101     2
6   6  6024 114     6
15 15 10045 148    15
> maxs.by.trawl.mat
  BT Year  W Trawl
1  1 2007 95     1
2  2 2008 60     2
3  3 2009 56     3

Upvotes: 0

hpesoj626
hpesoj626

Reputation: 3629

You seem to be implementing some split-apply-combine calculations. Here are several ways that you can do it.

Base R

Data3 <- aggregate(Data1$W, by = list(Data1$BT, Data1$YEAR), sum)
colnames(Data3) <- c("Trawl", "YEAR", "sumaGr")
Data3

With dplyr

Data3 <- Data1 %>%
  group_by(BT, YEAR) %>%
  summarise(sumaGr = sum(W)) %>%
  rename(Trawl = BT)
Data3

With data.table

library(data.table)
Data3 <- setDT(Data1)[,.(sumaGr = sum(W)), by = .(BT, YEAR)]
setnames(Data2, "BT", "Trawl")
Data3

Here is the output from the base R solution:

#   Trawl YEAR sumaGr
# 1     1 2007    101
# 2     2 2008    114
# 3     3 2009    148

Upvotes: 1

Related Questions