R: How to combine rows of a data frame with the same id and take the newest non-NA value?

Question

Example data frame

date       name     speed  acceleration
1/1/17     bob      5      NA
1/1/15     george   5      NA
1/1/15     bob      NA     4
1/1/17     bob      4      NA

I want to condense all rows with the same name into one row and keep the newest non-na value for the speed and acceleration column.

Desired output

date       name     speed  acceleration
1/1/17     bob      5      4
1/1/15     george   5      NA

See [this post](http://stackoverflow.com/questions/24237399/how-to-select-the-rows-with-maximum-values-in-each-group-with-dplyr) for how to select the maximum value (max works with dates). — Malo Marrec, Feb 06 '17 at 23:24
@Malo: Here it's not the max but the newest, you don't take the max speed but the latest speed w.r.t. date column. So i don't think your link is relevant. — Scarabee, Feb 07 '17 at 00:08

Scarabee · Accepted Answer · 2017-02-06T23:47:21.857

3

You can do it this way:

library(dplyr)
library(lubridate)

input = read.table(text = 
 "date       name     speed  acceleration
  1/1/17     bob      5      NA
  1/1/15     george   5      NA
  1/1/15     bob      NA     4
  1/1/17     bob      4      NA",
  header = TRUE, stringsAsFactors = FALSE)

output <- input %>%
  mutate(date = mdy(date)) %>% # or maybe dmy, depending on your date format
  group_by(name) %>%
  arrange(desc(date)) %>%
  summarise_all(funs(na.omit(.)[1]))

output
# # A tibble: 2 × 4
#     name       date speed acceleration
#    <chr>     <date> <int>        <int>
# 1    bob 2017-01-01     5            4
# 2 george 2015-01-01     5           NA

edited Feb 06 '17 at 23:47

answered Feb 06 '17 at 23:36

Scarabee

5,437
5
29
55

It's a little simpler to just use `na.omit`. – alistaire Feb 06 '17 at 23:39
2

Something like `dat %>% ... summarize_all(function(x) na.omit(x)[1] )` would simplify this too applying the same function to all columns. – thelatemail Feb 06 '17 at 23:40
or use `funs`: `summarise_all(funs(na.omit(.)[1]))` – alistaire Feb 06 '17 at 23:42

score 0 · Answer 2 · answered Feb 07 '17 at 03:41

Here is an option using data.table. Convert the 'data.frame' to 'data.table' (setDT(input)), order the 'date' after converting to Date class, grouped by 'name', loop through the columns and get the first non-NA element

library(data.table)
library(lubridate)
setDT(input)[order(-mdy(date)), lapply(.SD, function(x) x[!is.na(x)][1]), name]
#     name   date speed acceleration
#1:    bob 1/1/17     5            4
#2: george 1/1/15     5           NA

R: How to combine rows of a data frame with the same id and take the newest non-NA value?

2 Answers2