CODEWITHSUNDEEP
r
David Kružlík
David Kružlík

Reputation: 83

How to create new column by two different variables?

I have a data.frame like this:

Term     Value   Rel     
 1 can       0   0.5       
 2 can       1   0.6        
 3 can       2   0.8       
 4 can       3   1        
 5 lol       0   5.1       
 6 lol       1   5.5    
 7 lol       2  12.9      
 8 lol       3  17.3     
 9 eas       0  10.4     
10 eas       1  74.1    
10 eas       2  26.1     
10 eas       3  61.8

I want to group new column by Value 0-3, or by column Term and get max values from column Rel and add this new column Reltop to an existing data.frame where other values will be NA´s, or blank.

I want to data.frame like this:

Term     Value   Rel   Relmax  
 1 can       0   0.5   NA    
 2 can       1   0.6   NA     
 3 can       2   0.8   NA   
 4 can       3   1     1   
 5 lol       0   5.1   NA   
 6 lol       1   5.5   NA 
 7 lol       2  12.9   NA   
 8 lol       3  17.3   17.3  
 9 eas       0  10.4   NA  
10 eas       1  74.1   74.1 
10 eas       2  26.1   NA  
10 eas       3  61.8   NA

I tried this, but this solution does not keep rest of the df:

data.frame <- data.frame %>%
  group_by(Term) %>%
  summarize(Reltop = max(rel))

Upvotes: 0

Views: 44

Answers (2)

akrun
akrun

Reputation: 887118

We could also do this with

library(dplyr)
df1 %>%
   group_by(Term) %>%
   mutate(Relmax = Rel * NA^(Rel != max(Rel))) %>%
   ungroup

-output

# A tibble: 12 x 4
#   Term  Value   Rel Relmax
#   <chr> <int> <dbl>  <dbl>
# 1 can       0   0.5   NA  
# 2 can       1   0.6   NA  
# 3 can       2   0.8   NA  
# 4 can       3   1      1  
# 5 lol       0   5.1   NA  
# 6 lol       1   5.5   NA  
# 7 lol       2  12.9   NA  
# 8 lol       3  17.3   17.3
# 9 eas       0  10.4   NA  
#10 eas       1  74.1   74.1
#11 eas       2  26.1   NA  
#12 eas       3  61.8   NA

data

df1 <- structure(list(Term = c("can", "can", "can", "can", "lol", "lol", 
"lol", "lol", "eas", "eas", "eas", "eas"), Value = c(0L, 1L, 
2L, 3L, 0L, 1L, 2L, 3L, 0L, 1L, 2L, 3L), Rel = c(0.5, 0.6, 0.8, 
1, 5.1, 5.5, 12.9, 17.3, 10.4, 74.1, 26.1, 61.8)), class = "data.frame",
row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"))

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 388982

You can use ifelse to assign a value only at max position.

library(dplyr)

df %>%
  group_by(term) %>%
  mutate(Relmax = ifelse(Rel == max(Rel), max(Rel), NA)) %>%
  ungroup

#   term  Value   Rel Relmax
#   <chr> <int> <dbl>  <dbl>
# 1 can       0   0.5   NA  
# 2 can       1   0.6   NA  
# 3 can       2   0.8   NA  
# 4 can       3   1      1  
# 5 lol       0   5.1   NA  
# 6 lol       1   5.5   NA  
# 7 lol       2  12.9   NA  
# 8 lol       3  17.3   17.3
# 9 eas       0  10.4   NA  
#10 eas       1  74.1   74.1
#11 eas       2  26.1   NA  
#12 eas       3  61.8   NA

Upvotes: 2

Related Questions