Merge rows in a dataframe where the rows are disjoint and contain NAs

by

You can use aggregate. Assuming that you want to merge rows with identical values in column name:

aggregate(x=DF[c("v1","v2","v3","v4")], by=list(name=DF$name), min, na.rm = TRUE)
   name v1 v2 v3 v4
1 Yemen  4  2  3  5

This is like the SQL SELECT name, min(v1) GROUP BY name. The min function is arbitrary, you could also use max or mean, all of them return the non-NA value from an NA and a non-NA value if na.rm = TRUE.
(An SQL-like coalesce() function would sound better if existed in R.)

However, you should check first if all non-NA values for a given name is identical. For example, run the aggregate both with min and max and compare, or run it with range.

Finally, if you have many more variables than just v1-4, you could use DF[,!(names(DF) %in% c("code","name"))] to define the columns.

More Related Contents:

  1. Aggregate / summarize multiple variables per group (e.g. sum, mean)
  2. Extract row corresponding to minimum value of a variable by group
  3. How to create a lag variable within each group?
  4. Is there a dplyr equivalent to data.table::rleid?
  5. Getting the top values by group
  6. Fastest way to replace NAs in a large data.table
  7. Fastest way to add rows for missing time steps?
  8. Assign multiple columns using := in data.table, by group
  9. R spreading multiple columns with tidyr [duplicate]
  10. How to replace NA values in a table for selected columns
  11. dplyr filter with condition on multiple columns
  12. Finding Overlaps between interval sets / Efficient Overlap Joins
  13. R – add column that counts sequentially within groups but repeats for duplicates
  14. Change value of variable with dplyr
  15. dplyr – filter by group size
  16. Replace logical values (TRUE / FALSE) with numeric (1 / 0)
  17. How to speed up subset by groups
  18. Calculate group mean while excluding current observation using dplyr
  19. mutate_each / summarise_each in dplyr: how do I select certain columns and give new names to mutated columns?
  20. Canonical tidyverse method to update some values of a vector from a look-up table
  21. Join two data frames in R based on closest timestamp
  22. write.csv for large data.table
  23. data.table row-wise sum, mean, min, max like dplyr?
  24. DT[!(x == .)] and DT[x != .] treat NA in x inconsistently
  25. How to dplyr rename a column, by column index?
  26. How to add only missing Dates in Dataframe
  27. How to use data.table within functions and loops?
  28. What you can do with a data.frame that you can’t with a data.table?
  29. Override column types when importing data using readr::read_csv() when there are many columns
  30. Adding column if it does not exist

Leave a Comment