R/Dplyr: Data Frame - Filling in NA/NULL values with 0, Columns for each row to be filled in is based on value from each rows specific value

I am new to this community.

I am trying to code in R but having a little trouble with my code.

#Create dataframe with 5 rows and 3 columns
my_dataframe=data.frame(Student_Id = c( '1012501', '1012502', '1012503', '1012504', '1012505', '1012506'), 	
                        Student_Nm = c( 'John Doe', 'John Smith', 'John Simon', 'Jane Powel', 'Jane Smith', 'Jane Simon'), 	
                        School_Cd = c( '808971', '808972', '808973', '808974', '808975', '808976'), 	
                        Grade = c(1,1,2,2,3,4), 	
                        Age = c(6,6,7,7,8,9), 	
                        Join_Month_Count = c(36,18,2,9,3,22), 	
                        New_Student_FL = c(0,0,1,0,1,0), 	
                        Subject = c( 'Math', 'Science', 'Geography', 'Social_Study', 'Music', 'Math'), 	
                        Mar_22 = c(NA,NA,NA,NA,NA,NA), 	
                        Apt_22 = c(NA,9,NA,NA,NA,2), 	
                        May_22 = c(12,NA,NA,NA,NA,9), 	
                        Jun_22 = c(NA,8,NA,NA,NA,8), 	
                        Jul_22 = c(1,NA,NA,8,NA,NA), 	
                        Aug_22 = c(NA,NA,NA,NA,NA,7), 	
                        Sep_22 = c(9,8,NA,NA,NA,NA), 	
                        Oct_22 = c(NA,NA,NA,8,NA,NA), 	
                        Nov_22 = c(26,NA,NA,NA,NA,4), 	
                        Dec_22 = c(NA,7,NA,NA,NA,NA), 	
                        Jan_23 = c(4,6,NA,8,NA,1), 	
                        Feb_23 = c(8,NA,4,NA,12,1), 	
                        Mar_23 = c(9,NA,NA,8,NA,5))

#Display dataframe
print(my_dataframe)

The sample table is below:

1. I am trying to fill in the data frame's column (9 to 21) existing NA or Null values with 0. from Column 9 to Column 21 (Mar_22 to Mar_23 in sample data).
2. The formula needs to check what the value of the "Join_Month_Count" is and
3. subtract the "Join_Month_Count" value from 22 (total column count + 1 ) to get the target column index. if the index is less than 9 then the starting column index will be 9, else start from calculated index column to the last column and
4. Fill in any NA/NULL value to 0

For example: In the sample dataset,
Row 1: Col 9 through col 21, NA will be filled with 0
Row 2: Col 9 through col 21, NA will be filled with 0
Row 3: Col 20 through col 21, NA will be filled with 0, every other columns will remain as it is
Row 4: Col 13 through col 21, NA will be filled with 0, every other columns will remain as it is
Row 5: Col 19 through col 21, NA will be filled with 0, every other columns will remain as it is
Row 6: Col 9 through col 21, NA will be filled with 0

Currently, I am using a for loop as below, but it is not working.
Code is filling all Na with 0.
Is there an easier way to do it and faster?

My R Code is as below.

for(i in 1:nrow(my_dataframe)){
  ref_val = 22 - my_dataframe[i,6]    ## 6 is the index of the "Join_Month_Count" column
  
  if(ref_val < 9){          ## 9 is the column index of the month start
    ref_val = 9
  }

my_dataframe[ , ref_val:21][is.na(my_dataframe[ , ref_val:21])] <- 0
}

and this 

# Replace NA on multiple columns by Index
library("tidyr")
library("dplyr")
my_dataframe <- my_dataframe %>% 
  mutate(across(c(Sep_22:Mar_23),~ ifelse(is.na(.), 0, .)))

The Following Seems to work, but really Slow


for(i in 1:nrow(my_dataframe)){
  ref_val = 22 - my_dataframe[i,6]    ## 6 is the index of the "Join_Month_Count" column
  
  if(ref_val < 9){          ## 9 is the column index of the month start
    ref_val = 9
  }
  print(ref_val)
  my_dataframe[i, ref_val:21][is.na(my_dataframe[i, ref_val:21])] <- 0
}

#Display dataframe
print(my_dataframe)

Thanks,
JC

Here's a base R approach.

tmp <- lapply(pmin(pmax(22 - my_dataframe$Join_Month_Count, 9), 21) - 8,
              function(z) replace(rep(NA, 13), z:13, 0))
str(tmp, vec.len = 13)
# List of 6
#  $ : num [1:13] 0 0 0 0 0 0 0 0 0 0 0 0 0
#  $ : num [1:13] 0 0 0 0 0 0 0 0 0 0 0 0 0
#  $ : num [1:13] NA NA NA NA NA NA NA NA NA NA NA 0 0
#  $ : num [1:13] NA NA NA NA 0 0 0 0 0 0 0 0 0
#  $ : num [1:13] NA NA NA NA NA NA NA NA NA NA 0 0 0
#  $ : num [1:13] 0 0 0 0 0 0 0 0 0 0 0 0 0
tmp <- setNames(do.call(rbind.data.frame, tmp), names(my_dataframe)[9:21])
tmp
#   Mar_22 Apt_22 May_22 Jun_22 Jul_22 Aug_22 Sep_22 Oct_22 Nov_22 Dec_22 Jan_23 Feb_23 Mar_23
# 1      0      0      0      0      0      0      0      0      0      0      0      0      0
# 2      0      0      0      0      0      0      0      0      0      0      0      0      0
# 3     NA     NA     NA     NA     NA     NA     NA     NA     NA     NA     NA      0      0
# 4     NA     NA     NA     NA      0      0      0      0      0      0      0      0      0
# 5     NA     NA     NA     NA     NA     NA     NA     NA     NA     NA      0      0      0
# 6      0      0      0      0      0      0      0      0      0      0      0      0      0

This first step creates a same-shape data.frame of the columns we need (9:21), with 0 if we want to replace NAs with 0, and NA otherwise.

With this, we can use dplyr::coalesce column-wise on the columns needed:

my_dataframe[,9:21] <- Map(dplyr::coalesce, my_dataframe[,9:21], tmp)
my_dataframe
#   Student_Id Student_Nm School_Cd Grade Age Join_Month_Count New_Student_FL      Subject Mar_22 Apt_22 May_22 Jun_22 Jul_22 Aug_22 Sep_22 Oct_22 Nov_22 Dec_22 Jan_23 Feb_23 Mar_23
# 1    1012501   John Doe    808971     1   6               36              0         Math      0      0     12      0      1      0      9      0     26      0      4      8      9
# 2    1012502 John Smith    808972     1   6               18              0      Science      0      9      0      8      0      0      8      0      0      7      6      0      0
# 3    1012503 John Simon    808973     2   7                2              1    Geography     NA     NA     NA     NA     NA     NA     NA     NA     NA     NA     NA      4      0
# 4    1012504 Jane Powel    808974     2   7                9              0 Social_Study     NA     NA     NA     NA      8      0      0      8      0      0      8      0      8
# 5    1012505 Jane Smith    808975     3   8                3              1        Music     NA     NA     NA     NA     NA     NA     NA     NA     NA     NA      0     12      0
# 6    1012506 Jane Simon    808976     4   9               22              0         Math      0      2      9      8      0      7      0      0      4      0      1      1      5