Creating a new column using existing values from other columns using R

Question

I am trying to calculate weight loss over period of time, therefore the initial weight is quite important. I want to create a column 'Initial weight' based on the weight at 'Day'=0 for each tray. Example is below:

Day   Tray  Weight    Initial Weight  
0      1     3303       3303
3      1     3302.4     3303 
6      1     3303       3303       
15     1     3295.3     3303
36     1     3281.2     3303
0      2     3428       3428
3      2     3426       3428
6      2     3425       3428
15     2     3422       3428
36     2     3417       3428

Once I have the initial Weight values then I can calculate my weight loss in a new column: (Initial Weight-Weight(t))/Initial weight.

data.frame(
         Day = c(0L, 3L, 6L, 15L, 36L, 0L, 3L, 6L, 15L, 36L),
        Tray = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L),
      Weight = c(3303, 3302.4, 3303, 3295.3, 3281.2, 3428, 3426, 3425, 3422, 3417)
)

Selcuk Akbas · Accepted Answer · 2018-08-20T22:25:35.067

0

dplyr way of doing this

library(tidyverse)

data.frame(
         Day = c(0L, 3L, 6L, 15L, 36L, 0L, 3L, 6L, 15L, 36L),
        Tray = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L),
      Weight = c(3303, 3302.4, 3303, 3295.3, 3281.2, 3428, 3426, 3425, 3422, 3417)
) %>% 
  mutate(inw = case_when(Day == 0 ~ Weight, TRUE ~ -99)) %>% 
  group_by(Tray) %>% 
  mutate(inw = max(inw)) %>% 
  mutate(loss = (inw-Weight) / inw)

result

     Day  Tray Weight   inw     loss
   <int> <int>  <dbl> <dbl>    <dbl>
 1     0     1  3303    3303 0       
 2     3     1  3302.4  3303 0.000182
 3     6     1  3303    3303 0       
 4    15     1  3295.3  3303 0.00233 
 5    36     1  3281.2  3303 0.00660 
 6     0     2  3428    3428 0       
 7     3     2  3426    3428 0.000583
 8     6     2  3425    3428 0.000875
 9    15     2  3422    3428 0.00175 
10    36     2  3417    3428 0.00321

edited Aug 20 '18 at 22:25

answered Aug 20 '18 at 22:14

Selcuk Akbas

609
1
8
18

note that you lost decimal point. Correct `inw` is `3302.4 ` and you have `33024` without decimal point. – Jilber Urbina Aug 20 '18 at 22:21
@SelcukAkbas that works quite well. Just wondering, when you use case_when, what does TRUE~-99 mean? – Leo Aug 22 '18 at 02:03
that is rest of the cases. similar to else – Selcuk Akbas Aug 22 '18 at 08:45

score 0 · Answer 2 · answered Aug 20 '18 at 22:18

Here's an R base approach

> df1$Initial_Weight <- unlist(lapply(split(df1, df1$Tray), function(x) rep(x$Weight[x$Day==0], nrow(x))))
> df1$Loss <- (df1$Initial_Weight-df1$Weight)/df1$Initial_Weight
> df1
   Day Tray Weight Initial_Weight         Loss
1    0    1 3303.0           3303 0.0000000000
2    3    1 3302.4           3303 0.0001816530
3    6    1 3303.0           3303 0.0000000000
4   15    1 3295.3           3303 0.0023312140
5   36    1 3281.2           3303 0.0066000606
6    0    2 3428.0           3428 0.0000000000
7    3    2 3426.0           3428 0.0005834306
8    6    2 3425.0           3428 0.0008751459
9   15    2 3422.0           3428 0.0017502917
10  36    2 3417.0           3428 0.0032088681

Data:

df1 <- read.table(text="Day   Tray  Weight    
0      1     3303       
3      1     3302.4
                  6      1     3303
                  15     1     3295.3
                  36     1     3281.2
                  0      2     3428
                  3      2     3426
                  6      2     3425
                  15     2     3422
                  36     2     3417", header=TRUE)

score 0 · Answer 3 · answered Aug 21 '18 at 10:53

A simple base R solution is the following.

dat$Initial_Weight <- with(dat, ave(Weight, Tray, FUN = function(x) x[1]))
dat$Loss <- with(dat, (Initial_Weight - Weight)/Initial_Weight)

dat
#   Day Tray Weight Initial_Weight         Loss
#1    0    1 3303.0           3303 0.0000000000
#2    3    1 3302.4           3303 0.0001816530
#3    6    1 3303.0           3303 0.0000000000
#4   15    1 3295.3           3303 0.0023312140
#5   36    1 3281.2           3303 0.0066000606
#6    0    2 3428.0           3428 0.0000000000
#7    3    2 3426.0           3428 0.0005834306
#8    6    2 3425.0           3428 0.0008751459
#9   15    2 3422.0           3428 0.0017502917
#10  36    2 3417.0           3428 0.0032088681

Creating a new column using existing values from other columns using R

3 Answers3