CODEWITHSUNDEEP
rdataframe
Delpux
Delpux

Reputation: 137

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

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

Upvotes: 0

Views: 37

Answers (3)

Chris Ruehlemann
Chris Ruehlemann

Reputation: 21400

Check out this dplyr solution:

library(dplyr)
library(tidyr)
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

Upvotes: 1

Leon Samson
Leon Samson

Reputation: 560

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

Upvotes: 0

Basti
Basti

Reputation: 1763

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.

Upvotes: 1

Related Questions