CODEWITHSUNDEEP
rpivot-table
doubleD
doubleD

Reputation: 269

pivottable in R using Count

I have this data set:

created On | status   
--------------------- 
2021-10-21 | complete 
2021-10-21 | complete
2021-10-21 | partial complete
2021-10-21 | on going
2021-10-20 | partial complete
2021-10-20 | on going
2021-10-19 | complete

I am trying to create a pivot table like in excel which expected output like below:

created on     |   complete     |     partial complete     |     on going     |     total     |     percent (complete+partial)/total
-----------------------------------------------------------------------------------------------------------
2021-10-21     |   2            |     1                    |     1            |     4         |    50
2021-10-20     |   0            |     1                    |     1            |     2         |    50
2021-10-19     |   1            |     0                    |     0            |     1         |    100

When I tried the following:

daily_numbers1 %>%
  pivot_table(
    .rows =  ~ `created on`,
    .columns = ~ status,
    .values = ~ status
  )

I got a tibble with the following output:

created on     |   complete     |     partial complete     |     on going     |     total     |     percent (complete+partial)/total
-----------------------------------------------------------------------------------------------------------
2021-10-21     |   <chr [2]>    |     <chr[1]>             |     <chr [1]>    |     4         |    50
2021-10-20     |   <NULL>       |     <chr [1]>            |     <chr [1]>    |     2         |    50

looks like i showed characters instead of number counts. I am still figuring our how to add the total and percent column. If you could add that too.

Another method I tried:

  daily_numbers1 %>% 
    group_by(`Created On`) %>%
    summarize(status = count(status))

i got this instead:

status     |     freq
---------------------
complete   |     3
partial    |     2
on going   |     2

far from what I needed, grouped by date.

Upvotes: 3

Views: 2051

Answers (1)

akrun
akrun

Reputation: 887048

In base R, this is easier

out <- addmargins(table(daily_numbers1), 2)
cbind(out, percent = rowSums(out[, c('complete', 'partial complete')])/
           out[, 'Sum'])

-output

#            complete on going partial complete Sum percent
#2021-10-19        1        0                0   1    1.00
#2021-10-20        0        1                1   2    0.50
#2021-10-21        2        1                1   4    0.75

Or using janitor

library(janitor)
library(dplyr)
daily_numbers1 %>%
    tabyl(createdOn, status) %>%
    adorn_totals("col") %>%
    mutate(Perc = (complete + `partial complete`)/Total)
#  createdOn complete on going partial complete Total Perc
# 2021-10-19        1        0                0     1 1.00
# 2021-10-20        0        1                1     2 0.50
# 2021-10-21        2        1                1     4 0.75

Or can use pivot_wider with adorn_totals

library(tidyr)
daily_numbers1 %>%
   pivot_wider(names_from = status, values_from = status,
        values_fn = length, values_fill = 0) %>% 
   adorn_totals('col') %>% 
   mutate(Perc = (complete + `partial complete`)/Total)

data

daily_numbers1 <- structure(list(createdOn = c("2021-10-21", "2021-10-21", 
"2021-10-21", 
"2021-10-21", "2021-10-20", "2021-10-20", "2021-10-19"), status = c("complete", 
"complete", "partial complete", "on going", "partial complete", 
"on going", "complete")), class = "data.frame", row.names = c(NA, 
-7L))

Upvotes: 2

Related Questions