Insert missing value based on other rows

November 8, 2021

I have a dataframe similar (just longer). My goal is to copy the username of [2] to [8] and [5] to [7]. I know it’s weird making a duplicate, but there’s a reason.

I’ve been trying solving it with an ifelse:

df$Username <- ifelse(df$Name == df$Name, df$Username, NA)

But it doesn’t work. I believe it’s rather simple, but can’t find a function anywhere on stackoverlow. Thanks in advance for any help.

# A tibble: 6 x 2
  Name           Username       
  <chr>          <chr>          
1 ZiadAboultaif  ziad_aboultaif 
2 ScottAitchison ScottAAitchison
3 DanAlbas       DanAlbas       
4 JohnAldag      jwaldag        
5 OmarAlghabra   OmarAlghabra   
6 ShafqatAli     Shafqat_Ali_1  
7 OmarAlghabra   NA
8 ScottAitchison NA

# Reproducilbe data:
df <- structure(list(Name = c("ZiadAboultaif", "ScottAitchison", "DanAlbas", 
"JohnAldag", "OmarAlghabra", "ShafqatAli"), Username = c("ziad_aboultaif", 
"ScottAAitchison", "DanAlbas", "jwaldag", "OmarAlghabra", "Shafqat_Ali_1"
)), row.names = c(NA, -6L), class = c("tbl_df", "tbl", "data.frame"
))

>Solution :

I’d use dplyr:

df %>% group_by(Name) %>% 
   mutate(Username=max(Username, na.rm=TRUE))

Output:

  Name           Username       
  <chr>          <chr>          
1 ZiadAboultaif  ziad_aboultaif 
2 ScottAitchison ScottAAitchison
3 DanAlbas       DanAlbas       
4 JohnAldag      jwaldag        
5 OmarAlghabra   OmarAlghabra   
6 ShafqatAli     Shafqat_Ali_1  
7 OmarAlghabra   OmarAlghabra   
8 ScottAitchison ScottAAitchison

To get the maximum value without having NAs (na.rm=TRUE) inside mutate.