calculate 5 year average of panel data with factors kept

user_13599401 0
Viewed 45

I have a panel data set that may look like

set.seed(123)
df <- data.frame(
  year = rep(2011:2020,5),
  county = rep(c("a","b",'c','d','e'), each=10),
  state = rep(c("A","B",'C','D','E'), each=10),
  country = rep(c("AA","BB",'CC','DD','EE'), each=10),
  var1 = runif(50, 0, 50),
  var2 = runif(50, 50, 100)
)

I want to transform the panel data set to 5 year averages of the counties by

df <- df %>% 
  mutate(period = cut(df$year, seq(2011, 2021, by = 5),right = F)) %>% 
  group_by(county, period) %>% 
  summarise_all(mean)

The data set looks like

   county period       year state country  var1  var2
   <chr>  <fct>       <dbl> <dbl>   <dbl> <dbl> <dbl>
 1 a      [2011,2016)  2013    NA      NA  33.1  69.7
 2 a      [2016,2021)  2018    NA      NA  24.7  73.6
 3 b      [2011,2016)  2013    NA      NA  27.6  72.3
 4 b      [2016,2021)  2018    NA      NA  24.7  83.1
 5 c      [2011,2016)  2013    NA      NA  38.7  75.7
 6 c      [2016,2021)  2018    NA      NA  22.8  66.8
 7 d      [2011,2016)  2013    NA      NA  33.8  72.2
 8 d      [2016,2021)  2018    NA      NA  20.0  83.7
 9 e      [2011,2016)  2013    NA      NA  14.9  71.0
10 e      [2016,2021)  2018    NA      NA  19.6  70.4

The warming messages are, for example

In mean.default(state) :
  argument is not numeric or logical: returning NA

Is there a smart way (not by merging as actually, I have a lot of character columns) to keep the time-invariant character of each county after the transformation? What I desire is

   county period       year state country  var1  var2
   <chr>  <fct>       <dbl> <dbl>   <dbl> <dbl> <dbl>
 1 a      [2011,2016)  2013    A      AA  33.1  69.7
 2 a      [2016,2021)  2018    A      AA  24.7  73.6
 3 b      [2011,2016)  2013    B      BB  27.6  72.3
 4 b      [2016,2021)  2018    B      BB  24.7  83.1
 5 c      [2011,2016)  2013    C      CC  38.7  75.7
 6 c      [2016,2021)  2018    C      CC  22.8  66.8
 7 d      [2011,2016)  2013    D      DD  33.8  72.2
 8 d      [2016,2021)  2018    D      DD  20.0  83.7
 9 e      [2011,2016)  2013    E      EE  14.9  71.0
10 e      [2016,2021)  2018    E      EE  19.6  70.4

Thank you in advance!

dplyr dataframe r
1 Answers

The warnning results from that summarise_all(mean) calculates averages not only on var1 & var2 but on state & country. If you want to keep state and country as grouping columns, you should put them into group_by():

library(dplyr)

df %>%
  group_by(county, state, country,
           period = cut(year, seq(2011, 2021, by = 5), right = FALSE)) %>%
  summarise_all(mean) %>%
  ungroup()

# # A tibble: 10 × 7
#    county state country period       year  var1  var2
#    <chr>  <chr> <chr>   <fct>       <dbl> <dbl> <dbl>
#  1 a      A     AA      [2011,2016)  2013  33.1  69.7
#  2 a      A     AA      [2016,2021)  2018  24.7  73.6
#  3 b      B     BB      [2011,2016)  2013  27.6  72.3
#  4 b      B     BB      [2016,2021)  2018  24.7  83.1
#  5 c      C     CC      [2011,2016)  2013  38.7  75.7
#  6 c      C     CC      [2016,2021)  2018  22.8  66.8
#  7 d      D     DD      [2011,2016)  2013  33.8  72.2
#  8 d      D     DD      [2016,2021)  2018  20.0  83.7
#  9 e      E     EE      [2011,2016)  2013  14.9  71.0
# 10 e      E     EE      [2016,2021)  2018  19.6  70.4

If the grouping columns are simply county and period, and other categorical variables are unique in each group, you could keep them by just leaving the first values with first() while doing summarise().

df %>%
  group_by(county,
           period = cut(year, seq(2011, 2021, by = 5), right = FALSE)) %>%
  summarise(across(!where(is.numeric), first),
            across( where(is.numeric), mean)) %>%
  ungroup()
user_100689850
Related