CODEWITHSUNDEEP
rdplyr
Nick Criswell
Nick Criswell

Reputation: 1743

R dplyr - Add Column Based on Results in Other Rows

I have a list of item numbers and inventory locations. Items can appear in two types of inventory locations - warehouse or par locations. I would like to add a column to my data to indicate if a particular item number is ever associated with a warehouse location. Sample data is below:

item_num <- c("Item - 1", "Item - 2", "Item - 3", "Item - 1", "Item - 3", 
          "Item - 2")
locs <- c("warehouse", "par", "par", "par", "warehouse", "par")
fake_data <- tibble(item_num, locs)

fake_data
# A tibble: 6 x 2
  item_num      locs
     <chr>     <chr>
1 Item - 1 warehouse
2 Item - 2       par
3 Item - 3       par
4 Item - 1       par
5 Item - 3 warehouse
6 Item - 2       par

I would like to add the a column that would be TRUE for all Item - 1 and Item - 3 since there is a warehouse location for both of those in my data. For Item - 2 it would be false. Something like this:

  item_num      locs wh_exists
1 Item - 1 warehouse      TRUE
2 Item - 2       par     FALSE
3 Item - 3       par      TRUE
4 Item - 1       par      TRUE
5 Item - 3 warehouse      TRUE
6 Item - 2       par     FALSE

My messy solution is below. 

wh_locs <- fake_data %>%
  filter(locs == "warehouse")
fake_data$wh_exist <- fake_data$item_num %in% wh_locs$item_num

fake_data

# A tibble: 6 x 3
  item_num      locs wh_exist
     <chr>     <chr>    <lgl>
1 Item - 1 warehouse     TRUE
2 Item - 2       par    FALSE
3 Item - 3       par     TRUE
4 Item - 1       par     TRUE
5 Item - 3 warehouse     TRUE
6 Item - 2       par    FALSE

This works, but it seems to me that there should be some clever way to use mutate and group_by to get the answer so I can keep it all in one piped set of functions.

Thank you.

Upvotes: 0

Views: 192

Answers (2)

akrun
akrun

Reputation: 887831

We can use any with ==

fake_data %>%
      group_by(item_num) %>% 
      mutate(wh_exists = any(locs == "warehouse"))
#     item_num      locs wh_exists
#     <chr>     <chr>     <lgl>
#1 Item - 1 warehouse      TRUE
#2 Item - 2       par     FALSE
#3 Item - 3       par      TRUE
#4 Item - 1       par      TRUE
#5 Item - 3 warehouse      TRUE
#6 Item - 2       par     FALSE

A similar option using data.table

library(data.table)
setDT(fake_data)[, wh_exists := any(locs == "warehouse"), by = item_num]

Upvotes: 3

akuiper
akuiper

Reputation: 215117

Use group_by with %in% operator to check if warehouse is within the locs for each group:

library(dplyr)
fake_data %>% group_by(item_num) %>% mutate(wh_exists = 'warehouse' %in% locs)

# Source: local data frame [6 x 3]
# Groups: item_num [3]
# 
#   item_num      locs wh_exists
#     <fctr>    <fctr>     <lgl>
# 1 Item - 1 warehouse      TRUE
# 2 Item - 2       par     FALSE
# 3 Item - 3       par      TRUE
# 4 Item - 1       par      TRUE
# 5 Item - 3 warehouse      TRUE
# 6 Item - 2       par     FALSE

Upvotes: 1

Related Questions