Nested table with percentages and counts with time trend on the top

Hi, I'm sorry if yo think this task is basic but I have issues with nested tables which might be exported to excel.
I have this data frame:

source <- data.frame(
   stringsAsFactors = FALSE,
            Jobcard = c(5660863,6325219,5725499,
                        5901064,5894979,6103468,5980049,6024022,5879135,
                        6322768,5501653,6198477,6139052,6206653,6311497,
                        5982023,6221379,5526636,5972438,5972654,5985139,5540807,
                        6182714,6068033,6068050,5921431,6391584,6028502,
                        6326756,5631504,6028639,5817717,6300466,6078360,
                        6341502,5930502,6081264,5906791,5943518,5947278,
                        5980451,5972703,6045056,5723754,5816699,5895124,6291001,
                        5577294,6140149,6243126,6398292,5656335,6096748,
                        6226194,6293398,5500038,6137288,5882662,6025331,
                        5621709,6118401,5686104,6244837,5662066,5665621,
                        5650764,5679383,5653473),
  SourceCategoryCat = c("Web Booking","Web Booking",
                        "Call","Other","Web Booking","Web Booking",
                        "Web Booking","Call","Web Booking","Web Booking","Call",
                        "Call","Web Booking","Web Booking","Web Booking",
                        "Web Booking","Web Booking","Call","Call","Call","Call",
                        "Call","Call","Call","Call","Call","Call",
                        "Web Booking","Web Booking","Call","Call","Call","Call",
                        "Web Booking","Web Booking","Call","Other",
                        "Web Booking","Web Booking","Call","Call","Web Booking",
                        "Other","Call","Call","Call","Call","Call","Call",
                        "Web Booking","Web Booking","Web Booking","Web Booking",
                        "Web Booking","Web Booking","Call","Call","Call",
                        "Other","Web Booking","Web Booking","Call","Call",
                        "Call","Other","Call","Call","Web Booking"),
               Year = c(2021,2022,2021,2021,2021,
                        2022,2022,2022,2021,2022,2021,2022,2022,2022,
                        2022,2022,2022,2021,2022,2022,2022,2021,2022,
                        2022,2022,2021,2022,2022,2022,2021,2022,2021,2022,
                        2022,2022,2021,2022,2021,2021,2022,2022,2022,
                        2022,2021,2021,2021,2022,2021,2022,2022,2022,
                        2021,2022,2022,2022,2021,2022,2021,2022,2021,2022,
                        2021,2022,2021,2021,2021,2021,2021),
              Month = c(10,8,10,12,11,3,1,2,
                        12,8,8,5,4,5,7,1,6,9,1,1,1,8,5,3,3,12,8,
                        2,8,9,2,11,7,3,8,12,3,12,12,1,2,1,2,11,
                        11,12,7,8,4,7,8,9,4,6,7,8,5,11,2,9,4,
                        10,6,9,10,10,10,9),
                NPS = c(0,-100,-100,-100,100,100,
                        100,-100,100,100,0,100,100,-100,100,100,-100,
                        100,100,-100,0,100,-100,100,100,100,0,100,
                        100,-100,100,100,0,100,0,100,0,0,-100,100,
                        -100,100,-100,100,100,-100,100,0,100,0,-100,
                        100,100,100,100,0,-100,0,-100,0,100,0,0,
                        100,-100,-100,-100,100),
       ResponseDate = c("2021-10-02","2022-08-11",
                        "2021-10-29","2021-12-15","2021-11-24","2022-03-23",
                        "2022-01-25","2022-02-24","2021-12-01","2022-08-03",
                        "2021-08-06","2022-05-26","2022-04-21","2022-05-28",
                        "2022-07-16","2022-01-27","2022-06-24","2021-09-10",
                        "2022-01-25","2022-01-13","2022-01-21","2021-08-08",
                        "2022-05-20","2022-03-05","2022-03-07","2021-12-09",
                        "2022-08-26","2022-02-26","2022-08-04","2021-09-07",
                        "2022-02-16","2021-11-05","2022-07-11","2022-03-24",
                        "2022-08-17","2021-12-18","2022-03-25","2021-12-05",
                        "2021-12-30","2022-01-01","2022-02-02","2022-01-12",
                        "2022-02-25","2021-11-09","2021-11-30","2021-12-03",
                        "2022-07-06","2021-08-26","2022-04-25","2022-07-06",
                        "2022-08-31","2021-09-30","2022-04-06","2022-06-13",
                        "2022-07-05","2021-08-24","2022-05-05","2021-11-24",
                        "2022-02-15","2021-09-10","2022-04-04","2021-10-13",
                        "2022-06-21","2021-09-24","2021-10-18","2021-10-30",
                        "2021-10-30","2021-09-27")
)

Now, I need two tables (or one) which could be easily exported to excel which column percentages and counts (ideally in one table), where first variable on the left is SourceCategoryCat and the second is NPS. At the top, I need a Year and underneath a Month (perhaps there is a clever way of using ResponseDate variable to show that rather then converting a date into Year and Month.
I can do it only this way:

library(dplyr)
my.table <- source %>%
  group_by(Year, Month, SourceCategoryCat, NPS) %>%
  summarise(cnt = n()) %>%
  mutate(freq = round(cnt / sum(cnt), 3))
my.table

but this is not very handy to create a trend table with a date at the top.
I can easily do it in SPSS and perhaps in excel pivot tables. Can I do it in R?

Dear Slavek

is this what you are looking for ?

Kind Regards

No, this is what I already have.
Year and month (or ideally a date) should be on top of the table (header of the table)

OUTPUT.pdf (172.4 KB)
Tables should look like this SPSS output attached.

You are confusing a "data frame" which is meant to contain tabular data, with a "table" which is a visual representation of the data.

There are many R packages for creating tables, for example the output you are showing on the pdf file can be produced with the gt package.

This topic was automatically closed 42 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.