Pivot_wider with dates or more than two variables

tidyverse
1

Hi,
I have this sample df where I need counts in a grid view.

data.source <- data.frame(
  stringsAsFactors = FALSE,
        DealerName = c("aaa","aaa","bbb","bbb",
                       "bbb","ccc","ccc","ccc","aaa","aaa","ccc"),
  LastWorkshopDate = c("2022-05-27","2020-07-29",
                       "2021-05-26","2019-07-29","2019-08-27","2021-02-26",
                       "2019-05-02","2022-07-29","2022-07-28","2019-08-28",
                       "2018-11-22"),
              Year = c(2022,2020,2021,2019,2019,
                       2021,2019,2022,2022,2019,2018),
             Month = c(5, 7, 5, 7, 8, 2, 5, 7, 7, 8, 11),
               Qtr = c(2, 3, 2, 3, 3, 1, 2, 3, 3, 3, 4)
)

data.source

library("dplyr")
annual.visits <- data.source %>%
  select (Year, DealerName)  %>% 
  add_count(Year, DealerName, name = "Annual.Visits") %>%
  mutate(Annual.Visits = Annual.Visits)

library(tidyverse)
annual.visits.grid <- annual.visits %>%
  group_by(DealerName, Year) %>% 
  summarise(Annual.Visits = unique(Annual.Visits)) %>% 
  arrange(DealerName) %>% 
  pivot_wider(id_cols = Year,
              names_from = DealerName,
              values_from = Annual.Visits) %>% 
  rowwise() %>% 
  ungroup() 

annual.visits.grid

Unfortunately, I don't know I could do it if I wanted to show not only Years but also Months.
I don't know how to add one more layer or use Months from LastWorkshopDate.

The only way I can do is:


data.source$Year <- as.character(data.source$Year)
data.source$Month <- as.character(data.source$Month)

str(data.source)

data.source$YearMonth <- paste(data.source$Year, data.source$Month, sep =" ")
str(data.source)

library("dplyr")
annual.visits <- data.source %>%
  select (YearMonth, DealerName)  %>% 
  add_count(YearMonth, DealerName, name = "Annual.Visits") %>%
  mutate(Annual.Visits = Annual.Visits)

library(tidyverse)
annual.visits.grid <- annual.visits %>%
  group_by(DealerName, YearMonth) %>% 
  summarise(Annual.Visits = unique(Annual.Visits)) %>% 
  arrange(DealerName) %>% 
  pivot_wider(id_cols = YearMonth,
              names_from = DealerName,
              values_from = Annual.Visits) %>% 
  rowwise() %>% 
  ungroup() 

annual.visits.grid

But I know it is only a silly, temporarily way. Also, dates are not in order.

Is there any easy way of doing that?

2

Its about as simple as just adding in Month at the same places that you were putting in year.

annual.visits <- data.source %>%
  select (Year,Month, DealerName)  %>% 
  add_count(Year,Month, DealerName, name = "Annual.Visits") %>%
  mutate(Annual.Visits = Annual.Visits)


annual.visits.grid<- annual.visits %>%
  group_by(DealerName, Year,Month) %>% 
  summarise(Annual.Visits = unique(Annual.Visits)) %>% 
  arrange(DealerName) %>% 
  pivot_wider(id_cols = c(Year,Month),
              names_from = DealerName,
              values_from = Annual.Visits) %>% 
  rowwise() %>% 
  ungroup()  |> arrange(Year,Month)
3

Excellent! Thank you :slight_smile:
I have Year and Month which can always be extrapolated from a date (LastWorkshopDate in this case) but perhaps some R users would like to use the data variable straight away. Is it possible to convert it to MM-YYYY format (so skipping days) and use it instead of two variables (Year, Month)? All conversions I can find are about date format but they hold days, months and years :thinking:

4

And finally, is it possible to add "Total" column in the end?

5

use standard dplyr approach to make a summary / total data.frame summarise()
put one data.frame on top of another with dplyr::bind_rows()

6

Oops, sorry for not being clear, adding "Total" in rows is simple but is it possible to add a "Total of columns"?

7

Either of these :


annual.visits.grid |> rowwise() |> 
  mutate(Total = 
           sum(c_across(where(is.integer)),
               na.rm=TRUE))

annual.visits.grid |> rowwise() |> 
  mutate(Total = 
           sum(c_across(aaa:ccc),
               na.rm=TRUE))
1 Like
8

I know I am asking about much but now I am trying to change sum to count NAs in each row and I'm struggling :thinking:

9

This topic was automatically closed 7 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.