Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ID <- c(1, 1, 2, 2, 3, 3, 4, 4, 5, 5)
- period<- c(1, 2, 1, 2, 1, 2, 1, 2, 1, 2)
- column1<- c(100, 400, 500, 300, 700, 800, 300, 100, 1100, 100)
- column2<- c(1500, 100,800,900,100,1100,1200,200,100,400)
- column3<- c(200, 1000, 200,NA,500,800,1100,300,100,2000)
- df<- data.frame(ID, period, column1, column2, column3)
- ID period column1 column2 column3
- 1 1 100 1500 200
- 1 2 400 100 1000
- 2 1 500 800 200
- 2 2 300 900 NA
- 3 1 700 100 500
- 3 2 800 1100 800
- 4 1 300 1200 1100
- 4 2 100 200 300
- 5 1 1100 100 100
- 5 2 100 400 2000
- agent_number<- c(100, 200, 300)
- agent_name<- c("agent1", "agent2", "agent3")
- lookup_table<- data.frame(agent_number, agent_name)
- ID period column1 column2 column3 column4 column5 column6 column7
- 1 1 100 1500 200 1 agent1 agent2 <NA>
- 1 2 400 100 1000 1 agent1 <NA> <NA>
- 2 1 500 800 200 1 agent2 <NA> <NA>
- 2 2 300 900 NA 0 agent3 <NA> <NA>
- 3 1 700 100 500 1 agent1 <NA> <NA>
- 3 2 800 1100 800 0 <NA> <NA> <NA>
- 4 1 300 1200 1100 1 agent3 <NA> <NA>
- 4 2 100 200 300 1 agent1 agent2 agent3
- 5 1 1100 100 100 1 agent1 agent1 <NA>
- 5 2 100 400 2000 1 agent1 <NA> <NA>
- agent_number<- c("100", "200", "300")
- df %>% select(ID, column1:column3) %>%
- mutate_at(vars(column1:column3),list(~ ifelse(. == agent_number, 1, 0)))
- left_join(df, lookup_table, by=c("column1"="agent_number"), suffix = c("", ".1"), suffixes_mandatory = c(FALSE, TRUE))
- left_join(df, lookup_table, by=c("column2"="agent_number"), suffix = c("", ".2"), suffixes_mandatory = c(FALSE, TRUE))
- ID period column1 column2 column3 agent_name
- 1 1 100 1500 200 <NA>
- 1 2 400 100 1000 <NA>
- 2 1 500 800 200 <NA>
- 2 2 300 900 NA <NA>
- 3 1 700 100 500 <NA>
- 3 2 800 1100 800 <NA>
- 4 1 300 1200 1100 <NA>
- 4 2 100 200 300 <NA>
- 5 1 1100 100 100 <NA>
- 5 2 100 400 2000 <NA>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement