CODEWITHSUNDEEP
rdatedplyr
Ajay singodia
Ajay singodia

Reputation: 21

Retrieve month wise data

df<-data.frame(id=c("xx33","xx33","xx22","xx11","xx11","xx00"),amount=c(10,15,100,20,10,15),date=c("01/02/2013","01/02/2013","02/02/2013","03/03/2013","03/03/2013","04/04/2013"))

    id  amount  date
1   xx33    10  01/02/2013
2   xx33    15  01/02/2013
3   xx22    100 02/02/2013
4   xx11    20  03/03/2013
5   xx11    10  03/03/2013
6   xx00    15  04/04/2013

Output like

    id  201302  201303  201304
1   xx33    25    0        0
2   xx22   100    0        0
3   xx11    0     30       0
4   xx00    0     0        15

Upvotes: 0

Views: 62

Answers (3)

Uwe
Uwe

Reputation: 42592

For the sake of completeness, here is also a solution which uses dcast() from the reshape2 package, one of the predecessors of dplyr:

library(reshape2)
library(lubridate)
dcast(df, id ~ format(dmy(date), "%Y%m"), sum, value.var = "amount")

    id 201302 201303 201304
1 xx00      0      0     15
2 xx11      0     30      0
3 xx22    100      0      0
4 xx33     25      0      0

Upvotes: 0

mdag02
mdag02

Reputation: 1175

Another way with the tidyverse :

library(tidyverse)
library(lubridate)

df %>%
  mutate(month_year = format(dmy(date), "%Y%m")) %>%
  group_by(month_year, id) %>% 
  summarise(s = sum(amount)) %>% 
  spread(month_year, s, fill = 0)

# A tibble: 4 x 4
  id     `201302` `201303` `201304`
* <fctr>    <dbl>    <dbl>    <dbl>
1 xx00        0        0       15.0
2 xx11        0       30.0      0  
3 xx22      100        0        0  
4 xx33       25.0      0        0

Upvotes: 1

akrun
akrun

Reputation: 887891

Convert the 'date' to Date class, extract the 'YearMonth' with format and use xtabs to create the pivot table in base R

df$monthyear <- format(as.Date(df$date, "%d/%m/%Y"), "%Y%m")
xtabs(amount~ id + monthyear, df)
#       monthyear
#id     201302 201303 201304
#  xx00      0      0     15
#  xx11      0     30      0
#  xx22    100      0      0
#  xx33     25      0      0

Upvotes: 0

Related Questions