R create new column where the value depends on the matching of two other columns

Question

I have troubles putting my question into words (hence the weird title) but :

I want to create a new column Earnings, that will take the value of the Price of the Date that matches the Last trading day. Like this :

For the first row, the last trading day is 2014-02-17, so I check in the Date column, and in the 5th row, the Date is equal to 2014-02-17. So I take the price of the 5th row which is 235 and assign it to all rows that have 2014-02-17 as the Last trading day.

   Price     Date       `Last trading day`           Earnings
  <dbl>     <date>       <date>                        <dbl>
    224. 2013-01-02   2014-02-17                      235
    224. 2013-01-02   2014-02-17                      235
    224. 2013-01-02   2014-02-17                      235
    224. 2013-01-02   2014-04-19                      260
    235. 2014-02-17   2014-04-19                      260
    260. 2014-04-19   2014-06-17                      253

I tried this, but it doesn't work :

   library(dplyr)
   library(plyr)
   df<-data %>%
   group_by(`Last trading day`) %>%
   mutate(Earnings = if_else(data$Date==data$`Last trading day`, Price, NA_real_))

Thanks a lot for your help.

Don't use `$` in `dplyr` pipes. How do you get `Earnings` value for first row as 235? — Ronak Shah, May 18 '20 at 03:49
It is the price of the date that is equal to the last trading day. @RonakShah — Narjems, May 18 '20 at 14:20
I don't get it `2013-01-02` is not the last trading day still how does it get 235? — Ronak Shah, May 18 '20 at 14:30
@RonakShah For the first row, the last trading day is `2014-02-17`, so I check in the Date column, and in the 5th row, the Date is equal to `2014-02-17`. So I take the price of the 5th row which is 235 and assign it to all rows that have `2014-02-17` as the `Last trading day`. — Narjems, May 18 '20 at 14:34
The last trading day `2014-06-17 ` is not present in `Date` anywhere . How does it get 253? — Ronak Shah, May 18 '20 at 14:42
@RonakShah it's just the head of the data, but it is present below. — Narjems, May 18 '20 at 14:43

score 2 · Accepted Answer · answered May 18 '20 at 14:54

2

We can use match :

df$Earnings <- df$Price[match(df$Last_trading_day, df$Date)]

Using it in dplyr pipe :

library(dplyr)
df %>% mutate(Earnings = Price[match(Last_trading_day, Date)])

Another option is to join dataframe with itself.

library(dplyr)
df %>% left_join(df, by = c('Last_trading_day' = 'Date'))

I renamed the spaces in column name of Last Trading day with an underscore.

answered May 18 '20 at 14:54

Ronak Shah

377,200
20
156
213

Thanks a lot ! It works – Narjems May 18 '20 at 15:05

akrun · Answer 2 · 2020-05-19T21:51:29.630

1

We can remove the data$ as it will take the whole column by breaking the group attribute instead of the values within each group

library(dplyr)
data %>%
   group_by(`Last trading day`) %>%
   mutate(Earnings = if_else(Date== `Last trading day`, Price, NA_real_))

Or another option is case_when

data %>%
   group_by(`Last trading day`) %>%
   mutate(Earnings = case_when(Date== `Last trading day` ~ Price))

Also, as we are comparing elementwise, we don't need any group_by

data %>%      
   mutate(Earnings = if_else(Date== `Last trading day`, Price, NA_real_))

Or with case_when remove the group_by

The above solutions were based on the code OP showed. If we need to do a replacement based on the two columns

library(data.table)
setDT(df)[df, on = .(Last_trading_day =  Date)]

edited May 19 '20 at 21:51

answered May 18 '20 at 02:22

akrun

874,273
37
540
662

Thanks for your reply. But I think that this code compares within a row, meaning that it only works if the `Last trading day` is equal to `Date` on the same row. What I want it to do is compare with all rows, like in the example. – Narjems May 18 '20 at 14:23
1

@Narjems this is based on your code and how it should be corrected – akrun May 18 '20 at 18:22
@Narjems It's okay, but based on the input on your dataset, I was trying to help – akrun May 19 '20 at 17:40

R create new column where the value depends on the matching of two other columns

2 Answers2