CODEWITHSUNDEEP
rdataframerowdata-manipulation
confusedindividual
confusedindividual

Reputation: 619

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

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

Upvotes: 1

Views: 995

Answers (3)

AndrewGB
AndrewGB

Reputation: 16856

Here's a tidyverse option, where I put into long form, then filter to keep only the values with 4 and only the first and last occurrence. Then, I create a new column to denote whether it is the first or last value, then pivot back to the wide format.

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

Data

df <- structure(list(ID = 1:3, WZ_1 = c(5L, 4L, 4L), WZ_2 = c(4L, 4L, 
4L), WZ_3 = c(4L, 3L, 4L), WZ_4 = c(3L, 3L, 4L)), class = "data.frame", row.names = c(NA, 
-3L))

Upvotes: 0

Ma&#235;l
Ma&#235;l

Reputation: 51994

With max.col:

data.frame(ID = df$ID,
           First = names(df)[max.col(df == 4, ties.method = "first")],
           Last = names(df)[max.col(df == 4, ties.method = "last")])

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

data

df <- read.table(header= T, text= "ID WZ_1 WZ_2 WZ_3 WZ_4
1  5    4    4    3 
2  4    4    3    3
3  4    4    4    4 ")

Upvotes: 2

Sweepy Dodo
Sweepy Dodo

Reputation: 1873

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))])
       )
   ]

Upvotes: 2

Related Questions