How to add a new row to a data table on a specific place using for loop in R-CodePudding

I have a table starting with:

> TABLE_sort_T_1
          Diagnosis Total 0 1-10 11-20 21-30 31-40 41-50 51-60 61-70 71-80 81-90   91>
11         Total_mf  1000 0    0   3.0   4.0  11.0  23.0  46.0 126.0 262.0 321.0 204.0
1          Total_mf   100 0    0   0.3   0.4   1.1   2.3   4.6  12.6  26.2  32.1  20.4
2           Total_f   508 0    0   0.0   1.0   2.0  10.0  18.0  56.0 114.0 176.0 131.0
3           Total_m   492 0    0   3.0   3.0   9.0  13.0  28.0  70.0 148.0 145.0  73.0
A04_f         A04_f     3 0    0   0.0   0.0   0.0   0.0   0.0   0.0   1.0   2.0   0.0
A04_m         A04_m     0 0    0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0
A04_Total A04_Total     3 0    0   0.0   0.0   0.0   0.0   0.0   0.0   1.0   2.0   0.0
A31_f         A31_f     0 0    0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0
A31_m         A31_m     1 0    0   0.0   0.0   0.0   0.0   0.0   0.0   1.0   0.0   0.0
A31_Total A31_Total     1 0    0   0.0   0.0   0.0   0.0   0.0   0.0   1.0   0.0   0.0

and ending with:

          Diagnosis Total 0 1-10 11-20 21-30 31-40 41-50 51-60 61-70 71-80 81-90 91>
Y34_f         Y34_f     1 0    0     0     0     0     0     0     0     0     1   0
Y34_m         Y34_m     1 0    0     0     0     1     0     0     0     0     0   0
Y34_Total Y34_Total     2 0    0     0     0     1     0     0     0     0     1   0
Y83_f         Y83_f     0 0    0     0     0     0     0     0     0     0     0   0
Y83_m         Y83_m     1 0    0     0     0     0     0     0     0     0     1   0
Y83_Total Y83_Total     1 0    0     0     0     0     0     0     0     0     1   0

I would like to add one row after every row ending with _Total, where I calculate percentages for that row. I tried this code:

for(i in 1:length(TABLE_sort_T_1$Diagnosis)){
  
  if (str_detect(TABLE_sort_T_1$Diagnosis[i], "(?i)_Total$"))
  {
    newrow_2 <- ((TABLE_sort_T_1[c(i),c(2)])/(TABLE_sort_T_1[c(1),c(2)]))*100
    newrow_3_13 <- ((TABLE_sort_T_1[c(i),3:13])/(TABLE_sort_T_1[c(i),c(2)]))*100
    newrow <- cbind(TABLE_sort_T_1$Diagnosis[i],newrow_2,newrow_3_13)

    names(newrow)<- c("Diagnosis", "Total","0","1-10","11-20","21-30","31-40","41-50","51-60","61-70","71-80","81-90","91>")
    print(newrow)

    TABLE_sort_T_2 <- rbind(TABLE_sort_T_1[1:i,], newrow, TABLE_sort_T_1[-(1:i),])                      
  }

}

and it prints me every newrow in the console like it should look like (correctly for every Diagnosis), but when it comes to binding, it binds only the last newrow to the TABLE_sort_T_1, and ignores all the others. Like this:

> print(tail(TABLE_sort_T_2))
           Diagnosis Total 0 1-10 11-20 21-30 31-40 41-50 51-60 61-70 71-80 81-90 91>
Y34_m          Y34_m   1.0 0    0     0     0     1     0     0     0     0     0   0
Y34_Total  Y34_Total   2.0 0    0     0     0     1     0     0     0     0     1   0
Y83_f          Y83_f   0.0 0    0     0     0     0     0     0     0     0     0   0
Y83_m          Y83_m   1.0 0    0     0     0     0     0     0     0     0     1   0
Y83_Total  Y83_Total   1.0 0    0     0     0     0     0     0     0     0     1   0
Y83_Total1 Y83_Total   0.1 0    0     0     0     0     0     0     0     0   100   0

How can I place newrow for every Diagnosis below that particular Diagnosis in a new table TABLE_sort_T_2, and not just for the last row?

CodePudding user response：

Every time you add a new row, you are adding it to the original table (TABLE_sort_T_1) and overwriting the value of TABLE_sort_T_2, so it isn't saving any of the previously created rows and only keeps the last iteration.

You also can't just replace it with TABLE_sort_T_1 as you would be changing the indices as you iterate through, which is dangerous.

I would recommend creating a copy of the table first, and then checking you are inserting in the right place each time.

# Create a copy of the table
TABLE_sort_T_2 <- TABLE_sort_T_1

for(i in 1:length(TABLE_sort_T_1$Diagnosis)){
  
  if (str_detect(TABLE_sort_T_1$Diagnosis[i], "(?i)_Total$")) {
    newrow_2 <- ((TABLE_sort_T_1[c(i),c(2)])/(TABLE_sort_T_1[c(1),c(2)]))*100
    newrow_3_13 <- ((TABLE_sort_T_1[c(i),3:13])/(TABLE_sort_T_1[c(i),c(2)]))*100
    newrow <- cbind(TABLE_sort_T_1$Diagnosis[i],newrow_2,newrow_3_13)
    
    names(newrow)<- c("Diagnosis", "Total","0","1-10","11-20","21-30","31-40","41-50","51-60","61-70","71-80","81-90","91>")
    print(newrow)
    
    # Make sure you are inserting in the right place in the copied table
    t2_index <- which(TABLE_sort_T_2[,1]==newrow[,1])
    
    # Insert new row into copied table, using correct index
    TABLE_sort_T_2 <- rbind(TABLE_sort_T_2[1:t2_index,], newrow, TABLE_sort_T_2[-(1:t2_index),])                      
  }
  
}