CODEWITHSUNDEEP
rdataframedplyr
Funkeh-Monkeh
Funkeh-Monkeh

Reputation: 661

Restructure dataframe with values in multiple rows into single rows by column categories using dplyr

Below is an example of a subset of a dataframe I have in R that has information on multiple companies over multiple rows by categories - company_name, no_workers, product, address and contact_person)

comp_df <- structure(list(desc = c("AAA", "Company", "Ltd", "fish", "344", 
"12", "West", "Road", "Bob C", "BBB", "Enteprises", "vegetables", 
"12", "North", "Perak", "Simon T", "EF", "Industries", "cement", 
"8800", "Green", "Lane", "Singapore", "Sylvia P"), category = c("company_name", 
"company_name", "company_name", "product", "no_workers", "address", 
"address", "address", "contact_person", "company_name", "company_name", 
"product", "no_workers", "address", "address", "contact_person", 
"company_name", "company_name", "product", "no_workers", "address", 
"address", "address", "contact_person")), row.names = c(NA, -24L
), class = c("tbl_df", "tbl", "data.frame"))

Is there an easy way to add to my dplyr pipe a function to convert the above dataframe into something like the below

enter image description here

Upvotes: 2

Views: 60

Answers (1)

lroha
lroha

Reputation: 34441

Assuming that in your original data frame, in the category column that the first value of company_name in each set marks the beginning of a new group, you could do:

library(dplyr)
library(tidyr)

comp_df %>%
  group_by(category, grp = cumsum(category == "company_name" & lag(category, default = "") != "company_name")) %>%
  summarise(desc = paste(desc, collapse =  " ")) %>%
  pivot_wider(id_cols = grp, names_from = category, values_from = desc)

# A tibble: 3 x 6
    grp address              company_name    contact_person no_workers product   
  <int> <chr>                <chr>           <chr>          <chr>      <chr>     
1     1 12 West Road         AAA Company Ltd Bob C          344        fish      
2     2 North Perak          BBB Enteprises  Simon T        12         vegetables
3     3 Green Lane Singapore EF Industries   Sylvia P       8800       cement

Upvotes: 2

Related Questions