Help with for loop on grouped data

[ksingh19]

Hi All,

Looking to apply for loop correctly on grouped data. Achieving correct result per group without applying for loop
Thanks for your help in advance!

library(tidyverse)

# Example data
df <- data.frame(
  stringsAsFactors = FALSE,
       check.names = FALSE,
                  ACCTDT = c(202309L,202309L,
                             202309L,202309L,202310L,202310L,202310L,202310L,
                             202309L,202309L,202309L,202310L,202310L,
                             202310L),
                `ACT/FC` = c("ACT","ACT","ACT",
                             "ACT","FC","FC","FC","FC","ACT","ACT",
                             "ACT","FC","FC","FC"),
                 Country = c("CA","CA","CA",
                             "CA","CA","CA","CA","CA","CA","CA","CA","CA",
                             "CA","CA"),
                    Type = c("A","A","A","A",
                             "A","A","A","A","B","B","B","B","B","B"),
                    Code = c("A_1","A_2","A_3",
                             "A_4","A_1","A_2","A_3","A_4","A_1","A_2",
                             "A_3","A_1","A_2","A_3"),
                   Sales = c(100L,90L,80L,
                             120L,110L,NA,NA,NA,89L,130L,96L,125L,NA,NA)
      )

# Works fine without For Loop as below

df2 <- df
df2 <- df2%>%
  group_by(ACCTDT, `ACT/FC`, Country, Type, Code) %>%
  mutate(Sales = Sales,
         `New_Sale`= if_else(`ACT/FC` == "ACT", NA, Sales)
  )

# For Grouped Data using For Loop
df1 <- df

# New Sales 
df$"New_Sale" <- integer(length(df$Sales))
df1$"New_Sale" <- integer(length(df$Sales))

# Doesn't work for all groups 
# Works only for first group
for(i in seq_along(df)){
  if(df$`ACT/FC`[i] == "ACT"){
    df$`New_Sale`[i] = NA
  } else{
    df$`New_Sale`[i] = df$Sales[i]
  }
  
}

# Trying for all groups
# No result here
for(i in seq_along(df1)){
  df1%>%
    group_by(ACCTDT, `ACT/FC`, Country, Type, Code) %>%
    mutate(Sales = Sales, `New_Sale`= ifelse(
      `ACT/FC`[i] == "ACT", NA, Sales[i]
    )
    )
}

[technocrat]

@ksingh19

Works only for first group

Actually, only the last group. What are you trying to accomplish beyond this?

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
d <- data.frame(
  ACCTDT = c(202309,202309,
             202309,202309,202310,202310,202310,202310,
             202309,202309,202309,202310,202310,
             202310),
  ACT_FC = c("ACT","ACT","ACT",
               "ACT","FC","FC","FC","FC","ACT","ACT",
               "ACT","FC","FC","FC"),
  Country = c("CA","CA","CA",
              "CA","CA","CA","CA","CA","CA","CA","CA","CA",
              "CA","CA"),
  Type = c("A","A","A","A",
           "A","A","A","A","B","B","B","B","B","B"),
  Code = c("A_1","A_2","A_3",
           "A_4","A_1","A_2","A_3","A_4","B_1","B_2",
           "B_3","B_1","B_2","B_3"),
  Sales = c(100,90,80,
            120,110,NA,NA,NA,89,130,96,125,NA,NA)
)

d$New_Sale <- ifelse(d$ACT_FC == "ACT",NA,d$Sales)
d
#>    ACCTDT ACT_FC Country Type Code Sales New_Sale
#> 1  202309    ACT      CA    A  A_1   100       NA
#> 2  202309    ACT      CA    A  A_2    90       NA
#> 3  202309    ACT      CA    A  A_3    80       NA
#> 4  202309    ACT      CA    A  A_4   120       NA
#> 5  202310     FC      CA    A  A_1   110      110
#> 6  202310     FC      CA    A  A_2    NA       NA
#> 7  202310     FC      CA    A  A_3    NA       NA
#> 8  202310     FC      CA    A  A_4    NA       NA
#> 9  202309    ACT      CA    B  B_1    89       NA
#> 10 202309    ACT      CA    B  B_2   130       NA
#> 11 202309    ACT      CA    B  B_3    96       NA
#> 12 202310     FC      CA    B  B_1   125      125
#> 13 202310     FC      CA    B  B_2    NA       NA
#> 14 202310     FC      CA    B  B_3    NA       NA


for(i in seq_along(d)){
  if(d$ACT_FC[i] == "ACT"){
    d$New_Sale[i] = NA
  } else{
    d$New_Sale[i] = d$Sales[i]
  }
}


for(i in seq_along(d)){
  d%>%
    group_by(ACCTDT, ACT_FC, Country, Type, Code) %>%
    mutate(Sales = Sales, New_Sale = ifelse(
      ACT_FC[i] == "ACT", NA, Sales[i]
    )
    )
}

^{Created on 2023-10-21 with reprex v2.0.2}

[ksingh19]

Apologies!! Typo correction in Sample Data above (Edited) - Code variable has values A_1, A_2, A_3 or A_4 and nothing starts with B in this variable.

I am trying to achieve the same result with loop for grouped data as I think loop might work better in certain cases . But I didn't perform loops on grouped data yet and am trying out small to learn it better.

In this sample data, first group with FC in ACT/FC is showing right result, but not showing anything for teh rest of data with FC in ACT/FC