Find column name at change of value

Question

I have a dataset like this (reproducible)

X1 <- c(0,0,1,3)
X2 <- c(0,0,4,5)
X3 <- c(0,2,2,6)
X4 <- c(0,0,0,1)

df <- data.frame(rbind(X1, X2, X3, X4))
rownames(df) <- NULL
df

  X1 X2 X3 X4
1  0  0  1  3
2  0  0  4  5
3  0  2  2  6
4  0  0  0  1

I want to add a column, which will take the value of the column name where, per row wise, value changed from 0 to any value greater than 0

Hence expected output is

  X1 X2 X3 X4 Value
1  0  0  1  3    X3
2  0  0  4  5    X3
3  0  2  2  6    X2
4  0  0  1  1    X4

How can I achieve this for each row?

Related: [For each row return the column name of the largest value](https://stackoverflow.com/questions/17735859/for-each-row-return-the-column-name-of-the-largest-value), where e.g. the `max.col` method is described. — Henrik, Nov 13 '18 at 09:28

score 2 · Accepted Answer · answered Nov 13 '18 at 09:18

2

The Vectorized way to do it would be,

names(df)[max.col(df != 0, ties.method = 'first')]
#[1] "X3" "X3" "X2" "X4"

In addition, you can use apply with margin 1 (to do row operations), and find the first index where the diff is not 0, i.e.

names(df)[apply(df, 1, function(i) which(diff(i) != 0)[1]) + 1]
#[1] "X3" "X3" "X2" "X4"

answered Nov 13 '18 at 09:18

Sotos

51,121
6
32
66

2

The vectorized way is a nice catch. – RLave Nov 13 '18 at 09:25

score 1 · Answer 2 · answered Nov 13 '18 at 09:21

1

Another option using apply again:

names(df)[apply(df, 1, function(x) which(x > 0)[1])]
# [1] "X3" "X3" "X2" "X4"

answered Nov 13 '18 at 09:21

nghauran

6,648
2
20
29

score 1 · Answer 3 · answered Nov 13 '18 at 09:29

A tidyverse solution:

df %>%
  rowid_to_column() %>% #Creating an ID
  gather(var, val, -rowid) %>% #Transforming the data from wide to long
  arrange(rowid) %>% #Arranging according ID
  group_by(rowid) %>% #Grouping by ID
  mutate(res = ifelse(cumsum(val) > 0, paste0(var), NA)) %>% #Applying the condition
  filter(res == first(res[!is.na(res)])) %>% #Selecting the relevant value
  left_join(df %>% rowid_to_column(), by = c("rowid" = "rowid")) %>% #Joining with the original df
  ungroup() %>% 
  select(-rowid, -var, -val) #Deleting the redundant variables

  res      X1    X2    X3    X4
  <chr> <dbl> <dbl> <dbl> <dbl>
1 X3       0.    0.    1.    3.
2 X3       0.    0.    4.    5.
3 X2       0.    2.    2.    6.
4 X4       0.    0.    0.    1.

Find column name at change of value

3 Answers3