Looping over the rows of a dataframe and use it as function input in r

user_13921965 0
Viewed 65

I want to loop over a table with categories to assign a subcategory to a column in another dataframe called 'data'. The text is handwritten by a person and is a character field which contains around 100 words, but is dynamic in length. I want to check if the word provided in 'search_pat' is in the field 'text' if it is and the 'category' is also matched, the subcat must be assigned.The apply function i do in the code below provides me the result: "cat1" "cat2"

But my desired result is the same dataframe 'data' but then with filled values (if applicable) for the column 'subcat'.

My real 'data' dataframe is 400.000 rows and the categories table is now around 100 rows but will be expanded in the future. I got this working with a for-loop, but to me this seems quite inefficient. My goal is to make the code more efficient.

See the example provided below.

# Create example data
data <- data.frame('text' = rep(c('blabla FOO blabla', 'blabla test','bla BAR blablabla', 'test BOO blabla'),2),
                   'category' = c('X', 'Y', 'Z', 'X', 'X', 'X', 'X', 'Z'),
                   'subcat' = rep(NA, 8))

categories <- data.frame('search_pat' = c('FOO', 'BAR', 'BOO', 'BOO'),
                         'category' = c('X', 'Z', 'X', 'Z'),
                         'subcat' = c('cat1', 'cat2', 'cat3', 'cat4'))

# Function for assigning the subcategory to the data
assign_subcat <- function(df, searchterm, category, subcat){
  bool_mask <- (grepl(searchterm, df$text) & (df$category == category))
  df$subcat[bool_mask] <- subcat
}

# Loop over the categories dataframe to assign subcategory on the data
apply(categories, MARGIN = 1, FUN = function(x) assign_subcat(df = data,
                                                              x['search_pat'],
                                                              x['category'],
                                                              x['subcat']))

# Desired result
       text           category subcat
1 blabla FOO blabla        X     cat1
2       blabla test        Y     NA
3 bla BAR blablabla        Z     cat2
4   test BOO blabla        X     cat3
5 blabla FOO blabla        X     cat1
6       blabla test        X     NA
7 bla BAR blablabla        X     NA
8   test BOO blabla        Z     cat4
stringr dplyr dataframe r join
1 Answers

You could exploit a left join operation to achieve your expected result. With the dplyr package you can first create a column search_pat that stores the keys you are looking for (in your example "FOO" and "BAR" are extracted thanks to the str_extract function from package stringr), then you join by search_pat and category and then you discard the unnecessary columns.

library(dplyr)

data %>% 
  mutate(search_pat = stringr::str_extract(text, paste(categories$search_pat, collapse = "|"))) %>% 
  left_join(categories, by = c("search_pat", "category"), suffix = c("_to_be_removed", "")) %>% 
  select(-search_pat, -subcat_to_be_removed)

#                text category subcat
# 1 blabla FOO blabla        X   cat1
# 2       blabla test        Y   <NA>
# 3 bla BAR blablabla        Z   cat2
# 4 blabla FOO blabla        X   cat1
# 5       blabla test        Z   <NA>
# 6 bla BAR blablabla        X   <NA>
user_74654620
Related