I have a dataframe df1 which is like this:

Currently, the dataframe only has the category for the first time the Name appeared.

However, I would like to fill the categories within the dataframe to make it like this based on the Name:

Would appreciate the help! :)

CodePudding user response：

You could use dplyr::group_by and then tidyr::fill.

library(dplyr)
library(tidyr)

df1 %>% 
  group_by(Name) %>% 
  fill(Category) %>%
  ungroup()

Result:

  Name    Category 
  <chr>   <chr>    
1 Apple   Fruit    
2 Banana  Fruit    
3 Cabbage Vegetable
4 Apple   Fruit    
5 Orange  Fruit    
6 Cabbage Vegetable
7 Toy     Misc     
8 Apple   Fruit 

Data:

df1 <- structure(list(Name = c("Apple", "Banana", "Cabbage", "Apple", 
"Orange", "Cabbage", "Toy", "Apple"), Category = c("Fruit", "Fruit", 
"Vegetable", NA, "Fruit", NA, "Misc", NA)), class = "data.frame", row.names = c(NA, 
-8L))

CodePudding user response：

We can use left_join from dplyr package

library(dplyr)
dat %>% 
  left_join(dat %>% 
              drop_na() %>% 
              unique(), by = "Name") %>% 
  select(Name,
         Category = Category.y)
     Name  Category
1   Apple     Fruit
2  Banana     Fruit
3 Cabbage Vegetable
4   Apple     Fruit
5  Orange     Fruit
6 Cabbage Vegetable
7     Toy      Misc
8   Apple     Fruit

Consider this alternative using %l% from qdapTools package

library(qdapTools)
dat$Category <-  dat[,1] %l% unique(dat[complete.cases(dat), ])  
dat
     Name  Category
1   Apple     Fruit
2  Banana     Fruit
3 Cabbage Vegetable
4   Apple     Fruit
5  Orange     Fruit
6 Cabbage Vegetable
7     Toy      Misc
8   Apple     Fruit

We can use lookup as well from qdapTools package

dat$Category <- lookup(dat[,1],  unique(dat[complete.cases(dat), ]))

CodePudding user response：

In base R we can use match (which is pretty analogous to excel's vlookup that you are familiar with)

df1[,'Category'] = df1[,'Category'][match(df1$Name, df1$Name)]