How to check in how many columns character can be found

user_12486623 0
Viewed 123

I have a dataset with 4 columns containing names, where the number of names and the order of names differ between columns. Some columns can also contain the same name twice or more. It looks like follows:

df<- data.frame(x1=c("Ben","Alex","Tim", "Lisa", "MJ","NA", "NA","NA","NA"), 
x2=c("Ben","Paul","Tim", "Linda", "Alex", "MJ", "Lisa", "Ken","NA"), 
x3=c("Tomas","Alex","Ben", "Paul", "MJ", "Tim", "Ben", "Alex", "Linda"), 
x4=c("Ben","Alex","Tim", "Lisa", "MJ", "Ben", "Barbara","NA", "NA"))

Now I have to first extract the unique names within the dataset. I did that using the following code:

u<- as.vector(unique(unlist(df)))

Second, I need to find the names that can be found in all 4 columns (class A names), in 3 out of 4 columns (class B names) and in 2 out of 4 columns (class C names).

Here is where I get stuck. I can only extract the names that are contained in all 4 columns using:

n<- ifelse(u%in%df$x1 & u%in%df$x2 & u%in%df$x3 & 
               u%in%df$x4", A, B)

So, e.g., Ben would be a A class name because it can be found in all 4 columns and Lisa would be a B class name because it can only be found in 3 out of 4 columns.

Name Class
Ben    A
Lisa   B

Is there a nicer way to classify the unique names according to the number of columns they can be found in and how can it be done for B and C class names?

Thanks in advance!

categorization if-statement character r
3 Answers

Here is an idea via base R. We loop over the unique elements and take the rowSums when the dataframe equals with each name. The maximum result is your expected output

sapply(unique(c(t(df))), function(i) max(rowSums(df == i)))

#  Ben Tomas  Alex  Paul   Tim  Lisa Linda    MJ 
#    3     1     3     1     3     2     1     3
user_56355800

You can get the data in long format and for each name find how many unique column it occurs :

library(dplyr)

df %>%
  tidyr::pivot_longer(cols = everything(), values_drop_na = TRUE) %>%
  group_by(value) %>%
  summarise(count = n_distinct(name))

#   value   count
#   <chr>   <int>
# 1 Alex        4
# 2 Barbara     1
# 3 Ben         4
# 4 Ken         1
# 5 Linda       2
# 6 Lisa        3
# 7 MJ          4
# 8 NA          3
# 9 Paul        2
#10 Tim         4
#11 Tomas       1

Here you get "NA" in the output because it is a string. If your data has real NA's it will be dropped because of values_drop_na = TRUE.

user_39629140

A simple base R option using aggregate + stack

aggregate(.~values,unique(stack(df)),length)

such that

> aggregate(.~values,unique(stack(df)),length)
    values ind
1     Alex   4
2  Barbara   1
3      Ben   4
4      Ken   1
5    Linda   2
6     Lisa   3
7       MJ   4
8       NA   3
9     Paul   2
10     Tim   4
11   Tomas   1
user_121587570
Related