CODEWITHSUNDEEP
rsummary
Matty Cash
Matty Cash

Reputation: 3

Summarizing data for each month

I've got a following table in csv:

Date Results
2015/09/01 1 811
2015/09/03 1 009
2015/09/20 1 889
2015/10/03 1 139
2015/10/06 1 275

I would like to summarize values from "result" for each month and convert them into a bar chart. I cannot find any sum function to fit my problem which can summarize by year and month ignoring days in the month.

I should look like this (except yellow bar):

barchart

Upvotes: 0

Views: 119

Answers (2)

Beni
Beni

Reputation: 251

Probably answered somehwere else too... magic is to convert your Date to a Date format and then extract the month from it... this might do the trick:

data <- data.frame(Results =c(1811, 1009, 1889, 1139, 1275), 
              Date = c("01.09.15", "03.09.15", "20.09.15", "03.10.15", "06.10.15"))

data$Date <- as.Date(data$Date,format = "%d.%m.%y")

library(lubridate)
data$Month <- month(data$Date,label=T)

aggregate(data= data,Results~Month, FUN = sum)

leads to:

  Month Results
   Sep    4709
   Oct    2414

for plotting:

library(ggplot2)

qplot(data=data, x = Month, y=Results, geom="col")

enter image description here

Upvotes: 1

Vin&#237;cius F&#233;lix
Vin&#237;cius F&#233;lix

Reputation: 8826

Here a tidyverse approach that might help you

data <- 
  structure(list(Results = c(1811, 1009, 1889, 1139, 1275),
                 Date = c("01.09.15","03.09.15", "20.09.15", "03.10.15", "06.10.15")),
            class = "data.frame", row.names = c(NA,-5L))


library(dplyr)
library(lubridate)
library(ggplot2)

data %>% 
  mutate(
    Date = dmy(Date),
    Month = month(Date,label = TRUE),
    Year = year(Date)
  ) %>% 
  group_by(Year,Month) %>% 
  summarise(Results = sum(Results,na.rm = TRUE)) %>% 
  ggplot(aes(x = Month,Results))+
  geom_col()

enter image description here

Upvotes: 1

Related Questions