Use mutate to create rows instead of columns

user_1024586 0
Viewed 111

Some data:

webdata %>% dput
structure(list(`2021-01-01` = c(9769.77145143346, 240.374021734625
), `2021-01-02` = c(9747.83506410782, 205.305063093191), `2021-01-03` = c(10244.2824708895, 
253.285005879351), `2021-01-04` = c(9903.98090317497, 250.068739190171
), `2021-01-05` = c(10126.8521068156, 152.172995948704), Total = c(sessions = 49792.7219964214, 
conversions = 1101.20582584604)), class = "data.frame", row.names = c("sessions", 
"conversions"))

Looks like this:

webdata
            2021-01-01 2021-01-02 2021-01-03 2021-01-04 2021-01-05     Total
sessions      9769.771  9747.8351  10244.282  9903.9809  10126.852 49792.722
conversions    240.374   205.3051    253.285   250.0687    152.173  1101.206

I'd like to add a row, ConversionRate which is conversions / sessions. This is like a transposed dplyr operation since I need a row mutation.

How can I do this so that there's a new row for ConversionRate that is conversions / sessions?

dplyr r
4 Answers

Updated In case you would like to use rownames instead of indices to carry out your operation, I think this solution may be helpful for your original data set:

library(dplyr)
library(tibble)

df %>%
  summarise(across(everything(), ~ (.x)[which(rownames(df) == "sessions")]/
                     .x[which(rownames(df) == "conversions")]),
            rowname = 'ConversionRate') %>%
  bind_rows(df %>% 
              rownames_to_column()) %>%
  column_to_rownames("rowname")

               2021-01-01 2021-01-02  2021-01-03 2021-01-04  2021-01-05       Total
ConversionRate   40.64404   47.47976    40.44567   39.60503    66.54829    45.21654
sessions       9769.77145 9747.83506 10244.28247 9903.98090 10126.85211 49792.72200
conversions     240.37402  205.30506   253.28501  250.06874   152.17300  1101.20583
user_143145200

May be this also do

library(tidyverse)

library(dplyr, warn.conflicts = F)

webdata %>%
  bind_rows(webdata %>% summarise(across(everything(), ~last(.)/first(.))) %>% `row.names<-`('Rate'))

#>               2021-01-01   2021-01-02   2021-01-03   2021-01-04   2021-01-05
#> sessions    9.769771e+03 9.747835e+03 1.024428e+04 9.903981e+03 1.012685e+04
#> conversions 2.403740e+02 2.053051e+02 2.532850e+02 2.500687e+02 1.521730e+02
#> Rate        2.460385e-02 2.106161e-02 2.472452e-02 2.524932e-02 1.502668e-02
#>                    Total
#> sessions    4.979272e+04
#> conversions 1.101206e+03
#> Rate        2.211580e-02

As mentioned in comments, if you want to use rownames

webdata %>%
  bind_rows(webdata %>% summarise(across(everything(), ~first(.x[rownames(webdata) == 'conversions'])/
                                           first(.x[rownames(webdata) == 'sessions']))) %>% `row.names<-`('Rate'))

              2021-01-01   2021-01-02   2021-01-03   2021-01-04   2021-01-05        Total
sessions    9.769771e+03 9.747835e+03 1.024428e+04 9.903981e+03 1.012685e+04 4.979272e+04
conversions 2.403740e+02 2.053051e+02 2.532850e+02 2.500687e+02 1.521730e+02 1.101206e+03
Rate        2.460385e-02 2.106161e-02 2.472452e-02 2.524932e-02 1.502668e-02 2.211580e-02

Though it is safer to use aggregating functions while summarising, still, the below code will also work

webdata %>%
  bind_rows(webdata %>% summarise(across(everything(), ~.x[rownames(webdata) == 'conversions']/
                                           .x[rownames(webdata) == 'sessions'])) %>% `row.names<-`('Rate'))
user_28848590

We may also do this in summarise itself`

library(dplyr)
library(tibble)
webdata %>%
    rownames_to_column('rn') %>% 
    summarise(rn = c(rn, 'Rate'), across(-rn, ~ c(., first(.)/last(.)))) %>% 
    column_to_rownames('rn')

-output

         2021-01-01 2021-01-02  2021-01-03 2021-01-04  2021-01-05       Total
sessions    9769.77145 9747.83506 10244.28247 9903.98090 10126.85211 49792.72200
conversions  240.37402  205.30506   253.28501  250.06874   152.17300  1101.20583
Rate          40.64404   47.47976    40.44567   39.60503    66.54829    45.21654
user_37322710

Does this work:

webdatanew <- rbind(webdata,sapply(webdata, function(x) x[1,drop = T]/x[2, drop = T]))
rownames(webdatanew)
[1] "sessions"    "conversions" "3"          
rownames(webdatanew)[3] <- 'ConversionRate'
webdatanew
               2021-01-01 2021-01-02  2021-01-03 2021-01-04  2021-01-05       Total
sessions       9769.77145 9747.83506 10244.28247 9903.98090 10126.85211 49792.72200
conversions     240.37402  205.30506   253.28501  250.06874   152.17300  1101.20583
ConversionRate   40.64404   47.47976    40.44567   39.60503    66.54829    45.21654
user_107227520
Related