Creating another row based on differences of other rows

  Kiến thức lập trình

I have been stuck with this problem for a week or so now, and need some help.

My data looks like this:

Apples Bananas Cherries Date Type
10 5 20 Date 1 Store
20 10 30 Date 1 Car
30 50 15 Date 1 Home
40 30 20 Date 2 Store
45 20 10 Date 2 Car
40 10 60 Date 2 Home

I want to make another category under “Type” called, let’s say, “Farm” which is the difference between Store, Car, and Home (or Farm = Store – Car – Home), which is calculated for every unique Date.

The result I would like is:

Apples Bananas Cherries Date Type
10 5 20 Date 1 Store
20 10 30 Date 1 Car
30 50 15 Date 1 Home
40 30 20 Date 2 Store
45 20 10 Date 2 Car
40 10 60 Date 2 Home
-40 -55 -25 Date 1 Farm
-45 -20 -70 Date 2 Farm

(The numbers are made up and are negative in this case, but not in my real dataset. Additionally, my dataset has more “fruit” columns)

I haven’t tried much on R yet, because I don’t know where to start. I exported the data to do the calculations manually. However, ideally, I would like for R to do the calculations before exporting to reduce a step.

Any help is appreciated. Thank you.

New contributor

INeedHelp is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

1

df <- data.frame(
  stringsAsFactors = FALSE,
            Apples = c(10L, 20L, 30L, 40L, 45L, 40L),
           Bananas = c(5L, 10L, 50L, 30L, 20L, 10L),
          Cherries = c(20L, 30L, 15L, 20L, 10L, 60L),
              Date = c("Date 1","Date 1","Date 1",
                       "Date 2","Date 2","Date 2"),
              Type = c("Store", "Car", "Home", "Store", "Car", "Home")
)

library(tidyverse)
bind_rows(df,
          summarize(df, across(Apples:Cherries,  ~.x[Type == "Store"] - 
                               .x[Type == "Car"] - .x[Type == "Home"]), 
            .by = Date) %>%
          mutate(Type = "Farm"))

Result

  Apples Bananas Cherries   Date  Type
1     10       5       20 Date 1 Store
2     20      10       30 Date 1   Car
3     30      50       15 Date 1  Home
4     40      30       20 Date 2 Store
5     45      20       10 Date 2   Car
6     40      10       60 Date 2  Home
7    -40     -55      -25 Date 1  Farm
8    -45       0      -50 Date 2  Farm

LEAVE A COMMENT