Fill numeric variable while preserving group

  • A+
Category:Languages

[EDITED to reflect a better example]

Say I have a dataframe like this:

df <- data.frame(x = c("A","A","B", "B"), year = c(2001,2004,2002,2005))  > df   x year 1 A 2001 2 A 2004 3 B 2002 4 B 2005 

How can I increment year by 1 while preserving x? I would like to fill in year so that the sequence is this:

  x year 1 A 2001 2 A 2002 3 A 2003 4 A 2004 5 B 2002 6 B 2003 7 B 2004 8 B 2005 

Can anyone recommend a good way of doing this?

@useR recommend this approach:

> data.frame(year = min(df$year):max(df$year)) %>%    full_join(df) %>%    fill(x)  Joining, by = "year"   year x 1 2001 A 2 2002 B 3 2003 B 4 2004 A 5 2005 B 

However that does not match the desired output.


An option using tidyr::complete and dplyr::lead can be as:

library(tidyverse)  df <- data.frame(x = LETTERS[1:3], year = c(2001,2004,2007))    df %>% mutate(nextYear = ifelse(is.na(lead(year)),year, lead(year)-1)) %>%   group_by(x) %>%   complete(year = seq(year, nextYear, by=1)) %>%    select(-nextYear) %>%   as.data.frame()  #   x year # 1 A 2001 # 2 A 2002 # 3 A 2003 # 4 B 2004 # 5 B 2005 # 6 B 2006 # 7 C 2007 

Edited: The solution for modified data

df <- data.frame(x = c("A","A","B", "B"), year = c(2001,2004,2002,2005)) library(tidyverse) df %>%  group_by(x) %>%   complete(year = seq(min(year), max(year), by=1)) %>%    as.data.frame()   #   x year # 1 A 2001 # 2 A 2002 # 3 A 2003 # 4 A 2004 # 5 B 2002 # 6 B 2003 # 7 B 2004 # 8 B 2005 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: