Count string length using external table

user_5877690 0
Viewed 122

Suppose you have a table of data:

df<-tibble(person = c("Alice", "Bob", "Mary"),
           colour = c("Red", "Green", "Blue"),
           city = c("London", "Paris", "New York"))

# A tibble: 3 x 3
  person colour city    
  <chr>  <chr>  <chr>   
1 Alice  Red    London  
2 Bob    Green  Paris   
3 Mary   Blue   New York

And a second table which contains the field names and the maximum string length of each field:

len<-tibble(field_name = c("person", "colour", "city"),
            field_length = c(12, 4, 6))

# A tibble: 3 x 2
  field_name field_length
  <chr>             <dbl>
1 person               12
2 colour                4
3 city                  6

How can I check, for each field in len, whether a string in df is less than or equal to len$field_length, returning rows which fail the test?

As an example:

Output Row 1 in df would pass because:

  • 'Alice' <= 12 characters long,
  • 'Red' is <= 4 characters long and
  • 'London' is <= 6 characters long.

However,

  • Row 2 would fail because:
    • 'Green' > 4 characters long and
  • Row 3 would fail because:
    • 'New York' > 6 characters long.

Thus the returned data frame should only display Rows 2 and Row 3 of the original df.

dplyr tidyverse dataframe r
4 Answers

A dplyr solution with c_across():

library(dplyr)

df %>%
  rowwise() %>%
  filter(any(nchar(c_across(everything())) > len$field_length)) %>%
  ungroup()

# # A tibble: 2 x 3
#   person colour city    
#   <chr>  <chr>  <chr>   
# 1 Bob    Green  Paris   
# 2 Mary   Blue   New York
user_100689850

Using base R with mapply :

df[rowSums(mapply(function(x, y) nchar(x) > y, df, len$field_length)) > 0, ]

# A tibble: 2 x 3
#  person colour city    
#  <chr>  <chr>  <chr>   
#1 Bob    Green  Paris   
#2 Mary   Blue   New York

If column names in df are not in the same order as len$field_name use df[len$field_name] in mapply.

In tidyverse we can get data in long format join it with len data by column name, select the rows which fail and get data in wide format again.

library(dplyr)
library(tidyr)

df %>%
  mutate(row = row_number()) %>%
  pivot_longer(-row) %>%
  left_join(len, by = c('name' = 'field_name')) %>%
  group_by(row) %>%
  filter(any(nchar(value) > field_length)) %>%
  dplyr::select(-field_length) %>%
  pivot_wider()
user_39629140

It's easier to solve your problem in terms of 2 matrices, first the length of each of your entries:

nchar(as.matrix(df))
     person colour city
[1,]      5      3    6
[2,]      3      5    5
[3,]      4      4    8

And a corresponding matrix of allowed length:

allowed = replicate(nrow(df),len$field_length[match(colnames(df),len$field_name)])

allowed

     [,1] [,2] [,3]
[1,]   12   12   12
[2,]    4    4    4
[3,]    6    6    6

Then matrix wise comparison, and only keep those where the rowSums() are

df[rowMeans(nchar(as.matrix(df)) > allowed)>0,]
# A tibble: 2 x 3
  person colour city    
  <chr>  <chr>  <chr>   
1 Bob    Green  Paris   
2 Mary   Blue   New York

If your two data.frames are already in the same order like your example, you can do (thanks to @zx8754) for pointing it out:

df[rowMeans(nchar(as.matrix(df)) > len$field_length)>0,]
# A tibble: 2 x 3
  person colour city    
  <chr>  <chr>  <chr>   
1 Bob    Green  Paris   
2 Mary   Blue   New York
user_122584590

Pivot df into the same format as len and join the two. After this, it is trivial to compare each string to the field_length.

library(tidyverse)

test_result_df <- df %>% 
  mutate(id = row_number()) %>% 
  pivot_longer(-id, names_to = 'field_name') %>% 
  left_join(len, by = 'field_name') %>% 
  mutate(test_passed = str_length(value) <= field_length) %>% 
  group_by(id) %>% 
  summarise(all_passed = all(test_passed))

df[!test_result_df$all_passed,]


# A tibble: 2 x 3
  person colour city    
  <chr>  <chr>  <chr>   
1 Bob    Green  Paris   
2 Mary   Blue   New York
user_14986560
Related