Help with Row-wise Calculations

[ksingh19]

Hello,

Need help with row-wise calculation. In this example, I am looking for another row that shows "Total - Type A" from variable Type and for both products A & B. I am also looking for another row that shows sum of Type A & Type B (Type A + B).

Any help will be appreciated. Thanks a bunch!

Example of data below:

ttl <- data.frame(
  stringsAsFactors = FALSE,
       check.names = FALSE,
                   Product = c("Product A",
                               "Product A","Product A","Product A","Product B",
                               "Product B","Product B","Product B"),
                      Type = c("Type A",
                               "Type B","Type C","Total","Type A","Type B",
                               "Type C","Total"),
                    `2012` = c(2709.918,
                               2008.885,5064.211,9783.014,1227.195,971.391,
                               3599.942,5798.528),
                    `2013` = c(3225.571,
                               2067.898,5150.199,10443.668,1297.668,792.976,
                               4113.281,6203.925),
                    `2014` = c(3487.596,
                               2148.958,5115.467,10752.021,1387.781,771.827,
                               4245.271,6404.879),
                    `2015` = c(3779.972,
                               2303.884,5245.154,11329.01,1500.451,692.246,
                               4370.139,6562.836)
        )

[ksingh19]

Is it possible to perform calculations on row-wise with tidyverse?
Thanks!

[martin.R]

Group input by rows — rowwise

[nirgrahamuk]

I dont see all that much rowwise about your ask.
it seems to me a matter of

1. identifying what elements of your data are to contribute
2. summarise that
3. label the result
4. add it into the table

I did your first ask, the second is a similar variation.

library(tidyver)
ttl <- data.frame(
  stringsAsFactors = FALSE,
  check.names = FALSE,
  Product = c("Product A",
              "Product A","Product A","Product A","Product B",
              "Product B","Product B","Product B"),
  Type = c("Type A",
           "Type B","Type C","Total","Type A","Type B",
           "Type C","Total"),
  `2012` = c(2709.918,
             2008.885,5064.211,9783.014,1227.195,971.391,
             3599.942,5798.528),
  `2013` = c(3225.571,
             2067.898,5150.199,10443.668,1297.668,792.976,
             4113.281,6203.925),
  `2014` = c(3487.596,
             2148.958,5115.467,10752.021,1387.781,771.827,
             4245.271,6404.879),
  `2015` = c(3779.972,
             2303.884,5245.154,11329.01,1500.451,692.246,
             4370.139,6562.836)
)

(row_type_a_bothprods <- filter(
  ttl,
  Type == "Type A"
) %>%
  summarise(across(
    where(is.numeric),
    sum
  )) %>%
  mutate(
    Product = "Both", Type = "Type A"
  ))

bind_rows(ttl,
          row_type_a_bothprods)

[ksingh19]

Thanks @nirgrahamuk !

I am not looking to add to same types. Instead I am looking to subtract or add two different types. In this example, looking to achieve Total - Type A values or Type B - Type A. Same goes with the sum also.

Really looking to see how we can subtract values from one row values to another, like we do in excel.

Thanks!

[ksingh19]

Thanks @martin.R !
Hmn....looks like rowwise() might not be useful for my example above.

Thanks for this resource. I am sure, I will get to use it often.

[nirgrahamuk]

well ok, so here is an example of such a subtraction in this case for Product A, taking type A away from Total.

step1 <- ttl %>% filter(
  Product=='Product A'
)

r1 <- filter(step1,
       Type=="Total")

r2 <- filter(step1,
             Type=="Type A")

#we want to subtract r2 so 

r2x <- mutate(r2,
              across(where(is.numeric),
                     ~.*-1))

bind_rows(r1,r2x) %>% summarise(across(where(is.numeric),
                                       sum))

[ksingh19]

Thanks again @nirgrahamuk!

Yes, this is what I am looking for. Is it possible to perform this simultaneously on both Products A & B without repeating code for each Product. I do have several products in main dataset and that's why it will be better to perform it on all Products at the same time.

Thanks again!

[nirgrahamuk]

I hope you can see how the below is something like 90% the same as I previously posted, its just wrapped with a map function to do the operation for each of the inputs, which are the products to process in the same way.

library(tidyver)
ttl <- data.frame(
  stringsAsFactors = FALSE,
  check.names = FALSE,
  Product = c("Product A",
              "Product A","Product A","Product A","Product B",
              "Product B","Product B","Product B"),
  Type = c("Type A",
           "Type B","Type C","Total","Type A","Type B",
           "Type C","Total"),
  `2012` = c(2709.918,
             2008.885,5064.211,9783.014,1227.195,971.391,
             3599.942,5798.528),
  `2013` = c(3225.571,
             2067.898,5150.199,10443.668,1297.668,792.976,
             4113.281,6203.925),
  `2014` = c(3487.596,
             2148.958,5115.467,10752.021,1387.781,771.827,
             4245.271,6404.879),
  `2015` = c(3779.972,
             2303.884,5245.154,11329.01,1500.451,692.246,
             4370.139,6562.836)
)

prodsvec <- c('Product A',
              'Product B')
purrr::map( 
  prodsvec,
  ~{
    
    step1 <- ttl %>% filter(
      Product== .x
    )
    
    r1 <- filter(step1,
                 Type=="Total")
    
    r2 <- filter(step1,
                 Type=="Type A")
    
    #we want to subtract r2 so 
    
    r2x <- mutate(r2,
                  across(where(is.numeric),
                         ~.*-1))
    
    bind_rows(r1,r2x) %>% summarise(across(where(is.numeric),
                                           sum))
  }) %>% set_names(prodsvec)