Using R - reshape a dataframe based on group max values of another dataframe

Question

I am working with a very large dataset. Consider the following example for illustration:

df1<-{data.frame(MyID=c(1, 2, 3, 1, 2, 3, 1, 2, 3, 4, 5),v1=c(0.1, 0.2, NA, 0.4, 0.2, 0.1, 0.8, 0.3, 0.1, 0.4, 0.3), v2=c(NA, 0.4, 0.2, 0.1, 0.8, 0.3, 0.1, 0.4, 0.3, 0.1, 0.2))}

df2<-{data.frame(MyID=c(1, 2, 3, 1, 2, 3, 1, 2, 3, 4, 5),v1=c(10, 8, 0, 6, 10, 5, 3, 1, 10, 8, 3), v2=c(0, 10, 5, 1, 8, 5,10, 3, 3, 1, 5))}

I would like to extract information from df1 but based on maximum values per MyID in df2. The final result should be a dataframe with:

one row per a unique MyID
each column would have the value in df1 corresponding the maximum of MyID group of df2.

The result should be

ExpectedResult<-{data.frame(MyID=c(1, 2, 3, 4, 5),v1=c(0.1,0.2,0.1,0.4,0.3), v2=c(0.1,0.4,0.2,0.1,0.2))}

What I have tried already but solved only a part of the problem:

using groups and finding max per group, e.g. df2Max<- df2 %>% group_by(MyID) %>% slice_max(1,)
splitting the data using e.g. df2.split <- split(df2, list(df2$MyID))

But, I am still not sure how to link the two dataframes to extract what I need.

score 1 · Answer 1 · answered Nov 09 '20 at 05:04

We can group_by MyID and get the index of maximum value in each column and store it in df3.

library(dplyr)

df2 %>%
  group_by(MyID) %>%
  summarise(across(.fns = which.max)) -> df3

We split df3 by row and split df1 by MyID and extract the relevant value using indexing.

df3[-1] <- t(mapply(function(x, y) x[cbind(y, 1:ncol(x))], 
            split(df1[-1], df1$MyID), asplit(df3[-1], 1)))

#   MyID    v1    v2
#  <dbl> <dbl> <dbl>
#1     1   0.1   0.1
#2     2   0.2   0.4
#3     3   0.1   0.2
#4     4   0.4   0.1
#5     5   0.3   0.2

akrun · Accepted Answer · 2020-11-08T23:51:01.627

We get the row index for 'v1', 'v2', column where the values are highest in 'df2' grouped by 'MyID', then do a join with the first dataset by 'MyID' and summarise the 'v1', 'v2' columns based on the index after grouping by 'MyID'

library(dplyr)
df2 %>% 
   group_by(MyID) %>% 
   summarise(rnv1 = row_number()[which.max(v1)], 
             rnv2 = row_number()[which.max(v2)], .groups = 'drop' ) %>%  
   right_join(df1, by = 'MyID') %>%
   group_by(MyID) %>% 
   summarise(v1 = v1[first(rnv1)], v2 = v2[first(rnv2)], .groups = 'drop')

-output

# A tibble: 5 x 3
#   MyID    v1    v2
#  <dbl> <dbl> <dbl>
#1     1   0.1   0.1
#2     2   0.2   0.4
#3     3   0.1   0.2
#4     4   0.4   0.1
#5     5   0.3   0.2

Or another option is a join with data.table

library(data.table)    
nm1 <- names(df2)[-1]
setDT(df1)[setDT(df2)[, lapply(.SD, which.max), MyID], 
    Map(function(x, y) x[first(y)], .SD, mget(paste0("i.", nm1))), 
    on = .(MyID), by = .EACHI]
#   MyID  v1  v2
#1:    1 0.1 0.1
#2:    2 0.2 0.4
#3:    3 0.1 0.2
#4:    4 0.4 0.1
#5:    5 0.3 0.2

Thank you! The thing is that I have thousands of these columns v1, v2, ...vn. Could we modify your solution taking that into account? — ie-con, Nov 08 '20 at 23:50
@ie-con yes, then the updated data.table solution should work for that — akrun, Nov 08 '20 at 23:51

Using R - reshape a dataframe based on group max values of another dataframe

2 Answers2