R: Create 2 columns with difference and percentages values of another column

Question

I have a dataframe like this

ID <- c(101,101,101,102,102,102,103,103,103)
Pt_A <- c(50,100,150,20,30,40,60,80,90)
df <- data.frame(ID,Pt_A)

+-----+------+
| ID  | Pt_A |
+-----+------+
| 101 |   50 |
| 101 |  100 |
| 101 |  150 |
| 102 |   20 |
| 102 |   30 |
| 102 |   40 |
| 103 |   60 |
| 103 |   80 |
| 103 |   90 |
+-----+------+

I want to create 2 new columns with values calculated from Pt_A column.

df$Del_Pt_A <- NthRow(Pt_A) - 1stRow(Pt_A) grouped by ID, where n = 1,2,...n
df$Perc_Pt_A <- NthRow(Del_Pt_A) / 1stRow(Pt_A) grouped by ID, where n = 1,2,...n

Here is my desired output

+-----+------+---------+-----------+
| ID  | Pt_A | Del_Pt_A | Perc_Pt_A|
+-----+------+---------+-----------+
| 101 |   50 | 0       | 0         |
| 101 |  100 | 50      | 1.0       |
| 101 |  150 | 100     | 2.0       |
| 102 |   20 | 0       | 0         |
| 102 |   30 | 10      | 0.5       |
| 102 |   40 | 20      | 1.0       |
| 103 |   60 | 0       | 0         |
| 103 |   80 | 20      | 0.3       |
| 103 |   90 | 30      | 0.5       |
+-----+------+---------+-----------+

I currently get the desired result in MS Excel but I want to learn to do it in R to make my work efficient. I came across packages like dplyr, plyr, data.table etc but I couldn't solve it using those. Could some one please help me figure out how to work around this.

Answer 1

Here's a data.table way:

library(data.table)
setDT(df)[,`:=`(
    del       = Pt_A - Pt_A[1],
    perc      = Pt_A/Pt_A[1]-1
),by=ID]

which gives

    ID Pt_A del      perc
1: 101   50   0 0.0000000
2: 101  100  50 1.0000000
3: 101  150 100 2.0000000
4: 102   20   0 0.0000000
5: 102   30  10 0.5000000
6: 102   40  20 1.0000000
7: 103   60   0 0.0000000
8: 103   80  20 0.3333333
9: 103   90  30 0.5000000

Answer 2

Here another option in base R:

cbind(df,
      do.call(rbind,by(df,df$ID,
   function(x)
           setNames(data.frame(x$Pt_A-x$Pt_A[1],
                      x$Pt_A/x$Pt_A[1]-1),
                    c('Del_Pt_A','Perc_Pt_A')))))

#        ID Pt_A Del_Pt_A Perc_Pt_A
# 101.1 101   50        0 0.0000000
# 101.2 101  100       50 1.0000000
# 101.3 101  150      100 2.0000000
# 102.1 102   20        0 0.0000000
# 102.2 102   30       10 0.5000000
# 102.3 102   40       20 1.0000000
# 103.1 103   60        0 0.0000000
# 103.2 103   80       20 0.3333333
# 103.3 103   90       30 0.5000000

I am using :

• by to apply a function by group, the result is a list
• do.call(rbind, list_by) to transform the list to a data.frame
• cbind to add the result to the initial data.frame