Second axis with `asis` mode #84

JanMarvin · 2022-12-07T23:16:10Z

Because I have a couple of cases where I need a secondary y axis. This currently only works with asis data. ~~And I have tested only this single case down below.~~ This fixes #9

Works quite well. ~~I have added a secondary argument. This way it is possible to treat the secondary plot as if it is a normal plot.~~ This code should only impact cases where a second plot is added. I'm not sure how to implement this with the basic asis = FALSE mschart output. It might need some glue code to patch everything together, but for now I do not care about this case.

JanMarvin

minor cleanups remain

R/ms_chart.R

R/to_pml.R

R/ms_chart.R

R/axis_codes.R

R/ms_chart.R

JanMarvin · 2023-04-25T13:00:40Z

Hi @davidgohel , could you review this sometime in the future, especially if you like the general idea? Unfortunately I only got it to work with asis = TRUE and have no real clue how to integrate it with the standard asis = FALSE approach. Maybe you have an idea? I have attempted to create a single data frame [edit[ and use this [/edit] via match.call() and create a single data frame from the data_series, but of course the column names do not match. Another approach would be to write every data_series to its own worksheet, but that would probably be some additional changes here and there. (And to be honest, I do not require asis = FALSE and therefore I'm a bit lazy).

My approach for this pull request was to integrate everything without touching [edit] anything but [/edit] the x$secondary objects. Therefore, the default behavior should be the same. I needed some additional arguments to identify the second y-axis, but we could wrap some of them in ... if you prefer not to announce arguments that are not usable with the default option.

Please note:

I hacked in a solution to disable the major and minor grids for the second y-axis because I found them visually distracting, but this should probably be handled differently.
In the following example, the graphs for the first and second y-axis overlap.

library(officer)
library(mschart)

t1 <- AirPassengers
t2 <- do.call(cbind, split(t1, cycle(t1)))
dimnames(t2) <- dimnames(.preformat.ts(t1))
t2 <- as.data.frame(t2)
t2$date <- as.Date(paste0(row.names(t2), "-01-01"))
nms <- c("date", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", 
         "Oct", "Nov", "Dec")
AirPass <- t2[nms]


# bar and line on first and line on second axis
bc <- AirPass |>
  ms_barchart(x = "date", y = c("Jan", "Feb"), asis = TRUE) |>
  chart_ax_x(num_fmt = "[$-fr-FR]mmm yyyy") |>
  chart_labels(title = "Title", xlab = "x axis",
               ylab = "y axis")

sec_x <- AirPass |>
  ms_linechart(x = "date", y = c("Apr", "Jul", "Oct"), asis = TRUE) |>
  chart_ax_y(second_axis = TRUE)

sec_y <- AirPass |>
  ms_linechart(x = "date", y = c("May", "Jun", "Sep"), asis = TRUE)  |>
  chart_labels(ylab = "y axis 2") |>
  chart_ax_y(second_axis = TRUE)

sec_y2 <- AirPass |>
  ms_barchart(x = "date", y = c("Mar", "Nov", "Dec"), asis = TRUE) |>
  chart_ax_y(second_axis = TRUE)

cc <- ms_combochart(bc, sec_x, sec_y, sec_y2)

### create powerpoint
doc <- read_pptx()
doc <- add_slide(doc, layout = "Title and Content", master = "Office Theme")
doc <- ph_with(doc, value = cc, location = ph_location_fullsize())
print(doc, target = "/tmp/example.pptx")

[Edit] Made minor correction above and added the powerpoint example image output.
[Edit2] Updated example code

R/ms_chart.R

JanMarvin · 2023-05-09T18:04:16Z

Alright the next revision:

only inside ms_combochart() and without add and secondary_y
the user has to define second_axis if required
added support to disable major grids
Secondary x axis should be possible too

I'm waiting for a review. We could add more checks or implement combo charts for the non asis case, but the current implementation is what I need and it already works well.

t1 <- AirPassengers
t2 <- do.call(cbind, split(t1, cycle(t1)))
dimnames(t2) <- dimnames(.preformat.ts(t1))
t2 <- as.data.frame(t2)
t2$date <- as.Date(paste0(row.names(t2), "-01-01"))
nms <- c("date", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", 
         "Oct", "Nov", "Dec")
AirPass <- t2[nms]


library(openxlsx2)
library(mschart)

### barchart with line

# bar and line on first axis
wb <- wb_workbook()$add_worksheet()$add_data(x = AirPass)
dat <- wb_data(wb, dims = "A1:M13")

bc <- dat %>%
  ms_barchart(x = "date", y = c("Jan", "Feb")) %>%
  chart_ax_x(num_fmt = "[$-fr-FR]mmm yyyy") %>%
  chart_labels(title = "Title", xlab = "x axis",
               ylab = "y axis")

sec_x <- dat %>%
  ms_linechart(x = "date", y = c("Apr", "Jul", "Oct"))

cc <- ms_combochart(bc, sec_x)

wb$add_mschart(dims = "B20:L35", graph = cc)

# bar on first line on second axis
bc <- dat %>%
  ms_barchart(x = "date", y = c("Jan", "Feb")) %>%
  chart_ax_x(num_fmt = "[$-fr-FR]mmm yyyy") %>%
  chart_labels(title = "Title", xlab = "x axis",
               ylab = "y axis")

sec_y <- dat %>%
  ms_linechart(x = "date", y = c("May", "Jun", "Sep"))  %>%
  chart_ax_y(second_axis = TRUE) %>% 
  chart_labels(ylab = "y axis 2") %>% 
  chart_theme(grid_major_line_x = FALSE, grid_major_line_y = FALSE)

cc <- ms_combochart(bc, sec_y)

wb$add_mschart(dims = "B3:L18", graph = cc)


# bar and line on first and line on second axis
bc <- dat %>%
  ms_barchart(x = "date", y = c("Jan", "Feb")) %>%
  chart_ax_x(num_fmt = "[$-fr-FR]mmm yyyy") %>%
  chart_labels(title = "Title", xlab = "x axis",
               ylab = "y axis")

sec_y2 <- dat %>%
  ms_barchart(x = "date", y = c("Mar", "Nov", "Dec")) %>% 
  chart_ax_y(second_axis = TRUE)

cc <- ms_combochart(bc, sec_x, sec_y, sec_y2)

wb$add_mschart(dims = "B40:L55", graph = cc)

### linechart with bar

wb$add_worksheet()

lc <- dat %>% 
  ms_linechart(x = "date", y = c("Apr", "Jul", "Oct")) %>%
  chart_ax_x(num_fmt = "[$-fr-FR]mmm yyyy") %>% 
  chart_labels(title = "Title", xlab = "x axis",
               ylab = "y axis")
sec <- dat %>% 
  ms_barchart(x = "date", y = c("Jan", "Feb")) %>%
  chart_ax_y(second_axis = TRUE) %>% 
  chart_labels(ylab = "y axis 2")

cc <- ms_combochart(lc, sec)

wb$add_mschart(dims = "B3:L18", graph = cc)

### scatterplot

wb$add_worksheet()

sc <- dat %>% 
  ms_scatterchart(x = "Apr", y = c("Apr", "Jul", "Oct")) %>%
  chart_labels(title = "Title", xlab = "x axis",
               ylab = "y axis")
sec <- dat %>% 
  ms_barchart(x = "date", y = c("Jan", "Feb")) %>% 
  chart_ax_y(second_axis = TRUE) %>% 
  chart_labels(ylab = "y axis 2")

sec_y <- dat %>%
  ms_linechart(x = "date", y = c("May", "Jun", "Sep"))  %>%
  chart_ax_y(second_axis = TRUE) %>% 
  chart_labels(ylab = "y axis 2")

cc <- ms_combochart(sc, sec, sec_y)

wb$add_mschart(dims = "B3:L18", graph = cc)


### areachart with bar

wb$add_worksheet()

ac <- dat %>% 
  ms_areachart(x = "date", y = c("Apr", "Jul", "Oct")) %>%
  chart_ax_x(num_fmt = "[$-fr-FR]mmm yyyy") %>% 
  chart_labels(title = "Title", xlab = "x axis",
               ylab = "y axis")
sec <- dat %>% 
  ms_barchart(x = "date", y = c("Jan", "Feb"))  %>%
  chart_ax_y(second_axis = TRUE) %>% 
  chart_labels(ylab = "y axis 2")

cc <- ms_combochart(ac, sec)

wb$add_mschart(dims = "B3:L18", graph = cc)

unlink("/tmp/combo_charts.xlsx")
wb$save("/tmp/combo_charts.xlsx")

JanMarvin · 2023-05-10T09:04:57Z

### second x axis

wb$add_worksheet()

dat1 <- wb_data(wb, sheet = 1, dims = "A1:M6")
dat2 <- wb_data(wb, sheet = 1, dims = "A1:M1;A7:M13")

ac <- dat1 %>% 
  ms_areachart(x = "date", y = c("Apr", "Jul", "Oct")) %>%
  chart_ax_x(num_fmt = "[$-fr-FR]mmm yyyy") %>% 
  chart_labels(title = "Title", xlab = "x axis",
               ylab = "y axis")

sec <- dat2 %>% 
  ms_barchart(x = "date", y = c("Jan", "Feb"))  %>%
  chart_ax_x(num_fmt = "[$-fr-FR]mmm yyyy", second_axis = TRUE) %>% 
  chart_labels(xlab = "x axis 2")

cc <- ms_combochart(ac, sec)

wb$add_mschart(dims = "B3:L18", graph = cc)

JanMarvin · 2023-05-13T15:03:27Z

Pushed examples and documentation. There might still be some cornerstones e.g. secondary x and y axis at the same time does not work as expected, but likely nobody wants a second x axis anyways.

library(officer)
library(mschart)

t1 <- AirPassengers
t2 <- do.call(cbind, split(t1, cycle(t1)))
dimnames(t2) <- dimnames(.preformat.ts(t1))
t2 <- as.data.frame(t2)
t2$date <- as.Date(paste0(row.names(t2), "-01-01"))
nms <- c("date", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", 
         "Oct", "Nov", "Dec")
AirPass <- t2[nms]


# bar and line on first and line on second axis
fst_x <- AirPass |>
  ms_barchart(x = "date", y = c("Jan"), asis = TRUE) |>
  chart_ax_x(num_fmt = "[$-fr-FR]mmm yyyy") |>
  chart_labels(title = "Title", xlab = "x axis",
               ylab = "y axis")

fst_x2 <- AirPass |>
  ms_linechart(x = "date", y = c("Jan"), asis = TRUE)

sec_x <- AirPass |>
  ms_barchart(x = "date", y = c("Feb"), asis = TRUE) |>
  chart_ax_x(num_fmt = "[$-fr-FR]mmm yyyy", second_axis = TRUE) |>
  chart_labels(xlab = "x axis 2")

sec_x2 <- AirPass |>
  ms_linechart(x = "date", y = c("Feb"), asis = TRUE) |>
  chart_ax_x(second_axis = TRUE)

sec_y <- AirPass |>
  ms_linechart(x = "date", y = c("Mar"), asis = TRUE)  |>
  chart_labels(ylab = "y axis 2") |>
  chart_ax_y(second_axis = TRUE)

sec_y2 <- AirPass |>
  ms_barchart(x = "date", y = c("Mar"), asis = TRUE) |>
  chart_ax_y(second_axis = TRUE)

# cc <- ms_combochart(fst_x, sec_x, sec_y)
# cc <- ms_combochart(fst_x, fst_x2, sec_x, sec_x2)
cc <- ms_combochart(fst_x, fst_x2, sec_y, sec_y2)
# cc <- ms_combochart(fst_x, fst_x2, sec_x, sec_x2, sec_y, sec_y2)

### create powerpoint
doc <- read_pptx()
doc <- add_slide(doc, layout = "Title and Content", master = "Office Theme")
doc <- ph_with(doc, value = cc, location = ph_location_fullsize())
print(doc, target = "/tmp/example.pptx")

JanMarvin · 2023-12-05T16:32:53Z

The first birthday of this PR is approaching. I'm in no rush and have been using this branch in production for quite a while, but please let me know if there is anything I can do to speed up the review process.

…`second_axis`

JanMarvin changed the title ~~second y axis with linechart~~ [draft] second y axis with linechart Dec 7, 2022

JanMarvin changed the title ~~[draft] second y axis with linechart~~ Second y axis with asis mode Dec 8, 2022

This comment was marked as outdated.

Sign in to view

JanMarvin force-pushed the sec_y_axis branch 2 times, most recently from 8f17ab5 to 59289f9 Compare April 22, 2023 14:02

This comment was marked as outdated.

Sign in to view

JanMarvin commented Apr 22, 2023

View reviewed changes

R/ms_chart.R Outdated Show resolved Hide resolved

JanMarvin force-pushed the sec_y_axis branch 3 times, most recently from 3144692 to b87e06a Compare April 23, 2023 13:02

JanMarvin commented Apr 23, 2023

View reviewed changes

R/axis_codes.R Show resolved Hide resolved

R/ms_chart.R Outdated Show resolved Hide resolved

R/ms_chart.R Outdated Show resolved Hide resolved

This comment was marked as outdated.

Sign in to view

JanMarvin commented May 9, 2023

View reviewed changes

R/ms_chart.R Outdated Show resolved Hide resolved

JanMarvin force-pushed the sec_y_axis branch from 57c306e to 32d8d6b Compare May 10, 2023 08:59

JanMarvin force-pushed the sec_y_axis branch from f019e3d to f1c98c0 Compare May 13, 2023 09:20

JanMarvin force-pushed the sec_y_axis branch 2 times, most recently from c21ba59 to be726a1 Compare May 13, 2023 15:59

JanMarvin changed the title ~~Second y axis with asis mode~~ Second axis with asis mode May 14, 2023

JanMarvin mentioned this pull request Apr 30, 2024

Create editable box plots with mschart #97

Open

JanMarvin added 4 commits May 4, 2024 11:04

second y axis with linechart

959c7c3

fixes

d0717fd

provide secondary argument to mschart functions

4fa2c57

cleanup

d245d29

JanMarvin and others added 17 commits May 4, 2024 11:05

secondary_x/secondary_y allow to create combo plots

03ee785

cleanups

7e8fe9e

cleanup argument order

c09135e

add ms_combochart

c8e4f9d

cleanup argument order

b1e4c24

remove add and secondary_y with simpler implementations based on …

107a498

…`second_axis`

allow setting grid_major_line_x/y to FALSE

2253faa

the loop already starts at 2 ...

ce34b2d

fix label logic

f69ccd8

add secondary x option

614e2b8

avoid altering the seed

a9771af

cleanup

5cd4510

skip non asis

5cccc29

cleanup

1156f18

move code from axis settings

ff77933

add documentation

23eccff

Fix rebase and please roxygen2

e350d5b

JanMarvin force-pushed the sec_y_axis branch from be726a1 to e350d5b Compare May 4, 2024 09:30

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Second axis with `asis` mode #84

Second axis with `asis` mode #84

JanMarvin commented Dec 7, 2022 •

edited

Loading

This comment was marked as outdated.

This comment was marked as outdated.

This comment was marked as outdated.

JanMarvin left a comment

JanMarvin commented Apr 25, 2023 •

edited

Loading

This comment was marked as outdated.

JanMarvin commented May 9, 2023

JanMarvin commented May 10, 2023

JanMarvin commented May 13, 2023

JanMarvin commented Dec 5, 2023

Second axis with asis mode #84

Are you sure you want to change the base?

Second axis with asis mode #84

Conversation

JanMarvin commented Dec 7, 2022 • edited Loading

This comment was marked as outdated.

This comment was marked as outdated.

This comment was marked as outdated.

JanMarvin left a comment

Choose a reason for hiding this comment

JanMarvin commented Apr 25, 2023 • edited Loading

This comment was marked as outdated.

JanMarvin commented May 9, 2023

JanMarvin commented May 10, 2023

JanMarvin commented May 13, 2023

JanMarvin commented Dec 5, 2023

Second axis with `asis` mode #84

Second axis with `asis` mode #84

JanMarvin commented Dec 7, 2022 •

edited

Loading

JanMarvin commented Apr 25, 2023 •

edited

Loading