Pfthb

I have a dataframe that looks something like this:

 Name Variable1 Value1 Variable2 Value2
1 Michael A 2 <NA> <NA>
2 Michael B 3 <NA> <NA>
3 Michael <NA> <NA> X 10
4 John B 5 <NA> <NA>
5 John E 3 <NA> <NA>
6 John <NA> <NA> Y 20
7 John <NA> <NA> Z 40
8 Paul C 10 <NA> <NA>
9 Paul D 5 <NA> <NA>

In all rows, there is either a Variable1/Value1 pair, or a Variable2/Value2 pair.
I'm trying to use dplyr with a combination of group_by and filter functions to generate a dataframe that has (1) only one row for each name and (2) the max-Value pairs for both 1 and 2. In this case, my final table would look something like:

 Name Variable1 Value1 Variable2 Value2
1 Michael B 3 X 10
2 John B 5 Z 40
3 Paul C 10 <NA> <NA>

I can't seem to get the correct flow of grouping to execute this properly. One solution I've thought of is to split the dataframe into two for each pair, filter, using group_by and filter on each separately, and then joining them back together based on Name, but I was wondering if anybody had a more straightforward solution to offer. Maybe something with summarise?

Thank you!

EDIT: Corrected missing NA values in table.

library(dplyr)

df[is.na(df)] <- 0

df1 <-
 df %>%
 select(1:3)

df1_max <-
 df %>%
 group_by(Name) %>%
 summarise(Value1 = max(Value1))

df2 <-
 df %>%
 select(c(1, 4:5))

df2_max <-
 df %>%
 group_by(Name) %>%
 summarise(Value2 = max(Value2))

result <- left_join(
 left_join(df1_max, df1),
 left_join(df2_max, df2) %>%
 distinct()
) %>%
 select(c(1, 3, 2, 5, 4))

result[result == 0] <- NA

Result:

result

# A tibble: 3 x 5
 Name Variable1 Value1 Variable2 Value2
 <chr> <chr> <dbl> <chr> <dbl>
1 John B 5 Z 40
2 Michael B 3 X 10
3 Paul C 10 <NA> NA

Notes:

• In data frames, the elements in a row are not independent from each other. So you shouldn't be able to dissociate them from each other. But this is what you are trying to do since the max value for Variable1 and the max value for Variable2 are not on the same row, yet you want your result to have the max for each (thus creating a row that does not exist in your original data frame). This is why I split your data frame into 2, then re-associated them with a join.




• The max of NA and a value is NA (we don't know what value NA is since it is missing, so we cannot know what the max is). Yet, you are considering NA to be inferior to any value. Since this is not how R works, I had to assign 0 to your NA to get the max the way you consider it, then reassign NA to 0 at the end.




• You are missing two values in your data and I considered them to be NA.