CODEWITHSUNDEEP
rdataframe
Jirka Čep
Jirka Čep

Reputation: 191

Select values of a row based on whether other row is unique

I would need to create a new column in my data, which would be equal to 'tender' value in case the 'id' appears only once, and to the 'lot' value in case it does not. I cannot do it through anything concerning NA, since the data is incomplete and there is a lot of NAs in there. My idea was to do it that if 'id' is unique, then select

df <- data.frame('id'=c(1,1,2,3,3,4), 
                 'lot'=c(10,20,NA,40,50,NA), 'tender'=c(30,30,30,90,90,40))

A am expecting the output to be:

data.frame('id'=c(1,1,2,3,3,4), 'lot'=c(10,20,NA,40,50,NA), 
           'tender'=c(30,30,30,90,90,40),'price'=c(10,20,30,40,50,40))

Upvotes: 2

Views: 251

Answers (3)

IceCreamToucan
IceCreamToucan

Reputation: 28675

Based on this description, you can use an if statement on the group size with data.table

I would need to create a new column in my data, which would be equal to 'tender' value in case the 'id' appears only once, and to the 'lot' value in case it does not.

library(data.table)
setDT(df)

df[, price := if(.N == 1) tender else lot, by = id]
#    id lot tender price
# 1:  1  10     30    10
# 2:  1  20     30    20
# 3:  2  NA     30    30
# 4:  3  40     90    40
# 5:  3  50     90    50
# 6:  4  NA     40    40

Upvotes: 0

M--
M--

Reputation: 28826

We can do this:

df$price <- apply(df, 1, function(x) min(x["lot"], x["tender"], na.rm = TRUE))

Or in dplyr solution would be:

library(dplyr)
df %>% 
  rowwise() %>% 
  mutate(price = min(lot, tender, na.rm = TRUE))

# # A tibble: 6 x 4
# # Groups:   id [4]
#      id   lot tender price
#   <dbl> <dbl>  <dbl> <dbl>
# 1     1    10     30    10
# 2     1    20     30    20
# 3     2    NA     30    30
# 4     3    40     90    40
# 5     3    50     90    50
# 6     4    NA     40    40

Upvotes: 2

akrun
akrun

Reputation: 886938

Based on the condition, we can do a group by case_when 

library(dplyr)
df %>% 
  group_by(id) %>%
  mutate(price = case_when(n() ==1 & is.na(lot) ~ tender, TRUE ~ lot))

With the OP's current example, coalesce would also work

df %>%
   mutate(price = coalesce(lot, tender))

Upvotes: 2

Related Questions