R: Group data frame by attaching columns

Question

I want to group rows of my data frame. But the columns should not be summarized, I want to attach them.

I tried the tidyverse package and came to a close (wrong) solution.

This is my df:

  `Order ID` Quantity `Sub-Category 1` `Sub-Category 2` `Sub-Category 3` `Sub-Category 4` `Sub-Category 5` `Sub-Category 6`
  <chr>         <dbl> <chr>            <chr>            <chr>            <chr>            <chr>            <chr>           
1   22              2 Bookcases        Bookcases        NA               NA               NA               NA              
2   33              3 Chairs           Chairs           Chairs           NA               NA               NA              
3   22              2 Labels           Labels           NA               NA               NA               NA              
4   33              5 Tables           Tables           Tables           Tables           Tables           NA

The number of columns represent the Quantity, so Quantity column not longer needed. I want to group them and attach the same Order ID rows, to get this solution:

  `Order ID` Quantity `Sub-Category 1` `Sub-Category 2` `Sub-Category 3` `Sub-Category 4` `Sub-Category 5` `Sub-Category 6`
  <chr>         <dbl> <chr>            <chr>            <chr>            <chr>            <chr>            <chr>           
1   22              2 Bookcases        Bookcases        Labels               Labels               NA               NA              
2   33              3 Chairs           Chairs           Chairs           Tables               Tables               Tables               Tables               Tables

Thank you very much, appreciate your help.

What logic is used to make the transformation? What do you mean by attach? — NelsonGon, May 15 '19 at 16:28
See if [reshape data from long to wide in R](https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format) helps. — Rui Barradas, May 15 '19 at 16:29

Felix T. · Answer 1 · 2019-05-15T17:45:56.930

1

Using the tidyr package's spread and gather functions, maybe try:

library(tidyverse)


df <- data %>% 
  select(-Quantity) %>%
  gather(key = "col", value = "val", -`Order ID`) %>% 
  group_by(`Order ID`, col) %>% 
  mutate(val = max(val, na.rm = T)) %>% 
  slice(1) %>% 
  ungroup() %>% 
  spread(key = "col", value = "val")

edited May 15 '19 at 17:45

answered May 15 '19 at 17:33

Felix T.

520
3
11

Some data, such as the chairs, are missing after I use this solution. – May 15 '19 at 18:17
Ahh, I see. Could you explain why, for example in the original data's `Sub Category 1` column, you want to retain only the `Chairs` observation after the transformation (and not `Tables`)? I think this'll let me better understand how to help. – Felix T. May 15 '19 at 18:42

R: Group data frame by attaching columns

1 Answers1