Finding all columns in data frame of a certain value by row-CodePudding

I am trying to find the first column that has a specific number and the last column of the same value by each row in a dataframe. See example data and desired output if the number was 4.

Example Data

ID WZ_1 WZ_2 WZ_3 WZ_4
1  5    4    4    3 
2  4    4    3    3
3  4    4    4    4

Example Output

ID First Last 
1  WZ_2  WZ_3
2  WZ_1  WZ_2
3  WZ_1  WZ_4

CodePudding user response：

library(data.table)

# dummy data
# use setDT(df) if yours isn't a datatable already
df <- data.table(id = 1:3
                 , a = c(4,4,0)
                 , b = c(0,4,0)
                 , c = c(4,0,4)
                 ); df
   id a b c
1:  1 4 0 4
2:  2 4 4 0
3:  3 0 0 4

# find 1st & last column with target value
df[, .(id
       , first = apply(.SD, 1, \(i) names(df)[min(which(i==4))])
       , last = apply(.SD, 1, \(i) names(df)[max(which(i==4))])
       )
   ]

CodePudding user response：

Here's a tidyverse option:

library(tidyverse)

df %>% 
  pivot_longer(-ID) %>% 
  group_by(ID) %>% 
  filter(value == 4) %>% 
  filter(row_number()==1 | row_number()==n()) %>% 
  mutate(col = c("First", "Last")) %>% 
  pivot_wider(names_from = "col", values_from = "name") %>% 
  select(-value)

Output

  <int> <chr> <chr>
1     1 WZ_2  WZ_3 
2     2 WZ_1  WZ_2 
3     3 WZ_1  WZ_4