subtracting values in dataframe with condition(s) from another dataframe

user_14352940 0
Viewed 60

I have two dataframes that have sales data from fruits store.

1st Data frame has sales data from 'Store A', and the 2nd data frame has that data gathered from 'Store A + Store B'

StoreA = data.frame(
  Fruits = c('Apple', 'Banana', 'Blueberry'),
  Customer = c('John', 'Peter', 'Jenny'),
  Quantity = c(2, 3, 1)
)
Total = data.frame(
  Fruits = c('Blueberry', 'Apple', 'Banana', 'Blueberry', 'Pineapple'),
  Customer = c('Jenny' , 'John', 'Peter', 'John', 'Peter'),
  Quantity = c(4, 7, 3, 5, 3)
)

StoreA
Total

I wish to subtract the sales data of 'StoreA' from 'Total' to get sales data for 'StoreB'.

At the end, I wish to have something like

enter image description here

dataframe r
2 Answers

You could do a full join first, then rename the columns, fill the missing values resulting from the join and then compute the difference.

library(tidyverse)
StoreA = data.frame(Fruits = c('Apple', 'Banana', 'Blueberry'), Customer = c('John', 'Peter', 'Jenny'), Quantity = c(2,3,1))
Total = data.frame(Fruits = c('Blueberry','Apple', 'Banana', 'Blueberry', 'Pineapple'), Customer = c('Jenny' ,'John', 'Peter', 'John', 'Peter'), Quantity = c(4,7,3,5,3))

full_join(StoreA %>% 
            rename(Qty_A = Quantity), 
          Total %>% 
            rename(Qty_Total = Quantity), by = c("Fruits", "Customer")) %>% 
  # fill NAs with zero
  replace_na(list(Qty_A = 0)) %>% 
  # compute the difference
  mutate(Qty_B = Qty_Total - Qty_A)


#>      Fruits Customer Qty_A Qty_Total Qty_B
#> 1     Apple     John     2         7     5
#> 2    Banana    Peter     3         3     0
#> 3 Blueberry    Jenny     1         4     3
#> 4 Blueberry     John     0         5     5
#> 5 Pineapple    Peter     0         3     3

Created on 2020-09-28 by the reprex package (v0.3.0)

user_143569490

Great Question! There is a simple and graceful way of achieving exactly what you want.

The title to this question is: "subtracting values in a data frame with conditons() from another data frame"

This subtraction can be accomplished just like the title says. But there is a better way than using subtraction. Turning a subtraction problem into an addition problem is often the easiest way of solving a problem.

To make this into an addition problem, just convert one of the data frames (StoreA$Quantity) into negative values. Only convert the Quantity variable into negative values. And then rename the other data frame (Total) into StoreB.

Once that is done, it's easy to finish. Just use the join function with the two data frames (StoreA & StoreB). Doing that brings the negative and positive values together and the data is more understandable. When there are the same things with positive and negative values, then it's obvious these things need to be combined.

To combine those similar items, use the group_by() function and pipe it into a summarize() function. Doing the coding this way makes the code easy to read and easy to understand. The code can almost be read like a book.

Create data frames: 
StoreA = data.frame(Fruits = c('Apple', 'Banana', 'Blueberry'), Customer = c('John', 'Peter', 'Jenny'), Quantity = c(2,3,1))
StoreB = data.frame(Fruits = c('Blueberry','Apple', 'Banana', 'Blueberry', 'Pineapple'), Customer = c('Jenny' ,'John', 'Peter', 'John', 'Peter'), Quantity = c(4,7,3,5,3))
Convert StoreA$Quantity to negative values: 
StoreA_ <- StoreA
StoreA_Quanity <- StoreA$Quantity * -1
StoreA_
StoreA_ now looks like this: 
Fruits    Customer  Quantity
<fct>      <fct>    <dbl>
Apple      John     -2
Banana     Peter    -3
Blueberry  Jenny    -1

Now combine StoreA and StoreB. Use the full_join() function to join the two stores:

Total <- full_join(StoreA_, StoreB, disparse = 0)
Total

The last thing is accomplished using the group_by function. This will combine the positive and negative values together.

Total %>% group_by(Fruits, Customer) %>% summarize(s = sum(Quantity))

It's Done! The output is shown at this link:

user_110637290
Related