Column name of last non-NA row per row; using tidyverse solution?

Question

Brief Dataset description: I have survey data generated from Qualtrics, which I've imported into R as a tibble. Each column corresponds to a survey question, and I've preserved the original column order (to correspond with the order of the questions in the survey).

Problem in plain language: Due to normal participant attrition, not all participants completed all of the questions in the survey. I want to know how far each participant got in the survey, and the last question they each answered before stopping.

Problem statement in R: I want to generate (using tidyverse):

1) A new column (lastq) that lists, for each row (i.e. for each participant), the name of the last non-NA column (i.e. the name of the last question they completed).
2) A second new column that lists the number of the column in lastq

Sample dataframe df

df <- tibble(
  year = c(2015, 2015, 2016, 2016),
  grade = c(1, NA, 1, NA),
  height = c("short", "tall", NA, NA),
  gender = c(NA, "m", NA, "f")
 )

Original df

  # A tibble: 4 x 4
   year grade height gender
  <dbl> <dbl>  <chr>  <chr>
1  2015     1  short   <NA>
2  2015    NA   tall      m
3  2016     1   <NA>   <NA>
4  2016    NA   <NA>      f

Desired final df

   # A tibble: 4 x 6
   year grade height gender  lastq lastqnum
  <dbl> <dbl>  <chr>  <chr>  <chr>    <dbl>
1  2015     1  short   <NA> height        3
2  2015    NA   tall      m gender        4
3  2016     1   <NA>   <NA>  grade        2
4  2016    NA   <NA>      f gender        4

There are some other related questions, but I can't seem to find any answers focused on extracting the column names (vs. the values themselves) based on a tibble of mixed variable classes (vs. all numeric), using a tidyverse solution

What I've been trying - I know there's something I'm missing here... :

ds %>% map(which(!is.na(.)))
ds %>% map(tail(!is.na(.), 2))
ds %>% rowwise() %>% mutate(last = which(!is.na(ds)))

?

Thank you so much for your help!

Martin Morgan Martin Morgan · Accepted Answer · 2018-03-25T19:55:55

Write a function that solves the problem, following James' suggestion but a little more robust (handles the case when all answers are NA)

f0 = function(df) {
    idx = ifelse(is.na(df), 0L, col(df))
    apply(idx, 1, max)
}

The L makes the 0 an integer, rather than numeric. For a speed improvement (when there are many rows), use the matrixStats package

f1 = function(df) {
    idx = ifelse(is.na(df), 0L, col(df))
    matrixStats::rowMaxs(idx, na.rm=TRUE)
}

Follow markus' suggestion to use this in a dplyr context

mutate(df, lastqnum = f1(df), lastq = c(NA, names(df))[lastqnum + 1])
df %>% mutate(lastqnum = f1(.), lastq = c(NA, names(.))[lastqnum + 1])

or just do it

lastqnum = f1(df)
cbind(df, lastq=c(NA, names(df))[lastqnum + 1], lastqnum)

Edited after acceptance I guess the tidy approach would be first to tidy the data into long form

df1 = cbind(gather(df), id = as.vector(row(df)), event = as.vector(col(df)))

and then to group and summarize

group_by(df1, id) %>%
    summarize(lastq = tail(event[!is.na(value)], 1), lastqname = key[lastq])

This doesn't handle the case when here are no answers.

Column name of last non-NA row per row; using tidyverse solution?

1 Answers