Re-shape status columns based on value in another column

Question

I have a table that looks like this:

ID    Start_year    Status_2005    Status_2006    Status_2007
 1          2005            GBR            GBR            FRA
 2          2006             NA            FRA            FRA
 3          2007             NA             NA            GBR
 4          2006             NA            UKR            RUS

I would like to re-shape the data so that it gives the status in the years subsequent to the start year. So the above, would look like this:

ID     Year_0    Year_1    Year_2
 1        GBR       GBR       GBR
 2        FRA       FRA        NA
 3        GBR        NA        NA
 4        UKR       RUS        NA

I have been trying to use tidyverse in R, using gather in combination with "starts_with", and mutate to make the new columns. However, I keep ending up with a single column of "years_since_start_year", and can't work out how to spread this column to make my final table.

Any help much appreciated

Answer 1

Here's a data.table approach:

library(data.table)
setDT(df)
df <- melt(df, id.vars = c("ID", "Start_year"))   # melt to long format
df <- df[!is.na(value)]                           # remove NA entries
df[, year := seq_len(.N) -1L, by = ID]            # add year-number
dcast(df, ID ~ year, value.var = "value")         # reshape to wide format
#      ID      0      1      2
#1:     1    GBR    GBR    FRA
#2:     2    FRA    FRA   <NA>
#3:     3    GBR   <NA>   <NA>
#4:     4    UKR    RUS   <NA>

Answer 2

This is how I would do this with tidyverse

library(tidyverse)

# create data
df_raw <- data.frame(ID = c(1:4), 
                 Start_year = c(2005,2006,2007,2006),
                 Status_2005 =c("GBR", NA, NA, NA),
                 Status_2006 =c("GBR", "FRA", NA, "UKR"),
                 Status_2007 =c("FRA", "FRA", "GBR", "RUS"),
                 stringsAsFactors = F)



df <- df_raw %>% 
  gather(starts_with("Status"), key = Key, value = Value ) %>% 
  arrange(ID) %>% 
  drop_na(Value) %>% 
  mutate(cnt = unlist(map(rle(ID)$lengths-1, seq, from = 0, by =1 ))) %>% 
  mutate(Key = paste0("Year_", cnt)) %>% 
  select(-Start_year, -cnt) %>% 
  spread(key = Key, value = Value)

df
#>   ID Year_0 Year_1 Year_2
#> 1  1    GBR    GBR    FRA
#> 2  2    FRA    FRA   <NA>
#> 3  3    GBR   <NA>   <NA>
#> 4  4    UKR    RUS   <NA>

Answer 3

Here is some rough base R + dplyr:

df %>%
  select(starts_with("Status")) %>%
  apply(1, function(x) {x <- x[!is.na(x)]; length(x) <- 3; x}) %>%
  t() %>%
  as.data.frame() %>%
  cbind(df[["ID"]], .) %>%
  setNames(c("ID", paste0("Year_", 1:3)))

  ID Year_1 Year_2 Year_3
1  1    GBR    GBR    FRA
2  2    FRA    FRA   <NA>
3  3    GBR   <NA>   <NA>
4  4    UKR    RUS   <NA>

Tidyverse style:

library(tidyr)
library(dplyr)
df %>%
  select(-Start_year) %>%
  gather(key = "year", value = "country", -ID) %>%
  filter(!is.na(country)) %>%
  group_by(ID) %>%
  mutate(year = paste0("year_", 1:length(year))) %>%
  spread(key = "year", value = "country")

# A tibble: 4 x 4
# Groups:   ID [4]
     ID year_1 year_2 year_3
  <int> <chr>  <chr>  <chr> 
1     1 GBR    GBR    FRA   
2     2 FRA    FRA    NA    
3     3 GBR    NA     NA    
4     4 UKR    RUS    NA