Frequency count of two column based on condition in R

Question

Hello i have two columns in an dataframe. Material number and supplier number. I would like to find out which material number is ordered from two or more suppliers?

Following dataframe

OrderNo. Supplier Materialno
  1          LF       101
  2          LF       101
  3          LF       101
  4          DD       101
  5          DD       102
  6          DF       103
  7          DF       104
  8          DD       103
  9          DD       104
 10          AA       105
 11          AB       105
 12          AC       106

I would like to have the result in this format

OrderNo. Supplier Materialno Count
1           LF       101       2
2           LF       101       2
3           LF       101       2
4           DD       101       2
5           DD       102       1
6           DF       103       2 
7           DD       104       1
8           DD       103       2 
9           DD       104       1
10          AA       105       3
11          AB       105       3
12          AC       105       3

What have you tried to solve this on your own? There might be tutorials available to solve it — Nico Haase, Apr 06 '18 at 08:40

Jan · Answer 1 · 2018-04-06T08:44:35.897

Using dplyr:

library(dplyr)
df %>%
  group_by(Materialno) %>%
  mutate(Count = n_distinct(Supplier)) %>%
  ungroup()

Which yields

# A tibble: 12 x 4
   OrderNo. Supplier Materialno Count
      <int> <chr>         <int> <int>
 1        1 LF              101     2
 2        2 LF              101     2
 3        3 LF              101     2
 4        4 DD              101     2
 5        5 DD              102     1
 6        6 DF              103     2
 7        7 DF              104     2
 8        8 DD              103     2
 9        9 DD              104     2
10       10 AA              105     2
11       11 AB              105     2
12       12 AC              106     1

Please note that your input dataframe has other rows than your output dataframe, hence the difference to your desired output.

score 2 · Answer 2 · answered Apr 06 '18 at 08:43

Using base R it is a one-liner.

df$Count <- ave(as.integer(df$Supplier), df$Materialno, FUN = function(x) length(unique(x)))

df
#   OrderNo. Supplier Materialno Count
#1         1       LF        101     2
#2         2       LF        101     2
#3         3       LF        101     2
#4         4       DD        101     2
#5         5       DD        102     1
#6         6       DF        103     2
#7         7       DF        104     2
#8         8       DD        103     2
#9         9       DD        104     2
#10       10       AA        105     2
#11       11       AB        105     2
#12       12       AC        106     1

@Jan I don't understand, with the sample data you posted the output is as in my answer. Can you say what you have done and what was the result? — Rui Barradas, Apr 06 '18 at 09:01

score 0 · Answer 3 · answered Apr 06 '18 at 08:41

Base R:

merge(df, 
      do.call(rbind, by(df, df$Materialno, function(x) {
        c(Materialno = x$Materialno[1], Count = length(unique(x$Supplier)))))
      },
      by = "Materialno")

   Materialno OrderNo. Supplier Count
1         101        1       LF     2
2         101        2       LF     2
3         101        3       LF     2
4         101        4       DD     2
5         102        5       DD     1
6         103        6       DF     2
7         103        8       DD     2
8         104        7       DF     2
9         104        9       DD     2
10        105       10       AA     2
11        105       11       AB     2
12        106       12       AC     1

Frequency count of two column based on condition in R

3 Answers3