How can I combine rows within the same data frame in R (based on duplicate values under a specific column)?

r duplicates append combiners
poeticpersimmon picture poeticpersimmon · Mar 13, 2015 · Viewed 7.3k times · Source

Sample of 2 (made-up) example rows in df:

userid   facultyid  courseid schoolid
167       265        NA       1678  
167       71111      301      NA

Suppose that I have a couple hundred duplicate userid like in the above example. However, the vast majority of userid have different values.

How can I combine rows with duplicate userid in such a way as to stick to the column values in the 1st (of the 2) row unless the first value is NA (in which case the NA will be repopulated with whatever value came from the second row)?

In essence, drawing from the above example, my ideal output would contain:

userid   facultyid  courseid schoolid
167       265        301       1678

Answer

bergant picture bergant · Mar 13, 2015 
aggregate(x = df1, by = list(df1$userid), FUN = function(x) na.omit(x)[1])[,-1]

or use dplyr library:

library(dplyr)

df1 %>%
  group_by(userid) %>%
  summarise_each(funs(first(na.omit(.))))

Related questions

Remove duplicated rows

I have read a CSV file into an R data.frame. Some of the rows have the same element in one of the columns. I would like to remove rows that are duplicates in that column. For example: platform_external_…

Read More
Find indices of duplicated rows

Function duplicated in R performs duplicate row search. If we want to remove the duplicates, we need just to write df[!duplicated(df),] and duplicates will be removed from data frame. But how to find the indices of duplicated data? …

Read More
Filtering out duplicated/non-unique rows in data.table

Edit 2019: This question was asked prior to changes in data.table in November 2016, see the accepted answer below for both the current and previous methods. I have a data.table table with about 2.5 million rows. There are two columns. I …

Read More