-5

Let's say we have dataframe

  product qua    color  month
1 Box     3       red    jan  
2 Box     14      blue   jan
3 Box     22      green  jan
4 Box     10       red   feb  
5 Box     12      blue   feb
6 Box     36      green  feb
7 Box     31       red   mar  
8 Box     1      blue    mar
9 Box     7      green   mar

How to group data this way:

           jan feb mar
  box red   3   10  31
      blue  14  12  1
      green 22  36  7

Thanks a lot in advance)

Ivan Bukata
  • 3
  • 2
  • 1
    In your sample data, there is no value for e.g. `color == "red & month == "feb" | month == "mar"` etc.. Nevertheless, you might try `library(reshape2); dcast(df, formula = product + color ~ month, value.var = 'qua', fun.aggregate = sum)`. Replace `sum` by any other aggregation function as you like. – markus Jul 30 '18 at 08:14
  • 1
    How do you get 10 for 'box red' at 'feb'? – Nicolas2 Jul 30 '18 at 08:14
  • Welcome to SO! Please read [ask] and give a [mcve]! possible duplicate: https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format – jogo Jul 30 '18 at 08:23
  • @markus you almost close) thank you but it is also important to group value = box and print it once in column, like in description. Any idea? – Ivan Bukata Jul 30 '18 at 08:37
  • @IvanBukata You mean this aggregation should be done for different levels of `product`? Can you show some example data? Use `dput(df)` to share it. – markus Jul 30 '18 at 08:50
  • 1
    `xtabs(qua ~ color + month, dat)`. – Rui Barradas Jul 30 '18 at 11:19

1 Answers1

0

data:

df<-
data.table::fread("id product qua    color  month
1 Box     3       red    jan  
2 Box     14      blue   jan
3 Box     22      green  jan
4 Box     10       red   feb  
5 Box     12      blue   feb
6 Box     36      green  feb
7 Box     31       red   mar  
8 Box     1      blue    mar
9 Box     7      green   mar")[,-1] %>% setDF

code:

df %>% 
mutate(color = factor(color,levels=unique(na.omit(color))), month = factor(month,levels=unique(na.omit(month)))) %>%
spread(month, qua)

result:

  product color jan feb mar
1     Box   red   3  10  31
2     Box  blue  14  12   1
3     Box green  22  36   7

To have the Box only once:

Save the above to result for e.g..

result$product[duplicated(result$product)]<-""

New result:

  product color jan feb mar
1     Box   red   3  10  31
2          blue  14  12   1
3         green  22  36   7
Andre Elrico
  • 10,956
  • 6
  • 50
  • 69
  • great, man) thanks a lot, but is there any idea how to print Box once? (it is important option for report's owner) – Ivan Bukata Jul 30 '18 at 09:07
  • look at my edit @IvanBukata – Andre Elrico Jul 30 '18 at 09:11
  • Elrice - thank you very much) but ...there is last one) your issue works if it is only one product, but can it work for 2? Box and data and 2 rows below new product, cups, for example with same properties? – Ivan Bukata Jul 30 '18 at 10:17
  • @IvanBukata please make sure to make an example that covers all your needs. See my edit: You can use: `result$product[duplicated(result$product)]<-""` – Andre Elrico Jul 30 '18 at 10:27