CODEWITHSUNDEEP
rdplyr
Lalitha
Lalitha

Reputation: 147

aggregate from weekly to monthly data frame for a grouped data in R

My df had different states associated with their cities of weekly data with value_1 and value_2

 structure(list(city_id = c("FS030", "FS030", 
    "FS030", "FS030", "FS030", "FS030", "FS030", "FS030", "FS030", 
    "FS030", "FS030", "FS030", "FS030", "FS030", "FS030", "FS030", 
    "FS030", "FS030", "FS030", "FS030", "FS030", "FS030", "FS030", 
    "FS030", "FS030", "FS030", "FS030", "FS030", "FS030", "FS030", 
    "FS030", "FS030", "FS030", "FS030", "FS030", "FS030", "FS030", 
    "FS030", "FS030", "FS030"), state_id = c(580L, 
    580L, 580L, 580L, 580L, 580L, 580L, 580L, 580L, 580L, 580L, 580L, 
    580L, 580L, 580L, 580L, 580L, 580L, 580L, 580L, 580L, 580L, 580L, 
    580L, 580L, 580L, 580L, 580L, 580L, 580L, 580L, 580L, 580L, 580L, 
    580L, 580L, 580L, 580L, 580L, 580L), dt = structure(c(17650, 
    17685, 17517, 17622, 17566, 17475, 17734, 17559, 17636, 17657, 
    17461, 17713, 17608, 17503, 17510, 17454, 17727, 17692, 17664, 
    17587, 17524, 17538, 17419, 17440, 17594, 17447, 17468, 17678, 
    17552, 17629, 17426, 17643, 17720, 17706, 17601, 17489, 17580, 
    17671, 17545, 17699), class = "Date"), value_1 = c(0.411567769583333, 
    0.431277450416667, 0.35, 0.388759958333333, 0.425288925833333, 
    0.35, 0.510489409, 0.35, 0.413527006666667, 0.435785208333333, 
    0.35, 0.3437731696875, 0.370323125, 0.35, 0.35, 0.35, 0.3289119371875, 
    0.35, 0.385370253333333, 0.386275627916667, 0.35, 0.35, 0.35, 
    0.35, 0.38542251125, 0.35, 0.35, 0.433777874583333, 0.35, 0.37273111375, 
    0.35, 0.414055592916667, 0.36269250125, 0.3190907646875, 0.38305069625, 
    0.35, 0.36493013, 0.397780668333333, 0.35, 0.31741553375), value_2 = c(0.204795420930233, 
    0.171267078372093, 0.35, 0.255914227906977, 0.193582022093023, 
    0.35, 0.25652965627907, 0.35, 0.177623573488372, 0.196440658837209, 
    0.35, 0.19950949372093, 0.20368746627907, 0.35, 0.35, 0.35, 0.197593547674419, 
    0.194895088139535, 0.177148813255814, 0.168303015581395, 0.35, 
    0.35, 0.35, 0.35, 0.16433289372093, 0.35, 0.35, 0.169355578604651, 
    0.35, 0.174366632325581, 0.35, 0.183884318372093, 0.294483633953488, 
    0.193520130465116, 0.206268007209302, 0.35, 0.217896235813953, 
    0.177587891395349, 0.35, 0.207436642325581)), class = c("data.table", 
    "data.frame"), row.names = c(NA, -40L), .internal.selfref = <pointer: 0x000000000b7d1ef0>)

Would like to calculate the growth rate of each city using dplyr. Desired output : 

state city value1 value2
1      x    0.32    .11

Note the date are not in order so we need to order it first and then calculate growth rate of each city using dplyr

Upvotes: 1

Views: 196

Answers (2)

riyan
riyan

Reputation: 75

Might work like this also 

growth1 <- function(x) ((last(x)-first(x))/first(x))* 100
    library(dplyr)
    df <- df %>%
    group_by(state,city) %>%
    summarise_at(vars(starts_with('value')),growth1)

Upvotes: 0

akrun
akrun

Reputation: 886948

We could group by 'city_id', 'state_id' and rounded date of 'dt' and get the mean of 'value' columns

library(data.table)
library(lubridate)
dt1[, lapply(.SD, mean), 
      .(three_month = round_date(dt, "quarter"), city_id, state_id)]

Or with dplyr

library(tidyverse)
dt1 %>%
   group_by(three_month = round_date(dt, "quarter"), city_id, state_id) %>%
   summarise_at(vars(starts_with('value')), mean)

Upvotes: 4

Related Questions