How to count element X in Data Frame 1 in Data Frame 2-CodePudding

I have two data frames in R:

DataFrame 1 has two columns: MAC_Address and Manufacture This data frame has about 4000 rows, where each row is different MAC and Manufacture.

DataFrame 2 has one column: MAC_Addressess This data frame has about 300 rows, and the same MAC can be found in multiple rows.

What I am dealing with is, how can I count how often one Manufactures MAC from Dataframe1 is found in DataFrame 2?

This a simple example, not the correct MACs by the way.

Scanning <- data.frame (MAC  = c("EE-16-17", "EE-16-17",  "AE-04-80", "EE-16-17", "FA-53-17"))

OUI <- data.frame (MAC  = c("EE-16-17", "FA-53-17", "FA-B3-17"),
                  Manufacturer = c("Intel", "Apple", "Microsoft"))

So I would get a result like this, hopefully in another data frame, which I could then sort by frequency:

Intel: 3
Apple: 1
Microsoft: 0

CodePudding user response：

One solution could be :

merge(data.frame(table(Scanning)),OUI,by="MAC",all.y=T) %>% 
  replace_na(list(Freq=0))

First you count the occurence of each model with table in Scanning, then you merge the result with the table containing brands. Finally, you can replace the NA values by 0.

CodePudding user response：

Check out this dplyr solution:

library(dplyr)
Scanning %>%
  # for each `MAC`...:
  group_by(MAC) %>%
  # ... count how often it occurs:
  summarise(N = n()) %>%
  # join `OUI` with the result:
  left_join(OUI, .) %>% 
  # convert NA into 0:
  mutate(N = replace_na(N, 0))
       MAC Manufacturer  N
1 EE-16-17        Intel  3
2 FA-53-17        Apple  1
3 FA-B3-17    Microsoft  0

CodePudding user response：

with dplyr:

EDIT: I did't read it right the first time. This should work:

Scanning %>% 
  count(MAC, sort = TRUE) %>% 
  left_join(OUI, .) %>% 
  mutate(n = ifelse(is.na(n),0, n))
#    MAC       Manufacturer n
# 1  EE-16-17  Intel        3
# 2  FA-53-17  Apple        1
# 3  FA-B3-17  Microsoft    0