CODEWITHSUNDEEP
rdplyrdata.table
jsimpsno
jsimpsno

Reputation: 460

Return value from another column using dplyr

I’m trying to return a value from another column based on filtering of a different column in the same row grouped by another column.

I’ve tried :

library(dplyr)
library(data.table)

column1<- c("A","B","C","A","B","C")
column2<- c(1,2,3,5,6,8)
column3<- c("Hat","Shirt","Pants","Shoes","Jacket","Coat")

Df<- data.table(column1,column2,column3)

Df<- Df %>%
  grouped_by(column1) %>%
  mutate(dfcol3filtered= Df$col3[Df$col2= min(df$col2)])

to no avail.

I would like to have dfcol3filtered return the minimum value for each grouping.

This can be done in excel but can’t figure out how to do it in r.

Upvotes: 3

Views: 3225

Answers (1)

www
www

Reputation: 39154

The solution.

Df %>%
  group_by(column1) %>%
  mutate(dfcol3filtered = column3[which.min(column2)]) %>%
  ungroup()
# # A tibble: 6 x 4
#   column1 column2 column3 dfcol3filtered
#   <chr>     <dbl> <chr>   <chr>         
# 1 A          1.00 Hat     Hat           
# 2 B          2.00 Shirt   Shirt         
# 3 C          3.00 Pants   Pants         
# 4 A          5.00 Shoes   Hat           
# 5 B          6.00 Jacket  Shirt         
# 6 C          8.00 Coat    Pants

The solution.

Df[, dfcol3filtered := column3[which.min(column2)], by = column1][]
#    column1 column2 column3 dfcol3filtered
# 1:       A       1     Hat            Hat
# 2:       B       2   Shirt          Shirt
# 3:       C       3   Pants          Pants
# 4:       A       5   Shoes            Hat
# 5:       B       6  Jacket          Shirt
# 6:       C       8    Coat          Pants

Another solution based on join.

Df %>%
  group_by(column1) %>%
  filter(column2 == min(column2)) %>%
  select(column1, dfcol3filtered = column3) %>%
  right_join(Df, by = "column1") %>%
  select(names(Df), dfcol3filtered) %>%
  ungroup()
# # A tibble: 6 x 4
#   column1 column2 column3 dfcol3filtered
#   <chr>     <dbl> <chr>   <chr>         
# 1 A          1.00 Hat     Hat           
# 2 B          2.00 Shirt   Shirt         
# 3 C          3.00 Pants   Pants         
# 4 A          5.00 Shoes   Hat           
# 5 B          6.00 Jacket  Shirt         
# 6 C          8.00 Coat    Pants

Upvotes: 5

Related Questions