add_rows based on condition

[ksingh19]

Hi All,

I would like to add rows based on a condition as follows and am not able to achieve it:
I would like to make sure we have all codes if when ACT/FC == "FC", if we see them in data when ACT/FC == "ACT". So, I should see "A_2","A_3","A_4" when ACT/FC is FC for Country CA and Type A in 202310. Values will be NA for those additional rows. But the rest of the structure will be formed by group if exists in ACT of ACT/FC

library(tidyverse)

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

[bcavinee]

If I'm understanding the problem correctly, you can use a group_by with Country and Type to get the additional rows. After grouping I created a new column with the Code from the FC row in the group. I then used fill to add that value to all rows in the group for that column. That allowed me to check if the FC code was in the ACT rows. After filtering, the rows remaining are the ones you are after. Since the values were not NA, I went ahead and set them to NA before rbinding to the original data frame. I hope this helps!

fc= df %>% group_by(Country,Type) %>% mutate(FC_Value= case_when(`ACT/FC` == 'FC' ~ Code)) %>% fill(FC_Value, .direction = 'updown') %>% 
  mutate(Remove= case_when(Code == FC_Value ~ "Remove")) %>% filter(is.na(Remove)) %>% select(-FC_Value, -Remove)

cols= colnames(fc[colnames(fc) != 'Code'])

fc[,cols] = NA

new_df= rbind(df,fc)

[ksingh19]

@bcavinee

rows_to_add

Thanks @bcavinee ! Where is rows_to_add coming from?
Also, I was looking to utilize if function or just a function to be able to solve this.

[bcavinee]

@ksingh19 My apologizes, I updated my answer, rows_to_add should be fc. I can try to turn this into a function.

[bcavinee]

I took a different approach to this problem using a function. First I split the data by Country and Type. Then I used map to get the test codes in each entry into the list that map returns. I then use map with a custom function, the function looks to remove any duplicate values (including the original) from the list of codes. This returns the desired codes stated in the question. Now that you have the codes that are to be added to the original data frame, you can create a data frame with those codes, then use bind_rows to add them to the the original data frame.

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

fc_act_func= function(df) {
  
  group_data= df %>% group_split(Country,Type)
  
  get_codes=  map(group_data, \(x) x$Code)
  
  remove_dupo= map(get_codes, \(x) x[!(duplicated(x)|duplicated(x, fromLast=TRUE))])
  
  new_rows= data.frame(Code= unlist(remove_dupo))
  
  bind_rows(df,new_rows)
  
}

new_df= fc_act_func(df)

[ksingh19]

Thanks for giving it a try @bcavinee!

Yes, I was able to achieve it as well by splitting data into ACT and FC groups, duplicating same rows as that of ACT in FC group and then mapping into these groups. But I am trying to learn more efficient ways where probably using condition like IF or a function with IF or for loop can still do the trick without splitting the data. If this is achievable, I believe this will come very handy for many use cases.