CODEWITHSUNDEEP
raggregatereshape
rashikawa
rashikawa

Reputation: 41

R - Reshaping data frame and aggregate elements

I have a data frame like this

client  Product date
A        apple  May
B        grape  Jun
B        apple  Jun
C        apple  Jul
A        grape  Jul

And I would like to have this:

 client May Jun Jul
  A     1   0   1
  B     0   2   0
  C     0   0   1

That is, I would like to aggregate all products sold over months per client. I know that I try reshape and data.table but I can't figure out the best way to do this.

Thanks

Upvotes: 4

Views: 67

Answers (2)

CMH89
CMH89

Reputation: 107

This can be done with Dplyr aswell

library(tidyverse)


client <- factor(c("A","B","B","C","A"))
product <- factor(c("apple", "grape", "apple", "apple", "grape"))
date <- factor(c("May", "Jun", "Jun", "Jul", "Jul"))

df <- data.frame(client=client,
                 product=product,
                 date=date)

df_sum <- df %>%
  group_by(client, date) %>%
  summarise(n=n()) %>%
  spread(date, n, fill = 0)

Upvotes: 0

Onyambu
Onyambu

Reputation: 79188

as.data.frame.matrix(xtabs(f~client+date,cbind(f=1,dat)))
  Jul Jun May
A   1   0   1
B   0   2   0
C   1   0   0

or you can do:

as.data.frame.matrix(table(dat[-2]))
  Jul Jun May
A   1   0   1
B   0   2   0
C   1   0   0

Upvotes: 2

Related Questions