CODEWITHSUNDEEP
rdataframedplyr
ultron
ultron

Reputation: 443

Replacing an empty cell in one column based on corresponding value from another column?

I have this data frame: 

df <- data.frame(number = c(1,2,3,1,3,4,5,2), 
             name = c('dany', 'rand', 'tanya', 
                      ' ', ' ', 'bruce', 'tony', ' '), 
             grade = c('a', 'b', 'c', ' ', 'c', 'd', 'e', ' '))

which looks like this: 

 > df
# A tibble: 8 × 3
number  name grade
 <dbl> <chr> <chr>
    1  dany     a
    2  rand     b
    3 tanya     c 
    1         
    3           c
    4 bruce     d
    5  tony     e
    2

what I want is this: 

 > df
# A tibble: 8 × 3
number  name grade
 <dbl> <chr> <chr>
    1  dany     a
    2  rand     b
    3 tanya     c 
    1  dany     a  
    3 tanya     c
    4 bruce     d
    5  tony     e
    2  rand     b

How do I accomplish this ? how do I replace the empty spaces with the values corresponding to number column?

If possible, please explain how can I achieve this with dplyr(tidyverse).

Upvotes: 0

Views: 309

Answers (3)

989
989

Reputation: 12937

I would do this in base R using merge:

x <- df$name==" "
A <- df[x,]
B <- df[!x,]
rbind(B, setNames(merge(A, B, by = "number")[,c(1,4,5)], colnames(a)))

   # number  name grade
# 1       1  dany     a
# 2       2  rand     b
# 3       3 tanya     c
# 6       4 bruce     d
# 7       5  tony     e
# 11      1  dany     a
# 21      2  rand     b
# 31      3 tanya     c

Upvotes: 1

Felix Grossmann
Felix Grossmann

Reputation: 1314

This is my solution assuming that there are only numbers with an existing full row somewhere: 

library(tidyverse)

df <- data_frame(number = c(1,2,3,1,3,4,5,2), 
                 name = c('dany', 'rand', 'tanya', 
                          ' ', ' ', 'bruce', 'tony', ' '), 
                 grade = c('a', 'b', 'c', ' ', 'c', 'd', 'e', ' '))

master <- df %>%
  filter(name != " " & grade != "")
master <- master[!duplicated(master),]

only_number <- df %>%
  select(number)

df <- inner_join(only_number, master, by = c("number"))

Upvotes: 1

Sotos
Sotos

Reputation: 51582

It will be easier to handle if you have NAs,

library(dplyr)
library(tidyr)

df[df == ' '] <- NA

df %>% 
 group_by(number) %>% 
 fill(name, grade)

#Source: local data frame [8 x 3]
#Groups: number [5]

#  number  name grade
#   <dbl> <chr> <chr>
#1      1  dany     a
#2      1  dany     a
#3      2  rand     b
#4      2  rand     b
#5      3 tanya     c
#6      3 tanya     c
#7      4 bruce     d
#8      5  tony     e

Upvotes: 2

Related Questions