CODEWITHSUNDEEP
r
Anshul S
Anshul S

Reputation: 281

Distribute value based on another dataframe

I would like to distribute the Profit based on another dataframe

DF1:

Item    From    Price    Discount   
 A     Delhi    100        .10      
 A     Mumbai   200        .10    
 A     Pune     150        .10     
 A     Nagpur   200        .10

DF2:

Item      From     To
 A        Delhi    Mumbai
 A        Mumbai   Pune
 A        Mumbai   Nagpur

Profit is calculated as Price*Discount

Here, In DF1 we are calculating the profit and based on the relationship in DF2 we need to add it in the price of connected city. We calculated profit for Delhi and it is only connected to Mumbai so transfer the profit to the price of Mumbai.

But when we calculated the profit for Mumbai it is connected to Pune and Nagpur so the profit need to be divided and added to the price of Pune & Nagpur

Can someone help me to write a for loop for this.

Output:

Item    From    Price    Discount    Profit
 A     Delhi    100        .10         10
 A     Mumbai   200+10     .10         21
 A     Pune     150+10.5   .10         16
 A     Nagpur   200+10.5   .10         21

Upvotes: 0

Views: 57

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 389055

One way using for loop. Thanks to @Sotos and OP for detailed explanation.

#Initialize Profit column to 0
df1$Profit <- 0

for (i in seq_len(nrow(df1))) {
   #Check cities which are present in df2
   cities <- df2$To[df1$Item[i] == df2$Item & df1$From[i] == df2$From]
   inds <- df1$From %in% cities
   #Update the Price for matched cities
   if (any(inds))
     df1$Price[inds] <- df1$Price[inds] + 
                       (df1$Price[i] * df1$Discount[i]/length(cities))
   #Calculate Profit
   df1$Profit[i] <- df1$Price[i] * df1$Discount[i]
}

df1
#  Item   From Price Discount Profit
#1    A  Delhi 100.0      0.1  10.00
#2    A Mumbai 210.0      0.1  21.00
#3    A   Pune 160.5      0.1  16.05
#4    A Nagpur 210.5      0.1  21.05

Upvotes: 1

Related Questions