fill column with values in another columns based on their conditions-CodePudding

I would like to fill a column (KE) with values in other columns (K2007/K2008/K2009) based on the conditions (Year). For examle, If "Year" is 2007, KE would be 1.

Year <- c(2007,2008,2009)
K2007 <- c(1,2,3)
K2008 <- c(4,5,6)
K2009 <- c(7,8,9)
KE <- c(1,5,9)

Thanks in advance,

CodePudding user response：

If you don't need to keep the original columns you could pivot the data to a tidier long format, remove the "K" from the original column names, and only keep rows in which Year is the same as the old column:

library(tidyr)
library(dplyr)

dat |>
  pivot_longer(K2007:K2009, values_to = "KE") |> 
  mutate(name = sub('.', '', name) |> as.double()) |> 
  filter(Year == name) |> 
  select(-name)

#> # A tibble: 3 x 2
#>    Year    KE
#>   <dbl> <dbl>
#> 1  2007     1
#> 2  2008     5
#> 3  2009     9

^{Created on 2022-06-23 by the reprex package (v2.0.1)}

CodePudding user response：

Try this

df <- data.frame(K2007 , K2008 , K2009)

KE <- sapply(seq_along(Year) , 
       \(x) df[ x,grep(Year[x] , names(df))])

KE

#[1] 1 5 9

CodePudding user response：

library(tidyverse)

tbl <- tibble(
  year = c(2007,2008,2009),
  k2007 = c(1,2,3),
  k2008 = c(4,5,6),
  k2009 = c(7,8,9))
    
tbl %>% 
  pivot_longer(-year, values_to = 'ke') %>% 
  filter(name == str_c('k', year)) %>% 
  select(-name) %>% 
  left_join(tbl, ., 'year')
    
# A tibble: 3 x 5
   year k2007 k2008 k2009    ke
  <dbl> <dbl> <dbl> <dbl> <dbl>
1  2007     1     4     7     1
2  2008     2     5     8     5
3  2009     3     6     9     9