Order values inside a pivot table in openxlsx2

Question

I´m using openxlsx2 so that I can use the function wb_add_pivot_table() but the problem is that the order in which the rows appear, inside the pivot table, isn't in the order I want it to be displayed.

I´ve tried the following:

tbl_1 <- tibble(
  var_1 = c("FEBRERO", "SEPTIEMBRE","AGOSTO", "SEPTIEMBRE", "AGOSTO", "FEBRERO", "DICIEMBRE", "DICIEMBRE"),
  var_2 = seq_along(var_1)
) |> 
  mutate(
    var_1 = as.factor(var_1) |> 
      fct_relevel("FEBRERO", "AGOSTO", "SEPTIEMBRE", "DICIEMBRE")
  ) # I want the order of te rows, inside the pivot table, be like this
tbl_1
wb_1 <- wb_workbook() |> 
  wb_add_worksheet() |> 
  wb_add_data(x = tbl_1) 
df <- wb_data(wb_1)
wb_1 |> 
  wb_add_pivot_table(
    x = df,
    rows = "var_1",
    data = "var_2",
    fun = "sum"
  ) |> 
  wb_save("example_1.xlsx")

My naive approach, as you see, was to convert the class of the var_1 column to factor, but it didn't work. Thanks in advance.

Hi, this is currently not possible. You can create different variables and switch their orders or modify the order manually. (At some point you have to open the file ...). Feel free to open an issue with openxlsx2. — Jan Marvin
– Jan Marvin, Commented Sep 8, 2023 at 16:12
I see, I update the underlying data for that pivot table every day so i had to order manually the rows each day for my coworkers, that's why I wanted something more automated. But it's okay, Would be nice to have a vignette for pivot tables! ;) i really like the package btw! — Amarullazo
– Amarullazo, Commented Sep 10, 2023 at 15:51
Thanks! We'll get there, here are a few lose snippets and sorting should be simple to implement. Still as alternative: you could create a pivot table from some worksheet data and replace the data on the sheet and update the pivot table afterwards. I do this a lot with SAS — Jan Marvin
– Jan Marvin, Commented Sep 11, 2023 at 16:53
It's now possible with the development branch and will be part of the next release. — Jan Marvin
– Jan Marvin, Commented Sep 13, 2023 at 10:05

Amarullazo · Accepted Answer · 2023-11-04 06:21:58Z

1

I set up this question badly. The problem I really wanted to solve was this:

library(tidyverse); library(openxlsx2)

tbl_1 <- tibble(
  var_1 = seq(from = ymd("2023-01-01"),
              to = ymd("2024-03-31"),
              by = "month"),
  var_2 = seq_along(var_1)
) |> 
  uncount(2) |> 
  mutate(
    year = year(var_1),
    month = month(var_1, label = TRUE)
  )

With this I wanted the year and month columns be in the column field inside the pivot table. The problem with this is that the month column is not in the proper order, not Ene-Dic:

wb_1 <- wb_workbook() |>
  wb_add_worksheet() |>
  wb_add_data(x = tbl_1)
df <- wb_data(wb_1)
wb_1 |>
  wb_add_pivot_table(
    x = df,
    cols = c("year", "month"),
    data = "var_2",
    fun = "sum"
  )

This results in:

Now, according to the docs, the following should work:

wb_1 <- wb_workbook() |> 
  wb_add_worksheet() |> 
  wb_add_data(x = tbl_1) 
df <- wb_data(wb_1)
wb_1 |> 
  wb_add_pivot_table(
    x = df,
    cols = c("year", "month"),
    data = "var_2",
    fun = "sum",
    params = list(
      sort_item = list(month = 12:1)
    )
  )

And it does:

edited Nov 4, 2023 at 6:21

answered Nov 4, 2023 at 5:21

Amarullazo

1171 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jan Marvin Over a year ago

With release 1.2 it is also possible to sort on character strings. So month = c('Jan', 'Feb', ..., 'Dic')) is also possible and might guard against cases with unsorted factor levels.

Collectives™ on Stack Overflow

Order values inside a pivot table in openxlsx2

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related