Create a column per year defined within a timeframe range

user_19343355 0
Viewed 84

I have a dataframe with variables, 2 of which are the start and end year of the data collection timeframe.

i.e.

Var1  Var2   start   end
 1     A     2000   2022
 2     B     1990   1995
 3     C     2005   2006
...   ...     ...    ...

I would like to modify this dataframe so that a column for every year is displayed and the presence/absence of data for each year is coded as a binary variable (1/0).

Because the dataset has thousands of rows and many varied year ranges I would like this process to be automated and not manual.

i.e.

Var1  Var2   1990   1991  1992 1993 1994 1995 1996 ... 2021  2022
 1     A      0      0     0    0     0   0     0       1      1
 2     B      1      1     1    1     1   1     0       0      0     
 3     C      0      0     0    0     0   0     0       0      0

Then I would like to transform this dataframe so that the years are displayed into 1 row

i.e.

Year  Var1  Var2  Data_Availability
1990  1      A           0
1990  2      B           1
1990  3      C           0
...
2022  1      A           1
2022  2      B           0
2022  3      C           0

Any help on how to achieve this is greatly appreciated.

dummy-variable dataframe r date
5 Answers

This looks a bit cumbersome but it is an option. The methodology is to convert to long so start and end are one column. Then group by the Var2 and complete the sequence of year. Spread to wide to get the dummy variables then convert again to long to get the structure you want, i.e.

library(dplyr)
library(tidyr)

dd %>% 
 pivot_longer(-c(1:2)) %>% 
 group_by(Var2) %>% 
 complete(value = seq(min(value), max(value))) %>% 
 select(1, 2) %>% 
 pivot_wider(names_from = value) %>% 
 mutate_at(-1, ~ (ifelse(is.na(.), 0, 1))) %>% 
 pivot_longer(-1)

# A tibble: 87 x 3
# Groups:   Var2 [3]
#   Var2  name  value
#   <chr> <chr> <dbl>
# 1 A     2000      1
# 2 A     2001      1
# 3 A     2002      1
# 4 A     2003      1
# 5 A     2004      1
# 6 A     2005      1
# 7 A     2006      1
# 8 A     2007      1
# 9 A     2008      1
#10 A     2009      1
user_56355800

My approach is to pivot longer, expanding with all years between 1990 and 2022, and identifying which rows are between start and end.

library(dplyr)
library(tidyr)
dat_long <- dat |> 
        pivot_longer(start:end, values_to = "Year")

dat_long |> 
        full_join(data.frame(Var1 = 1, Var2 = "A", Year = 1990:2022)) |>
        expand(Var1, Var2, Year) |>
        left_join(dat_long) |> 
        group_by(Var1, Var2) |> 
        fill(name) |> 
        mutate(Data_Availability = case_when(name == "start" | name == "end" & lag(name == "start") ~ 1,
                                             TRUE ~ 0)) |> 
        select(Year, Var1, Var2, Data_Availability)
user_139682220

Base R solution:

# Vector of names of the start-end years:
# start_end_year_col_names => character vector 
start_end_year_col_names <- c("start", "end")

# Calculate the range of years in start end dates: 
# year_rng => integer vector
year_rng <- Reduce(
  seq, 
  range(
    unlist(
      df[,start_end_year_col_names]
    )
  )
)

# Compute whether or not start ends fall within range, 
# and column bind other variables: data.frame => stdout(console)
cbind(
    df[,names(df) != start_end_year_col_names],
    setNames(
      data.frame(
        t(
        vapply(
          seq_len(nrow(df)),
          FUN = function(i){
            +(df$start[i] <= year_rng & df$end[i] >= year_rng)
          },
          integer(length(year_rng))
        )
      )
    ),
    year_rng
  )
)

Data:

# df => data.frame

df <- structure(list(Var1 = 1:3, Var2 = c("A", "B", "C"), start = c(2000L, 
1990L, 2005L), end = c(2022L, 1995L, 2006L)), class = "data.frame", row.names = c(NA, 
-3L))
user_92031580 
df <- data.frame(
  stringsAsFactors = FALSE,
  Var1 = c(1L, 2L, 3L),
  Var2 = c("A", "B", "C"),
  start = c(2000L, 1990L, 2005L),
  end = c(2022L, 1995L, 2006L)
)

library(tidyverse)
df %>% 
  mutate(period = map2(start, end, ~seq(.x, .y))) %>% 
  mutate(period_all = list(seq(1990, 2022))) %>% 
  mutate(res = map2(period_all, period, ~+(.x %in% .y))) %>% 
  mutate(res = map2(res, period_all, ~set_names(x = .x, nm = .y))) %>% 
  unnest_wider(res) %>% 
  select(-starts_with("period"))
#> # A tibble: 3 × 37
#>    Var1 Var2  start   end `1990` `1991` `1992` `1993` `1994` `1995` `1996`
#>   <int> <chr> <int> <int>  <int>  <int>  <int>  <int>  <int>  <int>  <int>
#> 1     1 A      2000  2022      0      0      0      0      0      0      0
#> 2     2 B      1990  1995      1      1      1      1      1      1      0
#> 3     3 C      2005  2006      0      0      0      0      0      0      0
#> # … with 26 more variables: `1997` <int>, `1998` <int>, `1999` <int>,
#> #   `2000` <int>, `2001` <int>, `2002` <int>, `2003` <int>, `2004` <int>,
#> #   `2005` <int>, `2006` <int>, `2007` <int>, `2008` <int>, `2009` <int>,
#> #   `2010` <int>, `2011` <int>, `2012` <int>, `2013` <int>, `2014` <int>,
#> #   `2015` <int>, `2016` <int>, `2017` <int>, `2018` <int>, `2019` <int>,
#> #   `2020` <int>, `2021` <int>, `2022` <int>

Created on 2022-06-21 by the reprex package (v2.0.1)

user_120254830

You might do this in two lines of code using seq and ifelse.

oa_seq <- do.call(seq.int, as.list(range(unlist(df[3:4]))))
cbind(df[1:2], `colnames<-`(do.call(rbind, lapply(Map(seq.int, df$start, df$end), \(x) ifelse(oa_seq %in% x, 1, 0))), oa_seq))
#   Var1 Var2 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006
# 1    1    A    0    0    0    0    0    0    0    0    0    0    1    1    1    1    1    1    1
# 2    2    B    1    1    1    1    1    1    0    0    0    0    0    0    0    0    0    0    0
# 3    3    C    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1    1
#   2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022
# 1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1
# 2    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
# 3    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0

R >= 4.1 was used.

Data:

df <- structure(list(Var1 = 1:3, Var2 = c("A", "B", "C"), start = c(2000L, 
1990L, 2005L), end = c(2022L, 1995L, 2006L)), class = "data.frame", row.names = c(NA, 
-3L))
user_65740380
Related