how to create a row that is calculated from another row automatically like how we do it in excel?-CodePudding

does anyone know how to have a row in R that is calculated from another row automatically? i.e.

lets say in excel, i want to make a row C, which is made up of (B2/B1)

e.g. C1 = B2/B1 C2 = B3/B2 ... Cn = Cn 1/Cn

but in excel, we only need to do one calculation then drag it down. how do we do it in R?

CodePudding user response：

In R you work with columns as vectors so the operations are vectorized. The calculations as described could be implemented by the following commands, given a data.frame df (i.e. a table) and the respective column names as mentioned:

df["C1"] <- df["B2"]/df["B1"]
df["C2"] <- df["B3"]/df["B2"]

In R you usually would name the columns according to the content they hold. With that, you refer to the columns by their name, although you can also address the first column as df[, 1], the first row as df[1, ] and so on.

EDIT 1:

There are multiple ways - and certainly some more elegant ways to get it done - but for understanding I kept it in simple base R:

Example dataset for demonstration:

df <- data.frame("B1" = c(1, 2, 3),
                 "B2" = c(2, 4, 6),
                 "B3" = c(4, 8, 12))

Column calculation:

for (i in 1:ncol(df)-1) {
  col_name <- paste0("C", i)
  df[col_name] <- df[, i 1]/df[, i]
}

Output:

  B1 B2 B3 C1 C2
1  1  2  4  2  2
2  2  4  8  2  2
3  3  6 12  2  2

So you iterate through the available columns B1/B2/B3. Dynamically create a column name in every iteration, based on the number of the current iteration, and then calculate the respective column contents.

EDIT 2:

Rowwise, as you actually meant it apparently, works similarly:

a <- c(10,15,20, 1)
df <- data.frame(a)

for (i in 1:nrow(df)) {
  df$b[i] <- df$a[i 1]/df$a[i]
}

Output:

   a        b
1 10 1.500000
2 15 1.333333
3 20 0.050000
4  1       NA

CodePudding user response：

You can do this just using vectors, without a for loop.

a <- c(10,15,20, 1)
df <- data.frame(a)

df$b <- c(df$a[-1], 0) / df$a

print(df)
   a        b
1 10 1.500000
2 15 1.333333
3 20 0.050000
4  1 0.000000

Explanation:

In the example data, df$a is the vector 10 15 20 1.

df$a[-1] is the same vector with its first element removed, 15 20 1.

And using c() to add a new element to the end so that the vector has the same lenght as before:

c(df$a[-1],0) which is 15 20 1 0

What we want for column b is this vector divided by the original df$a.

So:

df$b <- c(df$a[-1], 0) / df$a