I have a very specific dataset it looks something like this:

How do I make it look like this:

Where rows with the same record_id and event_id get merged together, where NaN values are replaced with the other value, and if both values are NaN, then NaN can be kept (like in the forth and fifth row in the original dataframe).

Assume that only one of the related cells have a value and all others have NaN.

This should apply to all columns of the data, there are thousands of columns and rows.

I tried using group by, but don't know how to continue.

CodePudding user response：

With R

library(dplyr)
df1 %>%
   group_by(record_id, event_id) %>%
   summarise(across(everything(),  ~.x[!is.na(.x)][1]))