CODEWITHSUNDEEP
r
Syed
Syed

Reputation: 55

How to create new column based on the object name given in the column and Code ifurther

I have below data in the Excel. which I have imported.

Item_code   Price   Raw_Material

 1. 10001jk10002    20  Made with Apple
 2. 10001jk10002    20  Made with Grapes
 3. 10001jk10002    30  Made with Banana
 4. 10011jk10022    60  Made with Grapes
 5. 10011jk10022    60  Made with Grapes

Result I am looking for with New column

Item_code   Price   Raw_Material          Fruit Used    

 1. 10001jk10002    20  Made with Apple     Apple
 2. 10001jk10002    20  Made with Grapes    Grapes
 3. 10001jk10002    30  Made with Banana    Banana
 4. 10011jk10022    60  Made with Grapes    Grapes
 5. 10011jk10022    60  Made with Grapes    Grapes

From New column I want to drive one more new column 'Final Fruite'

Item_code   Price   Raw_Material        Fruit Used  Final Fruit

 1. 10001jk10002    20  Made with Apple     Apple       Banana 
 2. 10001jk10002    20  Made with Grapes    Grapes      Banana 
 3. 10001jk10002    30  Made with Banana    Banana      Banana   
 4. 10011jk10022    60  Made with Grapes    Grapes      Grapes 
 5. 10011jk10022    60  Made with Grapes    Grapes      Grapes

If you can see my first 3 rows are same. First I want to drive the Fruit column based on it Raw_material columns. Fruit name are used in the sentence (which can be random) and then i want to derive another column from the fruit column Final_Fruite no matter what fruit is come next rows I want to return the Banana in my new column

The actual list of the preferred fruit goes to 10. I am looking for dynamic solution. Can anyone suggest how I can do the same to get the desire result.

Upvotes: 1

Views: 37

Answers (2)

Kristian
Kristian

Reputation: 88

library(readxl)
library(dplyr)
library(magrittr)
library(stringr)

fruity <- read_excel("fruity.xlsx")
fruity <- fruity %>% 
  group_by(item_code) %>% 
  mutate(id = row_number()) %>% 
  mutate(fruit_used  = word(raw_material, -1)) 

tmp <- fruity %>%  group_by(item_code) %>% top_n(1, id) %>% 
  select(item_code, fruit_used) %>% 
  set_colnames(c('item_code','final_fruit'))

fruity <- fruity %>% left_join(tmp, by = 'item_code') %>% select(-"id")

Upvotes: 1

akrun
akrun

Reputation: 887711

We can extract the last word with

library(stringi)
df1$Fruite_used <- stri_extract_last(df1$Raw_Material, regex = "\\w+")

Upvotes: 1

Related Questions