R/Python, how to populate data based on 2 conditions without using simple for loop?

user_8160591 0
Viewed 43

I am doing some accounting but then wonder if there is a better way to replace for loop (CPU intensive) for long list of items.

Suppose we have a transaction record like this:

transaction_data

Qty  Date_purchase  Price_purchase  Date_sale  Price_sale  
5    01/11/2020     ???             02/11/2020 ???         
10   03/11/2020     ???             05/11/2020 ???         
5    03/11/2020     ???             06/11/2020 ???         
....

What I have to do is fill the ??? with the price according to the date in this list.

price_list

Date        Price
01/11/2020  10
02/11/2020  15
03/11/2020  17
....

I have tried merge, join but got stuck at writing the code with tidyverse in R (Can switch to Python if code work faster).

transaction_data


%>% transaction_data$Price_purchase  <- left_join(price_list, by = c('Date_purchase', 'Date')


%>% transaction_data$Price_sale  <- left_join(price_list, by = c('Date_sale', 'Date') (not working)

Edit: all the solutions are all equal good, I wish I could choose all as correct answer

conditional-statements dataframe r python
3 Answers

If we need a for loop, then loop over the vector of 'Date' columns from the 'transaction_date', do the left_join with 'price_list', mutate the 'Price_purchase' as the 'Price' column from the 'price_list' and remove that column

library(dplyr)
nm1 <- c("Date_purchase", "Date_sale")
for(nm in nm1) {
    transaction_date <- left_join(transaction_date, price_list, 
           by = setNames('Date', nm)) %>%
            mutate(Price_purchase = Price) %>%
            select(-Price)
 }

If the dates join are on a range, we may use non-equi join after converting the 'Date' columns to Date class

library(data.table)
nm1 <- c('Date_purchase', 'Date_sale')
setDT(transaction_date)[, nm1 := 
       lapply(.SD, as.IDate, format = "%d/%m/%Y"), .SDcols = nm1] 
setDT(price_list)[, Date := as.IDate(Date, "%d/%m/%Y")]
transaction_date[price_list, Price_purchase := 
    Price, on = .(Date_purchase >= Date, Date_sale <= Date)]
user_37322710

It should look like this:

library(dplyr)
transaction_data %>% 
  left_join(price_list %>% rename(Price_purchase = Price), by = c('Date_purchase' = 'Date')) %>% 
  left_join(price_list %>% rename(Price_sale = Price), by = c('Date_sale' = 'Date'))
user_99933610

In Python, you can use a map approach:

price_list = price_list.set_index('Date').squeeze()
transaction_data.loc[:, ['Price_purchase','Price_sale']] = transaction_data.filter(like='Date').apply(lambda x: x.map(price_list)).values

or use merge like the other answers:

transaction_data = (
    transaction_data.drop(columns=['Price_purchase','Price_sale'])
    .merge(price_list.add_suffix('_purchase'), how='left')
    .merge(price_list.add_suffix('_sale'), how='left')
)

But it's just a contribution, because I don't think it's gonna be faster than R.

user_70768190
Related