According to the data in column B corresponding to column A of different categories in table A, how to transfer to new data columns in table B

Question

Original Dataframe, picture 1:

This is the original data, and I want to analysis from this sample data frame.

As you can see, there are different companies, that make different products.

I want to make a new data frame, that is sorted by COMPANY, as you can see in picture 2.

I am using R, so I want to know :

1. How to count the frequency that the COMPANY appeared time.(Green in Picture2)
2. How to add all product names in a new column by different companies. (Red in Picture2)

THANKS Park, it works well.

I have a renewed question:

If I want to add the RegNo after the product like this, how should I do? Renewed Question

Dataframe I want, picture2:

Answer 1

You may try

data:

df <- data.frame(
  company = c("Moon", "Sun", "Earth", "Mars", "Moon", "Sun", "Earth", "Moon", "Sun", "Moon"),
  productname = c("a", "b", "c", "d", "e", "f", "g", "h", "i", "g")
)

code:

library(dplyr)

df %>%
  group_by(company) %>%
  summarize(Freq = n(),
            Product = paste0(productname, collapse = ",")) %>%
  arrange(desc(Freq))

  company  Freq Product
  <chr>   <int> <chr>  
1 Moon        4 a,e,h,g
2 Sun         3 b,f,i  
3 Earth       2 c,g    
4 Mars        1 d      

add

I set RegNo as 1 to 10

df <- data.frame(
  RegNo = c(1:10),
  company = c("Moon", "Sun", "Earth", "Mars", "Moon", "Sun", "Earth", "Moon", "Sun", "Moon"),
  productname = c("a", "b", "c", "d", "e", "f", "g", "h", "i", "g")
)

df %>%
  unite(product, c("productname", "RegNo"), sep = ",") %>%
  group_by(company) %>%
  summarize(Freq = n(),
            Product = paste0(product, collapse = ";")) %>%
  arrange(company)

  company  Freq Product         
  <chr>   <int> <chr>           
1 Earth       2 c,3;g,7         
2 Mars        1 d,4             
3 Moon        4 a,1;e,5;h,8;g,10
4 Sun         3 b,2;f,6;i,9