2

I have a data frame and I would like to add every three rows of it. Then, to apply cumprod to the rows in order to have a new data.frame with the resulting new rows.

At the end I will have only one third of the number of rows.

Below you can find some code that I wrote. I have tried to look for the class of the objects and to replicate a code that worked for a vector, not a matrix.

    XYZ<-read.xlsx2("XYZ.xlsx",1)
    XYZ.CUT<-aggregate(XYZ~gl(nrow(XYZ)/3, 3), data.frame(XYZ), sum)
    F.XYZ<-apply(t(XYZ.CUT+1),1,cumprod)

This is what I have:

 X       Y      Z 
-0,01%   0,32%  0,11%
-0,04%   0,01%  0,45%
-0,11%  -0,06%  0,03%
 0,03%  -0,04%  0,45%
 0,02%   0,04%  0,30%
-0,07%  -0,11%  0,11%
-0,12%  -0,13%  0,30%
-0,01%  -0,07%  0,04%
-0,37%   0,08%  0,05%

first I want:

 X       Y      Z
-0,16%   0,25%  0,59%
-0,02%  -0,11%  0,86%
-0,50%  -0,12%  0,39%

and after adding 1 to every element:

 X           Y           Z
(1-0,16%)   (1+0,25%)   (1+0,59%)
(1-0,02%)   (1-0,11%)   (1+0,86%)
(1-0,50%)   (1-0,12%)   (1+0,39%)

I would like to make a cumprod with the rows:

X           Y           Z
x1          y1          z1
x1*x2       y1*y2       z1*z2
x1*x2*x3    y1*y2*y3    z1*z2*z3

Advanced greetings.

Yesid
  • 125
  • 5

3 Answers3

3

We can use tidyverse. Using parse_number from readr, extract the numeric part from the columns (mutate_all), grouped by the index created with gl, summarise all the columns to get the sum

library(tidyverse)
library(readr)
out <- XYZ %>% 
          mutate_all(parse_number) %>%
          group_by(grp = as.integer(gl(n(), 3, n()))) %>%
          summarise_all(sum)

Then, we use rowCumprods from matrixStats to get the cumulative product of each row

library(matrixStats)
rowCumprods(as.matrix(out[-1]) + 1) 
#     [,1]   [,2]     [,3]
#[1,] 0.84 1.0668 1.696212
#[2,] 0.98 0.8722 1.622292
#[3,] 0.50 0.4400 0.611600

data

XYZ <- structure(list(X = c("-0.01%", "-0.04%", "-0.11%", "0.03%", "0.02%", 
"-0.07%", "-0.12%", "-0.01%", "-0.37%"), Y = c("0.32%", "0.01%", 
"-0.06%", "-0.04%", "0.04%", "-0.11%", "-0.13%", "-0.07%", "0.08%"
), Z = c("0.11%", "0.45%", "0.03%", "0.45%", "0.30%", "0.11%", 
"0.30%", "0.04%", "0.05%")), row.names = c(NA, -9L), class = "data.frame")
akrun
  • 874,273
  • 37
  • 540
  • 662
1

We can do it in the following way using only base R :

#First remove the % symbol from the columns and convert the values to numeric
XYZ[] <- lapply(XYZ, function(x) as.numeric(sub("%", "", x)))

#Sum every 3 rows
XYZ.CUT <- aggregate(.~ gl(nrow(XYZ)/3, 3),XYZ, sum)[-1]

#Add 1 and take cumulative product for each row
t(apply(XYZ.CUT + 1, 1, cumprod))
#Or if you need it columnwise use 
#apply(XYZ.CUT + 1, 2, cumprod) 
#        X      Y        Z
#[1,] 0.84 1.0668 1.696212
#[2,] 0.98 0.8722 1.622292
#[3,] 0.50 0.4400 0.611600
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
1

Another base R version using rowsum to get the aggregated sums (using @Akrun's XYZ dataset):

XYZ[] <- lapply(XYZ, sub, pat="%$", rep="")
XYZ[] <- lapply(XYZ, as.numeric)

out <- rowsum(XYZ, (seq_len(nrow(XYZ)) + 2) %/% 3) + 1

Then choose-your-own-adventure depending on which way you want the cumulative product to work:

## column-wise cumprod

out[] <- lapply(out, cumprod)
out
#       X        Y        Z
#1 0.8400 1.270000 1.590000
#2 0.8232 1.130300 2.957400
#3 0.4116 0.994664 4.110786

## row-wise cumprod

out[] <- Reduce(`*`, out, accumulate=TRUE)
out
#     X      Y        Z
#1 0.84 1.0668 1.696212
#2 0.98 0.8722 1.622292
#3 0.50 0.4400 0.611600
thelatemail
  • 91,185
  • 12
  • 128
  • 188