CODEWITHSUNDEEP
r
user7542670
user7542670

Reputation:

Grouping data by specific observations in R

I want to create a new variable that's derived from specific values in my existing variables. My data frame looks something like the following: 

year <- c("2010", "2011", "2012", "2013", "2014", "2015")
x <- c(2980, 2955, 3110, 2962, 2566, 3788)
y <- c(2453, 2919, 2930, 2864, 2873, 3031)
df <-  data.frame(year, x, y)

More specifically, I want to create a third column, z, that is the ratio of x and y. However, I don't want to create this ratio by simply dividing x by y for each individual year. Instead, I want the values in 2015 (and 2014 etc.) to be an average of this ratio in the three preceding years, i.e. 2014, 2013, and 2012.

I've looked at Wickham's dplyr package and, in particular, the group_by function but I'm stumped because I don't want to group my data by year per se but by each years' three preceding years as illustrated (hopefully) above.

Upvotes: 0

Views: 94

Answers (4)

thisisrg
thisisrg

Reputation: 606

With dplyr and library(zoo):

df_fin<- df %>% mutate( z = rollmeanr(x/y,3,na.pad=TRUE))

I think the column z is what you want but it would be good to have the desired output.

Upvotes: 1

Artem Sokolov
Artem Sokolov

Reputation: 13691

The answers that use zoo::rollmean are all on the correct track, but they have a couple of "off by one" errors in them. First, you actually want zoo::rollmeanr( ..., na.pad=TRUE ) which will correctly pad the output with NA on the left side:

> zoo::rollmeanr( df$x / df$y, 3, na.pad=TRUE )
[1]        NA        NA 1.0962018 1.0359948 0.9962648 1.0590378

The second "off by one" error arises from alignment of this vector with the rest of your data. From your description, you want the value for 2015 to be the average of 2014, 2013, and 2012. However, appending the vector above to your table will make the value for 2015 to be the average of 2015, 2014, and 2013, instead. To correct, you want to omit the last value in your input to the rolling average and prepend an NA to compensate:

> c( NA, zoo::rollmeanr( head(df$x / df$y,-1), 3, na.pad=TRUE ) )
[1]        NA        NA        NA 1.0962018 1.0359948 0.9962648

Putting it all together using dplyr notation:

df %>% mutate( z = c( NA, zoo::rollmeanr( head(x/y,-1), 3, na.pad=TRUE ) ) )
  year    x    y         z
1 2010 2980 2453        NA
2 2011 2955 2919        NA
3 2012 3110 2930        NA
4 2013 2962 2864 1.0962018
5 2014 2566 2873 1.0359948
6 2015 3788 3031 0.9962648

Upvotes: 0

mrhellmann
mrhellmann

Reputation: 5489

library(zoo)
library(dplyr)

df %>% mutate(z = x/y, zz = rollmean(z, 3, fill = NA)

Upvotes: 0

derive111
derive111

Reputation: 143

df$z<-0
for (i in 4:6){
  df$z[i]<-mean(df$x[(i-3):(i-1)])/mean(df$y[(i-3):(i-1)])
}

Whit a loop, you can get this:

  year    x    y        z
1 2010 2980 2453 0.000000
2 2011 2955 2919 0.000000
3 2012 3110 2930 0.000000
4 2013 2962 2864 1.089497
5 2014 2566 2873 1.036038
6 2015 3788 3031 0.996654

Upvotes: 0

Related Questions